Excel vba поиск по двум столбцам
Обновлено: 04.07.2024
В данной статье показаны 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.
А возможна работа только для значений со знаком "="? А можно ли использовать "<>"?
Поиск одинаковых чисел в двух столбцах
Excel Доброго времени суток, форумчане! Извините что напрягаю, но вот в чем загвоздка.
Поиск .Find по двум полям в двух столбцах
Подскажите, пожалуйста, что нужно добавить в код With Worksheets('B').Range('E1:E650') Set C =.
Поиск схожих значений в двух столбцах и перенос из первого на другой лист
Добрый вечер! Идея такая! Есть Два столбца, Один большой, второй меньше, нужно найти значения из.
Проверка совпадений в двух столбцах
Sub Poisk() For a = 1 To 5 i = Cells(1, 5) m = Cells(a, 1) If i = m Then Cells(a, 2).Select.
Данные из A в массив, его циклом в словарь.
Данные из B в массив, его циклом сверяем с словарём - если есть в словаре, то действуем в строке.
Будет значительно быстрее - и чем больше данных, тем больше будет разница скорости обработки. А нет в запасниках какой-то ссылки, где можно почитать про работу со словарями? Я так сходу ничего толкового не нашел.
Решение
Ссылки - не по правилам (а на MSDN не знаю).Но есть пример. Там кода всего 10 строк. Очень интересный пример, спасибо.
Подскажите, а как можно изменить код, если на Sheet2 в столбце А будут совпадающие значения? То есть будут 2 раза встречаться а2 с разными цифрами, и нужно эти цифры на Sheet3 просуммировать?
Я так полагаю, что если встречаются повторящиеся значения в первом столбце на странице2, то a(i, 3) = b(.Item(a(i, 1)), 2) работать как положено не будет?
В примере в файле сперва в словаре запоминается номер строки (массива) уникальных значений второго листа, затем при цикле по массиву первого листа эти значения подтягиваются. Но т.к. запоминается только один номер - то суммирование там не получится. Но зато из этой строки можно подтянуть хоть 1000 значений.
Если нужно суммировать одну позицию - можно сразу сумму собирать в словаре, затем из словаря и подставлять:
Если нужно - попробуйте самостоятельно, в этих двух примерах всё необходимое кажется есть.
Я тут посидел на выходных, поразмышлял, и так и не придумал, как можно применить словарь к своей задаче.
Алгоритм у меня в конкретной задаче такой:
1. Берется страница Sheet(1401_1402) с данными, загоняется полностью в массив а() (14 столбцов)
2. Берем массив b(), и перегоняем циклом данные из 2-х нужных столбцов в этот массив, при это обрабатывая данные из одного из столбцов (в столбце идентификаторы, у каких-то мы отрезаем лишние цифры (P.26004.09.658.11 -> P.26004), у каких-то отрезаем первые четыре символа.
3. В третий массив с() перегоняем вложенным циклом эти два столбца из массива b(), но уже суммируя строки с одинаковыми идентификаторами (были, скажем, 2 разных идентификатора P.26004.09.658.11 и P.26004.12.758.51, а стал один P.26004).
4. Потом уже берем данным из соответствующего столбца страницы Sheet(CER), сравниваем вложенным циклом каждый идентификатор из этого столбца [они тут уже приведенные к тому виду, который был получен при переводе b()->c()] с идентификаторами в массиве c(), и прописываем идентификатору соответствующую сумму.
Иногда на изначальной странице идентификаторы разбросаны по нескольким столбцам, и приходится при перегоне из массива а() в массив b() делать что-то вроде if a(i,10)<>Empty then . else if a(i,11)<> empty then . ну и т.д.
Можно упростить решение такой задачи с помощью словарей?
Код выглядит так:
З.Ы. Во вложении файл с примером, если нужно.
Если нужно добавить к тому, что уже есть на листе - то массив не создаём, а берём с листа с тем, что там уже есть.
Добавлено через 20 минут
Вот например так (но в примере совпадений нет - я создал искусственно):
Сравнение значений в двух столбцах
Доброго времени суток! Помогите пожалуйста написать макрос для такой задачи: * следует.
Удаление совпадающих записей в двух столбцах
Здравствуйте. В excel необходимо сравнить два столбца и удалить совпадающие записи. Пример: 1 2.
Макрос для сравнения данных в двух столбцах, вместо vlookup
Подскажите, пожалуйста, как написать макрос, который бы можно было использовать для сравнения.
Поиск и сравнение значений в столбцах по двум параметрам
Добрый день. Помогите с проблемой. В VBA не силен, но очень СРОЧНО нужно. Буду рад любой помощи.
Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.
Синтаксис метода Range.Find
Expression . Find ( What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.
В скобках перечислены параметры метода, среди них только What является обязательным.
Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.
Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.
Параметры метода Range.Find
- xlValues (-4163) – значения;
- xlComments (-4144) – примечания*;
- xlNotes (-4144) – примечания*;
- [xlFormulas (-4123) – формулы]**.
- xlWhole (1) – полное совпадение;
- xlPart (2) – частичное совпадение.
- xlByRows (1) – поиск по строкам;
- xlByColumns (2) – поиск по столбцам.
- xlNext (1) – поиск вперед;
- xlPrevious (2) – поиск назад.
- False (0) – поиск без учета регистра (по умолчанию);
- True (1) – поиск с учетом регистра.
- False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
- True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
В некоторых ситуациях может потребоваться поиск на основе значений в двух столбцах (рис. 122.1). Таблица содержит марки и модели автомобилей и соответствующие коды для каждого из них. Метод, описанный здесь, позволяет искать значение, основываясь на марке и модели автомобиля.
Рис. 122.1. В таблице выполняется поиск с использованием информации из двух столбцов (D и Е)
Лист использует именованные диапазоны, как показано в табл. 122.1.
| Диапазон | Название |
|---|---|
| F2:F12 | Код |
| B1 | Марка |
| B2 | Модель |
| D2:D12 | Диапазон1 |
| E2:E12 | Диапазон2 |
Следующая формула массива отображает соответствующий код для марки и модели автомобиля:
=ИНДЕКС(Код;ПОИСКПОЗ(Марка&Модель;Диапазон1&Диапазон2;0))
При вводе формулы массива нажмите Ctrl+Shift+Enter (а не просто Enter).
Формула объединяет содержимое диапазонов Марка и Модель, а затем находит этот текст в массиве, состоящем из соответствующего объединенного текста, в диапазонах Диапазон1 и Диапазон2.
Альтернативный подход заключается в создании новой таблицы поиска из двух столбцов, как показано на рис. 122.2. Таблица содержит ту же информацию, что и исходная таблица, но столбец Н включает объединенные данные из столбцов D и Е.
Рис. 122.2. Избегайте поиска по двум столбцам, комбинируя два столбца в один
После создания новой таблицы вы можете использовать простую формулу для выполнения поиска:
=ВПР(Марка&Модель;H2:I12;2)
Читайте также: