Вложенные IF и IFS в Excel: порядок, который незаметно меняет метки ваших данных

1. ЕСЛИ и сколько на самом деле стоит вложенность

IF(test, value_if_true, value_if_false). Одно испытание, два результата. Для трех исходов требуется второй ЕСЛИ, помещенный в ложный слот первого.

=IF(A2>=800, "Large", IF(A2>=500, "Medium", "Small"))

Запустите 880, который вернется Large, что верно. Читается плохо и работает хорошо, а для трёх полос вполне нормально.

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

Здесь скрывается настоящее различие, о котором люди спотыкаются по отдельности: IF помечает одну строку за раз и строит столбец, в то время как COUNTIF и его семейство прочесывает каждую строку и возвращает одно число. Семейный путеводитель IF это страница этой разницы; речь идет о том, что делать, когда для одной строки требуется более двух возможных меток.

2. IFS: та же логика, плоская

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

=IFS(A2>=800, "Large",
     A2>=500, "Medium",
     TRUE,     "Small")

Запустите 880, который вернется Large, тот же ответ, что и во вложенной версии. Каждая пара находится на одном уровне, поэтому четвертая полоса — это еще одна пара, а не еще один слой, и скобки не накапливаются.

Прочтите его сверху вниз как набор правил, и там написано, что он делает: если оно меньше 800, то это Большой, в противном случае, если оно меньше 500, это Средний, в противном случае — Малый. Эта читабельность — единственная причина, по которой я предпочитаю его. Логика идентична.

3. Ловушка ордера с числом ее перемещения

Перед пояснением: вот те же два условия с поменянными местами парами. Значение равно 880, что удовлетворяет обоим требованиям. Скажите, что получится.

=IFS(880>=500, "Medium", 880>=800, "Large")

Medium

Medium. IFS остановился на первом условии, которое было истинным, и никогда не рассматривал второе. Это не выбор ближайшего диапазона или самого конкретного правила; он идет вниз по списку и принимает первый удар.

В таблице из шестнадцати строк эта замена делает следующее:

                        correct order        swapped
Small                   6 orders   2,305     6 orders   2,305
Medium                  6 orders   3,875    10 orders   7,585
Large                   4 orders   3,710     0 orders       0

Каждый крупный заказ становился средним. Четыре заказа и 3710 доходов изменили категорию, общая сумма по-прежнему составляет 9890, ни в одной ячейке не отображается ошибка, а строка «Большая» просто не отображается ни в одной сводке, потому что в ней ничего нет. Пустую полосу гораздо легче не заметить, чем неправильный номер.

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

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

4. У IFS нет ЕЩЕ

До пояснения: значение 240 не соответствует ни тому, ни другому. >=800 ни >=500. Скажите, что показывает ячейка.

=IFS(240>=800, "Large", 240>=500, "Medium")

#N/A

#N/A, потому что у IFS закончились условия, а истинное не найдено, и ему нечего возвращать. Это честное поведение, и это не то, что вы хотите видеть в отчете.

Все дело в последней паре, состояние которой буквально TRUE:

=IFS(240>=800, "Large", 240>=500, "Medium", TRUE, "Small")

Small

TRUE всегда истинно, поэтому последняя пара срабатывает всякий раз, когда ничего выше нее не произошло. Это версия финала от IFS. else, и его отсутствие — вторая по распространенности ошибка с этой функцией после упорядочивания.

Одно суждение, которое стоит вынести сознательно: что следует сказать в целом. "Small" утверждает, что каждое несовпадающее значение принадлежит нижней полосе, что подходит для дохода и неправильно для кода состояния, где неожиданным значением является новость. "check this" поскольку универсальное решение превращает формулу в собственный сигнал тревоги, и часто это лучший вариант по умолчанию, пока вы еще изучаете, что содержится в данных.

5. Границы намеренно

У каждой полосы есть два ребра, и кто-то должен решить, к какой стороне принадлежит каждое ребро. Формула принимает это решение независимо от того, думали вы об этом или нет, поэтому сверьте ее с фактическими значениями.

=IF(499>=800,"Large",IF(499>=500,"Medium","Small"))     Small
=IF(500>=800,"Large",IF(500>=500,"Medium","Small"))     Medium
=IF(800>=800,"Large",IF(800>=500,"Medium","Small"))     Large

499 — маленький, 500 — средний, 800 — большой. Вот что >= означает, и это соответствует тому, как люди описывают группы вслух: «500 и больше — это средний уровень». С использованием > вместо этого я бы поместил 500 в Малую полосу и в формуле это выглядело бы идентично.

На этих краях живут два провала. А зазор, где полоса заканчивается на 499, а следующая начинается на 501, поэтому ровно 500 ничего не соответствует и попадает в общий список. Ан перекрывать, где обе группы заявляют по 500, что невидимо, поскольку порядок автоматически разрешает эту проблему, точно так же, как в третьем разделе.

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

Где должна проходить граница — это отдельный и более сложный вопрос, чем то, как ее прописать. Порог, выбранный потому, что он круглый, — это решение, облаченное в костюм наблюдения, и выбор порогового значения из данных это страница для этого.

6. ПЕРЕКЛЮЧАТЕЛЬ для точного совпадения.

Когда каждая ветка "равна ли это тому", SWITCH говорит это более прямо. Вы указываете выражение один раз, затем пары значений и результатов и, при необходимости, значение по умолчанию в конце.

=SWITCH("Desk", "Desk","Furniture", "Chair","Furniture", "Lamp","Lighting", "other")
Furniture

=SWITCH("Sofa", "Desk","Furniture", "Chair","Furniture", "Lamp","Lighting", "other")
other

=SWITCH("Sofa", "Desk","Furniture", "Chair","Furniture")
#N/A

Последний аргумент является значением по умолчанию, и он распознается как значение по умолчанию, поскольку он автономен и не имеет партнера. Оставьте это значение, и вы получите непревзойденное значение. #N/A, то же самое, что и IFS, без универсального подхода.

SWITCH не может выполнять диапазоны, только равенство, и именно поэтому он безопаснее там, где он применяется: ничто не может соответствовать двум ветвям, поэтому ничего не зависит от порядка. Используйте SWITCH для кодов и категорий и IFS для всего, что связано с числом «больше».

7. Масштабируемая версия: таблица диапазонов

После примерно трех полос правильный ответ — перестать писать условия и поместить полосы в ячейки. Два столбца, отсортированные по возрастанию по нижней границе:

Floor    Label
    0    Small
  500    Medium
  800    Large

Затем выполняется один поиск с использованием приблизительного соответствия, который находит самый большой этаж, значение которого не превышает вашего значения.

=XLOOKUP(A2, Floors, Labels, "none", -1)

При использовании тех же значений он дает те же ответы:

499   Small
500   Medium
880   Large
1100  Large

Более старый эквивалент книги, которую нужно открыть где угодно, — это VLOOKUP с последним аргументом, оставленным как TRUE для той же отсортированной таблицы, которая вернула Medium для 680. Оба полагаются на сортировку таблицы по возрастанию, и это единственное, что нужно защищать.

Когда группы живут в клетках, меняются три вещи. Пороги становятся видимыми, поэтому любой может увидеть, что означает «большой», не открывая формулу. Их может редактировать кто-то кроме вас. А добавление четвертой полосы — это новая строка, а не редактирование формулы, а это означает, что изменение не может привести к ошибке упорядочения, поскольку порядок обрабатывается поиском.

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

8. Тестирование каждой ветки

Формула трех полос имеет четыре пути: три полосы и всеобъемлющий. Пятиполосная формула имеет шесть. Каждый из этих путей — это отдельная вещь, которая может быть неправильной, и ни один из них не используется до тех пор, пока по нему не упадет значение.

Поэтому протестируйте их намеренно, отложив небольшой блок значений сбоку:

value      expected     formula
  0        Small        =the formula on this value
499        Small
500        Medium
799        Medium
800        Large
9999       Large
""         catch-all

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

Затем проверьте итоговые суммы и этикетки. Подсчитайте по группам и суммируйте по группам и убедитесь, что значения добавляются к количеству строк, а суммы добавляются к общему количеству столбцов:

6 + 6 + 4 = 16 rows
2,305 + 3,875 + 3,710 = 9,890

Именно эта сверка и зафиксировала бы измененный порядок в третьем разделе, потому что Большая полоса вернулась бы с нулевыми строками против общей суммы, которая все еще сбалансирована.

Краевые случаи, нарушающие формулу группирования

Шесть, которые пройдут.

Пустые ячейки. Пробел рассматривается как 0, поэтому он попадает в нижнюю полосу, а не помечается. Сначала проверьте это: IFS(A2="", "missing", ...).

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

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

Несортированная таблица групп. Поиски с приблизительным совпадением предполагают возрастающий порядок и возвращают уверенный неправильный ответ, если вместо этого строки сортируются по меткам. Защитите сорт или запишите его рядом со столом.

Пороги скопированы в три разные формулы. Кто-то обновляет один из них. Теперь отчет и диаграмма не совпадают, и оба выглядят нормально. Одна таблица, на которую везде ссылаются, исключает такую ​​возможность.

Группы, которые изменились в прошлом квартале. Старые строки сохраняют свои старые метки, если метка была вставлена ​​как значение, а не оставлена ​​как формула, поэтому одинаковый доход составляет Средний в марте и Большой в июне. Сохраняйте метки в виде формул или записывайте, какая версия полос использовалась в моментальном снимке.