Формулы эксель если в эксель: ЕСЛИ (функция ЕСЛИ)

ЕСЛИ (функция ЕСЛИ)

Формулы и функции

Формулы и функции

Формулы и функции

Функция ЕСЛИ

• 
  
  Общие сведения о формулах в Excel
  
  
  
  
  Статья
  
  
• 
  
  ПРОСМОТРX
  
  
  
  
  Статья
  
  
• 
  
  ВПР
  
  
  
  
  Статья
  
  
• 
  
  Функция СУММ
  
  
  
  
  Статья
  
  
• 
  
  Функция СЧЁТЕСЛИ
  
  
  
  
  Статья
  
  
• 
  
  Функция ЕСЛИ
  
  
  
  
  Статья
  
  
• 
  
  ЕСЛИМН
  
  
  
  
  Статья
  
  
• 
  
  СУММЕСЛИ
  
  
  
  
  Статья
  
  
• 
  
  СУММЕСЛИМН
  
  
  
  
  Статья
  
  
• 
  
  ПОИСКПОЗ
  
  
  
  
  Статья
  
  

Далее:


Использование функций

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. ..Меньше

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

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).

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

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Например:

Простые примеры функции ЕСЛИ

• =ЕСЛИ(C2=»Да»;1;2)

В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)

В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст «Да», в противном случае вернуть текст «Нет»). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.

В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст «Превышение бюджета», в противном случае вернуть текст «В пределах бюджета»)

На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение «Фактические» больше значения «Плановые», то вычесть сумму «Плановые» из суммы «Фактические», в противном случае ничего не возвращать).

В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = «Да», то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)

Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: «Текст»). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.

Распространенные неполадки

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Операторы вычислений и их приоритеты в Excel

Использование вложенных функций в формуле

Использование функции ЕСЛИ для проверки ячейки на наличие символов

Видео: расширенное применение функции ЕСЛИ

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздние версии)

Усложненные функции ЕСЛИ: как работать с вложенными формулами и избежать ошибок

Обучающие видео: усложненные функции ЕСЛИ

Подсчет значений на основе одного условия с помощью функции СЧЁТЕСЛИ

Подсчет значений на основе нескольких условий с помощью функции СЧЁТЕСЛИМН

Суммирование значений на основе одного условия с помощью функции СУММЕСЛИ

Суммирование значений на основе нескольких условий с помощью функции СУММЕСЛИМН

Функция И

Функция ИЛИ

Функция ВПР

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Логические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Функция ЕСЛИ в Excel с примерами нескольких условий

Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.

Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

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

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

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

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

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

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.



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

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

Синтаксис будет выглядеть следующим образом:

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

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Как сравнить данные в двух таблицах

Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.

Исходные данные (таблицы, с которыми будем работать):

Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

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

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Функция ЕСЛИ

Формулы и функции

Справка по Excel и обучение

Формулы и функции

Формулы и функции

ЕСЛИ функция

• Обзор формул в Excel
  

  Статья

• XLOOKUP
  

  Статья

• ВПР
  

  Статья

• Функция СУММ
  

  Статья

• СЧЁТЕСЛИ функция
  

  Статья

• ЕСЛИ функция
  

  Статья

• ИФС
  

  Статья

• СУММЕСЛИ
  

  Статья

• СУММЕСЛИМН
  

  Статья

• СООТВЕТСТВИЕ
  

  Статья

Следующий:

Обычное использование функций

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно. .. Меньше

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

Таким образом, оператор IF может иметь два результата. Первый результат — если ваше сравнение истинно, второй — если ваше сравнение ложно.

Например, =ЕСЛИ(C2=”Да”,1,2) говорит ЕСЛИ(C2=Да, то возвращает 1, иначе возвращает 2).

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

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

Например:

Простые примеры IF

• = ЕСЛИ (C2 = «Да», 1,2)

В приведенном выше примере ячейка D2 говорит: ЕСЛИ(C2 = Да, то вернуть 1, иначе вернуть 2)

В этом примере формула в ячейке D2 говорит: ЕСЛИ(C2 = 1, затем вернуть Да, иначе вернуть Нет) Как видите, функцию ЕСЛИ можно использовать для оценки как текста, так и значений. Его также можно использовать для оценки ошибок. Вы не ограничены только проверкой равенства одной вещи другой и возвратом одного результата, вы также можете использовать математические операторы и выполнять дополнительные вычисления в зависимости от ваших критериев. Вы также можете вложить несколько функций ЕСЛИ вместе, чтобы выполнить несколько сравнений.

В приведенном выше примере функция ЕСЛИ в D2 говорит ЕСЛИ (C2 больше, чем B2, затем вернуть «Превышение бюджета», в противном случае вернуть «В рамках бюджета»)

• =ЕСЛИ(С2>В2,С2-В2,0)

На приведенном выше рисунке вместо текстового результата мы собираемся вернуть математический расчет. Таким образом, формула в E2 говорит: ЕСЛИ (фактическое значение больше запланированного, затем вычтите запланированную сумму из фактической суммы, в противном случае ничего не возвращайте).

В этом примере формула в F7 говорит: ЕСЛИ(E7 = «Да», затем рассчитайте общую сумму в F5 * 8,25%, в противном случае налог с продаж не взимается, поэтому верните 0)

Примечание.  Если вы собираетесь использовать текст в формулах, его необходимо заключить в кавычки (например, «Текст»). Единственным исключением является использование ИСТИНА или ЛОЖЬ, которые Excel автоматически понимает.

Общие проблемы

Нужна дополнительная помощь?

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

См. также

Операторы вычисления и приоритет в Excel

