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, как и Word от Microsoft, является одной из самых полезных и функциональных программ для работы с документами. Она содержит множество опций, необходимых для любой сферы.

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

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

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

Ставим курсор в ячейку напротив первого участника таблицы — у нас это D4. Вводим «=» и выбираем функцию ЕСЛИ в списке над таблицей.

Откроется новое окно, в котором надо задать параметры функции.

Логическое выражение — любое условие, при выполнении которого можно получить ответ «да» или «нет», что на языке Экселя обозначается как «ложь» и «истина». В нашем случае мы сравниваем возраст человека с числом 18, чтобы определить, взрослый это или ребенок. Значит, в поле «Лог_выражение» следует записать формулу «С4 (ячейка с возрастом) >=18». В графе «Значение_если_истина» пишем «Взрослый», а в «Значение_если_ложь» — «Ребенок». Все буквенные обозначения должны быть в кавычках.

Кликаем ОК или нажимаем Enter. В ячейке D4, куда вводилась формула, появится результат вычислений. Чтобы применить функцию к другим позициям в таблице, надо растянуть ее на другие ячейки с помощью автозаполнения (выделения с черным крестиком).

В логическом условии функции ЕСЛИ сравнивают не только числа. Например, можно проверить, есть ли в ячейке конкретное слово.

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

Новая функция проверяет, есть ли в ячейке слово «Взрослый». Если да — она возвращает значение 500, если нет — 200.

Изучаем Эксель: функция ЕСЛИ с несколькими условиями

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

Предположим, надо поделить участников таблицы не на две категории, а на три — «Ребенок», «Взрослый» и «Пожилой.» Для этого уже недостаточно простой проверки «больше или равно 18» — а значит, надо ввести еще одно условие: в нашем случае это будет «больше 60».

Синтаксис функции ЕСЛИ выглядит следующим образом: =ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь) Аргументы после оператора ЕСЛИ заключены в скобки и разделены точкой с запятой. Добавляя новые условия, важно сохранить такой же порядок.

В первом примере наша формула выглядела так: ЕСЛИ(C4>=18;»Взрослый»;»Ребенок»)

Добавим в ней еще одно выражение — C4>60. Также надо прописать “Значение_если_истина”, которое будет выдавать Excel при соответствии — “Пожилой”. В поле “Значение_если_ложь” оставим формулу из первого примера и добавим в конце еще одну закрывающую скобку. В итоге получится: =ЕСЛИ(C4>60;«Пожилой»;ЕСЛИ(C4>=18;«Взрослый»;«Ребенок»))

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

В MS Excel 2010 таким образом можно вкладывать друг в друга до семи условий.

Читайте также:

  • 5 способов замены запятых на точки в Excel=«content_internal_link»>

  • Как в Эксель пронумеровать строки: 3 способа=«content_internal_link»>

Автор

Ольга Дмитриева

Редактор направлений «Мобильные устройства» и «Техника для дома»

Оператор Excel ЕСЛИ ИЛИ с примерами формул

В этом учебном пособии показано, как написать оператор ЕСЛИ ИЛИ в Excel для проверки различных условий «это ИЛИ то».

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

  • оператор ЕСЛИ ИЛИ в Excel

    • Примеры формулы

  • Excel ЕСЛИ ИЛИ
    • ЕСЛИ с несколькими условиями ИЛИ
    • ЕСЛИ это ИЛИ это, то вычислить
    • Формула ЕСЛИ ИЛИ с учетом регистра
    • Вложенные операторы IF OR
    • ЕСЛИ И ИЛИ формула

Чтобы оценить два или более условий и вернуть один результат, если какое-либо из условий ИСТИНА, и другой результат, если все условия ЛОЖЬ, вставьте функцию ИЛИ в логическую проверку ЕСЛИ:

ЕСЛИ(ИЛИ( условие1 , условие2 ,…), значение_если_истина, значение_если_ложь)

На простом английском языке логика формулы может быть сформулирована следующим образом: Если ячейка является «этой» ИЛИ «той», сделать одно действие, если нет, то сделать что-то другое.

Вот пример формулы ЕСЛИ ИЛИ в простейшей форме:

=ЕСЛИ(ИЛИ(B2="доставлено", B2="оплачено"), "Закрыто", "Открыто")

Что Формула гласит следующее: Если в ячейке B2 указано «доставлено» или «оплачено», пометьте заказ как «Закрытый», иначе «Открытый».

Если вы хотите, чтобы ничего не возвращал , если логическая проверка оценивается как ЛОЖЬ , включите пустую строку («») в последний аргумент:

=ЕСЛИ(ИЛИ(B2="доставлено", B2 ="оплачено"), "Закрыто", "")

Эту же формулу можно записать в более компактной форме, используя константу массива:

=ЕСЛИ(ИЛИ(B2={"доставлено","оплачено "}), "Closed", "")

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

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

Примеры формулы ЕСЛИ ИЛИ в Excel

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

Формула 1. ЕСЛИ с несколькими условиями ИЛИ

Нет конкретного ограничения на количество условий ИЛИ, встроенных в формулу ЕСЛИ, если она соответствует общим ограничениям Excel:

  • В Excel 2007 и выше , допускается до 255 аргументов общей длиной не более 8 192 символов.
  • В Excel 2003 и более ранних версиях можно использовать до 30 аргументов, а общая длина не должна превышать 1024 символа.

В качестве примера давайте проверим столбцы A, B и C на наличие пустых ячеек и вернем «Неполный», если хотя бы одна из 3 ячеек пуста. Задачу можно решить с помощью следующей функции ЕСЛИ ИЛИ: 9Если ячейка это ИЛИ это, затем вычислите

Ищете формулу, которая может сделать что-то более сложное, чем возврат предопределенного текста? Просто вложите другую функцию или арифметическое уравнение в аргументы value_if_true и/или value_if_false функции IF.

Допустим, вы рассчитываете общую сумму заказа ( Кол-во умножить на Цена за единицу ), и вы хотите применить скидку 10%, если выполняется одно из следующих условий:

  • в B2 больше или равно 10, или
  • Цена за единицу в C2 больше или равна 5 долларам.

Итак, вы используете функцию ИЛИ, чтобы проверить оба условия, и, если результат ИСТИНА, уменьшите общую сумму на 10% (B2*C2*0,9), в противном случае верните полную цену (B2*C2):

=ЕСЛИ(ИЛИ(В2>=10, С2>=5), В2*С2*0,9, B2*C2)

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

=ЕСЛИ(ИЛИ(B2>=10, C2>=5),"Да", "Нет")

На приведенном ниже снимке экрана показаны обе формулы в действии:

Формула 3.

Формула ИЛИ с учетом регистра

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

ЕСЛИ(ИЛИ(ТОЧНО( ячейка, » условие1 «), ТОЧНО( ячейка, » условие2 «)), значение_если_истина, значение_если_ложь)

В этом примере найдем и отметим идентификаторы ордеров «AA-1» и «BB-1»:

=ЕСЛИ(ИЛИ(ТОЧНО(A2, "AA-1"), ТОЧНО(A2, "BB-1")), "x", "")

В результате только два идентификатора заказов, где все буквы заглавные, помечены знаком «x»; похожие идентификаторы, такие как «aa-1» или «Bb-1», не помечаются:

Формула 4. Вложенные операторы ЕСЛИ ИЛИ в Excel

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

Чтобы продемонстрировать концепцию, давайте проверим названия элементов в столбце A и вернем «Фрукты» для Яблоко или Апельсин и «Овощи» для Помидор или Огурец :

=ЕСЛИ(ИЛИ(A2="яблоко", A2="апельсин")), "Фрукт", ЕСЛИ(ИЛИ(A2="помидор", A2="огурец"), "Овощ", ""))

Дополнительные сведения см. в разделе Вложенные ЕСЛИ с условиями ИЛИ/И.

Формула 5. Оператор IF AND OR

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

В качестве примера, мы пометим строки, в которых элемент в столбце A равен либо 9, либо0003 Apple или Orange и количество в столбце B больше 10:

=ЕСЛИ(И(ИЛИ(A2="яблоко",A2="апельсин"), B2>10), "x", "")

Дополнительные сведения см. в разделе ЕСЛИ в Excel с несколькими условиями И/ИЛИ.

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

Вас также может заинтересовать

Как использовать функцию ЕСЛИ в Excel (Простое руководство)

Простые примеры | И/или Критерии | Вложенный, если | Подробнее о If

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

Примеры Simple If

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

1а. Например, взгляните на функцию ЕСЛИ в ячейке B2 ниже.

Объяснение: если цена больше 500, функция ЕСЛИ возвращает значение High, в противном случае — значение Low.

1б. Следующая функция ЕСЛИ дает точно такой же результат.

Примечание: вы можете использовать следующие операторы сравнения: = (равно), > (больше), = (больше или равно), (не равно).

2. Всегда заключайте текст в двойные кавычки.

3а. Приведенная ниже формула вычисляет прогресс между двумя моментами времени.

3б. Вы можете использовать функцию ЕСЛИ для отображения пустой строки, если конечное значение еще не введено (см. строку 5).

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

И/или Критерий

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

1. Например, взгляните на функцию ЕСЛИ в ячейке D2 ниже.

Объяснение: функция И возвращает ИСТИНА, если первая оценка больше или равна 60, а вторая оценка больше или равна 90, в противном случае она возвращает ЛОЖЬ. Если TRUE, функция ЕСЛИ возвращает значение Pass, если FALSE, функция IF возвращает значение Fail.

2. Например, взгляните на функцию ЕСЛИ в ячейке D2 ниже.

Объяснение: функция ИЛИ возвращает ИСТИНА, если хотя бы одна оценка больше или равна 60, в противном случае она возвращает ЛОЖЬ. Если TRUE, функция ЕСЛИ возвращает значение Pass, если FALSE, функция IF возвращает значение Fail.

3. Например, взгляните на функцию ЕСЛИ в ячейке D2 ниже.

Объяснение: приведенная выше функция И имеет два аргумента, разделенных запятой (Таблица, Зеленый или Синий). Функция AND возвращает TRUE, если значение Product равно «Table», а Color равно «Green» или «Blue». Если TRUE, то функция ЕСЛИ снижает цену на 50%, если FALSE, то ЕСЛИ функция снижает цену на 10%.

Вложенные, если

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

1. Например, взгляните на вложенную формулу ЕСЛИ в ячейке C2 ниже.

Объяснение: если оценка равна 1, вложенная формула ЕСЛИ возвращает Плохо, если оценка равна 2, вложенная формула ЕСЛИ возвращает Хорошо, если оценка равна 3, вложенная формула ЕСЛИ возвращает Отлично, в противном случае она возвращает Недействительно . Если у вас Excel 2016 или более поздней версии, просто используйте функцию IFS.

2. Например, взгляните на вложенную формулу ЕСЛИ в ячейке C2 ниже.

Объяснение: если оценка меньше 60, вложенная формула ЕСЛИ возвращает F, если оценка больше или равна 60 и меньше 70, формула возвращает D, если оценка больше или равна 70 и меньше 80, формула возвращает C, если оценка больше или равна 80 и меньше 90, формула возвращает B, иначе она возвращает A.

Подробнее о If

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

1. Например, используйте операторы ЕСЛИ и И, чтобы проверить, находится ли значение между двумя числами.

Объяснение: функция И возвращает ИСТИНА, если человеку больше 12 и меньше 20 лет, иначе она возвращает ЛОЖЬ. Если ИСТИНА, функция ЕСЛИ возвращает Да, если ЛОЖЬ, функция ЕСЛИ возвращает Нет.

2. Вы можете комбинировать ЕСЛИ со СРЗНАЧ, СУММ и другими функциями Excel. Небо это предел!

Объяснение: функция И возвращает ИСТИНА, если входное значение больше 100 и среднее значение значений в именованном диапазоне Данные1 больше 100, в противном случае она возвращает ЛОЖЬ.

Читайте также: