Страницы: 1
RSS
Минимизировать время открытия книги, содержащей источники из Power Query
 
Добрый вечер, уважаемые форумчане! Попробую описать ситуацию, файл с примером не могу приложить он очень громоздкий.

Создана финансовая модель, одна страница содержит дынные по месяцам на 20 лет по нескольким проектам (7 500 строк ), данные в этот лист подтягиваются с соседнего листа Исходные данные  посредствам формул (в основном СУММЕСЛИМН). Сам лист Исходные данные выгружаются запросом с сервера с обработкой через Power Query. Файл работает, но открывается по 20 минут, при сохранении долго приходиться ждать.
Хочу улучшить эффективность работы этого файла, предполагаю что работа функции СУММЕСЛИМН тормозит работу. Можете пожалуйста, подсказать, может лучше данные формировать в Power Pivot , прописать меры, но стает вопрос как их вытащить в ячейки на 20 лет, может КУБЗНАЧЕНИЕ, КУБЭЛЕМЕНт (я пока не знакома с этим , но нашла что это ускоряет процесс и таким образом можно вытащить данные в любой отчет).

Можете дать профи-совет, буду благодарна. P.s. если все-таки нужен пример, постараюсь своять примерчик из нескольких строк.
Изменено: vikttur - 12.07.2021 22:13:12
 
Цитата
Елена Семенова написал:
Улучшить работоспособность файла ексель,
Переформулируйте и предложите новое название темы: что конкретно нужно улучшить? Проблема в чём? Модераторы поменяют.
 
Минимизировать время открытие файла до максимально возможного минимума (сейчас жду открытия файла 20 мин). Коллеги предложили исходные данные источник с Power Query вынести в отдельный файл и в модель ссылками забирать данные из вновь созданного файла, но я считаю что это не улучшит работу, куча ссылок.
 
Елена Семенова, 7500 строк в PP должны летать. PQ тоже, если запросы нормально написаны.

Цитата
Елена Семенова написал:
но стает вопрос как их вытащить в ячейки на 20 лет
Вот это вообще не понял.  
 
Цитата
Елена Семенова написал:
(сейчас жду открытия файла 20 мин)
отключите обновление данных при открытии файл

и исходные данные, которые грузятся с сервера стоит в отдельном файле хранить, а не в этом. А к ним уже со стотыщпятсот запросами обращаться за нужной информацией

7500 строк это ни о чем. минуту максимум на старом компе должно грузиться
 
Пример нужен, сделайте несколько строк с реальной структурой, покажите, что вы там выводите на лист и каким запросом.

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

Плюс к этому, данные тянутся с сервера, а это значит, что данные можно сразу затащить в Power query и выгрузить в модель данных при этом не выгружая на лист, тем самым не снижая производительность ( да и вообще, я уверен, что выгрузка на лист у вас лишнее звено)

Загрузив в модель данных можно уже мерами получить любые расчеты в любых разрезах и файл при этом будет просто летать.

В общем:
1) Покажите запрос с сервера
2) Покажите Файл - что выгружаете на лист и как потом эти данные тянете на другой лист
3) Напишите главную решаемую задачу, что вы всем этим хотите решить. Что посчитать, что показать.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Страница Исх. данные формируется из Power Query (удален-источник с сервера) , получается таблица с 230 000 строк. На листе модель (такой вид требуется для отчетности) по годам с ячейки AP  c 2011 по 2036 год вытягиваются данные формулой СУММЕСЛИМН(пример в ячейке есть). Файл ужасно весит, я буду очень благодарна, если подскажете, как затащить данные в эти месяцы по годам другим способом, чтобы файл стал поворотливее (сейчас он открывается 20-30 мин, иногда вообще отваливается при сохранении). Предполагаю , что функция СУММЕСЛИМН так утяжеляет файл. Может есть иные варианты. Была идея с помощью Power Query сформировать группировки данных на листы (что делает функция СУММЕСЛИ МН), а потом ВПР затащить данные в модель, но так как это большой объем правок, хотелось бы услышать мнение профи, поможет ли это!  
 
Елена Семенова,   из вашего примера вы ищете формулой
Код
=СУММЕСЛИМН('Исх. данные'!$K:$K; 'Исх. данные'!$A:$A; $A12;'Исх. данные'!$B:$B; $B12;
                      'Исх. данные'!$C:$C; $C12; 'Исх. данные'!$D:$D; AP$3; 'Исх. данные'!$E:$E; AP$2)

Значение "ДОУ 75 мест"  в исходных данных. Но там такого значения нет. Либо у вас некорректный пример, либо ошибка какая-то.
Из-за этого я не могу понять задачу.
Вы можете побольше строк в исходнике вывести?
Изменено: vikttur - 14.07.2021 21:20:47
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Добавила данные, там в ячейка некоторых пусто потому что в какие-то периоды нет данных, цифры там появились!
Изменено: Елена Семенова - 14.07.2021 13:21:25
 
Елена Семенова, Где в исходных данных найти  "ДОУ 75 мест"? - вы же его пытаетесь считать на листе Модель. Но в исходных данных нет такой записи
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Там просто по этому объекту нет данных, можно смотреть следующую строку Корп 1-3 , там есть данные ,логика одинаковая, затягивание данных формулой СУМЕСЛИМН из Исх.данных .
По строке Корп 1-3 данные есть.

Удалила строку ДОУ 75 чтоб не смущала))
 
Елена Семенова, не помещается у  меня сюда файл, даже заархивированный.
В общем, все тоже самое, что  вы делаете формулой СУММЕСЛИМН можно сделать сводной таблицей на основе модели данных.

Если напишете почту- вышлю вам пример. А пока картинка с результатом
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Спасибо.
у меня есть в профиле почта, Вы можете туда направить?
Изменено: vikttur - 15.07.2021 16:04:58
 
Елена Семенова, отправил
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Страницы: 1
Наверх