Данные на листах - агрегированная информация за все периоды, сравнение годов, кварталов и т.д. Прошлые периоды участвуют на равне с текущими )

В файл собираются данные за 10 лет работы. Агрегация по 28 столбцам. Это минимум данных. Если меньше - то считать каждую вкладку в БД отдельно и выгружать данные под нее. Чтобы сделать я повторю путь описанный мной в варианте 1.

Суть проблемы:
Есть книга, в которую грузится из Oracle порядка 300-400 тыс. строк.
Далее, на основании этих данных рассчитывается порядка 14000 формул, множество сводных, графиков и пр.
Только вкладок в книге больше 40.
Файл "весит" 80-90 МБ. Ежемесячное увеличение не критическое.
Проблема в том, что обновление файла занимает 2-3 часа, что для конечного пользователя мрак и ужас.

Пробовал использовать макросы для вычистки мусора - результат печальный, за 1 сутки, файл уменьшился на 20 мегабайт и пропало множество связей, модулей, графиков и пр.

Сейчас думаю над 3 вариантами:
1) Полностью рассчитать данные в БД и в файл выгружать только конечные значения. Размер файла будет минимальным, Обновление занимать до 1 минуты. Количество времени на реализацию даже не могу себе представить. В файле находил формулы на основе формул и те, в свою очередь, на основе формул и так раз 20. Только чтобы разобраться потрачу месяц, если не больше.
2) Обновлять файл автоматически раз в месяц ночью. Конечный пользователь работает с копией файла, в которой данные(уже посчитанные) тянуться из файла на общем сетевом ресурсе. Потратил 1 день. Не знаю даже как подступиться. Без понятия каким образом зайти под учетными данными пользователя на сетевой ресурс, открыть в фоне книгу (или не открывая) вытащить данные в том же виде и формате (ессно без "сырых" данных, только посчитанные и готовые для использования)
3) Держать файл на сетевом ресурсе, обновлять его автоматически. Конечный пользователь копирует файл на локальный компьютер и творит с ним, что хочет. Проблема в том, что пользователей много и каждый на основе файла допиливает еще свою логику под свои нужды и повторять это каждый раз никто не хочет.


Вот как бы и все. Файл создавался не мной. Дополнялся в течении лет 6. Идеи закончились. Не знаю что гуглить.

Загрузил в excel 13 миллионов строк. Идея мертворожденная. Ворочать подобным на клиентской машине - идиотизм. По крайней мере дохлый ПК и excel на это нормально не способны. Спасибо всем. Буду искать другие способы.

Повторил примеры StepanWolkoff и Максим Зеленский. Каждый раз получаю ошибку при попытке запихнуть данные в Модель данных )
И грузит оно весь объем, а к нему применяет фильтры. А объема у меня на 10-15ГБ. Видимо что-то я делаю не так...

Отчечаю TheBestOfTheBest. Пример во вложении. Ради 1 пункта в фильтрах поля сводной и делаю все это.

рисунки удалены: превышение допусимого размера вложения [МОДЕРАТОР]

Спасибо огромное, ваши ссылки и ссылки Максим Зеленский как раз и изучаю. Отвечаю только TheBestOfTheBest. Вижу, что ваши примеры то что мне и надо. Осталось разобраться с интерфейсом PowerQuery и найти границы допустимых объемов данных.

Вы понимаете суть сводной таблицы? Пользователю нужна возмоность крутить многомерные данные с автоматическим подсчетом уникальных значений "на лету". Стандартная сводная такого делать не умеет. Я же могу в БД расчитать одну из множества проекций. То, что вы предлагаете называется OLAP CUBE и уже совсем другая история...

Потому что только сводная построенная на основе Модели данных может подсчитывать количество уникальных элементов.

Набор возвращаемых строк будет неизменным (для начала), меняются только фильтры запроса. Это конкретная задача? Вытаскиватьодну и ту же информацию за разные периоды. Конечный пользователь не должен знать sql, не должен лазить по менюшкам и править запросы руками. Я понимаю, что нужен нормальный ОЛАП под такие задачи, но его пока нет. Его внедрение займет много времени и сил. Ищу альтернативы.

Не понял, можете навести на статью по этому поводу? Что за таблица параметров запроса?

кнопка цитирования не для ответа [МОДЕРАТОР]

Со сводными вроде как уже успешно работал, но фишка с количеством уникальных элементов не работает. А PowerPivot значит совсем закрыт для разработчиков? ((( Грусть. Спасибо

Если открыть в excel вкладку PowerPivot - Управление - Из других источников - Oracle - Написать запрос, указывающий данные для импорта, то можно выгрузить столько строк, на сколько хватит оперативки. Дело в том, что пользователь должен получить уже готовые решения для разных запросов, а не лазить по этим вкладкам, писать SQL и т.д. Как мне формировать такой SQL с помощью VBA?

Так точно. Поэтому на лист их и не выгрузишь. Только напрямую в Сводную или Модель данных.

Доброго времени суток.
Задача: запихнуть в эксель 3-5 миллионов записей. Крутить их во всех pivot'ах и проекциях. Часто нужно подсчитывать количество уникальных элементов.
Последнее умеет делать сводная таблица с источником данных из модели данных.
Как мне посредством VBA запихнуть мой RecordSet в модель данных? Или как мне сразу формировать модель данных с параметризированным запросом к БД?
В качестве фильтров запроса нужно будет выбирать период.
В идеале еще и динамически менять количество выводимых столбцов.
Заранее благодарен за любые подсказки!!!

Еще 1 косяк. Кириллица отображается аброкозябрами. как можно этого избежать не меняя кодировки пользовательского компа? У кого-то отображается корректно, у кого-то-нет

Столкнулся с идиотской проблемой, прошу помощи!
Есть excel отчет.
Посредством VBA реализован следующий интерфейс и фичи:
Пользователь жмакает на кнопку "Обновить данные" и видит форму со списком полей , выделяет нужные ему, жмет "ОК" и в этот момент строится SQL и происходит запрос в Oracle, далее данные просто выгружаются на лист. У меня запрос, который возвращает 16к строк отрабатывает за 15-20 секунд. У пользователя - запрос выполняется с той же скоростью, но отрисовка на лист ОЧЕНЬ медленно, может 1-2 строки в секунду. Компьютеры аналогичные, excel тот же. Куда копать?

Огромное спасибо!
До этого пробовал ActiveX объекты, в них как раз начало с 0 элемента! Сейчас доделаю инициализацию при открытии книги, проставлю индексы и юххха! Спасибо!

Не работает. Пробовал и батник запускать из примеров. Не помогает. А если создать файл в 2013 офисе, то потом при открытии его в 2007 - элемент просто не активен. Вот и мучаюсь с этим ListBox. Да и нужно мне от него немного. Все 3 параметра вытащить, а уж как там дальше буду морочится, пока не думал.

Получить доступ к объекту вышло, но нижеприведенный код почемуто не работает:

Доброго времени суток.
Задача:
Есть список полей. Нужно предоставить возможность пользователю выбрать нужные из них для последующей загрузки. Для выполнения данной задачи решил использовать элемент ListBox(Не ActiveX. 2007 Office!). Он поддерживает мультивыбор и подходит в плане использования рядовым юзером.
Проблема в том, что никак из VBA не могу достучаться до этого объекта и получить такие данные: количество строк, названия выделенных строк, индексы выделенных строк.
Прошу помощи! Заранее благодарен!
P.S. В интеренете море информации, однако, все примеры у меня не запускаются. Обращение по полному пути Книга.Лист.Объект тоже выдает ошибку.
P.P.S. Файл с примером во вложениях.

Да именно по этой статье и делаю.
У меня проблемы даже не с некорректными значениями регионов, а с невозможностью построить карту по сводной таблице.
В сводную таблицу может быть выгружены несколько миллионов записей. И она их скушает. Но если эти же данные выгружать на лист, то excel не позвлит этого сделать.

Спасибо! Думаю если разберусь с Power BI, то выйдет конфетка, вроде как там даже через мобильные можно смотреть отчеты. О таком начальство и мечтать не могло. Правда пока все звучит на грани фантастики.
А попробовать доделать отчет средствами попроще пока не знаю как. Работал кто-то с Power Pivot? Вроде его можно использовать для корректной модели данных для отчета.

Сам ответ нужен многим клиентам. Excel сам лезет в БД и по заданным параметрам вытаскивает данные в сводную таблицу. Чистить названия регионов нужно после отображения в сводной таблице. Пока в голову кроме как дублировать информацию еще и на другой лист и уже по нему строить карту не приходит

Очень нравится вид Power Map, ино\формативность на уровне, но проблема в том, что как его подружить со сводной и корретно настроить вообще без понятия. Надеюсь, что Bing maps попроще.

Пример во вложениях. Добавить карту Bing не могу - сразу размер файла больше 100к.
Карта нужна по полям Регион, Итог и зависимость от фильтра Тип.
Если бы не было Колонка 1 и Колонка 2, то все бы получилось. Если столбиков более 1, то ничего не могу сделать. А как заставить смотреть на столбик Регион с учетом того, что сводная классическая и позволяет менять их местами, не представляю.

Да какой уж тут пример. Сводная таблица из 3 колонок: Города, Тип, Количество операций. Нужно их корректно отобразить на карте Bing из магазина Microsoft.

Строю сводную таблицу напрямую из БД. В сводной таблице есть колонка с населенным пунктами, но не совсем корректно заполненными(пример: Луцк и Волынская область) и вторая колонка количество операций в нас. пункте. Мне надо рядом на листе сделать интерактивную карту по этим столбцам. Каким образом мне натравить карты на корректные колонки сводной таблицы (с учетом того, что их могут перетаскивать) + на лету подкорректировать названия?
В идеале еще прикрутить корректное отображение на карте только тех нас. пунктов, которые выбраны фильтром в сводой таблице.

Спасибо. Все портит Excel 2007. К нему применим только самый примитивный способ, который я даже не представляю как менять, перекрашивать и т.д.
И подобные диаграммы не применимы к сводным таблицам.

Поставили задачу - на основе данных сводной таблицы построить карту Украины.
В таблице данные в виде:
Регион - количество обращений
В идеале должно иметь вид графика или графическго объекта, обновляться по кнопке, вести свою легенду.
С какой стороны зайти даже не представляю.
Excel 2007. Возможности обновиться нет.
Если и будет возможность обновить, то только мой компьютер. Клиенты такой возможности иметь не будут.