Содержание:
Недавно мы посвятили статью одной из самых полезных функций Excel под названием ВПР и показал, как его можно использовать для извлечения необходимой информации из базы данных в ячейку рабочего листа. Мы также упомянули, что существует два варианта использования функции ВПР и только один из них занимается запросами к базе данных. В этой статье вы узнаете еще один менее известный способ использования функции. ВПР в Excel.
Если вы этого еще не сделали, то обязательно прочтите последнюю статью о функции ВПР, ведь вся информация ниже предполагает, что вы уже знакомы с принципами, описанными в первой статье.
При работе с базами данных функции ВПР передается уникальный идентификатор, который используется для идентификации информации, которую мы хотим найти (например, код продукта или идентификационный номер клиента). Этот уникальный код должен присутствовать в базе данных, в противном случае ВПР сообщит об ошибке. В этой статье мы рассмотрим такой способ использования функции ВПРкогда идентификатор вообще не существует в базе данных. Как будто функция ВПР переключился в приблизительный режим и выбирает, какие данные нам предоставить, когда мы хотим что-то найти. В определенных обстоятельствах это именно то, что нужно.
Пример из жизни. Мы ставим задачу
Давайте проиллюстрируем эту статью реальным примером – расчетом комиссий на основе широкого спектра показателей продаж. Начнем с очень простого варианта, а затем постепенно будем его усложнять, пока единственным рациональным решением задачи не станет использование функции ВПР. Исходный сценарий нашей воображаемой задачи следующий: если продавец зарабатывает на продажах более 30000 30 долларов в год, то его комиссия составляет 20%. В противном случае комиссия составит всего XNUMX%. Представим это в виде таблицы:
Продавец вводит данные о продажах в ячейку B1, а формула в ячейке B2 определяет правильную ставку комиссии, на которую может рассчитывать продавец. В свою очередь, полученная ставка используется в ячейке B3 для расчета общей комиссии, которую должен получить продавец (простое перемножение ячеек B1 и B2).
Самая интересная часть таблицы содержится в ячейке B2 – это формула определения размера комиссии. Эта формула содержит функцию Excel под названием IF (ЕСЛИ). Для тех читателей, кто не знаком с этой функцией, объясню, как она работает:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
состояние — это аргумент функции, который принимает значение либо ИСТИННЫЙ КОД (ИСТИНА) или НЕПРАВДА (ЛОЖЬ). В приведенном выше примере выражение B1
Верно ли, что B1 меньше B5?
Или можно сказать по-другому:
Правда ли, что общий объем продаж за год меньше порогового значения?
Если мы ответим на этот вопрос ДА (ИСТИНА), то функция возвращает значение, если правда (значение, если ИСТИНА). В нашем случае это будет значение ячейки B6, т. е. ставка комиссии, когда общий объем продаж ниже порогового значения. Если мы ответим на вопрос НЕТ (ЛОЖЬ) затем возвращает значение, если ложь (значение, если ЛОЖЬ). В нашем случае это значение ячейки B7, т. е. ставка комиссии, когда общий объем продаж превышает пороговое значение.
Как видите, если мы возьмем общий объем продаж в размере 20000 2 долларов США, мы получим ставку комиссии в размере 20% в ячейке B40000. Если мы введем значение $30, то ставка комиссии изменится на XNUMX%:
Вот как работает наша таблица.
Мы усложняем задачу
Давайте немного усложним ситуацию. Установим еще один порог: если продавец зарабатывает более $40000 40, то ставка комиссии увеличивается до XNUMX%:
Вроде бы все просто и понятно, но наша формула в ячейке В2 заметно усложняется. Если вы внимательно посмотрите на формулу, то увидите, что третий аргумент функции IF (ЕСЛИ) превратился в еще одну полноценную функцию IF (ЕСЛИ). Такая конструкция называется вложением функций друг в друга. Excel с радостью допускает такие конструкции, и они даже работают, но их гораздо труднее читать и понимать.
Мы не будем углубляться в технические подробности — почему и как это работает, и не будем вдаваться в нюансы написания вложенных функций. Ведь эта статья посвящена функции ВПР, а не полное руководство по Excel.
В любом случае формула усложняется! Что, если мы введем еще один вариант комиссии в размере 50% для тех продавцов, которые зарабатывают более 50000 60000 долларов США? А если кто-то продал более 60 XNUMX долларов, заплатит ли он комиссию XNUMX%?
Теперь формула в ячейке В2, даже если она была написана без ошибок, стала совершенно нечитаемой. Думаю, мало кто захочет использовать в своих проектах формулы с 4 уровнями вложенности. Должен быть более простой способ?!
И есть такой способ! Нам поможет функция ВПР.
Применяем функцию ВПР для решения проблемы
Давайте немного изменим дизайн нашей таблицы. Мы сохраним все те же поля и данные, но расположим их по-новому, более компактно:
Найдите минутку и убедитесь, что новая таблица Таблица ставок включает те же данные, что и предыдущая таблица пороговых значений.
Основная идея заключается в использовании функции ВПР определить желаемую тарифную ставку по таблице Таблица ставок в зависимости от объема продаж. Обратите внимание, что продавец может продать товар на сумму, не равную одному из пяти порогов таблицы. Например, он мог бы продать за $34988, но такой суммы нет. Давайте посмотрим, как функция ВПР сможет справиться с такой ситуацией.
Вставка функции ВПР
Выделите ячейку B2 (куда мы хотим вставить нашу формулу) и найдите ВПР (ВПР) в библиотеке функций Excel: Формулы (формулы) > Библиотека функций (Библиотека функций) > Поиск и справка (Ссылки и массивы).
Появится диалоговое окно Аргументы функций (Аргументы функции). Заполняем значения аргументов по одному, начиная с Искомое_значение (Искомое_значение). В данном примере это общая сумма продаж из ячейки B1. Поместите курсор в поле Искомое_значение (Искомое_значение) и выберите ячейку B1.
Далее необходимо указать функции ВПРгде искать данные. В нашем примере это таблица Таблица ставок. Поместите курсор в поле Таблица_массив (Таблица) и выделите всю таблицу Таблица ставоккроме заголовков.
Далее нам нужно указать, из какого столбца следует извлечь данные с помощью нашей формулы. Нас интересует ставка комиссии, которая находится во втором столбце таблицы. Поэтому для аргумента Col_index_num (Колонка_номер) введите значение 2.
И, наконец, введем последний аргумент – Диапазон_поиска (Интервальный_просмотр).
важно: именно использование этого аргумента определяет разницу между двумя способами применения функции ВПР. При работе с базами данных аргумент Диапазон_поиска (range_lookup) всегда должен иметь значение НЕПРАВДА (FALSE) для поиска точного совпадения. В нашем использовании функции ВПР, мы должны оставить это поле пустым или ввести значение ИСТИННЫЙ КОД (ИСТИННЫЙ). Крайне важно правильно выбрать этот вариант.
Чтобы было понятнее, представим ИСТИННЫЙ КОД (ИСТИНА) в поле Диапазон_поиска (Интервальный_просмотр). Хотя, если оставить поле пустым, это не будет ошибкой, так как ИСТИННЫЙ КОД это его значение по умолчанию:
Мы заполнили все параметры. Теперь мы нажимаем OK, а Excel создаст для нас формулу с функцией ВПР.
Если мы поэкспериментируем с несколькими разными значениями общей суммы продаж, то убедимся в корректности работы формулы.
Заключение
Когда функция ВПР работает с базами данных, аргумент Диапазон_поиска (range_lookup) должен принять НЕПРАВДА (ЛОЖЬ). И значение, введенное как Искомое_значение (Lookup_value) должно существовать в базе данных. Другими словами, он ищет точное совпадение.
В примере, который мы рассмотрели в этой статье, нет необходимости получать точное совпадение. Это тот случай, когда функция ВПР необходимо переключиться в приблизительный режим, чтобы вернуть желаемый результат.
Например: Мы хотим определить, какую ставку использовать при расчете комиссии для продавца с объемом продаж 34988 XNUMX долларов США. Функция ВПР возвращает нам значение 30%, что абсолютно верно. Но почему формула выбрала строку, содержащую именно 30%, а не 20% или 40%? Что подразумевается под приближенным поиском? Давайте внесем ясность.
Когда аргумент Диапазон_поиска (interval_lookup) имеет значение ИСТИННЫЙ КОД (ИСТИНА) или опущена, функция ВПР выполняет итерацию по первому столбцу и выбирает наибольшее значение, не превышающее искомое значение.
Важная точка: Чтобы эта схема работала, первый столбец таблицы необходимо отсортировать по возрастанию.