基于国家维度的Excel数据集分级公式技术问询

需求说明 #

针对每个国家独立处理Rate列数据：

• 无绩效数据（Rate为空）→ Tier 4
• 该国Rate排名前20% → Tier 1
• 该国Rate排名在20%-50%区间（含50%）→ Tier 2
• 该国Rate排名后50% → Tier 3

适用Excel 365/2021的简化公式（推荐） #

假设：

• A列：国家（如A2:A100）
• B列：Rate值（如B2:B100）
• C列：输出Tier结果

在C2单元格输入以下公式，下拉填充即可：

=IF(ISBLANK(B2),"Tier 4",
  LET(
    countryRates, FILTER($B$2:$B$100,$A$2:$A$100=$A2),
    totalValid, COUNTA(countryRates),
    rankDesc, RANK.EQ(B2,countryRates,0),
    pctRank, rankDesc/totalValid,
    IF(pctRank<=0.2,"Tier 1",IF(pctRank<=0.5,"Tier 2","Tier 3"))
  )
)

公式拆解

1. ISBLANK(B2)：判断当前行是否无绩效数据，直接返回Tier 4
2. LET函数：定义变量简化逻辑，提升可读性
   • countryRates：过滤出当前国家的所有非空Rate值
   • totalValid：统计当前国家的有效数据条数
   • rankDesc：计算当前Rate在该国的降序排名（0代表降序）
   • pctRank：计算当前排名占该国总数据量的比例
3. 区间判断：根据占比返回对应Tier

兼容旧版Excel的公式（无LET函数） #

如果使用Excel 2019及更早版本，用以下公式（需按Ctrl+Shift+Enter触发数组计算）：

=IF(ISBLANK(B2),"Tier 4",
  IF(
    RANK.EQ(B2,IF($A$2:$A$100=$A2,$B$2:$B$100,""),0)/COUNTIFS($A$2:$A$100=$A2,$B$2:$B$100,"<>")<=0.2,
    "Tier 1",
    IF(
      RANK.EQ(B2,IF($A$2:$A$100=$A2,$B$2:$B$100,""),0)/COUNTIFS($A$2:$A$100=$A2,$B$2:$B$100,"<>")<=0.5,
      "Tier 2",
      "Tier 3"
    )
  )
)

处理并列值的替代方案（用百分位函数） #

如果需要更精准的百分位划分（避免并列排名导致的层级数量偏差），可以使用PERCENTRANK.INC函数：

=IF(ISBLANK(B2),"Tier 4",
  LET(
    countryRates, FILTER($B$2:$B$100,$A$2:$A$100=$A2),
    pct, PERCENTRANK.INC(countryRates,B2),
    IF(pct>=0.8,"Tier 1",IF(pct>=0.5,"Tier 2","Tier 3"))
  )
)

注：PERCENTRANK.INC返回0-1的数值，代表当前值在数据集中的从小到大百分位，因此Top20%对应pct>=0.8，20%-50%区间对应0.5<=pct<0.8。

加拿大手动分级示例验证 #

假设加拿大有10条有效Rate数据：95,90,85,80,75,70,65,60,55,50

• 前2条（95、90）：排名1、2，占比≤20% → Tier 1
• 中间3条（85、80、75）：排名3、4、5，占比≤50% → Tier 2
• 最后5条（70、65、60、55、50）：排名6-10，占比>50% → Tier 3
• 空值行直接返回Tier 4

内容的提问来源于stack exchange，提问作者stayschemin