Формула ЕСЛИ Excel - Если, то операторы

Скачать пример рабочей книги

Загрузите образец книги

В этом руководстве показано, как использовать Функция ЕСЛИ в Excel в Excel для создания операторов If Then.

Обзор функции IF

Функция ЕСЛИ проверяет выполнение условия. Если ИСТИНА делает одно, если ЛОЖЬ делает другое.

Чтобы использовать функцию IF Excel Worksheet, выберите ячейку и введите:

(Обратите внимание, как появляются входные данные формулы)

Функция ЕСЛИ Синтаксис и входы:

1	= ЕСЛИ (логический_тест; значение_если_ истинно; значение_если_ ложь)

логический_тест - Логическое выражение. Пример: A1> 4.

value_if_true - Значение или вычисление для выполнения, если логическое выражение ИСТИНА.

value_if_false - Значение или вычисление, которое необходимо выполнить, если логическое выражение - ЛОЖЬ.

ЕСЛИ - это «условная» функция. Это означает, что вы определяете логический тест, и он вернет одно значение, если этот тест оценивается как истина, и другое значение, если оно ложно.

Как использовать функцию ЕСЛИ

Вот очень простой пример, чтобы вы могли понять, что я имею в виду. Попробуйте ввести в Excel следующее:

1	= ЕСЛИ (2 + 2 = 4, «Это правда», «Это неверно!»)

Поскольку 2 + 2 на самом деле равно 4, Excel вернет «Верно!». Если бы мы использовали это:

1	= ЕСЛИ (2 + 2 = 5, «Это правда», «Это неверно!»)

Теперь Excel вернет «Это ложь!», Потому что 2 + 2 не равно 5.

Вот как можно использовать IF в электронной таблице.

1	= ЕСЛИ (C4-D4> 0; C4-D4,0)

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

Вы проверяете, больше ли C4-D4 (их текущее количество вкладок минус их лимит) 0. Это ваш логический тест. Если это правда, IF возвращает «Да» - их следует отключить. Если это ложь, IF возвращает «Нет» - вы позволяете им продолжать пить.

Что IF может вернуть

Выше мы вернули текстовую строку «Да» или «Нет». Но вы также можете возвращать числа или даже другие формулы.

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

Вы можете использовать IF для этого:

1	= ЕСЛИ (C4> D4; C4 * 0,03,0)

Если вкладка превышает предел, верните вкладку, умноженную на 0,03, что возвращает 3% вкладки. В противном случае верните 0: они не вышли за пределы своей вкладки, поэтому вы не начисляете проценты.

Использование IF с AND

Вы можете комбинировать ЕСЛИ с функцией И в Excel <>. Вы используете это в логическом тесте, который позволяет вам указать два или более условий для тестирования. Excel вернет ИСТИНА, только если ВСЕ тесты верны.

Итак, вы реализовали свою процентную ставку. Но некоторые из ваших постоянных клиентов жалуются. Раньше они всегда платили по счетам, почему вы сейчас с ними расправляетесь? Вы предлагаете решение: вы не будете взимать проценты с определенных доверенных клиентов.

Вы создаете новый столбец в своей электронной таблице, чтобы идентифицировать доверенных клиентов, и обновляете свой оператор IF с помощью функции AND:

1	= ЕСЛИ (И (C4> D4, F4 = «Нет»), C4 * 0,03,0)

Давайте посмотрим на часть AND отдельно:

1	И (C4> D4, F4 = «Нет»)

Обратите внимание на два условия:

• C4> D4: проверка, не превышают ли они ограничение на количество вкладок, как и раньше
• F4 = «Нет»: это новый бит, проверяющий, не являются ли они надежным клиентом.

Итак, теперь мы возвращаем процентную ставку только в том случае, если клиент зашел за свою вкладку, И у нас есть «Нет» в столбце доверенных клиентов. Ваши завсегдатаи снова счастливы.

Подробнее читайте на главной странице функции Excel И <>.

Использование IF с OR

ИЛИ - еще одна логическая функция Excel. Как и AND, он позволяет вам определять более одного условия. Но в отличие от AND, он вернет ИСТИНА, если ЛЮБОЙ из заданных вами тестов верен.

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

Итак, вы добавляете новый столбец для идентификации заблокированных клиентов и обновляете свой «Отключить?» столбец с тестом ИЛИ:

1	= ЕСЛИ (ИЛИ (C4> D4, E4 = «Да»), «Да», «Нет»)

Глядя только на часть ИЛИ:

1	ИЛИ (C4> D4, E4 = "Да")

Есть два условия:

• C4> D4: проверка, не превышают ли они ограничение на количество вкладок
• F4 = «Да»: новая часть, проверка, заблокированы ли они в настоящее время

