Сравнение производительности формул массива с обычными формулами

Формулы массива в Excel — это замечательный инструмент, который позволяет выполнить практически любую задачу. Поиск склеенных значений, возврат второго по величине значения, специализированные функции — это лишь малая часть о того, что можно с ними делать.

Как только пользователи знакомятся с ними — они говорят «Вау, как здорово!» и пытаются их применить практически везде.

Посчитать сумму заказа — легко с формулами массива!
Посчитать сумму с учетом скидки — и тут пригодятся нам формулу массива
Посчитать накопленный итог — ну тут уж точно без массивов не обойтись..

… А спустя какое-то время удивляются почему файл пересчитывается по несколько секунд или, что еще хуже, десятков секунд.

А причина проста — если есть специальная функция, то лучше использовать ее. СУММЕСЛИ(), СЧЕТЕСЛИ(), СУММПРОИЗВ()  и прочие замечательные функции были введены в Excel не просто так — они реально работают быстрее, чем их аналоги с использованием формул массива.

Приведу пример на цифрах. Возьмем 1 миллион строк и посчитаем по нему итоговое значение — так мы можем узнать разницу производительности уже на основании одной формулы.

Миллион

В первом случае определим  сумму по менеджеру через функцию СУММЕСЛИ(), а во втором случае — используя формулу массива СУММ(ЕСЛИ()).

Формулы.PNG

Для измерения производительности будем простенький макрос на VBA

[code language=»vb»]
Sub Тест_производительности()

Dim i As Integer
Dim Timer1, Timer2

Application.ScreenUpdating = False

‘Ради чистоты эксперимента — очищаем ячейки
Range("K3").Clear
Range("K7").Clear

Timer1 = Timer
‘Range("K3").FormulaR1C1 = "=AVERAGEIF(C[-9],RC[-2],C[-6])"
‘Range("K3").FormulaR1C1 = "=COUNTIF(C[-9],RC[-2])"
‘Range("K3").FormulaR1C1 = "=SUMIFS(C[-6],C[-9],RC[-2],C[-5],RC[-1])"
Range("K3").FormulaR1C1 = "=SUMIFS(C[-6],C[-9],RC[-2],C[-5],RC[-1],C[-8],""Комплект эко"")"
Timer1 = Timer — Timer1
Debug.Print "Формула с СУММЕСЛИМН() (3 критерия)- " & Timer1

Range("K3").Clear

Timer2 = Timer
‘Range("K7").FormulaArray = "=AVERAGE(IF(R[-5]C[-9]:R[999993]C[-9]=RC[-2],R[-5]C[-6]:R[999993]C[-6],""""))"
‘Range("K7").FormulaArray = "=COUNT(IF(C[-9]=RC[-2],1,""""))"
‘Range("K7").FormulaArray = "=SUM(IF(C[-5]=RC[-1],IF(C[-9]=RC[-2],C[-6],"""")))"
Range("K7").FormulaArray = "=SUM(IF(C[-9]=RC[-2],IF(C[-5]=RC[-1],IF(C[-8]=""Комплект эко"",C[-6],""""))))"

Timer2 = Timer — Timer2

Debug.Print "Формулы массива — " & Timer2
Debug.Print "Разница в производительности — " & Timer1 — Timer2

Range("K7").Clear

Application.ScreenUpdating = True

End Sub
[/code]

Результат можно увидеть в окне Immediate — разница в производительности от 1,5 раз. И, естественно, при увеличении количества условий, формулы массива сдают все больше — даже при 2х критериях производительность уже отличается в 3 раза.

[code]
Формулы с СУММЕСЛИ() — 0,171875
Формулы массива — 0,4375
Разница в производительности — 0,265625

Формула с СРЗНАЧЕСЛИ() — 0,171875
Формулы массива — 0,3046875
Разница в производительности — 0,1328125

Формула с СЧЕТЕСЛИ() — 0,171875
Формулы массива — 0,28125
Разница в производительности — 0,109375

Формула с СУММЕСЛИМН() (два критерия) — 0,21875
Формулы массива — 0,671875
Разница в производительности — 0,453125
[/code]

Думаю, не нужно объяснять — если мы попробовали бы использовать на миллион строк формулу массива в каждой строчке, то она бы «повесила» Excel — формула в каждой строчке, которая вычисляется 0,3 секунды…

Мораль — каждый инструмент нужно применять с умом. Не следует каждую задачу решать с помощью формул массива — если есть аналог на функциях, то лучше использовать его

Оставьте комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.