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

Описание

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

ПОИСК("н";"принтер")

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

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

ПОИСК("base";"database")

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

Важно:

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

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

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

Синтаксис

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

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

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

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

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

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

Замечание

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

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

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

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

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

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

Примеры

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

Данные
Доход: маржа
Здесь "босс".
Формула	Описание	Результат
ПОИСК("и";A2;6)	Позиция первого знака "и" в строке ячейки A2, начиная с шестого знака.
ПОИСК(A4;A3)	Начальная позиция строки "маржа" (искомая строка в ячейке A4) в строке "Доход: маржа" (ячейка, в которой выполняется поиск - A3).
ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;"объем")	Заменяет слово "маржа" словом "объем", определяя позицию слова "маржа" в ячейке A3 и заменяя этот знак и последующие пять знаков текстовой строкой "объем."	Доход: объем
ПСТР(A3;ПОИСК(" ";A3)+1,4)	Возвращает первые четыре знака, которые следуют за первым пробелом в строке "Доход: маржа" (ячейка A3).
ПОИСК("""";A5)	Позиция первой двойной кавычки (") в ячейке A5.
ПСТР(A5;ПОИСК("""";A5)+1;ПОИСК("""";A5;ПОИСК("""";A5)+1)-ПОИСК("""";A5)-1)	Возвращает из ячейки A5 только текст, заключенный в двойные кавычки.

Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

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

Предположим, вы получаете от поставщика/заказчика/клиента заполненную таблицу с перечнем продукции:

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

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


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

(49,5 KiB, 13 249 скачиваний)

На листе "Заказ" в этом файле таблица, полученная от заказчика, а на листе "Каталог" наши артикулы.
Сама формула на примере файла будет выглядеть так:

ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2);Каталог!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)
эта формула вернет название артикула, если в тексте есть хоть один артикул из каталога и #Н/Д (#N/A) если артикул не найден в каталоге.
Прежде чем облагородить эту формулу всякими дополнениями(вроде виде убирания ненужных #Н/Д) давайте разберемся как она работает.
Функция ПРОСМОТР (LOOKUP) ищет заданное значение(2) в указанном диапазоне(массиве - второй аргумент). В качестве диапазона обычно приводится массив ячеек, но функция ПРОСМОТР имеет первую нужную нам особенность - она старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом. Иными словами она вычисляет выражение в этом аргументе, чем мы и пользуемся, подставив в качестве второго аргумента выражение: 1/ПОИСК(Каталог!$A$2:$A$11;A2) . Часть ПОИСК(Каталог!$A$2:$A$11;A2) ищет поочередно каждое значение из списка Каталога в ячейке A2 (наименование из таблицы Заказчика). Если значение найдено, то возвращается номер позиции первого символа найденного значения. Если значение не найдено - возвращается значение ошибки #ЗНАЧ!(#VALUE!). Теперь вторая особенность: функция требует расположения данных в массиве в порядке возрастания. Если данные расположены иначе - функция будет просматривать массив до тех пор, пока не найдет значение больше искомого, но максимально к нему приближенное(хотя если данные позволяют - для более точного поиска все же лучше отсортировать список по возрастанию). Поэтому сначала мы 1 делим на выражение ПОИСК(Каталог!$A$2:$A$11;A2) , чтобы получить массив вида: {0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}
Ну а в качестве искомого значения мы подсовываем функции число 2 - заведомо большее число, чем может вообще встретиться в массиве(т.к. единица, поделенная на любое число будет меньше двух). И как результат мы получим позицию в массиве, в которой встречается последнее совпадение из каталога. После чего функция ПРОСМОТР запомнит эту позицию и вернет значение из массива Каталог!$A$2:$A$11 (третий аргумент), записанное в этом массиве для этой позиции.
Вы можете просмотреть этапы вычисления функции самостоятельно для каждой ячейки, я здесь просто приведу этапы чуть в расширенном для понимания виде:

1. =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2);Каталог!$A$2:$A$11)
2. =ПРОСМОТР(2;
   1/{55:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};
   Каталог!$A$2:$A$11)
3. =ПРОСМОТР(2;{0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};Каталог!$A$2:$A$11)
4. =ПРОСМОТР(2;
   1;
   {"FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX292710"})
5. ="FM2-3320"

Теперь немного облагородим функцию и сделаем еще пару реализаций
Реализация 1:
Вместо артикулов и #Н/Д выведем для найденных позиций "Есть" , а для отсутствующих "Не найден в каталоге" :
=ЕСЛИ(ЕНД(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2)));"Не найден в каталоге";"Есть")
=IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),"Не найден в каталоге","Есть")
работа функции проста - с ПРОСМОТР(LOOKUP) разобрались, поэтому остались только ЕНД и ЕСЛИ.
ЕНД (ISNA) возвращает ИСТИНА (TRUE) если выражение внутри неё возвращает значение ошибки #Н/Д (#N/A) и ЛОЖЬ (FALSE) если выражение внутри не возвращает значение этой ошибки.
ЕСЛИ (IF) возвращает то, что указано вторым аргументом если выражение в первом равно ИСТИНА (TRUE) и то, что указано третьим аргументом, если выражение первого аргумента ЛОЖЬ (FALSE) .

Реализация 2:
Вместо #Н/Д выведем "Не найден в каталоге" , но при этом если артикулы найдены - выведем названия этих артикулов:
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);"Нет в каталоге")
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),"Нет в каталоге")
Про функция ЕСЛИОШИБКА (IFERROR) я подробно рассказывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0 .
Если вкратце, то если выражение, заданное первым аргументом функции, возвращает значение любой ошибки, то функция вернет то, что записано вторым аргументом(в нашем случае это текст "Не найден в каталоге"). Если же выражение не возвращает ошибку, то функция ЕСЛИОШИБКА запишет то значение, которое было получено выражением в первом аргументе(в нашем случае это будет наименование артикула).

Реализация 3
Надо не просто определить какому артикулу соответствует, но и вывести цену для наименования по этому артикулу(сами цены должны быть расположены в столбце B листа Каталог):
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),"")

Пара важных замечаний:

• данные на листе с артикулами не должны содержать пустых ячеек. Иначе с большой долей вероятности формула будет возвращать значение именно пустой ячейки, а не то, которое подходит под условия поиска
• формула осуществляет поиск таким образом, что ищется любое совпадение. Например, в качестве артикула записана цифра 1 , а в строке наименований может встречаться помимо целой 1 еще и 123 , 651123 , FG1412NM и т.п. Для всех этих наименований может быть подобран артикул 1, т.к. он содержится в каждом наименовании. Как правило это может произойти, если артикул 1 расположен в конце списка

Поэтому желательно перед использованием формулы отсортировать список по возрастанию(от меньшего к большему, от А до Я).

В приложенном в начале статьи примере вы найдете все разобранные варианты.

Если же вам понадобится выводить все наименования, то можно воспользоваться функцией СОДЕРЖИТ_ОДНО_ИЗ из моей надстройки MulTEx .

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

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

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

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

• Искомое значение – значение, которое будем искать. Это обязательный аргумент;
• Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
• Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
• Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения .

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

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Часто возникает вопрос « Как найти в Excel »? В Excel можно найти любую информацию: текст, часть текста, цифру, номер телефона, эл. адрес , фамилию, формулу, примечание, формат ячейки, т.д.
Найти ячейку на пересечении строки и столбца Excel – « Как найти в Excel ячейку на пересечении строки и столбца » (функция "ИНДЕКС" в Excel ).
Найти и перенести в другое место в Excel (например, в бланк) несколько данных сразу – смотрите в статье «Найти в Excel несколько данных сразу» (функция "ВПР" в Excel ).
Или найти ячейку с ссылкой в формуле Excel, чтобы заменить ссылку, смотрите « Поменять ссылки на другие листы в формулах Excel ».
Найти в Excel ячейки с примечанием - статья "Вставить примечание в Excel" .
Для быстрого поиска существует сочетание клавиш – Ctrl + F . Нажимаем клавишу Ctrl и, удерживая её, нажимаем клавишу F. Появится окно поиска.
Ещё окно поиска можно вызвать так - на закладке "Главная" нажать кнопку "Найти и выделить".
На вкладке «Найти» в ячейке «найти» пишем искомое слово (можно часть слова) и нажимаем «найти далее». Будет найдено первое такое слово. Затем нажимаете «найти далее» и поиск перейдет на второе такое слово.

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

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