Excel и Google Workspace / IF, IFS
IFS / ЕСЛИМН для шкалы статусов и рейтингов
IFS проверяет несколько условий по порядку и возвращает результат для первого истинного условия. Функция удобна для шкал статусов, рейтингов, сегментов и пороговых правил.
Формула
Обозначения
- $B2>=0.95$
- первое, самое строгое условие для зеленого статуса, доля выполнения
- $B2>=0.8$
- следующий порог для желтого статуса, доля выполнения
- $TRUE$
- резервное условие, которое срабатывает для всех оставшихся случаев
- $IFS / ЕСЛИМН$
- функция множественного выбора по условиям
Условия применения
- Каждое условие должно иметь соответствующий результат, иначе структура пар нарушится.
- Пересекающиеся пороги записывают от самого строгого к более мягкому, чтобы первое подходящее правило не перехватило строку ошибочно.
- Для результата по умолчанию в конце удобно ставить TRUE и нужный текст или число.
Ограничения
- IFS может быть недоступна в старых версиях Excel, где придется использовать вложенный IF или справочник с поиском.
- Если нет последней пары TRUE и ни одно условие не выполнилось, пользователь получит ошибку вместо статуса.
- Для длинных и часто меняющихся шкал лучше использовать отдельную таблицу правил, потому что формулу с десятками порогов трудно сопровождать.
Подробное объяснение
IFS полезна, когда результат выбирается не между двумя вариантами, а по шкале. Внутри формулы записываются пары: условие, результат, затем следующее условие и следующий результат. Таблица проверяет их сверху вниз и возвращает значение из первой пары, где условие истинно. Это делает формулу похожей на компактную таблицу бизнес-правил.
Порядок условий является частью смысла. Для порогов с больше или равно высокий порог должен идти выше низкого, потому что значение 97% одновременно удовлетворяет условиям >=95% и >=80%. Если поставить широкое условие первым, оно заберет строки, которые должны попасть в более точный статус. Поэтому шкалу полезно тестировать на значениях ниже, выше и ровно на границах.
Последняя пара TRUE, результат выполняет роль else. TRUE всегда истинно, но до него формула дойдет только тогда, когда все предыдущие проверки оказались ложными. Такой прием предотвращает неожиданную ошибку и делает отчет аккуратнее: пользователь видит Красный, Не классифицировано или Проверить данные вместо технического сообщения.
IFS не заменяет справочник правил. Если у компании десять сегментов клиентов и пороги меняются каждый квартал, формулу трудно редактировать безопасно. В этом случае лучше хранить правила в отдельной таблице и подбирать результат функцией поиска. Но для коротких статусов KPI, оценок качества и простых уровней риска IFS обычно быстрее, прозрачнее и устойчивее, чем вложенные IF.
Как пользоваться формулой
- Выпишите все статусы и пороги в отдельном черновике или рядом с таблицей.
- Поставьте более строгие и специфичные условия выше более общих.
- Запишите пары условие и результат внутри IFS без пропусков.
- Добавьте последнюю пару TRUE и результат для всех неохваченных случаев.
- Проверьте несколько строк на границах порогов и около них.
Историческая справка
Множественные условия в электронных таблицах выросли из практики вложенных IF. Пользователи давно строили цепочки вида если одно, иначе если другое, иначе если третье, но такие формулы становились длинными и плохо проверяемыми. Функция IFS появилась как более читаемая запись той же идеи: вместо вложенности пользователь видит последовательность правил. Это особенно совпало с потребностями офисной аналитики, где данные нужно классифицировать по уровням, статусам и порогам. В Google Таблицах и современных версиях Excel функция стала привычным мостом между простой проверкой IF и полноценной таблицей правил. Ее история отражает не открытие новой математики, а развитие удобного языка формул для людей, которые описывают бизнес-логику в ячейках.
Историческая линия формулы
IFS не имеет единственного автора как математическая формула. Это продуктовая функция электронных таблиц, основанная на общей идее последовательной проверки условий. Атрибуция должна идти к развитию офисных табличных систем и потребности заменить громоздкие вложенные IF.
Пример
В B2 хранится доля выполнения плана: 0,97 означает 97%. Нужно присвоить статус KPI. Формула =IFS(B2>=0.95,"Зеленый",B2>=0.8,"Желтый",TRUE,"Красный") сначала проверяет порог 95%, затем 80%, затем все остальное. Для B2=0,97 результат будет Зеленый. Для B2=0,86 первое условие ложно, второе истинно, значит результат Желтый. Для B2=0,72 первые два условия ложны, а TRUE срабатывает как вариант по умолчанию и возвращает Красный. Проверка границ важна: значение ровно 0,95 должно попадать в Зеленый, если правило написано через >=.
Частая ошибка
Главная ошибка - неправильный порядок условий. Если сначала поставить B2>=0.8, то значение 0,97 получит Желтый, потому что формула остановится на первом истинном условии и не дойдет до более высокого порога. Вторая ошибка - забыть последнюю пару TRUE, из-за чего редкое значение превращается в ошибку. Третья ошибка - смешать проценты и доли: 95% хранится как 0,95, а число 95 означает 9500%. Еще одна проблема - использовать IFS там, где правила зависят от справочника и регулярно меняются; в таком случае лучше таблица порогов и lookup.
Практика
Задачи с решением
Рейтинг клиента по обороту
Условие. В A2 годовой оборот клиента. Нужно: от 1000000 - A, от 300000 - B, иначе C.
Решение. Порог 1000000 должен идти первым, затем 300000, затем вариант по умолчанию. Формула: =IFS(A2>=1000000,"A",A2>=300000,"B",TRUE,"C").
Ответ. =IFS(A2>=1000000,"A",A2>=300000,"B",TRUE,"C")
Исправить шкалу KPI
Условие. Формула =IFS(B2>=0.8,"Желтый",B2>=0.95,"Зеленый",TRUE,"Красный") для B2=0,98 возвращает Желтый. Почему?
Решение. Условие >=0.8 стоит раньше и уже истинно для 0,98. Нужно поставить более строгий порог >=0.95 первым.
Ответ. =IFS(B2>=0.95,"Зеленый",B2>=0.8,"Желтый",TRUE,"Красный")
Дополнительные источники
- Microsoft Support: IFS function - https://support.microsoft.com/en-au/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45
- Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
- Microsoft Support: Excel functions by category - https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
Связанные формулы
Excel и Google Workspace
Несколько условий IFS / ЕСЛИМН
IFS проверяет несколько условий по порядку и возвращает результат для первого выполненного условия. В русской локализации Excel функция обычно называется ЕСЛИМН.
Excel и Google Workspace
IF / ЕСЛИ для двух вариантов результата в отчете
IF проверяет одно логическое условие и возвращает один результат, если условие истинно, и другой результат, если оно ложно. В русской локализации Excel функция называется ЕСЛИ.
Excel и Google Workspace
SWITCH / ПЕРЕКЛЮЧ для фиксированных категорий
SWITCH сравнивает одно выражение с набором фиксированных значений и возвращает результат для найденного совпадения. Это удобно для кодов, статусов и коротких справочников.
Excel и Google Workspace
IFERROR / ЕСЛИОШИБКА для понятного сообщения
IFERROR возвращает обычный результат формулы, если ошибки нет, и заданное сообщение или значение, если расчет завершился ошибкой. В Excel функция называется ЕСЛИОШИБКА.