标签:Excel公式与函数,FILTERXML函数
如下图1所示,在单元格B2中包含由逗号分隔的数字组成的字符串。
图1
现在,需要求这些数字之和,即:
15+6+2022+9+606+89+2=2749
如何编写公式来获得结果?
如果我们使用传统的Excel函数来编写,公式如下:
=SUM(--(MID(SUBSTITUTE(B2,",",REPT("",LEN(B2))),(ROW(INDIRECT("1:" &(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)))-1)*LEN(B2)+1,LEN(B2))))
这是传统的公式编写思路。使用一定数量的空格代替字符串中的逗号来分隔数字,然后提取出各个数字,得到由这些数字字符串组成的数组,双减号(--)使数组中的数字字符串转换成数字,传递给SUM函数求和,从而得到结果,如下图2所示。
图2
下面我们使用FILTERXML函数来解决这个问题。
前面我们讲解过FILTERXML函数,参考:
使用FILTERXML函数的公式更简洁:
=SUM(FILTERXML("<t><s>" &SUBSTITUTE(B2,",","</s><s>") &"</s></t>","//s"))
公式中由FILTERXML函数得到数组:
{15;6;2022;9;606;89;2}
传递给SUM函数求和得到结果,如下图3所示。
图3
其实,还可以使用定义的名称来实现。
单击功能区“公式”选项卡中的“定义名称”,在新建名称对话框中,输入名称:
GetSum
在引用位置中输入公式:
=EVALUATE(SUBSTITUTE(B2,",","+"))
定义好的名称如下图4所示。
图4
现在,只输在工作表单元格中输入:
=GetSum
即可获得结果,如下图5所示。
图5
注意,如果使用这种方法,需要将工作簿保存为.xlsm文件。
有兴趣的朋友可以到知识星球App完美Excel社群下载本文示例工作簿。
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。