Функция поиска в Excel

Обновлено: 06.07.2024

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

Поиск в электронной таблице Microsoft Excel может показаться простым. В то время как Ctrl + F может помочь вам найти большинство вещей в электронной таблице, вы захотите использовать более сложные инструменты для поиска и извлечения данных на основе определенных значений. Мы поможем вам сэкономить массу времени с помощью нашего списка функций расширенного поиска.

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

1. Функция ВПР

Функция ВПР позволяет найти конкретное значение в столбце и извлечь значения из соответствующей строки в соседних столбцах. Это можно сделать двумя примерами: (1) найти фамилию сотрудника по его номеру или (2) найти номер телефона, указав фамилию.

Вот синтаксис функции:

[lookup_value] — это часть информации, которая у вас уже есть. Например, если вам нужно узнать, в каком состоянии находится город, это будет название города.
[table_array] позволяет указать ячейки, в которых функция будет искать искомые и возвращаемые значения. Выбирая диапазон, убедитесь, что первый столбец, включенный в массив, содержит искомое значение!
[col_index_num] – это номер столбца, содержащего возвращаемое значение.
[range_lookup] — это необязательный аргумент, который может принимать значения 1 или 0, хотя вы также можете ввести TRUE или FALSE. Если вы введете 1 или опустите этот аргумент, функция ищет приблизительное значение, но мы обнаружили, что это случайность. В приведенном ниже примере функция ВПР, ищущая значение 100, возвращает 90. Поиск меньшего значения, например 88, возвращает ошибку.

Вот формула, которую вы бы использовали:

Поскольку четвертая оценка находится в пятом столбце после фамилии, которую мы ищем, 5 является аргументом индекса столбца. Обратите внимание, что при поиске текста рекомендуется установить для параметра [range_lookup] значение 0. Без этого вы можете получить плохие результаты.

Вот результат:

Примечания к функции ВПР

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

Кроме того, имейте в виду, что функция ВПР всегда возвращает только одно значение. Был еще один учащийся с фамилией "Дэвидсон", но функция ВПР всегда возвращает результаты только для первой записи, без указания того, что имеется более одного совпадения.

2. Функция ГПР

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

Вот синтаксис функции:

[lookup_value] – это известное вам значение, для которого вы хотите найти соответствующее значение.
[table_array] – это ячейки, в которых вы хотите выполнить поиск.
[row_index_num] указывает строку, из которой будет получено возвращаемое значение.
[range_lookup] такой же, как и в функции ВПР, оставьте это поле пустым, чтобы получить ближайшее значение, если это возможно, или введите 0, чтобы искать только точные совпадения.

Мы будем использовать ту же таблицу, что и раньше. Вы можете использовать HLOOKUP, чтобы найти оценку для определенной строки. Вот как мы это сделаем:

Как вы можете видеть на изображении ниже, возвращается оценка:

Учащийся в ряду 6, Томас Дэвидсон, набрал 68 баллов за четвертый тест.

Примечания к HLOOKUP

Как и в случае с функцией ВПР, искомое значение должно находиться в первой строке массива таблиц. Это редко проблема с HLOOKUP, так как вы обычно будете использовать заголовок столбца для значения поиска. HLOOKUP также возвращает только одно значение.

3-4. Функции ИНДЕКС и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ – это две разные функции, но при их совместном использовании поиск в большой электронной таблице становится намного быстрее. У обеих функций есть недостатки, но, объединив их, мы задействуем сильные стороны обеих.

Во-первых, синтаксис обеих функций:

[массив] — это массив, в котором вы будете искать.
[row_number] и [column_number] можно использовать для сужения области поиска (мы рассмотрим это чуть позже).

[lookup_value] – это поисковый запрос, который может быть строкой или числом.
[lookup_array] — это массив, в котором Microsoft Excel будет искать поисковый запрос.
[match_type] — необязательный аргумент, который может принимать значения 1, 0 или -1.1 вернет наибольшее значение, которое меньше или равно вашему поисковому запросу. 0 вернет только ваш точный термин, а -1 вернет наименьшее значение, которое больше или равно вашему поисковому запросу.

Может быть непонятно, как мы будем использовать эти две функции вместе, поэтому я объясню это здесь. ПОИСКПОЗ принимает условие поиска и возвращает ссылку на ячейку. На изображении ниже видно, что при поиске фамилии «Дэвидсон» в столбце B функция ПОИСКПОЗ возвращает 2.

Индекс, с другой стороны, делает обратное: берет ссылку на ячейку и возвращает содержащееся в ней значение. Здесь вы можете видеть, что при запросе вернуть вторую строку столбца B функция ИНДЕКС возвращает «Дэвидсон», значение из строки 2.

Что мы собираемся сделать, так это объединить их, чтобы ПОИСКПОЗ возвращал ссылку на ячейку, а ИНДЕКС использовал эту ссылку для поиска значения в ячейке. Допустим, вы помните, что был студент по фамилии Таунсенд, и вы хотите узнать, какой у него был четвертый балл. Вот формула, которую мы будем использовать:

Вы заметите, что здесь для типа соответствия установлено значение 0. Когда вы ищете строку, это то, что вы захотите использовать. Вот что мы получаем, когда запускаем эту функцию:

Как видно из вставки, Ральф Таунсенд набрал 68 баллов в своем четвертом тесте. Это число появляется при запуске функции. Это может показаться не таким уж полезным, когда вы можете просто просмотреть несколько столбцов, но представьте, сколько времени вы бы сэкономили, если бы вам пришлось делать это 50 раз для большой электронной таблицы базы данных, содержащей несколько сотен столбцов!

5. Функция НАЙТИ

Статья о поиске чего-либо в Excel была бы неполной без функции НАЙТИ. Но это может быть не то, что вы ожидаете. Вы можете использовать функцию FIND Excel, чтобы определить положение строки текста в другой строке текста.

Допустим, мы хотели найти первое вхождение буквы "x" во фразе "Коричневая лиса перепрыгнула через забор". Это будет наша функция:

Полученное число представляет позицию запрошенной строки. Если вы ищете многосимвольную строку, скажем, мы запросили «лиса», результат будет указывать позицию первого символа запроса; в данном случае 11.

Примечания по ПОИСКУ

Как и функции ВПР, ГПР и другие, функция НАЙТИ идентифицирует только первое вхождение строки. Обратите внимание, что FIND чувствителен к регистру. Вы можете использовать его, чтобы НАЙТИ несколько символов. И хотя в нашем примере мы использовали буквы, он работает и с цифрами.

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

ПОИСК и ПОИСК

Мы не можем описать НАЙТИ, не упомянув функцию ПОИСК. По сути, это то же самое, что и НАЙТИ, за исключением того, что регистр не учитывается. Он также позволяет использовать подстановочные знаки, что означает, что вы можете искать совпадения, которые не являются точными.

Excel поддерживает три подстановочных знака:

Звездочка (*), которая заменяет любое количество символов, включая ноль.
Вопросительный знак (?), который может заменить один любой символ.
Тильда (~), которая превращает подстановочные знаки "звездочка" и "вопросительный знак" в буквальные символы, то есть отменяет их функцию подстановочных знаков. Вы бы использовали его как ~* или ~?.

6. Функция XLOOKUP

XLOOKUP — это новая функция, предназначенная для замены функции VLOOKUP. Как и функцию ВПР, вы можете использовать ее для поиска элементов в таблице или диапазоне путем поиска известного значения. Он отличается от ВПР тем, что позволяет искать значения, расположенные в столбцах слева или справа от запрашиваемого значения; с функцией ВПР вы можете найти данные только справа от запрашиваемого столбца.

Вот синтаксис функции:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

