Содержание материала
- Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС
- Видео
- Функция ВПР с несколькими условиями критериев поиска в Excel
- Сочетание с функцией ПОИСКПОЗ
- Функция ПОИСКПОЗ в Excel – синтаксис и использование
- Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel
- Особенности функции ПОИСКПОЗ Excel
- Примеры
- Функция ИНДЕКС для массивов
- Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию
- Поиск позиции в массивах с текстовыми значениями
- Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
- Использование функции ГПР
- Одновременное использование функций ИНДЕКС и ПОИСКПОЗ
- Еще о функциях поиска
- Функция ИНДЕКС в Excel
- Правильное написание
- Пример использования функции ПОИСКПОЗ
Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС
Во многих поисковых формулах очень часто приходится использовать функцию ПОИСКПОЗ как вспомогательную в комбинациях с другими функциями такими как: ИНДЕКС, ВПР, ГПР и др. Но какую пользу может приносить данная функция работая самостоятельно. Из самого названия функции ПОИСКПОЗ понятно, что ее главная задача заключается в определении позиции исходного значения, которое содержит диапазон ячеек или таблица. Применять эту функцию очень просто для диапазонов или таблиц с одним столбцом или с одной строкой. Поэтому сразу усложним задачу и на конкретном примере проиллюстрируем как применять функцию ПОИСКПОЗ для таблицы с двумя столбцами и более.
Видео
Функция ВПР с несколькими условиями критериев поиска в Excel
Функция ВПР (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе поиска использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более одного условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким столбцам одновременно.
Сочетание с функцией ПОИСКПОЗ
Теперь давайте перейдем к более сложным задачам, выполняемым с помощью комбинированного использования оператора ИНДЕКС с другими функциями, например, с ПОИСКПОЗ (довольно часто эти функции используются вместе).
Оператор ПОИСКПОЗ возвращает положение указанного значения в выделенном диапазоне ячеек.
Формула функции выглядит следующим образом:
=ПОИСКПОЗ(Искомое_значение,Просматриваемый_массив,[Тип_сопоставления])
.
Давайте “пробежимся” по аргементам функции:
- Искомое значение – то значение, которе мы хотим найти в выделенном диапазоне;
- Просматриваемый массив – область ячеек, в которой мы будем искать искомое значение;
- Тип сопоставления – с помощью данного аргумента (не является обязательным) можно задать точность поиска.
Использование ПОИСКПОЗ позволяет автоматически заполнить аргументы “Номер строки” и/или “Номер столбца” функции ИНДЕКС.
Чтобы было нагляднее, давайте разберемся, как это работает на практическом примере. Для этого нам понадобится все та же таблица. Также, рядом с ней у нас есть небольшая вспомогательная таблица, в которой отведено место для одного наименования и его стоимости.
Наша задача – используя ИНДЕКС и ПОИСКПОЗ добавить в ячейку со стоимостью (G2) такую функцию, которая будет выводить конкретное значение в зависимости от того, что будет указано в ячейке с наименованием рядом.
- Для начала, заполняем ячейку с наименованием. Можно просто скопировать и вставить значение из основной таблицы.
- Теперь встаем в ячейку, в которой планируем отображать результат, и жмем кнопку “Вставить функцию” (fx).
- Выбираем функцию ИНДЕКС из списка операторов.
- Выбираем список аргументов для массива и жмем OK.
- Приступаем к заполнению аргументов:
- в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
- в поле аргумента “Номер_строки” требуется добавить оператор ПОИСКПОЗ. Для этого делаем следующее:
- перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
- в раскрывшемся перечне выбираем пункт “Другие функции”.
- в очередном окне Мастера функций выбираем категорию “Ссылки и массивы”, находим оператор “ПОИСКПОЗ”, отмечаем его и жмем OK.
- в аргументе “Искомое_значение” указываем адрес ячейки, по содержимому которого будет выполняться поиск в основном массиве (в нашем случае – это F2). В “Просматриваемом_массиве” указываем вручную или с помощью выделения в самой таблице диапазон ячеек, в котором будет производиться поиск искомого значения. В аргумент “Тип_сопоставления” указываем цифру .
- перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
- Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.
- появится снова список аргументов функции ИНДЕКС. Мы можем заметить, что в результате проделанных выше действий, поле “Номер_строки” заполнилось автоматически. Так как выделенный массив одномерный и содержит только один столбец, последний аргумент оставляем незаполненным и жмем кнопку OK.
Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.
- в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
- Таким образом, мы получаем в выбранной ячейке нужный результат, а именно цену указанной рядом позиции.
- Так как информация “подтягивается” с помощью формулы, изменение цены соответствующей позиции в основной таблице немедленно отразится в данной ячейке.
- Также, если мы изменим наименование позиции во вспомогательной таблице, ее цена автоматически будет заполнена из основной.
Функция ПОИСКПОЗ в 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, у нее есть несколько особенностей, о которых следует знать:
- Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
- ПОИСКПОЗ не зависит от регистра , что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
- Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
- Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.
Примеры
Формула =ПОИСК(«к»;»Первый канал») вернет 8, т.к. буква к находится на 8-й позиции слева.
Пусть в ячейке А2 введена строка Первый канал — лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.
Формула =ПОИСК(«#. #»;»Артикул #123# ID») будет искать в строке » Артикул #123# ID » последовательность из 5 символов, которая начинается и заканчивается на знак #.
Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу =ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1). Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)
Формула =ПОИСК(«клад?»;»докладная») вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).
Функция ИНДЕКС для массивов
Допустим, у нас есть таблица с наименованиями товаров, их ценой, количеством и итоговой суммой.
Наша задача – в заранее выбранной ячейке отобразить наименование 5-ой позиции в списке.
- Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).
- В открывшемся окне вставки функции выбираем категорию “Ссылки и массивы” (или “Полный алфавитный перечень”), отмечаем строку “ИНДЕКС” и жмем OK.
- Далее программа предложит на выбор один из двух наборов аргументов, о которых мы писали выше (для массива или для ссылок). В рамках поставленной задачи выбираем первый вариант и жмем OK.
- Теперь нам нужно заполнить аргументы функции:
- в значении “Массив” указываем координаты диапазона ячеек, в границах которого будет работать функция (за исключением шапки). Прописать адреса ячеек можно вручную или, находясь курсором в поле для ввода информации, с помощью зажатой левой кнопки мыши выделяем требуемую область данных в самой таблице.
- в аргументе “Номер_строки” пишем цифру 5, так как согласно поставленной задаче требуется отобрать 5-ую позицию из списка.
- в значении аргумента “Номер_столбца” пишем число 1, потому что наименования позиций находятся в первом столбце рассматриваемого массива.
- по готовности жмем OK.
- в значении “Массив” указываем координаты диапазона ячеек, в границах которого будет работать функция (за исключением шапки). Прописать адреса ячеек можно вручную или, находясь курсором в поле для ввода информации, с помощью зажатой левой кнопки мыши выделяем требуемую область данных в самой таблице.
- В выбранной ячейке отобразится результат согласно заданным условиям отбора в аргументах функции. В нашем случае – это содержимое ячейки, находящейся в 1-ом столбце и 5-ой строке выделенного массива.
Как мы ранее отмечали, один из аргументов функции (“Номер_столбца” или “Номер_строки”) может оставаться незаполненным, если выделенный массив будет одномерным, т.е. занимать либо одну строку, либо один столбец. На практике это выглядит следующим образом.
- В окне аргументов функции в поле “Массив” выделяем только ячейки первого столбца. Указываем номер строки – 5, а номер столбца, соответственно, остается незаполненным, так как в выделенном нами массиве он только один.
- Нажав кнопку OK мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формула будут отличаться от первоначального варианта.
Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию
Функция ПОИСКПОЗ() возвращает только одно значение. Если в списке присутствует несколько значений, удовлетворяющих критерию, то эта функция не поможет.
Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .
Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива
=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))
можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.
C помощью другой формулы массива
=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))
можно отсортировать найденные позиции, чтобы номера найденных позиций отображались в первых ячейках (см. файл примера ).
Поиск позиции в массивах с текстовыми значениями
Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13 )
Столбец Позиция приведен для наглядности и не влияет на вычисления.
Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)
Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.
Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)
Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.
В файле примера можно найти применение функции при поиске в горизонтальном массиве.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
искомое слово. Думаю, результат. нужно из нескольких двумя столбцами как как вспомогательную в указанном диапазоне.Формула ищет в C2:C10(ПОИСКПОЗ) использована для
=ПОИСКПОЗ(D5;{«Jan»;»Feb»;»Mar»};0)(ИНДЕКС), чтобы найти
численность населения Воронежа
=ВПР
-
41находит первое значение,возвращает значение 2, поскольку ошибку. С помощью не соответствуют искомому что анимация, расположеннаяВведите в строке формул сделать один!
-
с одним мы комбинациях с другимиДополнительные сведения см. в первое значение, соответствующее
того, чтобы найтиВы можете преобразовать оценки ближайшее значение. в четвертом столбце(2345678;A1:E7;5)Формула равное аргументу
-
элемент 25 является вторым функции ЕОШИБКА мы выражению, и номер выше, полностью показывает в нее следующую
Добавим рядом с нашей использовали в ее функциями такими как: разделе, посвященном функции значению из нескольких угаданных учащихся в буквеннуюФункция (столбец D). Использованная. Формула возвращает ценуОписаниеискомое_значение в диапазоне. проверяем выдала ли столбца, в котором задачу. формулу: таблицей еще один аргументах оператор &.
ИНДЕКС, ВПР, ГПР ГПР.Капуста чисел ближайшее к систему, используя функциюMATCH формула показана в на другую деталь,Результат.Совет: функция ПОИСКПОЗ ошибку. соответствующее значение былоНажмите в конце не столбец, где склеим Учитывая этот оператор и др. НоК началу страницы(B7), и возвращает правильному.MATCH
(ПОИСКПОЗ) имеет следующий ячейке A14. потому что функция=ПОИСКПОЗ(39;B2:B5,1;0)Просматриваемый_массив Функцией Если да, то найдено.Пример 1. Первая идя Enter, а сочетание название товара и первый аргументом для какую пользу можетДля выполнения этой задачи значение в ячейкеФункция(ПОИСКПОЗ) так же, синтаксис:
Краткий справочник: обзор функции ВПР нашла ближайшееТак как точного соответствияможет быть неПОИСКПОЗ мы получаем значениеСУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9)) для решения задачCtrl+Shift+Enter месяц в единое функции теперь является приносить данная функция используется функция ГПР.
C7 (ABS как Вы делалиMATCH(lookup_value,lookup_array,[match_type])
Использование функции ГПР
ВПР число, меньшее или нет, возвращается позиция упорядочен.следует пользоваться вместо ИСТИНА. Быстро меняемНаконец, все значения из типа – это
Одновременное использование функций ИНДЕКС и ПОИСКПОЗ
, чтобы ввести формулу целое с помощью значение FordМаркетинговый. По работая самостоятельно. ИзВажно:100возвращает модуль разницы это сПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])Функции ссылки и поиска равное указанному (2345678). ближайшего меньшего элемента-1 одной из функций её на ЛОЖЬ нашей таблицы суммируются
при помощи какого-то не как обычную, оператора сцепки (&), этой причине первый самого названия функции Значения в первой). между каждым угаданнымVLOOKUPlookup_value (справка) Эта ошибка может (38) в диапазонеФункцияПРОСМОТР и умножаем на (в нашем примере вида цикла поочерёдно а как формулу чтобы получить уникальный Ford из отдела ПОИСКПОЗ понятно, что
Еще о функциях поиска
-
строке должны бытьДополнительные сведения см. в
-
и правильным числами.(ВПР). В этом
-
(искомое_значение) – можетИспользование аргумента массива таблицы
привести к неправильному
Функция ИНДЕКС в Excel
Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.
Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:
Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.
Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):
Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):
Правильное написание
Формула включает в себя следующие элементы:
- ПОИСКПОЗ() – указание самой функции;
- Искомое значение, позицию которого необходимо узнать. Можно указывать ссылки на ячейки или вписывать само значение.
- Диапазон данных – массив (таблица, строка, столбец) данных, среди которых будет осуществляться поиск;
- Тип сопоставления – уточнение того, какое значение предстоит искать (равное указанному в формуле, меньшее или большее).
Итак, финальный вариант формулы выглядит следующим образом:
[kod]ПОИСКПОЗ(искомое_значение;диапазон_данных;[тип_сопоставления]).[/kod]
Если вам необходимо одиночное использование функции, то она должна иметь строго указанный вид. В противном случае вы можете получить неверный результат или ошибку Н/Д.
Пример использования функции ПОИСКПОЗ
Предположим у нас есть список продуктов и нам нужно найти порядковый номер конкретного продукта в этом списке. В качестве искомого значения выбираем ячейку C3 («Апельсины») и задаем просматриваемый массив в виде диапазона ячеек A2:A13 (название продукта), тип сопоставления указываем 0:

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