Функции рабочего листа и скорость расчетов
В прошлом посте мы упоминали, что скорость расчетов встроенных функций Excel больше скорости самостоятельно созданных функций на VBA.Однако ситуация не всегда так однозначна. Встроенными средствами VBA можно создавать пользовательские функции которые будут работать гораздо быстрее встроенных, используя другие алгоритмы расчетов. Особенно сильное различие получается при составлении сложных формул. В Excel одну и ту же задачу можно решить несколькими способами, в зависимости от квалификации пользователя и текущих задач. Так же и в VBA. Помимо этого в VBA можно производить расчеты непосредственно в программном коде, не обращаясь к ячейкам. Такие расчеты проходят в тысячи раз быстрее, т.к. операция обращения к ячейкам рабочего листа является очень медленной.
Рассмотрим на конкретном примере. Создадим пользовательскую функцию рабочего листа, задачей которой будет подсчет уникальных значений в заданном массиве.Такой прямо встроенной функции нет, однако задачу можно решить следующей формулой:
=СУММ(1/СЧЁТЕСЛИ(A:A;A:A))
Создадим на основе этой формулы пользовательскую функцию УНИЧ:
Function УНИЧ(Диапазон As Range) As Long
УНИЧ = Evaluate("Sum(1/CountIf(" & Диапазон.Address(, , , True) & "," & Диапазон.Address(, , , True) & "))")
End Function
Преимуществом данной функции является лишь то, кто пишется она на листе намного короче первой и диапазон нужно задавать только один раз. На листе она будет представлена в виде:
=УНИЧ(А:А)
Но если мы возьмем какой-либо объемный массив данных, то можно увидеть, что скажем на 10000 строк эта функция будет выполнятся около 30 сек. А если файлов с такими массивами 100, 200, 1000? На расчеты можно потратить целый день.
Теперь давайте попробуем средствами VBA создать аналогичную функцию, но с прямым алгоритмом подсчета:
Function УНИЧ2(Диапазон As Range) As Long
Dim iCell As Range
Set Диапазон = Intersect(Диапазон.Parent.UsedRange, Диапазон)
On Error Resume Next
With New Collection
For Each iCell In Диапазон
If iCell.Value <> "" Then .Add iCell.Value, CStr(iCell.Value)
Next
УНИЧ2 = .Count
End With
End Function
Как можно понять из алгоритма, данная функция с помощью цикла For Each In проходит по всем ячейкам диапазона и сравнивая их значения подсчитывает только ячейки с разными значениями. Эта функция на том же размере диапазона, что и в предыдущем примере работает только 0,4 сек. Как видите разница существенна, и на больших объемах данных будет давать существенный прирост скорости расчетов.
Рассмотрим еще одни пример. В прошлом примере мы все равно каждый раз обращались к ячейкам рабочего листа в цикле, и если функция будет более сложной. или же массив выводимых данных будет больше одной ячейки, прирост производительности будет не таким выраженным. В VBA все расчеты можно провести в программном массиве, обратившись к рабочему листу только 2 раза - для считывания диапазона и для вывода итоговых расчетов.
Function УНИЧ3(Диапазон As Range)
Dim tmpArr()
Dim i As Long, j As Long
tmpArr = Диапазон.Value
On Error Resume Next
With New Collection
For i = LBound(tmpArr, 1) To UBound(tmpArr, 1)
For j = LBound(tmpArr, 2) To UBound(tmpArr, 2)
If tmpArr(i, j) <> "" Then .Add tmpArr(i, j), CStr(tmpArr(i, j))
Next j
Next i
УНИЧ3 = .Count
End With
End Function
Как можно понять из функции, текущий диапазон записывается во временный массив tmpArr, и затем этот массив обрабатывается непосредственно в коде, без обращения к ячейкам.Данная функция выполняется 0,09 секунд, что при больших диапазонах расчетов может дать существенную экономию времени расчетов.
Рассмотрим на конкретном примере. Создадим пользовательскую функцию рабочего листа, задачей которой будет подсчет уникальных значений в заданном массиве.Такой прямо встроенной функции нет, однако задачу можно решить следующей формулой:
=СУММ(1/СЧЁТЕСЛИ(A:A;A:A))
Создадим на основе этой формулы пользовательскую функцию УНИЧ:
Function УНИЧ(Диапазон As Range) As Long
УНИЧ = Evaluate("Sum(1/CountIf(" & Диапазон.Address(, , , True) & "," & Диапазон.Address(, , , True) & "))")
End Function
Преимуществом данной функции является лишь то, кто пишется она на листе намного короче первой и диапазон нужно задавать только один раз. На листе она будет представлена в виде:
=УНИЧ(А:А)
Но если мы возьмем какой-либо объемный массив данных, то можно увидеть, что скажем на 10000 строк эта функция будет выполнятся около 30 сек. А если файлов с такими массивами 100, 200, 1000? На расчеты можно потратить целый день.
Теперь давайте попробуем средствами VBA создать аналогичную функцию, но с прямым алгоритмом подсчета:
Function УНИЧ2(Диапазон As Range) As Long
Dim iCell As Range
Set Диапазон = Intersect(Диапазон.Parent.UsedRange, Диапазон)
On Error Resume Next
With New Collection
For Each iCell In Диапазон
If iCell.Value <> "" Then .Add iCell.Value, CStr(iCell.Value)
Next
УНИЧ2 = .Count
End With
End Function
Как можно понять из алгоритма, данная функция с помощью цикла For Each In проходит по всем ячейкам диапазона и сравнивая их значения подсчитывает только ячейки с разными значениями. Эта функция на том же размере диапазона, что и в предыдущем примере работает только 0,4 сек. Как видите разница существенна, и на больших объемах данных будет давать существенный прирост скорости расчетов.
Рассмотрим еще одни пример. В прошлом примере мы все равно каждый раз обращались к ячейкам рабочего листа в цикле, и если функция будет более сложной. или же массив выводимых данных будет больше одной ячейки, прирост производительности будет не таким выраженным. В VBA все расчеты можно провести в программном массиве, обратившись к рабочему листу только 2 раза - для считывания диапазона и для вывода итоговых расчетов.
Function УНИЧ3(Диапазон As Range)
Dim tmpArr()
Dim i As Long, j As Long
tmpArr = Диапазон.Value
On Error Resume Next
With New Collection
For i = LBound(tmpArr, 1) To UBound(tmpArr, 1)
For j = LBound(tmpArr, 2) To UBound(tmpArr, 2)
If tmpArr(i, j) <> "" Then .Add tmpArr(i, j), CStr(tmpArr(i, j))
Next j
Next i
УНИЧ3 = .Count
End With
End Function
Как можно понять из функции, текущий диапазон записывается во временный массив tmpArr, и затем этот массив обрабатывается непосредственно в коде, без обращения к ячейкам.Данная функция выполняется 0,09 секунд, что при больших диапазонах расчетов может дать существенную экономию времени расчетов.
Комментарии
Отправить комментарий