Формулы массива в Excel — это замечательный инструмент, который позволяет выполнить практически любую задачу. Поиск склеенных значений, возврат второго по величине значения, специализированные функции — это лишь малая часть о того, что можно с ними делать.
Как только пользователи знакомятся с ними — они говорят «Вау, как здорово!» и пытаются их применить практически везде.
Посчитать сумму заказа — легко с формулами массива!
Посчитать сумму с учетом скидки — и тут пригодятся нам формулу массива
Посчитать накопленный итог — ну тут уж точно без массивов не обойтись..
… А спустя какое-то время удивляются почему файл пересчитывается по несколько секунд или, что еще хуже, десятков секунд.
А причина проста — если есть специальная функция, то лучше использовать ее. СУММЕСЛИ(), СЧЕТЕСЛИ(), СУММПРОИЗВ() и прочие замечательные функции были введены в Excel не просто так — они реально работают быстрее, чем их аналоги с использованием формул массива.
Приведу пример на цифрах. Возьмем 1 миллион строк и посчитаем по нему итоговое значение — так мы можем узнать разницу производительности уже на основании одной формулы.
В первом случае определим сумму по менеджеру через функцию СУММЕСЛИ(), а во втором случае — используя формулу массива СУММ(ЕСЛИ()).
Для измерения производительности будем простенький макрос на 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 секунды…
Мораль — каждый инструмент нужно применять с умом. Не следует каждую задачу решать с помощью формул массива — если есть аналог на функциях, то лучше использовать его