基于Excel文本的多变量评分公式需求：处理分号分隔数据

核心思路 #

先拆分单元格内分号分隔的类型项，匹配预设评分规则求和，最后合并A、B、C列得分得到D列结果。

前置准备 #

1. 新建评分对照区域（可在当前工作表空白列，如F:G列，或单独工作表）：
   • F列填写所有可能出现的类型（如"类型1""类型2"等）
   • G列填写对应分值（支持负数，按需求设置）

公式实现（分版本） #

适用于Excel 365/2021及以上版本（支持TEXTSPLIT） #

D2单元格输入以下公式，下拉填充：

=SUM(
  XLOOKUP(TEXTSPLIT(A2,";",,TRUE),$F$2:$F$100,$G$2:$G$100,0),
  XLOOKUP(TEXTSPLIT(B2,";",,TRUE),$F$2:$F$100,$G$2:$G$100,0),
  XLOOKUP(TEXTSPLIT(C2,";",,TRUE),$F$2:$F$100,$G$2:$G$100,0)
)

• TEXTSPLIT(A2,";",,TRUE)：拆分A2的分号内容，自动忽略空值，解决最后一项无分号的格式问题
• XLOOKUP(...)：匹配每个拆分出的类型到评分表，返回对应分值，无匹配则返回0
• SUM(...)：合并A、B、C列所有项的得分

适用于旧版Excel（无TEXTSPLIT，用FILTERXML） #

D2单元格输入以下数组公式（输入后按Ctrl+Shift+Enter确认），下拉填充：

=SUM(
  IFERROR(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(A2,";","</s><s>")&"</s></t>","//s[text()]"),$F$2:$G$100,2,0),0),
  IFERROR(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(B2,";","</s><s>")&"</s></t>","//s[text()]"),$F$2:$G$100,2,0),0),
  IFERROR(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(C2,";","</s><s>")&"</s></t>","//s[text()]"),$F$2:$G$100,2,0),0)
)

• FILTERXML(...)：通过XML语法提取非空类型项，适配分号分隔的格式
• IFERROR(VLOOKUP(...),0)：匹配类型到评分表，无匹配时返回0避免错误

注意事项 #

• 评分对照区域的范围（如$F$2:$F$100）请根据实际数据调整，确保覆盖所有可能的类型
• 若类型存在大小写差异，可在公式中加入UPPER()/LOWER()统一格式，如UPPER(TEXTSPLIT(A2,";",,TRUE))

内容的提问来源于stack exchange，提问作者Mo-L