Excel и Google Workspace / Формулы Google Таблиц
IFS в Google Таблицах: несколько статусов без вложенных IF
IFS проверяет условия по порядку и возвращает результат для первого истинного условия. В Google Таблицах функция удобна для шкал KPI, рейтингов и статусов.
Формула
Строка проходит проверки сверху вниз и получает первый подходящий статус.
В IFS порядок условий определяет результат для пересекающихся порогов.
Обозначения
- $B2>=0.95$
- первый порог для зеленого статуса, доля выполнения плана
- $B2>=0.8$
- второй порог для желтого статуса, доля выполнения плана
- $TRUE$
- резервное условие для всех остальных строк
- Зеленый, Желтый, Красный
- результаты классификации
Условия применения
- Условия проверяются сверху вниз, поэтому более строгие и специфичные правила ставят выше.
- Каждое условие должно иметь парный результат, иначе формула будет собрана неверно.
- Последняя пара TRUE и результат по умолчанию защищает отчет от ошибки, если остальные условия не выполнились.
Ограничения
- IFS не заменяет справочник правил, если статусов много и они регулярно меняются.
- Пересекающиеся условия требуют аккуратного порядка; широкое условие выше узкого перехватит строку.
- Если процент введен как 95 вместо 95%, шкала будет работать неправильно, потому что 95 и 0,95 - разные значения.
Подробное объяснение
IFS превращает набор правил в одну формулу. Каждая пара говорит: если это условие истинно, верни этот результат. Google Таблицы проверяют пары сверху вниз и останавливаются на первом совпадении. Поэтому формула похожа на короткую таблицу решений, встроенную в ячейку.
Порядок условий особенно важен для шкал. Значение 97% одновременно удовлетворяет условиям больше 95% и больше 80%. Если сначала стоит 80%, строка никогда не дойдет до зеленого статуса. Поэтому правила нужно писать от более строгих к более мягким или от более специфичных к общим.
Пара TRUE, результат играет роль последней страховки. Она нужна не только для красоты, но и для устойчивости отчета. Если данные выходят за ожидаемый диапазон или не подходят ни под одно правило, пользователь получает понятную категорию, а не техническую ошибку. Иногда вместо Красный лучше писать Проверить данные, если неясно, корректна ли строка.
В Google Таблицах IFS удобно использовать в дашбордах, где статусы должны обновляться сразу после ввода данных. Но формула остается хорошей только пока шкала короткая. Если правила должны редактировать сотрудники без знания формул, более безопасно хранить пороги и подписи в отдельной таблице, а в отчете использовать поиск.
Как пользоваться формулой
- Составьте список статусов и порогов до написания формулы.
- Отсортируйте условия от самого строгого к более общему.
- Запишите пары условие и результат внутри IFS.
- Добавьте последнюю пару TRUE и значение по умолчанию.
- Проверьте значения на границах порогов: ровно 80%, 95% и рядом с ними.
Историческая справка
Множественные условия в таблицах появились из практики вложенных IF. Пользователи строили длинные цепочки условий, но такие записи было трудно читать, исправлять и проверять. IFS предложила более плоскую структуру: список условий и результатов. В Google Таблицах это особенно полезно для совместных отчетов, где формулу могут видеть люди с разным уровнем подготовки. Исторически IFS отражает развитие языка электронных таблиц от простых расчетов к описанию бизнес-правил. Это не новое математическое открытие, а улучшение формы записи, позволяющее надежнее классифицировать строки в живом облачном файле. Такой формат ближе к таблице правил, поэтому его легче обсуждать с аналитиками и менеджерами.
Историческая линия формулы
IFS не имеет персонального автора в смысле формулы. Она развивает идею условного выбора и последовательной проверки правил в электронных таблицах. Для Google Таблиц атрибуция связана с эволюцией формульных языков и потребностью делать вложенную логику читабельной.
Пример
В B2 хранится выполнение плана как доля: 0,87 означает 87%. Формула =IFS(B2>=0.95,"Зеленый",B2>=0.8,"Желтый",TRUE,"Красный") сначала проверяет 95%, затем 80%, затем все остальное. Для B2=0,87 результат Желтый. Для B2=0,96 результат Зеленый. Для B2=0,79 результат Красный. Если поставить условие B2>=0.8 первым, значение 0,96 ошибочно получит Желтый, потому что IFS остановится на первом истинном условии. Полезная проверка - подставить ровно 0,95 и 0,80: оба значения должны попасть в верхнюю допустимую категорию своего порога. Если в ячейке введено 87 вместо 87%, сначала нужно исправить формат или разделить значение на 100.
Частая ошибка
Главная ошибка - неправильный порядок порогов. При условиях больше или равно высокие пороги должны идти раньше низких. Вторая ошибка - не добавить TRUE в конце и получить ошибку для значений вне шкалы. Третья ошибка - смешать проценты и доли: 80% хранится как 0,8. Еще одна проблема - пытаться поддерживать длинную тарифную таблицу внутри IFS; если правила занимают несколько строк документации, лучше сделать отдельный справочник и искать статус по нему.
Практика
Задачи с решением
Сегмент по обороту
Условие. В 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")
Исправить порядок
Условие. Формула =IFS(B2>=0.8,"Желтый",B2>=0.95,"Зеленый",TRUE,"Красный") для 0,98 возвращает Желтый.
Решение. Условие 0,8 стоит раньше и перехватывает 0,98. Нужно поставить B2>=0.95 первым.
Ответ. =IFS(B2>=0.95,"Зеленый",B2>=0.8,"Желтый",TRUE,"Красный")
Дополнительные источники
- Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
- Microsoft Support: IFS function - https://support.microsoft.com/en-au/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45
- 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
IF в Google Таблицах: условие в ячейке
IF проверяет условие и возвращает один результат при истине и другой при лжи. В Google Таблицах функция нужна для статусов, проверок и простых бизнес-правил.
Excel и Google Workspace
FILTER в Google Таблицах: выбрать строки по условиям
FILTER возвращает строки или столбцы, которые соответствуют заданным условиям. В Google Таблицах это быстрый способ сделать живую выборку без ручного фильтра интерфейса.
Excel и Google Workspace
IFS / ЕСЛИМН для шкалы статусов и рейтингов
IFS проверяет несколько условий по порядку и возвращает результат для первого истинного условия. Функция удобна для шкал статусов, рейтингов, сегментов и пороговых правил.
Excel и Google Workspace
ARRAYFORMULA в Google Таблицах: формула на весь столбец
ARRAYFORMULA применяет расчет сразу к диапазону и возвращает массив результатов. В Google Таблицах она помогает не копировать формулу вручную по каждой строке.