如何在Google Sheets/Excel中映射逗号分隔任务列表并计算工作积分？

这个需求完全可以在Google Sheets和Excel中实现，以下是针对两个平台的具体解决方法：

Google Sheets 操作步骤 #

假设Sheet A的结构为：

• A列：Work Points（需要填充的积分总和）
• B列：Work Categories（逗号分隔的任务列表）

Sheet B的结构为：

• A列：Task（任务名称）
• B列：Grade（任务难度等级）
• C列：Points（任务对应积分）

1. 计算Work Points总积分 #

在Sheet A的A2单元格输入以下公式，然后下拉填充到所有行：

=SUMPRODUCT(XLOOKUP(TRIM(SPLIT(B2, ",")), SheetB!$A:$A, SheetB!$C:$C, 0, 0))

• SPLIT(B2, ",")：把B列里的逗号分隔任务拆成单个任务的数组，不管任务数量多少都能处理
• TRIM()：去掉每个任务前后的空格，避免因为空格导致匹配不到Sheet B的任务
• XLOOKUP()：在Sheet B的Task列找对应的任务，返回它的Points值；没匹配到的任务按0计算
• SUMPRODUCT()：把所有匹配到的Points加起来，得到该行的总积分

2. 关联显示任务对应的Grade（可选） #

如果需要展示每个任务的难度等级，可以在Sheet A新增一列（比如C列），在C2输入公式后下拉：

=TEXTJOIN(", ", TRUE, XLOOKUP(TRIM(SPLIT(B2, ",")), SheetB!$A:$A, SheetB!$B:$B, "未匹配", 0))

• TEXTJOIN()：把多个Grade用逗号加空格拼接成一个字符串
• "未匹配"：针对找不到对应Task的条目显示的内容，不需要可以改成空字符串""

Excel 操作步骤 #

适用于Excel 365/2021（支持TEXTSPLIT函数） #

公式逻辑和Google Sheets一致，仅替换拆分函数：
在Sheet A的A2单元格输入：

=SUMPRODUCT(XLOOKUP(TRIM(TEXTSPLIT(B2, ",")), SheetB!$A:$A, SheetB!$C:$C, 0, 0))

下拉填充即可处理所有行。

适用于旧版Excel（无TEXTSPLIT） #

用FILTERXML实现拆分，A2单元格公式：

=SUMPRODUCT(XLOOKUP(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(B2, ",", "</s><s>")&"</s></t>", "//s")), SheetB!$A:$A, SheetB!$C:$C, 0, 0))

• SUBSTITUTE()：把逗号替换成XML标签格式
• FILTERXML()：解析生成的XML字符串，得到拆分后的任务数组

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