Интервальный просмотр в функции ВПР

Многие знают и успешно используют формулу ВПР, которая входит в перечень наиболее полезных функций Excel. Иногда для правильной работы ВПР приходится вносить некоторые коррективы, но в целом функция справляется со своей задачей неплохо.

В этой заметке обсудим еще одну возможность ВПР – интервальный просмотр, при котором не требуется точного совпадения критерия. Такая возможность позволяет эффективно решать еще целый ряд задач.

Синтаксис ВПР следующий:

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

искомое_значение – критерий, по которому происходит поиск

таблица – диапазон данных, где находится искомый критерий и соответствующее ему значение

номер_столбца – количество столбцов между критерием и искомым значением (отсчет начинается от столбца с критерием)

интервальный_просмотр – 0 (ЛОЖЬ) для точного поиска, 1 (ИСТИНА либо опущено) для интервального просмотра или неточного поиска.

Первые три параметра известны многим пользователям. Обратим внимание на последний. Здесь возможны два типа поиска: точное или приблизительное совпадение для искомого критерия. В случае точного совпадения необходимо указать 0 или ЛОЖЬ. Именно этот вариант используется чаще всего.

Для выбора приблизительного совпадения (или интервального просмотра) нужно указать 1 или ИСТИНА (или вообще опустить – используется по умолчанию).

Рассмотрим алгоритм работы ВПР при выборе интервального просмотра. Прежде всего (это обязательно) столбец с критериями в таблице поиска должен быть отсортированы по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев сверху и ищет равный или ближайший меньший к указанному критерию (предыдущий по алфавиту). После нахождения подходящего критерия ВПР отсчитывает указанное количество столбцов вправо и забирает оттуда содержимое ячейки, что и является результатом работы формулы.

Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, и сверх 120% – премия 60%. Данные находятся в следующем виде.

Данные для ВПР

Нужно чтобы ВПР на основании выполнения планов продаж подтянул соответствующий размер премии. Для решения задачи в первой ячейке пропишем следующую формулу:

=ВПР(B2;$E$2:$F$5;2;1)

и протянуть вниз.

На рисунке ниже изображена схема, как работает интервальный просмотр функции ВПР.

Интервальный просмотр в ВПР

Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.

Примерно так функционирует интервальный просмотр в ВПР. Работа с текстовым критерием аналогичная.

Стоит отметить один существенный минус: данные в таблице поиска должны быть отсортированы по возрастанию критерия. В примере выше все выглядит логично, но если данные будут идти вразброс, то ВПР будет работать некорректно. Следует быть предельно внимательным при выборе типа сопоставления и, в случае выбора типа 1, проконтролировать сортировку второй таблицы. В остальном ВПР работает исправно.

Напоследок рекомендую посмотреть ролик, где все демонстрируется в движении.

Скачать файл с примером.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •