ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Функция SEARCH (ПОИСК) в Excel используется для определения расположения текста внутри какого-либо текста и указания его точной позиции.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:

<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;

<НОМЕР_СТОЛБЦА> — номер столбца в диапазоне, из которого будет возвращено значение;

<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

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

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

Поиск слов

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

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

как искать в экселе

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

Существует также способ нестрогого поиска, который подходит для ситуаций, когда искомое слово помнится частично. Он предусматривает использование символов-заменителей (джокерные символы). В Excel их всего два:

  •  «?» – подразумевает любой отдельно взятый символ;
  •  «*» – обозначает любое количество символов.

 Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («~»). При поиске тильды, соответственно – две тильды.

как в excel найти слово

 Алгоритм неточного поиска слова:

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

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

как найти слово в таблице в excel

Как выполнить поиск значений в программе «Excel» .


Поиск в Эксель
Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

  1. Классический поиск «MS Office».
  2. Условное форматирование (выделение нужных ячеек цветом)
  3. Настройка фильтров по одному или нескольким значениям.
  4. Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

Задача

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

Об этой статье

Эту страницу просматривали 30 259 раз.

,

,

).

Искать слова запроса …

  • все(«и») – документ находится только в том случае, если в нем присутствуют все слова запроса; включено по умолчанию ;
  • хотя бы одно(«или») – документ находится, если в нем встретилось хотя бы одно слово из запроса;
  • точную фразу – документ находится если в нем встретились все слова запроса, причем в том же порядке и в тех же формах что и в запросе; выбор этой опции равнозначен заключению запроса в двойные кавычки.

Расстояние между словами запроса …

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

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

Язык документа …

  • любой– находятся любые документы, независимо от языка; включено по умолчанию;
  • русский– поиск только по «русскоязычным» (кириллическим) документам;
  • английский поиск только по «англоязычным» документам (набранным латиницей).

Дата документа …
Позволяет отбирать только те документы, дата создания которых укладывается в заданный диапазон. В частности, можно ограничить выдачу только «новыми» ( начиная с указанной даты) или «старыми» документами (до указанной даты). Все даты задаются в формате день/месяц/год, например, 19/05/2002. По умолчанию находятся любые документы, вне зависимости от даты. Внимание: если сервер не возвращает даты документа, то в качестве таковой проставляется дата индексирования (день, когда документ был считан «пауком»).

Искать документы только на следующих сайтах …
gозволяет отбирать только те документы, которые найдены на указанных сайтах. Под сайтом понимается либо уникальное DNS – имя (домен ), либо DNS – имя с каталогом первого уровня, начинающимся с тильды. Например :
    Top 100.rambler.ru, www.lenta.ru, www.hosting.ua/ name – но это не может быть www.rambler.ru/domains/.
Можно указать несколько сайтов через запятые. По умолчанию в поиске участвуют документы во всех проиндексированных сайтов.

Синтаксис

=SEARCH(find_text, within_text, [start_num]) – английская версия

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

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

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

  •  если фраза точная, введите ее и нажмите клавишу «Найти все»;
  •  если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

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

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

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

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

исходные данные в таблицу.

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

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

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

адрес_анализируемой_ячейки.

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

ПСТР.

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

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

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

Найти любое слово из списка

Настройка !SEMTools с лёгкостью решает такого рода проблемы. Более того, практически вне зависимости от количества слов, распознавание их наличия происходит очень быстро даже в диапазоне из 10 000 ячеек и более.

Чтобы найти список слов диапазоне ячеек с помощью !SEMTools, нужно:

  1. скопировать в соседний столбец диапазон, в котором мы хотим найти список слов. Это нужно для того, чтобы не стереть исходные данные,
  2. вызвать макрос на панели настройки,
  3. выбрать список слов, которые необходимо найти,
  4. нажать OK.

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

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

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

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

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

Найти все слова из списка

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

Использование с оператором СУММ

Оператор ИНДЕКС можно использовать вместе с функцией СУММ, формула которой выглядит так:

=СУММ(Адрес_массива).

Если применить функцию суммирования к нашей таблице, например, к столбцу с суммой, формула будет выглядет так: =СУММ(D2:D9).

Сумма ячеек столбца в Эксель

Мы можем слегка изменить формулу, добавив в нее оператор ИНДЕКС:

  1. В качестве первого аргумента функции СУММ оставляем координаты ячейки, которая является началом диапазона суммирования.
  2. Второй аргумент (крайняя нижняя ячейка диапазона) будем задавать с помощью оператора ИНДЕКС. Перейдя в режим редактирования формулы (двойным щелчком мыши по ячейке с формулой или просто кликнув по строке формул, предварительно выбрав нужную ячейку), приводим ее к следующему виду с учетом нашей таблицы: =СУММ(D2:ИНДЕКС(D2:D9;8)).

    Комбинирование функций СУММ и ИНДЕКС в Эксель
    Цифра 8 означает, что мы ограничиваем указанный диапазон между ячейками D2 и D9 позицией под номером 8. Мы можем задать любую цифру (больше или равно 0), не превышающую общее количество всех позиций в выбранном массиве.

  3. Нажав Enter мы получим результат в выбранной ячейке.

    Сочетание функций СУММ и ИНДЕКС в Excel

Функция — игнорировать домен

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

Поиск через фильтр

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

  • выделить заполненную ячейку;
  • во вкладке «Главная» выбрать функцию «Сортировка»;
  • нажать на кнопку «Фильтр»;
  • открыть выпадающее меню;
  • ввести искомый запрос;
  • нажать кнопку «Ок».

Как искать в Excel слова, текст, ячейки и значения в таблицах

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

Видеоурок по теме

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

Комментировать Разместить на своем сайте

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте?

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

Предназначение и синтаксис метода Range.Find

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

1 Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

В скобках перечислены параметры метода, среди них только What является обязательным.

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Параметры метода Range.Find

Наименование Описание
Обязательный параметр
What Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра – Variant.
Необязательные параметры
After Ячейка, после которой следует начать поиск.
LookIn Уточняет область поиска. Список констант xlFindLookIn:
  • xlValues (-4163) – значения;
  • xlComments (-4144) – примечания*;
  • xlNotes (-4144) – примечания*;
  • **.
LookAt Поиск частичного или полного совпадения. Список констант xlLookAt:
  • xlWhole (1) – полное совпадение;
  • xlPart (2) – частичное совпадение.
SearchOrder Определяет способ поиска. Список констант xlSearchOrder:
  • xlByRows (1) – поиск по строкам;
  • xlByColumns (2) – поиск по столбцам.
SearchDirection Определяет направление поиска. Список констант xlSearchDirection:
  • xlNext (1) – поиск вперед;
  • xlPrevious (2) – поиск назад.
MatchCase Определяет учет регистра:
  • False (0) – поиск без учета регистра (по умолчанию);
  • True (1) – поиск с учетом регистра.
MatchByte Условия поиска при использовании двухбайтовых кодировок:
  • False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
  • True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
SearchFormat Формат поиска – используется вместе со свойством Application.FindFormat.

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes. ** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...