Поиск слов в Excel

Содержание

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

Поиск слов в Excel

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

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

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

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

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

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

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

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

=ПОИСК(нужный_текст;анализируемый_текст;[начальная_позиция]).

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

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

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

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

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

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

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

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

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

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

=ПОИСК(“, тел.”;адрес_анализируемой_ячейки).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: https://exceltable.com/funkcii-excel/primery-funkciy-poisk-i-nayti

Извлечение слов из текста в Excel

Поиск слов в Excel

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

Важное замечание!

Перед применением описанных ниже функций убедитесь, что текст не содержит лишних пробелов (то есть пробелов в начале строки, в конце строки, двойных, тройных и т.д.). Убрать лишние пробелы можно формулой =СЖПРОБЕЛЫ(А1), где А1 — ячейка с текстом. Полученный результат можно скопировать и вставить значениями в исходную ячейку и в дальнейшем работать с «чистым» текстом.

Задача 1. Подсчет количества слов в тексте

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

Итак, слова в тексте отделены друг от друга пробелами. Пробелов всегда на 1 меньше, чем слов (действительно, между двумя словами — один пробел, между тремя — два и т.д.). Значит, достаточно подсчитать пробелы и прибавить к ним единицу, чтобы узнать число слов в ячейке.

В Excel есть замечательные функции ДЛСТР() и ПОДСТАВИТЬ(). Первая подсчитывает количество символов в указанной ячейке, а вторая умеет заменять указанный символ на любой другой или на пусто (обозначается как двойные кавычки без пробела между ними «»).

Функция ПОДСТАВИТЬ() имеет 4 аргумента:

1) Ячейка с текстом

2) То, что нужно заменить (указывается в кавычках)

3) То, на что нужно заменить (указывается в кавычка)

4) Необязательный аргумент — номер вхождения заменяемого текста. То есть, если в тексте 4 пробела, мы можем заменить только тот, номер которого укажем. Если аргумент опущен — заменяются все символы.

Первой функцией мы можем узнать длину текста в ячейке. Второй функцией мы можем заменить все пробелы в тексте на «» (пусто), то есть удалить. Если после удаления мы подсчитаем длину текста еще раз, то она сократится ровно на число удаленных пробелов. Таким образом, чтобы подсчитать пробелы, нужна формула:

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

Прибавим к ней единицу — и получим число слов в ячейке.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))+1

Имейте в виду, что такой способ подсчета принимает символы, отделенные от слов пробелом, за отдельные слова. Например, тире, обрамленное пробелами, также будет считаться отдельным словом. Избежать этого можно, если перед подсчетом пробелов убрать функцией ПОДСТАВИТЬ() лишние знаки из ячейки.

Задача 2. Извлечение первого слова из текста

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

Найти символ в тексте поможет функция НАЙТИ(). У нее есть 3 аргумента:

1) Искомый текст;

2) Текст, в котором ищем;

3) С какого символа начать поиск (необязательный, если опущен — функция ищет с самого начала).

Результатом работы функции является позиция первого символа найденного текста в строке. Например, формула =НАЙТИ(«плюс»;»Один плюс один») вернет в результате число 6 (именно с этой позиции начинается искомое слово «плюс»). Если в строке несколько раз содержится искомое слово, то вернется результат для первого вхождения.

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

Теперь мы можем найти позицию первого пробела

=НАЙТИ(» «;A1)

Извлечь символы до пробела поможет функция ЛЕВСИМВ. Ей нужно указать текст, а также число символов, которое нужно извлечь начиная с первого. Если найденный пробел, например, 6-ой символ, то нам нужно извлечь 6-1=5. Формула для извлечения первого слова:

=ЛЕВСИМВ(A1;НАЙТИ(» «;A1)-1)

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

=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(» «;A1)-1);A1)

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

Задача 3. Извлечение последнего слова из текста

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

подсчитаем, сколько в ячейке пробелов, воспользовавшись приемом из Задачи 1.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

с помощью функции ПОДСТАВИТЬ заменим последний пробел в тексте на символ, которого там точно нет (например, «*» или «$»). Чтобы заменить именно последний пробел, добавим в качестве необязательного аргумента формулу из предыдущего пункта (то есть число пробелов, так как оно равно номеру последнего пробела)

=ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))

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

=НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

если из общей длины текста мы вычтем найденную позицию звездочки, то получим число символов после звездочки (то есть длину последнего слова в ячейке)

=ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

Итоговая формула:

=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))))

Как и в предыдущей задаче, можно обернуть в ЕСЛИОШИБКА, чтобы верно работало с ячейками, в которых только 1 слово.

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))));A1)

Если Вас не пугает VBA и макросы, то есть гораздо более изящное решение. Добавьте в модуль книги (можно в личную книгу макросов) следующий код:

Function РЕВЕРС(str As String)

РЕВЕРС = StrReverse(str)

End Function

Это макрофункция. Теперь, используя ее, можно отразить текст зеркально, извлечь первое слово, как в Задаче 2 и отразить его обратно. Формула:

=ЕСЛИОШИБКА(РЕВЕРС(ЛЕВСИМВ(РЕВЕРС(A1);НАЙТИ(» «;РЕВЕРС(A1))));A1)

Берите на вооружение, если любите макросы.

Задача 4. Извлечение n-ного слова из текста

Самый интересный пример. Для решения подобной задачи нужно применить фантазию. Разберем решение поэтапно.

Помимо уже использовавшихся в статье функций, нам понадобятся еще 2:

1) ПОВТОР(). Умеет повторять указанный текст указанное число раз.

2) ПСТР(). Имеет 3 аргумента:

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

Теперь мы вооружены всем нужным инструментарием. Приступаем:

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

=ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)))

Теперь каждое слово отделено друг от друга числом символов, равным общей длине строки. Чтобы извлечь слово под номером n, нам нужно узнать позицию какого-нибудь пробела между словом n и n-1 в обработанном удлиненном тексте. Это может сделать формула ниже (+1 в ней нужно для правильного поиска первого слова):

=ДЛСТР(A1)*(n-1)+1

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

Теперь мы может извлечь из найденной позиции число символов ДЛСТР(A1) (на рисунке — m) и извлеченный текст всегда будет захватывать нужное нам слово целиком (с некоторым количеством предшествующих и последующих пробелов)

=ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1))

Осталось избавиться от лишних пробелов функцией СЖПРОБЕЛЫ()

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1)))

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

Задача 5. Извлечение имя файла из полного пути к нему

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

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

Берем формулу из задачи 3 и заменяем в ней пробелы на «\».

Получим следующую формулу:

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;»\»;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;»\»;»»)))));A1)

Вставляем в ячейку и вуаля! Имя файла извлечено.

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

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

аши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru

Источник: https://golos.io/excel/%40tdots/izvlechenie-slov-iz-teksta-v-excel

Поиск в Эксель: способы найти нужное слово

Поиск слов в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтрация

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

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

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

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

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

Источник: http://v-ofice.ru/excel/poisk-v-excel/

Поиск слов в Excel

В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк, столбцов и позиций.

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

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

В редакторе таблиц существует несколько способов поиска

Поиск ячеек

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

Если у вас Office 2010, перейдите в Меню — Правка — Найти.
Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
В Excel 2007 эта кнопка есть в меню «» на панели «Редактирование». Она находится справа.
Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
В поле напечатайте слово, фразу или цифры, которые надо отыскать.
Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
При клике на каждую строчку будет выделяться соответствующая ячейка.
Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
Эти столбцы также «растягиваются».

Параметры поиска

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П???», отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

В окне «Найти» нажмите «Параметры».
В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:

В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

Напишите их в строке поиска.
Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
Этим способом можно задать ключ даже с отдельными буквами.

Фильтр

Вот как искать в Экселе, используя фильтр:

Выделите какую-нибудь заполненную ячейку.
Нажмите — Сортировка — Фильтр.
В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
В текстовом поле введите запрос и нажмите «OK».
В столбце будут отображаться только ячейки, содержащие искомую фразу.
Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
Чтобы отключить фильтр, повторно нажмите на него в сортировке.

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

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

Источник: http://NastroyVse.ru/programs/review/kak-v-excel-najti-slovo.html

Поиск по листам в excel как сделать

Поиск слов в Excel

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

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

Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

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

Находясь во вкладке «», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.
После того, как вы перешли по соответствующим пунктам на ленте, или нажали комбинацию «горячих клавиш», откроется окно «Найти и заменить» во вкладке «Найти». Она нам и нужна. В поле «Найти» вводим слово, символы, или выражения, по которым собираемся производить поиск. Жмем на кнопку «Найти далее», или на кнопку «Найти всё».
При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.
Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.
Поисковые символы не обязательно должны быть самостоятельными элементами.
Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова.
Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».
Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».
Так можно продолжать до тех, пор, пока отображение результатов не начнется по новому кругу.
В случае, если при запуске поисковой процедуры вы нажмете на кнопку «Найти все», все результаты выдачи будут представлены в виде списка в нижней части поискового окна. В этом списке находятся информация о содержимом ячеек с данными, удовлетворяющими запросу поиска, указан их адрес расположения, а также лист и книга, к которым они относятся. Для того, чтобы перейти к любому из результатов выдачи, достаточно просто кликнуть по нему левой кнопкой мыши. После этого курсор перейдет на ту ячейку Excel, по записи которой пользователь сделал щелчок.

Способ 2: поиск по указанному интервалу ячеек

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

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

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3.

Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2.

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

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

После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».
В окне появляется целый ряд дополнительных инструментов для управления поиском. По умолчанию все эти инструменты находятся в состоянии, как при обычном поиске, но при необходимости можно выполнить корректировку.
По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение.
Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование.
Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.
По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.
В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам», можно задать порядок формирования результатов выдачи, начиная с первого столбца.
В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат.
Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения». Кроме того, существует возможность поиска по примечаниям.
В этом случае, переключатель переставляем в позицию «Примечания».
Ещё более точно поиск можно задать, нажав на кнопку «Формат».
При этом открывается окно формата ячеек. Тут можно установить формат ячеек, которые будут участвовать в поиске. Можно устанавливать ограничения по числовому формату, по выравниванию, шрифту, границе, заливке и защите, по одному из этих параметров, или комбинируя их вместе.

Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».
После этого, появляется инструмент в виде пипетки. С помощью него можно выделить ту ячейку, формат которой вы собираетесь использовать.
После того, как формат поиска настроен, жмем на кнопку «OK».
Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.
Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Источник: http://word-office.ru/poisk-po-listam-v-excel-kak-sdelat.html

Как в экселе найти нужное слово: 100% сопобы!

Поиск слов в Excel

Всем добра друзья! И сегодня вы узнаете как в экселе найти нужное слово. Зачастую работа с Excel подразумевает существенные информационные объемы. В подобных файлах разрабатываются поистине громадные таблицы с тысячами строчек, столбиков, а также, позиций. Найти в таком огромном массиве какие-либо конкретные значения порой бывает невероятно тяжело.

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

Как в экселе найти нужное слово по ячейкам

Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:

Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
Программа предыдущей версии располагает данной кнопкой в меню под названием «», расположенная на панели редактирования.
Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
В поле следует пропечатать фразу, искомые слова либо цифры.
Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
Кликнув на любую строчку, будет выделена соответствующая ячейка.
С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».

Поисковые параметры

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

Следует ввести лишь частичку надписи. Можно даже одну из букв – будут обозначены все участки, где она имеется.
Применяйте значки «звездочка», а аткже, знак вопроса. Они способны заместить пропущенные символы.
Вопросом обозначается одна недостающая позиция. Если, например, вы пропечатаете «А????», будут отображены ячейки, которые содержат слово из пяти символов, которое начинается с «А».
Благодаря звездочке, замещается любое количество знаков. Для поиска всех значений, содержащих корень «раст», следует начать искать согласно ключу «раст*».

Кроме того, вы можете посещать настройки:

В окошке поиска следует кликнуть «Параметры».
В разделах просмотра и области поиска, необходимо будет указать, где именно и на основании каких критериев нужно отыскивать совпадения. Можно подобрать формулы, различные примечания либо значения.
Для различия системой строчных и прописных букв, нужно выставить галочку в поле под названием «Учитывать регистр».
Отметив пункт «Ячейка полностью», результаты отобразятся в виде клеток, содержащих исключительно заданную поисковую фразу, и ничего кроме нее.

Настройки форматирования ячеек

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

В окошке поиска следует кликнуть по параметрам и нажать клавишу «Формат». Будет открыто меню, содержащее несколько вкладок.
Можно указывать тот или иной шрифт, тип рамочки, окраску фона, а также, формат вводимых данных. Системой будут просмотрены те участки, которые соответствуют обозначенным критериям.
Для взятия информации из текущей клеточки (выделенной на данный момент), следует кликнуть «Использовать формат данной ячейки». В таком случае программой будут найдены все значения, обладающие тем же размером и типом символов, той же окраской, а также, теми же границами и т.п.

Минутку внимания, возможно вам будет полезно узнать как активировать windows 10 без ключа или как скачать сайт целиком на компьютер.

Как найти несколько слов в Excel

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

Для посика не одного, а одновременно нескольких слов, необходимо выполнить следующее:

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

Применяем фильтр

Итак, для того, чтобы воспользоваться фильтром, необходимо:

Выделить определенную ячейку, содержащую данные.
Кликнуть по главной, затем – «Сортировка», и далее – «Фильтр».
В строчке вверху клетки будут оснащены стрелочками. Это и есть меню, которое нужно открыть.
В текстовом поле нужно пропечатать запрос и нажать подтверждение.
В столбике будут отображаться лишь ячейки. В которых присутствует искомая фраза.
Для сброса результатов, в выпавшем списке следует отметить «Выделить все».
Для отключения фильтра, заново стоит нажать по нему в сортировке.

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

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

Источник: http://bezkompa.ru/kak-v-eksele-najti-nuzhnoe-slovo.html

Как в Excel найти слово или фразу?

Поиск слов в Excel

Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов.

Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).

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

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

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

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

Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.

В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.

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

Дополнительные параметры поиска слов и фраз

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

Здесь можно указать дополнительные параметры поиска.

Искать:

на листе — только на текущем листе;
в книге — искать во всем документе Excel, если он состоит из нескольких листов.

Просматривать:

по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.

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

Область поиска — определяет, где именно нужно искать совпадения:

в формулах;
в значениях ячеек (уже вычисленные по формулам значения);
в примечаниях, оставленных пользователями к ячейкам.

А также дополнительные параметры:

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

Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.

Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».

Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.

Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки…

Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.

Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.

Способ 2

Второй способ, который позволяет найти искомое слово в таблице Excel, не является именно поиском, но также может быть удобен для работы. Это фильтр по фразе (символам), который позволяет вывести на экране только те строки, которые содержат искомые символы.

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

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

Нужно нажать на стрелочку в том столбце, в котором будет выполняться фильтр. В нашем случае нажимаем стрелочку в столбце Слова и пишем символы, которые мы будем искать — «замок». То есть мы выведем только те строки, в которых есть слово «замок».

Результат будет таков.

Таблица до применения фильтра и таблица после применения фильтра.

Фильтрация не изменяет таблицу и не удаляет строки, она просто показывает искомые строки, скрывая не нужны. Чтобы удалить фильтр, нужно нажать на стрелочку в заголовке — Удалить фильтр с слова…