Vba excel поиск значения в двумерном массиве
Приветствую всех!
Стояла задача преобразовать хаотично перемешанные данные в читабельный вид, согласно имеющемуся шаблону.
Написал макрос, который со своей задачей справляется, но проблема в том, что при большом количестве обрабатываемых строк(7 000 и выше), работает не очень быстро.
Очень прошу подсказать возможные варианты оптимизации(если таковые имеются).
Согласно таймеру, следующий кусок кода выполняется медленнее всех:
For i = LBound(arr) To UBound(arr)
For j = LBound(arr4) To UBound(arr4)
n = InStr(arr4(j), " ")
If Val(Left(arr4(j), n)) = arr(i, 1) And InStr(arr4(j), "PR No.") <> 0 Then
k = j + 1
For ind = k To UBound(arr4)
If InStr(arr4(ind), "PR No.") <> 0 And Val(Left(arr4(ind), n)) = arr(i, 1) + 10 Then
li = ind - 1
Exit For
Else
li = UBound(arr4)
End If
Next
ReDim arr2(k To li)
For x = k To li
arr2(x) = arr4(x)
Next
Dim a
a = Join(arr2, Chr(10))
Dim n1&, n2&
n1 = InStr(1, a, " ")
n2 = InStr(n1 + 1, a, " ")
arr(i, 6) = Trim(Left(a, n1))
If Trim(Mid(a, n1, n2 - n1)) = "EA" Then
arr(i, 7) = "Each"
ElseIf Trim(Mid(a, n1, n2 - n1)) = "M" Then
arr(i, 7) = "Meter"
Else
arr(i, 7) = Trim(Mid(a, n1, n2 - n1))
End If
arr(i, 4) = arr(i, 3) & Chr(10) & Trim(Mid(a, n2))
arr(i, 3) = arr(i, 4)
End If
Next
Next
именно здесь производится операция, вынесенная в заголовок темы.
То есть каждое значение первого массива ищется во втором, и на основании этого динамически определяется диапазон значений, которые переносятся из второго массива в третий.
Возможно, несколько запутано, поэтому для лучшего понимания прилагаю пример.
Приветствую всех!
Стояла задача преобразовать хаотично перемешанные данные в читабельный вид, согласно имеющемуся шаблону.
Написал макрос, который со своей задачей справляется, но проблема в том, что при большом количестве обрабатываемых строк(7 000 и выше), работает не очень быстро.
Очень прошу подсказать возможные варианты оптимизации(если таковые имеются).
Согласно таймеру, следующий кусок кода выполняется медленнее всех:
For i = LBound(arr) To UBound(arr)
For j = LBound(arr4) To UBound(arr4)
n = InStr(arr4(j), " ")
If Val(Left(arr4(j), n)) = arr(i, 1) And InStr(arr4(j), "PR No.") <> 0 Then
k = j + 1
For ind = k To UBound(arr4)
If InStr(arr4(ind), "PR No.") <> 0 And Val(Left(arr4(ind), n)) = arr(i, 1) + 10 Then
li = ind - 1
Exit For
Else
li = UBound(arr4)
End If
Next
ReDim arr2(k To li)
For x = k To li
arr2(x) = arr4(x)
Next
Dim a
a = Join(arr2, Chr(10))
Dim n1&, n2&
n1 = InStr(1, a, " ")
n2 = InStr(n1 + 1, a, " ")
arr(i, 6) = Trim(Left(a, n1))
If Trim(Mid(a, n1, n2 - n1)) = "EA" Then
arr(i, 7) = "Each"
ElseIf Trim(Mid(a, n1, n2 - n1)) = "M" Then
arr(i, 7) = "Meter"
Else
arr(i, 7) = Trim(Mid(a, n1, n2 - n1))
End If
arr(i, 4) = arr(i, 3) & Chr(10) & Trim(Mid(a, n2))
arr(i, 3) = arr(i, 4)
End If
Next
Next
именно здесь производится операция, вынесенная в заголовок темы.
То есть каждое значение первого массива ищется во втором, и на основании этого динамически определяется диапазон значений, которые переносятся из второго массива в третий.
Возможно, несколько запутано, поэтому для лучшего понимания прилагаю пример. Xpert
For i = LBound(arr) To UBound(arr)
For j = LBound(arr4) To UBound(arr4)
n = InStr(arr4(j), " ")
If Val(Left(arr4(j), n)) = arr(i, 1) And InStr(arr4(j), "PR No.") <> 0 Then
k = j + 1
For ind = k To UBound(arr4)
If InStr(arr4(ind), "PR No.") <> 0 And Val(Left(arr4(ind), n)) = arr(i, 1) + 10 Then
li = ind - 1
Exit For
Else
li = UBound(arr4)
End If
Next
ReDim arr2(k To li)
For x = k To li
arr2(x) = arr4(x)
Next
Dim a
a = Join(arr2, Chr(10))
Dim n1&, n2&
n1 = InStr(1, a, " ")
n2 = InStr(n1 + 1, a, " ")
arr(i, 6) = Trim(Left(a, n1))
If Trim(Mid(a, n1, n2 - n1)) = "EA" Then
arr(i, 7) = "Each"
ElseIf Trim(Mid(a, n1, n2 - n1)) = "M" Then
arr(i, 7) = "Meter"
Else
arr(i, 7) = Trim(Mid(a, n1, n2 - n1))
End If
arr(i, 4) = arr(i, 3) & Chr(10) & Trim(Mid(a, n2))
arr(i, 3) = arr(i, 4)
End If
Next
Next
именно здесь производится операция, вынесенная в заголовок темы.
То есть каждое значение первого массива ищется во втором, и на основании этого динамически определяется диапазон значений, которые переносятся из второго массива в третий.
Возможно, несколько запутано, поэтому для лучшего понимания прилагаю пример. Автор - Xpert
Дата добавления - 05.04.2022 в 17:08
В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.
Поскольку искомое значение может встретиться в нескольких строках обрабатываемого двумерного массива,
оба способа получают на выходе отфильтрованный двумерный массив.
Способы формирования отфильтрованных массивов - разные:
второй способ - функцию ArraySearchResults
Основные отличия и особенности этих 2 способов поиска:
- ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
- ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
- ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз - ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
- ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
- ArraySearchResults производит поиск в заранее сформированной текстовой строке
Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.
Сохранить изображения с листа Excel в файлы
Надстройка позволяет экспортировать все изображения с листа Excel в графические файлы.
Загрузка изображений из интернета по ссылкам в одну папку
Макрос предназначен для загрузки изображений (или любых других файлов) из интернета, и сохранения скачанных файлов в одну папку.
Исходные данные для работы макроса:
таблица, в которой содержатся по меньшей мере 2 столбца - один с гиперссылками, второй - с именами файлов.
- создаваемым файлам присваиваются имена из выбранного столбца листа Excel
- макрос корректно работает со ссылками, содержащими символы кириллицы
- автоматическое добавление расширения для скачиваемых файлов (если имя файла из ячейки его не содержит)
Если вам требуется вставлять много изображений на лист Excel, - то вам поможет надстройка, позволяющая производить поиск изображений в заданной папке, и производить вставку картинок в ячейки или примечания
Кроме того, надстройка для вставки изображений в Excel умеет загружать картинки из интернета (по ссылкам в таблице Excel)
Настройки макроса легко выполнить, изменив в коде значения констант:
Смотрите также аналогичный (более сложный) макрос загрузки изображений
Функция VBA для выполнения веб-запроса (Web Query)
Функция GetQueryRange предназначена для автоматизации загрузки данных с веб-страниц.
Например, нам надо из макроса Excel получить данные с нескольких однотипных страниц сайта.
Самый простой способ достичь этого - выполнять почти идентичные веб-запросы (где незначительно отличаться будет только URL страницы),
каждый раз анализируя данные, загруженные веб-запросом на лист Excel
Поскольку количество обращений ко мне, с просьбами сделать программу загрузки данных из интернета, с каждым днём растёт, я решил сделать для этих целей универсальную функцию:
Скачивание (загрузка) текста web-страницы в текстовый файл
При помощи этого макроса вы можете скачать текст с выбранной страницы веб-сайта:
Данная функция ищет в массиве все строки, подходящие под заданные критерии, и возвращает результат в виде отфильтрованного массива:
PS: Код обновлён 4 января 2021 года, — теперь он работает в 40 раз быстрее, нежели прежняя версия функции
Комментарии
UPD. Разогнал поиск и сопоставление с нескольких часов до нескольких минут для 800к+ записей в одном массиве и 365к+ в другом. если кому интересно - могу поделиться наработками.
Игорь, добрый день.
Доработал Ваш код под свои потребности и дополнительно сделал следующее (как говорил ранее, требуется сопоставление довольно больших массивов):
1. перед вызовом функции формирую временный массив, в котором содержится только 2 колонки: колонка со значениями и колонка с номером строки в исходном массиве.
2. при поиске в функции добавляю в отдельную коллекцию найденные значения (номера строк).
3. если количество найденных элементов более 250 - применяю Ваш способ формирования итогового массива на выходе из функции (т.е. перебором по временному массиву), иначе перебор идет по коллекции. При сотнях тысяц записей прирост по скорости ощутимый. В исходном (из п.1) массиве найденные строки очищаю от значений.
4. после обработки заданного количества строк (например, 5000-10000) удаляю из массива (п.1) пустые строки. Удаление производится перебором (через промежуточный массив), но это позволяет сократить время обработки. Если при первом запуске на 50000 сопоставленных строк тратится 4584 секунды, то после 250 000 - 365 секунд (365 000 записей сопоставлял с 596 000, на все про все ушло порядка 3 часов, при том что если делать сопоставление "в лоб" - потребуется более 7 часов).
Код обработки получился объемный, но попробую как пример сделать лайтовую версию (безусловно, если интересует).
Но без Вас вряд ли смог сделать нормальное решение. Спасибо.
Игорь, доброго времени суток.
Вариант фильтрации массива реализован достойно, но не проще ли сразу номера нужных строк загонять, например, в коллекцию либо словарь и потом на основании сформированного перечня выдавать результирующий массив с нужными строками (чтобы не бегать по исходному массиву несколько раз)? единственное НО - в случае с коллекцией есть ограничение порядка 250 записей (получено опытным путем, в документации не нашел такого).
Так же есть проблема с выводом значений, если найдена всего одна строка - итоговый массив не совсем некорректно формируется (не исключаю, что мои кривые ручки/параметры Excel виноваты, но факт остается фактом и для обхода данного "ограничения") немного поправил строчку: ReDim newarr(RowsCount&, UBound(arr, 2)) (безусловно, в таком случае образуется пустая нулевая строчка, но её можно легко пропустить при дальнейшей обработке результатов (для перфекционистов Redim Preserve в помощь, но оно того не стоит)).
P.S. для моей задачи требуется сравнение двух массивов, в одном порядка 365 000 записей, в другом примерно 800 000 записей (причем сверка производится трижды и тупой перебор через For. Next в лоб слишком медленно отрабатывает, соотношение записей один ко многим). Вариант с формированием поисковой текстовой строки не рассматриваю ибо её размер будет похож на мемуары (сравнение производится по текстовым значениям, индексов нет, при этом в "малом" массиве размер каждого значения около 65 знаков, в "большом" - порядка 130-180 символов). Собственно по этой причине и подыскиваю оптимальное решение (Ваше в ТОПе).
В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.
Поскольку искомое значение может встретиться в нескольких строках обрабатываемого двумерного массива,
оба способа получают на выходе отфильтрованный двумерный массив.
Способы формирования отфильтрованных массивов - разные:
второй способ - функцию ArraySearchResults
Основные отличия и особенности этих 2 способов поиска:
- ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
- ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
- ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз - ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
- ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
- ArraySearchResults производит поиск в заранее сформированной текстовой строке
Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.
Примеры поиска в огромных массивах:
Поиск с использованием ArrAutofilterEx
Поиск с использованием ArraySearchResults
Код функции ArraySearchResults:
При поиске только одного значения время работы обоих макросов поиска не сильно отличается - но обычно функция 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 колонки большого (чтобы было более понятно: по номерам продавцов найти все операции, которые они совершали (первый столбец большого), при этом, отбор производится только в том случае, если операции проводились с другими продавцами). Т.е. в результате поиска по продавцу "Пегасову" (из мелкого файла) должны отобраться строки, когда "Пегасов" что либо продавал другим продавцам, перечисленным в мелком файле. Одному продавцу может соответствовать множество операций.
Сделать можно, но - оба столбца большого файла не надо загружать в один массив (иначе компу памяти не хватит, т.к. в массиве будет много лишних столбцов)
Компьютер с 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.
А возможна работа только для значений со знаком "="? А можно ли использовать "<>"?
Добрый день! Помогите решить. Ничего не нашел по этой теме.
Необходимо разработать подпрограмму поиска максимального элемента в двухмерном массиве:
Поиск максимального элемента в двумерном массиве
Public Function Max(ByVal ArrM(,) As Integer) As Integer Dim C1, C2 As Integer .
Поиск максимального/минимального элемента в массиве
Данная матрица размера m * n. Найти 1. минимальный 2. максимальный среди 1).
Поиск максимального элемента в двухмерном массиве и нахождение его индекса
Помогите, пожалуйста, найти индекс максимального элемента в двумерном массиве. Массив - результат.
Решение
Здравствуйте, также решаю эту задачу. Смог только сделать функцию, которая возвращает максимальное значение самой ячейки, а как вернуть еще и индекс этой ячейки двухмерного массива?
В двухмерном массиве вычислить произведение минимального и максимального элемента массива
В двухмерном массиве вычислить произведение минимального и максимального элемента массива.Решить в.
Написать программу поиска номеров строки и столбца минимального и максимального элемента в заданном двухмерном массиве целых чисел
Написать программу поиска номеров строки и столбца минимального и максимального элемента в заданном.
Поиск максимального четного элемента в массиве
Задан целочисленный массив a из n строк и m столбцов. Найти значение максимального элемента среди.
Поиск максимального и минимального элемента в массиве
Как найти максимальный и минимальный элемент в одномерном массиве
Поиск максимального и минимального элемента в массиве
Помогите отредактировать программу. Задание: Ввести двумерный массив, найти индексы и значения.
Поиск максимального элемента в двумерном массиве
Здравствуйте! Собственно вопрос - оптимальный алгоритм. Есть ли тут вообще алгоритм который находит.
Читайте также: