Excel技术问询：按四分位筛选男性员工计算薪酬均值与中位数
2026-6-24
解决Excel中男性员工分四分位计算薪酬均值/中位数的问题
针对你提到的「直接引用四分位数值导致相同薪酬被拆分到不同四分位」的统计误差问题，推荐使用基于排名分组的方法，确保相同薪酬值的员工被归为同一四分位，具体实现如下：
前提说明
假设数据范围：
- B列为员工性别（值为
male/female） - C列为总薪酬
- 数据从第2行开始（表头在第1行）
步骤1：计算男性员工总人数
先获取男性员工的总数，方便后续划分四分位范围：
=COUNTIF(B:B,"male")
可以将这个单元格命名为MaleCount（方便后续公式引用，可选）。
步骤2：计算男性员工薪酬的排名（降序）
在D2单元格输入以下公式，下拉填充至所有行，得到每个男性员工的薪酬在男性群体中的降序排名（相同薪酬会获得相同排名）：
=IF(B2="male",RANK.EQ(C2,FILTER(C:C,B:C="male"),0),"")
- 若使用旧版Excel（无
FILTER函数），替换为数组公式（输入后按Ctrl+Shift+Enter）：=IF(B2="male",RANK.EQ(C2,IF(B:B="male",C:C),0),"")
步骤3：按四分位分组计算均值和中位数
基于排名范围划分四分位（Q1=最低25%，Q4=最高25%），使用AVERAGEIFS/MEDIANIFS（新版Excel）或数组公式（旧版）计算：
新版Excel（支持FILTER/MEDIANIFS）
| 四分位 | 均值公式 | 中位数公式 |
|---|---|---|
| Q1（最低25%） | =AVERAGEIFS(C:C,B:C,"male",D:D,">"&0.75*MaleCount) | =MEDIANIFS(C:C,B:C,"male",D:D,">"&0.75*MaleCount) |
| Q2（次低25%） | =AVERAGEIFS(C:C,B:C,"male",D:D,">"&0.5*MaleCount,D:D,"<="&0.75*MaleCount) | =MEDIANIFS(C:C,B:C,"male",D:D,">"&0.5*MaleCount,D:D,"<="&0.75*MaleCount) |
| Q3（次高25%） | =AVERAGEIFS(C:C,B:C,"male",D:D,">"&0.25*MaleCount,D:D,"<="&0.5*MaleCount) | =MEDIANIFS(C:C,B:C,"male",D:D,">"&0.25*MaleCount,D:D,"<="&0.5*MaleCount) |
| Q4（最高25%） | =AVERAGEIFS(C:C,B:C,"male",D:D,"<="&0.25*MaleCount) | =MEDIANIFS(C:C,B:C,"male",D:D,"<="&0.25*MaleCount) |
旧版Excel（无MEDIANIFS）
使用数组公式（输入后按Ctrl+Shift+Enter）：
- Q1均值：
=AVERAGE(IF((B:B="male")*(RANK.EQ(C:C,IF(B:B="male",C:C),0)>0.75*COUNTIF(B:B,"male")),C:C)) - Q1中位数：
=MEDIAN(IF((B:B="male")*(RANK.EQ(C:C,IF(B:B="male",C:C),0)>0.75*COUNTIF(B:B,"male")),C:C))
其他四分位的公式只需调整排名范围的阈值（0.5、0.25）即可，逻辑和Q1一致。
为什么这个方法能避免误差？
直接用QUARTILE返回的数值筛选时，若相邻四分位边界存在大量相同薪酬值，会导致某一四分位的人数远超25%，破坏分组的合理性。而基于排名分组的方式，会将相同薪酬的员工赋予相同排名，确保他们被归为同一四分位，分组结果更符合统计逻辑。
内容的提问来源于stack exchange，提问作者jenc
相关产品推荐
相关产品推荐
