Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Долгий пересчет при ручном изменении, и быстрый при программном
 
#1
03.09.2022 11:07:03
Доброго дня. К сути. Берем чистый лист, простую функцию, допустим
Код
Function tst(a)
    tst = a
End Function
Заполняем ей 15000 ячеек в 1 столбце(=tst(A1+1)). При вводе в первую ячейку происходит пересчет всех остальных. При ручном измненеии происходит значительная пауза, если менять программно, пересчет проихсодит очень быстро. Собственно говоря, и дело видимо не в пересчете, как сути, вопро в чем? Если кто-то решит проверить, не советую делать более 20 тыс. ячеек, можно словить мертвецкий зависон, даже Ctrl+Shift+Del не помогает
Изменено: testuser - 03.09.2022 11:09:21
 
 
 
#2
03.09.2022 13:37:18
у меня быстро, 20.000 строк с функцией, которую вы написали - секунд 5. См. файл. Файл с 20.000 строк 400кб, это больше, чем разрешено выкладывать, поэтому оставил 10 строк - продлите до 20.000 сами
Изменено: New - 03.09.2022 13:38:11
 
 
 
#3
03.09.2022 15:30:44
Цитата
написал:
у меня быстро
20 тыс. указал, из гуманных целей, сам я словил зависон на 100 к. 5 сек. это долго, если поменять значение из vba -  0,296875 для 30000, у вас может быть меньше.
Изменено: testuser - 03.09.2022 15:31:44
 
 
 
#4
03.09.2022 20:06:30
testuser, вызов UDF всегда более затратен по ресурсам, и чем больше тем растет проблема, как снежный ком. Могу предположить что зависимость для пересчета строится тоже с определенными ограничениями по объему, что может также влиять на пересчет после определенного количества зависимых ячеек на листе и где-то может быть резкое снижение производительности.

но все равно напрашивается вопрос, о той тайной связи файла с примером и тем что в тексте сообщения!
Изменено: БМВ - 03.09.2022 20:26:26
По вопросам из тем форума, личку не читаю.
 
 
 
#5
04.09.2022 03:33:27
Цитата
написал:
где-то может быть резкое снижение производительности.
Вот это "где-то" оно как-то связано с механизмом заполнения ячейки " в ручную". Пишу значение (или ctrl + V) нажимаю Enter и,, происходит что-то страшное..
Ели сделать так Range("A2") = 2 мгновенный пересчет (200 тыс. за 3 сек.) с отображением результатов!

Цитата
написал:
той тайной связи файла с примером и тем что в тексте сообщения!
Даже не догадываюсь о чем речь.
Изменено: testuser - 04.09.2022 03:33:49
 
 
 
#6
04.09.2022 04:41:06
Это со всеми Excel файлами у вас так или с каким-то одним?
P.s. в вашем файле сплошной мусор.  В тексте сообщения у вас одна функция, в файле куча непонятных и ещё в разных модулях. Скачайте свой файл из этой темы и посмотрите код и код в модулях листов и книги. В следующий раз поменьше мусора добавляйте в свой файл-пример
Изменено: New - 04.09.2022 04:43:27
 
 
 
#7
04.09.2022 05:14:22
В файле 1 лист с закомментированными событиями, 1 модуль с несколькими процедурками и 2 функции. Вторая функция, которая используется также простейшая, разницы в скорости между ними особой нет, поэтому оставил ее в примере. Критику не засчитываю)
Код
Public oldvals As New Dictionary

Function tst(a, id)        
   If a <> oldvals(id) Then        
    oldvals(id) = a
   End If
   tst = a
End Function
Изменено: testuser - 04.09.2022 05:16:04
 
 
 
#8
04.09.2022 07:11:08
На что бы еще хоте обратить внимание, этот адский пересчет происходит не только при изменении связанной с функциями ячейки, а любой. Говоря о практической части сего разговора можно сказать так, если у вас (гипотетических читателей сего топика) случайно окажется несколько десятков тысяч связанных vba-шных udf-ов (возможно и со стандартными также справедливо) в одной книге, то любое изменение вновь открытого файла неизежно будет приводить к ужасному и мерзкому зависанию. В то же время если вносить изменение  макросом, такого эффекта не происходит, хотя пересчет меж тем работает. Какое решение могло бы помочь в данной ситуации - перехват любых изменений на листе макросом. Допустим, выделяем ячейку и в ней образуется поле для ввода (кокой-нибудь текст-бокс или элемент списка допустим) вводим значение в него а дальше макрос переправляет его в ячейку.
Изменено: testuser - 04.09.2022 07:26:13
 
 
 
#9
04.09.2022 10:19:24
Цитата
testuser написал:
Критику не засчитываю
Критика заключается в том что с трудом понятен кейс. У Вас что-то крутится в голове, но другие об этом не знают.

