Ресурсоемкие формулы: какие операторы это делают и можно ли их заменить?

Пользователь

Сообщений: 104 Регистрация: 22.12.2018

17.09.2024 16:48:15

Добрый день.

Есть таблица на 25000+ строк, в ней прописаны формулы:
- нумерация иерархии (колонка D)
- расчет остатка по ветке иерархии (колонки V и X)

К сожалению, при использовании фильтров, или вставки каких-либо значений файл существенно подвисает, даже на хорошем ПК.
Предполагаю, что дело в формулах.

Кто может подсказать, какая функция "жрет" ресурсы и подвисает файл?
И какой выход можно найти из ситуации (может формулы заменить другими операторами или построение не корректное)?

В файле примере 15 строк, можно протянуть до 25000 и получить тот самый "эффект".

Прикрепленные файлы

Пример.xlsx (23.94 КБ)

Hugo

Пользователь

Сообщений: 25316 Регистрация: 22.12.2012

17.09.2024 17:02:24

25000 вот таких

Код

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ("*";F1:J1;0)<ПОИСКПОЗ("*";F2:J2;0);D1&"1.";

ЕСЛИ(И(ПОИСКПОЗ("*";F1:J1;0)=1;ПОИСКПОЗ("*";F2:J2;0)=1);(ЛЕВСИМВ(D1;НАЙТИ(".";D1)-1)+1)&".";

ЕСЛИ(ПОИСКПОЗ("*";F1:J1;0)=ПОИСКПОЗ("*";F2:J2;0);ЛЕВСИМВ(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1);НАЙТИ("#";ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))-1)&"."&(ПСТР(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1);НАЙТИ("#";ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))+1;ДЛСТР(D1)-НАЙТИ("#";ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))-1)+1)&".";

ЕСЛИ(И(ПОИСКПОЗ("*";F1:J1;0)>ПОИСКПОЗ("*";F2:J2;0);ПОИСКПОЗ("*";F2:J2;0)=1);ЛЕВСИМВ(D1;НАЙТИ(".";D1)-1)+1&".";ЛЕВСИМВ(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1);НАЙТИ("#";ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))-1)&"."&ЛЕВСИМВ(ПСТР(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1);НАЙТИ("#";ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))+1;ДЛСТР(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1)));НАЙТИ(".";ПСТР(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1);НАЙТИ("#";ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))+1;ДЛСТР(ПОДСТАВИТЬ(D1;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1))))-1)+1&"."))));"1.")

а там ещё тогда и 50000 таких

Код

=ЕСЛИ(ПОИСКПОЗ("*";F2:J2;0)<>1;
     (
     T2
     *
     ВПР(ЛЕВСИМВ(D2;НАЙТИ("#";ПОДСТАВИТЬ(D2;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1)));$D$1:$V$498;19;0)
     /
     ВПР(ЛЕВСИМВ(D2;НАЙТИ("#";ПОДСТАВИТЬ(D2;".";"#";ПОИСКПОЗ("*";F2:J2;0)-1)));$D$1:$T$498;17;0)
     )
     -U2;
T2-U2)

конечно будет долго...

EvgeniyLFC

Пользователь

Сообщений: 104 Регистрация: 22.12.2018

17.09.2024 17:13:15

Hugo, вот первые 25000, где строится нумерация иерархии - никак не сказывается на скорость, тестировал)
А вот вторые 50000 - да, сказываются.

Ну у экселя 1000000 строк / записей делать можно, а тут простой ВПР, и сразу прогружается?)

EvgeniyLFC Пользователь Сообщений: 104 Регистрация: 22.12.2018	#4 17.09.2024 19:35:55 Может кто платно готов помочь? Или текущим подходом вариантов нет…

Sanja Модератор Сообщений: 17958 Регистрация: 10.01.2013	#5 17.09.2024 19:39:48 Помочь в чем? Нужно на макросы переходить Изменено: Sanja - 17.09.2024 19:40:01 Согласие есть продукт при полном непротивлении сторон

Hugo Пользователь Сообщений: 25316 Регистрация: 22.12.2012	#6 17.09.2024 20:19:14 Если версия с динамическими диапазонами - можно UDF пробовать писать. Если есть точное полное ТЗ, вычислять задачу из формулы такое себе...

БМВ

Модератор

Сообщений: 23773 Регистрация: 28.12.2016

Excel 2013, 2016

17.09.2024 20:33:35

Цитата
Sanja написал: Нужно на макросы переходить

ну не совсем так категорично. Всего 5 уровней, все достаточно просто наращиваем счетчик нужного уровня по наличию инфо в одном из 5 столбцов. Нужно подумать но придумать точно можно. Альтернатива считать уровни только от начала предыдущего но это как минимум 4 раза поиск последней заполненной ячейки -что тоже ресурсоемко..
=LEFT(D1;FIND(",";SUBSTITUTE("."&D1;".";",";MATCH("*";F2:J2;)))-1)&IFERROR(--TRIM(MID(SUBSTITUTE("."&D1;".";REPT(" ";99));MATCH("*";F2:J2;)*99;99));0)+1&"."

Прикрепленные файлы

example2997.xlsx (23.04 КБ)

Изменено: БМВ - 17.09.2024 20:51:42

По вопросам из тем форума, личку не читаю.

EvgeniyLFC

Пользователь

Сообщений: 104 Регистрация: 22.12.2018

17.09.2024 21:45:14

Ребята, всем спасибо за ответы.

У меня вопрос - кажется, что все думают, проблема в нумерации иерархии.
Однако у себя провел эксперимент:
- если протянуть формулу нумерации иерархии на 25000 строк, то все быстро считается, фильтры работают как обычно, без задержек;
- если протянуть столбцы с расчетом количества по иерархии на 25000 строк, то сразу идет прогрузка.
Мне кажется, что проблема именно по второму пункту. Поправьте, если я ошибаюсь.

Мне бы пояснение, правильно или нет рассуждаю, правильно ли тесты показали проблему, какая функция или код формулы ведет к долгому расчету и каким способом (формулами) можно это устранить.

Hugo

Пользователь

Сообщений: 25316 Регистрация: 22.12.2012

17.09.2024 21:49:31

Цитата
EvgeniyLFC написал: если протянуть столбцы с расчетом количества по иерархии

- нет там такого заголовка.

БМВ Модератор Сообщений: 23773 Регистрация: 28.12.2016 Excel 2013, 2016	#10 17.09.2024 23:16:27 Если формула по первой части не эффективна, то рассуждать про эффективность второй, которая завязан а на результат первой - не стоит. Мой вариант пробовался? По вопросам из тем форума, личку не читаю.

EvgeniyLFC

Пользователь

Сообщений: 104 Регистрация: 22.12.2018

#11

18.09.2024 10:21:42

БМВ, разобрался, значительно лучше стало, огромное спасибо!
Со второй частью - мой вариант оптимален, если данный расчет необходим (колонки "Долг от сдачи" и "Долг от назначения") или есть еще варианты оптимизировать?

БМВ Модератор Сообщений: 23773 Регистрация: 28.12.2016 Excel 2013, 2016	#12 18.09.2024 20:06:28 ну критичного в ней нет ничего, будем считать что оптимален. По вопросам из тем форума, личку не читаю.