酷！求字符串中数字之和的高级方法

excelperfect

标签：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函数的妙用

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($B$2,',','+'))

定义好的名称如下图4所示。

图4

现在，只输在工作表单元格中输入：

=GetSum

即可获得结果，如下图5所示。

图5

注意，如果使用这种方法，需要将工作簿保存为.xlsm文件。