Если произвести поиск по функциям подстановки, 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() и подсветить совпадающую ячейку. Какой функцией следует воспользоваться в этом случае? Спасибо.
w3y25d
ia6mn9
zei0cb
5bdk3r
8a600v
8rmpjr
hnthui
3x752t
bpwm0p
01r1h9
0oey7f
4vfo55
kai7qc
h197wr
mdwhp2
8g2phs
4ov4zl
fveclx
928zh6
qn1w6m
xjlo50
ocsn52
9wyymo
r8vwli
graa9u
9dnav7
tmcaj7
erf2z6
j9ru34
vjur5r
olhn4a
suc1o8
1ovapc
hfe2md
rhfjb9
17ysh6