Это будет оценено как истина, если они перешли на свою вкладку или если в столбце E есть «Да». Как видите, Гарри отключен сейчас, даже если он не превышает свой предел вкладки.

Подробнее читайте на главной странице функции Excel ИЛИ <>.

Использование IF с XOR

XOR - еще одна логическая функция, которая возвращает «Исключающее ИЛИ». Это немного менее интуитивно понятно, чем предыдущие, которые мы обсуждали.

В простых случаях вы определяете два условия, и XOR вернет:

• ИСТИНА, если любой из аргументов верен (то же самое, что и обычное ИЛИ)
• ЛОЖЬ, если оба аргумента верны
• ЛОЖЬ, если оба аргумента ложны

Пример может прояснить это. Представьте, что вы хотите начать давать ежемесячные бонусы своим сотрудникам:

• Если они продают еду на сумму более 800 долларов или напитки на сумму более 800 долларов, вы дадите им половину бонуса.
• Если они продадут оба варианта на сумму более 800 долларов, вы дадите им полный бонус.
• Если они продадут оба товара менее чем за 800 долларов, они не получат никакого бонуса.

Вы уже знаете, как работать, если они получат полный бонус. Вы бы просто использовали IF с AND, как описано ранее.

1	= ЕСЛИ (И (C4> 800, D4> 800), «Да», «Нет»)

Но как бы вы вычислили, кто получит половину бонуса? Вот где на помощь приходит XOR:

1	= ЕСЛИ (XOR (C4> = 800, D4> = 800), «Да», «Нет»)

Как видите, продажи напитков Вуди превысили 800 долларов, но не продуктов питания. Итак, он получает половину бонуса. Обратное верно для тренера. Дайан и Карла продали более 800 долларов за оба, поэтому они не получили половину бонуса (оба аргумента ИСТИНА), а Ребекка сделала ниже порогового значения для обоих (оба аргумента ЛОЖЬ), поэтому формула снова возвращает «Нет».

Подробнее читайте на главной странице функции Excel XOR <>.

Использование IF с NOT

НЕ - еще одна логическая функция Excel, которая очень часто используется с ЕСЛИ.

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

Вы можете использовать его с IF следующим образом:

1	= ЕСЛИ (И (C3> = 1985, НЕ (D3 = "Стивен Спилберг")), "Смотри", "Не смотри")

Здесь у нас есть таблица с данными по некоторым фильмам 1980-х годов. Мы хотим идентифицировать фильмы, выпущенные после 1985 года, которые не были сняты Стивеном Спилбергом.

Поскольку НЕ вложено в функцию И, Excel сначала оценит это. Затем он будет использовать результат как часть AND.

Подробнее читайте на главной странице о функции НЕ в Excel <>.

Вложенные операторы IF

Вы также можете вернуть оператор IF в своем операторе IF. Это позволяет выполнять более сложные вычисления.

Вернемся к нашим клиентам. Представьте, что вы хотите классифицировать клиентов по уровню их долга перед вами:

• $ 0: нет
• До 500 долларов США: низкая
• От 500 до 1000 долларов: средний
• Свыше $ 1000: высокий

Вы можете сделать это, «вложив» операторы IF:

1	= ЕСЛИ (C4 = 0, «Нет», ЕСЛИ (C4 <= 500, «Низкий», ЕСЛИ (C4 <= 1000, «Средний», ЕСЛИ (C4> 1000, «Высокий»))))

Это легче понять, если вы поместите операторы IF в отдельные строки (ALT + ENTER в Windows, CTRL + COMMAND + ENTER на Mac):

12345

знак равноЕСЛИ (C4 = 0, «Нет»,ЕСЛИ (C4 <= 500, «Низкий»,ЕСЛИ (C4 <= 1000, «Средний»,ЕСЛИ (C4> 1000, «Высокий», «Неизвестный»))))

ЕСЛИ C4 равен 0, мы возвращаем «Нет». В противном случае мы переходим к следующему оператору IF. ЕСЛИ C4 меньше или равно 500, мы возвращаем «Низкий». В противном случае мы переходим к следующему оператору IF… и так далее.

Упрощение сложных операторов IF с помощью вспомогательных столбцов

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

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

Столбцы-помощники - отличный способ решить эту проблему.

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

Вот критерии:

Поэтому, если вам меньше 55 лет, вам необходимо иметь за плечами 30-летний стаж работы, чтобы иметь право на участие в программе. Если вам от 55 до 59 лет, вам потребуется стаж 15 лет. И так далее, до 65 лет, где вы имеете право независимо от того, как долго вы там проработали.

