ВПР является одной из самых востребованных функций в Excel. Эта функция позволяет подтянуть значения из одной таблицы в другую по указанному критерию. Одной из важных составляющих является аргумент “интервальный просмотр” в функции ВПР.
Синтаксис функции ВПР:
= ВПР (Искомое_значение; таблица; номер_столбца; интервальный просмотр)
В данной статье подробно остановимся на последнем аргументе — интервальном просмотре в функции ВПР.
Аргумент “интервальный просмотр” в функции ВПР означает точность поиска совпадений в таблице.
0 — поиск точного совпадения
1 — поиск приблизительного совпадения
В этой статье:
- ВПР с интервальным просмотром 0
- ВПР с интервальным просмотром 1
- Как посчитать скидку в Excel: интервальный просмотр 1 в функции ВПР для расчета скидки
- Что будет, если забыть указать аргумент интервальный просмотр?
ВПР с интервальным просмотром 0
В абсолютном большинстве случаев в качестве интервального просмотра 0 (или ЛОЖЬ) — точный поиск. Это означает, что функция ВПР будет искать точное совпадение искомого значения со значениями в таблице. Настолько точное, что даже незаметный пробел в конце слова может вызвать ошибку Н/Д (нет данных)
Виды ошибок в excel. Как обойти ошибки
Рассмотрим на примере. В таблице показан поиск при помощи функции ВПР с интервальным просмотром 0. Как видите, excel точно нашел совпадение.
Теперь добавим пробел внутри ячейки с искомым значением.
Появилась ошибка Н/Д (нет данных), хотя визуально ничего не изменилось.
В этом некоторое коварство интервального просмотра 0, которое обязательно нужно учитывать.
При этом, несмотря на то, что ВПР с интервальным поиском 0 ищет точное совпадение, функция игнорирует прописные и строчные буквы. Если в нашем примере мы напишем фамилию со строчной буквы, результат будет таким же.
Статья опубликована на моем сайте https://excel-analytics.ru/
Сообщество ВКонтакте
ВПР с интервальным просмотром 1
Если аргумент интервальный просмотр в функции ВПР указать 1 (или ИСТИНА), то функция будет искать приблизительное совпадение в таблице. Причем, в случае с текстовыми данными, как в нашем примере, результат совершенно непредсказуем.
Убедимся в этом на примере. Для той же фамилии Казаков укажем аргумент интервальный просмотр 1. Как ни странно, ничего не изменилось.
Теперь укажем другую фамилию в качестве критерия. Результат получился совершенно другой.
И даже если указать искомое значение, которого вообще нет в левом столбце данной таблицы, ошибки Н/Д не появится.
Как видите, результат таких вычислений довольно сложно использовать.
Однако, для чего же предусмотрена возможность приблизительного поиска в функции ВПР? Конечно же, для работы с числовыми значениями.
Как посчитать скидку в Excel: интервальный просмотр 1 в функции ВПР для расчета скидки
Сейчас я докажу на примере, что приблизительный поиск в ВПР очень может пригодиться.
Часто в торговле используется ступенчатая система скидок. При превышении определенной стоимости покупки скидка возрастает. В нашем примере две таблицы: в первой перечислены чеки с суммами, во второй — условия скидки.
Теперь в ячейку D2 напишем следующую формулу.
=ВПР(C2;$G$2:$H$5;2;1)
В качестве искомого значения укажем сумму чека.
Скопируем формулу во все ячейки столбца Скидка, не забыв закрепить диапазон абсолютными ссылками.
Завершим пример указанием процентного формата для ячеек со скидкой.
А теперь сравните получившиеся значения с правой таблицей. Скидка определилась правильно. Несмотря на то, что в качестве аргумента была задана сумма, и точного совпадения нет в таблице со скидками.
Возможности интервального просмотра 1 в функции ВПР можно использовать не только для определения величины скидки, но и для любых других числовых интервалов. К примеру, для определения возрастной группы, интервала заработных плат и т.д.
Только обратите внимание, что в таблице процент скидки тяготеет к нижнему порогу интервала. Например, для интервала от 2000 до 5000 процент скидки указан возле нижнего порога 2000.
Что будет, если забыть указать аргумент интервальный просмотр?
Аргумент интервальный просмотр в функции ВПР является необязательным. Заметьте, что он указан в квадратных скобках, что является признаком необязательного аргумента в формуле.
Вернемся к нашему предыдущему примеру и сотрем аргумент “интервальный просмотр”.
Как видите, ошибки не возникло и какое-то значение определилось. Даже несмотря на то, что искомого значения нет в таблице.
Это произошло потому, что по умолчанию ВПР считает интервальный просмотр равным ИСТИНЕ или 1. И осуществляет приблизительный поиск в случае интервального просмотра.
Потому так важно правильно указывать аргумент интервальный просмотр в функции ВПР правильно. И в случае необходимости точного поиска обязательно указывать 0.