Excel поиск в матрице
В данной статье показаны 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 (по матрице). ВПР и ПОИСКПОЗ
Предположим, нам задан месяц, город и зависящая от них стоимость продукта. Необходимо найти соответствующее им значение в большом двухмерном массиве/матрице/таблице и вывести его в ячейку, формулой. По сути нам нужно вернуть значение на пересечении нужных строки и столбца таблицы. Поиск в двумерном массиве Excel готовыми функциями не сделаешь — СУММЕСЛИ или просто ВПР здесь уже не подходит. Я использую конструкцию из функций ВПР и ПОИСКПОЗ. Как ее правильно записать смотрим ниже.
Конструкция здесь довольно нетривиальная. Поэтому рекомендую ознакомиться с принципами работы функции ВПР и ПОИСКПОЗ перед прочтением статьи ниже, особенно если не встречались с такими функциями ранее.
Пример
Чтобы было было понятнее, начнем с примера.
Имеется таблица по городам и стоимость услуг, стоимости зависят от месяца.
Необходимо найти стоимость для третьего месяца по городу Краснодару или найти Sij, если по умному. Как будем делать?
Поиск в двумерном массиве Excel. Как записать формулу
Как мы обсудили, готовой формулы в Excel нет. Поэтому создадим комбинацию двух формул. В любой ячейки запишем:
Где столбец G — искомый город (Краснодар), а столбец H — месяц (третий). При записи можно использовать и просто одну ячейку, например, G4 и H4
Как видите, формулу на рисунке (с использованием столбцов) можно продлить ниже в другие ячейки.
Описание конструкции формулы:
- Функция ВПР осуществляет поиск нужного нам значения (Города, синий столбец G) в большой таблице $B:$E (отмечена зеленым). Для этого нам необходимо указать в каком же именно столбце этой таблицы ВПРу искать город — втором, третьем или четвертом (C, D, E)
- Номер этого столбца найдем при помощи функции ПОИСКПОЗ, которая находит номер элемента в массиве. А точнее найдем номер значения фиолетового столбца H (месяца) в массиве $B$2:$E$2. Тем самым мы определили, что ВПР надо использовать третий столбец.
- Четвертый аргумент оставляем пустым (после последней «;»), т.к. по умолчанию можно оставить ЛОЖЬ или пусто. Подробнее здесь.
Обратите внимание! Нужно обязательно выполнить два условия, чтобы формула считалась правильно!
— Ширина массива $B$2:$E$2 в ПОИСКПОЗ и ширина таблицы $B:$E должны совпадать.
— Данные в массиве для ПОИСКПОЗ ($B$2:$E$2) должны быть отсортированы от большего к меньшему или от А до Я!
Коллеги, на днях меня попросили сделать файл для поиска списка определенных слов в другом списке. Самом собой автоматизированно. Причем во втором списке значения слов не полностью совпадают со значениям в первом списке….. Стоп. Сложное пояснение, нужен пример: найти слова Иван, Петр и далее по списку в столбце ФИО Иванов Сергей, Петр Сергеев и так далее (ФИО может быть записано в любой последовательности, т.е. осуществить поиск по всей длине значения/строки). Итак, как организовать Поиск слов в Excel, да еще и по матрице строк и с заменой? Циклом в макросе ? Правильно, но задача была сделать именно формулой. Дополнительно в найденном значении провести перестановку (имя должно быть на первом месте) и заменить искомое значение на правильное. Делюсь решением этой интересной задачи.
Начнем с самого начала.
Пример задачи. Поиск слов в Excel
Дан список имен, 10 штук
И список фамилий с именем (до 1000 строк), назовем ФИ
Необходимо
- Найти строки ФИ где присутствуют заданные слова
- Отредактировать найденные строки ФИ, чтобы имя стояло на первом месте.
Поиск слов в Excel. Решение
Сначала немного теории. Чтобы найти слово в какой-либо строке, нужно использовать формулы ПОИСК или НАЙТИ. Подробнее можно прочитать здесь
Excel без проблем может найти слово в строке, но вот проверить группу слов в группе значений, уже сложновато из-за двумерной структуры (матрицы).
Т.е на отдельный лист создаем матрицу, где построчно будут ФИ (т.к. их больше), а по столбцам — имена. Причем имена необходимо транспонировать, но не просто так, а еще и с формулой массива :))
Пример матрицы показан на рисунке.
Формула для расчета матрицы и замены
Затем сводим данные в отдельный столбец, в нашем случае L и передаем их ВПР на лист ФИ.
Итого
Получилось то, что вы видите на картинке в самом начале. А так же прикладываю файл с полными расчетами.
В общем, я нашел такой нетривиальный способ. Кому есть что сказать на этот счет, прошу не молчать! ;) Макросы не предлагать, задача не в этом ;)
В этом примере мы объясним вам, как искать значения в двумерном диапазоне. Мы будем использовать функции MATCH (ПОИСКПОЗ) и INDEX (ИНДЕКС).
На рисунке ниже вы видите график продаж различных видов мороженого в каждом месяце.
-
Чтобы найти номер строки Feb (февраль), используйте функцию MATCH (ПОИСКПОЗ):
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Подскажите, пожалуйста, есть ли решение такой задачи. Во вложении пример. Есть матрица по горизонтале первое условие, по вертикале второе. Нужно найти сумму всех значений в матрице по двум условиям. Например если первое условие 5 и второе условие 5, то ответ будет 576. И есть ли решение этой задачи без использования массивов (ctrl+shift+enter). Спасибо.
Подскажите, пожалуйста, есть ли решение такой задачи. Во вложении пример. Есть матрица по горизонтале первое условие, по вертикале второе. Нужно найти сумму всех значений в матрице по двум условиям. Например если первое условие 5 и второе условие 5, то ответ будет 576. И есть ли решение этой задачи без использования массивов (ctrl+shift+enter). Спасибо. Bregra
Подскажите, пожалуйста, есть ли решение такой задачи. Во вложении пример. Есть матрица по горизонтале первое условие, по вертикале второе. Нужно найти сумму всех значений в матрице по двум условиям. Например если первое условие 5 и второе условие 5, то ответ будет 576. И есть ли решение этой задачи без использования массивов (ctrl+shift+enter). Спасибо. Автор - Bregra
Дата добавления - 25.05.2017 в 17:40
Механизм подсчета: сумма всех значений, если первое условие 5 и второе условие 5, т. е. нужно найти перекрестие в матрице по двум условиям. Спасибо за внимание и беспокойство, но ответ найден. Если кому то интересно то ответ (вроде ответ можно найти только через массивы): sum( if ( (1 столбец = 1 условие)*(1 строка= 2 условие); диапозон поиска;0))
Механизм подсчета: сумма всех значений, если первое условие 5 и второе условие 5, т. е. нужно найти перекрестие в матрице по двум условиям. Спасибо за внимание и беспокойство, но ответ найден. Если кому то интересно то ответ (вроде ответ можно найти только через массивы): sum( if ( (1 столбец = 1 условие)*(1 строка= 2 условие); диапозон поиска;0)) Bregra
Название темы изменила сама.
Вам замечание за нарушение п.2 Правил форума и игнорирование замечания модератора
По теме: Обычная (немассивная) формула
Название темы изменила сама.
Вам замечание за нарушение п.2 Правил форума и игнорирование замечания модератора
По теме: Обычная (немассивная) формула
Читайте также: