Функция поиск() в excel

Способ 2

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

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

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

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

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

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

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

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

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

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

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

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

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

Взаимодействие с поиском по таблице 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 представлен довольно функциональный поиск по элементам. Мало того, вы можете искать необходимые значения не только на конкретном листе, но и по всему файлу или какой-то небольшой области.

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

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

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

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

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

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

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

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

Параметры

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

Обратите внимание, что в окне поиска, справа, есть кнопка — параметры, нажмите на неё

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

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

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

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

Регистр

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

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

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

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

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

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

Фильтры

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

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

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

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

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

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

Как искать слова и фразы в Эксель, видео

Ещё, обязательно прочитайте статьи, посвящённые данной программе:

.

.

.

.

Как правильно искать в таблицах Excel

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

Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H). От того, какую именно команду вы выполните, зависит, на какой из двух вкладок откроется диалоговое окно.

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

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

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

При поиске с помощью диалогового окна Найти и заменить можно использовать два подстановочных знака:

• ? — соответствует любому символу;
• * — соответствует любому количеству символов.

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

Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (

). Например, следующая строка поиска находит текст *NONE*: -*N0NE

* Чтобы найти символ тильды, поставьте в строке поиска две тильды.

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

• Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
• Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
• Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

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

Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат. Если вы ищете дату, к которой применено форматирование по умолчанию, Excel находит даты, даже если они отформатированы различными способами. Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

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

4 комментариев

А как быть, если в поле «Наименование» вперемешку числовые и текстовые значения? У меня эксель числовые значения игнорирует, т.к. они числятся у него, как текстовые. К слову сказать, в таблице 56 тыс. записей и вручную всё пройти довольно сложно.

Не понял Вашего вопроса. Судя по всему, у Вас все данные хранятся, как текст. А что не работает?

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

Здравствуйте. Смотря что Вы называете однотипными данными. Опишите, пожалуйста, подробнее.

Проверка ячейки на наличие в ней текста (без учета регистра)

​ формул, нужно переставить​​ случае, при формировании​. Дальнейшие действия точно​ все ячейки, которые​ «Найти и заменить».​ в диапазоне таблицы​Чтобы найти какой товар​ определенного товара и​ максимальном объеме и​ стоит знак «+».​ НАЙТИ. Она будет​ в таблице. Это​=НАЙТИ(«в»;A2)​ использовать подстановочные знаки,​ использование функций​введите текст —​ с тем очень​ переключатель из позиции​ результата будет учитываться​ такие же, что​ содержат данный последовательный​ Кроме того, в​

​ Excel по столбцам​ обладал максимальным объемом​ другие показатели. Например,​ в каком месяце​Как всегда, функция прописывается​ выглядеть так.​ может быть цифро-буквенное​Позиция первой «в» в​ воспользуйтесь функцией ПОИСК​НАЙТИ​ или номера —,​ функциональный набор инструментов​«Формулы»​

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

Поиск ячеек, содержащих текст

​ продаж в определенном​ минимальное или среднее​ была максимальная продажа​

1. ​ в ячейке, следующей​=НАЙТИ(«!»; A2; 1)​ сочетание, только цифры​

   ​ ячейке A2​ или ПОИСКБ.​и​

2. ​ вам нужно найти.​​ поиска. Для того,​​в позицию​​ точное совпадение. Если​​ способе. Единственное отличие​​ внутри слова. Например,​​ расширенного поиска данных.​​По сути содержимое диапазона​​ месяце следует:​

3. ​ значение объема продаж​​ Товара 4.​​ за последней в​При этом, для указания​ или только буквы,​15​Если в качестве аргумента​​НАЙТИБ​​ Или выберите из​

   ​ чтобы произвести простейший​​«Значения»​ вы введете слово​

4. ​ будет состоять в​ релевантным запросу в​​Простой поиск данных в​​ нас вообще не​В ячейку B2 введите​ используя для этого​​Чтобы выполнить поиск по​​ первой строчке. Формула​

5. ​ области поиска можно​​ пробелы или знаки.​​=НАЙТИ(«и»;A2;3)​ «искомый_текст» задана пустая​в Microsoft Excel.​ раскрывающегося списка​ писк, достаточно вызвать​. Кроме того, существует​

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

6. ​ Нужно помнить, что​​Позиция первой «и» в​​ строка («»), функция​​Функции НАЙТИ и НАЙТИБ​​Найти​

   ​ поисковое окно, ввести​​ возможность поиска по​ то в поисковую​ выполняется только в​ считаться слово «Направо».​ найти все ячейки,​ просто счетчик строк.​​ – это значение​​ СРЗНАЧ. Вам ни​

​В ячейку B1 введите​​=НАЙТИ(«+»; A1; 1)​ номерами. По окончанию​

​ функция учитывает введенный​ строке А2, начиная​​ НАЙТИ выводит значение,​​ находят вхождение одной​

Проверка соответствия содержимого ячейки определенному тексту

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

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

​ в которых содержится​ То есть изменить​​ будет использовано в​​ что не препятствует,​​ значение Товара 4​​Нажав Enter, вы получите​​ набора функции, нажмите​​ регистр. Результаты поиска​

​ с третьего знака​​ равное первому знаку​​ текстовой строки в​​Примечание:​

support.office.com>

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

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

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

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

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

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

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

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

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

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

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

Поиск куска текста в ячейке

​ проверки, если ячейка​​ по конкретному словосочетанию,​ столбца.​ поиска? Именно для​ которой пользователь сделал​.​ Только вместо A1​ формулу с помощью​ а номер строки​ помощью функции СТОЛБЕЦ​ проиллюстрируем пример на​ появляется номер места​оклад (размер оплаты).​​ равенства. Именно так​ номер знака, считая​НАЙТИ(искомый_текст;просматриваемый_текст;)​​Примечание:​

​ содержит текст.​​ а найти ячейки,​В графе​​ этих целей существует​

​ щелчок.​​При нажатии на кнопку​ надо B38)​ функций ИНДЕКС и​ в диапазоне где​ создаем массив номеров​ схематической таблице, которая​​ заданных букв в​

​Для отчета нужно выбрать​​ вы дадите знать​ от левого края​НАЙТИБ(искомый_текст;просматриваемый_текст;)​

planetaexcel.ru>

​ Чтобы прекратить процесс поиска,​

• Как в excel таблице найти нужное слово
• Как найти слово в таблице в excel
• Как в эксель найти слово в таблице
• Excel как в таблице найти нужное значение
• Как в эксель найти дубли
• Как в эксель найти циклические ссылки
• В эксель функция найти
• Как перевести таблицу из ворда в эксель без потери данных
• Как распечатать эксель с таблицей на всю страницу
• Готовые таблицы эксель
• Автосохранение эксель где найти
• Найти дубликаты эксель

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):

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

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; ):

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

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

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

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

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

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

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

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

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