Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)

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

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

Функция ЕСЛИ в Excel (общая информация)

Любая программа, даже если она небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Это может выглядеть так:

1. Проверьте весь столбец А на наличие четных чисел.
2. Если найдено четное число, добавьте такие-то значения.
3. Если четное число не найдено, то выводится надпись «не найдено».
4. Проверьте, является ли полученное число четным. 
5. Если да, то прибавьте его ко всем четным числам, выбранным в пункте 1.

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

Синтаксис функции ЕСЛИ с одним условием

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

=ЕСЛИ (логическое_выражение, значение_если_истина, значение_если_ложь)

Давайте рассмотрим синтаксис более подробно:

1. Булево выражение. Это само условие, соблюдение или несоблюдение которого проверяет Excel. Можно проверить как числовую, так и текстовую информацию.
2. Значение_если_истина. Результат, который будет отображаться в ячейке, если проверяемые данные соответствуют заданным критериям.
3. значение_если_ложь. Результат, который отображается в ячейке, если проверяемые данные не соответствуют условию.

Вот пример для ясности.

Здесь функция сравнивает ячейку A1 с числом 20. Это первый абзац синтаксиса. Если содержимое больше этого значения, в ячейке, где была записана формула, отображается значение «больше 20». Если ситуация не соответствует этому условию – «меньше или равно 20».

Если вы хотите отобразить текстовое значение в ячейке, необходимо заключить его в кавычки.

Вот еще одна ситуация. Чтобы иметь право сдать экзаменационную сессию, студенты должны пройти тестовую сессию. Студентам удалось набрать зачеты по всем предметам, и теперь остался последний, оказавшийся решающим. Наша задача – определить, кто из студентов допущен к экзаменам, а кто нет.

Поскольку мы хотим проверять текст, а не число, первым аргументом будет B2="cons".

Синтаксис функции ЕСЛИ с несколькими условиями

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

Чтобы было понятнее, вот синтаксис.

=ЕСЛИ(логическое_выражение, значение_если_истина, ЕСЛИ(логическое_выражение, значение_если_истина, значение_если_ложь))

В этом случае функция проверит сразу два критерия. Если первое условие истинно, возвращается значение, полученное в результате операции в первом аргументе. Если нет, то проверяется соответствие второму критерию.

Вот пример.

И с помощью такой формулы (показана на скриншоте ниже) можно проанализировать успеваемость каждого ученика.

Как видите, сюда добавилось еще одно условие, но принцип не изменился. Таким образом, вы можете проверить сразу несколько критериев.

Как расширить функциональность ЕСЛИ с помощью операторов И и ИЛИ

Время от времени возникает ситуация, когда нужно проверять сразу на соответствие нескольким критериям, а не использовать логические вложенные операторы, как в предыдущем примере. Для этого используйте либо функцию И или функция OR в зависимости от того, нужно ли вам соответствовать сразу нескольким критериям или хотя бы одному из них. Давайте подробнее рассмотрим эти критерии.

Функция ЕСЛИ с условием И

Иногда вам нужно проверить выражение на наличие нескольких условий одновременно. Для этого используется функция И, записанная в первый аргумент функции IF. Это работает следующим образом: если a равно единице и a равно 2, значением будет c.

Функция ЕСЛИ с условием «ИЛИ»

Функция ИЛИ работает аналогичным образом, но в этом случае истинно только одно из условий. Таким образом можно проверить максимально до 30 условий. 

Вот несколько способов использования функций И и OR как аргумент функции IF.

Сравнение данных в двух таблицах

Время от времени можно сравнивать две похожие таблицы. Например, человек работает бухгалтером и ему нужно сравнить два отчета. Есть и другие подобные задачи, например, сравнение стоимости товаров разных партий, затем оценки учащихся за разные периоды и так далее.

Чтобы сравнить две таблицы, используйте функцию COUNTIF. Давайте посмотрим на это более подробно.

Допустим, у нас есть две таблицы, содержащие характеристики двух кухонных комбайнов. И нам нужно их сравнить, и выделить различия цветом. Это можно сделать с помощью условного форматирования и функции COUNTIF. 

Наша таблица выглядит так.

Выбираем диапазон, соответствующий техническим характеристикам первого кухонного комбайна.

После этого нажмите на следующие меню: Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек.

В виде формулы форматирования запишем функцию =СЧЁТЕСЛИ (диапазон для сравнения; первая ячейка первой таблицы) = 0. В качестве сравнительного диапазона используется таблица с характеристиками второго кухонного комбайна.

Вам необходимо убедиться, что адреса являются абсолютными (со знаком доллара перед именами строк и столбцов). Добавьте =0 после формулы, чтобы Excel искал точные значения.

