Excel编写公式实现空值/N/A列权重按原始比例重分配

基础规则说明 #

• 数据范围：AD至AI列为临界等级评级列，单元格取值仅为1/2/3/4/5/N/A/空值；AJ列存储无权重调整的计算结果，AK列存储权重重平衡后的临界等级结果
• 原始权重：AD-AI六列对应权重依次为30%、20%、20%、10%、15%、5%，权重值存储在第729行的对应列，共需处理727行数据
• 核心计算逻辑：每列评级值乘以对应列权重后求和，得到最终临界等级

已实现：空值/N/A列权重平均分配 #

规则：存在N/A或空值的列，其对应权重平均拆分给所有存有效评级（1-5）的列。

计算示例（首行数据）：
该行评级为5、空值、2、1、1、5，对应原始权重为30%、20%、20%、10%、15%、5%

• 无调整结果（AJ列）：(5*30%) + (空值*20%) + (2*20%) + (1*10%) + (1*15%) + (5*5%) = 2.4
• 平均分配权重后：空值列对应权重共20%，剩余5列有效评级，每列有效列权重额外增加4%，最终计算结果为(5*34%) + (空值*0%) + (2*24%) + (1*14%) + (1*19%) + (5*9%) = 2.96，存入AK列
  已实现公式如下：

=LET(total,SUMIF(AD2:AI2,"N/A",$AD$729:$AI$729)+SUMIF(AD2:AI2,"",$AD$729:$AI$729),count,COUNTIFS(AD2:AI2,">=1",AD2:AI2,"<=5"),SUM(IFERROR((AD$729:AI$729+total/count)*AD2:AI2,0)))

待实现：空值/N/A列权重按原始权重占比分配 #

规则：存在N/A或空值的列，其对应权重按剩余有效列的原始权重占比拆分分配，保证重分配后有效列之间的权重相对比例与原始比例完全一致，公式需兼容最多6列全为空值/N/A的边界场景。

计算示例（同首行数据）：
空值列对应待分配权重为20%，剩余有效列原始权重总和为80%，按比例放大1.25倍后，最终新权重为37.5%、0%、25%、12.5%、18.75%、6.25%，满足有效列权重相对比例和原始一致的要求。

对应实现公式 #

直接在AK2单元格输入以下公式，下拉填充即可适配所有行：

=LET(
    rating_rng,AD2:AI2,
    weight_rng,$AD$729:$AI$729,
    valid_col,--ISNUMBER(MATCH(rating_rng,{1,2,3,4,5},0)),
    wait_alloc_w,SUM(weight_rng*(1-valid_col)),
    valid_origin_w_total,SUM(weight_rng*valid_col),
    new_weight,IF(valid_col,weight_rng*(1+wait_alloc_w/valid_origin_w_total),0),
    SUM(IFERROR(new_weight*rating_rng,0))
)

公式逻辑说明：

1. 逐列判断评级值是否为1-5的有效值，标记有效/无效列
2. 汇总所有无效列的原始权重，得到待分配的总权重
3. 汇总所有有效列的原始权重总和，计算权重等比放大系数
4. 有效列权重按系数放大，无效列权重置为0，最后加权求和得到结果，全空场景自动返回0不会报错

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