Функция VLOOKUP в Excel "вытягивает" данные из одного диапазона данных в другой диапазон данных, используя ключевое поле. Ключевое поле должно присутствовать в обоих диапазонах данных (как в том, из которого "вытаскиваются" данные, так и в том, из которого извлекаются данные).
Функция ВПР в Экселе: пошаговая инструкция
Представьте, что перед нами стоит задача определить себестоимость проданных товаров. Стоимость рассчитывается как произведение количества и цены. Это очень легко сделать, если количество и цена находятся в соседних колонках. Однако данные могут быть представлены не в таком удобном виде. Исходная информация может находиться в совершенно разных таблицах и в другом порядке. В первой таблице показано количество проданных товаров:
Во второй таблице указаны цены:
Если список товаров в обеих таблицах одинаков, то, зная волшебную комбинацию Ctrl+C и Ctrl+V, можно легко заменить данные о цене на данные о количестве. Однако порядок элементов в этих двух таблицах не совпадает. Простое копирование цен и вставка их в данные о количестве не сработает.
Поэтому мы не можем написать формулу умножения и "загрузить" все элементы.
Что мы должны делать? Нам нужно как-то подставить цены из второй таблицы в соответствующие количества в первой таблице, то есть цену продукта A в количество продукта A, цену B в количество B и так далее.
Например.
Функция ВПР в Excel может легко выполнить эту задачу.
Сначала добавим в первую таблицу новый столбец, в который будут подставляться цены из второй таблицы.
Чтобы вызвать функцию с помощью мастера, активируйте ячейку, в которой будет храниться формула, и нажмите кнопку f(x) в начале строки формул. Появится диалоговое окно Мастера, в котором из списка всех функций следует выбрать FFT.
Нажмите "БПФ". Откроется еще одно диалоговое окно.
Теперь вы должны заполнить предложенные поля. Первое поле "Искомое значение" определяет критерий для ячейки, в которую вы хотите ввести формулу. В нашем случае это ячейка с названием продукта "A".
Следующее поле - "Таблица". Здесь необходимо указать диапазон данных, в котором будет производиться поиск нужных значений. В нашем случае это вторая таблица с ценой. Крайний левый столбец выбранного диапазона должен содержать критерии поиска (столбец с названием продукта). Затем таблица выделяется справа, по крайней мере, до столбца, содержащего искомые значения (цены). Также возможен дальнейший выбор вправо, но это не имеет никакого эффекта. Самое главное, чтобы выделенная таблица начиналась в столбце критериев и охватывала нужный столбец данных. Также обратите внимание на тип ссылок, они должны быть абсолютными, так как формула будет копироваться в другие ячейки.
Следующее поле "Номер колонки" - это номер, по которому колонка с нужными данными (цены) отделяется от колонки с критерием (название товара) включительно. То есть подсчет начинается со столбца с самим критерием. Если во второй таблице оба столбца находятся рядом друг с другом, то необходимо указать count равный 2 (первый для критерия, а второй для цен). Часто бывает, что данные находятся на расстоянии 10 или 20 столбцов от критерия. Это не имеет значения, Excel все рассчитает.
Последнее поле "Интервал_просмотра" определяет тип поиска: точное (0) или приблизительное (1) соответствие критерию. Пока что мы ставим 0 (т.е. FALSE). Второй вариант рассматривается ниже.
Нажмите OK. Если все правильно и критерий найден в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только перетащить (или просто скопировать) формулу в последнюю строку таблицы.
Теперь легко рассчитать стоимость, просто умножив количество на цену.
Формулу VPR можно написать от руки, вводя аргументы по порядку и разделяя их точкой с запятой (см. видеоурок ниже).
Особенности использования формулы ВПР в Excel
Существуют специфические особенности функции БПФ, о которых вы должны знать.
1. первую особенность можно считать общей для функций, которые используются для нескольких ячеек, записывая формулу в одной ячейке, а затем копируя ее в другие. Здесь важно отметить относительность и абсолютность ссылок. В частности, в VPR критерий (первое поле) должен иметь относительную ссылку (без знаков $), поскольку каждая ячейка имеет свой собственный критерий. Но поле 'Table' должно иметь абсолютную ссылку (адрес диапазона должен содержать знак $). Если этого не сделать, то при копировании формулы диапазон "уйдет вниз" и многие значения просто не будут найдены, потому что искать будет негде.
2. номер столбца, указанный в третьем поле "Номер столбца" при использовании Мастера функций, следует считать, начиная с самого критерия.
3. функция БПФ из диапазона с искомыми данными выводит первое значение сверху. Это означает, что если вторая таблица, из которой вы пытаетесь "вытащить" какие-то данные, содержит несколько ячеек с одинаковым критерием, БПФ возьмет первое значение сверху в пределах выделенного диапазона. Важно помнить об этом. Например, если мы хотим подтянуть к цене товара количество из другой таблицы, и там этот товар встречается несколько раз (в нескольких строках), то цена будет подтянута к первому сверху количеству.
(4) Последний параметр формулы должен быть установлен на 0 (ноль). В противном случае формула может работать некорректно.
5. после использования формулы лучше удалить ее, оставив только значения формулы. Это очень легко сделать. Выделите диапазон с полученными значениями, нажмите "копировать" и вставьте значения в то же место с помощью специальной пасты. Если таблицы находятся в разных рабочих книгах Excel, очень удобно разорвать внешние связи (оставив только значения) с помощью специальной команды, расположенной по пути Изменить связи данных.
При вызове функции Разорвать внешние связи появится диалоговое окно, в котором нужно нажать кнопку Разорвать связь, а затем Закрыть.
Это позволит удалить все внешние ссылки сразу.
Примеры функции ВПР в Excel
Для следующих примеров использования функции БПФ возьмем немного другие данные.
Нам нужно перенести цены из второй таблицы в первую. Критерием, используемым здесь, является код. Ниже показаны этапы вычисления БПФ.
Вторая таблица меньше первой, т.е. некоторые коды отсутствуют. В случае отсутствия записей VPR выдает ошибку #N/D.
Возникновение таких ошибок, кстати, может быть использовано с пользой, когда нужно найти различия в таблицах. Но ошибки, скорее всего, будут мешать.
Конструкция с функцией ЕСЛИОШИБКА
Функция VPR часто используется вместе с ESLIO ERROR, которая "глушит" ошибки #H/D и возвращает вместо них некоторое значение. Обычно это значение равно 0 или пусто.
Как видите, ошибок больше нет, а вместо них - пустые ячейки.
Разные форматы критерия в таблицах
Одной из распространенных причин ошибок является несовместимость форматов критериев в двух таблицах. Текстовые и числовые форматы воспринимаются функцией VPR как разные значения. Возможны два варианта.
Первый случай - когда критерии в первой таблице хранятся в виде чисел, а критерии во второй таблице - в виде текста.
В левом верхнем углу ячеек с числами, сохраненными в виде текста, появляется зеленый треугольник. Вы можете выбрать все такие числа и выбрать в раскрывающемся списке Преобразовать в число.
Это решение используется довольно часто. Однако это не всегда правильное решение. Например, когда данные из второй таблицы регулярно выгружаются из какой-либо базы данных, например 1С. В таких файлах вообще все хранится в виде текста. А если вы планируете использовать такие данные постоянно, вставляя их в заранее определенный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства.
Вы не можете автоматически изменить формат критерия во второй таблице, потому что ссылка ведет на весь диапазон. Вы должны вмешаться в ссылку на критерий в первой таблице. Для этого добавьте функцию TEXT, которая изменяет числовой формат на текстовый. Из синтаксиса функции TEXT следует, что вам нужно указать формат. Достаточно использовать формат #. Ниже приведена фотография готовой формулы.
Еще две ошибки возникают из-за того, что эти элементы отсутствуют во второй таблице. Чтобы заглушить их, можно снова использовать функцию IF ERROR.
Вторая ситуация заключается в том, что "текст" - это критерии из первой таблицы. Форматы снова не совпадают.
Как и в прошлый раз, мы внесем коррективы в функцию VPR. Преобразование "текста" в "число" еще проще. Просто добавьте 0 к ссылке на критерий 'text' или умножьте на 1.
Существует и третья, смешанная ситуация. Это встречается гораздо реже. В первой и второй таблице критерии перемешаны в виде чисел и текста. Здесь все описанные выше функции должны выполняться одновременно: В начале мы пишем USER ERROR, TEXT и +0. В начале мы пишем USER ERROR и в качестве первого аргумента этой функции пишем ERR с некоторой структурой для переформатирования. Например, ERP с формулой TEXT. В качестве второго аргумента (который и должен быть ошибкой) мы записываем вторую структуру VPR с +0. Таким образом, если VPR с функцией TEXT не возвращает ошибку, то все в порядке. Но если первая конструкция возвращает ошибку #N/D, функция IFR ERROR заменяет вторую конструкцию - ERR на +0. Другими словами, мы принудительно делаем все критерии сначала текстовыми, а затем числовыми. Таким образом, ВПР проверяет оба формата. Один из них будет соответствовать формату во второй таблице. Это немного громоздко, но в целом работает.
Отсутствие критериев все равно приведет к ошибке #N/D. В этом случае вся формула может быть снова "завернута" в МФКК.
Функция СЖПРОБЕЛЫ для чистки текстового критерия
В качестве критерия целесообразно взять уникальный код, где опечатки, характерные для текста, маловероятны. Но иногда кода нет, а текст является критерием (названия организаций, имена людей и т.д.). В этом случае возможны случайные орфографические ошибки. Одна из распространенных ошибок - лишние пробелы. Проблема решается простым способом путем применения функции SPACE ко всем критериям. Вы можете сделать это внутри формулы VPR или предварительно заполнить все критерии в обеих таблицах. В зависимости от того, что удобнее.
Подсчет номера столбца в большой таблице
Если во второй таблице много столбцов, и даже некоторые из них скрыты или сгруппированы, нелегко напрямую подсчитать количество столбцов между критерием и нужными данными. Существует техника, позволяющая вообще не считать эти столбцы. Это делается путем просмотра правого нижнего угла выбранного диапазона при выборе второй таблицы. При этом отобразится информация о количестве выбранных строк и столбцов. Обратите внимание на количество столбцов и введите их в формулу.
Это очень экономит время.
Интервальный просмотр в функции ВПР
Теперь пришло время обсудить последний аргумент функции FIR. Как правило, я указываю 0, чтобы функция искала точное совпадение критерия. Однако существует вариант приблизительного поиска, он называется интервальным поиском.
Давайте рассмотрим алгоритм IDP при выборе интервального поиска. Прежде всего (это обязательно) столбец критериев в таблице поиска должен быть отсортирован по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев сверху и ищет равный или, если такового нет, ближайший меньший к заданному критерию, т.е. на одну ячейку выше (именно поэтому необходима предварительная сортировка). Как только подходящий критерий найден, VRP отсчитывает заданное количество столбцов вправо и извлекает оттуда содержимое ячейки, которое является результатом формулы.
Это легче понять на примере. Каждый торговый представитель должен получать заслуженную премию (в процентах от оклада), основанную на выполнении плана продаж. Если план выполнен менее чем на 100%, премия не полагается; если план выполнен от 100% до 110% (110% не считается), премия составляет 20%; от 110% до 120% (120% не считается) - 40%; 120% и выше - премия составляет 60%. Данные представлены в следующей форме.
Бонус должен быть заменен на основании выполнения планов продаж. Чтобы решить задачу, запишите в первой ячейке следующую формулу:
=VPR(B2;$E$2:$F$5;2;1)
и скопируйте его.
На рисунке ниже показана схема работы интервального представления функции БПФ.
Джеки Чан выполнил 124% плана. Поэтому в качестве критерия VPR ищет ближайшее меньшее значение во второй таблице. Это 120%. Затем он подсчитывает 2 столбца и возвращает премию в размере 60%. Брюс Ли не выполнил план, поэтому его ближайший нижний критерий равен 0%.
