Как найти диапазон ячеек в таблице

В этой заметке речь пойдет об альтернативе функции ВПР в виде связки из двух функций ИНДЕКС и ПОИСКПОЗ.

Поговорим о преимуществах такого решения, разберем работу каждой из функций отдельно и связку функций, для ее использования вместо ВПР.

Итак, поехали.

Преимущества ИНДЕКС и ПОИСКПОЗ

Связка функций ИНДЕКС и ПОИСКПОЗ фактически полностью заменяет функцию ВПР и лишена ее недостатков, которые вытекают из синтаксиса самой функции.

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

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

Однако при работе с данными может возникнуть необходимость удалить или вставить столбец в рамках ранее выбранного диапазона и тогда функцию ВПР придется корректировать и изменять диапазон.

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

Ну и для того, чтобы понять работу связки функций ИНДЕКС и ПОИСКПОЗ разберемся с синтаксисом каждой функции отдельно.

Функция ИНДЕКС

Функция ИНДЕКС возвращает значение, которое находится в указанном номере строки выделенного диапазона.

Например, найдем имя «Ольга» в соответствующем столбце.

Первое, что мы указываем – это где будет производиться поиск. Нас будет интересовать только один столбец, поэтому выбираем его.

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

В мое примере это третья строка. Аргументы в квадратных скобках являются необязательными, поэтому их можно не указывать.

Будет возвращено имя Ольга.

Если будет выбран диапазон, состоящий из нескольких столбцов, то нужно будет указать и номер строки, и номер столбца в диапазоне, на пересечении которых находится ячейка с нужным значением.

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

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


Функция ПОИСКПОЗ

Функция ПОИСКПОЗ производит поиск указанного значения в диапазоне ячеек и возвращает относительную позицию ячейки с искомым значением. То есть фактически эта функция возвращает координаты этой ячейки, а это как раз то, чего не хватает функции ИНДЕКС для полноценной замены функции ВПР.

Например, нам нужно узнать, какие координаты в диапазоне имеет «Ольга». Создадим формулу с функцией ПОИСКПОЗ и введем интересующее нас имя. Напомню, что текст вводится в кавычках.

Мы указали что искать, а теперь нужно указать где будет производиться поиск – выбираем значения столбца «Имя».

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

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

Ну а теперь объединим функции ИНДЕКС и ПОИСКПОЗ в полноценную замену функции ВПР.

Связка фукнций ИНДЕКС и ПОИСКПОЗ

Давайте найдем телефон Ивана. Создаем формулу с функцией ИНДЕКС.

Так как нам нужен телефон, то искать мы его будем в соответствующем столбце таблицы, поэтому выделяем его. Далее мы должны указать номер строки в этом диапазоне и тут на помощь приходит функция ПОИСКПОЗ. Так как нас интересует телефон Ивана, то мы бы хотели получить номер строки, в которой находится его имя. Указываем, что мы будем искать – имя “Иван”, затем указываем где мы его будем искать – соответствующий диапазон столбца «Имя». Ищем текст, поэтому точное совпадение – 0.

Получаем верный результат.

Формула проделала ровно тоже, с чем легко справится функция ВПР, но давайте решим задачи, которые ВПР не по зубам.

Преимущества ИНДЕКС + ПОИСКПОЗ

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

Также в аргументах функции мы можем указать только интересующие нас диапазоны, а значит конечная формула не будет чувствительна к вставке новых столбцов или удалению уже ненужных столбцов с данными из таблицы.

Давайте рассмотрим это на примере, но сначала просто попрактикуемся и подставим цену из прайс-листа с помощью связки функций ИНДЕКС и ПОИСКПОЗ.

Создаем формулу с функцией ИНДЕКС и выбираем диапазон с ценами. Далее подключаем функцию ПОИСКПОЗ и в качестве искомого значения указываем наименование товара. Затем указываем соответствующий диапазон в прайс-листе.

Что касается диапазонов, то здесь действует тоже правило, что и при работе с функцией ВПР – если мы планируем копировать формулу по диапазону, то нужно ОБЯЗАТЕЛЬНО фиксировать ссылки, превращая их в абсолютные. Иначе при протягивании формулы диапазон в ней будет «сползать» и функция выдаст ошибку. Поочередно выделяем диапазоны в формуле и нажимаем клавишу F4 для преобразования их ссылок в абсолютные.

В результате формула сработает верно и можно будет рассчитать сумму заказа.

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

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

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

Это прекрасно видно и из следующего примера.

Здесь у нас есть меню и два одинаковых заказа. Один посчитан с помощью ВПР, второй с помощью ИНДЕКС и ПОИСКПОЗ.

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

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

Тем не менее, во втором чеке все посчитано верно и вставка столбцов никак не повлияла на конечный результат.

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

Например, стандартная ситуация для многих кафе – напитки имеют различные объемы от чего зависит их цена. Исходные данные – это напиток и его размер. Необходимо найти цену.

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

В итоге получаем верный результат.

Ошибки и проблемы

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

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

Как я уже упоминал, здесь действует простое правило – если формулу с функциями ИНДЕКС и ПОИСКПОЗ предполагается копировать по диапазону, то обязательно нужно фиксировать ссылки, делая их абсолютными.

Вторая ошибка, с которой вы можете столкнуться – ССЫЛКА!

Связано ее появление с тем, что массивы в функциях ИНДЕКС и ПОИСКПОЗ имеют различные размеры.

Если искомое значение из одного диапазона выходит за границы второго, то появляется эта ошибка. Стоит проверить соответствие диапазонов и подкорректировать их размеры.

Ну и общая для всех функций просмотра проблема – это производительность или скорость работы. Она заметно падает при обработке значительных массивов данных.

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

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

Умные таблицы – это очень мощный инструмент Excel, позволяющий значительно ускорить рутинные операции и повысить эффективность вашей работы. И более подробно об умных таблицах я расскажу в следующей заметке.