Есть таблица в Excel 2003 (выгрузка из Оракла) с некоторым заданным количеством полей и несколькими сотнями тысяч строк.
Соответственно эта таблица разбита на несколько листов, т.к. в Excel 2003 может быть только 65535 строк.
Задача создать свод. Своды делать я умею, но задача немного усложняется тем, что свод многоуровневый.
Под этим в данной ситуации я подразумеваю, что шапка сводной таблицы состоит из нескольких строк. Более понятно видно в прикрепленном файле.

Я сам вижу решение этой задачи таким:
Написать макрос в Excel 2007, который для начала собирает таблицу со всех листов на один лист, думаю миллиона строк должно хватить.
В результате получиться одна большая таблица.
Т.к. вроде многоуровневый свод через "Данные -> Сводная таблица" просто так не сделать, то я думаю решить эту задачу следующим способом.
В основной таблице добавляем один дополнительный столбец, назовем его например "Порядковый столбец". И последовательно пробегаемся по всей таблице и для каждой строки в столбце "Порядковый столбец" в зависимости от комбинации значений других полей проставляем некое целое число, которое в дальнейшем будет использоваться как порядковый номер столбца по которому будет размещена эта сумма для заданной строки.
Тогда задача упрощается.
Через "Данные -> Сводная таблица" нам достаточно разместить всего три поля "Поле 1" разместить в поле строк, "Порядковый столбец" в поле столбцов, а поле "Сумма" соответственно на их пересечении.
Соответственно полученный свод потом можно копировать на другой лист на котором заранее будет нарисована нужная шапка.

Может есть какие-то более простые варианты решения, не используя при этом Access.

Пока писал лишь теорию, т.к. самих таблиц у меня на руках не было.
Теперь прислали файлы.
Основная таблица разбита на 10 листов, общее количество строк порядка 620 тысяч.
Один файл весит почти 300 мегабайт. Открывался минут 5.

PlanBDjon PlayerВопрос в том, можно ли сделать многоуровневый свод средствами Excel 2007. Под многоуровневостью понимается то, что в шапке несколько строк.т.е вопрос - умеет ли так делать сводная таблица (других-то вариантов у вас, по сути, нет)? да, умеет..
Да, основной вопрос был в этом, как строить многоуровневый свод понимая под этим то, что я показал в примере.

Кстати скажу, что в основном свою идею я вчера реализовал, правда ещё не всё автоматизировал.

Вот что сделал:
1. Реализовал макрос, который в дополнительном столбце "Порядковый столбец" проставляет что-то вида X.Y.Z, где X,Y,Z число соответствующее номеру позиции в шапке.
Т.е. например 2.1.3 в моём примере соответствует, координатам "Шапка 1.2, Шапка 2.1, Шапка 3.3".
Второй макрос создает сводную таблицу по трём полям "Поле 1", "Порядковый столбец", "Сумма".
Создал Итоговую таблицу с готовой шапкой в которой с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() подтягиваются суммы из полученного свода.

Что осталось реализовать.
Осталось реализовать мелочи, которые уже делал много раз, тупо надо просто потратить некоторое время.
1. Открытие файла с таблицами.
2. Сбор кусков таблицы со всех листов в одну таблицу.
3. Удаление лишних итоговых данных в собранной таблице.
4. После того как построен свод, осуществить копирование столбца "Поле 1" из свода в Итоговую табличку, чтобы получить список всех значений без дубликатов.
5. Автоматизировать установку формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в итоговой таблице.

В общем остались мелочи. Главное сама идея.

В Access тянуть смысла особо нет, т.к. в любом случае предварительно будет осуществляться открытие этой большой таблицы и время на это будет тратиться в любом случае.
Я пока тестировал макросы на 65 тысячах строк. Заполнение дополнительного столбца делается довольно быстро за 6 секунд. Соответственно, если строк в 10 раз больше, то примерно 1 минуту.
Формирование Свода выполняется за 2 секунды. Думаю что и итоговая формула так-же поставится за несколько секунд.
Программу будут запускать не чаще 1 раза в месяц.
В общем основное время уйдёт на открытие файла и переброску данных с нескольких листов в один.


Тем не менее для общего развития хотелось бы увидеть примеры многоуровневого свода.
Может какие-то ссылки с примерами.

На прямую из Оракла нельзя, туда у меня вобще нет доступа апсового, есть лишь доступ на формирование той самой большой таблицы, но и то, не у меня, а у того человека (из отдела кадров), кто запускал отчёт.
Причем этот отчёт формировался хрен знает сколько часов. Его запустили в Оракле утром одного дня, и до конца рабочего дня он ещё не был сформирован, мне его прислали только ближе к обеду следующего дня.

PlanB,

За многоуровневый свод спасибо.
До этого когда эксперементировал с созданием свода, самого файла с данными, который я в качестве примера потом выложил ещё не было, создал его позже.
Видимо поэтому и результат получался левый, вот и решил задать вопрос про многоуровневый свод.
Когда увидел ваш скриншот, даже хотел написать, что это не то, что я так уже делал, результат не такой, какой мне надо.
Но решил всё таки попробовать для чистоты эксперимента. И на этот раз с нормальными данными получилось то что надо. Спасибо.
Прилагаю файл, в котором сделан этот многоуровневый свод.