[lookup_value] — это значение, которое вы ищете; то есть ваш запрос.
[lookup_array] – массив или диапазон для поиска.
[return_array] — возвращаемый массив или диапазон. Это первое отличие от ВПР.
[if_not_found] – необязательный аргумент, возвращающий сообщение по вашему выбору, если совпадений не найдено.
[match_mode] – еще один необязательный аргумент, который позволяет найти точное совпадение (0), следующий меньший элемент (-1), следующий больший элемент (1) или совпадение с подстановочным знаком (2).
[search_mode] является необязательным и позволяет вам управлять порядком поиска. По умолчанию (1) поиск начинается с первого элемента. Вы также можете начать с последнего элемента (-1) и выполнить бинарный поиск, который зависит от сортировки lookup_array в порядке возрастания (2) или убывания (-2).

Возьмем наш пример с функцией ВПР и обратим порядок поиска. Это должно позволить нам найти счет второго студента по имени Дэвидсон. Вот формула:

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

На этот раз формула вернула оценку Томаса Дэвидсона, а не Эйдана Дэвидсона. Но он по-прежнему не может вернуть более одного результата.

Начнем поиск в Excel

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

Сегодня я покажу, как можно использовать функцию ПОИСК в Excel для поиска определенного текста в заданной строке.

Функция ПОИСК Excel (быстрый просмотр)

Загрузить практическую рабочую тетрадь

Функция ПОИСК в Excel: синтаксис и аргумент

Сводка

Возвращает номер символа, под которым первый найден определенный символ или текстовая строка, читая слева направо.
Поиск совпадения без учета регистра.
Подходит как для формул массива, так и для формулы без массива.
Доступно в Excel 2003.

Синтаксис

Синтаксис функции ПОИСК:

Аргумент

< td style="width: 20.7273%; text-align: center;">Необязательно

Аргумент	Обязательный или необязательный	Значение
find_text	Обязательно	Текст что ищут. Может быть отдельным текстом или массивом текстов.
within_text	Обязательный	Текстовое значение, в пределах которого ищется аргумент find_text. Может быть отдельным текстовым значением или массивом текстовых значений.
[start_num]	Позиция аргумента inside_text, с которой начинается поиск. Может быть одним числом или массивом чисел. По умолчанию 1.

Примечание:

Все три аргумента могут быть либо одним значением, либо массивом значений.
Если хотя бы один из аргументов является массивом, формула превратится в формулу массива, и вам нужно будет нажать Ctrl + Shift + Enter, чтобы ввести формулу.
Третий аргумент [start_num] является необязательным. По умолчанию – 1.

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

Возвращает номер символа, под которым впервые найден определенный символ или текстовая строка (find_text), читая слева направо.

Особые примечания

Функция ПОИСК также может искать номер в другом номере.

Например, формула ПОИСК(10,2510,1) возвращает 3, поскольку она находит 10 на 3-м месте числа 2510.

Он также может искать число, обернутое как текст внутри другого числа, или число внутри числа, обернутое как текст.

Например, формула ПОИСК("10",2510,1) и формула ПОИСК(10,"2510",1) вернут 3.

В случае текстов функция ПОИСК ищет совпадения без учета регистра.

Например, формула ПОИСК("БИОГРАФИЯ","Автобиография",1) вернет 5.

Функция ПОИСК в Excel: 3 примера

Попробуем рассмотреть несколько примеров использования функции ПОИСК в Excel.

1. Фильтрация имен из некоторых имен

Посмотрите на набор данных ниже. У нас есть идентификаторы и имена сотрудников некоторых сотрудников компании Mars Group.

Теперь мы извлечем имена сотрудников.

Очень просто. В первую ячейку нового столбца введите следующую формулу:

Затем дважды щелкните маркер заполнения, чтобы заполнить остальные ячейки.

Смотрите, мы извлекли все имена.

Объяснение формулы

ПОИСК(" ",C4,1) сообщает, где есть пробел (" ") в имени в ячейке C4.
LEFT(C4,SEARCH(" ",C4,1)-1) затем извлекает строку до пробела слева. Это первое имя, которое мы ищем. Подробнее см. в функции ВЛЕВО.

