ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel

Автор:

27 марта 2021 13:14

Метки: Microsoft Excel ВПР работа функции

1119

В абсолютном большинстве случаев ВПР применяется для поиска точного совпадения – для этого в последнем аргументе функции вводится значение ЛОЖЬ. Тем не менее, также очень полезно знать, как и для чего применяется ВПР со значением ИСТИНА в качестве последнего аргумента, тем более что сферы применения такого варианта ВПР достаточно интересны и на самом деле встречаются намного чаще, чем можно подумать.

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

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel

Для этого сперва переведём эту сетку оценок в читаемую для ВПР форму. Для этого в левом столбце вносим всегда минимальное значение баллов для каждой оценки:

Верхнюю же границу задавать не надо – она будет определяется по пороговому значению каждого следующего уровня.

При этом обязательно нужно обратить внимание, что значения во вспомогательной таблице должны быть просортированы в возрастающем порядке! Буквально через пару строк я объясню, как работает ВПР с ИСТИНА в последнем аргументе, и сразу станет почему сортировка вспомогательной таблицы так важна.

Но перед этим сперва пропишем функцию:

Текст функции у нас выходит: =ВПР(C3;$F$7:$G$10;2;ИСТИНА)

С3 – ссылка на искомое количество баллов

$F$7:$G$10 – ссылка на вспомогательную таблицу. Обязательно закрепляем с помощью F4

2 – это указание, что нужно значение из второго столбца вспомогательной таблицы

ИСТИНА – Поиск «примерного» значения

Отлично! Ну и остаётся лишь протянуть функцию:

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

Итак, у функции ВПР с ИСТИНОЙ в последнем аргументе есть три варианта срабатывания:

Первый: ВПР находит точное совпадение. Отличным примером является студент А. ВПР видит, что у него оценка 75 и начинает пошагово проверять каждую строчку в справочной табличке. 0 не подходит, идём дальше, 60 не подходит, идём еще дальше, и вот следующим и попадается 75. Значение точно соответствует искомому, поэтому Excel без раздумий берёт соседнее справа значение.

Теперь второй вариант – оценка студента В. Взяв его 86 баллов, Excel также начинает пошагово проверять справочную табличку. Сперва всё похоже с первым вариантом. 0 не подходит, 60 не подходит, 75 не подходит, но вдруг, что происходит дальше – следующее значение 90 у нас уже больше, чем искомое 86, поэтому Excel делает один шаг назад, возвращаясь к предыдущему уровню, и берёт его оценку. В итоге мы имеем как раз то, что нужно. Значение 86 лежит между 75 и 89, поэтому итоговой оценкой выходит 4.

Ну и последний вариант срабатывания ВПР с ИСТИНОЙ — это выведение ошибки. Такое происходит, если все значения в указанной табличке сравнения больше искомого и Excel не может найти подходящих значений.

Вот такой вариант использования ВПР со значением ИСТИНА в последнем аргументе. Естественно, применять этот вариант функции можно во всех схожих сюжета - вычислении комиссий, налогов и т.д.

Ссылки по теме: