Альтернатива ВПР: функции ИНДЕКС и ПОИСКПОЗ

Здравствуйте, друзья. Когда мы собираемся что-то искать в таблице, первая функция, которая приходит в голову – это ВПР. Все её знают, любят и активно пользуются. Но что, если есть другой, не менее удачный способ найти значение в таблице и вернуть что-то ему соответствующее?

Пусть у нас есть таблица ежемесячных продаж. Поиск продаж какого-то менеджера, например, в марте, решается с помощью ВПР:

Как еще можно это сделать? Давайте размышлять.

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

В функции мы вручную указали, что нужно вернуть значение на пересечении 8-й строки и 4 столбца, что соответствует искомым данным. Но такой «ручной» подход не имеет смысла. Лучше поручить определение номера строки и столбца Экселю. Сделаем это с помощью функции ПОИСКПОЗ.

Найдем порядковый номер строки, в которой расположена фамилия Соколов:

Аналогично, получим номер столбца с нужным месяцем:

Теперь подставим 2 формулы поиска в функцию ИНДЕКС. Получим такую конструкцию:

=ИНДЕКС( B2:G27 ; ПОИСКПОЗ(K2;B2:B27;0) ; ПОИСКПОЗ(K3;B2:G2;0) )

Здесь первый аргумент – ссылка на массив продаж, второй – расчет номера строки в зависимости от выбранной фамилии, третий – получение столбца по заданному месяцу.

Для более удобного выбора фамилии и месяца, я сделал в таблице итогов выбор в выпадающих списках, и теперь мне нужно писать эти данные вручную. Хотите поступить так же? Читайте эту статью.

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

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

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