В этом коротком видео мы рассмотрим, как заменить типичную вложенную формулу ЕСЛИ формулой ВПР. По сравнению с вложенными операторами IF, VLOOKUP проще и прозрачнее. Это также легче настроить позже. После настройки вы можете изменить логику формулы, даже не касаясь самой формулы. Это просто работает.
Вы можете создать или унаследовать рабочий лист, который использует серию вложенных операторов IF для присвоения значений того или иного типа. Многие люди используют вложенные операторы IF таким образом, потому что подход прост, когда вы его освоите. Но вложенные операторы IF могут быть трудными в обслуживании и отладке.
Давайте посмотрим, как вместо этого можно использовать функцию ВПР.
Здесь мы сталкиваемся с классической проблемой присвоения оценок баллам. У каждого учащегося в списке есть набор результатов тестов, которые усреднены в столбце G. В столбце H формула использует серию из четырех операторов ЕСЛИ для определения оценки на основе среднего. Формула начинается с низких оценок и работает до высоких с помощью оператора «меньше».
Давайте добавим еще один столбец, который рассчитывает ту же оценку с помощью ВПР.
Первое, что мы сделаем, это создадим таблицу, которую мы можем использовать для выставления оценок. Нам понадобится столбец для оценок и столбец для оценок. Чтобы упростить получение необходимых нам значений из существующей формулы, мы преобразуем вложенную формулу ЕСЛИ в текст, добавив один апостроф перед знаком равенства. Теперь мы можем видеть формулу во время работы. Нам нужно добавить строку для каждой возможной оценки.
Мы можем использовать средство рисования формата, чтобы быстро применить форматирование.
Теперь у нас есть все, что нужно для выставления оценок с помощью ВПР. ВПР соответствует первому столбцу таблицы. По умолчанию ВПР не требует точного совпадения, что важно, потому что мы не хотим добавлять строку для каждого возможного результата. Однако таблица должна быть отсортирована в порядке возрастания.
Прежде чем мы начнем использовать ВПР, давайте определим имя для таблицы. Это не обязательно, но это упростит чтение нашей формулы. Назовем таблицу "grade_key".
Теперь добавим нашу формулу ВПР. Первый аргумент - это значение, которое мы ищем, которое мы получаем из столбца G. Второй аргумент - это таблица поиска. Третий аргумент - это столбец, содержащий нужное нам значение. Поскольку оценки указаны во втором столбце, мы используем цифру 2.
ВПР принимает необязательный четвертый аргумент, который контролирует точное соответствие. Значение по умолчанию - ИСТИНА, что означает «неточное совпадение». В режиме неточного соответствия ВПР будет соответствовать точным значениям, когда это возможно, и следующему наименьшему значению, когда нет.
Когда мы вводим формулу, мы получаем первую оценку. Теперь мы можем просто скопировать формулу в таблицу.
Как видите, мы получаем те же оценки, но с некоторыми приятными преимуществами.
Во-первых, саму формулу читать намного проще. Кроме того, ключ оценки отображается на листе для удобства. Наконец, сам ключ оценок управляет оценками. Мы можем легко изменить оценку и получить новые оценки. Кроме того, мы можем добавлять к ключу новые строки, и существующая формула «просто работает».
Не нужно препирать непослушное стадо скобок.
В следующий раз, когда вы столкнетесь с формулой с вложенными IF, подумайте об использовании ВПР.
Курс
Основная формулаСвязанные ярлыки
Копировать выделенные ячейки Ctrl + C ⌘ + C
