Битва титанов в Excel: ВПР и ИНДЕКС(ПОИСКПОЗ) против нового ПРОСМОТРX. Почему "старички" всё ещё могут быть полезны?⁠⁠

Всем доброго дня. Сегодня поговорим про очень популярные функции.

Каждый, кто хоть раз собирал отчеты в Excel, знает: жизнь делится на "до" и "после" изучения функции ВПР (VLOOKUP). Это как познать дзен. Вы наконец-то перестаете судорожно искать совпадения глазами и копировать их вручную.

Но в Excel 2021 Microsoft выкатила убийцу старых формул — функцию ПРОСМОТРX (XLOOKUP). Многие после этого начали утверждать, что ВПР пора закопать, а связку ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH) — сдать в музей.

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

1. Легкая прогулка и капризы новичка

Начнем с хорошего. ПРОСМОТРX — действительно классная штука. Она не требует считать столбцы, не ломается, если вы ищете данные справа налево, и в неё уже встроен "предохранитель" от ошибок (замена ЕСЛИОШИБКА) в виде отдельного аргумента.

Но тут включается суровая офисная реальность: вам нужно заполнить итоговую таблицу из огромной базы. Причем столбцы в вашей таблице идут вразнобой — например, сначала "Статус", потом "Стоимость", а потом "Количество" (а в исходнике порядок другой).

• Что делает ПРОСМОТРX: увы, пасует. Вам придется писать формулу отдельно для каждого (!) столбца. Да, её можно настроить и закрепить, но перетаскивать диапазоны вручную для каждой колонки или переписывать — сомнительное удовольствие.

• Как отвечает старый добрый ВПР: в связке с функцией ПОИСКПОЗ (MATCH) старина ВПР делает это одной левой. Мы просто заставляем ПОИСКПОЗ автоматически определять номер нужной колонки по её заголовку.

• Результат: пишем ровно одну формулу в самую первую ячейку, протягиваем её на всю таблицу (и вниз, и вбок) — и готово! Более того, если завтра ваш коллега решит поменять местами столбцы в исходнике или воткнет туда новую колонку, магия ВПР + ПОИСКПОЗ даже не вздрогнет. Всё пересчитается автоматически.

2. Борьба с большими объемами (динамические массивы против Ctrl+Shift+Enter)

У ПРОСМОТРX есть потрясающая фишка — она умеет выдавать "динамические массивы". Вы указываете ей несколько столбцов, и она мгновенно заполняет сразу несколько соседних ячеек вправо. Выглядит как магия.

И снова прилетает офисный нюанс: у вас в таблице не одна строка с Москвой, а несколько тысяч строк с разными городами. А ещё вы не зажиточный боярин, у которого версия Офиса 21+.

• Проблема ПРОСМОТРX: динамический массив круто работает в одну строку. Но если вы попытаетесь кликнуть дважды по углу ячейки, чтобы протянуть формулу вниз на 5000 строк... Excel гордо ничего не сделает. Формулу динамического массива нельзя просто так взять и размножить вниз обычным автозаполнением (пока?). Придется либо тянуть мышку до мозолей вручную, либо городить костыли.

• Ответ связки ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH): и тут на помощь нам может прийти секретная техника — Формулы Массивов (привет всем, кто помнит и знает комбинацию клавиш Ctrl + Shift + Enter). Метод выглядит так: мы выделяем вообще весь пустой диапазон будущей таблицы, где должны быть значения, пишем одну формулу через ИНДЕКС и два ПОИСКПОЗ (один ищет строки, другой — столбцы), но только вместо одной ячейки указываем сразу все, которые нужно найти. Затем бахаем по клавиатуре тремя пальцами (но лучше спокойно сначала зажать Ctrl, потом Shift, а вот потом уже весело вдарить по Enter). И никакого закрепления ячеек! Сработает, кстати, даже если столбцы будут не по порядку.

• Результат: огромная матрица данных заполняется за долю секунды. Без единой протяжки мыши. И, что самое приятное, этот трюк провернет даже древний Excel на компьютере вашей бухгалтерии, где про ПРОСМОТРX даже не слышали.

ВАЖНО! После того, как выделили диапазон, СРАЗУ нажимаем равно (=) и прописываем формулу. А ещё следите за тем, чтобы строки в ИНДЕКСЕ совпадали с первым ПОИСКПОЗ, а столбцы - со вторым.

Итог

Хочу донести главную мысль - не нужно усложнять формулы просто ради того, чтобы они выглядели "круто, современно и не как у всех". Каждый инструмент хорош на своем месте:

• ПРОСМОТРX — идеален, если версия Офиса 21+, нужно по-быстрому связать пару табличек, подтянуть пару колонок.

• ВПР + ПОИСКПОЗ — незаменим, когда колонок много, они перепутаны, так ещё и структура таблицы может меняться (но столбец с исходными значениями всегда слева).

• ИНДЕКС + ПОИСКПОЗ — тяжелая артиллерия для двумерного поиска (и по строкам, и по столбцам одновременно) и работы с гигантскими массивами данных на любых версиях Excel. Плюс исходный столбец находится правее.

Так что не спешите забывать старые формулы — в умелых руках они экономят часы работы и кучу нервных клеток.

Всем спасибо за внимание. Надеюсь, кому-то был полезен данный материал. В комментариях делитесь своими необычными сочетаниями всем известных функций для решения нестандартных задач.

Для тех, кто хочет покрутить таблицы руками и повторить всё самостоятельно, вот ссылка на файл.