Примеры как пользоваться функцией если в excel: Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Содержание

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

Автор Амина С. На чтение 10 мин Опубликовано 28.07.2020

Одно из главных преимуществ электронных таблиц 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 в полноценную программу, которая может действовать по сложным алгоритмам. Понимание принципов работы функции ЕСЛИ является первым шагом на пути к обучению макросам – следующему этапу более гибкой работы с электронными таблицами. Но это уже более профессиональный уровень.

Оцените качество статьи. Нам важно ваше мнение:

Функция ЕСЛИ в Excel: как использовать, примеры с несколькими условиями

Наверх

11. 12.2020

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

1 звезда2 звезды3 звезды4 звезды5 звезд

Как работает функция ЕСЛИ в Excel: примеры использования

Выясняем назначение функции если в MS Ecxel и разбираемся в работе оператора с несколькими или одним условием.

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

Приложение 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

Как в Эксель пронумеровать строки: 3 способа

Автор

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

Была ли статья интересна?

Поделиться ссылкой

Нажимая на кнопку «Подписаться»,
Вы даете согласие на обработку персональных данных

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

Функция ЕСЛИ выполняет логическую проверку и возвращает одно значение для результата ИСТИНА и другое значение для результата ЛОЖЬ. Первый аргумент, logical_test , – это выражение, которое должно возвращать либо ИСТИНА, либо ЛОЖЬ. Второй аргумент value_if_true – это значение, которое будет возвращено, когда logical_test возвращает значение TRUE. Последний аргумент value_if_false – это значение, которое будет возвращено, когда logical_test вернет FALSE. Оба value_if_true и value_if_false являются необязательными, но необходимо указать хотя бы одно из них. Результатом ЕСЛИ может быть значение, ссылка на ячейку или даже другая формула.

Пример «пройдено или не пройдено»

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

 =ЕСЛИ(C5>=70,"Пройдено","Не пройдено")

Перевод: Если значение в C5 больше или равно 70, вернуть «Пройдено». В противном случае вернуть «Fail».

Обратите внимание, что логический ход этой формулы можно изменить на противоположный. Эта формула возвращает тот же результат:

 =ЕСЛИ(C5<70,"Fail","Pass")

Перевод: если значение в C5 меньше 70, вернуть "Fail". В противном случае верните «Pass».

Обе приведенные выше формулы при копировании вниз вернут правильные результаты.

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

Назначить баллы по цвету

В таблице ниже мы хотим присвоить баллы на основе цвета в столбце B. Если цвет «красный», результат должен быть 100. Если цвет «синий», результат должен быть 125. Для этого требуется что мы используем формулу, основанную на двух функциях ЕСЛИ, одна из которых вложена в другую. Формула в C5, скопированная ниже, выглядит так:

 =ЕСЛИ(B5="красный",100,ЕСЛИ(B5="синий",125))

Перевод: ЕСЛИ значение в ячейке B5 "красное", вернуть 100. В противном случае, если значение в ячейке B5 равно "синий", возврат 125.

В этом примере следует обратить внимание на три вещи:

Формула вернет ЛОЖЬ, если значение в ячейке B5 будет любым, кроме "красного" или "синего"
Текстовые значения "красный" и "синий" должны быть заключены в двойные кавычки ("")
Функция ЕСЛИ не учитывает регистр и будет соответствовать "красному" или "красному" или "КРАСНОМУ"

Это простой пример "вложенных IF"; см. ниже более сложный пример.

Вернуть другую формулу

В результате функция ЕСЛИ может вернуть другую формулу. Например, приведенная ниже формула вернет A1*5%, если A1 меньше 100, и A1*7%, если A1 больше или равно 100:

 =ЕСЛИ(A1<100,A1*5%,A1*7%)

Вложенные операторы ЕСЛИ

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

Например, следующую формулу можно использовать для присвоения оценки, а не результата "зачет/незачет":

 =ЕСЛИ(С6<70,"F",ЕСЛИ(С6<75,"D",ЕСЛИ(С6<85,"С",ЕСЛИ(С6<95,"В","А"))))

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

Примечание. Новая функция IFS предназначена для обработки нескольких условий без вложения.

Логические операторы

При построении теста с ЕСЛИ вы можете использовать любой из следующих логических операторов:

Оператор сравнения	Значение	Пример
=	равно	А1=Д1
>	больше	А1>D1
>=	больше или равно	А1>=D1
	меньше	А1
	меньше или равно	А1
	не равно	А1Д1

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

ЕСЛИ с И, ИЛИ, НЕ

Функция ЕСЛИ может сочетаться с функциями И и ИЛИ. Например, чтобы вернуть «ОК», когда A1 находится между 7 и 10, вы можете использовать следующую формулу:

 =ЕСЛИ(И(A1>7,A1<10),"OK","")

Перевод: если A1 больше 7 и меньше 10, вернуть "OK". В противном случае ничего не возвращать ("").

Чтобы вернуть B1+10, когда A1 "красный" или "синий", вы можете использовать функцию ИЛИ следующим образом:

 =ЕСЛИ(ИЛИ(A1="красный",A1="синий"),B1+10,B1)

Перевод: если A1 красный или синий, вернуть B1+10, иначе вернуть B1.

 =ЕСЛИ(НЕ(A1="красный"),B1+10,B1)

Перевод: если A1 НЕ красный, вернуть B1+10, иначе вернуть B1.

Дополнительная информация

Подробнее о вложенных IF
Узнайте, как использовать функцию ВПР вместо вложенных ЕСЛИ (видео)
50 Примеры критериев формулы

Примечания

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

19 советов для вложенных формул ЕСЛИ

Являются ли вложенные ЕСЛИ злом? Они необходимы? Есть ли альтернативы? Ответ: Да! В этой подробной статье подробно рассматриваются вложенные формулы ЕСЛИ с множеством советов и несколькими альтернативами.

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

Но одна ЕСЛИ часто приводит к другой, и как только вы объедините более пары ЕСЛИ, ваши формулы могут начать выглядеть как маленькие Франкенштейны 🙂

Являются ли вложенные ЕСЛИ злом? Нужны ли они иногда? Какие есть альтернативы?

Читайте дальше, чтобы узнать ответы на эти и другие вопросы. ..

1. Базовая IF

Прежде чем говорить о вложенных IF, давайте кратко рассмотрим базовую структуру IF:

 =ЕСЛИ(тест,[истина],[ложь])

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

Обратите внимание на квадратные скобки... они означают, что аргументы необязательны. Однако вы должны указать либо , либо значение true, либо значение false.

Чтобы проиллюстрировать это, здесь мы используем функцию ЕСЛИ для проверки оценок и расчета "пройдено" для оценок не менее 65:

Ячейка D3 в примере содержит следующую формулу:

 =ЕСЛИ(C3>=65,"Пройдено")

Что можно прочитать так: если оценка в C3 не менее 65, вернуть "Пройдено".

Однако обратите внимание, что если значение меньше, чем 65, ЕСЛИ возвращает ЛОЖЬ, поскольку мы не указали значение, если оно ложно. Чтобы отобразить «Fail» для непроходных баллов, мы можем добавить «Fail» в качестве ложного аргумента, например:

 =ЕСЛИ(C3>=65;"Пройдено","Не пройдено")

Видео: Как строить логические формулы.

2. Что означает вложенность

Вложение просто означает объединение формул одна внутри другой, так что одна формула обрабатывает результат другой. Например, вот формула, в которой функция СЕГОДНЯ вложена в функцию МЕСЯЦ:

 =МЕСЯЦ(СЕГОДНЯ())

Функция СЕГОДНЯ возвращает текущую дату внутри функции МЕСЯЦ. Функция МЕСЯЦ принимает эту дату и возвращает текущий месяц. Даже в умеренно сложных формулах часто используется вложенность, поэтому в более сложных формулах вы увидите вложенность повсюду.

3. Простой вложенный ЕСЛИ

Вложенный ЕСЛИ — это еще два оператора ЕСЛИ в формуле, где один оператор ЕСЛИ появляется внутри другого.

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

 =IF(C3="","Incomplete",IF(C3>=65,"Pass","Fail"))

Сначала запускается внешний IF и проверяется, не заполнен ли C3. Если это так, внешний ЕСЛИ возвращает "Незавершенный", а внутренний ЕСЛИ никогда не выполняется.

Если оценка не является пустой , внешний ЕСЛИ возвращает ЛОЖЬ и выполняется исходная функция ЕСЛИ.

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

4. Вложенный ЕСЛИ для весов

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

Хитрость заключается в том, чтобы определить направление (от высокого к низкому или от низкого к высокому), а затем соответствующим образом структурировать условия. Например, чтобы присвоить оценки в порядке «от низкого к высокому», мы можем представить необходимое решение в следующей таблице. Обратите внимание, что для «А» нет условия, потому что после того, как мы выполнили все остальные условия, мы знаем, что оценка должна быть больше 95 и, следовательно, «А».

Оценка	Марка	Состояние
0-63	Ф
64-72	Д
73-84	С
85-94	Б
95-100	А

Когда условия понятны, мы можем ввести первый оператор ЕСЛИ:

 =ЕСЛИ(С5<64,"F")

Это касается "F". Теперь, чтобы обработать «D», нам нужно добавить еще одно условие:

 =ЕСЛИ(C5<64,"F",ЕСЛИ(C5<73,"D"))

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

 =IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C")))

