Страницы: 1
RSS
Оптимизация отчета с большим количеством формул СУММПРОИЗВ, 1,5 тыс. формул СУММПРОИЗВ с ссылкой на диапазон до 20 тыс. строк.
 
Уважаемые эксперты, прошу помочь в решении следующей задачи.

Дано:

1. Дерево отчета неизменно и содержит порядка 300 рассчитываемых строк и до 20 столбцов (кол-во столбцов зависит от набора периодов, проектов, объектов; мин. количество столбцов = 5);
2. Каждая ячейка отчета содержит формулу СУММПРОИЗВ с несколькими условиями;
3. Обрабатываемый диапазон на соседнем листе может содержать до 20 тыс. строк.

Проблема:

При количестве столбцов = 5 и диапазоне=2 тыс. строк наблюдается замедление при группировке и разгруппировке структуры отчета.
Также замедляется копирование столбцов отчета с формулами вправо.

Вопрос:

Как оптимизировать формулы, чтобы не страдала производительность?

См. пример в приложении.




   
Изменено: Elena_14 - 12.07.2019 17:39:21
 
Power Query
Это не для формул задача.

/вам, по-моему, в платный раздел форума. Или разбираться с нуля, задавая тут вопросы про непонятное и как его применить к конкретной ситуации. Это надо разбирать или долго или умело.
Изменено: Xel - 12.07.2019 13:33:16
 
А PQ позволяет получать значения в отчет с фиксированным деревом?
Я просто никогда не сталкивалась...
 
И если можно короткий пример..
 
У вас задача вообще не для формул, вы же это уже видите.
Для начала надо адище в исходных данных разгрести.

/не реклама, я как раз чайник, недавно с помощью бесценных советов с этого форума нечто похожее кое как разгребший.
Изменено: Xel - 12.07.2019 13:41:13
 
Ну, это не такое уж адище)
Инфа из источника должна использоваться в нескольких отчетах и потому там такое большое количество аналитик.

Спасибо за вектор.
 
У вас задача по "выпрямлению" исходных данных, потом поместить их в модель данных и сделать обычную сводную таблицу. В ней будет и дерево и структура и фильтрация и много чего еще вкусного.
Вот только разбираться в ваших формулах мягко говоря долго.
Вот горшок пустой, он предмет простой...
 
Обычная сводная таблица не подойдет, т.к. в шапке каждого столбца может быть индивидуальный набор аналитик.
Например, по проектам и последний резюмирующий за период.
 
Так это и есть вопрос к исходным данным. Будут они нормальные - сводная прекрасно подойдет.
Но это работа, а не "ответить на вопрос чайника".

(мой компромиссный вариант от чайника, еще не достигшего просветления - подготовка в PQ небольших таблиц-полуфабрикатов под конкретный отчет с частичной группировкой данных, из которых данные собираются в отчет теми же формулами. Но формулы короче и работают по диапазону в сотни строк).
Изменено: Xel - 12.07.2019 14:09:40
 
Ок. Придется научиться самостоятельно.
Спасибо.
 
Цитата
Elena_14 написал:
Обычная сводная таблица не подойдет,
Принципиальным в моем посте было именно построение сводной на базе модели данных Power Pivot. А это уже не совсем обычная сводная и с разными наборами аналитик для каждого столбца там все в полном порядке. Другой вопрос, что сначала вам нужно с этим делом вдумчиво ознакомиться, а потом уже спрашивать что у вас не получается реализовать этим инструментом.
Вы же хотите чтобы к вам пришли, разобрались с километровыми формулами в вашем отчете и все оптимизировали.  :D
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
 все оптимизировали .  
зачетная кнопочка :)
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Я спрашивала "как увеличить производительность" и не просила делать эту работу за меня.
Направление я получила. Спасибо еще раз.  
 
Elena_14, Можно конечно чуть подрихтовать, подумать как перейти на сумму результата SUMIFS, что будет быстрее, но это погоды не сделает.
например использовать не весь диапазон столбцов, а только те, что указаны для периода, предварительно их определив первый и последний,
типа  в технической области для примера
=MATCH(D9;Транзакции!1:1;) и =MATCH(D10;Транзакции!2:2;) а сам диапазон
=INDEX(Транзакции!$1:$1048576;4;M9):INDEX(Транзакции!$1:$1048576;1916;M10) - это спрятать в имена для удобства и использовать в основной формуле вместо
Транзакции!$Y$4:$AJ$1916
при этом от туда уйдет часть (Транзакции!$Y$1:$AJ$1>=D$9)*(Транзакции!$Y$2:$AJ$2<=D$10) , сильно сократив формулу и так как вычисления производятся один раз, то и расчеты будут серьезно меньше.

=IF(D$5="Группа";
SUMPRODUCT((Транзакции!$F$4:$F$1916<>"Группа")*(Транзакции!$H$4:$H$1916=$B28);_Data);IF(D$5="Проект";
SUMPRODUCT((Транзакции!$G$4:$G$1916<>"Проект")*(Транзакции!$N$4:$N$1916=D$7)*(Транзакции!$H$4:$H$1916=$B28);_Data);IF(D$5="Нет";IF(AND(D$6<>"Нет";D$7="Нет";D$8="Нет");
SUMPRODUCT((Транзакции!$A$4:$A$1916=D$15)*(Транзакции!$H$4:$H$1916=$B28);_Data);IF(AND(D$6="Нет";D$7<>"Нет";D$8="Нет");
SUMPRODUCT((Транзакции!$B$4:$B$1916=D$16)*(Транзакции!$H$4:$H$1916=$B28);_Data);IF(AND(D$6="Нет";D$7<>"Нет";D$8<>"Нет");
SUMPRODUCT((Транзакции!$H$4:$H$1916=$B28)*_Data);
SUMPRODUCT((Транзакции!$K$4:$K$1916=D$4)*(Транзакции!$H$4:$H$1916=$B28);_Data)))))))


_Data = =INDEX(Транзакции!$1:$1048576;4;БДР!D$1):INDEX(Транзакции!$1:$1048576;1916;БДР!D$2)
D$1 =MATCH(D9;Транзакции!1:1;)
D$2 =MATCH(D10;Транзакции!2:2;)
Изменено: БМВ - 12.07.2019 15:20:57
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
сильно сократив формулу и так как вычисления производятся один раз, то и расчеты будут серьезно меньше.
Я чуть-чуть поправила Вашу формулу и полетело))
Большое человеческое Вам спасибо!
 
Цитата
Elena_14 написал:
чуть-чуть поправила Вашу формулу
Good job.
Цитата
Elena_14 написал:
и полетело
Excellent!
вот эту часть ;1916 вынести в отдельную ячейку и ссылаться на нее, а уже в ней считать строки в данных, чтоб не делать запас по строкам.
Обратите внимание, в отличии от первичной формулы, новая чувствительна к порядку столбцов с данными.
Но в целом, предыдущие критики писали правильные вещи, формат данных не самый хороший для отчетов, хоть и с ним можно работать.

Успехов.
По вопросам из тем форума, личку не читаю.
 
Спасибо!
Страницы: 1
Наверх