Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

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

Функция ВПР()

Формула ВПРПредположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

формула ВПР не работаетНо случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

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

Функция ИНДЕКС()

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

формула ИНДЕКС

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

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

Она говорит Excel: «Найди искомое_значение  в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

Описание формулы ПОИСКПОЗ

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

=ИНДЕКС($A$2:$B$6;ПОИСКПОЗ(A10;$B$2:$B$6;0);1)

формула индекс и поискпоз

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

формула индекс и поискпоз результат


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

  1. Дорого Вам дня, я использую функции ИНДЕКС и ПОИСКПОЗ при расчете зарплаты, в этом месяце один работник у нас уволился, я сортирую список сотрудников с прошлого месяца (или с января) но вместо уволенного сотрудника в новом месяце в ячейке выходи #Н/Д.
    Как нужно сделать формулу что бы место уволенного сотрудника на ячейке выходило «пусто» и «ноль»

  2. Здравствуйте.
    Какую надстройку мне использовать для ВПР если хочу сравнить две таблицы
    по словам. В одной ФИО полностью, в другой ФИО полностью и внутренний номер.
    Пример: Иванов Алексей Петрович и Иванов Алексей Петрович PS010.
    Можно ли искать по маске по первым 10 символам? и как такое осуществить?

    • Добрый день!
      Вам необходима функция ПСТР. Ее аргументы: ячейка, в которой необходимый текст; порядковый номер символа, с которого начинать возвращение данных; количество символов, которые необходимо вернуть.Примерно так: =ПСТР(А1;1;10)
      У нее есть «сестры» ЛЕВСИМВ и ПРАВСИМВ, которые, соответственно, возвращают заданное количество символов слева и справа
      Если необходимо сравнить две таблицы, пожалуй, проще всего это будет представить как
      =ВПР(ПСТР(А1;1;10);’диапазон’;’номер столбца’;0)
      Соответственно, первая таблица с номером, вторая без него
      Возвращать будет значение или Н/Д
      Если Н/Д, то удовлетворяющего данным значениям ничего нет 🙂
      ВАЖНО! Вы должны быть уверены, что возвращая только 10 знаков, вы вернете полностью значение без номера
      В идеале тогда нужно создать дополнительный столбец в таблице без номера, куда с помощью ПСТР или ЛЕВСИМВ вынести только первые 10 знаков, и сравнивать уже конкретно с ним

  3. Pingback: Четыре способа использования ВПР с несколькими условиями | Exceltip

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

  5. Здравствуйте. Имеется таблица (календарь, дата-день недели, в несколько столбиков). Требуется несколько (40) дней (не смежных) сравнить с =TODAY() и подсветить совпадающую ячейку. Какой функцией следует воспользоваться в этом случае? Спасибо.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *