Страницы: 1
RSS
Долго пересчитывается файл, Долго пересчитывается файл
 
Добрый день. Помогите пожалуйста, как можно облегчить файл 9 листов по 2000 строк вправо идут столбцы по приблизительно 10 отделам разбитые на 12 мес? все формулы нужны, но пересчитывает по 40 минут файл, а необходимо динамично с ним работать(( не могу выложить файл к сожалению, могут уволить за несоблюдение конфид-ти((
но формулы практически в каждой ячейке выглядят примерно так:

=СУММЕСЛИМН(БП_2023_27[Сумма];ДВССЫЛ($A950);$E950;БП_2023_27[Год];$D$1;БП_2023_27[Функция_РН];$T$5;БП_2023_27[Месяц];W$6;БП_2023_27[Производственный блок];$F950)

Ссылка проиcходит на там же находящуюся выгрузку на соседнем листе - запрос Power Query - где-то 100 тыс строк.  
 
на листе 2000 строк и 10*12 = 120 столбцов с формулами
это 200 тыс.формул
таких листов 9 - это 2 млн.формул.
пересматривайте философию (структуру) вашего файла
20 тыс. формул - уже повод задуматься
100 тыс. время бить тревогу
а у вас 2 млн.формул, даже самый быстрый компьютер можно задолбать расчетами при бестолковом алгоритме или кривой структуре файла
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Согласен с Ігор Гончаренко.
tat_lyk, а почему Вы все эти расчеты не делаете в Power Pivot?
 
Нам вышестоящая организация предоставляет такой шаблон для заполнения((
Там простыня - на 1750 строк - мы ведем свою отчетность в своей компании - все у нас зашито в Query и Pivot и смотрим результаты в сводной таблице.
Но материнская компания дает свои шаблоны и вот мы делаем плоскую таблицу-выгрузку из Query и затягиваем в их Excel - там структура тупо статьи - месяца, филиалы - все на одном листе, тут же в шаблоне нужна выверка ВГО, другой лист расшифровываем данные по персоналу, и третий лист энергия. И так 23 год, такие же 3 листа по 24 году и еще три листа - без разбивки по месяцам 25-27 года...

Но по сути 1750 строк - это только первый лист - со стаьями и там не все 1750 строк идут мои формулы - там еще много суммирующих строк.

Затянуть мои данные кроме как через суммесли - других идей просто нет((( может посоветуете что-то?

вот прикладываю скрин - и вот так тянется на 1750 строк - я заполняю только белые ячейки, т.е. такие сложные формулы у меня только в белых.. но вправо идут еще блоков 10 вот таких голубых и везде месяца...  
 
Цитата
написал:
Согласен с Ігор Гончаренко.
tat_lyk, а почему Вы все эти расчеты не делаете в Power Pivot?
мы делаем, но компания дает для сдачи отчетности свою такую простыню, как затянуть свои цифры в их экселевский лист..?  
 
tat_lyk, здравствуйте
Можно всю структуру оставить, как есть и заменить только "тяжёлые" формулы на макросы (вставка значений по кнопке, даблклику по ячейке или другому событию).
После этого всё станет заметно быстрее работать (а, может, и вообще будет "летать"), а пересчитывать будете только, когда надо.
Изменено: Jack Famous - 29.07.2022 14:26:32
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Для начала избавьтесь от ДВССЫЛ($A950). Зачем она Вам там? Просто $A950 почему не написать( Ну, на худой конец, =ИНДЕКС(A:A;950), но непонятно, какой смысл в этом
Посмотрел скрин. У Вас там в файле еще куча именованных диапазонов. Короче, без более-менее похожего примера будет сложно что-то посоветовать. Ну, разве что можно персчитывать только нужные диапазоны и макросом вставлять значения вместо формул
Изменено: _Boroda_ - 29.07.2022 14:29:08
Скажи мне, кудесник, любимец ба’гов...
 
Цитата
tat_lyk написал:
компания дает для сдачи отчетности свою такую простыню, как затянуть свои цифры в их экселевский лист..?  
Почему и Ваши, и "Чужие" цифры не привести в PQ к единому виду и не делать Ваш анализ в одной модели данных?
Я не могу это понять :)
Все, что у Вас на рисунке можно получить в одной сводной из одной модели данных.
Изменено: surkenny - 29.07.2022 14:44:14
 
Цитата
Цитата
написал:
посоветовать. Ну, разве что можно персчитывать только нужные диапазоны и макросом вставлять значения вместо формул
Здравствуйте, а как это сделать? есть какой-нибудь пример такого макроса?  
 
Цитата
написал:
Цитата
tat_lyk написал:
компания дает для сдачи отчетности свою такую простыню, как затянуть свои цифры в их экселевский лист..?  
Почему и Ваши, и "Чужие" цифры не привести в PQ к единому виду и не делать Ваш анализ в одной модели данных?
Я не могу это понять
Все, что у Вас на рисунке можно получить в одной сводной из одной модели данных.
а как заполнить эксель-шаблон? форму вышестоящей организации я менять не могу и что-то им предлагать тоже, даже менять суммирующие оранжевые строки я не могу - там все заблокировано. я могу только хоть как но вставлять свои цифры в белые строчки и все  
 
Цитата
tat_lyk: есть какой-нибудь пример такого макроса?
все тяжёлые функции типа ВПР, СЧЁТ/СУММ/СЦЕПИТЬ/МАКС/МИН/СРЕД-ЕСЛИ заменяются на макросы со словарями в качестве основы. Вот пример для ВПР.
Можно ещё сделать статичные словари, обновлять их при изменении исходных данных, а возвращать агрегации по ключам макрофункциями на листе.
Изменено: Jack Famous - 29.07.2022 14:52:06
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
_Boroda_ написал:
Для начала избавьтесь от ДВССЫЛ($A950)
Вот поддержу однозначно, хотя может не дать результата, так как один раз пересчитать все - это тоже проблема.
По вопросам из тем форума, личку не читаю.
 
Цитата
написал:
Цитата
_Boroda_ написал:
Для начала избавьтесь от ДВССЫЛ($A950)
Вот поддержу однозначно, хотя может не дать результата, так как один раз пересчитать все - это тоже проблема.
я не могу двссыл убрать( мне тогда надо прописывать еще одно условие - просматривать целый ряд статей на признак - Уровень в PL. Так я по статье ищу какой уровень у такой-то статьи (через ВПР с другого листа) - и потом в зависимости от уровня выбираю нужный столбец (так как уровни эти в выгрузке находятся в разных столбцах.. ) и ДВССЫЛ именно ссылается на эту ячейку.
Я тогда должна руками морочиться по каждой статье искать руками уровень и формулы каждую в зависимости от уровня подвязывать, у меня так усложняется вообще составление формулы. я буду по каждой из 1750 строк искать уровень..
в общем, понимаю, что непонятно, но от ДВссыл пока не могу избавиться(
Приложила скрин - может поможете от нее избавиться..  
 
Цитата
tat_lyk написал:
ДВссыл пока не могу избавиться
понимаете, по скринам судить сложно, но летучая функция ( а это не означает что она влет пересчитывается) заставляет пересчитывать всю таблицу при каждом чихе, при этом даже в соседней открытой книге. Как написал выше, если даже разовый пересчет затруднен, то избавляться не стоит, но если беспокоит задумчивость, при каждом вводе, при каждом действии, тогда это то, что к этому приводит. В целом если это верно
Цитата
Ігор Гончаренко написал:
на листе 2000 строк и 10*12 = 120 столбцов с формуламиэто 200 тыс.формултаких листов 9 - это 2 млн.формул.
то формулами что-то исправить врядли удастся, хотя убрав летучесть можно получить в 9 раз увеличение за счет расчетане по всем листам, но возможно это будет компенсировано поском нужного диапазона, но надо смотреть на нормальном примере а не на картинках.
По вопросам из тем форума, личку не читаю.
 
Цитата
написал:
а как заполнить эксель-шаблон? форму вышестоящей организации я менять не могу и что-то им предлагать тоже, даже менять суммирующие оранжевые строки я не могу - там все заблокировано. я могу только хоть как но вставлять свои цифры в белые строчки и все
Можно создать еще лист, на который будет выгружаться, например из PQ, таблица-копия шаблона. А из таблицы-копии уже ссылками на ячейки затягивать данные в основную эксель-шаблон.
Страницы: 1
Наверх