Контекстный поиск в excel vba

Обновлено: 05.07.2024

У меня возникло затруднение при решении следующей задачи. Для быстрой обработки данных мне необходимо в макросе (Excel) задействовать встроенный механизм поиска - тот, что запускается через иконку в виде бинокля. Поскольку этот механизм очень проворен - находит заданные данные значительно быстрее перебора ячеек, устроенного конструкцией типа "If Cells(. . ) = . Then. ". Через Интернет нашёл подходящий фрагмент (привожу здесь уже несколько переделанным под свою конкретную задачу):

При отработке этой процедуры мне нужно, чтобы поиск данных производился по всему заданному диапазону (Columns("A:O")), и не останавливался бы при нахождении первого же результата, совпадающего с заданной (Артикул). Как это происходит при поиске средствами встроенной функции поиска: ведь там после того, как находится первая ячейка с нужным содержимым, процедура не отключается, а приостанавливается и позволяет продолжить поиск далее по листу с того места, на котором остановилась.

Здесь же вышеприведённая процедура задумана так, чтобы поиск прошёл непрерывно по всему заданному диапазону с накоплением найденных данных (с их трансформацией через пересчёт) в переменной "ИндНомера". То есть, после того, как в пределах заданного диапазона найдена первая ячейка с искомым содержимым, это содержимое трансформируется и запоминается в переменной "ИндНомера", а процесс возвращается к дальнейшему поиску. Но проблема в том, что этот самый дальнейший поиск начинается вновь с начала заданного диапазона, а не с места, где было найдено первое совпадающее значение ячейки, невзирая на то, что в строках "Range("A1").Select" и "ActiveCell.Offset(Строка - 1, Колонка - 1).Activate" я надеялся задать место продолжения поиска: увы, это не работает. В результате процедура работает бесконечно, всё время повторяя один и тот же фрагмент, связанный с первым находимым значением, совпадающим с заданием. И переменная "ИндНомера" быстро разрастается, пополняясь одним и тем же фрагментом.

Знаю, что есть ещё функция FindNext. Пробовал использовать и её, да, видимо, неверно обставлял в коде должными атрибутами или не обставлял вообще - словом, она у меня не заработала.

Я надеюсь, что понятно объяснил ситуацию. Не найдётся ли среди вас специалист, знающий, как заставить эту процедуру продолжать поиск с места, в котором было найдено очередное подходящее значение? Как это сделать?

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Правильная организация поиска на листе Excel
Добрый день! Бьюсь с проблемой второй день. Суть в чем: Макрос сканирует лист входящей книги.

Excel Wb. Как создать меню для быстрого перехода на листе?
Проблема такова. Лист визуально разделен на области. В каждой такой области производятся свои.

Копирование данных на листе MS Excel
Добрый день! Открываю шаблон excell файла: fil:=ExtractFilePath(Application.ExeName)+'XD.xls';.

Пересчет данных на листе Excel
Извините за тупость, но можно ли сделать так, чтобы данные на листе Excel пересчитывались не на.

Ну что же вы, назвали решение FindNext, а не нажали F1.
Пример из справки с малой правкой:
Спасибо большое, Димит, за отзывчивость и помощь - очень кстати. Сейчас буду разбираться и тестировать Ваше предложение. Очень надеюсь, что моих куриных мозгов хватит, чтобы Вам более не надоедать с этой проблемой, но если что. похнычу ещё. Есть, Димит! Действительно, проблема была именно в этой "с"! Я её заменил на переменную "Результат" - и всё заработало!. Ещё раз огромное Вам спасибо - Вы мне очень помогли! Удачи Вам! Всем-всем прочим! Рекомендую: Димит - похоже, грамотный спец! Только не сильно на него наседайте. Спасибо, Димит, ещё раз! можете прислать полный код, а то так в собранном из кусков у меня не сработал)

Можно и без FindNext обойтись. Ведь Find ищет с первой ячейки после указанной.
Соответственно алгоритм такой:
1) Организуем массив (скажем строковый) Dim findaddr() as String
2) Поиск с помощью Find - Увеличиваем индекс массива на единицу используя Redim Preserve findaddr(Ubound(findaddr)+1)
3) Записали значение в массив используем Find указав адрес записанной ячейки, поиск продолжится со следующей после неё
4) Записываем следующее значение и т.д.
5) Повторяем шаги 2-5 до окончания диапазона

Изучите справку exel насчет Find там много интересных параметров

Добавлено через 21 минуту
К предыдущему ответу конечно лучше в качестве индекса массива использовать отдельную переменную
Улучшится читаемость кода, ускориться выполнение - не будет все время вызывать Ubound - он вообще не потребуется.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:

Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).

