В данной статье показаны 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 оказывается немного быстрее.
Комментарии
Работает быстро!
Даже в Accesse работает, но у меня массив начинается с 0, и результат выдает на строчку выше.
txt = spl(i): ro& = ro& + 1 + (Len(spl(i)) - Len(Replace(spl(i), Sep, ""))) / Len(Sep) \ 2' Было
txt = spl(i): ro& = ro& + 0 + (Len(spl(i)) - Len(Replace(spl(i), Sep, ""))) / Len(Sep) \ 2' Поменял
Может будет лучше если добавить СтолбецДляВывода&
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 1)' Было
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, СтолбецДляВывода&)' Поменял
Привет, спасибо за реализацию функции, помогла для обработки!
подскажите, как сделать поиск нескольких искомых значений?
Привет!
Для уважающих Option Explicit
в ArraySearchResults
Dim ro As Long, spl, i As Long, j As Long
В SearchString
Dim buffer As String, buffer2 As String, Sep2 As String, i As Long
и скорость возрастёт
Здравствуйте! А подскажите, пожалуйста, возможно ли использование подстановочных знаков для поиска искомого значения?
решено - можно. Всё работает
И второй вопрос - есть ли у вас функция типа SearchString, но для сцепления ВСЕГО двумерного массива в текстовую строку с разделителями, а не одного столбца. Или придётся цикл делать, чего бы очень не хотелось.
Я так понимаю, что ваш вариант даже шустрее, чем Join, который, к тому же, не работает с двумерными массивами (как я понял).
Здравствуйте! Подскажите пожалуйста - могу ли я объявить Optional ByVal ArrayColumn As Long=1 в функции SearchString? Дело в том, что я часто загружаю в массив данные с листа в 1 столбец…
Задача: есть большой список в 1 столбце, текстовый, сотни тысяч записей. И есть второй список тоже текстовый из нескольких сотен записей в двух столбцах. Задача - если в текстовый элемент в первом массиве входит элемент из первого столбца второго то во второй столбец первого массива проставить соответсвующий элемент из второго столбца второго массива. Т.е. поиск совпадений не по всему значению, а по вхождению в него куска.
Справился. Немного не так, как хотел, но работает.
For i = 1 To UBound(resArr)
ListBox1.AddItem resArr(i, 2)
Next i
Уважаемый Игорь, подскажите, пожалуйста, как вывести значения, например, второго столбца отфильтрованного массива в листбокс?
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 2)
все показывает, а вывести в листбокс не могу
ListBox1.List = resArr
естественно выводит 2 столбца
А вот и третья функция, которую я применил в своей работе в течение недели.
Все работает "на ура"!
А теперь вот думаю, чтобы я делал без Ваших функций? :)
Большое Вам спасибо!
Удачи!
А для большого файла и делается два массива, при этом каждый состоит только из одного столбца (своего рода индексы получаю для поиска (пробовал для теста подсовывать файл на 870 000 записей и загружал в массив порядка 20 столбцов - "машинка" с 4 гигами очень серьезно задумывалась при этом (собственно еще и по этой причине отказался от загрузки всего листа в массив (первая причина отказа - искажение данных при "перегонах")))).
При поиске важно найти все строки, которые есть в большом файле и которым соответствуют строки из маленького, при этом должны анализироваться 2 колонки большого (чтобы было более понятно: по номерам продавцов найти все операции, которые они совершали (первый столбец большого), при этом, отбор производится только в том случае, если операции проводились с другими продавцами). Т.е. в результате поиска по продавцу "Пегасову" (из мелкого файла) должны отобраться строки, когда "Пегасов" что либо продавал другим продавцам, перечисленным в мелком файле. Одному продавцу может соответствовать множество операций.
За ответ: премного благодарен - попробую поэксперементировать (о результатах нагрузочного тестирования сообщу (для информации: обработка 5 файлов (6 500 + 870 000 + 870 000 + 870 000 + 870 000) занимает порядка 28-30 минут на машине с intel i5 650, RAM 4 Gb (на 2 гиговой пробовать этих монстров не стал - жалко, "старушку"))).
Сделать можно, но - оба столбца большого файла не надо загружать в один массив (иначе компу памяти не хватит, т.к. в массиве будет много лишних столбцов)
Компьютер с 2 гигами памяти - для такого макроса более чем достаточно.
Функцию быстрого поиска в массиве можно использовать, только надо искать значения второго (огромного) массива в маленьком (первом)
Можно и наоборот - но возможно понадобится тройное кеширование строки поиска ( buffer$, buffer2$, и ещё добавить buffer3$)
Ничего конкретного подсказать не могу - надо знать, для чего это делается, и как это все должно работать.
Но функцию использовать можно.
Доброго времени суток!
Подскажите. пожалуйста, стоит ли использовать предложенные функции при сравнении трех массивов и копировании результатов на отдельный лист (первый массив - порядка 2 500 записей (данные отдельного файла, берется только 1 столбец), второй и третий - 150 000 - 250 000 (второй и третий формируются на основе одной таблицы (второй файл), но разных столбцов, которые отстоят друг от друга на неком расстоянии (грубо - первый столбец "А", второй - "AB") и изменять порядок столбцов нельзя))?
Последовательный перебор записей относительно медленный, при этом внесение всей таблицы из второго файла (по которому строятся второй и третий массивы) нежелательно, т.к. теряется формат отдельных столбцов при перегоне данных сначала в массив, а потом на лист Excel (собственно по этой причине приходится копировать с листа исходного файла на итоговый лист диапазон ячеек, при этом номер строки вычисляется на основании номера элемента массива). Количество колонок во втором файле - порядка 50-60.
Есть существенное ограничение: рабочая станция, на которой происходит обработка данных, относительно слабая и ждать от нее рекордов не приходится (памяти на ней всего 2 гига, но офис - 2010). Если бы была возможность прикрепить файл, то показал бы - что получилось (если вставить код здесь, то очень много получится)...
а если необходимо найти значение в столбце равное 3, затем спуститься на 2 строки и от этой строчки начать отсчет. такое возможно реализовать?
помогите, пожалуйста
Да, можно, если написать для этого специальную функцию.
а если использовать один массив и фильтровать его на основе значений из другого массива? так можно?
Всё можно сделать - но проще под вашу задачу написать отдельную функцию.
Или поступить иначе:
1) сформировать 3 массива при помощи функции ArrAutofilterEx (для каждого из значений)
2) соединить 3 массива в один при помощи функции CombineArrays
Ещё вариант: использовать средства Excel (автофильтр по нескольким значениям)
Тут вам поможет макрорекордер (запись макросов)
И как можно сделать, чтобы отбирать значения из столбца не только с одним значением? Например, столбец для поиска один и тот же = 3, а значения надо отобрать 560, 570, и 580.
А возможна работа только для значений со знаком "="? А можно ли использовать "<>"?
Отправить комментарий