Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом столбце, а извлекать значения можно только их тех, которые находятся правее. А как же быть, если наш ключевой столбец находится в середине таблицы, а данные нужно извлечь из левой ее части? Как сделать «левый» ВПР в таблице Excel?
Мы предложим вам несколько способов извлечения значений, находящихся слева от диапазона поиска. А вот какой из них будет для вас понятнее и удобнее – решайте сами.
Итак, у нас есть таблица с данными о продажах. Необходимо по коду заказа получить наименование товара и его стоимость. При этом колонка с кодом находится в середине таблицы, а наименования – левее него. Такой случай как раз и принято называть «левый (или обратный) ВПР».
Получить стоимость по нужному заказу не представляет труда, и с такой задачей ВПР прекрасно справляется. А вот для того, чтобы вывести в С6 название товара, стандартных возможностей этой функции будет недостаточно. Ведь она "работает только направо".
1. Дополнительный столбец.
Создадим дополнительный столбец с названиями товаров справа от ключевого. Для этого в ячейке H10 запишем формулу
=B10
Скопируем ее вниз и получим новый список, расположенный справа от диапазона поиска. Точнее говоря, это точная копия.
Формула в С6 тогда будет такова:
=ВПР(C2;C9:H25;6;0)
6 - это порядковый номер новой колонки с названиями напитков, который мы как бы переместили (продублировали) слева на правую сторону. Отсчет мы начинаем со столбца С, так как с него же начинается и наш диапазон значений в формуле ВПР.
Если это слишком портит вид вашей таблицы и попадает в распечатку, можете этот дополнительный столбец скрыть. Для этого щелкните на букве H правой кнопкой мыши и из выпадающего меню выберите «Скрыть». На расчеты это никак не повлияет.
2. Виртуальная перестановка данных.
В отличие от предыдущего способа, где мы реально меняли расположение данных, здесь мы сделаем это виртуально.
Для этого нам понадобится функция ВЫБОР (CHOOSE в английском варианте). Она позволяет выбрать данные из какого-то массива по их индексу. Проще говоря, по порядковому номеру колонки.
Для поиска напитка используем формулу:
=ВПР(C2;ВЫБОР({1;2};C10:C25;B10:B25);2;0)
Вся хитрость состоит в указании массива данных для поиска. Мы выбираем два диапазона: С10:С25 – с кодами (первый), и В10:В25 – с напитками (второй). И из них при помощи ВЫБОР создаем новую виртуальную таблицу, с которой и будем работать.
Конструкция в виде массива {1;2} показывает, что мы берем сначала первый указанный диапазон C10:C25, потом – второй B10:B25, и формируем из них виртуальную таблицу с двумя колонками. В ней мы и производим поиск по первой и извлекаем значения – из второй. И при этом не важно, где они реально расположены на листе Excel. ВПР работает с виртуальным массивом, который мы сами создали.
3. Левый ВПР при помощи ИНДЕКС + ПОИСКПОЗ.
И снова – об альтернативах функции ВПР, которые часто оказываются ничем не хуже, а даже и лучше.
Напомним, что функция ИНДЕКС (INDEX) позволяет извлечь элемент из перечня по его порядковому номеру. И этот номер нам поможет определить функция ПОИСКПОЗ (или MATCH).
Формула «обратного ВПР» выглядит теперь так:
=ИНДЕКС(B10:B25;ПОИСКПОЗ(C2;C10:C25;0))
Давайте подробно разберем последовательность вычислений. В начале используем ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска). Тип поиска – 0, то есть точное совпадение. В диапазоне C10:C25 мы ищем нужное нам значение, указанное в С2, и получаем его порядковый номер. В нашем случае это 2.
На этой же позиции, то есть в этой же второй строке нашего диапазона, находится и нужный нам напиток. При помощи ИНДЕКС из последовательности В10:В25 извлекаем второй по счету элемент. Задача выполнена.
Более подробно об этом методе поиска значений читайте здесь: ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР.
4. Извлекаем из столбца слева числовые значения.
Этот «обратный ВПР» подходит только для чисел. Предположим, нам нужно подсчитать количество литров в определенном заказе. Но объем также находится левее, чем расположен его код.
Используем ВПР в комбинации с функцией СУММПРОИЗВ (SUMPRODUCT).
Наша итоговая формула:
=СУММПРОИЗВ((ВПР(C2;D10:D25;1;0)=D10:D25)*C10:C25)
Давайте пошагово разберем все действия.
Сразу вспомним важную особенность СУММПРОИЗВ: она работает с предложенным ей диапазоном как с массивом, то есть последовательно перебирает все его элементы от начала до конца.
Вначале ВПР(C2;D10:D25;1;0) позволяет нам найти в перечне заказов интересующий нас и вывести его же (порядковый номер колонки 1, то есть, что ищем, то и возвращаем). Если будет получена ошибка, то она автоматически в нашем случае функцией СУММПРОИЗВ преобразуется в ноль. Сравниваем извлеченное с соответствующим значением из перечня кодов при помощи равенства: ВПР(C2;D10:D25;1;0)=D10:D25
В случае соответствия получаем ИСТИНА (что соответствует 1), в противном случае – ЛОЖЬ (или 0).
В итоге получаем виртуальный набор цифр, состоящий из 0 и 1. Поскольку коды у нас не повторяются, единица будет в этом списке всего одна. Перемножаем этот набор нулей и единиц с соответствующими величинами объема C10:C25. Все произведения дадут нам 0, кроме одного – соответствующего нашему, где была 1. Общая сумма этих произведений и даст нам искомый объем по нужной позиции.
И еще один вариант левого ВПР с числовыми значениями. На этот раз используем функцию СУММЕСЛИ (SUMIF).
Она позволяет находить сумму в зависимости от выполнения определенных условий. Смотрите пример.
Формула расчета:
=СУММЕСЛИ(D10:D25;C2;C10:C25)
Синтаксис здесь такой:
СУММЕСЛИ(диапазон_просмотра; критерий; что_суммируем)
Находим сумму чисел из диапазона C10:C25, соответствующих тем ячейкам из D10:D25, которые равны С2.
Важное ограничение – коды не должны дублироваться, иначе получим сумму сразу нескольких позиций!
Есть еще один, пятый способ создания "левого" ВПР. На этот раз можно использовать комбинацию функций СМЕЩ и ПОИСКПОЗ. Подробно этот метод рассмотрен здесь: Формула СМЕЩ для левого ВПР в Excel.
Специальные инструменты для поиска и подстановки в Excel.
Если написание формул представляет для вас некоторую сложность, вы можете попробовать надстройку Мастер ВПР, которая является составной частью Ultimate Suite for Excel.
Как работает Мастер ВПР, в том числе как с его помощью можно сделать "левый" ВПР, вы можете прочитать здесь в нашем блоге.
А сейчас мы остановимся более подробно еще на одном интересном инструменте.
Объединить две таблицы — альтернатива ВПР в Excel без формул.
Если ваши файлы Excel чрезвычайно велики и сложны, а выполнить расчеты нужно быстро, и вы ищете кого-то, кто может протянуть вам руку помощи, то попробуйте Мастер объединения таблиц .
Этот инструмент является простой и наглядной альтернативой функции ВПР в Excel. Работает он следующим образом:
- Выберите свою основную таблицу. Предположим, это таблица с данными о продажах.
- Выберите таблицу поиска. Это может быть список менеджеров, закрепленных за отдельными покупателями.
- Выберите один или несколько общих столбцов в качестве уникальных идентификаторов. В нашем случае это будет наименование заказчика.
- Укажите, какие столбцы нужно обновить. В данном случае – ничего. Просто пропускаем этот шаг.
- При желании выберите столбцы, которые нужно добавить. Добавим в основную таблицу колонку с фамилиями менеджеров, которые работают с конкретным заказчиком. При этом совершенно не важно, где находятся эти столбцы для добавления — слева или справа от столбца поиска.
- На следующем шаге вы можете указать дополнительные опции объединения – выделение цветом добавленного, добавление несовпадающих значений в конец основной таблицы, вставка столбца статуса и др. Но в нашем случае в этом нет необходимости. Ведь мы просто хотим найти и добавить в основную таблицу фамилии менеджеров. Поэтому просто нажимаем Finish. Теперь дайте Мастеру объединения таблиц несколько секунд для обработки… и наслаждайтесь результатами :)
Согласитесь, это именно то, что делает функция ВПР — выбирает из таблицы поиска значения, соответствующие данным из основной таблицы. Но в данном случае мы прекрасно обошлись без формул.
Более подробную информацию об инструменте Merge Tables вы можете посмотреть здесь.
Спасибо!