Использование таймера для сравнения производительности формул. Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.


Использование таймера для сравнения производительности формул
Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: два гуру Excel заходят в бар. Один из них говорит, что быстрее работает формула =МАКС(0;МИН(А2;В2)), а другой – что =МЕДИАНА(0;А2;В2). Кто из них прав?
Решение: вы можете найти ответ путем использования макроса VBA для вычисления каждой формулы 50 000 раз. Прежде чем стартует макрос, вы сохраняете значение таймера в переменную. Когда 50 000 расчетов завершится, можно сравнить исходное и конечное значения таймера.
На ПК под управлением Windows, таймер показывает количество секунд и долей секунды, прошедших с полуночи. На Mac, функция возвращает только целые секунды, так что процесс вычислений нужно повторить достаточное количество раз, чтобы увидеть разницу в секундах. Также, убедитесь, что во время работы макроса не начался новый день))
Следующий код сравнивает формулы МАКС(МИН()) и МЕДИАНА() при расчете 60 000 значений:
Sub TestMinMaxVsMedian()
Application.ScreenUpdating = False
Range("A1:B65000").Formula = "=Randbetween(-1000,1000)"
Range("A1:B65000").Value = Range("A1:B50000").Value
StartTime = Timer
Range("C1:C65000").FormulaR1C1 = "=MAX(0,MIN(RC1,RC2))"
Application.Calculate
Range("C1:C65000").Value = Range("C1:C65000").Value
ElapsedTime1 = Timer - StartTime
StartTime = Timer
Range("C1:C65000").FormulaR1C1 = "=MEDIAN(0,RC1,RC2)"
Application.Calculate
Range("C1:C65000").Value = Range("C1:C65000").Value
ElapsedTime2 = Timer - StartTime
Application.ScreenUpdating = True
MsgBox "МАКС(МИН()) обработала за: " & ElapsedTime1 & vbCr & "МЕДИАНА обработала за: " & ElapsedTime2
End Sub
Сравнивая значения таймера до и после вычислений, вы можете определить время, необходимое для работы различных формул. В нашем случае МАКС(МИН()) чуть медленнее, чем МЕДИАНА (рис. 1).

Рис. 1. МАКС(МИН()) работает немного медленнее, чем МЕДИАНА()
Дополнительные сведения: различные компьютеры могут работать с разной скоростью, поэтому важно тестировать разные формулы на одном ПК (у Джелена наоборот, МЕДИАНА была медленнее).
Также, обратите внимание, что разница во времени на рис. 1 менее одной сотой секунды на 60 000 ячеек. Т.е., разница для одной ячейки совершенна ничтожна, и не будет играть никакой роли в большинстве решений на базе Excel. Однако, даже такая маленькая разница позволяет выиграть пари в баре.
Резюме: использование таймера, встроенного в Windows, позволяет анализировать производительность тех или иных формул в Excel.
Источник.

Приложенные файлы

  • docx 44300577
    Размер файла: 41 kB Загрузок: 0

Добавить комментарий