Формула	Вывод	Пояснение
=LEFT(C4,ПОИСК (” “,C4,1)-1)	Моррис	Извлекает строку слева вверх до того места, где в ячейке C4 есть пробел. Это обязательное имя.

2. Фильтрация книг определенного типа из списка книг

Теперь взгляните на этот новый набор данных.

У нас есть названия некоторых книг и их типы из магазина Kingfisher Bookstore.

Теперь мы попробуем выяснить для каждой книги, роман это или нет.

Просто. Введите эту формулу в первую ячейку нового столбца:

Затем дважды щелкните маркер заполнения, чтобы заполнить остальные ячейки.

Видите, по каждой книге мы выяснили, роман это или нет. «Да», если это так, «Нет», если это не так.

Объяснение формулы

Формула	Вывод	Пояснение
=IF(ISNUMBER(ПОИСК («Роман», C4,1))», «Да», «Нет»)	Да	Сначала ищет, есть ли текст «Роман» в книжном типе. Если есть, возвращает число. Если нет, возвращает ошибку. Затем преобразует числа в TRUE, а ошибки в FALSE. Наконец возвращает «Да» для ИСТИНА и «Нет» для ЛОЖЬ.

3. Использование формулы массива с функцией ПОИСК

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

На этот раз мы применим формулу ко всем ячейкам одновременно.

Попробуем одновременно извлечь имена из всех имен примера 1.

Мы должны применить формулу массива.

Формула будет такой:

Он возвращает имена из всех имен одновременно.

Объяснение формулы

Если разбить формулу НАЛЕВО(C4:C20,ПОИСК(" ",C4:C20,1)-1) , то мы увидим, что она состоит из 17 отдельных формул.

НАЛЕВО(C4,ПОИСК(" ",C4,1)-1)
НАЛЕВО(C5,ПОИСК(" ",C5,1)-1)
НАЛЕВО(C6,ПОИСК(" ",C6,1)-1)

Каждый из них извлекает имя из полных имен (см. пример 1).

Формула	Вывод	Пояснение
=LEFT(C4:C20,ПОИСК( ” “,C4:C20,1)-1)	Моррис

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

Распространенные ошибки функции ПОИСК

Заключение

Используя эти методы, вы можете использовать функцию ПОИСК в Excel для поиска определенного текста в строке. У вас есть вопросы? Не стесняйтесь спрашивать нас.

Дополнительная литература

Как использовать функцию TRIM в Excel (7 примеров)
использовать функцию ПОДСТАВИТЬ в Excel (4 примера)
Как использовать функцию ПРОПИСЬ в Excel (2 примера)
Использование функции MID в Excel (3 примера)
Как использовать функцию ВЛЕВО в Excel (4 примера)

Рифат Хассан

Здравствуйте! Добро пожаловать в мой профиль. Здесь я буду публиковать статьи, связанные с Microsoft Excel. Я страстный инженер-электрик со степенью бакалавра в области электротехники и электронной техники Бангладешского инженерно-технологического университета. Помимо академических занятий, я всегда люблю идти в ногу с революцией в технологиях, к которой мир стремится изо дня в день. Я прилежна, ориентирована на карьеру и готова дорожить знаниями на протяжении всей жизни.

Функция ПОИСК в Excel ищет текстовую строку в другой текстовой строке и возвращает ее позицию, если она найдена. Посмотрите это короткое видео, чтобы увидеть 3 примера использования ПОИСКА. Под видео есть письменные инструкции.

Используется для ПОИСКА

Функция ПОИСК ищет текстовую строку внутри другой текстовой строки и может:

Поиск текстовой строки в другой текстовой строке без учета регистра
Используйте подстановочные знаки в поиске
Укажите начальный номер в тексте поиска

Синтаксис ПОИСКА

Функция ПОИСК имеет следующий синтаксис:

ПОИСК(найти_текст,внутри_текста,начальный_номер)
- find_text — это текст, который вы ищете.
- within_text — это строка, в которой вы ищете.
- если start_num опущен, поиск начинается с первого символа
Ловушки ПОИСКА