Продолжаем в том же духе, пока не достигнем последнего класса. Затем, вместо того, чтобы добавлять еще один IF, просто добавьте окончательную оценку для false.

 =ЕСЛИ(С5<64,"F",ЕСЛИ(С5<73,"D",ЕСЛИ(С5<85,"С",ЕСЛИ(С5<95,"В","А"))))

Вот окончательная формула вложенной ЕСЛИ в действии:

Видео: Как создать вложенную ЕСЛИ для выставления оценок

5. Вложенные ЕСЛИ имеют логический поток

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

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

6. Используйте Оценку для просмотра логического потока

В Windows вы можете использовать функцию Оценки, чтобы наблюдать, как Excel решает ваши формулы шаг за шагом. Это отличный способ «увидеть» логическую последовательность более сложных формул и устранить неполадки, когда что-то работает не так, как вы ожидаете. На приведенном ниже экране показано, что окно Evaluate открыто и готово к работе. Каждый раз, когда вы нажимаете кнопку «Оценить», решается «следующий шаг» в формуле. Вы можете найти «Оценить» на вкладке «Формулы» ленты (Alt M, V).

К сожалению, версия Excel для Mac не содержит функции оценки, но вы все равно можете использовать прием F9, описанный ниже.

7. Используйте F9 для выборочной проверки результатов

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

Используйте Control + Z (Command + Z) на Mac, чтобы отменить F9. Вы также можете нажать Esc, чтобы выйти из редактора формул без каких-либо изменений.

Видео: отладка формулы с помощью F9

8. Знайте свои ограничения

В Excel есть ограничения на глубину вложения функций ЕСЛИ. До Excel 2007 в Excel было разрешено до 7 уровней вложенных ЕСЛИ. В Excel 2007+ Excel позволяет использовать до 64 уровней.

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

9. Сопоставьте скобки как профессионал

Одной из проблем с вложенными ЕСЛИ является сопоставление или «балансировка» скобок. Когда скобки не совпадают, ваша формула не работает. К счастью, E xcel предоставляет несколько инструментов, которые помогут вам убедиться, что скобки «сбалансированы» при редактировании формул.

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

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

К сожалению, выделение жирным шрифтом возможно только в Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор (Text Wrangler бесплатен и превосходен), чтобы получить лучшие инструменты сопоставления скобок. Text Wrangler будет мигать при совпадении скобок, и вы можете использовать Command + B, чтобы выбрать весь текст, содержащийся в скобках. Вы можете вставить формулу обратно в Excel после того, как все исправите.

10. Используйте всплывающую подсказку для навигации и выберите

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

Вы можете видеть, как я часто использую окно всплывающей подсказки в этом видео: Как построить вложенный ЕСЛИ.

11. Будьте осторожны с текстом и цифрами

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

 =ЕСЛИ(A1="100","Удачно","Непройдено")

Действительно ли результат теста в A1 представляет собой текст , а не число? Нет? Тогда не используйте кавычки вокруг числа. В противном случае логический тест вернет FALSE, даже если значение является проходным баллом, потому что "100" не равно 100. Если тестовый балл является числовым, используйте это:

 =ЕСЛИ(A1=100,"Удачно","Неудачно")

12. Добавьте разрывы строк, чтобы упростить чтение вложенных ЕСЛИ

Когда вы работаете с формулой, которая содержит много уровней вложенных ЕСЛИ, она может быть сложным, чтобы держать вещи прямо. Поскольку Excel не заботится о «пробелах» в формулах (т. е. о дополнительных пробелах или разрывах строк), вы можете значительно улучшить читаемость вложенных операторов if, добавив разрывы строк.

Например, на приведенном ниже экране показана вложенная ЕСЛИ, которая вычисляет комиссионную ставку на основе количества продаж. Здесь вы можете увидеть типичную вложенную структуру ЕСЛИ, которую трудно расшифровать:

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

Вы можете добавить разрывы строк в Windows с помощью Alt + Enter, на Mac — с помощью Control + Option + Return.

Видео: Как упростить чтение вложенных ЕСЛИ.

13. Ограничение ЕСЛИ с И и ИЛИ

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

Например, в приведенной ниже задаче мы хотим поставить "x" в столбце D, чтобы пометить строки, где цвет "красный" , а размер "маленький".

Мы могли бы написать формулу с двумя вложенными ЕСЛИ следующим образом:

 =ЕСЛИ(B6="красный",ЕСЛИ(C6="маленький","х",""),"")

Однако, заменив тест на функцию И, мы можем упростить формулу:

 =ЕСЛИ(И(B6="красный",C6="маленький"),"x","")

Таким же образом мы можем легко расширить эту формулу с помощью функции ИЛИ, чтобы проверить красный ИЛИ синий И маленький:

 =ЕСЛИ(И(ИЛИ(B4="красный",B4="синий"),C4="маленький"),"x","")

Все это можно было бы сделать с помощью вложенных ЕСЛИ, но формула быстро усложнилась бы.

Видео: ЕСЛИ это ИЛИ то

14. Замените вложенные ЕСЛИ на ВПР

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

 =ЕСЛИ(E3="красный",100,ЕСЛИ(E3="синий",200,ЕСЛИ(E3="зеленый",300,ЕСЛИ(E3="оранжевый",400 500))))

Мы можем легко заменить его на этот (гораздо проще) ВПР:

 =ВПР(E3,B3:C7,2,0)

В качестве бонуса функция ВПР сохраняет значения на листе (где их можно легко изменить), а не встраивает их в формулу.

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

См. также: 23 вещи, которые нужно знать о ВПР

Видео: Как использовать ВПР

Видео: Почему ВПР лучше, чем вложенные ЕСЛИ

15. Выберите ВЫБРАТЬ

Функция ВЫБОР может предоставить элегантное решение, когда вам нужно сопоставить простые последовательные числа (1,2,3 и т. д.) с произвольными значениями.

В приведенном ниже примере ВЫБОР используется для создания пользовательских сокращений дней недели:

Конечно, вы могли бы использовать длинный и сложный вложенный ЕСЛИ, чтобы сделать то же самое, но, пожалуйста, не делайте этого 🙂

16. Используйте IFS вместо вложенных IF

Если вы используете Excel 2016 через Office 365, есть новая функция, которую вы можете использовать вместо вложенных IF: функция IFS. Функция IFS предоставляет специальную структуру для оценки нескольких условий без вложенности :

Приведенная выше формула выглядит так:

 =ЕСЛИ(D5<60,"F",D5<70,"D",D5<80,"C",D5<90,"B",D5>=90,"A")

Обратите внимание иметь только одну пару скобок!

Что происходит, когда вы открываете электронную таблицу, использующую функцию IFS, в более старой версии Excel? В Excel 2013 и 2010 (и я верю в Excel 2007, но не могу проверить) вы увидите «_xlfn». присоединяется к IFS в ячейке. Ранее рассчитанное значение останется, но если что-то заставит формулу пересчитаться, вы увидите ошибку #NAME. У Microsoft есть дополнительная информация здесь.

17. Max out

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

Функция МАКС дает вам умный способ сделать это без использования IF где-либо в поле зрения:

 =MAX(вычисление,0)

Этот метод возвращает результат вычисления, если он положительный, и ноль в противном случае.

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

18. Перехват ошибок с помощью IFERROR

Классическое использование оператора IF заключается в перехвате ошибок и предоставлении другого результата при возникновении ошибки, например:

 =ЕСЛИ(ЕОШИБКА(формула),результат_ошибки,формула)

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

В Excel 2007 была представлена функция ЕСЛИОШИБКА, которая позволяет более элегантно перехватывать ошибки:

 =ЕСЛИОШИБКА(формула,результат_ошибки)

Теперь, когда формула выдает ошибку, ЕСЛИОШИБКА просто возвращает введенное вами значение.

19. Используйте логическую логику

Иногда вы также можете избежать вложенных IF, используя так называемую "логическую логику". Слово boolean относится к значениям TRUE/FALSE. Хотя Excel отображает слова ИСТИНА и ЛОЖЬ в ячейках, внутри Excel воспринимает ИСТИНА как 1, а ЛОЖЬ – как ноль. Вы можете использовать этот факт для написания умных и очень быстрых формул. Например, в приведенном выше примере с функцией ВПР у нас есть вложенная формула ЕСЛИ, которая выглядит следующим образом:

 =ЕСЛИ(E3="красный",100,ЕСЛИ(E3="синий",200,ЕСЛИ(E3="зеленый",300,ЕСЛИ(E3="оранжевый",400 500))))

Использование логического значения логики, можно переписать формулу так:

 =(E3="красный")*100+(E3="синий")*200+(E3="зеленый")*300+(E3="оранжевый")*400+(E3="фиолетовый")* 500

Каждое выражение выполняет тест, а затем умножает результат теста на «значение, если оно истинно». Поскольку тесты возвращают либо ИСТИНА, либо ЛОЖЬ (1 или 0), результаты ЛОЖЬ фактически нейтрализуют формулу.

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

Видео: как использовать логическую логику в формулах Excel

Когда вам нужен вложенный ЕСЛИ?

Со всеми этими возможностями избежать вложенных ЕСЛИ вы можете задаться вопросом, когда имеет смысл использовать вложенный ЕСЛИ?

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

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

В этом случае , вложенный ЕСЛИ - прекрасное решение.