Способ 1. Power Query

Само-собой, сначала превращаем наши таблицы в динамические ("умные") с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) , даём им имена (например Марки и Запчасти ) и загружаем по очереди в редактор Power Query, выбрав на вкладке Данные - Из таблицы/диапазона (Data - From Table/Range) . Если у вас старые версии Excel 2010-2013, где Power Query установлена как отдельная надстройка, то нужная кнопка будет на вкладке Power Query. Если у вас совсем новая версия Excel 365, то кнопка Из таблицы/диапазона называется там теперь С листа (From Sheet) .

После загрузки каждой таблицы в Power Query возвращаемся обратно в Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. - Только создать подключение (Home - Close & Load - Close & Load to. - Only create connection) .

Теперь создадим дубликат запроса Запчасти, щёлкнув по нему правой кнопкой мыши и выбрав команду Дублировать запрос (Duplicate query) , затем переименуем получившийся запрос-копию в Результаты и дальше будем работать уже с ним.

Логика действий следующая:

Способ 2. Формулы

Если у вас версия Excel 2016 или новее, то нашу проблему можно весьма компактно и изящно решить с помощью новой функции ОБЪЕДИНИТЬ (TEXTJOIN) :

Логика работы этой формулы проста:

Сравнение быстродействия и буферизация запроса Power Query для ускорения

Для тестирования быстродействия возьмем в качестве исходных данных таблицу из 100 000 описаний запчастей. На ней получаем следующие результаты:

Время пересчета формулами (Способ 2) - 9 сек. при первом копировании формулы на весь столбец и 2 сек. при повторном (сказывается буферизация, видимо).
Время обновления запроса Power Query (Способ 1) гораздо хуже - 110 сек.

Само-собой, многое зависит от "железа" отдельно взятого ПК и установленной версии Office и обновлений, но общая картина, думаю, понятна.

Для ускорения запроса Power Query давайте буферизуем таблицу-справочник Марки, т.к. она у нас не меняется в процессе выполнения запроса и постоянно пересчитывать её (как это де-факто делает Power Query) не нужно. Для этого используем функцию Table.Buffer из встроенного в Power Query языка М.

Для этого откроем запрос Результаты и на вкладке Просмотр нажмём на кнопку Расширенный редактор (View - Advanced Editor) . В открывшемся окне добавим строку с новой переменной Марки2, которая будет буферизованной версией нашего справочника автопроизводителей и используем эту новую переменную далее в следующей команде запроса:

После такой доработки скорость обновления нашего запроса возрастает почти в 7 раз - до 15 сек. Совсем другое дело :)

Я собираюсь вам рассказать о фантастически полезном и эффектном приёме. Это одна из самых интересных вещей, которую мне доводилось видеть в Excel. Кроме того, она достаточно легко реализуется. Если вы разберётесь, как она работает, то вы сразу узнаете об Excel необычно много. Я постараюсь рассказать всё максимально подробно, последовательно и внятно.

Выпадающий список с контекстным поиском

Итак, речь пойдёт о выпадающем списке (так называемый combo box), в который встроена возможность динамического поиска по подстроке, которую пользователь вводит с клавиатуры. Посмотрите пример, в котором мы имеем топ 300 крупнейших городов России. На анимированной иллюстрации видно, как мы динамически сужаем список выбора, вводя подстроку "кр" или "ниж", экономя огромное количество времени. Более того, список меняется после ввода каждого нового символа! Выглядит чрезвычайно привлекательно и профессионально, не так ли? Давайте разбираться, как это устроено.

Файл примера

Пошаговая инструкция

Предварительные замечания

В файле примера выпадающий список с поиском реализован сразу в двух вариантах: для обычного диапазона (лист Range ) и для умной таблицы (лист Table ). Мы будим эти варианты обсуждать одновременно, отмечая их различия.

Шаг 1. Готовим таблицу для списка

Подготовьте таблицу с четырьмя колонками: Город (или то, что вам нужно), Статус , Индекс , Фильтр . Заполните столбец Город значениями. В остальных трёх колонках будут формулы, которые мы обсудим ниже. Я всем рекомендую использовать умную таблицу, так как это значительно проще.

Шаг 2. Формулы для столбца Статус

На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F ). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.

$B$2 - ячейка, с которой будет связан выпадающий список (добавляется на шаге 6). Что значит связано? Всё, что вы введёте в выпадающий список, тут же отразится в ячейке B2 .

