В недавней статье мы представили функцию Excel, называемую ВПР и объяснил, как его можно использовать для извлечения информации из базы данных в ячейку на локальном листе. В этой статье мы упоминали, что для VLOOKUP было два использования, и только один из них занимался запросом баз данных. В этой статье, второй и заключительной в серии VLOOKUP, мы рассмотрим это другое, менее известное использование для функции VLOOKUP.
Если вы еще этого не сделали, прочитайте первую статью VLOOKUP - в этой статье предполагается, что многие из понятий, описанных в этой статье, уже известны читателю.
При работе с базами данных VLOOKUP передается «уникальный идентификатор», который служит для идентификации записи данных, которую мы хотим найти в базе данных (например, код продукта или идентификатор клиента). Этот уникальный идентификатор должен существуют в базе данных, иначе VLOOKUP возвращает нам ошибку. В этой статье мы рассмотрим способ использования VLOOKUP, где идентификатор вообще не должен существовать в базе данных. Это похоже на то, что VLOOKUP может принять подход «достаточно близко, достаточно хороший» для возвращения данных, которые мы ищем. В определенных обстоятельствах это именно так то, что нам нужно.
Мы проиллюстрируем эту статью реальным примером - вычислением комиссий, которые генерируются по набору показателей продаж. Мы начнем с очень простого сценария, а затем постепенно сделаем его более сложным, пока единственным рациональным решением проблемы является использование VLOOKUP. Первоначальный сценарий в нашей фиктивной компании работает следующим образом: если продавец создает более чем 30 000 долларов продаж в этом году, комиссия, которую они зарабатывают на этих продажах, составляет 30%. В противном случае их комиссия составляет всего 20%. Пока это довольно простой рабочий лист:
Ячейка B2 содержит только интересную часть этого рабочего листа - формулу для определения, какую комиссионную ставку использовать: одну ниже порог в 30 000 долл. США, или один выше порог. Эта формула использует функцию Excel, называемую ЕСЛИ, Для тех читателей, которые не знакомы с IF, он работает следующим образом:
IF(condition,value if true,value if false)
Где состояние это выражение, которое оценивается как правда или же ложный, В приведенном выше примере состояние является выражением B1, который можно прочитать как «Является ли B1 меньше, чем B5?», или, иначе говоря, «Общий объем продаж меньше порога». Если ответ на этот вопрос «да» (истина), то мы используем значение true параметр функции, а именно B6 в этом случае - комиссионные, если сумма продаж была ниже порог. Если ответ на вопрос «нет» (ложь), то мы используем значение, если false параметр функции, а именно B7 в этом случае - комиссионные, если сумма продаж была выше порог.
Как вы можете видеть, общая сумма продаж в размере 20 000 долларов США дает нам комиссионную ставку в размере 20% в ячейке B2. Если мы введем стоимость в 40 000 долларов, мы получим другую комиссию:
Давайте сделаем это более сложным. Давайте представим второй порог: если продавец зарабатывает более 40 000 долларов, тогда их комиссионная ставка увеличивается до 40%:
Достаточно легко понять в реальном мире, но в ячейке B2 наша формула становится все сложнее. Если вы внимательно посмотрите на формулу, вы увидите, что третий параметр исходной функции IF ( значение, если false) теперь представляет собой целую функцию IF в своем собственном праве. Это называется вложенная функция (функция внутри функции). Он отлично работает в Excel (он даже работает!), Но его труднее читать и понимать.
Мы не собираемся вдаваться в гайки о том, как и почему это работает, и не будем рассматривать нюансы вложенных функций. Это учебник по VLOOKUP, а не Excel в целом.
Во всяком случае, это становится хуже! Как насчет того, когда мы решаем, что если они зарабатывают более 50 000 долларов, тогда они имеют право на 50% комиссионных, а если они зарабатывают более 60 000 долларов, то они имеют право на 60% комиссионных?
Конечно. VLOOKUP на помощь!
Давайте немного изменим рабочий лист. Мы будем держать все те же цифры, но организуем их по-новому, более табличный путь:
Найдите момент и убедитесь сами, что новый Таблица тарифов работает точно так же, как и ряд пороговых значений выше.
Концептуально то, что мы собираемся сделать, - это использовать 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, в какую таблицу искать эти данные. В этом примере это, конечно, таблица тарифов. Поместите курсор в таблица_массив поле, а затем выделите всю таблицу тарифов - исключая заголовки:
Затем мы должны указать, в каком столбце таблицы содержится информация, которую мы хотим вернуть нашей формуле. В этом случае нам нужна ставка комиссии, которая находится во втором столбце таблицы, поэтому мы поэтому вводим 2 в Col_Index_Num поле:
Наконец, мы вводим значение в диапазон_просмотра поле.
Важно: использование этого поля отличает два способа использования VLOOKUP. Чтобы использовать VLOOKUP с базой данных, этот последний параметр, диапазон_просмотра, всегда необходимо установить ЛОЖНЫЙ, но при этом другом использовании VLOOKUP мы должны либо оставить его пустым, либо ввести значение ПРАВДА, При использовании VLOOKUP очень важно, чтобы вы сделали правильный выбор для этого окончательного параметра.
Чтобы быть явным, мы введем значение правда в диапазон_просмотра поле. Также было бы неплохо оставить его пустым, так как это значение по умолчанию:
Мы выполнили все параметры. Теперь нажмите Хорошо и Excel строит нашу формулу VLOOKUP для нас:
Заключение
В «базе данных» версии VLOOKUP, где диапазон_просмотра параметр ЛОЖНЫЙ, значение, переданное в первом параметре (lookup_Value) должен присутствовать в базе данных. Другими словами, мы ищем точное совпадение.
Но в этом другом использовании VLOOKUP мы не обязательно ищем точное совпадение. В этом случае «достаточно близко достаточно». Но что мы подразумеваем под «достаточно близко»? Давайте воспользуемся примером: при поиске ставки комиссионного вознаграждения на общую сумму 34 988 долларов наша формула VLOOKUP вернет нам значение 30%, что является правильным ответом. Почему он выбрал строку в таблице, содержащую 30%? Что в действительности означает «почти достаточно»? Давайте уточним:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Также важно отметить, что для работы этой системы, таблица должна быть отсортирована в порядке возрастания по столбцу 1!
Если вы хотите попрактиковаться в VLOOKUP, образец файла, проиллюстрированный в этой статье, можно загрузить здесь.
