Использование функции ВПР в Excel: неточное соответствие

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

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

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

• Пример из жизни. Ставим задачу
• Усложняем задачу
• Применяем функцию ВПР к решению задачи
• Заключение

Пример из жизни. Ставим задачу

Проиллюстрируем эту статью примером из реальной жизни – расчёт комиссионных на основе большого ряда показателей продаж. Мы начнём с очень простого варианта, и затем постепенно будем усложнять его, пока единственным рациональным решением задачи не станет использование функции ВПР. Первоначальный сценарий нашей вымышленной задачи звучит так: если продавец за год делает объём продаж более $30000, то его комиссионные составляют 30%. В противном случае, комиссия составляет, лишь 20%. Оформим это в виде таблицы:

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

Самая интересная часть таблицы заключена в ячейке B2 – это формула для определения ставки комиссионного вознаграждения. Эта формула содержит функцию Excel под названием IF (ЕСЛИ). Для тех читателей, кто не знаком с этой функцией, поясню как она работает:

IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)

Условие – это аргумент функции, который принимает значение либо TRUE (ИСТИНА), либо FALSE (ЛОЖЬ). В примере, приведённом выше, выражение B1<B5 может быть прочитано как:

Правда ли, что B1 меньше B5?

Или можно сказать по-другому:

Правда ли, что общая сумма продаж за год меньше порогового значения?

Если на этот вопрос мы отвечаем ДА (ИСТИНА), то функция возвращает value if true (значение если ИСТИНА). В нашем случае это будет значение ячейки B6, т.е. ставка комиссионных при общем объёме продаж ниже порогового значения. Если мы отвечаем на вопрос НЕТ (ЛОЖЬ), тогда возвращается value if false (значение если ЛОЖЬ). В нашем случае это значение ячейки B7, т.е. ставка комиссионных при общем объёме продаж выше порогового значения.

Как Вы можете видеть, если мы берём общую сумму продаж $20000, то получаем в ячейке B2 ставку комиссионных 20%. Если же мы введём значение $40000, то ставка комиссионных изменится на 30%:

Таким образом работает наша таблица.

Усложняем задачу

Давайте немного усложним задачу. Установим ещё одно пороговое значение: если продавец зарабатывает более $40000, тогда ставка комиссионных возрастает до 40%:

Вроде бы всё просто и понятно, но наша формула в ячейке B2 становится заметно сложнее. Если Вы внимательно посмотрите на формулу, то увидите, что третий аргумент функции IF (ЕСЛИ), превратился в ещё одну полноценную функцию IF (ЕСЛИ). Такая конструкция называется вложением функций друг в друга. Excel с радостью допускает такие конструкции, и они даже работают, но их гораздо сложнее читать и понимать.

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

Как бы там ни было, формула усложняется! А что, если мы введем еще один вариант ставки комиссионных, равный 50%, для тех продавцов, кто сделал объём продаж более $50000. А если кто-то продал на сумму более $60000 – тому заплатить 60% комиссионных?

Теперь формула в ячейке B2, даже если она записана без ошибок, стала совершенно не читаемой. Думаю, что найдется мало желающих использовать формулы с 4-мя уровнями вложенности в своих проектах. Должен же существовать более простой способ?!

И такой способ есть! Нам поможет функция ВПР.

Применяем функцию ВПР к решению задачи

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

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

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

Вставляем функцию ВПР

Выбираем ячейку B2 (место, куда мы хотим вставить нашу формулу), и находим VLOOKUP (ВПР) в библиотеке функций Excel: Formulas (Формулы) > Function Library (Библиотека Функций) > Lookup & Reference (Ссылки и массивы).

Появляется диалоговое окно Function Arguments (Аргументы функции). По очереди заполняем значения аргументов, начиная с Lookup_value (Искомое_значение). В данном примере это общая сумма продаж из ячейки B1. Ставим курсор в поле Lookup_value (Искомое_значение) и выбираем ячейку B1.

Далее нужно указать функции ВПР, где искать данные. В нашем примере это таблица Rate Table. Ставим курсор в поле Table_array (Таблица) и выделяем всю таблицу Rate Table, кроме заголовков.

Далее мы должны уточнить, данные из какого столбца необходимо извлечь с помощью нашей формулы. Нас интересует ставка комиссионных, которая находится во втором столбце таблицы. Следовательно, для аргумента Col_index_num (Номер_столбца) вводим значение 2.

И, наконец, вводим последний аргумент — Range_lookup (Интервальный_просмотр).

Важно: именно в использовании этого аргумента заключается различие между двумя способами применения функции ВПР. При работе с базами данных аргумент Range_lookup (Интервальный_просмотр) должен всегда иметь значение FALSE (ЛОЖЬ), чтобы искать точное соответствие. В нашем же варианте использования функции ВПР, мы должны оставить это поле пустым, либо ввести значение TRUE (ИСТИНА). Крайне важно правильно выбрать этот параметр.

Чтобы было понятнее, мы введём TRUE (ИСТИНА) в поле Range_lookup (Интервальный_просмотр). Хотя, если оставить поле пустым, это не будет ошибкой, так как TRUE — это его значение по умолчанию:

Мы заполнили все параметры. Теперь нажимаем ОК, и Excel создаёт для нас формулу с функцией ВПР.

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

Заключение

Когда функция ВПР работает с базами данных, аргумент Range_lookup (Интервальный_просмотр) должен принимать FALSE (ЛОЖЬ). А значение, введённое в качестве Lookup_value (Искомое_значение) должно существовать в базе данных. Другими словами, идёт поиск точного совпадения.

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

Например: Мы хотим определить, какую ставку использовать в расчёте комиссионных для продавца с объёмом продаж $34988. Функция ВПР возвращает нам значение 30%, что является абсолютно верным. Но почему же формула выбрала строку, содержащую именно 30%, а не 20% или 40%? Что понимается под приближенным поиском? Давайте внесём ясность.

Когда аргумент Range_lookup (Интервальный_просмотр) имеет значение TRUE (ИСТИНА) или опущен, функция ВПР просматривает первый столбец и выбирает наибольшее значение, которое не превышает искомое.

Важный момент: Чтобы эта схема работала, первый столбец таблицы должен быть отсортирован в порядке возрастания.