Поиск функция эксель: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Найти и заменить в Excel

Поиск и замена данных – одна из часто применяемых операций в Excel. Используют даже новички. На ленте есть большая кнопка.

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.

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

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.

С помощью дополнительных параметров поиск в Excel может заиграть новыми красками в прямом смысле слова. Так, искать можно не только заданное число или текст, но и формат ячейки (залитые определенным цветом, имеющие заданные границы и т.д.).

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

По умолчанию (если не лезть в параметры) поиск происходит только на активном листе. Для повторения поиска на другом листе все действия нужно проделать еще раз. А если таких листов много, то поиск данных может отнять немало времени. Однако если выбрать пункт Книга, то поиск произойдет сразу по всем листам активной книги. Выгода очевидна.

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т. е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

Функция поиска и замены при правильном использовании заменяет часы работы неопытного пользователя. Настоятельно рекомендую использовать все вышеизложенное. Если что-то не ищется в ваших данных или наоборот, выдает слишком много лишних ячеек, то попробуйте уточнить поиск с помощью подстановочных символов «*» и «?» или настраиваемых параметров поиска. Важно понимать, что если вы ничего не нашли, это еще не значит, что там этого нет.

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

Поделиться в социальных сетях:

Поиск в Эксель: способы нахождения нужного слова

Основное назначение офисной программы Excel – осуществление расчётов. Документ этой программы (Книга) может содержать много листов с длинными таблицами, заполненными числами, текстом или формулами. Автоматизированный быстрый поиск позволяет найти в них необходимые ячейки.

Содержание

1. Простой поиск
2. Расширенный поиск
3. Разновидности поиска
4. Поиск совпадений
5. Фильтрация
6. Видео: Поиск в таблице Excel

Простой поиск

Чтобы произвести поиск значения в таблице Excel, необходимо на вкладке «Главная» открыть выпадающий список инструмента «Найти и заменить» и щёлкнуть пункт «Найти». Тот же эффект можно получить, используя сочетание клавиш Ctrl + F.

В простейшем случае в появившемся окне «Найти и заменить» надо ввести искомое значение и щёлкнуть «Найти всё».

Как видно, в нижней части диалогового окна появились результаты поиска. Найденные значения подчёркнуты красным в таблице. Если вместо «Найти все» щёлкнуть «Найти далее», то сначала будет произведён поиск первой ячейки с этим значением, а при повторном щелчке – второй.

Аналогично производится поиск текста. В этом случае в строке поиска набирается искомый текст.

Если данные или текст ищется не во всей экселевской таблице, то область поиска предварительно должна быть выделена.

Расширенный поиск

Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3???. Подстановочный знак «?» заменяет собой любой другой.

Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.

Можно удовольствоваться большинством полученных результатов, игнорируя неправильные. Но функция поиска в эксель 2010 способна работать гораздо точнее. Для этого предназначен инструмент «Параметры» в диалоговом окне.

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

Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).

Теперь обратимся к пункту «Искать».

Если вместо установленного по умолчанию «На листе» выбрать значение «В книге», то нет необходимости находиться на листе искомых ячеек. На скриншоте видно, что пользователь инициировал поиск, находясь на пустом листе 2.

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

При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.

Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.

Разновидности поиска

Поиск совпадений

Иногда бывает необходимо обнаружить в таблице повторяющиеся значения. Чтобы произвести поиск совпадений, сначала нужно выделить диапазон поиска. Затем, на той же вкладке «Главная» в группе «Стили», открыть инструмент «Условное форматирование». Далее последовательно выбрать пункты «Правила выделения ячеек» и «Повторяющиеся значения».

Результат представлен на скриншоте ниже.

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

Фильтрация

Другая разновидность поиска – фильтрация. Предположим, что пользователь хочет в столбце B найти числовые значения в диапазоне от 3000 до 4000.

1. Выделить первый столбец с заголовком.
2. На той же вкладке «Главная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
3. В верхней строчке столбца B появляется треугольник – условный знак списка. После его открытия в списке «Числовые фильтры» щёлкнуть пункт «между».
4. В окне «Пользовательский автофильтр» следует ввести начальное и конечное значение плюс OK.

Как видно, отображаться стали только строки, удовлетворяющие введённому условию. Все остальные оказались временно скрытыми. Для возврата к начальному состоянию следует повторить шаг 2.

Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

Видео: Поиск в таблице Excel

Как использовать функцию ПОИСК в Excel

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

Синтаксис

Синтаксис функции ПОИСК в Excel следующий:

  =ПОИСК(найти_текст,внутри_текста,[начальный_номер]) 
 

• Find_text — это подстрока или символ, который вы хотите найти.
• Within_text — это текстовая строка или ссылка на ячейку, в которой вы будете искать свои символы.
• Start_num — (необязательно) номер позиции символа, с которого вы хотите начать поиск. Если третий аргумент опущен, ПОИСК начинает поиск с первого символа строки.

