|
В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.
Поскольку искомое значение может встретиться в нескольких строках обрабатываемого двумерного массива,
оба способа получают на выходе отфильтрованный двумерный массив.
Способы формирования отфильтрованных массивов - разные:
первый способ использует функцию ArrAutofilterEx
второй способ - функцию ArraySearchResults
Основные отличия и особенности этих 2 способов поиска:
- ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
- ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
- ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз
- ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
- ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
- ArraySearchResults производит поиск в заранее сформированной текстовой строке
Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.
Примеры поиска в огромных массивах:
Поиск с использованием ArrAutofilterEx
Sub ПримерМедленногоПоискаВМассиве()
t = Timer
ИскомоеЗначение$ = 560
СтолбецДляПоиска& = 3
' загружаем массив с листа
arr = [a1:d30000].Value
' укорачиваем массив Arr, оставляя лишь те строки,
' где в заданном столбце есть искомое значение
On Error Resume Next: Err.Clear
resArr = ArrAutofilterEx(arr, СтолбецДляПоиска& & "=" & ИскомоеЗначение$)
' проверяем возвращеное функцией значение на наличие результатов поиска
If Err Then Debug.Print "Такие строки в массиве не найдены": Exit Sub
' выводим из отфильтрованных строк значения первого столбца
For i = LBound(resArr) To UBound(resArr)
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 1)
Next i
Debug.Print "Время: " & Timer - t & " сек."
End Sub
Поиск с использованием ArraySearchResults
Sub ПримерБыстрогоПоискаВМассиве()
t = Timer
ИскомоеЗначение$ = 560
СтолбецДляПоиска& = 3
' загружаем массив с листа
arr = [a1:d30000].Value
' формируем строку поиска
ss$ = SearchString(arr, СтолбецДляПоиска&)
' укорачиваем массив Arr, оставляя лишь те строки,
' где в заданном столбце есть искомое значение
resArr = ArraySearchResults(arr, ss$, ИскомоеЗначение$)
' проверяем возвращеное функцией значение на наличие результатов поиска
If Not IsArray(resArr) Then Debug.Print "Такие строки в массиве не найдены": Exit Sub
' выводим из отфильтрованных строк значения первого столбца
For i = LBound(resArr) To UBound(resArr)
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 1)
Next i
Debug.Print "Время: " & Timer - t & " сек."
End Sub
Код функции ArraySearchResults:
Function ArraySearchResults(ByRef arr, ByRef searchStr As String, ByVal txt As String, _
Optional ByVal Sep As String = "%$%") As Variant
' функция получает в качестве параметров массив Arr,
' и заранее сформированную строку SearchString из значений ячеек нужного столбца массива
' По этой строке SearchString функция ищет строки массива, в которые встречается значение txt,
' и возвращает усечённый массив, содержащий только подходящие строки
' Поиск ведётся по ТОЧНОМУ совпадению значений
ro& = 0: spl = Split(searchStr, Sep & txt & Sep)
If UBound(spl) = 0 Then Exit Function ' нет в массиве нужных строк
' перебираем результаты поиска, вычисляя номера строк в исходном массиве
For i = LBound(spl) To UBound(spl) - 1
txt = spl(i): ro& = ro& + 1 + (Len(spl(i)) - Len(Replace(spl(i), Sep, ""))) / Len(Sep) \ 2
spl(i) = ro&
Next i
' подготавливаем массив для результатов:
' по ширине - как исходный, по высоте - содержащий столько строк, сколько найдено совпадений
ReDim resArr(1 To UBound(spl), LBound(arr, 2) To UBound(arr, 2))
' заполняем новый массив
For i = LBound(spl) To UBound(spl) - 1
For j = LBound(arr, 2) To UBound(arr, 2)
resArr(i + 1, j) = arr(spl(i), j)
Next j
Next i
ArraySearchResults = resArr
End Function
Function SearchString(ByRef arr, ByVal ArrayColumn As Long, _
Optional ByVal Sep As String = "%$%") As String
' Объединяет все значения из столбца ArrayColumn массива Arr в одну текстовую строку,
' в качестве разделителя элементов используя строку Sep
' Для ускорения конкатенации длинных строк используются
' промежуточные переменные buffer$ и buffer2$
buffer$ = "": buffer2$ = "": Sep2$ = Sep$ & Sep$: Const BufferLen& = 6000
On Error Resume Next: Err.Clear: SearchString = Sep2$
If ArrayColumn > UBound(arr, 2) Or ArrayColumn < LBound(arr, 2) Then Exit Function
For i = LBound(arr) To UBound(arr)
buffer$ = buffer$ & Trim$(arr(i, ArrayColumn)) & Sep2$
If Len(buffer$) > BufferLen& Then
buffer2$ = buffer2$ & buffer$: buffer$ = ""
If Len(buffer2$) > BufferLen& * 20 Then _
SearchString = SearchString & buffer2$: buffer2$ = ""
End If
Next i
SearchString = SearchString & buffer2$ & buffer$
End Function
При поиске только одного значения время работы обоих макросов поиска не сильно отличается - но обычно функция ArraySearchResults оказывается немного быстрее.
|
