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

Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:

1. Использование функции СУММПРОИЗВ

Ну а начнем мы с вами с самого простого.

Использование дополнительной колонки

В большинстве случаев сложные проблемы становятся проще и более управляемыми, если их разбить на маленькие кусочки. Тоже самое касается при построении формул в Excel.

Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

ВПР(G1&G2;A2:D13;4;0)

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

ВПР(G1&G2;ВЫБОР({1;2};B2:B13&C2:C13;D2:D13);2;0)

Основной момент данной формулы заключается в части ВЫБОР({1;2};B2:B13&C2:C13;D2:D13), который делает две вещи:

1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
2. Объединяет два массива в таблицу, состоящую из двух столбцов.

Результатом работы данной функции будет таблица, которая выглядит следующим образом:

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

Использование функций ИНДЕКС и ПОИСКПОЗ

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

Формула будет выглядеть следующим образом.

ИНДЕКС(D2:D13;ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0))

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

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

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

Использование СУММПРОИЗВ

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

СУММПРОИЗВ((B2:B13=G1)*(C2:C13=G2);D2:D13)

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.

Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.

ИТОГ

Итак, какой же способ использовать? Хотя все они работают стабильно, я предпочитаю первый способ. В своей ежедневной работе, я предпочитаю работать с файлами, которые просты для понимания и поддаются изменениям. Оба эти требования отвечают условиям первого подхода.

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

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

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

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

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне нужно яблоко и другие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.

Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4» . Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$» , и диапазон смещаться не будет.

Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2» .

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК» .

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

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

ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)

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

Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.

Если у Вас в первой таблице есть названия продуктов, которых нет в прайсе, у меня это овощи, то напротив данных пунктов формула ВПР выдаст ошибку #Н/Д .

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14 .

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

В открывшемся окне «Тип данных» будет «Список» , ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК» .

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1 ).

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

Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.

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

Жмем по любой ячейке в столбце D и вставляем один новый.

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

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .

Выделяем мышкой необходимые столбцы и строки, без заголовков.

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12» . Выделите строчку и нажмите «F4» , чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2 ) и пишем «ЛОЖЬ» .

В конце нажмите кнопку «ОК» .

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

Надеюсь, у меня получилась пошаговая инструкция по использованию и применению функции ВПР в Excel, и Вам теперь все понятно.

Оценить статью:

(13 оценок, среднее: 5,00 из 5)

Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности

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

Работа функции ВПР по нескольким критериям

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

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

1. – Дата сдачи выручки в кассу.
2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:




Найдена сумма выручки конкретного торгового представителя на конкретную дату.



Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

1. элемент – Дата.
2. элемент – Фамилия.
3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений (""). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

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

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

Как работает функция ВПР в Excel: пример

Функция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.




В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.




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



Функция ВПР в Excel и две таблицы

Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:




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

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


1. Исходное значение: G3.
2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
3. Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
4. Интервальный просмотр: ЛОЖЬ.
5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «{}», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.




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

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

Другими словами если в нашей таблице повторяются значения «груши», «яблока» мы не сможем просуммировать всех груш и яблок. Для этого нужно использовать функцию ПРОСМОТР(). Она очень похожа на ВПР но умеет хорошо работать с массивами в исходных значениях.

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

Пользовательская надстройка rLOOKUP работает как ВПР, но позволяет производить обратный поиск. Функция просто использует методы указательным и матч в VBA. Она предоставляется в Excel надстройка для простоты использования.

Скачать надстройку

Сохраните файл на компьютере и установить его в Excel, выбрав Файл -> Параметры -> Надстройки . Нажмите на кнопку Перейти... , в открывшемся окне выберите Обзор . Выберите сохраненный файл и нажмите кнопку OK, чтобы завершить установку. После установки появится сообщение "Reverse_Lookup" в списке доступных надстроек и функция rLOOKUP становится доступной во всех книгах.



Описание функции

Аргументы для функции rLOOKUP такие же, как и для функции ВПР, см. скриншоты ниже. Чтобы выполнить обратный поиск, просто введите отрицательное число в аргумент "Col_Index_Num" (смотри примеры ниже). Формула будет Lookup в направлении влево.



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

Пример функции

Следующие данные Excel показывает адреса офисов, имена сотрудников и должность.



Для ответа на этот запрос "Кто работает офисе B43?"мы можем использовать функцию ВПР:

ВПР ("B43"; A1:C6;2; ЛОЖЬ) возвращает "Dilbert".

Тем не менее, если мы хотим знать, "Где находится офис Дилберта?", то ВПР не будет работать. Вот тут пригодится функция rLOOKUP:

rLOOKUP ("Dilbert"; A1:B6, -2).

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

Вообще, функция работает в обоих направлениях:

rLOOKUP ("B43";A1:C6;2) возвращает "Dilbert" и

rLOOKUP ("Dilbert"; A1:B6; -2) возвращает "B43".

Другие примеры приведены ниже:



Не забудьте сначала установить надстройку, в противном случае будет возвращаться ошибка #NAME? .

Как работает функция

Функция использует функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) для запуска обратного поиска.

Исходный код для функции смотрите ниже.

Function rLOOKUP(Lookup_value, Table_array As Range, Col_index_num As Integer, Optional Range_lookup As Boolean)
"
"Custom function that allows forward and reverse vertical lookups
"Works like VLookup for positive Col_index_num, and does reverse lookups for negative Col_index_num
"Unlike VLOOKUP, it looks for the exact value by default
"
"Nitin Mehta, www.Engineers-Excel.com
"
"
Dim Source_Col As Range, Dest_Col_num As Integer

"The Source Column is the Column where the Lookup_value is located. It is the leftmost column if
"Col_index_number is positive. If Col_index_number is negative, it is the rightmost column
Set Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

"Dest_col_num is the column number within the Table_array range from where we have to pick up the entry against the Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)

"Use MATCH and INDEX functions for the Lookup.
"Use of CVErr(xlErrRef) suggested by Brian Canes
rLOOKUP = IIf(Dest_Col_num < 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))

End Function

Оставьте свой комментарий!