Полезно знать

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

Загрузите бесплатный файл практики!

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

Введите адрес электронной почты

Основное приложение

В следующем списке адресов электронной почты нам может понадобиться узнать номер позиции символа «@».

В этом примере нет необходимости указывать третий аргумент (start_num), так как мы ожидаем только один символ @ в каждом адресе электронной почты.

  =ПОИСК("@",A2)  

Эта функция может быть невероятно полезна несколькими способами.

Проверка наличия текстовой строки

ПОИСК можно комбинировать с ISNUMBER для проверки наличия подстроки. ISNUMBER просто проверяет, является ли оцениваемое значение числом или нет, логика заключается в том, что если номер позиции возвращается функцией SEARCH, формула ISNUMBER вернет значение TRUE. И если ПОИСК возвращает #ЗНАЧ! ошибка, ISNUMBER вернет значение FALSE.

Синтаксис функции ЕСЧИСЛО:

  =ЧИСЛО(значение) 
 

Итак, мы можем просто сделать нашу формулу ПОИСК аргументом формулы НОМЕР.

  =ISNUMBER(SEARCH("xyz.net",A4))  

Приведенная выше формула определяет, содержит ли текстовая строка подстроку «xyz.net». Если эта подстрока не содержится, формула возвращает значение ЛОЖЬ.

В качестве альтернативы мы можем поставить два знака минус перед функцией ISNUMBER, что приведет к тому, что формула будет возвращать 1 или 0 для ИСТИНА или ЛОЖЬ соответственно. Затем становится просто добавить количество адресов с доменом «xyz. net» с помощью функции SUM.

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

Функция ЕСЛИ оценивает логический оператор и возвращает настроенный ответ, если оператор оценивается как ИСТИНА, и другой настраиваемый ответ, если оператор оценивается как ЛОЖЬ.

С помощью этого принципа мы можем указать Excel, что делать, если ISNUMBER возвращает ответ TRUE, и что делать, если нет.

Синтаксис функции ЕСЛИ:

  =ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])  

Таким образом, мы можем просто сделать нашу комбинацию формул ЕЧИСЛО/ПОИСК первым аргументом формулы ЕСЛИ.

  =ЕСЛИ(ЧИСЛО(ПОИСК(2,A2,5)),"Да","Нет")  

Обратите внимание, что в приведенном выше примере мы использовали все три аргумента функции ПОИСК. Это позволило нам искать значение «2», начиная с 5-го значения.

Необязательный аргумент start_num обычно используется в ситуациях, когда искомая подстрока встречается более одного раза, и мы хотели бы игнорировать определенное количество начальных вхождений. Указав начальный номер, мы получаем, что ПОИСК игнорирует цифры года.

Начиная с 5-го символа, если найдено число «2», функция ПОИСК возвращает номер позиции, в результате чего функция ЕЧИСЛО возвращает ИСТИНА. Затем записывается функция ЕСЛИ, возвращающая текст «Да» для ИСТИННЫХ результатов и «Нет» для ЛОЖНЫХ результатов.

Примечание : Поиск числового значения работает как с двойными кавычками, так и без них.

Заменить подстроку, если она присутствует

ПОИСК можно комбинировать с функцией ЗАМЕНИТЬ для замены одной подстроки другой, если она присутствует в исходной текстовой строке. Синтаксис функции ЗАМЕНИТЬ:

  =ЗАМЕНИТЬ(старый_текст, начальный_номер, число_знаков,новый_текст) 
 

Значение этих аргументов следующее:

• Старый_текст — текстовая строка, содержащая заменяемую подстроку.
• Start_num — номер начальной позиции заменяемого текста.
• Num_chars — длина заменяемого текста.
• New_text — это текст, который заменит символы, которые вы хотите удалить.

Ниже мы можем найти слово «песня» и заменить его словом «стихотворение», если оно присутствует.

  =ЗАМЕНИТЬ(A2,ПОИСК("песня",A2),4,"стихотворение") 
 

Результат формулы ПОИСК был использован в качестве аргумента start_num функции ЗАМЕНИТЬ, поскольку требуется номер позиции, а точное расположение подстроки «песня» в текстовой строке неизвестно.

С помощью этой формулы ячейки, не содержащие искомый текст, возвращают #ЗНАЧ! ошибка. Этого можно избежать, включив функцию ЕСЛИОШИБКА для возврата альтернативного результата.

Синтаксис функции ЕСЛИОШИБКА:

  =ЕСЛИОШИБКА(значение, значение_если_ошибка)  

