Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Измерение скорости работы формулы.
 
#1
31.07.2009 19:56:11
Переодически сталкиваюсь с относительно точным измерением скорости пересчёта на этом форуме.  Стало интересно как именно это делается...может кто-нибудь поделиться своими вариантами пожалуйста?
 
 
 
#2
31.07.2009 20:23:48
см. надстройку RangeCalc.zip здесь:    
 
http://www.decisionmodels.com/downloads.htm  
 
Очень важно: перед проведением замеров, обязательно менять режим пересчета на ручной, иначе данные будут искаженными!  
 
Вот тут можно почитать про особенности мотора расчетов Excel:    
http://www.decisionmodels.com/calcsecrets.htm  
http://www.decisionmodels.com/optspeed.htm  
http://www.decisionmodels.com/memlimits.htm  
 
Внимание! На указанном сайте тонны информации, но навигация малоинтуитивна. Нужно обращать внимание на наличие ссылок в верхней части страницы и полосы прокрутки в правой ее части.  
 
Есть еще менее точный способ:  
 
Sub Test()  
t=Timer  
Range("A1:B2").Calculate  
MsgBox (Timer - t)*1000 & "msec"  
End Sub
KL
 
 
 
#3
31.07.2009 20:59:46
Спасибо.  
*пошёл изучать*
 
 
 
#4
31.07.2009 21:35:08
{quote}{login=KL}{date=31.07.2009 08:23}{thema=}{post}  
Есть еще менее точный способ:  
{/post}{/quote}Я пока надстройку не смотрел, но что в ней такого, что этот способ "еще менее точный"?  
Я им или его аналогом измерял и не сомневался.  
Правда, я дапазон всегда побольше брал, а если его не хватало, то в цикл.
Bite my shiny metal ass!      
 
 
 
#5
31.07.2009 21:38:27
{quote}{login=Лузер™}{date=31.07.2009 09:35}{thema=Re: }{post}Я пока надстройку не смотрел, но что в ней такого, что этот способ "еще менее точный"?  
Я им или его аналогом измерял и не сомневался.  
Правда, я дапазон всегда побольше брал, а если его не хватало, то в цикл.{/post}{/quote} Так вы посмотрите :-) В описании надстройки об этом сказано.
KL
 
 
 
#6
31.07.2009 21:47:30
KL, Вы в каком часовом поясе?  
Не знаю как Вам, а мне трудно изучать в пятницу вечером, после пива (много) новую надстройку.  
Но посмотрю.
Bite my shiny metal ass!      
 
 
 
#7
31.07.2009 21:51:57
Лузер, открою страшную тайну: KL в Мадриде сидит по кондиционером и хлещет холодное пиво :-) Но у него тоже... пятница.
 
 
 
#8
31.07.2009 22:05:10
:-D
KL
 
 
 
#9
31.07.2009 22:09:27
В Мадриде явно не так много пятницы как в Москве :-)  
Но посмотрел.  
Основная идея понятна.  
Непонятно другое. Почему COPYRIGHT © DECISION MODELS LIMITED не могли комменты на русском сделать?  
Ну хотя бы ради пятницы?
Bite my shiny metal ass!      
 
 
 
#10
31.07.2009 22:30:07
Надстройка хороша и плоха одновременно.  
Хороша тем, что покажет сколько в реальности будет пересчитываться диапазон вместе с влияющими ячейками.  
Плоха (а скорее мне не нужна) тем, что мне не нужно это знать. Мне нужно знать на сколько формула А быстрее формулы Б.  
В коде  
Sub Test()  
t=Timer  
Range("A1:B2").Calculate  
MsgBox (Timer - t)*1000 & "msec"  
End Sub  
не используется high-resolution timer, зато его точность всегда можно нивелировать так:  
Sub Test()  
t=Timer  
for i=1 to N  
Range("A1:B" & M).Calculate  
next  
MsgBox (Timer - t)*1000 & "msec"  
End Sub  
 
При достаточно больших N и M без разницы high или не high-resolution timer используется.  
Главное не переборщить с N и M :)
Bite my shiny metal ass!      
 
 
 
#11
01.08.2009 13:05:26
во-во..  
 
к тому же в действия excel часто вмешиваются разные службы..  
 
в любом случае лучше взять за большее время
Живи и дай жить..
 
 
 
#12
01.08.2009 15:19:01
Возможно я опять что-то путаю или у меня дежавю, но со слэном мы уже имели подобную дискуссию на sql.ru :-)  
А насчет "Мне нужно знать насколько формула А быстрее формулы Б" - это действительно так на начальном этапе познания свойств формул. Я например использую сравнение формул только для форумов. Мне лично, почти никогда не нужно замерять скорости формул для сравнения, я в большинстве случаев по их виду определяю какая быстрее. Ошибаюсь редко :-). Часто, когда задача состоит в ускорении работы файла, абсолютная скорость пересчета сразу дает мне представление о том, насколько она возрастет после оптимизации, арифметика почти всегда работает, не смотря на вмешательство разных служб. Ну и наиболее часто - напишешь формулу для диапазона, скажем в 1000 ячеек, измеришь скорость и выйдет 150 мсек. Сразу понимаешь, что нужно писать другую. И тому подобное :-). Кстати масштабирование замеров скорости с помощью цикла еще сильнее искажает результат и неверно влияет на конечное соотношение скоростей формул ;-)
KL
 
 
 
#13
03.08.2009 10:47:12
На sql.ru не зарегистрирован. Если такая дискуссия имело место, был бы благодарен за ссылку.  
"Я например использую сравнение формул только для форумов"  
Право сказать, поленился дописать про сравнение скорости только для форума.  
Чисто академический интерес.  
У меня практически нет "тяжелых" расчетов. В одном месте, где потребовалась куча ВПР()ов, а исходные данные для расчета подгружаются макросом, я этим же макросом вставляю формулы, пересчитываю, заменяю значениями.    
Эксель без формул легче и быстрее :)  
"Кстати масштабирование замеров скорости с помощью цикла еще сильнее искажает результат и неверно влияет на конечное соотношение скоростей формул ;-)" - Хотелось бы услышать обоснование, если не сильно затруднит.
Bite my shiny metal ass!      
 
 
 
#14
03.08.2009 12:05:31
{quote}{login=Лузер™}{date=03.08.2009 10:47}{thema=}{post}На sql.ru не зарегистрирован. Если такая дискуссия имело место, был бы благодарен за ссылку.  
"Я например использую сравнение формул только для форумов"  
Право сказать, поленился дописать про сравнение скорости только для форума.  
Чисто академический интерес.  
У меня практически нет "тяжелых" расчетов. В одном месте, где потребовалась куча ВПР()ов, а исходные данные для расчета подгружаются макросом, я этим же макросом вставляю формулы, пересчитываю, заменяю значениями.    
Эксель без формул легче и быстрее :)  
"Кстати масштабирование замеров скорости с помощью цикла еще сильнее искажает результат и неверно влияет на конечное соотношение скоростей формул ;-)" - Хотелось бы услышать обоснование, если не сильно затруднит.{/post}{/quote}  
Ссылку искать долго и лень, если честно, - это было так к слову. Насчет обоснования - оно простое: для пересчетов Excel использует механизм SmartCalc. Какие-то вещи он считает от раза к разу, а какие-то - только лишь когда это необходимо. Например, можно поймать формулу в тот момент, когда пересчет ее займет вдвое больше времени, чем все последующие разы - это тот момент, когда Excel перестраивает все зависимости. Формулы, построенные по принципу =ЕСЛИ(условие, ПростоеВыражение, СложноеВыражение) затрачивают различное время на пересчет в зависимости от выполнения или нет условия. Если руководствоваться промером скорости одной инстанции этой формулы, можно прийти к крайне искаженным результатам. Есть еще случаи, когда наличие более одной инстанции формулы не ведет к увеличение времени в прямой пропорции, например формула массива введенная сразу в несколько ячеек, и т.д. Подробнее здесь: http://www.decisionmodels.com/calcsecrets.htm
KL
 
 
 
#15
03.08.2009 12:43:27
Такой пример:  
 
- Заполните [A1:A1000] серией из месяцев в 3 символа
- Заполните [B1:B1000] произвольными числовыми значениями
- Заполните [C1:C1000] формулой =СУММЕСЛИ($A$1:$A$1000;"jan";$B$1:$B$1000)
 
теперь:  
 