Использование вложенных функций в формуле

Использование ЕСЛИ для проверки, пуста ли ячейка

Видео: расширенные функции ПЧ

Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)

Расширенные функции ЕСЛИ — Работа с вложенными формулами и избежание ошибок

Обучающие видео: Расширенные функции IF

Функция COUNTIF будет подсчитывать значения на основе одного критерия.

Функция COUNTIFS будет подсчитывать значения на основе нескольких критериев.

Функция СУММЕСЛИ суммирует значения на основе одного критерия.

Функция СУММЕСЛИМН суммирует значения на основе нескольких критериев.

И функция

функция ИЛИ

Функция ВПР

Обзор формул в Excel

Как избежать неработающих формул

Обнаружение ошибок в формулах

Логические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Функция ЕСЛИ – вложенные формулы и избежание ловушек

Функция ЕСЛИ позволяет провести логическое сравнение между значением и тем, что вы ожидаете, проверяя условие и возвращая результат, если оно истинно или ложно.

Таким образом, оператор IF может иметь два результата. Первый результат — если ваше сравнение истинно, второй — если ваше сравнение ложно.

Операторы

IF невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как Мужчина/Женщина, Да/Нет/Возможно, и это лишь некоторые из них, но иногда вам может понадобиться оценить более сложные сценарии, требующие вложения* более 3 функций ЕСЛИ вместе.

* «Вложенность» относится к практике объединения нескольких функций в одной формуле.

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

Синтаксис

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

Например:

Замечания

Хотя Excel позволяет вкладывать до 64 различных функций ЕСЛИ, делать это совсем не рекомендуется. Почему?

• org/ListItem»>

  Множественные операторы IF требуют тщательного обдумывания, чтобы правильно построить и убедиться, что их логика может правильно вычислять каждое условие до конца. Если вы не вложите свою формулу на 100 % точно, она может работать в 75 % случаев, но возвращать неожиданные результаты в 25 % случаев. К сожалению, шансы на то, что вы поймаете 25%, невелики.

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

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

Давайте посмотрим, как правильно создать сложную вложенную инструкцию ЕСЛИ, используя несколько ЕСЛИ, и когда понять, что пришло время использовать другой инструмент в вашем арсенале Excel.

Примеры

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

• =ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

  Этот сложный вложенный оператор IF следует простой логике:

1. Если результат теста (в ячейке D2) больше 89, учащийся получает A

2. Если результат теста выше 79, учащийся получает B

3. org/ListItem»>

   Если результат теста выше 69, учащийся получает C

4. Если результат теста больше 59, то студент получает D

5. В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

• =ЕСЛИ(В2>97,»А+»,ЕСЛИ(В2>93,»А»,ЕСЛИ(В2>89,»А-«,ЕСЛИ(В2>87,»В+»,ЕСЛИ(В2>83,» В», ЕСЛИ(В2>79,»В-«, ЕСЛИ(В2>77,»С+»,ЕСЛИ(В2>73,»С»,ЕСЛИ(В2>69,»С-«,ЕСЛИ(В2>57) ,»D+»,ЕСЛИ(B2>53,»D»,ЕСЛИ(B2>49,»D-«,»F»))))))))))))

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

Совет: Каждая функция в Excel требует открывающей и закрывающей скобки (). Excel попытается помочь вам понять, что и куда нужно, окрашивая различные части формулы, когда вы ее редактируете. Например, если вы отредактируете приведенную выше формулу, то при перемещении курсора за каждую из закрывающих скобок «)» соответствующая открывающая скобка окрасится в тот же цвет. Это может быть особенно полезно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли у вас совпадающих скобок.

Дополнительные примеры

Ниже приведен очень распространенный пример расчета комиссии с продаж на основе уровня достижения дохода.

• =ЕСЛИ(С9>15000,20%,ЕСЛИ(С9>12500,17,5%,ЕСЛИ(С9>10000,15%,ЕСЛИ(С9>7500,12,5%,ЕСЛИ(С9>5000,10%,0)) )))

Эта формула гласит: ЕСЛИ(C9 больше 15 000, то возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.)

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

Совет:  Вы можете вставлять разрывы строк в строку формул, чтобы упростить чтение длинных формул. Просто нажмите ALT+ENTER перед текстом, который вы хотите перенести на новую строку.

Вот пример сценария комиссии с неисправной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях эти типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула предлагает искать значение в C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

Аналогичным образом эта формула ищет значение в ячейке B9 в диапазоне B2:B22. Если значение найдено, вернуть соответствующее значение из той же строки в столбце C.

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

VLOOKUP рассматривается здесь более подробно, но это, безусловно, намного проще, чем 12-уровневый сложный вложенный оператор IF! Есть и другие менее очевидные преимущества:

• Справочные таблицы

  VLOOKUP открыты и легко видны.

• org/ListItem»>

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

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

Знаете ли вы?

Теперь есть функция IFS, которая может заменить несколько вложенных операторов IF одной функцией. Итак, вместо нашего примера с начальными оценками, который имеет 4 вложенные функции ЕСЛИ:

• =ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

Это можно сделать намного проще с помощью одной функции IFS:

• org/ListItem»>

  =ЕСЛИ(D2>89,»A»,D2>79,»B»,D2>69,»C»,D2>59,»D»,ИСТИНА,»F»)

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена ​​последняя версия Office.

Купить или попробовать Microsoft 365

Нужна дополнительная помощь?

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

Связанные темы

Видео: расширенные функции ПЧ

Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)

Функция СЧЁТЕСЛИ будет подсчитывать значения на основе одного критерия.