В данной статье показаны 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 оказывается немного быстрее.

• 39421 просмотр