Используя комбинацию формул ЗАМЕНИТЬ/ПОИСК в качестве аргумента значение функции ЕСЛИОШИБКА, мы можем изменять ячейки, содержащие искомые значения, не изменяя остальные.

  =ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2,ПОИСК("песня",A2),4,"стихотворение"),A2)  

Последний аргумент приведенной выше функции ЕСЛИОШИБКА возвращает исходный текст в столбце A, если нет совпадения находится по аргументу ПОИСК.

Извлечение подстроки из текстовой строки большего размера

Функция ПОИСК может быть объединена с функциями ВЛЕВО, ВПРАВО и СРЕДНЯЯ для извлечения символов из текстовой строки путем нахождения символа или начала текстовой строки и последующего использования этой информации для извлечь нужную подстроку.

Извлечь крайние левые символы

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

Функция ВЛЕВО извлекает заданное количество символов из текстовой строки, начиная с крайнего левого символа, с необязательным аргументом, представляющим собой количество возвращаемых символов. Синтаксис функции ВЛЕВО:

  =LEFT(текст, [число_символов])  

Поскольку функция ПОИСК возвращает номер позиции символа или текстовой строки, этот номер позиции можно использовать в качестве аргумента num_chars функции ЛЕВАЯ. Обычно мы вычитаем 1 из этого номера позиции, чтобы получить «конечную» позицию строки, которую мы хотим извлечь.

  =ЛЕВО(A2,ПОИСК("@",A2)-1)  

Найдя номер позиции символа @, мы можем определить, что имя пользователя заканчивается предыдущим символом, то есть нам нужно вычесть 1 из результат формулы ПОИСК. Это становится num_chars аргумент формулы LEFT.

Извлечь крайние правые символы

Если, с другой стороны, мы хотим извлечь доменное имя из текстовой строки, мы можем сделать это, используя либо функции ПРАВО и ДЛСТР вместе, либо, немного изобретательно, функцию СРЕДН. Оба варианта показаны ниже.

Вариант 1

Функция ДЛСТР подсчитывает количество символов в текстовой строке со следующим синтаксисом:

  =ДЛСТР(текст)  

Функция ПРАВИЛЬНО возвращает заданное количество символов из текстовой строки, считая от крайнего правого символа. Синтаксис функции ПРАВИЛЬНО:

. 
  =ПРАВО(текст, [число_знаков])  

Теперь мы будем использовать комбинацию формул ДЛСТР/ПОИСК в качестве аргумента число_знаков формулы ПРАВИЛЬНО.

  =ВПРАВО(A2,ДЛСТР(A2)-ПОИСК("@",A2)) 
 

После того, как ПОИСК определит номер позиции символа @, и мы вычтем этот номер позиции из длины текстовой строки (любезно предоставлено LEN), у нас останется количество символов, составляющих доменное имя. Затем это становится num_chars аргумент функции ПРАВО.

Вариант 2

Наш второй вариант еще проще. Мы можем использовать функцию MID для захвата символов, которые начинаются в середине текстовой строки и доходят до конца. Синтаксис функции MID:

.
  =MID(текст, start_num, num_chars) 
 

Хотя требуются все аргументы, полезно знать, что MID позволяет рассматривать всю строку после тире как среднюю строку, указывая очень большое число. Для этого примера мы можем использовать 100.  

  =СРЕДН(A2,ПОИСК("@",A2)+1,100) 
 

Мы использовали ПОИСК, чтобы определить положение символа «@». Добавление 1 к этому номеру позиции сообщает нам начальную позицию (или start_num ) функции MID.

Извлечение символов из середины текстовой строки

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

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

Шаг 1

Первый шаг прост. Нам нужно найти номер позиции первого символа пробела. Мы можем использовать стандартную формулу ПОИСК без дополнительного аргумента.

  =ПОИСК(" ",A2) 
 

Результат равен 7. Это означает, что первый пробел находится на седьмой позиции текстовой строки. Таким образом, добавляя 1, мы знаем, что отчество начинается с символа номер 8. 

  =ПОИСК(" ",A2)+1 
 

Эта информация позже будет использоваться в качестве аргумента start_num функции MID.

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

Мы можем думать об этом так. Предполагая, что мы начинаем поиск с 8-го символа в строке, где будет следующий пробел?

Шаг 2

Мы уже знаем, что SEARCH(“ ”,A2)+1 сообщает Excel, где начинается отчество, поэтому давайте воспользуемся этим же местоположением, чтобы начать поиск следующего символа пробела.

Эта формула возвращает номер позиции второго пробела в исходной текстовой строке.

Шаг 3

Осталось только использовать функцию MID для просмотра текста в ячейке A2 (аргумент text ), начиная с восьмого символа ( start_num аргумент), извлечь три символа ( num_chars аргумент).

  ПОИСК(" ",A2,ПОИСК(" ",A2)+1)-ПОИСК(" ",A2)) 
 