- Измерьте время пересчета ячейки [C1]
- Умножьте время пересчета ячейки [C1] на 1000
- Измерьте время пересчета ячеек [C1:C1000] одним ударом
 
Sub Test1()  
   t = Timer  
   Range("C1:C1000").Calculate  
   MsgBox (Timer - t) * 1000 & "msec"  
End Sub  
 
- Измерьте время пересчета ячеек [C1:C1000] циклом
 
Sub Test2()  
   t = Timer  
   For i = 1 To 1000  
       Range("C" & 1).Calculate  
   Next  
   MsgBox (Timer - t) * 1000 & "msec"  
End Sub  
 
Как говорил Владимир Николаевич из к/ф "Кин-дза-дза: "А ты, что видишь?"
KL
 
 
 
#16
03.08.2009 13:55:51
{quote}{login=Лузер™}{date=03.08.2009 10:47}{thema=}{post}  
Эксель без формул легче и быстрее :)...{/post}{/quote}  
Перефразируя, Эксель без формул - нафиг не нужен  
- Как контейнер для информации, текстовые файлы и таблицы СУБД  намного практичнее  
- Как фронт-енд он ни в какое сравнение не идет ни с какой СУБД  
- Визульное оформление таблиц и графиков не хуже в Word и PowerPoint  
- Для функций простого калькулятора - это слишком дорогой продукт, да и открывать дольше, чем считать.  
- Макросы на VBA пользователь может писать из под любого другого приложения Office, да и batch-files с VbScript тоже не "rocket science"  
:)
KL
 
 
 
#17
03.08.2009 15:48:48
{quote}{login=KL}{date=03.08.2009 12:43}{thema=}{post}Такой пример:  
...  
Sub Test2()  
   t = Timer  
   For i = 1 To 1000  
       Range("C" & 1).Calculate  
   Next  
   MsgBox (Timer - t) * 1000 & "msec"  
End Sub  
...  
{/post}{/quote}  
Кирилл, здесь немного о другом цикле говорилось, при котором достаточно  точности Timer:  
 
Sub Test3()  
 Dim i&, j&, t!  
 Const N& = 10000  
 With Range("C1:C1000")  
   j = .Cells.Count  
   t = Timer  
   For i = 1 To N  
     .Calculate  
   Next  
   t = Timer - t  
 End With  
 If t = 0 Then t = 0.001  
 MsgBox Format(t, "0.000") & " sec; " & Format(N * j / t, "# ### ##0") & " cell/sec"  
End Sub
 
 
 
#18
03.08.2009 16:56:06
ZVI (Владимир?),  
 
В вашем примере нет принципиальной разницы для моей аргументации. Погрешность между надстройкой Чарльза Уильямса и таймером в макросе как правило небольшая (серьезные погрешности известны и носят локальный характер). Т.ч. я понимаю, что чаще всего такая точность не является необходимой. Я же говорю о масштабировании времени с помощью цикла. Суть моего довода в том, что пересчет большого кол-ва инстанций формулы разом в большом диапазоне и пересчет меньшего кол-ва инстанций n-раз далеко не одно и то же. Каждая формула имеет свой индивидуальный "overhead" (обязательно затрачиваемое время), который при одновременном пересчете нескольких инстанций одной формулы не повторяется для них для всех. Что-то вроде платы за установление звонка с последующей поминутной оплатой. В зависимости от сложности расчетов, этот "overhead" может разниться как в абсолютном так и в процентном отношении к переменному времени и к такому же "overhead" других формул. В вашем примере, вы устранили дублирование "overhead" для 1000 ячеек, а затем помножили тот единственный на "10000". Т.е. шаг вперед, десять шагов назад. При определенном (весьма вероятном) стечении обстоятельств, погрешность таких расчетов может быть весьма ощутимой как в абсолютном значении так и в сравнении с другой формулой.
KL
 
 
 
#19
03.08.2009 17:19:55
Кирилл, с Вами никто ведь и не спорит :-), просто я уточнил то, что имелось в виду под использованием Timer: цикл не для пересчета каждой ячейки диапазона в отдельности, а цикл пересчета всего достаточно большого диапазона ячеек, это ведь две большие разницы.  
 
Ничего не имею против API функций более прецизионного таймера и часто использую их. У каждого метода свои погрешности: операционные системы на базе NT (XP, Vista, Windows7) не являются системами реального времени, поэтому на коротком промежутке времени при вызове API – функций проявляется погрешность многозадачности операционки,  а при использовании Timer требуется приличное время для усреднения результата, чтобы результат стал стабильным.  
 
Чаще всего требуется относительная оценка быстродействия нескольких версий кода или формул, поэтому не очень то и важно, какой таймер использовать если использовать один и тот же таймер.    
Но важно учитывать особенности этих таймеров и если их сравнивать, то в сопоставимых условиях. Впрочем, в этом вопросе у нас здесь - полный консенсус :-)
 
 
 
#20
03.08.2009 19:04:52
Согласен :-)
KL
 
 
 
#21
10.08.2009 10:00:44
Можно и я к вам?  
http://www.planetaexcel.ru/forum.php?thread_id=9116  
Здесь список летучих формул в сообщении KL.  
http://www.planetaexcel.ru/forum.php?thread_id=7460  
Здесь Pavel55 приводил более расширенный список летучих формул. Тут же ZVI упоминал о летучести условного форматирования.  
 
Есть ли еще функции или опции XL, которые не встречаются в этих темах, но исподтишка пакостят, тормозя расчеты?  
Можно ли с помощью каких-нибудь хитростей "обмануть" летучесть (кроме замены на нелетучие и отключение автопересчета)?
 
 
 
#22
10.08.2009 10:02:58
Не верь глазам своим - в предыдущем посте "формул" читать как "функций" :)
 
 
 
#23
10.08.2009 11:40:50
В Excel, начиная с версии 2002, добавлен пересчет формул при использовании автофильтра. Это может быть причиной тормознутости, если использованы медленные формулы обработки массивов (ВПР и т.п.).    
 
Приложенный пример иллюстрирует медленную работу автофильтра на листе "Формулы", а на листе "Макрос" приведен пример эффективной VBA-альтернативы.  
 
Но в общем случае, советую все же исходить из здравого смысла: не стоит тратить своё время на длительную возню с быстродействием, и тем более не делать это самоцелью.  
Если разница во времени работы двух каких-то вариантов незначительна (обрабатывается небольшое количество данных, например), то лучше выбрать наиболее понятный и комфортный для себя вариант.
 
 
 
#24
10.08.2009 11:52:35
{quote}{login=ZVI}{date=10.08.2009 11:40}{thema=Автофильтр вызывает пересчет формул}{post}не стоит тратить своё время на длительную возню с быстродействием, и тем более не делать это самоцелью.{/post}{/quote}  
Двумя руками "за".  
Но знать (на всякий случай) и использовать эти знания при подходящем случае.
 
 
 
#25
10.08.2009 12:39:23
На абсолютную летучесть можно, кстати, нарваться, и не используя летучих функций и других явно летучих действий, а просто превысив лимиты зависимостей :-)  
http://www.decisionmodels.com/calcsecretsf.htm  
 
Думаю, что если не тратить свое время на возню с быстродействием, то, когда быстродействие понадобится, врядли будешь в состоянии диагносцировать проблему ;-)
KL
 
 
 
#26
10.08.2009 12:42:05
Вообще, летучесть сама по себе не влияет на быстродействие. Она лишь усугубляет проблему если формула медленная.
KL
 
 
 
#27
10.08.2009 12:54:10
{quote}{login=KL}{date=10.08.2009 12:39}{thema=}{post}...Думаю, что если не тратить свое время на возню с быстродействием, то, когда быстродействие понадобится, врядли будешь в состоянии диагносцировать проблему ;-){/post}{/quote}  
Это верно, но у меня-то было про "длительную", причём это в порядке самокритики :-)
 
 
 
#28
11.08.2009 10:22:09
Ув. ZVI, можно ли ваш пример переделать для совмещения двух списков, используя 1 столбец как опорный?
 
 
 
#29
11.08.2009 12:13:22
{quote}{login=NIKOASCS}{date=11.08.2009 10:22}{thema=Вопрос ZVI}{post}Ув. ZVI, можно ли ваш пример переделать для совмещения двух списков, используя 1 столбец как опорный?{/post}{/quote}  
Переделаю, конечно, но приложите пример с несколькими строчками Ваших данных с формул.
 
 
 
#30
11.08.2009 13:26:15
Пробуем
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх