Продвинутый Excel: Почему я перестал пользоваться функцией VLOOKUP (ВПР)

Я уже писал про то, что функция VLOOKUP (ВПР), наверное, самая полезная функция после простых арифметических операций в Excel (про VLOOKUP тут, тут и тут). Но недавно я перестал ей пользоваться и вот, почему.

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

2.     Столбец, в котором мы ищем должен быть левее, чем столбец с искомым значением

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

4.     Вставленный в массиве столбец может «сломать» формулу. Когда вы вставляете внутри массива столбец, формула автоматически не изменяет номер столбца, из которого вы берете значение. А представьте, что вы работаете с чужим файлом и просто не знаете о том, что где-то есть ссылка на этот массив с помощью функции VLOOKUP (ВПР)

5.     Это формула, которая может значительно увеличивать объем расчетов там, где это совершенно не нужно. Представьте, что вы работаете с массивом с 50 колонками. Значение, по которому вы ищете находится в крайней левой колонке, а значение, которое вам нужно – в правой. Изменение любой ячейки из 48 никак не влияющих на значение столбцов заставит вашу формулу пересчитаться. Зачем?

И что теперь?

Действительно. Я не призываю вас вместо VLOOKUP (ВПР) пользоваться Ctrl + C, Alt + Tab, Ctrl + F, Ctrl + V, Enter, Esc, Ctrl + C, Alt + Tab, Tab, Ctrl + V (и это если вы знаете цену клавиатуре и не пользуетесь мышью). Ни в коем случае!

Я предлагаю вам пользоваться сочетанием функций INDEX (ИНДЕКС) + MATCH (ПОИСКПОЗ). Работает это так.

INDEX (массив_откуда_взять, [номер_строки], [номер_столбца])

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

Если пропустить значение номер_строки (просто ставится две запятых или точки с запятой подряд, в зависимости от ваших настроек), то программа автоматически возьмет значение из первой строки.

Номер столбца можно вообще опустить (закрыть скобку после номера строки), тогда программа возьмет значение из первого столбца массива.

MATCH (что_искать, где_искать, как_искать)

что_искать – ссылка на ячейку или значение, которое будем искать.

где_искать – массив данных, в котором ищем (самая левая колонка в случае с VLOOKUP (ВПР))

как_искать — -1, 0 или 1. Обычно используем 0 – точное совпадение (отлично работает в несортированных массивах). -1 – поиск первого значения, которое меньше искомого (массив данных должен быть отсортирован по ключу по убыванию). 1 – поиск первого значения, которое больше искомого (массив должен быть отсортирован по возрастанию).

Конкретный пример.

Читаем формулу изнутри наружу, как обычно. В этом примере формула определяет номер строки, соответствующей значению из колонки A:A в диапазоне D:D, а потом с помощью функции INDEX выбирает значение с этим номером строки из колонки O:O.

См. также: Почему я пользуюсь ссылками на колонки, а не на конкретные диапазоны

И еще один пример

Одно пояснение: обратите внимание, как меняется формула при копировании влево. Больше не надо менять номер столбца, если данные организованы с периодами в столбцах.

Полезные материалы и курсы обучения по Microsoft Excel
Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.