Как использовать несколько операторов ЕСЛИ в Microsoft Excel

Оператор IF в Excel запускает логический тест, который возвращает одно значение, если условие выполнено, и другое значение, если это не так. Использование одного оператора IF в Excel позволяет выполнять только базовые операции, но если вы вложите несколько операторов IF, вы сможете выполнять сложные задачи. Единственным недостатком является то, что при построении формулы нужно соблюдать осторожность, чтобы не допустить ошибок. Альтернативные функции Excel могут выполнять ту же работу с меньшим риском ошибки. Изучите основы вложенных операторов ЕСЛИ в Excel и альтернативы, чтобы найти лучший подход для ваших нужд.

Функция ЕСЛИ в Excel, по сути, говорит: «Если это правда, вернуть значение x, а если нет, вернуть значение y». Например, если вы проверяли, работают ли различные отделы вашего бизнеса были меньше или превышали бюджет, вы можете сравнить их расходы с их бюджетами с помощью функции и получить ответ «Превышение бюджета», если отдел потратил слишком много, или «В рамках бюджета», если он не сделал. Вообще говоря, вы используете операторы сравнения для формирования своего утверждения, например:

• = (равно)

• (лучше чем)

• = (больше или равно)

• <= (меньше или равно)
• <> (не равно)

Понимание синтаксиса оператора IF в Excel - важный шаг на пути к правильному его использованию. Формат функции: ЕСЛИ (логический_тест; значение_если_ истинно; [значение_если_ ложь]) где квадратные скобки указывают, что этот последний аргумент является необязательным. Их легко интерпретировать: логический_тест означает утверждение, которое вы хотите проверить, value_if_true это место, где вы хотите, чтобы Excel показывал, верен ли тест, и [value_if_false] - это необязательное место для того, что вы хотите, чтобы Excel отображал, если тест неверен.

Этого достаточно, чтобы использовать формулу Excel для операторов «если-то», что можно продемонстрировать на примере «Превышение бюджета / в пределах бюджета» из предыдущего. Представьте, что у вас есть сумма, потраченная различными отделами в столбце B, и сумма, заложенная в бюджет в столбце C, причем значения начинаются во второй строке. В ячейке D2 введите = ЕСЛИ (B2> C2, «Превышение бюджета», «В рамках бюджета») чтобы вернуть результат, который вы ищете (обратите внимание, что вам нужно использовать кавычки, чтобы заставить его вернуть текст внутри). Первая часть говорит: «Если значение в B2 больше, чем значение в C2» (если расходы больше, чем бюджет), во второй части говорится: «Затем верните текст. Сверх бюджета а в третьей части написано «если нет, то верни» В пределах бюджета."

Максимально эффективное использование средства выражения IF в Excel означает возможность комбинировать несколько операторов IF, если у вас есть задача, которая подходит для этого. Вы можете включить второй оператор IF в исходный оператор IF, а можете включить в него другой оператор IF и так далее. Это особенно полезно, если вы хотите разделить что-либо на одну из нескольких групп с помощью одной формулы. Например, вы можете использовать вложенные операторы IF, чтобы преобразовать оценки, полученные учащимися на тесте, в оценку.

Чтобы правильно использовать несколько операторов IF, подумайте о логике того, что вы просите Excel сделать. По сути, после вашего условия (первого аргумента в функции) вы можете добавить еще один оператор IF в value_if_true или value_if_false пробелы, чтобы сообщить Excel, что делать дальше. Подумайте об этом как о том, чтобы заставить Excel проверить ячейку по второму критерию, если рассматриваемая ячейка не соответствовать первому критерию (или действительно, если он действительно соответствует первому), и вы можете продолжать этот процесс еще долго время. Другими словами, вы превращаете "если это, то то" в ", если это и то, то то "или" если не то, а то, то то ".

Представьте, что вы дали студентам тест, в котором результат 85 или выше соответствует оценке от 70 до 85 процентов. - это B, от 55 до 70 процентов - это C, от 40 до 55 процентов - это D, а все, что ниже, - это Потерпеть поражение. Баллы указаны в столбце B со 2 по 11 строку. Вы можете использовать тщательно разработанную функцию ЕСЛИ в Excel с несколькими условиями для категоризации оценок учащихся.

Первая часть проста (в данном примере для оценки B2): = ЕСЛИ (B2> = 85; "A") сообщает Excel, чтобы он возвратил пятерку, если учащийся набрал 85 или выше. Вложенность позволяет добавлять другие оценки, используя третий аргумент (что делать, если условие ложно) для добавления дополнительных условий: = ЕСЛИ (B2> = 85; «A»; ЕСЛИ (B2> = 70; «B»)), который сообщает Excel, чтобы он возвращал A при оценке 85 или выше, но если нет, возвращает B, если оценка превышает 70. Продолжайте таким же образом, чтобы заполнить формулу, каждый раз используя третий аргумент для нового оператора IF: = ЕСЛИ (B2> = 85, «A», IF (B2> = 70, «B», IF (B2> = 55, «C», IF (B2> = 40, «D», «Fail»)) ))

Это выглядит сложным, поэтому его разбивка должна закрепить идею. Первые два аргумента говорят: «Если оценка в B2 составляет 85 или больше, тогда ученик получает пятерку», а третий говорит: «В противном случае проверьте следующее условие». В второй IF приводит к следующему утверждению: «(для студентов, набравших менее 85), если оценка в B2 составляет 70 или выше, тогда студент получает B, а если нет, отметьте следующее условие. «Третья IF говорит:» ​​(для студентов, набравших меньше 70), если оценка в B2 составляет 55 или выше, учащийся, получивший оценку, получает C, а если нет, отметьте следующее условие «и четвертый говорит» (для студентов, набравших меньше 55), если оценка в B2 выше 40, студент получает D, если нет, студент терпит неудачу ".

Если вы правильно введете формулу для своего оператора IF, он вернет один из параметров, которые вы указали в Excel. Однако, если вы сделаете ошибку, формула может вернуть 0 или #ИМЯ? в ячейке, содержащей вашу формулу.

А 0 ошибка означает, что либо value_if_true или value_if_false аргумент пуст. Устраните эту проблему, убедившись, что значение введено в оба аргумента или, по крайней мере, в value_if_true аргумент. В value_if_false аргумент может быть полностью удален, и он вернет ЛОЖЬ, если вы не поставите вторую запятую после value_if_true поле.

А #ИМЯ? error сообщает вам, что вы, вероятно, неправильно написали или опечатали что-то в формуле. Это может быть немного сложнее исправить, потому что это может быть любая часть формулы, но лучший совет - перепроверить все или снова записать формулу.

Формула вложенной функции ЕСЛИ в предыдущем примере получилась довольно длинной, что является одним из основных недостатков использования нескольких операторов ЕСЛИ в Excel. Они могут усложняться, и легко ошибиться, потому что вы должны тщательно следовать логике, чтобы убедиться, что все работает так, как вы предполагаете. Microsoft отмечает, что неправильно вложенная формула может даже возвращать правильный результат в большинстве случаев, но в редких случаях - неправильные результаты. Их трудно заметить.

Распространенная ошибка - не расставить условия в правильном порядке. В примере оцениваемых тестов, если вы начали с более низким баллом = ЕСЛИ (B2> = 40, «D»,… » и продолжил перечисление остальных в порядке возрастания, даже при 100 баллах вернется D. Это потому, что первое условие проверяется первым, поэтому, если вы начали с "если ячейка B2 40 или выше, верните D "он классифицирует любой результат 40 или более как D, не проверяя ничего другого, потому что он соответствует первому состояние. Это прекрасный пример того, почему вам нужно досконально следовать логике своей формулы.

Вам также необходимо убедиться, что вы добавили правильное количество скобок в конец нескольких операторов IF, чтобы правильно закрыть формулу. Excel помогает в этом, согласовывая цвета и выделяя скобки во время работы.

Технически вы можете вложить до 64 функций ЕСЛИ в одну формулу, но Microsoft не рекомендует использовать такую ​​функцию по причинам, указанным выше. Все быстро усложняется, и есть вероятность, что если вы пытаетесь вложить более 10 операторов IF, вероятно, есть лучший инструмент для работы, уже включенный в Excel.

Наиболее удобной альтернативой функции ЕСЛИ является функция ЕСЛИ, которая является версией функция, которая выполняет ту же задачу, что и вложение в одну функцию, и может обрабатывать до 127 условия. Функция IFS имеет более простой синтаксис: = IFS (логический_тест1, значение_если_истина1, [логический_тест2, значение_если_истина2], [логический_тест3, значение_если_ истинное3]…) и так далее. По сути, это работает как вложенные операторы IF, за исключением того, что последующие операторы IF занимают пространство после «значения, если истинно» без необходимости повторного вызова функции.

Формулу в примере оценки за тест можно написать намного проще с помощью функции IFS. Вместо:

= ЕСЛИ (B2> = 85, «A», IF (B2> = 70, «B», IF (B2> = 55, «C», IF (B2> = 40, «D», «Fail»)) ))

просто напишите

= IFS (B2> = 85, «A», B2> = 70, «B», B2> = 55, «C», B2> = 40, «D», TRUE, «Fail»)

В ИСТИННЫЙ в месте перед последним говорит: «Если ни одно из других значений не выполнено, вернуть следующее значение».

Эта функция доступна только в Excel 2019 или Excel для Office 365.

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

Синтаксис VLOOKUP: = ВПР (значение_просмотра, массив_таблиц, номер_столбца, [поиск_диапазона]) а изучение каждого из компонентов покажет вам, как его использовать. В lookup_value Аргумент - это значение, которое вы хотите проверить, которое должно быть ячейкой в ​​том же столбце, что и начало таблицы, по которой вы хотите его проверить, либо выше, либо ниже нее. В table_array - это диапазон ячеек, содержащий вашу таблицу с информацией, например о товарах и их соответствующих ценах, и col_index_num - это столбец, в котором Excel должен искать возвращаемое значение, с 1 указывает крайний левый столбец, 2 с указанием следующего и так далее. Ну наконец то, [range_lookup] является необязательным, но введите «ИСТИНА», если вы хотите, чтобы Excel основывал ответ на ближайшем значении, или «ЛОЖЬ», если вам нужно только точное совпадение.