В этой заметке речь пойдет об альтернативе функции ВПР в виде связки из двух функций ИНДЕКС и ПОИСКПОЗ.
Поговорим о преимуществах такого решения, разберем работу каждой из функций отдельно и связку функций, для ее использования вместо ВПР.
Итак, поехали.
Преимущества ИНДЕКС и ПОИСКПОЗ
Связка функций ИНДЕКС и ПОИСКПОЗ фактически полностью заменяет функцию ВПР и лишена ее недостатков, которые вытекают из синтаксиса самой функции.
Напомню, что функция ВПР возвращает значение из диапазона, который может находиться только справа от столбца со значениями где происходит поиск. ИНДЕКС и ПОИСКПОЗ могут осуществлять поиск в любом столбце диапазона.
Кроме этого, в функции ВПР указывается неразрывный диапазон, включающий все столбцы между столбцом, в котором происходит поиск искомого значения и столбцом, откуда необходимо значение вернуть.
Однако при работе с данными может возникнуть необходимость удалить или вставить столбец в рамках ранее выбранного диапазона и тогда функцию ВПР придется корректировать и изменять диапазон.
В формуле с функциями ИНДЕКС и ПОИСКПОЗ указываются конкретные столбцы или только необходимые диапазоны значений, поэтому удаление или добавление столбцов на их работу не оказывает никакого влияния.
Ну и для того, чтобы понять работу связки функций ИНДЕКС и ПОИСКПОЗ разберемся с синтаксисом каждой функции отдельно.
Функция ИНДЕКС
Функция ИНДЕКС возвращает значение, которое находится в указанном номере строки выделенного диапазона.
Например, найдем имя «Ольга» в соответствующем столбце.
Первое, что мы указываем - это где будет производиться поиск. Нас будет интересовать только один столбец, поэтому выбираем его.
Далее мы должны указать номер строки в выделенном диапазоне, из которого будет возвращено значение. Здесь все по аналогии с функцией ВПР - указывается номер строки в выделенном диапазоне, а не номер строки таблицы Excel.
В мое примере это третья строка. Аргументы в квадратных скобках являются необязательными, поэтому их можно не указывать.
Будет возвращено имя Ольга.
Если будет выбран диапазон, состоящий из нескольких столбцов, то нужно будет указать и номер строки, и номер столбца в диапазоне, на пересечении которых находится ячейка с нужным значением.
Например, если мы выделим диапазон со всеми данными и нам нужен будет адрес Ольги, то укажем, что он находится в третьей строке и втором столбце выделенного диапазона.
Функция ИНДЕКС возвращает нужное нам значение, но в отличии от ВПР координаты этой ячейки приходится задавать вручную. Чтобы автоматизировать процесс воспользуемся функцией ПОИСКПОЗ.
Функция ПОИСКПОЗ
Функция ПОИСКПОЗ производит поиск указанного значения в диапазоне ячеек и возвращает относительную позицию ячейки с искомым значением. То есть фактически эта функция возвращает координаты этой ячейки, а это как раз то, чего не хватает функции ИНДЕКС для полноценной замены функции ВПР.
Например, нам нужно узнать, какие координаты в диапазоне имеет «Ольга». Создадим формулу с функцией ПОИСКПОЗ и введем интересующее нас имя. Напомню, что текст вводится в кавычках.
Мы указали что искать, а теперь нужно указать где будет производиться поиск - выбираем значения столбца «Имя».
Ну и последний аргумент функции ПОИСКПОЗ очень напоминает аналогичный аргумент интервальный просмотр функции ВПР, но он может принимать три значения - меньше, больше и точное совпадение.
Если речь идет о поиске текстовых данных, то всегда выбираем точное совпадение. Другие параметры актуальны только при работе с числовыми данными. Поэтому указываем ноль и в итоге получаем цифру 3, что соответствует третьей строке в выделенном диапазоне.
Ну а теперь объединим функции ИНДЕКС и ПОИСКПОЗ в полноценную замену функции ВПР.
Связка фукнций ИНДЕКС и ПОИСКПОЗ
Давайте найдем телефон Ивана. Создаем формулу с функцией ИНДЕКС.
Так как нам нужен телефон, то искать мы его будем в соответствующем столбце таблицы, поэтому выделяем его. Далее мы должны указать номер строки в этом диапазоне и тут на помощь приходит функция ПОИСКПОЗ. Так как нас интересует телефон Ивана, то мы бы хотели получить номер строки, в которой находится его имя. Указываем, что мы будем искать - имя "Иван", затем указываем где мы его будем искать - соответствующий диапазон столбца «Имя». Ищем текст, поэтому точное совпадение - 0.
Получаем верный результат.
Формула проделала ровно тоже, с чем легко справится функция ВПР, но давайте решим задачи, которые ВПР не по зубам.
Преимущества ИНДЕКС + ПОИСКПОЗ
Неоспоримым преимуществом связки функций ИНДЕКС и ПОИСКПОЗ перед ВПР является то, что мы можем производить поиск данных влево, что может быть крайне важным при работе с таблицами, имеющими некоторую стандартизированную форму, то есть когда нет возможности поменять столбцы местами.
Также в аргументах функции мы можем указать только интересующие нас диапазоны, а значит конечная формула не будет чувствительна к вставке новых столбцов или удалению уже ненужных столбцов с данными из таблицы.
Давайте рассмотрим это на примере, но сначала просто попрактикуемся и подставим цену из прайс-листа с помощью связки функций ИНДЕКС и ПОИСКПОЗ.
Создаем формулу с функцией ИНДЕКС и выбираем диапазон с ценами. Далее подключаем функцию ПОИСКПОЗ и в качестве искомого значения указываем наименование товара. Затем указываем соответствующий диапазон в прайс-листе.
Что касается диапазонов, то здесь действует тоже правило, что и при работе с функцией ВПР - если мы планируем копировать формулу по диапазону, то нужно ОБЯЗАТЕЛЬНО фиксировать ссылки, превращая их в абсолютные. Иначе при протягивании формулы диапазон в ней будет «сползать» и функция выдаст ошибку. Поочередно выделяем диапазоны в формуле и нажимаем клавишу F4 для преобразования их ссылок в абсолютные.
В результате формула сработает верно и можно будет рассчитать сумму заказа.
Фактически получили тот же результат, что и с помощью функции ВПР, но теперь давайте подставим город, основываясь на фамилии менеджера. В таблице с филиалами столбец с менеджерами находится правее столбца с городам и поэтому функция ВПР тут бесполезна.
Создаем формулу с функцией ИНДЕКС и указываем столбец с городами, так как именно город нас будет интересовать в конечном итоге. Не забываем фиксировать диапазон. Затем с помощью функции ПОИСКПОЗ находим номер соответствующей строки. Сделать это можно по фамилии менеджера, которая присутствует в обеих таблицах.
Получаем нужный результат. При этом если вдруг понадобится добавить столбец в таблицу с филиалами, то данные никуда не денутся, так как они будут привязаны к конкретным столбцам таблицы.
Это прекрасно видно и из следующего примера.
Здесь у нас есть меню и два одинаковых заказа. Один посчитан с помощью ВПР, второй с помощью ИНДЕКС и ПОИСКПОЗ.
Если меню изменится и в него будет внесен еще один столбец, то расчеты с функцией ВПР будут неверными, так как теперь в них будет участвовать не цена, а вес порции в граммах.
Диапазон в формуле расширился, но номер столбца в аргументах по-прежнему ведет на третий столбец в диапазоне, хотя теперь он уже стал четвертым.
Тем не менее, во втором чеке все посчитано верно и вставка столбцов никак не повлияла на конечный результат.
Ну и еще одним преимуществом связки функций является то, что мы можем искать не только в столбцах, но и в строках.
Например, стандартная ситуация для многих кафе - напитки имеют различные объемы от чего зависит их цена. Исходные данные - это напиток и его размер. Необходимо найти цену.
Для этого воспользуемся функцией ИНДЕКС и укажем весь диапазон цен. Ну а для того, чтобы определить координаты конкретной ячейки воспользуемся функцией ПОИСКПОЗ. Причем задействуем ее дважды - для поиска нужной строки по названию напитка и для поиска нужного столбца по его размеру.
В итоге получаем верный результат.
Ошибки и проблемы
Функции ИНДЕКС и ПОИСКПОЗ делают поиск данных в массиве максимально гибким и позволяют решать задачи, на которые функция ВПР не способна. Но и у этого решения есть особенности, которые нужно учитывать, чтобы не столкнуться с проблемами.
Пожалуй самой распространенной ошибкой, как и в случае с ВПР, являются относительные ссылки в диапазонах.
Как я уже упоминал, здесь действует простое правило - если формулу с функциями ИНДЕКС и ПОИСКПОЗ предполагается копировать по диапазону, то обязательно нужно фиксировать ссылки, делая их абсолютными.
Вторая ошибка, с которой вы можете столкнуться - ССЫЛКА!
Связано ее появление с тем, что массивы в функциях ИНДЕКС и ПОИСКПОЗ имеют различные размеры.
Если искомое значение из одного диапазона выходит за границы второго, то появляется эта ошибка. Стоит проверить соответствие диапазонов и подкорректировать их размеры.
Ну и общая для всех функций просмотра проблема - это производительность или скорость работы. Она заметно падает при обработке значительных массивов данных.
Безусловно, здесь очень многое зависит от десятков или даже сотен факторов, однако в любом случае для ускорения вычислений я бы рекомендовал пользоваться умными таблицами.
Да, не всегда это возможно, но в тех случаях, когда данные могут быть оформлены умными таблицами, всегда используйте их.
Умные таблицы - это очень мощный инструмент Excel, позволяющий значительно ускорить рутинные операции и повысить эффективность вашей работы. И более подробно об умных таблицах я расскажу в следующей заметке.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
