VLOOKUP в Excel, часть 2 Использование VLOOKUP без базы данных
В недавней статье мы представили функцию Excel под названием ВПР и объяснил, как ее можно использовать для извлечения информации из базы данных в ячейку на локальном рабочем листе. В этой статье мы упоминали, что было два варианта использования VLOOKUP, и только одно из них касалось запросов к базам данных. В этой статье, второй и последней в серии VLOOKUP, мы рассмотрим другое, менее известное использование функции VLOOKUP..
Если вы еще этого не сделали, прочитайте первую статью VLOOKUP - в этой статье предполагается, что многие концепции, описанные в этой статье, уже известны читателю..
При работе с базами данных VLOOKUP передается «уникальный идентификатор», который служит для идентификации того, какую запись данных мы хотим найти в базе данных (например, код продукта или идентификатор клиента). Этот уникальный идентификатор должен существует в базе данных, в противном случае VLOOKUP возвращает нам ошибку. В этой статье мы рассмотрим способ использования VLOOKUP, когда идентификатор вообще не должен существовать в базе данных. Похоже, что VLOOKUP может использовать подход «достаточно близко, достаточно хорошо» для возврата данных, которые мы ищем. В определенных обстоятельствах это именно так то, что нам нужно.
Мы проиллюстрируем эту статью реальным примером - расчетом комиссий, которые генерируются на основе набора показателей продаж. Мы начнем с очень простого сценария, а затем постепенно усложним его, пока единственное рациональное решение проблемы - это использование VLOOKUP. Первоначальный сценарий в нашей фиктивной компании работает следующим образом: если продавец создает за год продажи на сумму более 30 000 долларов, комиссионные, которые они получают от этих продаж, составляют 30%. В противном случае их комиссия составляет всего 20%. Пока что это довольно простой лист:
Чтобы использовать этот рабочий лист, продавец вводит свои данные о продажах в ячейку B1, а формула в ячейке B2 вычисляет правильную ставку комиссионных, которую они имеют право получать, которая используется в ячейке B3 для расчета общей комиссии, которую продавец должен ( это простое умножение B1 и B2).
Ячейка B2 содержит единственную интересную часть этой таблицы - формулу для определения, какую комиссию использовать: одну ниже порог 30 000 долларов, или один выше порог. Эта формула использует функцию Excel под названием ЕСЛИ. Для тех читателей, которые не знакомы с IF, он работает так:
ЕСЛИ(условие, значение, если true, значение, если false)
Где состояние это выражение, которое оценивает либо правда или же ложный. В приведенном выше примере состояние это выражение B1
Как видите, использование суммы продаж в 20 000 долларов дает нам комиссию 20% в ячейке B2. Если мы введем значение в 40 000 долларов, мы получим другую комиссию:
Итак, наша таблица работает.
Давайте сделаем это более сложным. Давайте введем второй порог: если продавец зарабатывает более 40 000 долларов, то его комиссия увеличивается до 40%:
Достаточно легко понять в реальном мире, но в ячейке B2 наша формула становится все более сложной. Если вы внимательно посмотрите на формулу, вы увидите, что третий параметр исходной функции IF ( значение, если ложно) теперь является целой функцией 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, в какой таблице искать эти данные. Конечно, в этом примере это таблица тарифов. Мы помещаем курсор в таблица_массив поле, а затем выделите всю таблицу ставок - исключая заголовки:
Далее мы должны указать, в каком столбце таблицы содержится информация, которую мы хотим, чтобы наша формула возвращала нам. В этом случае нам нужна ставка комиссии, которая находится во втором столбце таблицы, поэтому мы поэтому вводим 2 в Col_Index_Num поле:
Наконец, мы вводим значение в диапазон_просмотра поле.
Важно: именно использование этого поля отличает два способа использования VLOOKUP. Чтобы использовать VLOOKUP с базой данных, этот последний параметр, диапазон_просмотра, всегда должен быть установлен на ЛОЖНЫЙ, но при этом другом использовании VLOOKUP мы должны либо оставить его пустым, либо ввести значение ПРАВДА. При использовании VLOOKUP важно, чтобы вы сделали правильный выбор для этого последнего параметра.
Чтобы быть явным, мы введем значение правда в диапазон_просмотра поле. Также было бы хорошо оставить это поле пустым, так как это значение по умолчанию:
Мы завершили все параметры. Теперь мы нажимаем Хорошо Кнопка, и Excel создает нашу формулу VLOOKUP для нас:
Если мы поэкспериментируем с несколькими разными суммами продаж, мы сможем убедиться, что формула работает.
Заключение
В «базе данных» версия VLOOKUP, где диапазон_просмотра параметр ЛОЖНЫЙ, значение, переданное в первом параметре (lookup_Value) должен присутствовать в базе данных. Другими словами, мы ищем точное соответствие.
Но в этом другом использовании VLOOKUP мы не обязательно ищем точное соответствие. В этом случае «достаточно близко, достаточно хорошо». Но что мы подразумеваем под «достаточно близко»? Давайте рассмотрим пример: при поиске ставки комиссионного вознаграждения при общей сумме продаж 34 988 долларов наша формула VLOOKUP вернет нам значение 30%, что является правильным ответом. Почему он выбрал строку в таблице, содержащую 30%? Что на самом деле означает «достаточно близко» в этом случае? Давайте будем точны:
когда диапазон_просмотра установлен в ПРАВДА (или опущено), VLOOKUP будет смотреть в столбце 1 и совпадать самое высокое значение, которое не превышает lookup_Value параметр.
Также важно отметить, что для работы этой системы, таблица должна быть отсортирована в порядке возрастания в столбце 1!
Если вы хотите попрактиковаться с VLOOKUP, образец файла, показанного в этой статье, можно скачать здесь.