Я всё таки пошёл чисто по Excel-евскому пути.
На данный момент реализовал так-же открытие файла с таблицами, сбор всех таблиц в одну и удаление лишних данных. Осталось лишь доделать установку формулы для заполнения итоговой таблицы на основе свода.
Но я пока остановился на том, что хочу ускорить операцию сбора таблиц в одну.
На данный момент сбор таблиц в одну происходит путем последовательного копирования таблиц на один лист, при этом используется буфер обмен, что не очень хорошо.
Процесс копирования длиться около 8 минут.

Код примерно такой:

wb - книга в которой находиться данный макрос, а так-же лист "Свод", на который собираются данные со всех листов книги wb1 начиная не с первой строки, а со строки nomer_pervoy_stroki_s_dannimi=5, т.к. первые 4 строчки занимают шапки таблиц.


Часть этого кода связанного с копированием и вставкой я хотел заменить таким кодом:
Но этот код выдает ошибку 1004 Application-defined or object-defined error.

И вот вопрос такой, нижний вариант кода принципиально не может работать, когда используются две разные книги или что-то там не так.

PlanBDjon PlayerПрилагаю файл, в котором сделан этот многоуровневый свод.зачем?
я же все написал, как сделать!Приложил в качестве ответа на свой изначальный вопрос с вашей подсказки.

Hugo121Djon Player,
а так:

Спасибо, такой код работает, причем примерно в 2 раза быстрее копированяи через буфер.
120 тысяч строк перенёс примерно за 50 секунд.
Одна только проблема, некоторые данные при таком переносе исказились
Например в поле был счёт "23.01", а стало "23,01". Причем даже не совсем понятно, почему именно так произошло искажение. Логичнее было бы, если вместо этого написало 23 января.
Можно ли таким способом как-то сохранить форматы ячеек?

Наконец-то доделал свою программу.

Общее время выполнения программы на моём компьютере вышло 9 минут 42 секунды.
Из них примерно 3 минуты 40 секунд шло открытие файла размером 271 мегабайт содержащего 10 листов с общим количеством строк 618 тысяч, 4-5 минут на сбор данных со всех листов на один лист. Остальное время 1-2 минуты ушло на установка вспомогательных столбцов, формирование промежуточного свода и формирование итоговой таблицы.

FDjon Player, СУБД обрабатывает такой объем данных за пару секунд.Дело в том, что этот объем данных сначала ещё надо загнать в СУБД, а это тоже время.
А данные изначально даны в Ecxel.

Я не стал заморачиваться с Access, потому что хоть и знаю как делать руками импорт данных из Excel, установку связей с Excel и построение селектов, но опыта работы непосредственно с VBA Access у меня мало. А тот опыт что был, был давно, более 10 лет назад.

А в данном конкретном случае количество листов в Excel-евском файле заранее не известно, поэтому я например не могу заранее установить связи на все листы Excel. Плюс по старому опыту помню, что установленная связь жёстко сохраняла путь к связываемому файлу и потом на другом компьютере его было не как не поменять (во всяком случае я тогда, когда этим занимался не смог этого найти и мне потом пришлось пересоздавать повторно руками кое какие связи на компьютере непосредственно пользователя, для которого программа делалась). Наверно в VBA это можно, но тоже не знаю как.

Ради интереса сейчас решил сделать связь с самым первым листом, сразу-же возникла проблема с тем, что шапка таблицы находиться не в первой строчке, а указать в какой строке не предлагает.
В результате в таблицу попало куча левых данных которая находиться над таблицей.
Так-же возникла проблема с числовыми полями, они почему-то не отображаются в связанной таблице, вместо них везде написано #Число! . Что-за такая фигня не знаю, могу лишь предположить, что Access определял тип данных по нескольким первым строчкам, а т.к. сама таблица начиналась лишь с 7-ой строки, а первые 6 строк по данным полям были пустые, то он что-то там неверно определил.

Вобщем не спорю, Access вещь хорошая, при условии что есть большой опыт работы с ним.
А если опыт маленький, а время ограничено, то лучше работать с тем, в чём лучше разбираешься.

PlanBDjon Player, попробуйте сделать, как я написал. там НИЧЕГО сложного, access даже открывать не надо уметь. а работать будет ГОРАЗДО быстрее и надежнее.Как я уже сказал, свою задачу я уже решил, но для общего развития в принципе можно попробовать её дополнительно решить в Access.
Но для этого мне нужна следующая информация для начала:
1. Как из Access программно узнать количество листов в определенном файле Excel.
2. Как программно установить связь с определенным листом Excel, при этом указав конкретный номер строки заголовка таблицы.

Другая проблема ещё в том, что в отчёте с таблицами идут не однородные данные, а именно, периодически внутрь данных вклиниваются всякие итоги, подитоги по некоторым полям, соответственно может быть так, что слово итог и прочие другие слова связанные с итогами могут находиться в полях, в которых идут суммы.
Если при связывании указывать тип полей как числовой и в такое поле попадёт какое-то слово, например итог, то наверняка будет ошибка. Как этого избежать.
Как в принципе задавать правильную размерность полей так, чтобы потом не обломаться, что вдруг какие-то данные не влазят в поле. Слишком большое значение размерности приведет к лишним затратам на размер базы, а со слишком малой размерностью вероятность того, что какое-то значение не влезет в поле, повышается.