Вы можете использовать один сложный оператор IF для решения этой проблемы:

1	= ЕСЛИ (ИЛИ (F4> = 65, И (F4> = 62, G4> = 5), И (F4> = 60, G4> = 10), И (F4> = 55, G4> = 15), G4 > 30), «Допущено», «Не допущено»)

Ух! Довольно сложно понять это, не так ли?

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

12345678

= ЕСЛИ (ИЛИ(F4> = 65,И (F4> = 62, G4> = 5),И (F4> = 60, G4> = 10),И (F4> = 55, G4> = 15),G4> 30), «Допущено», «Не допущено»)

Итак, мы можем разделить эти пять тестов на отдельные столбцы, а затем просто проверить, верен ли какой-либо из них:

Каждый столбец в таблице от E до I содержит каждый из наших критериев отдельно. Тогда в J4 имеем следующую формулу:

1	= ЕСЛИ (СЧЁТЕСЛИ (E4: I4; ИСТИНА); «Допущено»; «Не допущено»)

Здесь у нас есть оператор IF, и логический тест использует COUNTIF <> для подсчета количества ячеек в E4: I4, содержащих TRUE.

Если СЧЁТЕСЛИ не находит значения ИСТИНА, он вернет 0, что ЕСЛИ интерпретирует как ЛОЖЬ, поэтому ЕСЛИ возвращает «Не допущено».

Если СЧЁТЕСЛИ находит какие-либо ИСТИННЫЕ значения, он вернет их количество. ЕСЛИ интерпретирует любое число, кроме 0, как ИСТИНА, поэтому возвращает «Допущено».

Такое разделение логических тестов облегчает чтение формулы, и если с ней что-то не так, гораздо легче определить, в чем ошибка.

Использование группировки для скрытия вспомогательных столбцов

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

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

Лучший вариант - группировка.

Выберите столбцы, которые вы хотите сгруппировать, в нашем случае E: I. Затем нажмите ALT + SHIFT + СТРЕЛКА ВПРАВО в Windows или COMMAND + SHIFT + K на Mac. Вы также можете перейти на вкладку «Данные» на ленте и выбрать «Группа» в разделе «Структура».

Вы увидите группу, отображаемую над заголовками столбцов, например:

Затем просто нажмите кнопку «-», чтобы скрыть столбцы:

Функция IFS

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

Из Excel 2022 и Excel 365 Microsoft представила еще одну функцию, IFS, чтобы упростить управление. Приведенный выше пример вложенного IF может быть реализован с помощью IFS следующим образом:

1234567

= IFS (C4 = 0, «Нет»,C4 <= 500, «Низкий»,C4 <= 1000, «Средний»,C4> 1000, «Высокий»,ИСТИНА, "Неизвестно",)

Вы можете прочитать все об этом на главной странице Excel IFS Function <>.

Использование IF с условным форматированием

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

Итак, давайте вернемся к нашей таблице бонусов для сотрудников, сделанной ранее.

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

Вот как бы вы это сделали:

• Выберите диапазон ячеек, содержащий ваши операторы IF. В нашем случае это E4: F8.
• Щелкните «Условное форматирование» в разделе «Стили» вкладки «Главная» на ленте.
• Нажмите «Выделить правила ячеек», а затем «Равно».
• Введите «Да» (или любое другое возвращаемое значение) в первое поле, а затем выберите нужное форматирование во втором поле. (Я выберу для этого зеленый).
• Повторите эти действия для всех возвращаемых значений (я также установлю красный цвет для значений «Нет»).

Вот результат:

Использование IF в формулах массива

Массив - это диапазон значений, и в Excel массивы представлены в виде значений, разделенных запятыми, заключенных в фигурные скобки, например:

1	{1,2,3,4,5}

Прелесть массивов в том, что они позволяют выполнять вычисление для каждого значения в диапазоне, а затем возвращать результат. Например, функция СУММПРОИЗВ принимает два массива, умножает их и суммирует результаты.

Итак, эта формула:

1	= СУММПРОИЗВ ({1,2,3}, {4,5,6})

… Возвращает 32. Почему? Давайте поработаем над этим:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

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

Например, возьмем эти данные:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png «нет»> 1 = СУММПРОИЗВ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))

Примечание. В Excel 2022 и более ранних версиях вам нужно нажать CTRL + SHIFT + ENTER, чтобы превратить это в формулу массива.

У нас получилось бы что-то вроде этого:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png «нет»> 1 $ C $ 2: $ C $ 10 = $ G2

На английском языке, если имя в столбце C совпадает с именем в G2 («Olivia»), ОБЯЗАТЕЛЬНО умножьте значения в столбцах D и E для этой строки. В противном случае не умножайте их. Затем просуммируйте все результаты.