Последний элемент SEARCH(“ ”,A2) гарантирует, что число 11 будет вычтено из номера позиции первого символа пробела.

Теперь мы объединим все эти элементы в качестве аргументов функции MID.

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

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

Использование подстановочных знаков для поиска

Подстановочные знаки полезны, когда точная подстрока неизвестна или допускается частичное совпадение.

Функция ПОИСК поддерживает использование следующих подстановочных знаков:

В следующем примере мы ищем подстроку «цент» независимо от того, находится ли она в начале, середине или конце текстовой строки.

  =ПОИСК("цент*",A2) 
 

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

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

  =ПОИСК("цент??",A2) 
 

При таком поиске совпадениями считаются только строки, содержащие буквы «цент», за которыми следуют ровно два символа и пробел. Текстовые строки, не соответствующие этому критерию, возвращают ошибку #ЗНАЧ! ошибка.

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

  =ПОИСК("~?",A2) 
 

Поскольку вопросительный знак является подстановочным знаком, мы столкнемся с проблемами, если просто будем искать «?». Тильда действует как своего рода «экранирующий» символ, поэтому символы, которые обычно используются в качестве подстановочных знаков, могут быть интерпретированы Excel буквально.

SEARCH и SEARCHB 

Если вам интересно узнать о функции SEARCHB, вы должны знать, что единственная разница между SEARCH и SEARCHB заключается в том, что SEARCHB считает 2 байта на символ, когда набор двухбайтовых символов ( DBCS) установлен как язык по умолчанию. Языки, поддерживающие DBCS, включают японский, китайский (упрощенный), китайский (традиционный) и корейский.

В противном случае SEARCHB ведет себя так же, как и SEARCH, подсчитывая 1 байт для каждого символа. По этой причине мы обсудили только ПОИСК в этом ресурсе.

Другие способы поиска в Excel

Функция ПОИСК в Excel предназначена для помощи в поиске чего-либо в определенной текстовой строке. Но если вы хотите найти элемент в таблице или наборе данных Excel, функция ВПР или ГПР может больше подойти для этого. Если у вас есть Excel 365, функция XLOOKUP еще более гибкая, поскольку она может делать все то же, что и предыдущие функции, и даже больше.

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

Ознакомьтесь с нашей библиотекой курсов Excel, чтобы найти другие интересные советы по Excel. Вы можете начать с курса Excel — базовый и продвинутый .

Готов стать сертифицированным ниндзя Excel?

Начните учиться бесплатно с курсами GoSkills

Начать бесплатную пробную версию

6 Функции поиска Microsoft Excel для более эффективного поиска в электронных таблицах

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

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

1.

Функция ВПР

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

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

 = ВПР([искомое_значение], [массив_таблиц], [номер_столбца], [диапазон_просмотра]) 

• [lookup_value] — часть информации, которая у вас уже есть. Например, если вам нужно знать, в каком состоянии находится город, это будет название города.
• [table_array] позволяет указать ячейки, в которых функция будет искать искомые и возвращаемые значения. При выборе диапазона убедитесь, что первый столбец, включенный в ваш массив, будет содержать значение поиска!
• [col_index_num] — это номер столбца, содержащего возвращаемое значение.
• [диапазон поиска] — это необязательный аргумент, который принимает значения 1 или 0, хотя вы также можете ввести ИСТИНА или ЛОЖЬ. Если вы введете 1 или опустите этот аргумент, функция ищет приблизительное значение, но мы обнаружили, что это случайность. В приведенном ниже примере функция ВПР, ищущая значение 100, возвращает 90. Поиск меньшего значения, например 88, возвращает ошибку.

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

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

 =ВПР("Дэвидсон 

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

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

Он вернул 79, что является четвертой оценкой запрошенного нами ученика.

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

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

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

Связанный: Как выполнить поиск с функцией ВПР в Excel

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

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

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

 =HLOOKUP([искомое_значение], [табличный_массив], [номер_индекса_строки], [диапазон_поиска]) 

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

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

 =ГПР("Оценка 4" 

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

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

Связанные Как рассчитать средневзвешенное значение в Excel

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

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

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

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

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

 =ИНДЕКС([массив], [номер_строки], [номер_столбца]) 

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

 =ПОИСКПОЗ([искомое_значение], [искомый_массив], [тип_совпадения]) 

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

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

.

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

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

.

 =ИНДЕКС(F:F, MATCH("Таунсенд", B:B, 0)) 

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

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

5.

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

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

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

 =НАЙТИ("x", "Коричневая лиса перепрыгнула через забор") 

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

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

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

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

ПОИСК и ПОИСК

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

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

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

Связано: Советы по работе с текстом и текстовыми функциями в 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).

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

 =XLOOKUP(G2,B2:B25,F2:F25,-1) 

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

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