И так действительно при множестве вызовов UDF зависимых от результата их выполнения при смене родительского значения, при этом тоже происходит и при "протягивании", при этом именно руками
Код
    Range("A2").AutoFill Destination:=Range("A2:A15001")
происходит мгновенно,
. Пересчет выполняется тоже время, чего не происходит если типа вставить формулу. Причины скорее всего в механизмах пересчета, которые скрыты от пользователя.
По вопросам из тем форума, личку не читаю.
 
 
 
#10
04.09.2022 17:37:00
Интересная тема, спасибо автору и коллегам!
У меня для 100 000 строк при ручном внесении значения ячейки тоже пересчет занимает существенное время. Если убрать птичку с параметра Excel Дополнительно / Формулы / Включить многопоточные вычисления (или
Код
Application.MultiThreadedCalculation.Enabled = False

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

Данный пример является "экстремальным", поскольку имеет длинную цепочку (граф) вычисления ячеек (см. \xl\calcChain.xml в структуре файла .xlsm).
Владимир
 
 
 
#11
04.09.2022 18:04:48
sokol92,  Владимир, я всегда говорил, что чем больше потоков, тем больще ресурса тратится на то, чтоб распределить по ним, компенсируя этим ускорения от многопоточности, но чтоб замедляло ......   :D
По вопросам из тем форума, личку не читаю.
 
 
 
#12
04.09.2022 18:06:49
Цитата
БМВ написал:
но чтоб замедляло
Здравствуйте, Михаил, не зря разработчик предусмотрел параметр для отключения многопоточности.  :)
Владимир
 
 
 
#13
04.09.2022 18:09:58
sokol92, Приветствую. то есть разработчик сознательно признал что они конкретно что-то упустили и упустили давно и надолго? Мдаааа. Слова заканчиваются.
По вопросам из тем форума, личку не читаю.
 
 
 
#14
04.09.2022 18:17:31
Многопоточность вычислений - одна из самых сложных тем в программировании. В данном конкретном случае сразу "видно", что дерево зависимости формул состоит из одной длинной цепи и "лишние" процессоры будут только вредить (это не Ваше "фирменное" произведение матриц, в котором можно независимо умножать каждую строку на каждый столбец).
Владимир
 
 
 
#15
04.09.2022 18:21:25
sokol92, Таким образом мы выходим на то что многопоточность отключается на время выполнения макроса, так как те же изменения сделанные кодом, а не руками не приводят к таким тормозам. Ну к несчастью могу только взгрустнуть, если это так. Типа одна не успевает за другим. Узнаю даже свою работу. Там та же фигня.
По вопросам из тем форума, личку не читаю.
 
 
 
#16
04.09.2022 18:53:23
Если мы в вышеуказанном примере заменим UDF-функцию на, к примеру, ABS, то многопоточность не мешает (и не помогает).
Нашел у разработчика соответствующие рекомендации:

Избегайте однопоточных функций.
Большинство "родных" функций Excel хорошо работают с многопоточными вычислениями. Однако по возможности избегайте использования следующих однопоточных функций:
Пользовательские функции VBA и автоматизации (UDF), но UDF на основе XLL могут быть многопоточными.
...

Иными словами, если Вы используете UDF-функции вместе с многопоточными вычислениями, то, как говорится, вас предупреждали...
Изменено: sokol92 - 04.09.2022 18:58:18
Владимир
 
 
 
#17
04.09.2022 19:00:56
sokol92, Владимир, думаю что эти рекомендации относятся к тому что многопоточные по скорости выигрывают, но это не объясняет, почему при многопоточности приложение просто "блуждает в трех соснах".
По вопросам из тем форума, личку не читаю.
 
 
 
#18
05.09.2022 09:52:03
Цитата
написал:
Application.MultiThreadedCalculation.Enabled = False
В моем случае не помогает, т.е. нет заметной разницы. Там еть еще "многопотоковая обработка", тоже не влияет.
Цитата
Если мы в вышеуказанном примере заменим UDF-функцию на, к примеру, ABS, то многопоточность не мешает (и не помогает).Нашел у разработчика соответствующие  рекомендации :Избегайте однопоточных функций.Большинство "родных" функций Excel хорошо работают с многопоточными вычислениями. Однако по возможности избегайте использования следующих однопоточных функций:
Пользовательские функции VBA и автоматизации (UDF), но UDF на основе XLL могут быть многопоточными.
Спасибо, познавательно. Аналогично, =СУММ(A1; 1) работатет работает "штатно"

Цитата
написал:
фирменное" произведение матриц, в котором можно независимо умножать каждую строку на каждый столбец
Это о чем и где? Интересно посмотреть.
Изменено: testuser - 05.09.2022 15:02:00
 
 
 
#19
06.09.2022 22:57:44
Всем привет!
На моем компьютере нет зависимости тормозов от многопоточности.
Совет: проверьте тормоза с открытым и закрытым окном VBE  ;)

Код для тестирование в новой книге:
Код
Option Explicit

Sub TestingData()
  Range("A1").Value = 1
  Range("A2:A100000").Formula = "=x(A1)"
End Sub

Function x(a)
  x = a + 1
End Function
Изменено: ZVI - 07.09.2022 07:57:33
 
 
 
#20
07.09.2022 00:03:31
ZVI, Добрый день, Владимир. Потестировал у себя. Разницы нет, всё быстро. Но где-то пару недель назад у меня была ситуация, что код при открытом редакторе VBE выполнялся очень долго, а с закрытым быстро. Я удивился. Но так и не понял, почему так происходит
 
 
 
#21
07.09.2022 00:17:02
Цитата
New написал: Потестировал у себя.
Спасибо (Павел?), у меня разница приличная.
Можно ещё сохранить файл, закрыть Excel, в затем открыть в Excel и проверить, не открывая VBE.
Но, похоже, что эффект зависит от железа.
 
 
 
#22
07.09.2022 00:27:30
ZVI, Потестировал в новом открытом файле с закрытым VBE и открытым, результат в среднем 0,2900391. Потом сохранил книгу, закрыл, открыл, так же запускал с закрытом VBE и открытым. Плюс/минус те же 0,2900391 (иногда 0,28)
P.S. отличается только самый первый запуск с закрытым или открытым VBE не играет роли - 0,22. Второй и последующие запуски снова 0,29. У меня ноутбук, Excel 2021 (64bit)
Изменено: New - 07.09.2022 15:40:08
 
 
 
#23
07.09.2022 00:38:36
Павел, спасибо!
Интересно, знают ли в MS про такие загадки? Вопрос риторический, конечно ))
 
 
 
#24
07.09.2022 02:52:37
ZVI, совет и вправду работает! 100 к. функций теперь можно заряжать на на лист со спокойной душой )
Цитата
написал:
любое изменение вновь открытого файла неизежно будет приводить к ужасному и мерзкому зависанию.
Утверждение было недостоверным
Изменено: testuser - 07.09.2022 02:53:06
 
 
 
#25
07.09.2022 14:43:14
Добрый день, Владимир, Павел, коллеги!
В теме речь идет о двух разных экспериментах.

А. Занесение формулы, содержащей ссылку на пользовательскую функцию, в ячейки A2:A100000 (сообщение #19).

Б. Стартуем Excel, открываем книгу, ранее созданную  и сохраненную в эксперименте A. Меняем ячейку A1 вручную и макросом и сравниваем время перерасчета листа (сообщение #10).

Перепроверил результаты эксперимента Б. Владимир прав, результат зависит от того, открыто ли окно VBE (многопоточность реабилитирована). Приношу извинения на невольную дезинформацию.
Разница по времени, если заносить значение A1 через интерфейс пользователя: при открытом окне VBE перерасчет занимает 65 секунд, при закрытом - менее 2 секунд.
Макрос по изменению значения ячейки A1 выполняется за доли секунды, независимо от того, открыто ли окно VBE.
Этот эффект, похоже, касается только формул с использованием UDF-функций.
Изменено: sokol92 - 07.09.2022 14:52:04
Владимир
 
 
 
#26
10.09.2022 16:19:32
При ручной замене, пересчет идет в 15 раз дольше, чем программной, но при открытом окне VBA-редактора добавляется затуп, между пересчетом и событием Calculate, который еще удваивает время выполнения.
Номер   ф-цииПрограммная замена значенияРучная замена 1Ручная замена 22/13/13/2
20000,0156250,2343750,2812515181,2
40000,031250,4531250,51562514,516,51,137931
60000,0468750,68750,7514,6666667161,090909
80000,06250,90625114,5161,103448
100000,0781251,1251,23437514,415,81,097222
120000,093751,343751,4687514,333333315,6666671,093023
140000,1093751,5781251,70312514,428571415,5714291,079208
160000,1251,7968751,95312514,37515,6251,086957
180000,1406252,0156252,187514,333333315,5555561,085271
200000,156252,252,42187514,415,51,076389
WS Calculate0,156252,343756,281251540,22,68
Скрытый текст
Изменено: testuser - 10.09.2022 16:37:10
 
 
 
#27
11.09.2022 16:39:25
Кстати, первый вариант (с программной заменой) даже сопоставим по времени с простым перебором того же диапазона  в цилке с последовательным вызовом udf.
2000  0,015625
4000  0,03125
6000  0,046875
8000  0,0625
10000  0,078125
12000  0,09375
14000  0,109375
16000  0,125
18000  0,140625
20000  0,15625
Скрытый текст
 
 
 
Страницы: 1
Наверх