Вы можете узнать больше об этой формуле на главной странице формулы СУММПРОИЗВ, ЕСЛИ <>.

ЕСЛИ в Google Таблицах

Функция ЕСЛИ работает в Google Таблицах точно так же, как и в Excel:

Дополнительные замечания

Используйте функцию ЕСЛИ, чтобы проверить, истинно ли условие. Если условие ИСТИНА, сделайте одно. Если это ЛОЖЬ, сделайте другое. Условие должно быть логическим выражением (например, a1> 5), ссылкой на ячейку, содержащую ИСТИНА или ЛОЖЬ, или массивом, содержащим все логические значения.

Функция ЕСЛИ может проверять только одно условие за раз. Однако вы можете «вложить» другие логические функции в условие ЕСЛИ для одновременной проверки нескольких условий:

= если (И (a1> 0, a2> 0), ИСТИНА, ЛОЖЬ)
= если (ИЛИ (a1> 0, a2> 0), ИСТИНА, ЛОЖЬ)
= if (XOR (a1> 0, a2> 0), ИСТИНА, ЛОЖЬ)
Функции ИЛИ проверяют, если один или больше условия соблюдены.
Функции И проверяют, если все условия соблюдены.
Функции XOR проверяют, если один и только один условие соблюдены.

Вы также можете «вложить» функцию ЕСЛИ в функцию ЕСЛИ:

1	= if (a1 <0, if (a2 <0, «Оба», «только 1»), «только один»)

Теперь несколько конкретных примеров того, как функция ЕСЛИ работает на практике:

1. Создайте новую рабочую книгу.

2. В ячейке A1 введите значение 10 (и нажмите Enter).

3. Затем в ячейке B1 введите следующую формулу:

1	= ЕСЛИ (A1> 5; «БОЛЬШЕ, ЧЕМ 5»; «МЕНЬШЕ 5»)

4. Теперь экран должен выглядеть так:

5. Если вы ввели формулу правильно, вы увидите сообщение «Больше 5» в ячейке B1.

6. Формула, которую вы ввели в ячейку B1, выполняет тест «A1> 5», то есть проверяет, больше ли значение в ячейке A1, чем 5. В настоящее время значение в ячейке A1 равно 10, поэтому условие истинно и сообщение Появляется «БОЛЬШЕ, ЧЕМ 5»

7. Если теперь изменить значение в ячейке A1 на 2:

Теперь сообщение в ячейке B2 имеет значение «МЕНЬШЕ 5», так как условие - ЛОЖЬ.

8. Вы можете продолжать изменять значение в ячейке A1, и сообщение в ячейке B2 изменится соответствующим образом.

9. Конечно, бывают ситуации, когда состояние может привести к пагубным последствиям:

• Что произойдет, если мы введем значение 5 в ячейку A1?

• Что делать, если мы оставим ячейку A1 пустой?

• А что, если мы поместим какой-нибудь текст в ячейку A1, например фразу DOG

Подробнее о функции ЕСЛИ в Excel

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

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

Предположим, что критерием для получения бонуса было то, что объем продаж, совершенных этим человеком, превысил 40 000 фунтов стерлингов. Вы могли просто "взглянуть" на данные и определить, что только Антон, Ньютон и Моник достигли цели.

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

Создайте новую книгу и введите данные, как указано выше. Затем в ячейке D4 введите следующую формулу: -

1	= ЕСЛИ (C4> 40000, «ВЫПЛАТА БОНУСА», «БОНУС НЕТ»)

чтобы у вас было:

Обратите внимание, как Excel показывает структуру формулы ЕСЛИ - это полезная памятная записка.

Введя формулу, нажмите ENTER, и вы увидите, что она вычисляется для первой строки:

Формула была оценена для Мартина - поскольку он заработал менее 40 000 фунтов стерлингов, он не имеет права на получение бонуса.

Затем мы перетаскиваем формулы вниз, щелкнув нижний правый угол и потянув вниз, мы можем определить, имеет ли каждый человек право на бонус:

И мы видим, что Excel определила, кто из продавцов имеет право на бонус.

Вернуться к списку всех функций в Excel

Заявления VBA IF

Вы также можете использовать операторы If в VBA. Щелкните ссылку, чтобы узнать больше, но вот простой пример:

1234567

Sub Test_IF ()Если Range ("a1"). Value <0, тоДиапазон ("b1"). Значение = "Отрицательное"Конец, еслиКонец, если

Этот код проверяет, отрицательное ли значение ячейки. Если это так, в следующей ячейке будет написано «отрицательное».