Уважаемые эксперты, прошу помочь в решении следующей задачи.
Дано:
1. Дерево отчета неизменно и содержит порядка 300 рассчитываемых строк и до 20 столбцов (кол-во столбцов зависит от набора периодов, проектов, объектов; мин. количество столбцов = 5); 2. Каждая ячейка отчета содержит формулу СУММПРОИЗВ с несколькими условиями; 3. Обрабатываемый диапазон на соседнем листе может содержать до 20 тыс. строк.
Проблема:
При количестве столбцов = 5 и диапазоне=2 тыс. строк наблюдается замедление при группировке и разгруппировке структуры отчета. Также замедляется копирование столбцов отчета с формулами вправо.
Вопрос:
Как оптимизировать формулы, чтобы не страдала производительность?
/вам, по-моему, в платный раздел форума. Или разбираться с нуля, задавая тут вопросы про непонятное и как его применить к конкретной ситуации. Это надо разбирать или долго или умело.
У вас задача по "выпрямлению" исходных данных, потом поместить их в модель данных и сделать обычную сводную таблицу. В ней будет и дерево и структура и фильтрация и много чего еще вкусного. Вот только разбираться в ваших формулах мягко говоря долго.
Обычная сводная таблица не подойдет, т.к. в шапке каждого столбца может быть индивидуальный набор аналитик. Например, по проектам и последний резюмирующий за период.
Так это и есть вопрос к исходным данным. Будут они нормальные - сводная прекрасно подойдет. Но это работа, а не "ответить на вопрос чайника".
(мой компромиссный вариант от чайника, еще не достигшего просветления - подготовка в PQ небольших таблиц-полуфабрикатов под конкретный отчет с частичной группировкой данных, из которых данные собираются в отчет теми же формулами. Но формулы короче и работают по диапазону в сотни строк).
Elena_14 написал: Обычная сводная таблица не подойдет,
Принципиальным в моем посте было именно построение сводной на базе модели данных Power Pivot. А это уже не совсем обычная сводная и с разными наборами аналитик для каждого столбца там все в полном порядке. Другой вопрос, что сначала вам нужно с этим делом вдумчиво ознакомиться, а потом уже спрашивать что у вас не получается реализовать этим инструментом. Вы же хотите чтобы к вам пришли, разобрались с километровыми формулами в вашем отчете и все оптимизировали.
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)))))))
Excellent! вот эту часть ;1916 вынести в отдельную ячейку и ссылаться на нее, а уже в ней считать строки в данных, чтоб не делать запас по строкам. Обратите внимание, в отличии от первичной формулы, новая чувствительна к порядку столбцов с данными. Но в целом, предыдущие критики писали правильные вещи, формат данных не самый хороший для отчетов, хоть и с ним можно работать.