求Google Sheets权重匹配计分公式（审计与运营对齐场景）

场景说明 #

现有「Alignment form responses」表格，包含4个工作表：

• Sheet1：存储审计（auditor）和运营（ops）的表单回复（子参数多选结果）
• Sheet2：仅存储审计人员的回复
• Comparison：用于计算审计与运营的对齐分数
• Parameters：存储子参数名称及对应权重

核心规则：当运营选择的子参数是审计所选子参数的子集时，两者的对齐分数相同，分数为「运营匹配子参数的权重总和 ÷ 审计所选子参数的总权重」。

公式实现（以Comparison工作表为例） #

假设表格列对应关系：

• Sheet2的B列 = 审计人员所选子参数（多选结果用, 分隔）
• Sheet1的C列 = 运营人员所选子参数（多选结果用, 分隔）
• Parameters的A列 = 子参数名称，B列 = 子参数权重
• Comparison表从第2行开始计算数据

1. 计算审计所选子参数总权重（D2单元格） #

=SUMPRODUCT(IFERROR(VLOOKUP(SPLIT(Sheet2!B2, ", "), Parameters!A:B, 2, FALSE), 0))

逻辑：拆分审计的子参数列表，逐个匹配权重后求和，找不到的子参数权重计为0。

2. 计算运营与审计匹配的子参数权重总和（E2单元格） #

=SUMPRODUCT(IFERROR(VLOOKUP(FILTER(SPLIT(Sheet1!C2, ", "), ISNUMBER(MATCH(SPLIT(Sheet1!C2, ", "), SPLIT(Sheet2!B2, ", "), 0))), Parameters!A:B, 2, FALSE), 0))

逻辑：先拆分运营的子参数列表，筛选出和审计重叠的子参数，再匹配权重求和。

3. 计算对齐分数（F2和G2单元格，分别对应审计、运营分数） #

=IF(D2=0, 0, E2/D2)

逻辑：用匹配权重除以审计总权重得到比例，避免除0错误；设置单元格格式为「百分比」即可显示为百分比形式。

批量应用 #

选中D2:G2，下拉填充至所有数据行，完成批量计算。

示例验证 #

对应你给出的示例：

• 审计子参数：子参数1（权重100）、子参数2（权重50）→ 审计总权重=150
• 运营子参数：子参数2 → 匹配权重=50
• 分数=50/150≈33.3%（若示例中权重为各子参数占比50，则总权重100，分数为50%，公式同样适用）

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