Как использовать условный массив с более чем одним условием для вычисления процентилей?

340
SuziLmrdo

У меня есть таблица в Excel со следующими данными: 

+-------------------+----------------------+----------+ | Contribution Type | % Contribution Match | % Salary | +-------------------+----------------------+----------+ | Type 1 | 0.5 | | | Type 1 | 0.6 | | | Type 1 | | | | Type 2 | | 0.03 | | Type 2 | | 0.04 | | Type 2 | | 0 | | Type 3 | 0.7 | 0.05 | | Type 3 | 0.6 | 0.04 | | Type 3 | | 0.05 | | Type 1 | 0.5 | | | Type 2 | | 0.04 | | Type 3 | 0.75 | 0.1 | +-------------------+----------------------+----------+

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

Тип 1 соответствует 100% заработной платы до определенного процента (X) вклада работников:

{= PERCENTILE.EXC (ЕСЛИ (Взносы [Тип взноса] = «Тип 1», Взносы [% соответствия взносов]), 0,25)} (и т. Д. Для med, avg и 75th)

Тип 2 соответствует ограниченному проценту (Y) заработной платы без ограничения вклада работника:

{= PERCENTILE.EXC (IF (Взносы [Тип взноса] = «Тип 2», Взносы [% зарплата]), 0,25)} (и т. Д. Для med, avg и 75th)

Тип 3 имеет ограничения как на вклад сотрудника, так и на процент заработной платы:

{= PERCENTILE.EXC (ЕСЛИ (взносы [тип взноса] = «тип 3», взносы [% соответствия взносов]), 0,25)} (и т. Д. Для med, avg и 75-й)

{= PERCENTILE.EXC (IF (Взносы [Тип взноса] = «Тип 3», Взносы [% зарплата]), 0,25)} (и т. Д. Для med, avg и 75th)

Полученная таблица вычисляет квартили, включая пустые и нулевые значения (не то, что я хочу):

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+ | Percentage of Employee Contribution | Percentage of Salary | +--------+-----------+--------+---------+------------+-----------+--------+---------+------------+ | | 25th %-ile| Median | Average | 75th %-ile | 25th %-ile| Median | Average | 75th %-ile | | Type 1 | 12.50% | 50.00% | 40.00% | 57.50% | 0.00% | 0.00% | 0.00% | 0.00% | | Type 2 | 0.00% | 0.00% | 0.00% | 0.00% | 0.75% | 3.50% | 2.75% | 4.00% | | Type 3 | 15.00% | 65.00% | 51.25% | 73.75% | 4.25% | 5.00% | 6.00% | 8.75% | +--------+-----------+--------+---------+------------+-----------+--------+---------+------------+

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

0

2 ответа на вопрос

0
Rajesh S

Вы можете применить эту формулу массива:

{=PERCENTILE(IF((($A$2:$A$100=$F$3)*($B$2:$B$100=$G$3)),$C$2:$C$100),0.5)}

NB

  1. В Cell F3& G3вы должны хранить Критерий, который делает Formula Dynamic, а не Hard Core.
  2. Готово Формула с Ctrl+Shift+Enter.
  3. При необходимости измените ссылки на ячейки в формуле.
Thank you so much! This worked perfectly! I wasn't sure how to store the criterion "<>0" in a separate cell, so I ended up hard coding them into the formulas. Do you know if there is, in fact, a way to store "not equal to zero" dynamically? SuziLmrdo 6 лет назад 0
@SuziLmrdo, glad to help you ,,, put any value is not ZERO is the cell and use the Cell address <>$A$3. Keep asking ☺ Rajesh S 6 лет назад 0
0
SuziLmrdo

Большое спасибо Rajesh S за помощь в моем вопросе! Вот формулы, которые я придумал, основываясь на его совете:

Процент вклада сотрудника - 25-й процентиль (замените 0,25 на 0,75 для расчета 75-го процентиля)

{= IFERROR (PERCENTILE.EXC (IF (((Взносы [Тип взноса] = «Тип 1») (Взносы [% соответствия взносов] <> 0)), Взносы [% соответствия взносов]), 0,25), «N / A ")} {= IFERROR (PERCENTILE.EXC (IF (((Взносы [Тип взноса] =" Тип 2 ") (Взносы [Соответствие взносов% <> 0)), Взносы [Соответствие взносов%]), 0,25), "N / A")} {= IFERROR (PERCENTILE.EXC (IF (((Взносы [Тип взноса] = "Тип 3") * (Взносы [% соответствия взносов] <> 0)), Взносы [% соответствия взносов ]), 0,25), "N / A")}

Процент вклада сотрудника - медиана (замените MEDIAN на AVERAGE для расчета средних значений)

{= IFERROR (MEDIAN (IF (((Вклады [Тип взноса] = «Тип 1») (Вклады [Соответствие взносов%] <> 0)), Взносы [Соответствие взносов%))), «Н / Д»)} {= IFERROR (MEDIAN (IF (((Вклады [Тип взноса] = "Тип 2") (Вклады [Соответствие взносов%] <> 0)), Взносы [Соответствие взносов%))), "Н / Д")} {= IFERROR (MEDIAN (IF (((Вклады [Тип вклада] = «Тип 3») * (Вклады [Соответствие вклада%] <> 0)), Вклады [Соответствие вклада%))), «Нет данных») }

Чтобы рассчитать Процент зарплаты - 25-е, Медиана, Среднее, 75-е - замените все случаи «Соответствия вклада%» в вышеприведенных формулах на «% Зарплаты»

Похожие вопросы