Поиск по значению. Поиск, поискб (функции поиск, поискб). Совмещаем функции поискпоз и индекс для работы

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

В этой статье

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

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

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

функция ВПР .

Примеры ИНДЕКСов и СОВПАДЕНИй

Что означает:

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))

Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 (100 ) - первое значение, соответствующее капусты .

Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ .

Поиск значений в списке по вертикали по приблизительному совпадению

Для этого используйте функцию ВПР.

Важно:

В приведенном выше примере функция ВПР ищет имя учащегося, у которого есть 6 тардиес в диапазоне A2: B7. В таблице нет записи для 6 тардиес, поэтому функция ВПР ищет следующее самое высокое соответствие ниже 6 и находит значение 5, связанное с первым именем Дэйв , и, следовательно, возвращает Дэйв .

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

Поиск значений по вертикали в списке неизвестного размера с точным соответствием

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

Примечание: Этот подход используется, если данные находятся в диапазоне внешних данных, который вы обновляете каждый день. Вы знаете, что в столбце B есть Цена, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортирован по алфавиту.

C1 - это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match ("апельсины"; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 - количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales .

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР .

Поиск значений в списке по горизонтали с использованием приблизительного совпадения

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР .

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007)

Примечание: Надстройка "Мастер подстановок" прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками) .

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

При большом количестве данных на листе Excel рано или поздно придется выполнить поиск переделенного значения. Простой поиск выполняется с помощью команды: «Найти и выделить», которая находится в группе «Редактирование» на вкладке «Главная».

Смотрите также видеоверсию статьи .

Вызвать диалоговое окно поиска можно также и с помощью горячего сочетания клавиш «Ctrl+F «.

На самом деле, диалоговое окно называется «Найти и заменить «, поскольку оно решает эти две смежные задачи и если его вызвать через горячее сочетание «Ctrl+H «, то будет все тоже самое, только окно откроется с активированной вкладкой «Заменить».

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

В этом материале и рассмотрим нюансы и секреты окна поиска, которые большинству пользователей не знакомы.

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

Рассмотрим ситуацию, когда нужно найти сотрудника Комарова Александра Ивановича, но есть ряд факторов, из-за которых возникают сложности:

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

Комаров Александр Иванович может быть записан как: А. Комаров, Комаров Александр, Ка маров Александр И. и т.д. вариантом достаточно.

Чтобы выполнить нестрогий поиск, следует воспользоваться символами-заменителями их еще называют джокерными символами. Окно поиска в Excel поддерживает работу с двумя такими символами: «*» и «?»:

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

Здесь сразу же возникает вопрос, а что делать если необходимо выполнить поиск вопросительного знака или астериск (известный как знак умножения)? Все просто, достаточно перед искомыми знаками просто поставить тильду «~», соответственно, если необходимо выполнить поиск тильды, тогда необходимо поставить две тильды.

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

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

В тоже время, если немного перестроить запрос «*К?маров*», тогда записи будут найдены вне зависимости от активности параметра «Ячейка целиком».

При осуществлении поиска в Excel нужно помнить несколько важных правил:

1. Поиск выполняется в выделенной области, а если нет выделения, тогда на всем листе. Поэтому не стоит удивляться, если вы случайно выделите пару пустых ячеек, запустите окно поиска и не сможете ничего найти.
2. При поиске не учитывается форматирование, поэтому, никаких знаков обозначения валюты добавлять не стоит.
3. При работе с датами, лучше выполнять поиск их в формате по умолчанию для конкретной системы, в этом случае, в Excel будут найдены все даты, удовлетворяющие условию. Например, если в системе используется формат д/м/г, то поисковый запрос */12/2015 выведет все даты за декабрь 2015 года, независимо от того, как они отформатированы (28.12.2015, 28/12/2015, или 28 декабря 2015 и т.д.).

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

Это можно сделать с помощью ВПР или ГПР. ВПР используется если искомое значение размещено в столбце. ГПР - если в строчке. А сам порядок и принцип работы функций одинаковый. Данные функции можно использовать, если данные уникальны (т.е. не повторяются).

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

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

У вас должен получиться вот такой выпадающий список:

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

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

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

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

Интервальный_просмотр - ЛОЖЬ - поскольку нам нужно точное совпадение данных с заданным в поиске.

Нажимаем кнопку ОК.

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

Можно ли осуществлять поиск аналогичный поиску с использованием функции ВПР, но без сортировки данных? ВПР выдает в таком случае ошибку 9 .

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

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

Для упрощения поиска нужных значений можно сделать поле со списком, который будет состоять из данных, которые мы задаем для поиска (например, наименование товара). Для этого, установив курсор в свободную ячейку, переходим на вкладку Данные группу Работа с данными и нажимаем на кнопку Проверка данных , из выпадающего списка выбираем пункт Проверка данных… В диалоговом окне Проверка вводимых значений на вкладке Параметры в поле Тип данных выбираем Список и указываем диапазон, который содержит данные для поиска.

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

Искомое_значение - указываем ячейку, которая содержит поле со списком, созданный нами в самом начале.

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

Тип_сопоставления - указываем "0" (ноль) - это позволит функции находить первый аргумент, который соответствует искомому значению.

Нажимаем кнопку ОК.

Функция ПОИСКПОЗ указывает в какой строчке содержится искомое значение. Став курсором на ячейку, которая содержит формулу, в строке формул вы увидите синтаксис функции, выделяем его до знака "=" и копируем (нажав Ctrl+C) и нажимаем Enter, чтобы выйти из ячейки.

Затем, устанавливаем курсор в свободную ячейку, в которой у нас будут отображаться результаты, и с помощью вкладки Формулы или кнопки Вставить функцию на Панели формул выбираем функцию ИНДЕКС. Данная функция содержит два вида аргумента, о чем вам сразу же и сообщат, нам нужен "массив; номер_строки; номер_столбца".

В диалоговом окне Аргументы функции указываем следующие значения:

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

Номер_сроки - здесь мы вставляем функцию ПОИСКПОЗ путем нажатия клавиш Ctrl+V.

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

Нажимаем кнопку ОК.

В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel , а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.

Также в этой статье рассматривается комбинация формул ИНДЕКС ПОИСКПОЗ в Excel .

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

Функция ПОИСКПОЗ в Excel - синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

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

Искомое_значение (обязательный аргумент) - значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) - диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) - определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) - находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 - находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 - находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ , основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel - Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

Как вы только что видели, использовать ПОИСКПОЗ в Excel легко. Однако, как и у любой функции Excel, у нее есть несколько особенностей, о которых следует знать:

• Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
• ПОИСКПОЗ не зависит от регистра , что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
• Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
• Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.

Использование ПОИСКПОЗ в Excel - примеры формул

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

Как и многие другие функции Excel, ПОИСКПОЗ распознает следующие подстановочные знаки:

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

Примечание . Подстановочные знаки могут использоваться только в формулах ПОИСКПОЗ с параметром тип_сопоставления равному 0.

Формула ПОИСКПОЗ с подстановочными знаками полезна в ситуациях, когда требуется осуществить поиск не всей текстовой строки, а только некоторых символов или некоторой части строки. Чтобы проиллюстрировать это, рассмотрим следующий пример.

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

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

ПОИСКПОЗ("восток*"; A2:A8; 0)

Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и эту ячейку с подстановочным символом, например:

ПОИСКПОЗ(E1&"*"; A2:A8; 0)

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании «Восток-авто »:

Функция ПОИСКПОЗ в Excel - Пример функции ПОИСКПОЗ Excel с подстановочными знаками

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

ПОИСКПОЗ("б?йкер"; A2:A8;0)

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

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

ПОИСКПОЗ(ИСТИНА;СОВПАД(искомое_значение;просматриваемый_массив); 0)

Логика этой формулы такова:

• Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
• Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска - A2:A8, тогда формула выглядит следующим образом:

ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:

Функция ПОИСКПОЗ в Excel - Пример функции ПОИСКПОЗ Excel с учетом регистра

Сравнить 2 столбца на совпадения и различия (ЕНД и ПОИСКПОЗ)

Проверка двух списков на совпадения и различия - одна из наиболее распространенных задач в Excel, и это можно сделать различными способами. Использование формулы ЕНД/ПОИСКПОЗ является одним из них:

ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); "Нет в Списке 1"; "")

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

• Функция ПОИСКПОЗ выполняет поиск значения из Списка 1 в Списке 2. Если значение найдено, она возвращает относительное положение, в противном случае ошибку #Н/Д.
• Функция ЕНД в Excel выполняет только одно: проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если заданное значение является ошибкой #Н/Д, функция возвращает ИСТИНА, в противном случае - ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из Списка 1 не найдено в Списке 2 (ПОИСКПОЗ возвращает ошибку #Н/Д).
• Поскольку для других пользователей будет сложно интерпретировать результаты в формате ИСТИНА/ЛОЖЬ, то мы будем использовать функцию ЕСЛИ для отображения текста «Нет в Списке 1» для значений, которых нет в Списке 1.

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 - самая верхняя ячейка):

ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));"Нет в Списке 1"; "")

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));"Нет в Списке 1"; "")

На следующем изображении демонстрируются обе формулы в действии:

Функция ПОИСКПОЗ в Excel - Сравнение 2 столбцов на совпадения с использованием ЕНД и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ в Excel

С функцией ПОИСКПОЗ Excel мы уже знакомы. Давайте теперь рассмотрим, как можно использовать комбинацию ИНДЕКС ПОИСКПОЗ в Excel. Начнем с синтаксиса функции ИНДЕКС.

Синтаксис и использование функции ИНДЕКС

Функция ИНДЕКС Excel возвращает значение в массиве, основанное на указанных вами строках и столбцах. Синтаксис функции ИНДЕКС прост:

ИНДЕКС(массив; номер_строки; [номер_столбца])

Вот очень простое объяснение каждого параметра:

• массив - это диапазон ячеек, из которого вы хотите вернуть значение.
• номер_строки - номер строки в массиве, из которого вы хотите вернуть значение. Если этот параметр опущен, требуется номер_столбца.
• номер_столбца - номер столбца в массиве, из которого вы хотите вернуть значение. Если этот параметр опущен, требуется номер_строки.

Если используются оба параметра номер_строки и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении указанной строки и столбца.

Вот простейший пример формулы ИНДЕКС:

ИНДЕКС(A1:C10;2;3)

Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.

Очень легко, не так ли? Однако при работе с реальными данными вы вряд ли знаете, какие строки и столбцы вам нужны, поэтому вам нужна помощь функции ПОИСКПОЗ .

ИНДЕКС ПОИСКПОЗ в Excel пример

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

Функция ПОИСКПОЗ определяет относительное положение значения поиска в указанном диапазоне ячеек. А функция ИНДЕКС принимает это число и возвращает значение в соответствующую ячейку.

Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:

ИНДЕКС(столбец для возвращения значения, ПОИСКПОЗ (значение поиска, столбец для поиска, 0))

Для лучшего понимания рассмотрим наглядный пример. Предположим, у нас есть список стран с населением, подобных этому:

Функция ПОИСКПОЗ в Excel - Исходные данные для формулы ИНДЕКС ПОИСКПОЗ в Excel

Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ :

ИНДЕКС($C$2:$C$11;ПОИСКПОЗ("Россия";$B$2:$B$11;0))

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

• Функция ПОИСКПОЗ выполняет поиск значения «Россия» в столбце B, точнее в ячейках B2:B11 и возвращает номер 7, потому что «Россия» находится на седьмом месте в списке.
• Функция ИНДЕКС принимает «7» во втором параметре (номер_строки), который указывает, из какой строки вы хотите вернуть значение, и превращается в простую формулу =ИНДЕКС($C$2:$C$11, 7). То есть формула производит поиск в ячейках C2-C11 и возвращает значение ячейки в седьмой строке, то есть ячейке C8, потому что мы начинаем отсчет со второй строки.

И вот результат, который мы получаем в Excel:

Функция ПОИСКПОЗ в Excel - ИНДЕКС ПОИСКПОЗ пример формулы

Обратите внимание! Количество строк и столбцов в массиве ИНДЕКС должно соответствовать значениям в параметрах номер_строки и/или номер_столбца функции ПОИСКПОЗ соответственно. В противном случае формула вернет неверный результат.

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

Синтаксис функции

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

Искомый_текст - текст, который требуется найти.

Просматриваемая_строка - текст, в которой ищется Искомый_текст .

Нач_позиция - позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

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

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

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

Примеры

Формула =ПОИСК("к";"Первый канал") вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал - лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК("#???#";"Артикул #123# ID") будет искать в строке "Артикул #123# ID " последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы "а" в строке "мама мыла раму" используйте формулу =ПОИСК("а";"мама мыла раму";ПОИСК("а";"мама мыла раму")+1). Чтобы определить есть ли третье вхождение буквы "м" в строке "мама мыла раму" используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ("мама мыла раму";"м";"";3))=ДЛСТР("мама мыла раму");"Нет третьего вхождения м";"Есть третье вхождение м")

Формула =ПОИСК("клад?";"докладная") вернет 3, т.е. в слове "докладная" содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).

Функция НАЙТИ() vs ПОИСК()

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

Связь с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР()

Функция ПОИСК() может быть использована совместно с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР() .

Например, в ячейке А2 содержится фамилия и имя "Иванов Иван", то формула =ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1) извлечет фамилию, а =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2)) - имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .