Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: два гуру Excel заходят в бар. Один из них говорит, что быстрее работает формула =МАКС(0;МИН(A1;B1)), а другой – что =МЕДИАНА(0;A1;B1). Кто из них прав?
Решение: вы можете найти ответ путем использования макроса VBA для вычисления каждой формулы 50 000 раз. Прежде чем стартует макрос, вы сохраняете значение таймера в переменную. Когда 50 000 расчетов завершится, можно сравнить исходное и конечное значения таймера.
На ПК под управлением Windows, таймер показывает количество секунд и долей секунды, прошедших с полуночи. На Mac, функция возвращает только целые секунды, так что процесс вычислений нужно повторить достаточное количество раз, чтобы увидеть разницу в секундах. Также, убедитесь, что во время работы макроса не начался новый день (вы не перешли через полночь).
Рис. 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.