У меня есть таблица в 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(И ЕСЛИ (ЕСЛИ я могу придумать). Я относительно новичок в использовании формул массива, поэтому любая помощь будет принята с благодарностью. Я открыт для использования другой формулы, если есть лучшая, но не рекомендую переформатировать таблицу данных для предварительной сортировки значений.