Команда find в excel

Обновлено: 02.07.2024

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

Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.

Синтаксис

выражение.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

выражение: переменная, представляющая объект Range.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
What	Обязательный	Variant	Искомые данные. Может быть строкой или любым типом данных Microsoft Excel.
After	Необязательный	Variant	Ячейка, после которой нужно начать поиск. Соответствует положению активной ячейки, когда поиск выполняется из пользовательского интерфейса.

Обратите внимание, что параметр After должен быть одной ячейкой в диапазоне. Помните, что поиск начинается после этой ячейки; указанная ячейка не входит в область поиска, пока метод не возвращается обратно в эту ячейку.

Возвращаемое значение

Объект Range, представляющий первую ячейку, в которой обнаружены требуемые сведения.

Примечания

Этот метод возвращает значение Nothing, если совпадения не найдены. Метод Find не влияет на выделенный фрагмент или активную ячейку.

Параметры для аргументов LookIn, LookAt, SearchOrder и MatchByte сохраняются при каждом использовании этого метода. Если вы не укажете значения этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет параметры в диалоговом окне Найти, а изменение параметров в диалоговом окне Найти приводит к изменению сохраненных значений, которые используются, если опустить аргументы. Чтобы избежать проблем, устанавливайте эти аргументы явным образом при каждом использовании этого метода.

Для повторения поиска можно использовать методы FindNext и FindPrevious.

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

Чтобы найти ячейки, отвечающие более сложным шаблонам, используйте инструкцию For Each. Next с оператором Like. Например, следующий код выполняет поиск всех ячеек в диапазоне A1:C5, где используется шрифт, имя которого начинается с букв Cour. Когда Microsoft Excel находит соответствующее значение, шрифт изменяется на Times New Roman.

Примеры

В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие значение 2, и изменить значение ячейки на 5. Таким образом, значения 1234 и 99299 содержали 2 и оба значения ячеек станут 5.

В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие подстроку "abc", и изменить ее на "xyz".

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

В этой статье описаны синтаксис формулы и использование функций НАЙТИ и НАЙТИБ в Microsoft Excel.

Описание

Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую и возвращают начальную позицию искомой строки относительно первого знака второй строки.

Эти функции могут быть доступны не на всех языках.

Функция НАЙТИ предназначена для языков с однобайтовой кодировкой, а функция НАЙТИБ — для языков с двухбайтовой кодировкой. Заданный на компьютере язык по умолчанию влияет на возвращаемое значение указанным ниже образом.

Функция НАЙТИ при подсчете всегда рассматривает каждый знак, как однобайтовый, так и двухбайтовый, как один знак, независимо от выбранного по умолчанию языка.

Функция НАЙТИБ при подсчете рассматривает каждый двухбайтовый знак как два знака, если включена поддержка языка с БДЦС и такой язык установлен по умолчанию. В противном случае функция НАЙТИБ рассматривает каждый знак как один знак.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций НАЙТИ и НАЙТИБ описаны ниже.

Искомый_текст — обязательный аргумент. Текст, который необходимо найти.

Просматриваемый_текст — обязательный аргумент. Текст, в котором нужно найти искомый текст.

Начальная_позиция — необязательный аргумент. Знак, с которого нужно начать поиск. Первый знак в тексте "просматриваемый_текст" имеет номер 1. Если номер опущен, он полагается равным 1.

Замечания

Функции НАЙТИ и НАЙТИБ работают с учетом регистра и не позволяют использовать подстановочные знаки. Если необходимо выполнить поиск без учета регистра или использовать подстановочные знаки, воспользуйтесь функцией ПОИСК или ПОИСКБ.

Если в качестве аргумента "искомый_текст" задана пустая строка (""), функция НАЙТИ выводит значение, равное первому знаку в строке поиска (знак с номером, соответствующим аргументу "нач_позиция" или 1).

Искомый_текст не может содержать подстановочные знаки.

Аргумент "нач_позиция" можно использовать, чтобы пропустить нужное количество знаков. Предположим, например, что для поиска строки "МДС0093.МесячныеПродажи" используется функция НАЙТИ. Чтобы найти номер первого вхождения "М" в описательную часть текстовой строки, задайте значение аргумента "нач_позиция" равным 8, чтобы поиск в той части текста, которая является серийным номером, не производился. Функция НАЙТИ начинает со знака 8, находит искомый_текст в следующем знаке и возвращает число 9. Функция НАЙТИ всегда возвращает номер знака, считая от левого края текста "просматриваемый_текст", а не от значения аргумента "нач_позиция".

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция INDEX (ИНДЕКС) в Excel используется для получения данных из таблицы, при условии что вы знаете номер строки и столбца, в котором эти данные находятся.

Например, в таблице ниже, вы можете использовать эту функцию для того, чтобы получить результаты экзамена по Физике у Андрея, зная номер строки и столбца, в которых эти данные находятся.

Что возвращает функция

Возвращает данные из конкретной строки и столбца табличных данных.

Синтаксис

Аргументы функции

Дополнительная информация

Примеры использования функции ИНДЕКС в Excel

Пример 1. Ищем результаты экзамена по физике для Алексея

Предположим, у вас есть результаты экзаменов в табличном виде по нескольким студентам:

Для того, чтобы найти результаты экзамена по физике для Андрея нам нужна формула:

В формуле мы определили аргумент диапазона данных, где мы будем искать данные $B$3:$E$9. Затем, указали номер строки “3”, в которой находятся результаты экзамена для Андрея, и номер колонки “2”, где находятся результаты экзамена именно по физике.

Пример 2. Создаем динамический поиск значений с использованием функций ИНДЕКС и ПОИСКПОЗ

Не всегда есть возможность указать номера строки и столбца вручную. У вас может быть огромная таблица данных, отображение данных которой вы можете сделать динамическим, чтобы функция автоматически идентифицировала имя или экзамен, указанные в ячейках, и дала правильный результат.

Пример динамического отображения данных ниже:

Для динамического отображения данных мы используем комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).

Вот такая формула поможет нам добиться результата:

В формуле выше, не используя сложного программирования, мы с помощью функции MATCH (ПОИСКПОЗ) сделали отображение данных динамическим.

Она сканирует имена студентов и определяет значение поиска ($G$4 в нашем случае). Затем она возвращает номер строки для поиска в наборе данных. Например, если значение поиска равно Алексей, функция вернет “1”, если это Максим, оно вернет “4” и так далее.

Она сканирует имена объектов и определяет значение поиска ($H$3 в нашем случае). Затем она возвращает номер столбца для поиска в наборе данных. Например, если значение поиска Математика, функция вернет “1”, если это Физика, функция вернет “2” и так далее.

Пример 3. Создаем динамический поиск значений с использованием функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) и выпадающего списка

На примере выше мы вручную вводили имена студентов и названия предметов. Вы можете сэкономить время на вводе данных, используя выпадающие списки. Это актуально, когда количество данных огромное.

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

Используя такой подход, вы можете создать удобный дашборд, например для учителя. Ему не придется заниматься фильтрацией данных или прокруткой листа со студентами, для того чтобы найти результаты экзамена конкретного студента, достаточно просто выбрать имя и результаты динамически отразятся в лаконичной и удобной форме.

Для того, чтобы осуществить динамическую подстановку данных с использованием функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) и выпадающего списка, мы используем ту же формулу, что в Примере 2:

Единственное отличие, от Примера 2, мы на месте ввода имени и предмета создадим выпадающие списки:

Выбираем ячейку, в которой мы хотим отобразить выпадающий список с именами студентов;
Кликаем на вкладку “Data” => Data Tools => Data Validation;
В окне Data Validation на вкладке “Settings” в подразделе Allow выбираем “List”;
В качестве Source нам нужно выбрать диапазон ячеек, в котором указаны имена студентов;
Кликаем ОК

Теперь у вас есть выпадающий список с именами студентов в ячейке G5. Таким же образом вы можете создать выпадающий список с предметами.

Пример 4. Использование трехстороннего поиска с помощью INDEX (ИНДЕКС) / MATCH (ПОИСКПОЗ)

Функция INDEX (ИНДЕКС) может быть использована для обработки трехсторонних запросов.

Что такое трехсторонний поиск?

В приведенных выше примерах мы использовали одну таблицу с оценками для студентов по разным предметам. Это пример двунаправленного поиска, поскольку мы используем две переменные для получения оценки (имя студента и предмет).

Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Итоговый экзамен».

Вот пример трехстороннего поиска:

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

Для таких расчетов нам поможет формула:

Давайте разберем эту формулу, чтобы понять, как она работает.

По всем вышеприведенным примерам мы использовали первый синтаксис, но для трехстороннего поиска нам нужно использовать второй синтаксис.

Рассмотрим каждую часть формулы на основе второго синтаксиса.

Уверен, что теперь вы подробно изучили работу функции INDEX (ИНДЕКС) в Excel!

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