Формула ПОИСК вернёт ошибку, если содержимое B2 не найдено в $E2 . ЕОШИБКА перехватит ошибку и вернёт ИСТИНА, если действительно была ошибка, и - ЛОЖЬ, если строка таки была найдена. Функция НЕ делает из истины ложь и наоборот (инверсирует результат). Таким образом, мы получим в этом столбце ИСТИНА, если подстрока найдена в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки столбца Статус имеют значения ИСТИНА, когда мы не ввели ещё ничего в B2 .

= НЕ( ЕОШИБКА ( ПОИСК ( $B$2 ; $E2 ) ) )

=NOT( ISERROR( SEARCH( $B$2; $E2) ) )

= НЕ ( ЕОШИБКА ( ПОИСК ( $B$2 ; [@ Город ]) ) )

=NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )

[@ Город ] - на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё - тоже самое.

Шаг 3. Формула для столбца Индекс

Если B2 содержит подстроку поиска, то в столбце Статус не все ячейки примут значение ИСТИНА. Статус ИСТИНА будет только там, в чьи названия городов входит соответствующая подстрока. А в столбце Индекс мы рассчитываем номер по порядку для всех строк, которые содержат искомую подстроку. Например, на рисунке ниже B2 содержит "ни", что заставляет столбец Статус быть истинным у строк с городами Нижний Новгород , Калининград , Магнитогорск и т.д., а в столбце Индекс мы начинаем считать факты срабатываний в F : Нижний Новгород - первое срабатывание, Калининград - второе и так далее.

Функция ЕСЛИ отсекает все значения в F , которые не равны ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество значений ИСТИНА в F .

= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); "")

=IF( $F2; COUNTIF( $F$2:$F2; TRUE ); "")

= ЕСЛИ ( [@ Статус ]; СЧЁТЕСЛИ ( $F$2 :[@ Статус ]; ИСТИНА ); "")

=IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); "")

Обратите внимание, что диапазон условия в СЧЁТЕСЛИ введен скользящий - вторая координата не закреплена - и во время протягивания она растёт пропорционально таблице. За счёт этого трюка мы получаем механизм подсчёта значения ИСТИНА. Например, 6-я строка будет подсчитывать ИСТИНУ по диапазону $F$2:$F6 (там одно значение - от Нижнего Новгорода ), а 41-я строка будет подсчитывать ИСТИНУ уже по диапазону $F$2:$F41 (а там уже 2 значения - от Нижнего Новгорода и от Калининграда ). Вот суть механизма. Это полезный приём, который стоит запомнить.

Шаг 4. Формула для столбца Фильтр

Теперь наша задача, опираясь на столбец Индекс , сформировать в столбце H отфильтрованный список городов, который необходимо показывать в выпадающем списке.

= ЕСЛИОШИБКА( ИНДЕКС ( стлГород ; ПОИСКПОЗ ( ЧСТРОК ( $G$2:$G2 ); стлИндекс ; 0) ); "")

=IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); "" )

Обратите внимание на динамический именованный диапазон стлГород и стлИндекс, которые мы вынуждены создавать для случая диапазона, чтобы придать решению должный уровень универсальности. Техника, по которой созданы эти именованные диапазоны разобрана тут.

= ЕСЛИОШИБКА ( ИНДЕКС ( [ Город ]; ПОИСКПОЗ ( ЧСТРОК ( $G$2 :[@ Индекс ]); [ Индекс ]; 0) ); "")

=IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); "" )

Не путайте: [ Индекс ] - ссылка на весь столбец, а [@ Индекс ] - ссылка на ячейку из этого столбца в текущей строке. Никакие дополнительные именованные диапазоны нам создавать нет никакой необходимости, так как мы пользуемся встроенным в умные таблицы сервисом при ссылке на столбцы.

Формула ЧСТРОК ( $G$2:$G2 ) используется для генерации последовательных номеров от 1 (для второй строки) до N (в строке N+1), равному количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, а формула ЧСТРОК возвращает его высоту в строках.

Формула ПОИСКПОЗ ищет номер реальной строки, содержащий соответствующий индекс. Например, в столбце Фильтр мы видим Магнитогорск на третьей позиции, но в реальности он взят из E45 , так как в G45 стоит цифра 3, которую мы и нашли через ПОИСКПОЗ . То есть ПОИСКПОЗ сказал нам, что Магнитогорск находится в 45-й строке, а извлекли мы его оттуда уже при помощи формулы ИНДЕКС .

Если же при извлечении возникает ошибка (текущая строка находится ниже строки N+1), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА .

Метод 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.

Читайте также: