Excel合并单元格训练完成率平均值计算问题求助

原公式存在的问题 #

• 班级与科目数据分开过滤，无法同步排除空白科目的对应行，导致维度不匹配
• 训练3的完成率计算中误引用了训练1的列，逻辑错误
• 平均值计算为所有训练得分的整体平均，而非每个科目对应的三个训练得分的行级平均
• 输出时班级列与汇总后的科目列维度不匹配，导致列显示错误

修正后的公式 #

=LET(
    // 过滤掉科目为空的行，同步保留班级、科目及训练数据
    _filtered_data, FILTER('Content Tracker'!A3:G1048576, 'Content Tracker'!B3:B1048576<>""),
    _classes, INDEX(_filtered_data,,1),
    _subjects, INDEX(_filtered_data,,2),
    _trainings, INDEX(_filtered_data,,5):INDEX(_filtered_data,,7),
    // 定义状态转得分的通用逻辑
    _get_score, LAMBDA(status, IF(status="",0,IF(LEFT(status,1)="3",100,IF(LEFT(status,1)="2",50,0)))),
    // 获取唯一科目列表
    _u_subjects, UNIQUE(_subjects),
    // 按科目汇总各训练的总完成率
    _t1_total, MAP(_u_subjects, LAMBDA(subj, SUM(--(_subjects=subj)*_get_score(INDEX(_trainings,,1))))),
    _t2_total, MAP(_u_subjects, LAMBDA(subj, SUM(--(_subjects=subj)*_get_score(INDEX(_trainings,,2))))),
    _t3_total, MAP(_u_subjects, LAMBDA(subj, SUM(--(_subjects=subj)*_get_score(INDEX(_trainings,,3))))),
    // 计算每个科目三个训练的完成率平均值
    _avgs, BYROW(HSTACK(_t1_total,_t2_total,_t3_total), LAMBDA(row, AVERAGE(row))),
    // 匹配每个唯一科目对应的班级（取首次出现的班级）
    _u_classes, XLOOKUP(_u_subjects, _subjects, _classes),
    // 构建最终输出表
    _header, HSTACK("Classes","Subjects","Training 1 Completed","Training 2 Completed","Training 3 Completed","Average"),
    _output, VSTACK(_header, HSTACK(_u_classes, _u_subjects, _t1_total, _t2_total, _t3_total, _avgs)),
    _output
)

关键修改说明 #

1. 统一数据过滤：通过单次FILTER操作移除空白科目行，保证班级、科目、训练数据的同步性，避免维度错位
2. 通用得分逻辑：用_get_score函数封装状态转得分规则，替换重复代码，同时用LEFT精准判断状态开头字符，比SEARCH更高效
3. 修复训练3的逻辑错误：修正原公式中误引用训练1列的问题，确保训练3的得分计算正确
4. 行级平均值计算：使用BYROW实现每个科目对应的三个训练得分的平均值，符合需求中的“训练完成率平均值”逻辑
5. 班级与科目匹配：用XLOOKUP获取每个唯一科目对应的班级，解决原公式中Class列显示错误的问题

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