В недавней статье мы представили функцию Excel под названием VLOOKUP и объяснили, как ее можно использовать для извлечения информации из базы данных в ячейку на локальном рабочем листе. В этой статье мы упоминали, что VLOOKUP использовались двумя способами, и только один из них касался запросов к базам данных. В этой статье, второй и последней в серии VLOOKUP, мы рассмотрим другое, менее известное использование функции VLOOKUP.
Если вы еще этого не сделали, прочитайте первую статью VLOOKUP – в этой статье предполагается, что многие концепции, описанные в этой статье, уже известны читателю.
При работе с базами данных VLOOKUP передается «уникальный идентификатор», который служит для идентификации записи данных, которую мы хотим найти в базе данных (например, код продукта или идентификатор клиента). Этот уникальный идентификатор должен существует в базе данных, в противном случае VLOOKUP возвращает нам ошибку. В этой статье мы рассмотрим способ использования VLOOKUP, когда идентификатор вообще не должен существовать в базе данных. Похоже, что VLOOKUP может использовать подход «достаточно близко, достаточно хорошо» для возврата данных, которые мы ищем. В определенных обстоятельствах это именно то, что нам нужно.
Мы проиллюстрируем эту статью реальным примером – расчетом комиссий, которые генерируются на основе набора показателей продаж. Мы начнем с очень простого сценария, а затем постепенно усложним его, пока единственное рациональное решение проблемы – это использование VLOOKUP. Первоначальный сценарий в нашей фиктивной компании работает следующим образом: если продавец создает за год продажи более чем на 30 000 долларов, комиссия, которую они получают за эти продажи, составляет 30%. В противном случае их комиссия составляет всего 20%. Пока это довольно простой лист:
Чтобы использовать этот рабочий лист, продавец вводит свои данные о продажах в ячейку B1, а формула в ячейке B2 вычисляет правильную ставку комиссионных, которую они имеют право получать, которая используется в ячейке B3 для расчета общей комиссии, причитающейся продавцу (которая это простое умножение B1 и B2).
Ячейка B2 содержит единственную интересную часть этой таблицы – формулу для определения, какую комиссию использовать: одну ниже порога в 30 000 долларов или одну выше порога , Эта формула использует функцию Excel под названием IF . Для тех читателей, которые не знакомы с IF, он работает так:
IF ( условие, значение, если true, значение, если false )
Где условие – это выражение, которое оценивается как true или false . В приведенном выше примере условие является выражением B1
Как видите, использование суммы продаж в 20 000 долларов дает нам комиссию 20% в ячейке B2. Если мы введем значение в 40 000 долларов, мы получим другую комиссию:
Итак, наша таблица работает.
Давайте сделаем это более сложным. Давайте введем второй порог: если продавец зарабатывает более 40 000 долларов, то его комиссия увеличивается до 40%:
Достаточно легко понять в реальном мире, но в ячейке B2 наша формула становится все более сложной. Если вы внимательно посмотрите на формулу, вы увидите, что третий параметр исходной функции IF (значение , если false ) теперь представляет собой целую функцию IF самостоятельно. Это называется вложенной функцией (функцией внутри функции). В Excel это совершенно правильно (даже работает!), Но его сложнее читать и понимать.
Мы не будем вдаваться в подробности о том, как и почему это работает, а также не будем рассматривать нюансы вложенных функций. Это учебник по VLOOKUP, а не по Excel в целом.
Во всяком случае, это становится хуже! Как насчет того, когда мы решим, что если они зарабатывают более 50 000 долларов, то они имеют право на 50% комиссионных, а если они зарабатывают более 60 000 долларов, то они имеют право на 60% комиссионных?
Теперь формула в ячейке B2, хотя и правильная, стала практически нечитаемой. Никто не должен писать формулы, где функции вложены в четыре уровня!Наверняка должен быть более простой способ?
Там, безусловно, есть. ВЛАКУП на помощь!
Давайте немного переделаем рабочий лист. Мы сохраним все те же цифры, но организуем их по-новому, более в табличной форме :
Найдите минутку и убедитесь, что новая таблица ставок работает точно так же, как и серия порогов выше.
Концептуально мы собираемся использовать VLOOKUP, чтобы найти общую сумму продаж продавца (из B1) в таблице тарифов и вернуть нам соответствующую комиссию. Обратите внимание, что продавец, возможно, действительно создал продажи, которые не являются одним из пяти значений в таблице тарифов (0, 30 000, 40 000, 50 000 или 60 000 долларов). Они, возможно, создали продажи 34 988 $. Важно отметить, что $ 34 988 не появляются в таблице тарифов. Посмотрим, сможет ли VLOOKUP решить нашу проблему в любом случае …
Мы выбираем ячейку B2 (место, куда мы хотим поместить нашу формулу), а затем вставляем функцию VLOOKUP на вкладке Формулы :
Появится окно Аргументы функций для VLOOKUP. Мы заполняем аргументы (параметры) один за другим, начиная с Lookup_value , в данном случае это общая сумма продаж в ячейке B1. Поместим курсор в поле Lookup_value и затем нажмите один раз на ячейку B1:
Далее нам нужно указать VLOOKUP, в какой таблице искать эти данные. Конечно, в этом примере это таблица тарифов. Поместим курсор в поле Table_array , а затем выделим всю таблицу тарифов – , исключая заголовки :
Далее мы должны указать, в каком столбце таблицы содержится информация, которую мы хотим, чтобы наша формула возвращала нам. В этом случае нам нужна ставка комиссии, которая находится во втором столбце таблицы, поэтому мы вводим 2 в поле Col_index_num :
Наконец, мы вводим значение в поле Range_lookup .
Важно: именно использование этого поля отличает два способа использования VLOOKUP. Чтобы использовать VLOOKUP с базой данных, этот последний параметр Range_lookup всегда должен быть установлен на FALSE , но при этом другом использовании VLOOKUP мы должны либо оставить его пустым, либо ввести значение ИСТИНА . При использовании VLOOKUP важно, чтобы вы сделали правильный выбор для этого последнего параметра.
Чтобы быть в явном виде, мы введем значение true в поле Range_lookup . Также было бы хорошо оставить это поле пустым, так как это значение по умолчанию:
Мы завершили все параметры. Теперь мы нажимаем кнопку ОК , и Excel создает для нас формулу VLOOKUP:
Если мы поэкспериментируем с несколькими разными суммами продаж, мы сможем убедиться, что формула работает.
Заключение
В версии VLOOKUP для «базы данных», где параметром Range_lookup является FALSE , значение, передаваемое в первом параметре ( Lookup_value ) должен присутствовать в базе данных. Другими словами, мы ищем точное соответствие.
Но в этом другом использовании VLOOKUP мы не обязательно ищем точное соответствие. В этом случае «достаточно близко, достаточно хорошо». Но что мы подразумеваем под «достаточно близко»? Давайте рассмотрим пример: при поиске ставки комиссионного вознаграждения при общей сумме продаж 34 988 долларов наша формула VLOOKUP вернет нам значение 30%, что является правильным ответом. Почему он выбрал строку в таблице, содержащую 30%? Что на самом деле означает «достаточно близко» в этом случае? Давайте будем точны:
Если для Range_lookup установлено значение TRUE (или опущено), VLOOKUP будет искать в столбце 1 и соответствовать наибольшему значению, которое не превышает значения < Параметр strong> Lookup_value .
Также важно отметить, что для работы этой системы таблица должна быть отсортирована в порядке возрастания в столбце 1 !
Если вы хотите попрактиковаться с VLOOKUP, образец файла, показанного в этой статье, можно скачать здесь.
