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

ВПР является одной из самых востребованных функций в Excel. Эта функция позволяет подтянуть значения из одной таблицы в другую по указанному критерию. Одной из важных составляющих является аргумент “интервальный просмотр” в функции ВПР.

Синтаксис функции ВПР:

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

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

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

0 — поиск точного совпадения

1 — поиск приблизительного совпадения

 

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

 

В абсолютном большинстве случаев в качестве интервального просмотра 0 (или ЛОЖЬ) — точный поиск. Это означает, что функция ВПР будет искать точное совпадение искомого значения со значениями в таблице. Настолько точное, что даже незаметный пробел в конце слова может вызвать ошибку #Н/Д (нет данных)

 

Рассмотрим на примере. В таблице показан поиск при помощи функции ВПР с интервальным просмотром 0. Как видите, excel точно нашел совпадение.

 

 

Теперь добавим пробел внутри ячейки с искомым значением.

 

 

Появилась ошибка #Н/Д (нет данных), хотя визуально ничего не изменилось. 

В этом некоторое коварство интервального просмотра 0, которое обязательно нужно учитывать.

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

 

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

 

Если аргумент интервальный просмотр в функции ВПР указать 1 (или ИСТИНА), то функция будет искать приблизительное совпадение в таблице. Причем, в случае с текстовыми данными, как в нашем примере, результат совершенно непредсказуем.

Убедимся в этом на примере. Для той же фамилии Казаков укажем аргумент интервальный просмотр 1. Как ни странно, ничего не изменилось.

 

 

Теперь укажем другую фамилию в качестве критерия. Результат получился совершенно другой.

 

 

И даже если указать искомое значение, которого вообще нет в левом столбце данной таблицы, ошибки #Н/Д не появится.

 

 

Как видите, результат таких вычислений довольно сложно использовать.

Однако, для чего же предусмотрена возможность приблизительного поиска в функции ВПР? Конечно же, для работы с числовыми значениями.

 

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

 

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

Часто в торговле используется ступенчатая система скидок. При превышении определенной стоимости покупки скидка возрастает. В нашем примере две таблицы: в первой перечислены чеки с суммами, во второй — условия скидки. 

 

 

Теперь в ячейку D2 напишем следующую формулу.

 

=ВПР(C2;$G$2:$H$5;2;1)

 

В качестве искомого значения укажем сумму чека.

Скопируем формулу во все ячейки столбца Скидка, не забыв *закрепить диапазон абсолютными ссылками*

 

Завершим пример указанием процентного формата для ячеек со скидкой.

А теперь сравните получившиеся значения с правой таблицей. Скидка определилась правильно. Несмотря на то, что в качестве аргумента была задана сумма, и точного совпадения нет в таблице со скидками.

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

Только обратите внимание, что в таблице процент скидки тяготеет к нижнему порогу интервала. Например, для интервала от 2000 до 5000 процент скидки указан возле нижнего порога 2000.

 

 

 

Что будет, если забыть указать аргумент интервальный просмотр?

 

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

 

 

Поэтому, если забыть его указать, то ошибки не возникнет. Но и результат вычисления будет не точным.

Вернемся к нашему предыдущему примеру и сотрем аргумент “интервальный просмотр”. 

 

 

Как видите, ошибки не возникло и какое-то значение определилось. Даже несмотря на то, что искомого значения нет в таблице. 

Это произошло потому, что по умолчанию ВПР считает интервальный просмотр равным ИСТИНЕ или 1. И осуществляет приблизительный поиск в случае интервального просмотра.

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