☰

如何使用具有多个条件的条件数组来计算百分位数？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 array finance
如何使用具有多个条件的条件数组来计算百分位数？

我在 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(IF(贡献[贡献类型]="类型 1",贡献[贡献匹配百分比]),0.25)} (等适用于中等、平均和 75 分之一)

类型 2 匹配工资的有限百分比（Y），对员工的贡献没有限制：

{=PERCENTILE.EXC(IF(贡献[贡献类型]="类型 2",贡献[% 薪水]),0.25)}(等，针对中等、平均和 75% 的情况)

类型 3 对员工缴费和工资百分比都有限制：

{=PERCENTILE.EXC(IF(贡献[贡献类型]="类型 3",贡献[贡献匹配百分比]),0.25)} (等适用于中等、平均和 75 分之一)

{=PERCENTILE.EXC(IF(贡献[贡献类型]="类型 3",贡献[% 薪水]),0.25)} (等，针对中等、平均和 75% )

结果表计算四分位数包括空白和零值（不是我想要的）：

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|         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(AND IF(IF 的组合。我对使用数组公式还比较陌生，因此任何帮助都将不胜感激。如果有更好的公式，我愿意使用不同的公式，但不鼓励重新格式化数据表以对值进行预排序。

答案1

您可以应用此数组公式：

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

注意：

  1. 在单元格F3&中，G3您必须存储标准，这使得公式变得动态而不是硬核。
  2. 用 完成公式Ctrl+Shift+Enter。
  3. 根据需要调整公式中的单元格引用。

答案2

非常感谢拉杰什·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）），贡献[贡献匹配百分比]）），“N/A”）} {=IFERROR(MEDIAN(IF(((贡献[贡献类型]="类型 2")(贡献[贡献匹配百分比]<>0)),贡献[贡献匹配百分比])),"N/A")} {=IFERROR(MEDIAN(IF(((贡献[贡献类型]="类型 3")*(贡献[贡献匹配百分比]<>0)),贡献[贡献匹配百分比])),"N/A")}

要计算薪资百分比 - 第 25 位、中位数、平均值、第 75 位 - 将上述公式中的所有“贡献匹配百分比”替换为“薪资百分比”

相关内容