VLOOKUP в Excel, часть 2: Использование VLOOKUP без базы данных

В недавней статье мы представили функцию 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 , которое можно прочитать как «Является ли B1 меньше, чем B5?», Или, другими словами, «Есть общий объем продаж меньше порога ». Если ответом на этот вопрос является «да» (true), то мы используем параметр функции значение if true , а именно B6 в этом случае — размер комиссии, если общий объем продаж был ниже . Если ответом на вопрос является «нет» (false), тогда мы используем параметр функции значение if false , а именно B7 в этом случае — размер комиссии, если общий объем продаж был выше .

Как видите, использование суммы продаж в 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, образец файла, показанного в этой статье, можно скачать здесь.

Оцените статью
TutoryBird.Ru
Добавить комментарий