Excel works!
Excel работает за вас
- Залогиниться
- Зарегистрироваться
Забыли пароль?
Excel works!
- Главная
- Excel
- Управление проектами
- Книги
- Прочее
- Аналитика
Thanks for Visiting
- Главная
- Excel
- Управление проектами
- Книги
- Прочее
- Аналитика
Индекс и Поискпоз. Левый ВПР. Так удобнее!
Наверное самая популярная функция из продвинутых в Excel — это ВПР . Многие знают и часто пользуются. Но при этом у нее есть два значительных недостатка, например, как сделать «Левый ВПР «. Я использую сам ВПР, только если нужно сделать что-то быстро. В файлах для «регулярного» использования я делаю конструкцию ИНДЕКС и ПОИСКПОЗ. Чем она лучше?
- При добавлении столбца в таблицу данных не нужно менять номер столбца в самой формуле (как в ВПР). Столбец подвинется автоматом
- Можно сделать ВПР наоборот, т.е. сделать выбор из таблицы справа-налево. В ВПР первый столбец всегда должен быть для поиска.
Как все это сделать, читаем ниже 🙂
Вводная информация
Использовать буду пример из этой статьи . Для того, чтобы лучше понять применение этой конструкции в сравнение с ВПР:
Как просто заполнить, если видишь формулу первый раз?
Сперва, определитесь, где таблица изначальная, а где для получения данных! Поэтапно, что заполнять.
- Вставляем формулу в нужную область таблицы
- На место $G:$G поставьте те ячейки, в которых должно быть найдено значение, и соответственно, должно появиться в результате. Мы ищем фамилию, значит ищите столбец с фамилиями в начальной таблице.
- Замените $J:$J на в зависимости, от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС, около которых должны появиться значения.
- Вместо $H:$H заполняем столбец, в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.
Если вам интересно, для чего в формуле знаки $ читайте здесь
ИНДЕКС и ПОИСКПОЗ. Что это за функции?
ИНДЕКС и ПОИСКПОЗ — очень сильные функции, которые в комбинации с другими дают отличный результат.
=ИНДЕКС(массив; номер_строки; номер_столбца)
Возвращает значение на пересечении указанной строки и столбца определенного диапазона. Т.е. изначально она работает с двумерными массивами.
Выбрав массив данных J1:K4 и задав номер строки и столбца равным двум, мы получили соответствующее значение.
Посмотрев на изначальную формулу
Мы увидим, что вместо второго аргумента (номер строки) у нас формула ПОИСКПОЗ. Что тут она делает?
ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.
И уже в одномерном массиве $G:$G находим ячейку с номером строки = 2. Так это будет работать для каждой ячейки в столбце J.
Вот так вроде бы не очень сложная конструкция, но, как я писал выше, очень эффективная. Т.к. вам не придется постоянно менять номер искомого значения, как в ВПР и можно делать поиск как направо, так и налево 🙂
Левый ВПР
Необходимое предисловие
Если вы раньше не работали с функцией ВПР (VLOOKUP) , то много потеряли очень рекомендую сначала почитать вот эту статью и посмотреть в ней видеоурок.
Проблема
Как многим известно, функция ВПР (VLOOKUP) может выдавать в качестве результата значения, которые находятся строго правее того столбца, где производится поиск. Ах, как было бы красиво, если бы третий аргумент этой функции (номер столбца, откуда выдаются значения) можно было бы задавать отрицательным, но — нет.
На практике же сплошь и рядом возникают ситуации, когда искать данные надо в столбце, который находится правее, а не левее столбца результатов, например:
Стоимость по коду заказа найти легко — обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке "а как сделать левый ВПР"?
Давайте разберем несколько способов.
Способ 1. Лобовая атака
Если следовать принципу Оккама и не усложнять без надобности, то можно просто скопировать нужный столбец правее (или сделать его ссылками) и использовать обычный ВПР:
Дешево и сердито, но требует ручного допиливания таблицы. Кроме того, часто бывают случаи, когда таблицу нельзя менять: она защищена паролем, это корпоративный шаблон, таблица в общем доступе и т.д. Тогда нужен другой подход.
Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР
Если переставить местами столбцы на листе нельзя, то это можно сделать виртуально, т.е. "на лету" прямо в самой формуле. Для этого нам потребуется функция ВЫБОР (CHOOSE) . Основное ее предназначение – выбирать нужный элемент из списка по заданному номеру. Ее, например, можно использовать для замены номера дня недели на его текстовый аналог:
Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.
Во-первых, вместо текстовых названий выбираемых элементов списка ("пн", "вт" и т.д.) можно использовать адреса диапазонов. И тогда функция вернет ссылку на выбранный диапазон. Так, например, формула:
=ВЫБОР(2; A1:A10 ; D1:D10 ; B1:B10 )
… выдаст на выходе ссылку на второй указанный диапазон (D1:D10).
Во-вторых, вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:
=ВЫБОР( ; A1:A10 ; D1:D10 ; B1:B10 )
Тогда на выходе мы получим два первых диапазона (A1:A10 и D1:D10), склеенных в единое целое.
И вот теперь все это можно вложить внутрь нашей ВПР, чтобы реализовать «левый поиск»:
От "классического ВПР" отличается, как видно, только тем, что диапазон задается склейкой двух столбцов Код заказа и Товар с помощью функции ВЫБОР. В остальном все привычно.
Минусы такого способа — это скорость (примерно в 5-7 раз медленнее обычного ВПР) и некоторая непривычность для коллег (а может это даже плюс!)
Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ
Если не держаться имеено за функцию ВПР, то можно использовать ее более мощный аналог — связку двух очень полезных функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) :
Функция ПОИСКПОЗ ищет заданное значение (С2, т.е. код нужного нам заказа) в одномерном диапазоне (столбце кодов в таблице C10:C25) и выдает в качестве результата порядковый номер ячейки, где нашла искомое — в нашем случае это будет число 4, т.к. код нужного нам заказа четвертый в таблице.
А затем в дело вступает функция ИНДЕКС, которая умеет извлекать данные из вертикального массива-столбца (названия товаров в B10:B25) по порядковому номеру (который предварительно нашла ПОИСКПОЗ). Таким образом, ИНДЕКС выдаст нам содержимое четвертой ячейки из столбца Товар, что и требовалось.
По сравнению с предыдущим способом, такой вариант гораздо быстрее пересчитывается (почти также быстро как обычный ВПР), что важно для больших таблиц.
Похожий пример (с видео) я разбирал ранее вот в этой статье. А уж про функцию ИНДЕКС можно говорить совсем долго 🙂
Способ 4. Функция СУММЕСЛИ(МН)
Если нужно извлечь из таблицы именно число (допустим, объем в литрах), то иногда проще использовать для реализации "левого ВПР" функцию выборочного суммирования СУММЕСЛИ (SUMIF) или ее старшую сестру — функцию СУММЕСЛИМН (SUMIFS) :
Минусы такого подхода очевидны — он работает только для чисел и, при условии, что в столбце нет повторяющихся значений. Если есть дубликаты (несколько заказов с одинаковым кодом), то эта функция сложит все объемы, а не выдаст первый, как это сделала бы ВПР. Ну, и скорость у такого способа тоже не очень — примерно в 3-4 раза медленнее, чем обычный ВПР.
Большая статья про функции выборочного подсчета по одному или нескольким условиям есть тут.
Способ 5. Готовая макрофункция из PLEX
Если не пугает использование макросов, то можно использовать готовую пользовательскую функцию VLOOKUPS на Visual Basic, которая входит в состав последней версии моей надстройки PLEX для Microsoft Excel. По сравнению с обычной ВПР она умеет:
- искать по нескольким столбцам сразу (до 3)
- выдавать результаты из любого столбца (левее или правее — не важно)
- выдавать не только первое встретившееся значение, а нужное по порядку
- можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д
У такого способа два минуса: нужно сохранять файл с поддержкой макросов (XLSM) и скорость у любой макрофункции не очень высокая — на больших таблицах может ощутимо подтормаживать.