Логический оператор ЕСЛИ в 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
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.
Содержание
- 1 Что это такое
- 2 Функция ЕСЛИ в Excel (общие сведения)
- 2.1 Синтаксис функции ЕСЛИ с одним условием
- 2.2 Синтаксис функции ЕСЛИ с несколькими условиями
- 2.3 С несколькими условиями в математических выражениях
- 2.4 С операторами «и», «или», «не»
- 2.5 Для проверки ячейки на наличие символов
- 2.6 Когда один из аргументов не вписан в формулу
- 3 Сложные логические выражения
- 4 Несколько условий
- 5 Проверяем условие для полного совпадения текста.
- 5.1 ЕСЛИ + СОВПАД
- 6 Использование функции ЕСЛИ с частичным совпадением текста.
- 7 Примечания
- 8 Примеры
- 9 Функция если в Excel больше или равно
- 10 Функция если в Excel больше но меньше
- 11 Дополнительные примеры
- 12 Вы знали?
- 13 Дополнительные сведения
- 14 Как задать функции ЕСЛИ через Построитель формул
- 15 Ошибки
- 16 Вместо заключения
Что это такое
Рекомендую перед дальнейшим прочтением ознакомиться со статьей: «Правильное написание формул».
Функция ЕСЛИ проверяет условие. Когда оно выполняется (ИСТИНА) возвращается значение. Если нет — другое. 
Выглядите так: =ЕСЛИ(логическое выражение; истина; ложь).
Где:
- Выражение задается в виде числа или фразы. Например, с текстом «без налога»;
- Истина. Значение, которое отобразится если выражение правильно;
- Ложь — когда неправильно.
Простой пример формулы если в Эксель (Excel). Проверка равенства чисел из двух ячеек. 
Рассмотрим примеры формулы ЕСЛИ в Ексель (Excel).
Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:
- Проверить всю колонку А на предмет наличия четных чисел.
- Если четное число обнаружено, сложить такие-то значения.
- Если четное число не обнаружено, то выдать надпись «не обнаружено».
- Проверить получившееся число, является ли оно четным.
- Если да, то сложить его со всеми четными числами, отобранными в пункте 1.
И пусть это только гипотетическая ситуация, которая вряд ли будет нужной в реальной жизни, но выполнение любой задачи обязательно подразумевает наличие похожего алгоритма. Перед тем, как использовать функцию ЕСЛИ, нужно иметь в голове четкое представление о том, какого результата нужно достичь.
Синтаксис функции ЕСЛИ с одним условием
Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.
=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)
Давайте рассмотрим синтаксис более подробно:
- Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
- Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
- Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.
Вот пример для наглядности.
1
Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».
Если надо отображать текстовое значение в ячейке, необходимо его брать в кавычки.
Вот еще одна ситуация. Для получения права сдавать экзаменационную сессию учащимися должна быть пройдена зачетная. Студентам получилось одержать зачет по всем предметам, и теперь остался последний, который оказался решающим. Наша задача – определить, какие из студентов допущены к экзаменам, а какие – нет.
2
Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.
Синтаксис функции ЕСЛИ с несколькими условиями
Нередко недостаточно одного критерия, на предмет соответствия которому нужно проверять значение. Если надо учитывать больше одного варианта, можно вкладывать функции ЕСЛИ одна в другую. Получится несколько вложенных функций.
Чтобы было более наглядно, вот синтаксис.
=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))
В этом случае функция проверит сразу два критерия. Если первое условие оказывается истинным возвращается значение, полученное в результате совершения операции в первом аргументе. Если нет – осуществляется проверка на соответствие второму критерию.
Вот пример.
3
А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.
4
Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.
С несколькими условиями в математических выражениях
Логика такая же, как и в формуле выше, только нужно произвести математическое действие внутри оператора ЕСЛИ.
Есть таблица со стоимостью за единицу продукта, которая меняется в зависимости от его количества.
Цель – вычесть стоимость для любого количества продуктов, введенного в указанную ячейку. Количество – ячейка B8.
Формула для решения данной задачи принимает вид =B8*ЕСЛИ(B8>=101;12;ЕСЛИ(B8>=50;14;ЕСЛИ(B8>=20;16;ЕСЛИ(B8>=11; 18;ЕСЛИ(B8>=1;22;»»))))) или =B8*ЕСЛИ(B8>=101;B6;ЕСЛИ(B8>=50;B5;ЕСЛИ(B8>=20;B4;ЕСЛИ(B8>=11;B3;ЕСЛИ(B8>=1;B2;»»))))).
Было проверено несколько критериев и выполнились различные вычисления в зависимости от того, в какой диапазон суммы входит указанное количество продуктов.
С операторами «и», «или», «не»
Оператор «и» используется для проверки нескольких правдивых или нескольких ложных критериев, «или» – одно условие должно иметь верное или неверное значение, «не» – для убеждения, что данные не соответствуют одному условию.
Синтаксис выглядит так:
=ЕСЛИ(И(один_критерий;второй_критрий);значение_если_результат_соответствует_критерию;значение_если_результат_соответствует_критерию)
=ЕСЛИ(ИЛИ(один_критерий;второй_критрий);значение_если_результат_соответствует_критерию;значение_если_результат_соответствует_критерию)
=ЕСЛИ(НЕ(критерий);значение_если_результат_соответствует_критерию;значение_если_результат_соответствует_критерию)
Операторы «и», «или» теоретически могут проверить до 255 отдельных критериев, но такое количество сложно создавать, тестировать и изменять, поэтому лучше использовать до 5-ти. А «нет» – только один критерий.
Для проверки ячейки на наличие символов
Иногда нужно проверить ячейку, пустая она или нет. Требуется это для того, чтобы формула не отображала результат при отсутствии входного значения.
Пустые двойные кавычки в формуле означают «ничего». То есть: если в A2 нет символов, программа выводит текст «пустая», в противном случае будет «не пустая».
Для проверки ячейки ЕСЛИ часто используется в одной формуле c функцией ЕПУСТО (вместо пустых двойных кавычек).
Когда один из аргументов не вписан в формулу
Второй аргумент можно не вводить, когда интересующий критерий не выполняется, но тогда результат будет отображаться некрасиво.Как вариант – можно вставить в ячейку пустое значение в виде двойных кавычек.
И все-таки лучше использовать оба аргумента.
Сложные логические выражения
В аргументе Лог_выражение можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.
Основное правило — логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.
Приведем несколько полезных примеров.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1<=0;1;2)
Несколько условий
Что делать, если нужно учесть 2-3 варианта. Например, 3 условия. Проверяется значение ячейки А5. Если оно равно нулю, возвращает текст «ноль». Если «истина». Когда значение ложь, происходит другая проверка, является ли значение меньше 0, и возвращается «Меньше нуля», ЕСЛИ результат положительный. Иначе — «больше нуля». 
Проверяем условие для полного совпадения текста.
Проверку выполнения доставки организуем при помощи обычного оператора сравнения «=».
=ЕСЛИ(G2=»выполнено»,ИСТИНА,ЛОЖЬ)
При этом будет не важно, в каком регистре записаны значения в вашей таблице.
Если же вас интересует именно точное совпадение текстовых значений с учетом регистра, то можно рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет идентичность двух текстовых значений с учетом регистра отдельных букв.
Вот как это может выглядеть на примере.
Обратите внимание, что если в качестве аргумента мы используем текст, то он обязательно должен быть заключён в кавычки.
ЕСЛИ + СОВПАД
В случае, если нас интересует полное совпадение текста с заданным условием, включая и регистр его символов, то оператор «=» нам не сможет помочь.
Но мы можем использовать функцию СОВПАД (английский аналог — EXACT).
Функция СОВПАД сравнивает два текста и возвращает ИСТИНА в случае их полного совпадения, и ЛОЖЬ — если есть хотя бы одно отличие, включая регистр букв. Поясним возможность ее использования на примере.
Формула проверки выполнения заказа в столбце Н может выглядеть следующим образом:
=ЕСЛИ(СОВПАД(G2,»Выполнено»),»Да»,»Нет»)
Как видите, варианты «ВЫПОЛНЕНО» и «выполнено» не засчитываются как правильные. Засчитываются только полные совпадения. Будет полезно, если важно точное написание текста — например, в артикулах товаров.
Использование функции ЕСЛИ с частичным совпадением текста.
Выше мы с вами рассмотрели, как использовать текстовые значения в функции ЕСЛИ. Но часто случается, что необходимо определить не полное, а частичное совпадение текста с каким-то эталоном. К примеру, нас интересует город, но при этом совершенно не важно его название.
Первое, что приходит на ум – использовать подстановочные знаки «?» и «*» (вопросительный знак и звездочку). Однако, к сожалению, этот простой способ здесь не проходит.
Примечания
Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?
-
Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.
-
Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.
Если вы видите, что ваш оператор ЕСЛИ все разрастается, устремляясь в бесконечность, значит вам пора отложить мышь и пересмотреть свою стратегию.
Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel.
Примеры
Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.
=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))
Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:
-
Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.
-
Если тестовых баллов больше 79, учащийся получает оценку B.
-
Если тестовых баллов больше 69, учащийся получает оценку C.
-
Если тестовых баллов больше 59, учащийся получает оценку D.
-
В противном случае учащийся получает оценку F.
Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:
-
=ЕСЛИ(B2>97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«; ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))
Она по-прежнему работает правильно и работает правильно, но на написание и проверку нужно много времени, чтобы убедиться, что она работает правильно. Еще одна наиболее взглялая проблема в том, что вам приходилось вручную вводить оценки и эквивалентные буквы оценок. Какова вероятность случайного опечатки? Теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но действительно ли вы хотите обучебиться с такого рода усилиями и возможными ошибками, которые будет трудно обнаружить?
Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку «)» тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.
Функция если в Excel больше или равно
Рассчитаем скидки. Условия, следующие:
- До 100 000 — скидка 0 процентов;
- От 100 001 до 30000 — 3;
- 300001 до 500 000 — 5;
- Более 500001 — 7.
В ячейке А1 объем продаж. Формула примет вид: 
Функция если в Excel больше но меньше
Используйте ЕСЛИ совместно с функцией И. Если А2 больше В2 но меньше С1 возвратится значение ИСТИНА. Иначе ЛОЖЬ. 
Дополнительные примеры
Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.
5000;10%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>15000;20%;0)))))» src=»/images/ru-ru/media/b1eeb416-b9d0-4537-9594-cb54c7f86c59.png» loading=»lazy»>
Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом. Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:
-
=ВПР(C2;C5:D17;2;ИСТИНА)
В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.
-
=ВПР(B9;B2:C6;2;ИСТИНА)
Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.
Примечание: В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.
В этой области в этой области вложена более подробная информация,но это намного проще, чем 12-уровневая сложная вложенная если-выписка! Есть и другие, менее очевидные, преимущества:
-
Таблицы ссылок функции ВПР открыты и их легко увидеть.
-
Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.
-
Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.
Вы знали?
Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:
-
=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))
можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:
-
=ЕСЛИМН(D2>89;»A»;D2>79;»B»;D2>69;»C»;D2>59;»D»;ИСТИНА;»F»)
Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.
Примечание: Эта функция доступна только при наличии подписки на Microsoft 365.. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как задать функции ЕСЛИ через Построитель формул
Использовать функцию ЕСЛИ можно не только прописывая ее вручную в ячейке или строке формул, но также и через Построитель формул.
Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%.
- Ставим курсор на нужную ячейку, заходим во вкладку “Формулы”, далее щелкаем “Вставить функцию”.
- В открывшемся списке Построителя формул выбираем “ЕСЛИ” и нажимаем “Вставить функцию”.
- Открывается окно настройки функции.
В поле “логическое выражение” пишем условие, по которому будет проводиться проверка. В нашем случае это “B2=”женский”.
В поле “Истина” пишем значение, которое должно отобразиться в ячейке, если условие выполняется.
В поле “Ложь” – значение при невыполнении условия. - После того, как все поля заполнены, жмем “Готово”, чтобы получить результат.
Ошибки
В результате может отобразиться одна из ошибок:
- #ИМЯ? – опечатка в самой формуле.
- 0 – не указан истинный или ложный аргумент.
Вместо заключения
ЕСЛИ часто используют вместе с другими функциями в одной формуле для более сложных вычислений. Главное здесь – внимательно вписывать данные.
В программах OpenOffice и LibreOffice также используется ЕСЛИ (IF), а синтаксис и принцип работы функции точно такие же, как описано выше.
Источники
- https://public-pc.com/formula-esli-v-excel-3-usloviya/
- https://office-guru.ru/excel/functions-proverka-svoistv-znachenii/funkciya-esli-v-excel-primery-s-neskolkimi-usloviyami.html
- https://sysadmin-note.ru/article/funkciya-esli-v-excel/
- https://excel2.ru/articles/funkciya-esli-v-ms-excel-esli
- https://mister-office.ru/funktsii-excel/function-if-with-text.html
- https://support.microsoft.com/ru-ru/office/%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%B5%D1%81%D0%BB%D0%B8-%D0%B2%D0%BB%D0%BE%D0%B6%D0%B5%D0%BD%D0%BD%D1%8B%D0%B5-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D1%8B-%D0%B8-%D1%82%D0%B8%D0%BF%D0%BE%D0%B2%D1%8B%D0%B5-%D0%BE%D1%88%D0%B8%D0%B1%D0%BA%D0%B8-0b22ff44-f149-44ba-aeb5-4ef99da241c8
- https://MicroExcel.ru/operator-esli/
Одно из главных преимуществ электронных таблиц Excel – возможность запрограммировать функциональность конкретного документа. Как знает большинство людей из школьных уроков информатики, один главных компонентов, позволяющих реализовать это на практике – логические операторы. Один из них – это оператор ЕСЛИ, который предусматривает выполнение определенных действий при соблюдении конкретных условий.
Например, если значение соответствует определенному, то в ячейке отображается одна надпись. Если же нет – другая. Давайте рассмотрим этот эффективный инструмент более детально на практике.
Содержание
- Функция ЕСЛИ в Excel (общие сведения)
- Синтаксис функции ЕСЛИ с одним условием
- Синтаксис функции ЕСЛИ с несколькими условиями
- Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”
- Функция ЕСЛИ с условием «И»
- Функция ЕСЛИ с условием «ИЛИ»
- Сравнение данных в двух таблицах
- Функция СУММЕСЛИ в Excel
- Функция СУММЕСЛИМН в Excel
- Функция СЧЁТЕСЛИ в Excel
- Функция СЧЕТЕСЛИМН в Excel
Функция ЕСЛИ в Excel (общие сведения)
Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:
- Проверить всю колонку А на предмет наличия четных чисел.
- Если четное число обнаружено, сложить такие-то значения.
- Если четное число не обнаружено, то выдать надпись «не обнаружено».
- Проверить получившееся число, является ли оно четным.
- Если да, то сложить его со всеми четными числами, отобранными в пункте 1.
И пусть это только гипотетическая ситуация, которая вряд ли будет нужной в реальной жизни, но выполнение любой задачи обязательно подразумевает наличие похожего алгоритма. Перед тем, как использовать функцию ЕСЛИ, нужно иметь в голове четкое представление о том, какого результата нужно достичь.
Синтаксис функции ЕСЛИ с одним условием
Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.
=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)
Давайте рассмотрим синтаксис более подробно:
- Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
- Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
- Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.
Вот пример для наглядности.
Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».
Если надо отображать текстовое значение в ячейке, необходимо его брать в кавычки.
Вот еще одна ситуация. Для получения права сдавать экзаменационную сессию учащимися должна быть пройдена зачетная. Студентам получилось одержать зачет по всем предметам, и теперь остался последний, который оказался решающим. Наша задача – определить, какие из студентов допущены к экзаменам, а какие – нет.
Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.
Синтаксис функции ЕСЛИ с несколькими условиями
Нередко недостаточно одного критерия, на предмет соответствия которому нужно проверять значение. Если надо учитывать больше одного варианта, можно вкладывать функции ЕСЛИ одна в другую. Получится несколько вложенных функций.
Чтобы было более наглядно, вот синтаксис.
=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))
В этом случае функция проверит сразу два критерия. Если первое условие оказывается истинным возвращается значение, полученное в результате совершения операции в первом аргументе. Если нет – осуществляется проверка на соответствие второму критерию.
Вот пример.
А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.
Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.
Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”
Время от времени появляется ситуация проверять сразу на предмет соответствия нескольким критериям, а не использовать логические вложенные операторы, как в предыдущем примере. Для этого используется или функция И или функция ИЛИ в зависимости от того, необходимо соответствие сразу нескольким критериям или хотя ы одному из них. Давайте более детально рассмотрим эти критерии.
Функция ЕСЛИ с условием «И»
Иногда нужно проверить выражение на предмет сразу нескольким условиям. Для этого используется функция И, записанная в первом аргументе функции ЕСЛИ. Работает это так: в случае если а равно единице и а равно 2, значение будет с.
Функция ЕСЛИ с условием «ИЛИ»
Функция ИЛИ работает аналогичным образом, но в этом случае достаточно истинности только одного из условий. Максимально так можно осуществить проверку до 30 условий.
Вот варианты, как можно применять функции И и ИЛИ как аргумент функции ЕСЛИ.
Сравнение данных в двух таблицах
Время от времени доводится сравнивать две похожие таблицы. Например, человек работает бухгалтер и ему необходимо сравнить два отчета. Есть и другие подобные задачи, такие как сопоставление стоимости товаров разных партий, то, оценки учеников за разные периоды и так далее.
Для сравнения двух таблиц используется функция СЧЕТЕСЛИ. Давайте рассмотрим ее более подробно.
Допустим, у нас есть две таблицы, содержащие технические характеристики двух кухонных комбайнов. И нам нужно их сравнить, а отличия выделять цветом. Это можно сделать с помощью условного форматирования и функции СЧЕТЕСЛИ.
Наша таблица выглядит так.
Выделяем диапазон, соответствующий техническим характеристикам первого кухонного комбайна.
После этого следует кликнуть по таким меню: Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек.
В виде формулы для форматирования записываем функцию =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. В качестве сравниваемого диапазона используется таблица с особенностями второго кухонного комбайна.
Нужно убедиться, что адреса абсолютные (со значком доллара перед названием строки и колонки). После формулы нужно добавить =0, чтобы Excel искал точные значения.
После этого нужно задать форматирование ячеек. Для этого возле образца нужно нажать на кнопку «Формат». В нашем случае мы используем заливку, поскольку она наиболее удобна для этих целей. Но вы можете выбрать любое форматирование, которое хотите.
Мы в качестве диапазона назначили имя столбца. Это значительно удобнее, чем прописывать диапазон вручную.
Функция СУММЕСЛИ в Excel
А теперь перейдем к разновидностям функции ЕСЛИ, которые помогут выполнить заменить сразу два пункта алгоритма. Первая из них – СУММЕСЛИ, которая складывает два числа, которые соответствуют определенному условию. Например, перед нами стоит задача определить, сколько денег нужно платить в месяц всем продавцам. Для этого необходимо.
- Добавить ряд с общими доходами всех продавцов и нажать по ячейке, в которой будет находиться результат после ввода формулы.
- Находим кнопку fx, которая находится рядом со строчкой для формул. Далее появится окошко, где посредством поиска можно отыскать необходимую функцию. После выбора оператора нужно нажать кнопку «ОК». Но всегда возможен ручной ввод.
11 - Далее появится окно ввода аргументов функции. Все значения можно указать в соответствующих полях, а диапазон ввести через кнопку рядом с ними.
12 - Первый аргумент – диапазон. Сюда вводятся ячейки, которые нужно проверять на предмет соответствия критериям. Если говорить про нас – это должности работников. Вводим диапазон D4:D18. Или же просто осуществить выделение интересующих ячеек.
- В поле «Критерий» следует ввести должность. В случае с нами – «продавец». Как диапазон суммирования, указываем те ячейки, где перечислены заработные платы сотрудников (это делается как вручную, так и выделить их с помощью мыши). Нажимаем «ОК», и получаем готовую посчитанную заработную плату всех сотрудников, являющихся продавцами.
Согласитесь, что это очень удобно. Не так ли?
Функция СУММЕСЛИМН в Excel
Эта функция позволяет определить сумму значений, которые соответствуют нескольким условиям. Например, перед нами была поставлена задача определить суммарную заработную плату всех менеджеров, работающих в южном филиале компании.
Добавляем ряд, где будет итоговый результат, и в нужной ячейке вставляем формулу. Для этого нажимаем на значок функции. Появится окно, в котором нужно найти функцию СУММЕСЛИМН. Далее выбираем ее из списка и открывается знакомое нам окно с аргументами. Но количество этих аргументов сейчас уже другое. Эта формула дает возможность использовать бесконечное количество критериев, но минимальное количество аргументов равно пяти.
Через диалоговое окно ввода аргументов можно указать только пять. Если нужно больше критериев, то их придется вводить вручную по той же логике, что указываются первые два.
Рассмотрим главные аргументы более подробно:
- Диапазон суммирования. Ячейки, которые будут суммироваться.
- Диапазон условия 1 – диапазон, который будет проверяться на предмет соответствия определенному критерию.
- Условие 1 – непосредственно условие.
- Диапазон условия 2 – второй диапазон, который будет проверяться на предмет соответствия критерию.
- Условие 2 – второе условие.
Дальше логика аналогична. В результате, нами была определена заработная плата всех менеджеров Южного филиала.
Функция СЧЁТЕСЛИ в Excel
Если необходимо определить, сколько ячеек попадает под определенный критерий, используется функция СЧЕТЕСЛИ. Допустим, нам необходимо понять, сколько продавцов работает в этой организации:
- Сначала добавляем строку, где приводится количество продавцов. После этого нужно нажать по ячейке, где будет выводиться результат.
- После этого нужно нажать на кнопку «Вставить функцию», которую можно найти во вкладке «Формулы». Появится окно, где есть перечень категорий. Нам нужно выбрать пункт «Полный алфавитный перечень». В списке нас интересует формула СЧЕТЕСЛИ. После того, как мы ее выберем, нужно нажать кнопку «ОК».
14 - После этого у нас появляется количество продавцов, трудоустроенных в этой организации. Оно было получено методом подсчета количества ячеек, в которых написано слово «продавец». Все просто.
Функция СЧЕТЕСЛИМН в Excel
Аналогично формуле СУММЕСЛИМН, эта формула считает количество ячеек, которые подпадают под несколько условий. Синтаксис похож, но немного отличается от формулы СУММЕСЛИМН:
- Диапазон условия 1. Это диапазон, который будет проверяться на соответствие первому критерию.
- Условие 1. Непосредственно первый критерий.
- Диапазон условия 2. Это диапазон, который будет проверяться на соответствие второму критерию.
- Условие 2.
- Диапазон условия 3.
И так далее.
Таким образом, функция ЕСЛИ в Excel – не единственная, существует еще несколько ее разновидностей, которые автоматически выполняют самые распространенные действия, чем значительно упрощают человеку жизнь.
Во многом благодаря функции ЕСЛИ таблицы Excel считаются программируемыми. Это нечто больше, чем обычный калькулятор. Если так подумать, то функция ЕСЛИ является краеугольной в любом виде программирования.
Так что если научиться работать с большими количествами данных в Excel, будет значительно проще осваивать программирование. Благодаря логическим операторам действительно очень много общего у этих сфер, хотя Excel чаще и используется бухгалтерами. Но механизм работы с данными во многом одинаковый.
В правильных руках функция ЕСЛИ и ее вариации позволяют превратить лист Excel в полноценную программу, которая может действовать по сложным алгоритмам. Понимание принципов работы функции ЕСЛИ является первым шагом на пути к обучению макросам – следующему этапу более гибкой работы с электронными таблицами. Но это уже более профессиональный уровень.
Оцените качество статьи. Нам важно ваше мнение:
Анонимный вопрос
14 марта 2019 · 876,6 K
НЕ совсем корректный вопрос. Где именно нужно проверить несколько условий? надо ли это сделать последовательно или при соблюдении условия дополнительно проверить второе условие, а пи его несоблюдение третье? Все зависти о ситуации.
на самом деле ответ кроется в самом синтаксисе функции ЕСЛИ. Ее параметры
-
Условие
-
Действие, если условие выполняется
-
действие, если условие не выполняется.
так вот,вторым и третьим параметром может быть вывод определенно указанного значения, вывод значения из ячейки, расчет заданной формулы или функции, в том числе и функции ЕСЛИ!. Главное не забывать следующие правила
-
Для каждого дополнительного условия надо прописывать отдельно функцию ЕСЛИ, не забывая про скобку после ее названия.
-
Следить, чтобы новое условие не дублировало старое . Другими словами, если в ячейке С2 находится число 25, а в формуле вы напишите так
=ЕСЛИ(С2<100;10;ЕСЛИ(С2<50;5;))
результат будет 10
секрет в том, что 25 УЖЕ МЕНЬШЕ 100, поэтому остальные условия не будут проверяться.
-
для выбора последнего варианта при последовательной проверке условий прописывать отдельно условие не надо. Надо подобрать результат для ситуации, когда не сработало ни одно из перечисленных условий.
-
не забываем про скобки. В частности, при последовательной проверке условий каждое дополнительное условие это содержимое третьего параметра для предыдущей функции ЕСЛИ. Поэтому тут надо открывать скобки после каждой функции ЕСЛИ, а вот закрывать их надо в самом конце. Я применяю простое правило – сколько ЕСЛИ применил, стоько скобок в конце закрыл.
приведу пример расчета скидки по нескольким условиям.
собственно, задание
установить процент скидки следующим образом. Если цена товара меньше 500 то процент равен 2,5%, если меньше 1000 то 5%, если меньше 2500 то 7,5%, если меньше 5000 то 10%, остальные – 12,5%
при условии, что цены лежат в столбце D и начинаются с третьей строки начальная формула будет выглядеть так
=ЕСЛИ(D3<500;2,5%;ЕСЛИ(D3<1000;5%;ЕСЛИ(D3<2500;7,5%;ЕСЛИ(D3<5000;10%;12,5%))))
Желаю успеха!
https://office-study.xyz
31,8 K
Комментировать ответ…Комментировать…
Мы смотрим на бизнес через цифры и знаем, как получить максимум пользы. · 26 мар 2019 · tdots.ru
Вопрос не совсем точно сформулирован, лучше было бы привести пример конкретной задачи. Допустим, задача такая: при выполнении ряда условий вывести букву “А”, а в ином случае – “Б”.
Сочетание нескольких условий внутри функции ЕСЛИ задается с помощью логической функции И. Например, нам надо вывести А, если в A1 – текст “Привет”, в B1 – число больше 5, а в C1 – число… Читать далее
224,0 K
а если к примеру, в ячейке какое либо выражение и мне нужно найти только одно слово в этих ячейках, как… Читать дальше
Комментировать ответ…Комментировать…
помогите пожалуйста, формула не считает то что нужно, как правильно составить?
как правильно составить? нужно чтобы в ячейке J12 считалось вознграждание в зависимости от цифры в ячейке i12 , но в соответствии с процентом в столбце P17-21, если число в i12<1000000, то 0,5% и т.д Читать далее
951
Комментировать ответ…Комментировать…
Так же для нескольких условий можно испольщовать функцию =ЕСЛИМН
Функция ЕСЛИМН проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение ИСТИНА. Функцию ЕСЛИМН можно использовать вместо нескольких вложенных операторов ЕСЛИ. Ее гораздо проще читать при наличии нескольких условий.
=ЕСЛИМН(условие1; значение1… Читать далее
42,3 K
нет такой функции. Только в excel 365
Комментировать ответ…Комментировать…
