Какая причина долгих расчетов без предварительного сохранения или открытия параметров экселя, почему скорость расчетов зависит от просмотра параметров экселя
В файле используется формульный расчет по выбору пользователя (реализовано через макроф. ВЫЧИСЛИТЬ подставляется одна из нужных формул со списка) Часть формул используют пользовательские (UDF) функции, часть "обычные"
В файл (умная таблица) вставляется большой массив данных, потом пользователь выбирает что ему нужно дальше увидеть/посчитать.
Столкнулся со следующей проблемой: - первый расчет идет крайне доооолго. При этом если отменить (эскепт) - все что нужно - все равно будет корректно расчитано. - последующие расчеты идут условно-моментально. При этом если после вставки с буфера, перед запустом расчетов файл сохранить, или зайти в параметры экселя - все происходит моментально.
Даю ссылку на видео, на котором заснял 2 ситуации показаны: 1. открываю файл, вставляю в него данные, выбираю нужный расчет - и процесс выполнения после 30 с чем-то процентов уже особо не идет... Отменяю по эскейпту. Потом закрываю. 2 . открываю тот же файл заново. Вставляю данные. Просто открываю-закрываю параметры экселя, выбираю нужный расчет - все считается условно моментально. (вместо открытия параметров - можно сохранить файл, потом также расчет пройдет шустро, но если сохранение можно натянуть на теорию, то при чем тут открытие окна параметров?)
Все это относится к первому использованию формул содержащих UDF. Можно выбирать расчеты использующие "обычные" формулы, все будет быстро, а на первом выборе затрагивающем UDF будут тормоза. Любые последующие расчеты (второй, третий, сорок восьмой) будут быстрыми.
Протестировано на 2 рахных компах, т.е. не проблема рабочего места.
PS: Я не могу приложить полноценные примеры, на которых форумчане смогут "покрутить" проблему; т.к. объем вставляемых данных должен быть большой (уменьшение до размера запаковки под требование форума - не покажет проблему), + в исходниках персоналка... обрезать которую = уменьшить размер = невозможность показать проблему... Прикрепляю только "чистый" файл с десятком строчек почищенных от персоналки, чтоб можно было просто увидеть структуру что откуда и как, вдруг косяки гдето в нем уже... но связи с октрытием окна параметров вот не могу придумать.
но эта часть никак не связана с первым расчетом через выпадающий список и ВЫЧИСЛИТЬ и при условии что этот расчет первый раз касается UDF. Но вариант с явным указанием ячеек выше/ниже тоже был не быстрый в момент расчета, и потом в работе с файлом, скажем при сортировке/фильтрации. И как мне показалось, с ростом объема данных - СМЕЩ стало выигрывать... именно на летучести при сортировке/фильтрах. Но попробую завтра переписываться без него на работе, для себя скорость посмотреть в этой части. Но именно этого момент не особо критичен.
Но в любом случае проблема не отсюда. Удалил все левее Z (выпадающий список выбора пользователем что считать) Никаких смещений и допрасчетов кроме заложенного на ВЫІЧИСЛИТЬ - и все точно так же. Копипаста - первый расчет "сразу" связанный с любой UDFдолгий, все последующие моментальные. Копипаста - открыть/закрыть параметры - любые расчеты сразу быстрые.
Вот эта завязка на необходимость открыть параметры (или сохраниться после копипасты). Что это и откуда и как побороть то...
andylu написал: на первом выборе затрагивающем UDF будут тормоза
"Вычислить" это макрофункция работает как UDF, кроме того она в диспетчере имен, а там обычные функции работают как формулы массива. Как она точно работает не скажу, но предполагаю что работа происходит так: сработала макрофункция "вычисляй" в одной ячейке - следом включилась в работу UDF пробежалось по всем ячейкам где есть эта UDF, далее изменение во второй ячейке и вновь UDF включилось в работу и так далее до последней ячейки. И чем больше ячеек в таблице, тем дольше пересчёт. На больших объёмах наверно даже видно последовательность изменений данных в ячейках. Предполагаю если вместо использования "вычисляй", выбранные формулы вставлять в таблицу макросом и после этого производить расчёт, будет быстрее работать.
БМВ,я не знаю как по другому можно организовать возможность выбора из нескольких вариантов формул для одного столбца. Плодить доп.столбцы (каждой формуле свой) не вариант.
Можно конечно просто руцями в нужный момент вставлять нужный вариант формулы. Но вот решил сделать "красиво" выбирая из выпадающего списка.
gling, Кстати, попробую завтра, если убрать эту конструкцию, и просто руками копипастить нужную формулу, останется ли этот тормоз при первом расчете... Но не вижу логически, чем это может помочь. Почему тормоз только на первом расчете связанном с Любой udf, без тормоза с "обычными" функциями, с моментальным пересчетом в дальннйшем уже независимо есть udf или нет ..
о, в обсуждении всплывают почему то не очевидные для меня сразу идеи) Если первый тормоз исчезнет при ручной вставке, без конструкции с ВЫЧИСЛИТЬ, то вносить нужную формулу по выпадающему списку - можно пробовать и макросом по изменению таргета)
При первом расчёте вставляется формула UDF в первую ячейку-- происходит пересчёт одной UDF-- вставляется во вторую ячейку происходит пересчёт двух UDF.....-- вставляется предпоследняя формула происходит пересчёт на одну меньше чем все--вставляется последняя формула происходит пересчёт всех UDF. Это конечно дольше, ведь в последующих пересчётах UDF уже вставлены и выполняется только последний пересчёт из описанного выше.
gling,наверно продолжение своих экспериментов в этом ключе я начну с копания теории что связано с расчетами умных таблиц, что с расчетами udf... Из вашей логики - мне никак не понятно почему просчет да, большого количества ячеек, связанных с первой вызванной UDF, долгий расчет... UDF_1. Почему при выборе/использовании другой функции, UDF_2, и полной переписи всего столбца в это т момент - все происходит уже моментально.
Скрытый текст
А если бы не заметил что лечится открытием/закрытием параметров - вообще бы думал "ну да, ну тормозит, штош... Терпим и намекаем руководству на новое железо
Вы хотите сказать что если выбрать UDF_1 - происходит пересчёт долго, выбрать UDF_2 - происходит пересчёт быстро, вновь выбрать UDF_1 - происходит пересчёт быстро. Так? Одна и таже UDF работает по разному в зависимости от очереди выбора? Медленно работают все UDFки которые запускаются первыми после открытия файла? Для меня такое тоже загадка. Возможно это связано со способностями "Умных таблиц", у них есть своя способность вставлять формулы во весь столбец при изменении в одной ячейке, а здесь ещё накладывается "вычислить". Не знаю, возможно кому и нравится, но я стараюсь не использовать "умные таблицы" с макросами, да и с формулами тоже. На мой взгляд с обычными таблицами работать проще и удобнее.
gling написал: ра? Медленно работают все UDFки которые запускаются первыми после открытия файла?
близко) После открытия (файл уже наполнен), или сохранения (ctrl+s) любая отработает моментально. Проблема именно после вставки нового большого массива в умную таблицу и первом запуске формулы использующей udf. Потом тут же сразу пересчет на другую udf_2 моментальный, повторный пересчет первой, или формулы на "простых" функциях, или udf_3_4 (неважно что потом считать) также моментальный.
Цитата
gling написал: способность вставлять формулы во весь столбец при изменении в одной ячейке
Да) именно поэтому+формат формул под умные таблицы (без "обычных" адресов типа А1) я и затеял все это)
Но и непосредственно с начальным заполнением/формированием диапазонов умной таблицы после вставки данных тоже не совсем связь, наверно. Т.к. к моменту первого просчёта с udf - нужный столбец уже полностью наполнен (=считай там сразу есть возращающая #Н/Д)
vikttur,с обычными диапазонами сейчас и работаем. И вместо макрофункции вычислить - набор формул-заготовок в блокнотике вставлять по мере надобности в свободный столбец А это скорее тесты.
Скрытый текст
Почему тут именно умные таблицы и не взлетит на обычных диапазонах: Умные умеют автозаполняться, и формулы для умных можно написать без привычных адресов. Т.е условно =А1+В1 в обычном диапазоне мы протягиваем донизу. А2+В2, А-n+В-n. В умной он (в обычном виде) протянется сам. Точно так же А2+В2, А-n+В-n. Но подсунуть в макрофункции вычислить текст А1+В1 не выйдет. По каждой строке результат будет именно от А1+В1.А в варианте умных таблиц - скармливаем текст [@[имя_столбца_А]]+[@[имя_столбца_В]] и получаем нужный расчет на любой сроке диапазона.
Ну во всяком случае мне именно вариант структуры показался самым оптимальным...
andylu: Почему тут именно умные таблицы и не взлетит на обычных диапазонах
вы просто не умеете их готовить — один раз разобраться и можно очень хорошо эмулировать умные таблицы. Формулы и так уже самопротягиваются, кстати…
К слову, я-то продолжаю использовать умные, но формулы в них построчные, без ссылки на диапазоны более одной ячейки и тем более - в другие листы Да и фильтр всегда сброшен после того, как используется - это единственная очевидная гарантия отсутствия скрытых фильтром строк
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
в итоге - нет. спойлер - глюка именно в связке макрофункции ВЫЧИСЛИТЬ и первом использовании UDF после наполнения данных умной таблицы. Почему именно первом - так и не локализовал.
Что сделано в процессе поиска проблемы и результат:
Скрытый текст
- вырезано все что не касается расчета через "красивости" выпадающего списка, максимально сокращено количество столбцов, расчетов, короче всего что не касается непосредственно полей для расчета по UDF; - на в среднем 2 порядка (да, именно "порядка", в сравнении с обычным объемом) сокращен объем исходных данных (и теперь могу дать "полноценный" пример) - и... Сюрприз. Все так же тормозит при первом использовании UDF. - UDF вполне быстро летают при заполнении формул прямо в столбце, не через макрофункцию - т.е. имеем 100% связь с макро ВЫЧИСЛИТЬ - расчеты подставляемые через ВЫЧИСЛИТЬ не касающиеся UDF тоже отрабатываются моментально - т.е. имеем 100% связь с использованием UDF - первый вызов UDF летает если после наполнения таблицы файл закрыть/открыть, или просто сохранить изменения после копи-пасты - т.е. имеем 99% связь с действиями сразу после наполнения данных "умной" таблицы
Как проявить проблему на приложенных файлах (если кому будет интересно)
Скрытый текст
Проблема проявится: Из файла "исходник" вставляем массив данных (копи-паста руками) в файл "для планеты - первый расчет UDF тормоз", и сразу (ничего не делая другого) в ячейке F1 выбираем в выпадающем любой вариант помеченный как UDF. = получаем тормоз вычисления. Дождавшись вычисления, или не дождавшись и отменив (пару кликов по экскейку) - в последующем можно выбирать любую формулу как с UDF так и с обычных - будет отработано быстро.
Проблема остается в т.ч. если для первого расчета выбрать формулу/ы из "обычных", она/и отрабатываются быстро, а при первом обращении к UDF опять тормоз.
Проблема НЕ проявится: После копи-пасты из "исходник" в "для планеты - первый расчет UDF тормоз "- и перед первый расчетом UDF выполнить любое из следующего: - сохранить изменения в файле - открыть окно параметров экселя - вписать руками что-либо в область за пределами умной таблицы - удалить что-либо на листе - список не исчерпывающий, и вычислять на каких еще не связанных логически операциях проходит глюк не стал.
Т.е. после первичного заполнения умной таблицы сделать какие-либо действия, не связанные непосредственно с областью умной таблицы
Решение пришло не в логику и через костыль:
Скрытый текст
Если проблема исчезает после каких-либо действий - заложим эти действия в логику. В примерах в файле "для планеты - решение через костыль ": - при изменении А3 (начало данных умной таблицы, будет заполнено гарантированно) - удаляем последнюю строку в листе (будет пустой гарантированно)
Код
Private Sub Worksheet_Change(ByVal Target As Range)
Set KeyCells = Range("A3")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Rows(1048576).Delete
End If
End Sub
Все) В чем логика (как возникновения проблемы, так и вариантов решения) - так и не понял
У меня эффект, описанный в #20, возникает только в случае, если значение ячейки F1 меняется в режиме "Копирования" (Application.CutCopyMode=xlCopy). Если после копирования диапазона ячеек нажать на ESC, то пересчет формул производится без замедления. Не думаю, что причина в конкретных формулах. Вероятно, выбор ячейки из списка в режиме копирования ячеек и является источником проблемы.
andylu: Какая причина долгих расчетов без предварительного сохранения
подумал не лишним будет упомянуть, что … При наличии большого объёма данных и совершений операций с ними на скорость работы начинает заметно влиять история изменений, сохраняемая Excel для "отката" При сохранении книги, закрытия и открытия скорость работы может сильно возрасти
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Здравствуйте, Михаил! А их и нет (по крайней мере у меня, Excel 2016), если перед выбором ячейки (F1) нажать Esc (см. #22). Но, разумеется, отказ от "допотопной" макрофункции более чем оправдан.
sokol92 написал: А их и нет (по крайней мере у меня, Excel 2016)
Владимир, добрый день. Ну тут два варианта, или версия или руки не могут воспроизвести то, что удается другому. Я даже не проверял просто сразу за напильник.
Jack Famous, я так понял, что ваш пост №23 касается ситуации, когда: 1) Имееется книга с большим объёмом данных и с большим количеством формул. 2) Открыли книгу и работаем с ней. 3) В процессе работы накапливаются операции в стеке отмены операций (Ctrl+Z) 4) Может возникнуть ситуация, когда это накопление начинает тормозить работу с книгой. 5) Ваш совет: в целях очищения стека --> сохранить, закрыть и открыть книгу.
Если я вас правильно понял, у меня возникли вопросы: 1) А можно ли вместо Закрытия-Открытия книги просто командой на окне Immediate очистить стек? (это же быстрее откр/закрытия книги) 2) Или можно ли для очищения стека запустить любой макрос - вроде запуск макроса тоже очищает стек? (быстрее откр/закрытия книги)
Бахтиёр, это я Вас не совсем понял. Для того, чтобы очистить стек Undo/Redo операций, относящихся к книге, макрос должен внести изменения в эту книгу. Можно, например, значение какой-нибудь ячейки присвоить само себе.