Excel найти корзину без вложенных IF

Question

Я должен разделить мои данные в категории, скажем, например:

Category    Minimum    Maximum
   A         -100        -20
   B         -20          0
   C           0         +20
   D         +20         +100

Таким образом, если одна точка данных равна -50, она относится к категории А.

Одним из решений является использование вложенных IF:

=IF(A1<-20,"A",IF(A1<0,"B",IF(A1<20,"C","D")))

Но когда количество категорий увеличивается, становится довольно грязно. Есть ли лучший способ добиться того же?

Answer 1

Предполагая, что у вас есть таблица с категориями в Y2:Y10 и минимумами в Z2:Z10 (с 20, а не +20 и похожими для всех положительных чисел), вы можете использовать формулу LOOKUP

=LOOKUP(A1,Y$2:Y$10,Z$2:Z$10)

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

Answer 2

Другим решением может быть использование именованных диапазонов.

Я хотел бы изменить ваш пример к этому:

Category    Minimum    Maximum
Cat_A         -100        -20
Cat_B         -20          0
Cat_C           0         +20
Cat_D         +20         +100

Затем отметьте данные от Cat_A до +100 и используйте Make from selection (На вкладке формулы , раздел Определенные имена) - используйте make из левого столбца.

Вы получите 4 именованных диапазона Cat_A, Cat_B, Cat_C и Cat_D, которые вы можете использовать в своей рабочей книге, например, для условного форматирования, не используя Min(Cat_A) и Max(Cat_A) качестве ограничений.

Тем не менее, это решение больше подходит для Барри Гудини.

Answer 3

Я использую этот трюк для равных данных. Предположим, у вас есть данные в диапазоне A1:A100 . Поместите эту формулу в B1:

=MAX(ROUNDUP(PERCENTRANK($A$1:$A$100,A1) *4,0),1)

Заполните формулу по всему столбцу B, и все готово. Формула делит диапазон на 4 равных сегмента и возвращает номер блока, в который входит ячейка A1. Первый сегмент содержит самые низкие 25% значений.

Отрегулируйте количество ведер в соответствии с вашим желанием:

=MAX(ROUNDUP(PERCENTRANK([Range],[OneCellOfTheRange]) *[NumberOfBuckets],0),1)

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