У функции ПОИСК есть несколько ловушек:
- Функция ПОИСК возвращает позицию первой совпадающей строки независимо от регистра.
- Если вам нужен поиск с учетом регистра, используйте функцию ПОИСК в Excel
Пример 1. Поиск текста в строке

Используйте функцию ПОИСК для поиска текста в текстовой строке. В этом примере мы ищем один символ (введенный в ячейку B5) в текстовой строке в ячейке B2.

=ПОИСК(B5,B2)

Вы можете использовать ЕСЛИОШИБКА, чтобы обернуть функцию ПОИСК и отобразить сообщение, если результатом является ошибка. ЕСЛИОШИБКА доступна в Excel 2007 и более поздних версиях. В более ранних версиях можно использовать ЕСЛИ с ЕОШИБКОЙ.

=ЕСЛИОШИБКА(ПОИСК(B5,B2);"Не найдено")

Пример 2: ПОИСК с подстановочными знаками

Еще один способ проверить результаты ПОИСКА — использовать функцию ISNUMBER. Если строка найдена, результатом ПОИСКА является число, поэтому результатом ISNUMBER является ИСТИНА. Если текст не найден, ПОИСК возвращает ошибку, а ISNUMBER возвращает ЛОЖЬ.

Вы также можете использовать подстановочные знаки в аргументе find_text. * (звездочка) обозначает любое количество символов или отсутствие символов, а ? (вопросительный знак) представляет один символ.

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

=ISNUMBER(ПОИСК($E$2,B3))

Пример 3. Укажите начальный номер для ПОИСКА

Если ввести два знака минус (двойной унарный) перед функцией ISNUMBER, она вернет 1/0 вместо TRUE/FALSE. Затем функция СУММ в ячейке E2 может суммировать количество записей, в которых была найдена текстовая строка.

В этом примере город и род занятий показаны в столбце B. Мы хотим найти все занятия с текстовой строкой, введенной в ячейку E1. Формула в ячейке C2:

=–ISNUMBER(ПОИСК($E$1,B2))

Формула нашла строку, содержащую "банк", но одна из них находится в названии города, а не в роде занятий:

После каждого названия города есть вертикальная черта, поэтому мы можем добавить для него ПОИСК. Его положение можно использовать в качестве аргумента start_number в основном ПОИСКЕ, поэтому при поиске города будут игнорироваться.

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

=–ISNUMBER(ПОИСК($E$1,B2,ПОИСК("|",B2)))

Получить образцы файлов

Чтобы ознакомиться с формулами, используемыми в этих примерах, скачайте пример рабочей книги функции ПОИСК. Файл заархивирован и имеет формат Excel xlsx, без макросов.

Используйте функцию ПОИСК в Excel, чтобы найти позицию подстроки в строке. Функция ПОИСК не учитывает регистр и поддерживает подстановочные знаки.
<р>1. Приведенная ниже функция ПОИСК находит строку "ro" в позиции 4.

<р>2. Функция ПОИСК нечувствительна к регистру. Приведенная ниже функция ПОИСК находит строку «excel», «Excel», «EXCEL», «EXcel» и т. д.

Функция ПОИСК поддерживает подстановочные знаки. Знак вопроса (?) соответствует ровно одному символу. Звездочка (*) соответствует последовательности из нуля или более символов.
<р>3. Приведенная ниже функция ПОИСК находит строку «2019», «2018», «2017» и т. д.

<р>4. Приведенная ниже функция ПОИСК находит строку «Microsoft Excel 2019», «Microsoft Word 2019», «Microsoft Access 2019» и т. д.

Функция ПОИСК имеет третий необязательный аргумент. Вы можете использовать этот аргумент, чтобы указать позицию, считая слева, с которой вы хотите начать поиск.
<р>5. Приведенная ниже функция ПОИСК находит строку "o" в позиции 5.

<р>6. Приведенная ниже функция ПОИСК начинает поиск с позиции 6 и находит строку "o" в позиции 7.

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