Использование таймера для сравнения производительности формул

Рубрика: 7. Полезняшки Excel

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

Задача: два гуру Excel заходят в бар. Один из них говорит, что быстрее работает формула =МАКС(0;МИН(A1;B1)), а другой – что =МЕДИАНА(0;A1;B1). Кто из них прав?

Решение: вы можете найти ответ путем использования макроса VBA для вычисления каждой формулы 50 000 раз. Прежде чем стартует макрос, вы сохраняете значение таймера в переменную. Когда 50 000 расчетов завершится, можно сравнить исходное и конечное значения таймера.

На ПК под управлением Windows, таймер показывает количество секунд и долей секунды, прошедших с полуночи. На Mac, функция возвращает только целые секунды, так что процесс вычислений нужно повторить достаточное количество раз, чтобы увидеть разницу в секундах. Также, убедитесь, что во время работы макроса не начался новый день (вы не перешли через полночь).

Рис. 1. МЕДИАНА работает немного медленнее, чем связка МАКС(МИН())

Рис. 1. МАКС(МИН()) работает немного медленнее, чем МЕДИАНА()

Скачать заметку в формате Word или pdf, примеры в формате Excel (с кодом VBA)

Прим. Багузина. Мне представляется, что сравнивать нужно формулы, дающие одинаковый результат разными методами. Но приведенные Джеленом формулы дают разный результат, если значения А1 и В1 оба отрицательные.

Следующий код сравнивает формулы МАКС(МИН()) и МЕДИАНА() при расчете 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 менее одной сотой секунды на 60 000 ячеек. Т.е., разница для одной ячейки совершенна ничтожна, и не будет играть никакой роли в большинстве решений на базе Excel. Однако, даже такая маленькая разница позволяет выиграть пари в баре.

Резюме: использование таймера, встроенного в Windows, позволяет анализировать производительность тех или иных формул в Excel.

Источник.


Прокомментировать