Если произвести поиск по функциям подстановки, 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)
В начале было непривычно, был огромный соблазн вставить еще одну колону и работать, как всегда работал. Но со временем использование функции ИНДЕКС вошло в привычку. Оказалось, что это быстрее и требует меньших манипуляций. Так что в следующий раз, когда у вас возникнет желание задать номеру столбца отрицательное число в ВПР, воспользуйтесь сочетанием двух странных функций ИНДЕКС и ПОИСКПОЗ, чтобы решить свою проблему.
На крайнем скрине ячейка B10 скорее всего дб равна A10.
На данном скрине я хотел продемонстрировать, что при использовании формулы ИНДЕКС, искомый элемент не обязательно должен находиться левее подстановочного
опечатка..( имел ввиду A1=Табельный номер
Спасибо, не сразу и заметил. Исправил)
Благодарю автора за полезный материал и подробное описание.
Дорого Вам дня, я использую функции ИНДЕКС и ПОИСКПОЗ при расчете зарплаты, в этом месяце один работник у нас уволился, я сортирую список сотрудников с прошлого месяца (или с января) но вместо уволенного сотрудника в новом месяце в ячейке выходи #Н/Д.
Как нужно сделать формулу что бы место уволенного сотрудника на ячейке выходило «пусто» и «ноль»
Воспользуйтесь функцией ЕСЛИОШИБКА.
Здравствуйте.
Какую надстройку мне использовать для ВПР если хочу сравнить две таблицы
по словам. В одной ФИО полностью, в другой ФИО полностью и внутренний номер.
Пример: Иванов Алексей Петрович и Иванов Алексей Петрович PS010.
Можно ли искать по маске по первым 10 символам? и как такое осуществить?
Добрый день!
Вам необходима функция ПСТР. Ее аргументы: ячейка, в которой необходимый текст; порядковый номер символа, с которого начинать возвращение данных; количество символов, которые необходимо вернуть.Примерно так: =ПСТР(А1;1;10)
У нее есть «сестры» ЛЕВСИМВ и ПРАВСИМВ, которые, соответственно, возвращают заданное количество символов слева и справа
Если необходимо сравнить две таблицы, пожалуй, проще всего это будет представить как
=ВПР(ПСТР(А1;1;10);’диапазон’;’номер столбца’;0)
Соответственно, первая таблица с номером, вторая без него
Возвращать будет значение или Н/Д
Если Н/Д, то удовлетворяющего данным значениям ничего нет 🙂
ВАЖНО! Вы должны быть уверены, что возвращая только 10 знаков, вы вернете полностью значение без номера
В идеале тогда нужно создать дополнительный столбец в таблице без номера, куда с помощью ПСТР или ЛЕВСИМВ вынести только первые 10 знаков, и сравнивать уже конкретно с ним
Pingback: Четыре способа использования ВПР с несколькими условиями | Exceltip
Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.
Здравствуйте. Имеется таблица (календарь, дата-день недели, в несколько столбиков). Требуется несколько (40) дней (не смежных) сравнить с =TODAY() и подсветить совпадающую ячейку. Какой функцией следует воспользоваться в этом случае? Спасибо.