19 советов по работе с вложенными функциями ЕСЛИ
Функция ЕСЛИ (IF) является одной из наиболее часто используемых функций в Excel. ЕСЛИ — простая функция, и люди любят ее, потому что она дает им возможность заставить Excel реагировать, когда информация вводится в электронную таблицу. С ЕСЛИ, вы можете оживить вашу таблицу.
Но использование одной функции ЕСЛИ часто приводит к использованию второй, и как только вы объединяете более пары ЕСЛИ, ваши формулы могут начать выглядеть как маленькие Франкенштейны 🙂
Являются ли вложенные ЕСЛИ опасными? Всегда ли они необходимы? Какие есть альтернативы?
Читайте дальше, чтобы узнать ответы на эти вопросы и многое другое …
Как сделать функцию если то в excel?
Является ли результат теста в А1 действительно текстом, а не числом? Нет? Тогда не используйте кавычки с числом. В противном случае логический тест вернет ЛОЖЬ, даже если значение является проходным баллом, потому что «100» не совпадает с 100. Если тестовый балл является числовым, используйте вот такую формулу:
| Баллы | Оценка | Условие |
| 0 — 63 | Неуд. | < 64 |
| 64 — 72 | Удовл. | < 73 |
| 73 — 89 | Хорошо | < 90 |
| 90 — 100 | Отлично |
Как использовать функцию
Пример №1
На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем заданное условие А1>25. ЕСЛИ это требование выполняется, тогда выводим в ячейку «больше 25», иначе «меньше или равно 25».
Пример №2
Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ. Критерии у нас будут такими: кто набрал более 45 балов, тот считается сдавшим экзамен, ну а все остальные нет.
Функция Если в Excel
- Логическое_условие – значение, которое будем проверять
- Значение_в_случае_ИСТИНА – действие, когда требование выполняется
- Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется
— Интервальный просмотр — Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.
Разновидности функций
В пакетном приложении Excel имеется порядка 400 функций. Их разделили на соответствующие категории (указаны несколько примеров для ознакомления):
- финансовые: ПРОЦПЛАТ, ПУО, ПС, СТАВКА, ЧПС и т.д.;
- даты и времени – ВРЕМЯ, ДЕНЬНЕД, ГОД и т.д.;
- математические – ABS, ATAN, LOG, LN, SIN и т.д.;
- статистические – ДИСП, ГИПЕРГЕОМЕТ, КВАРТИЛЬ и т.д.;
- ссылок и массивов – АДРЕС, ВЫБОР, ОБЛАСТИ, ПОИСКПОЗ, ЧСТРОК и т.д.;
- баз данных – БДДИСП, БСЧЁТА, БДСУММ, ДСРЗНАЧ и т.д.;
- текстовые – ЗАМЕНИТЬ, НАЙТИ, ПОВТОР, ПОДСТАВИТЬ, ФИКСИРОВАННЫЙ и т.д.;
- логические – ЕСЛИ, ИСТИНА, НЕ, ЛОЖЬ и т.д.;
- проверки свойств и значений – ЕОШИБКА, ИНФОРМ, ЕЧИСЛО, ЯЧЕЙКА и т.д.
Как видно, функция «ЕСЛИ» входит в категорию логических функций. Их более детальное изучение пройдет немного позже.
Функция «Если» в Excel, примеры нескольких условий, дополнительные возможности функции
- #ИМЯ? – неверно введен адрес ячейки, на которую ссылается функция, либо имеется недочет в имени формулы.
- #ДЕЛ/0! – все из математики знают, что на «0» (ноль) делить нельзя. Эта ошибка напоминает об этом важном моменте.
- #ЧИСЛО! – в различных математических функциях имеется ряд ограничений, т.е. условий (отрицательное число под знаком корня или натурального логарифма). Нужно пересмотреть заданную функцию и исправить недочет.
- #ЗНАЧ! – возможно, пользователь ввел неверные параметры. К примеру, вместо диапазона клеток указаны ячейки методом перечисления.
- #ССЫЛКА! – функция не может сослаться на клетку, поскольку ее адрес неверен.
- ########## – это говорит о том, что результат вычислений не вмещается в заданной ширине столбца. Достаточно расширить его, и тогда ответ будет виден.
Функция ЕСЛИ (IF) является одной из наиболее часто используемых функций в Excel. ЕСЛИ — простая функция, и люди любят ее, потому что она дает им возможность заставить Excel реагировать, когда информация вводится в электронную таблицу. С ЕСЛИ, вы можете оживить вашу таблицу.
Вложенность
Одной из особенностей, которой отличается функция «ЕСЛИ» — это вложенность. То есть внутри одной конструкции, может находиться ещё одна, от значения которой зависит и общий результат выполнения запроса. Помимо самой функции, внутри «ЕСЛИ» могут находиться и другие. Но в первом случае данная составляющая может располагаться в любом из трёх частей синтаксической конструкции.
10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только
— Диапазон условия 1 — Диапазон ячеек, которые проверяются на соответствие определенному условию.
— Условие 1 — Условие, которое определяет какие ячейки надо учитывать при подсчете.
Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.
СУММЕСЛИМН
Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.
— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные
— Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию
— Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.
Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно.
Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.
Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».
Функция ЕСЛИ в Excel с примерами.
Вложенные ЕСЛИ имеют свою логику, поскольку «внешние» ЕСЛИ действуют как ворота к «внутренним» ЕСЛИ. Это означает, что результаты внешних ЕСЛИ определяют, работают ли внутренние ЕСЛИ. Диаграмма ниже визуализирует логический ход формулы расчета оценок выше.
Как использовать функцию
Пример №1
Пример №2
Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ. Критерии у нас будут такими: кто набрал более 45 балов, тот считается сдавшим экзамен, ну а все остальные нет.
Поиск нескольких вхождений элемента в списке
- Логическое_условие – значение, которое будем проверять
- Значение_в_случае_ИСТИНА – действие, когда требование выполняется
- Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется
Часто на практике одного условия для логической функции мало. Когда нужно учесть несколько вариантов принятия решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас получиться несколько функций ЕСЛИ в Excel.
Функции ЕСЛИ в EXCEL, примеры
Интересен вариант работы с формулой IF, когда используется не цифровые данные, а текст.
Текст в формуле нужно выделять кавычками для корректной отработки функции.
Всем инженерам 2 категории премия составляет 30%, остальным работникам – 50%. Для реализации этого условия вставляем формулу: =ЕСЛИ(ячейка_профессия=“инженер 2 категории”; “премия 30%”; “премия 50%”). Все инженеры 2 категории промаркируются данными по премии в 30%, остальным работникам будет введено значение «премия 50%».
Когда условия более сложные, то используют комбинации вложенных функций.
Применение вложенных IF
На предприятии при ведении табеля учёта рабочего времени возникает необходимость поставить месячную норму времени для расчёта зарплаты. У разных специалистов эта величина различается. С помощью функции IF можно легко решить эту задачу.
Пусть у дневного персонала месячная норма равна 162 часа, у сменного при 8-часовом рабочем дне она составит 148 часов, а при рабочем дне 7,2 часа норма будет равна 136 часов.
Тогда нужно проверить дневную норму работника (8 или 7,2 часа) и категорию работающего (смена или день). Реализовать такую проверку можно разными способами.
Когда в качестве первой проверки берём дневную норму и вторую проверку делаем по истине, то формула будет следующая:
Первую проверку оставляем прежней, вторую проверяем по лжи, то выражение будет выглядеть так:
Когда первую проверку делаем по категории, то имеем: =ЕСЛИ(ячейка_категория=“смена”;ЕСЛИ(ячейка_дневнаянорма =8;148;136);162)
Допустим, что есть ещё смена по 12 часов с нормой в 144 часа. Тогда для реализации такого алгоритма потребуется ещё одно вложение функции IF. Это уже третий уровень.
=ЕСЛИ(ячейка_категория=“смена”;ЕСЛИ(ячейка_дневнаянорма =8;148;ЕСЛИ(ячейка_дневнаянорма=12;144; 136));162)
Освоив принцип проверок, можно сделать сложные древовидные структуры. Хотя на практике более 4 уровней вложенности практически не применяется, так как более сложные комбинации можно реализовать проще через другие функции программы, работающие с выбором данных из массивов по критериям.
Одновременное применение двух условий
Функция IF с формулой И
Если требуется чтобы было выполнено одновременно несколько условий, то поможет конструкция И, имеющая синтаксис:
Она проверяет истинность каждой из логических структур и возвращает ИСТИНУ только при истинности всех проверяемых выражений.
Где это можно использовать? Например, необходимо выделить из общего списка работников только слесарей 6 разряда, имеющих стаж работы больше 10 лет, то поможет конструкция вида:
Эта формула выделит в общем списке нужную категорию работников по установленному качественному признаку =1.
Функция IF с формулой ИЛИ
Вариант использования функции ИЛИ с IF может быть следующий. Руководство предприятия в течение года поквартально поощряло работников. Необходимо выбрать всех, кто получал поквартальные премии больше 5000 р.
Конструкция ИЛИ имеет синтаксис аналогичный формуле И. Но ИСТИНА возвращается при условии истинности хотя бы одного логического выражения.
=ЕСЛИ(ИЛИ(ячейка_премия1квартал>=5000; ячейка_премия2квартал>=5000; ячейка_премия3квартал>=5000; ячейка_премия4квартал>=5000);1;0)
Последующая фильтрация данных по признаку 1 даст сделать нужный список трудящихся.
Таким образом, очевидна полезность функции IF. В новых версиях программы появляются улучшенные формулы, объединяющие наиболее востребованные старые приёмы и действия, что даёт неограниченные возможности для изучения и применения на практике программы эксель.
Как вставить функцию «ЕСЛИ»
«И», «ИЛИ» могут насчитывать в своей формуле до 30 условий. Однако необходимо помнить: длинные, тяжелые формулы очень трудно воспринимаются. Поэтому по возможности желательно их несколько облегчить за счет добавления столбцов или строк.
