Как в excel искать по слову

Пример преимущества функции ПОИСК в Excel перед функцией НАЙТИ

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

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

Для нахождения позиции текстовой строки в другой аналогичной применяют ПОИСК и ПОИСКБ. Расчет ведется с первого символа анализируемой ячейки. Так, если задать функцию ПОИСК “л” для слова «апельсин» мы получим значение 4, так как именно такой по счету выступает заданная буква в текстовом выражении.

Функция ПОИСК работает не только для поиска позиции отдельных букв в тексте, но и для целой комбинации. Например, задав данную команду для слов «book», «notebook», мы получим значение 5, так как именно с этого по счету символа начинается искомое слово «book».

Используют функцию ПОИСК наряду с такими, как:

• НАЙТИ (осуществляет поиск с учетом регистра);
• ПСТР (возвращает текст);
• ЗАМЕНИТЬ (заменяет символы).

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

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

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

В этой формуле задаваемые значения определяются следующим образом.

1. Искомый текст. Это числовая и буквенная комбинация, позицию которой требуется найти.
2. Анализируемый текст. Это тот фрагмент текстовой информации, из которого требуется вычленить искомую букву или сочетание и вернуть позицию.
3. Начальная позиция. Данный фрагмент необязателен для ввода. Но, если вы желаете найти, к примеру, букву «а» в строке со значением «А015487.Мужская одежда», то необходимо указать в конце формулы 8, чтобы анализ этого фрагмента проводился с восьмой позиции, то есть после артикула. Если этот аргумент не указан, то он по умолчанию считается равным 1. При указании начальной позиции положение искомого фрагмента все равно будет считаться с первого символа, даже если начальные 8 были пропущены в анализе. То есть в рассматриваемом примере букве «а» в строке «А015487.Мужская одежда» будет присвоено значение 14.

При работе с аргументом «искомый_текст» можно использовать следующие подстановочные знаки.

1. Вопросительный знак (?). Он будет соответствовать любому знаку.
2. Звездочка (*). Этот символ будет соответствовать любой комбинации знаков.

Если же требуется найти подобные символы в строке, то в аргументе «искомый_текст» перед ними нужно поставить тильду (

Если искомый текст не был найден приложением или начальная позиция установлена меньше 0, больше общего количества присутствующих символов, в ячейке отобразиться ошибка #ЗНАЧ.

Если «искомый_текст» не найден, возвращается значение ошибки #ЗНАЧ.

Пример использования функции ПОИСК и ПСТР

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

Введем исходные данные в таблицу:

В ячейке, которая будет учитывать данные клиентов без телефона, введем следующую формулу:

Нажмем Enter для отображения искомой информации:

Далее мы можем использовать любые другие функции для отображения представленной информации в удобном формате:

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

Пример формулы ПОИСК и ЗАМЕНИТЬ

Пример 2. Есть таблица с текстовой информацией, в которой слово «маржа» нужно заменить на «объем».

Откроем книгу Excel с обрабатываемыми данными. Пропишем формулу для поиска нужного слова «маржа»:

Теперь дополним формулу функцией ЗАМЕНИТЬ:

Чем отличается функция ПОИСК от функции НАЙТИ в Excel?

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

Но опытный пользователь Excel знает, что отличие у этих двух функций очень существенные.

Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и маленькие буквы). Функция НАЙТИ чувствительна к регистру символов. Например, есть список номенклатурных единиц с артикулом. Необходимо найти позицию маленькой буквы «о».

Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буквы «О» в критериях поиска:

Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем использовать символы подстановки для указания не точного, а приблизительного значения, которое должно содержаться в исходной текстовой строке. Вторая функция НАЙТИ не умеет использовать в работе символы подстановки масок текста: «*»; «?»; «

Для примера попробуем в этих же исходных строках столбца «наименования» найти приблизительный текст. Для этого укажем следующий вид критерия поиска используя символы подстановки: «н*ая».

Как видим во втором отличии функция НАЙТИ совершенно не умеет работать и распознавать спецсимволы для подстановки текста в критериях поиска при неточном совпадении в исходной строке.

