如何修改FILTERXML公式提取@后全部数值数组以实现求和？

云原生阿岚

2026-7-19

Excel提取单元格内换行文本中@后的数值并求和问题

问题场景

单元格A1内容如下（包含换行）：

ABC@10
Gg hh ii@20
BB@30

需提取所有@后的数值并求和（目标结果：60），但原公式=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"@","</s><s>")&"</s></t>","//s[number(.)=.]"))仅返回最后一个数值30。

解决方案

方法1：修正FILTERXML公式（兼容多数Excel版本）

原公式未处理单元格内的换行符，导致XML节点拆分不完整。修改后公式：

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),"@"),"@","</s><s>")&"</s></t>","//s[number(.)=.]"))

第一步SUBSTITUTE(A1,CHAR(10),"@")：将单元格内的换行符替换为@，统一拆分标识
第二步SUBSTITUTE(..., "@","</s><s>")：将所有@替换为XML节点标签，使每个片段独立为节点

~~FILTERXML通过XPath筛选出所有可转换为数值的节点，最终SUMPRODUCT求和得到60~~

方法2：使用动态数组函数（Excel 365/2021及以上）
利用TEXTSPLIT和TEXTAFTER实现更直观的提取：
=SUM(--TEXTAFTER(TEXTSPLIT(A1,CHAR(10)),"@"))
TEXTSPLIT(A1,CHAR(10))：按换行拆分A1内容为独立文本片段
TEXTAFTER(..., "@")：提取每个片段中@之后的文本（即目标数值文本）
--：将文本型数值转换为数值类型，SUM直接求和得到结果
内容的提问来源于stack exchange，提问作者8平民