Быстрый поиск в excel

Работа функции ВПР по нескольким критериям

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

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

1. – Дата сдачи выручки в кассу.
2. – Фамилия торгового представителя.

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

1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:

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

Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

1. элемент – Дата.
2. элемент – Фамилия.
3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Надстройка для поиска на всех листах книги Excel

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

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

В Excel 2007 и 2010 панель инструментов можно найти на вкладке «Надстройки»:

Надстройка SearchText является расширенной версией надстройки для поиска на текущем листе.

Назначение кнопок надстройки:

— поле для ввода искомого текста. Нажатие клавиши Enter запускает поиск.

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

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

или — переключатель режимов поиска (щелкните на этой кнопке для переключения режима поиска)

При запуске поиска (вводе числа в текстовое поле, и нажатии Enter) макрос создаёт в текущем файле дополнительный лист (с названием «Результаты поиска»), и копирует на этот лист все строки, в которых содержится заданный текст.

Выполняется поиск по частичному совпадению текста (т.е., при поиске текста 1234 будут найдены ячейки, содержащие числа 12345, 5612348 и т.д.)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

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

Минусы : Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

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

Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

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

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

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

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

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

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

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

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

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

Способ 2

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

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

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

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

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

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

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

Также можно нажать на стрелочку и выбрать Текстовые фильтры — Содержит и указать искомые символы.

И далее ввести искомую фразу, например «Мюнхен».

Результат будет таков — только строки, содержащие слово «Мюнхен».

Этот фильтр сбрасывается также, как и предыдущий.

Таким образом, у пользователя есть варианты поиска слова в Excel — собственно сам поиск и фильтр.

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

Также статьи о работе с таблицами в Экселе:

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

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

Для примера попробуем найти необходимое число среди данных нашей таблицы, так как именно при поиске чисел необходимо учитывать некоторые тонкости поиска. Будем искать в таблице Excel число «10».

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

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

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

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

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

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

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

Как найти слово в экселе — способы фильтрации

Excel содержит в себе 3 способа текстовой фильтрации, иными словами, фильтровать можно по 3 критериям вхождения.

Ячейка содержит, начинается с, либо заканчивается на — какой то конкретной паттерн.

В нашем случае для фильтрации целого слова, когда слово содержится в середине строки — во всех таких случаях после слова и перед будут располагаться пробелы. Поэтому мы можем задать именно такой критерий фильтрации, (пример — «* слово *») . Пробелы являются тем самым ограничителем, который позволит ячейки, в которых слово середине фразы, а фильтровать полностью и исключить ситуаций, когда оно будет входить в другие слова. «Звездочки» нужны для того, чтобы сообщить Excel, что в строке действительно есть пробелы — по умолчанию он их просто убирает перед поиском, по-видимому, воспринимая их наличие как случайность.

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

И критерий номер 3 — ячейка должна начинаться на наше слово — в таком случае пробел должен быть после нашего слова, по аналогичной причине.

Проблема заключается в том, что в Excel нельзя фильтровать сразу по 3 критериям — можно только по двум. Этой ситуации есть простой лайфхак:1. Перед каждой строкой и после нее добавляем пробел (=» «&A1&» «)2. После этого фильтруем по полученному столбцу уже наше слово с пробелами перед и после него и специальными «звездочками» перед и после этих пробелов соответственно (пример — «* слово *»), для того, чтобы отфильтровать слова, которое обязательно содержат перед ними и после них пробел. Это и будут ячейки, содержащие наше слово.

Как извлечь текст из ячейки с помощью Ultimate Suite

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

Вы просто переходите на вкладку Ablebits Data > Текст и нажимаете Извлечь (Extract) :

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

1. Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
2. Щелкните Вставить результаты (Insert Results). Готово!

Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula)  в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.

В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:

Чтобы извлечь имя пользователя:

Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? 😉

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

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Как работать с поиском в Excel

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

Далее рассмотрим варианты поиска данных по таблице Excel.

Вариант 1: Быстрый поиск

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

Использование инструмента происходит по данной инструкции:

1. Откройте нужный документ в Excel. В верхнем меню переключитесь на вкладку «Главная». Вообще она открыта всегда по умолчанию.
2. Теперь кликните по блоку «Найти и выделить». В контекстном меню выберите «Найти». Вместо описанных действий можно просто воспользоваться сочетанием клавиш Ctrl+F.

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

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

Вариант 2: поиск в указанном интервале

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

Инструкция к этому типу поиска выглядит следующим образом:

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

Вариант 3: Расширенный поиск

При обычном поиске в результат выдачи будут попадать все ячейки, которые содержат последовательный набор поисковых символов в любом виде не зависимо от регистра. Мало того, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Если в поиск задать цифру 4, то все равно будет показываться ячейка E2, даже если результат сумм A4 и C3 получился совершенно другим.

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

1. Вызовите окно обычного поиска. Это можно сделать через кнопку «Найти» или воспользовавшись сочетанием клавиш Ctrl+F.
2. В открывшемся окне настроек воспользуйтесь кнопкой «Параметры».
3. После этого появится целый ряд параметров, которые можно настроить под себя, например, можно сделать так, чтобы во время поиска учитывался регистр, в поиске выводились элементы, содержащие точное наименование. Также здесь можно задать, что конкретно просматривать, в какой части документа искать и даже формат, в котором должен быть результат.

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

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

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

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

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

Эти функции могут быть доступны не на всех языках.

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

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

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

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

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

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

Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Если Нач_позиция не больше 0 или больше, чем длина аргумента просматриваемый_текст , #VALUE! возвращено значение ошибки.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

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