Как в таблице Эксель найти слово или фразу, инструкция

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

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

Как в таблице Эксель найти слово

Когда нужно просто найти нужное слово, то достаточно нажать на клавиатуре — Ctrl+F. Откроется окно поиска, в которое вводится искомое слово. Далее нужно нажать — найти все.

Например я ввёл слово — Татьяна и программа нашла 8 ячеек, содержащих данное слово. В строке рядом отмечен порядковый номер ячейки. То есть, я по номеру строки без труда отыщу все нужные ячейки в таблице.

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

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

Работа с вкладками

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

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

Параметры

Давайте рассмотрим, какие ещё существуют возможности поиска в программе Эксель. Обратите внимание, что в окне поиска, справа, есть кнопка — параметры, нажмите на неё.

Итак, на этой вкладке есть дополнительные параметры поиска содержащие следующие строки:

• Искать;
• Просматривать;
• Область поиска.

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

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

Регистр

Если вы установите галочку рядом с строкой — учитывать регистр, то при проведении поиска будет учитываться написание искомого слова. То есть, программа будет искать точную копию слова, внесённого в поиск, с соблюдением форматирования.

Если вы написали слово с заглавной буквы, то поиск выберет такие же варианты написания.

Ячейка целиком

Установив отметку напротив — ячейка целиком, будут найдены лишь те значения, которые указаны в поиске.

Например указав в поиске цифру 3, вы получите в результате все ячейки, где содержится 3, но при этом в ячейке будут присутствовать и другие цифры, слова и значения (35, 38 и так далее). А установив галочку вы получите в результатах только ячейки с цифрой 3.

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

Фильтры

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

На главной, рядом с блоком найти и заменить, есть блок — сортировка и фильтр. Жмём на него и выбираем фильтр.

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

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

К примеру мне нужно найти и скопировать в Эксель таблице все емейлы @ukr.net. Как раз в этом случае удобнее всего проводить поиск через фильтр.

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

Как использовать поиск в Excel по одному или нескольким значениям

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

Поиск в Excel

Самый простой и быстрый способ искать по слову, фразе, формуле и т.д в документе это использовать комбинацию клавиш Ctrl и F одновременно. Появится следующее окно.

Здесь понадобится ввести искомое слово, фразу и т.д.

Второй вариант поиска в Excel — на главной странице нажать вкладку «Найти и выделить». Выбираем «Найти», вводим нужные символы и запускается поиск.

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

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

В поле можно использовать подстановочные знаки * и ?.

Обычно их применяют в таких ситуациях:

1. * — может быть использована для замены букв. К примеру, если ввести «к*т», то найдутся все слова, которые начинаются на букву «к» и заканчиваются на «т», кит, кот и т.д. Звездочка заменяет как один символ, так и любое количество. Т.е. в нашем примере найдутся и слова кабинет, кабриолет и т.д.
2. ? — такой подстановочный знак заменяет только одну букву. Например, «к?т», выйдут слова, которые состоят из трех букв «кот», «кит».

Настройки поиска

Если необходимо установить определенные настройки, то в окне необходимо кликнуть «Параметры».

Тут можно задать следующие настройки:

• в строке «Искать» можно ограничить поиск только листом;
• в строке «Просматривать» можно выбрать вариант просмотра по строкам или по столбцам;
• строка «Область» позволяет выбрать искомые символы: формула либо значение;
• отметив галкой «Учитывать регистр» производится нахождение искомой комбинации с учетом регистра;
• если необходимо увидеть ячейку, которая содержит только заданные символы, нужно отметить «Ячейка целиком»;
• во вкладке «Формат» можно выбрать искомое форматирование.

Нажимая кнопку «Заменить», можно сразу заменить найденное на указанные значения.

В строку «Найти» нужно ввести искомую комбинацию значений, а в строке «Заменить» те символы, на которые следует заменить найденные.

Поиск по указанному интервалу

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

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

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

Поиск по нескольким условиям

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

Приведем пример поиска нескольких значений одновременно. В таблице указана разная цена различного гранита. Необходимо с помощью функции ВПР определить цену гранита 7.

Составим таблицу.

Формула будет в ячейке Е4, но можно использовать любую другую. Затем придерживаемся алгоритма:

1. В ячейке ставим знак равенства и прописываем функцию: «=ВПР(».
2. Аргумент 1 это Гранит7. Пишем «ВПР(«Гранит7»».
3. Аргумент 2 – это ячейки А4:А20. Формула получается такой: «ВПР(«Гранит7»; А4:А20;».
4. Аргумент 3, цифра 2, т.к. ищем значение во втором столбце. «ВПР(«Гранит7»; А4:А20;2».
5. Аргумент 4, дает понять функции, какое значение нужно искать, точное или приблизительное. Оно может быть ИСТИНА или ЛОЖЬ. ИСТИНА – приблизительно совпавшие значения, а ЛОЖЬ – точные. Мы ищем точные. Выглядит запись так: «ВПР(«Гранит7»; А4:А20;2;ЛОЖЬ)» или «=ВПР(A10;A4:B20;2;ЛОЖЬ)».
6. Кликаем по Enter и получаем искомое.

Аналогичным способом можно добавлять условия в более сложных таблицах.

К примеру, добавим в таблицу столбец с месяцем. Попробуем увидеть в каком месяце Гранит17 стоит 1016.

Составляем формулу, добавляя новые значения. Аргументом 3 будет цифра 3.

Нажимаем ввод и получаем месяц «май».

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

Также для обнаружения можно использовать функции ПОИСК и НАЙТИ.

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

Пример: найдем положение буквы «а» в слове Гранит6. Введем формулу =НАЙТИ(«а»;A9;3) и получим 3, т.е. «а» третья буква.

Теперь рассмотрим функцию ПОИСК. В данной формуле можно использовать подстановочные символы (? — один, * — несколько). Например, найдем в той же ячейке положение цифры 6. Введем формулу =ПОИСК(«6»;A9), получаем 8.

Поиск в экселе — как искать и применение функции с примерами

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

Поиск

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

1. Первый блок используется для записи искомой информации.
2. Вторая часть функции позволяет задать диапазон поиска по части текста.
3. Третий аргумент является необязательным. Его использование оправдано, если известна точка начала поиска внутри ячейки.

Рассмотрим пример: необходимо найти фрукты, которые начинаются на букву А из списка.

1. Составляете список на рабочем листе

1. В соседнем столбце записываете =ПОИСК(«а»;$B$4:$B$11). Не забывайте ставить двойные кавычки при использовании текста в качестве аргумента.
2. Используя маркер автозаполнения, применяете формулу ко всем остальным ячейкам. Диапазон поиска был зафиксирован значками доллара для более корректной работы.

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

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

1. Нулевая ячейка
2. Блок не содержит искомой информации.

В нашем случае фрукт Персик не содержит ни одной а, поэтому программа выдала ошибку.

Найти

Комбинация клавиш Ctrl+F позволяет быстро вызвать окно функции Найти. Также можно активировать инструмент через отдельную кнопку на Панели инструментов.

Расширенное диалоговое окно содержит поле для ввода искомой информации, параметры масштаба, например поиск по всем листам, а также варианты анализа информации по столбцу или строке.

Данная функция реализована и в виде формулы с одноименным названием, которая также позволяет осуществлять поиск по словам. Рассмотрим работу на примере списка фруктов. Вписываете формулу =НАЙТИ(«а»;$B$4:$B$11), применяете автозаполнение и получаете результат:

Различия

Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:

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

1. Дополнительным различием является то, что ПОИСК умеет работать с подстановочными символами типа вопросительный знак (?) и звездочка (*), которые используются для неточного формирования поискового запроса.

На заметку! Чтобы найти именно эти символы в тексте необходимо использовать тильду (

1. Инструмент НАЙТИ реализован как в виде отдельной кнопки на главной панели, так и в виде встроенной функции редактора.

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