После этого вам необходимо задать форматирование ячеек. Для этого рядом с образцом нужно нажать на кнопку «Формат». В нашем случае мы используем заливку, потому что она наиболее удобна для этой цели. Но вы можете выбрать любое форматирование.

Мы присвоили имя столбца как диапазон. Это гораздо удобнее, чем вводить диапазон вручную.

Функция СУММЕСЛИ в Excel

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

1. Добавьте строку с суммарным доходом всех продавцов и нажмите на ячейку, которая будет содержать результат после ввода формулы. 
2. Находим кнопку fx, которая находится рядом со строкой для формул. Далее появится окно, где вы сможете через поиск найти нужную функцию. После выбора оператора необходимо нажать кнопку «ОК». Но ручной ввод всегда возможен.
   

   

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

   

4. Первый аргумент — это диапазон. Здесь вы вводите ячейки, которые хотите проверить на соответствие критериям. Если говорить о нас, то это позиции сотрудников. Введите диапазон D4:D18. Или просто выберите интересующие ячейки.
5. В поле «Критерии» введите должность. В нашем случае – «продавец». В качестве диапазона суммирования указываем те ячейки, где указаны зарплаты сотрудников (это делается как вручную, так и выделением мышкой). Нажимаем «ОК», и получаем готовую расчетную заработную плату всех сотрудников-продавцов.

Согласитесь, это очень удобно. Не так ли?

Функция СУММЕСЛИМ в Excel

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

Добавьте строку, в которой будет конечный результат, и вставьте формулу в нужную ячейку. Для этого нажмите на значок функции. Появится окно, в котором нужно найти функцию СУММЕСЛИМН. Далее выбираем его из списка и открывается знакомое окно с аргументами. Но количество этих аргументов теперь другое. Эта формула позволяет использовать бесконечное количество критериев, но минимальное количество аргументов — пять. 

Через диалог ввода аргументов можно указать только пять. Если вам нужно больше критериев, то их придется вводить вручную по той же логике, что и первые два.

Рассмотрим основные аргументы подробнее:

1. Диапазон суммирования. Ячейки, подлежащие суммированию.
2. Диапазон условий 1 – диапазон, который будет проверяться на соответствие определенному критерию. 
3. Условие 1 — это само условие.
4. Диапазон критериев 2 — это второй диапазон, который будет проверяться на соответствие критерию.
5. Условие 2 является вторым условием.

Дальше логика аналогичная. В результате мы определили заработную плату всех руководителей Южного филиала.

Функция СЧЕТЕСЛИ в Excel

Если вам нужно определить, сколько ячеек попадает под определенный критерий, используйте функцию СЧЁТЕСЛИ. Допустим, нам нужно понять, сколько продавцов работает в данной организации:

1. Сначала добавьте строку, содержащую количество продавцов. После этого нужно нажать на ячейку, где будет отображаться результат.
2. После этого нужно нажать на кнопку «Вставить функцию», которую можно найти во вкладке «Формулы». Появится окно со списком категорий. Нам необходимо выбрать пункт «Полный алфавитный список». В списке нас интересует формула СЧЁТЕСЛИ. После того, как мы его выбрали, нам нужно нажать кнопку «ОК».
   

   

3. После этого у нас есть количество продавцов, работающих в данной организации. Его получили путем подсчета количества клеток, в которых написано слово «продавец». Все просто. 

Функция СЧЁТЕСЛИМ в Excel

Аналогично формуле СУММЕСЛИМН, эта формула подсчитывает количество ячеек, соответствующих нескольким условиям. Синтаксис аналогичен, но немного отличается от формулы СУММЕСЛИМН:

1. Диапазон условий 1. Это диапазон, который будет проверяться на соответствие первому критерию.
2. Условие 1. Непосредственно первый критерий.
3. Диапазон условий 2. Это диапазон, который будет проверяться на соответствие второму критерию. 
4. Условие 2.
5. Условия стрельбища 3.

И так далее.

Итак, функция IF в Excel – не единственный, существует еще несколько его разновидностей, автоматически выполняющих самые распространенные действия, что значительно упрощает жизнь человека. 

Во многом благодаря функции IF Таблицы Excel считаются программируемыми. Это больше, чем просто калькулятор. Если вдуматься, то функция IF является краеугольным камнем любого вида программирования.

Так что если вы научитесь работать с большими объемами данных в Excel, освоить программирование будет гораздо проще. Благодаря логическим операторам эти области действительно имеют много общего, хотя Excel чаще используется бухгалтерами. Но механизм работы с данными во многом тот же. 

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