Страницы: 1 2 След.
RSS
Какая причина долгих расчетов без предварительного сохранения или открытия параметров экселя, почему скорость расчетов зависит от просмотра параметров экселя
 
Д.день.

В файле используется формульный расчет по выбору пользователя (реализовано через макроф. ВЫЧИСЛИТЬ подставляется одна из нужных формул со списка)
Часть формул используют пользовательские (UDF) функции, часть "обычные"

В файл (умная таблица) вставляется большой массив данных, потом пользователь выбирает что ему нужно дальше увидеть/посчитать.

Столкнулся со следующей проблемой:
- первый расчет идет крайне доооолго. При этом если отменить (эскепт) - все что нужно - все равно будет корректно расчитано.
- последующие расчеты идут условно-моментально.
При этом если после вставки с буфера, перед запустом расчетов файл сохранить, или зайти в параметры экселя - все происходит моментально.

Даю ссылку на видео, на котором заснял 2 ситуации показаны:
1. открываю файл, вставляю в него данные, выбираю нужный расчет - и процесс выполнения после 30 с чем-то процентов уже особо не идет... Отменяю по эскейпту. Потом закрываю.
2 . открываю тот же файл заново. Вставляю данные. Просто открываю-закрываю параметры экселя, выбираю нужный расчет - все считается условно моментально.
(вместо открытия параметров - можно сохранить файл, потом также расчет пройдет шустро, но если сохранение можно натянуть на теорию, то при чем тут открытие окна параметров?)

Все это относится к первому использованию формул содержащих UDF. Можно выбирать расчеты использующие "обычные" формулы, все будет быстро, а на первом выборе затрагивающем UDF будут тормоза.
Любые последующие расчеты (второй, третий, сорок восьмой) будут быстрыми.

Протестировано на 2 рахных компах, т.е. не проблема рабочего места.

Видео https://youtu.be/xQRHHL3zOyU в спойлере
Скрытый текст


PS: Я не могу приложить полноценные примеры, на которых форумчане смогут "покрутить" проблему; т.к. объем вставляемых данных должен быть большой (уменьшение до размера запаковки под требование форума - не покажет проблему), + в исходниках персоналка... обрезать которую = уменьшить размер = невозможность показать проблему...
Прикрепляю только "чистый" файл с десятком строчек почищенных от персоналки, чтоб можно было просто увидеть структуру что откуда и как, вдруг косяки гдето в нем уже... но связи с октрытием окна параметров вот не могу придумать.
Изменено: andylu - 19.10.2021 20:04:52
 
andylu, сразу насколько знаю функция СМЕЩ очень тормознутая, если много их то будет думаю проблемы
Не бойтесь совершенства. Вам его не достичь.
 
я б обратил внимание на AB и OFFSET. последнее не быстро, и летуче , да и без него можно обойтись.
По вопросам из тем форума, личку не читаю.
 
СМЕЩ да, знаю что тугая,
Скрытый текст
Изменено: andylu - 19.10.2021 21:46:09
 
Цитата
andylu написал:
И как мне показалось, с ростом объема данных - СМЕЩ стало выигрывать... именно на летучести при сортировке/фильтрах.
ЭЭЭЭЭ у чего выигрывать?
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
у чего выигрывать
мне возможно показалось, что:
Скрытый текст
Но в любом случае проблема не отсюда. Удалил все левее Z (выпадающий список выбора пользователем что считать)
Никаких смещений и допрасчетов кроме заложенного на ВЫІЧИСЛИТЬ - и все точно так же.
Копипаста - первый расчет "сразу" связанный с любой UDFдолгий, все последующие моментальные.
Копипаста - открыть/закрыть параметры - любые расчеты сразу быстрые.

Вот эта завязка на необходимость открыть параметры (или сохраниться после копипасты). Что это и откуда и как побороть то...
Изменено: andylu - 19.10.2021 21:47:26
 
В чем глубокий смысл выносить в EVALUATE текстовую запись простой функции?
По вопросам из тем форума, личку не читаю.
 
Цитата
andylu написал:
на первом выборе затрагивающем UDF будут тормоза
"Вычислить" это макрофункция работает как UDF, кроме того она в диспетчере имен, а там обычные функции работают как формулы массива. Как она точно работает не скажу, но предполагаю что работа происходит так: сработала макрофункция "вычисляй" в одной ячейке - следом включилась в работу UDF пробежалось по всем ячейкам где есть эта UDF, далее изменение во второй ячейке и вновь UDF включилось в работу и так далее до последней ячейки. И чем больше ячеек в таблице, тем дольше пересчёт. На больших объёмах наверно даже видно последовательность изменений данных в ячейках. Предполагаю если вместо использования "вычисляй", выбранные формулы вставлять в таблицу макросом и после  этого производить расчёт, будет быстрее работать.
Изменено: gling - 19.10.2021 22:30:11
 
БМВ,я не знаю как по другому можно организовать возможность выбора из нескольких вариантов формул для одного столбца.
Плодить доп.столбцы (каждой формуле свой) не вариант.

Можно конечно просто руцями в нужный момент вставлять нужный вариант формулы. Но вот решил сделать "красиво" выбирая из выпадающего списка.

gling,
Кстати, попробую завтра, если убрать эту конструкцию, и просто руками копипастить нужную формулу, останется ли этот тормоз при первом расчете... Но не вижу логически, чем это может помочь. Почему тормоз только на первом расчете связанном с Любой udf, без тормоза с "обычными" функциями, с моментальным пересчетом в дальннйшем уже независимо есть udf или нет ..
Изменено: andylu - 19.10.2021 22:47:59
 
Цитата
andylu написал:
не знаю как по другому
о, в обсуждении всплывают почему то не очевидные для меня сразу идеи)
Если первый тормоз исчезнет при ручной вставке, без конструкции с ВЫЧИСЛИТЬ, то вносить нужную формулу по выпадающему списку - можно пробовать и макросом по изменению таргета)
 
Цитата
andylu написал:
Почему тормоз только на первом
При первом расчёте вставляется формула UDF в первую ячейку-- происходит пересчёт одной UDF-- вставляется во вторую ячейку происходит пересчёт двух UDF.....-- вставляется предпоследняя формула происходит пересчёт на одну меньше чем все--вставляется последняя формула происходит пересчёт всех UDF. Это конечно дольше, ведь в последующих пересчётах UDF уже вставлены и выполняется только последний пересчёт из описанного выше.
 
gling,наверно продолжение своих экспериментов в этом ключе я начну с копания теории что связано с расчетами умных таблиц, что с расчетами udf...
Из вашей логики - мне никак не понятно почему просчет да, большого количества ячеек, связанных с первой вызванной UDF, долгий расчет...
UDF_1.
Почему при выборе/использовании другой функции, UDF_2, и полной переписи всего столбца в это т момент - все происходит уже моментально.
Скрытый текст
Изменено: andylu - 19.10.2021 23:14:21
 
Вы хотите сказать что если выбрать UDF_1 - происходит пересчёт долго, выбрать UDF_2 - происходит пересчёт быстро, вновь выбрать UDF_1 - происходит пересчёт быстро. Так? Одна и таже UDF работает по разному в зависимости от очереди выбора? Медленно работают все UDFки которые запускаются первыми после открытия файла? Для меня такое тоже загадка. Возможно это связано со способностями "Умных таблиц", у них есть своя способность вставлять формулы во весь столбец при изменении в одной ячейке, а здесь ещё накладывается "вычислить". Не знаю, возможно кому и нравится, но я стараюсь не использовать "умные таблицы" с макросами, да и с формулами тоже. На мой взгляд с обычными таблицами работать проще и удобнее.
 
Цитата
gling написал:
ра? Медленно работают все UDFки которые запускаются первыми после открытия файла?
близко)
После открытия (файл уже наполнен), или сохранения (ctrl+s) любая отработает моментально.
Проблема именно после вставки нового большого массива в умную таблицу и первом запуске формулы использующей udf.
Потом тут же сразу пересчет на другую udf_2 моментальный, повторный пересчет первой, или формулы на "простых" функциях, или udf_3_4 (неважно что потом считать) также моментальный.

Цитата
gling написал:
способность вставлять формулы во весь столбец при изменении в одной ячейке
Да) именно поэтому+формат формул под умные таблицы (без "обычных" адресов типа А1) я и затеял все это)

Но и непосредственно с начальным заполнением/формированием диапазонов  умной таблицы после вставки данных тоже не совсем связь, наверно. Т.к. к моменту первого просчёта с udf - нужный столбец уже полностью наполнен (=считай  там сразу есть возращающая #Н/Д)
 
Попробуйте без умных таблиц (преобразовать в диапазоны)
 
vikttur,с обычными диапазонами сейчас и работаем.  И вместо макрофункции вычислить - набор формул-заготовок в блокнотике вставлять по мере надобности в свободный столбец ;)
А это скорее тесты.
Скрытый текст
Изменено: andylu - 20.10.2021 01:33:07
 
Подразумевал: без умных таблиц такое же торможение?
 
Все ж попробуйте без EVALUATE. Это решит ка минимум проблему локализации функций, ну и возможно сильно ускорит
Скрытый текст

всякие мелочи типа NOT меняем на другое условие.
Скрытый текст

Для краткости, сами функции можно в имена, и уже их использовать в головной.
Изменено: БМВ - 20.10.2021 08:01:32
По вопросам из тем форума, личку не читаю.
 
Цитата
vikttur: Попробуйте без умных таблиц
вангую, это тот самый случай ;)

Цитата
andylu: Почему тут именно умные таблицы и не взлетит на обычных диапазонах
вы просто не умеете их готовить — один раз разобраться и можно очень хорошо эмулировать умные таблицы. Формулы и так уже самопротягиваются, кстати…

К слову, я-то продолжаю использовать умные, но формулы в них построчные, без ссылки на диапазоны более одной ячейки и тем более - в другие листы  :)
Да и фильтр всегда сброшен после того, как используется - это единственная очевидная гарантия отсутствия скрытых фильтром строк
Изменено: Jack Famous - 20.10.2021 09:25:08
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
это  тот самый случай
в итоге - нет.
спойлер - глюка именно в связке макрофункции ВЫЧИСЛИТЬ и первом использовании UDF после наполнения данных умной таблицы. Почему именно первом - так и не локализовал.

Что сделано в процессе поиска проблемы и результат:
Скрытый текст

Как проявить проблему на приложенных файлах (если кому будет интересно)
Скрытый текст
Решение пришло не в логику и через костыль:

Скрытый текст

Изменено: andylu - 20.10.2021 10:22:48
 
Цитата
БМВ написал:
попробуйте без EVALUATE.
О! Супер!
БМВ, я как-то с утра пропустил ваш пост (открыл по последнему сообщению, и не скролил выше.

Вот не зря модераторы постоянно делают замечания - описывать не проблему, а задачу ;)
Ваше решение то что нужно!
 
У меня эффект, описанный в #20, возникает только в случае, если значение ячейки F1 меняется в режиме "Копирования" (Application.CutCopyMode=xlCopy). Если после копирования диапазона ячеек нажать на ESC, то пересчет формул производится без замедления.
Не думаю, что причина в конкретных формулах. Вероятно, выбор ячейки из списка в режиме копирования ячеек и является источником проблемы.
Изменено: sokol92 - 20.10.2021 15:52:00
Владимир
 
Цитата
andylu: Какая причина долгих расчетов без предварительного сохранения
подумал не лишним будет упомянуть, что …
При наличии большого объёма данных и совершений операций с ними на скорость работы начинает заметно влиять история изменений, сохраняемая Excel для "отката"
При сохранении книги, закрытия и открытия скорость работы может сильно возрасти
Изменено: Jack Famous - 20.10.2021 15:04:28
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
andylu написал:
Ваше решение то что нужно!
Тормоза пропали?
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Тормоза пропали?
Здравствуйте, Михаил! А их и нет (по крайней мере у меня, Excel 2016), если перед выбором ячейки (F1) нажать Esc (см. #22).
Но, разумеется, отказ от "допотопной" макрофункции более чем оправдан.
Изменено: sokol92 - 20.10.2021 15:55:22
Владимир
 
Цитата
sokol92 написал:
А их и нет (по крайней мере у меня, Excel 2016)
Владимир, добрый день. Ну тут два варианта, или версия или руки не могут воспроизвести то, что удается другому. Я даже не проверял просто сразу за напильник.
По вопросам из тем форума, личку не читаю.
 
del
Изменено: Бахтиёр - 20.10.2021 16:38:55
 
Здравствуйте, Бахтиёр! Почитайте эту тему.
Владимир
 
Jack Famous, я так понял, что ваш пост №23 касается ситуации, когда:
1) Имееется книга с большим объёмом данных и с большим количеством формул.
2) Открыли книгу и работаем с ней.
3) В процессе работы накапливаются операции в стеке отмены операций (Ctrl+Z)
4) Может возникнуть ситуация, когда это накопление начинает тормозить работу с книгой.
5) Ваш совет: в целях очищения стека --> сохранить, закрыть и открыть книгу.

Если я вас правильно понял, у меня возникли вопросы:
1) А можно ли вместо Закрытия-Открытия книги просто командой на окне Immediate очистить стек? (это же быстрее откр/закрытия книги)
2) Или можно ли для очищения стека запустить любой  макрос - вроде запуск макроса тоже очищает стек? (быстрее откр/закрытия книги)
Изменено: Бахтиёр - 20.10.2021 16:41:05
 
Бахтиёр, это я Вас не совсем понял. :)  Для того, чтобы очистить стек Undo/Redo операций, относящихся к книге, макрос должен внести изменения в эту книгу. Можно, например, значение какой-нибудь ячейки присвоить само себе.
Изменено: sokol92 - 20.10.2021 16:34:11
Владимир
Страницы: 1 2 След.
Читают тему (гостей: 1)
Наверх