Excel函数文本数字混合计算*3

今天来搞一下文本数字混合求和的问题

这种方法是教程中出现最多的。

如图写入例子，回车，Ctrl+E

之后得到这样的结果

之后选中【开票总金额】列，Ctrl+H替换其中的A

这样就可以将文本替换为公式，得到我们的求和结果

需要注意的点：如果Ctrl+E没有得到全部的数据，可以试试隐藏标题，之后再操作一次

网上也有说写成A=数字1+数字2+数字3+......的形式，这种是将A替换成空格，原理都是一样的

这里的分列有2种含义，通过分列功能处理数据得到数字，然后相加；另一种是通过函数构建公式，分别得到对应列的数字，然后相加，这里介绍的是通过函数构建。

因为要分列嘛，所以我们得知道分成哪些列，所以观察数据发现，金额的构成主要有这么4类【现金】【医保卡】【门诊预缴金】【结算支付中心】，那么我们先写标题

=IFNA(VALUE(VLOOKUP(D$1,TEXTSPLIT($B2,":",";",TRUE),2,FALSE)),0)

这个公式填写到如图位置，向右向下填充即可得到对应的数据。

那么这个公式如何理解呢？

首先TEXTSPLIT，这个函数很厉害了，简单来说它是一个按照分隔符来拆分文本的函数。

具体含义：TEXTSPLIT($B2,":",";",TRUE)

$B2 要拆分的文本单元格，因为要向右向下填充，所以这里锁定列但不锁定行，简称锁列不锁行(*^▽^*)

":",";" 逗号之前表示按照什么符号拆分为列，逗号之后表示按照什么符号拆分为行

TRUE 表示 可忽略连续分隔符。 默认为 FALSE，这会创建一个空单元格。（这里可以将函数放到表格中自己体会一下）

后面还有2个参数，但是这里不会应用到，就不多说了，有兴趣可以搜索一下这个函数怎么用。

总之这个函数会得到这样的数组，这个很重要，方法三就是一句这个结果来构建的。

还记得这个方法叫什么吗？对，分列，我们得到的数组中属于【现金】这一列的数字是多少呢？这就用到了VLOOKUP来匹配了。

=VLOOKUP(D$1,TEXTSPLIT($B2,":",";",TRUE),2,FALSE)

D$1 表示的就是标题上的【现金】，同样因为要向右向下填充，所以锁行不锁列

现金在我们刚才得到的数据区域中的第二列对应的第一个的精确匹配，这公式翻译成汉字也就这样了

以为这就完了？nonono，这样得到一个文本，以为我们的拆分函数是个文本函数啊，它返回的结果都是文本，所以我们V到结果之后再套一个VALUE函数，将文本转换成数字。

=VALUE(VLOOKUP(D$1,TEXTSPLIT($B2,":",";",TRUE),2,FALSE))

然后我们会得到这样的表

哎呀，为啥报错了呢？因为那一列没有消费呀，所以还需要一步判断，可以用if/ifs/iferror，不过这里都不用，因为是#N/A，所以直接用IFNA函数判断一下，如果是N/A，则返回指定的值。

=IFNA(VALUE(VLOOKUP(D$1,TEXTSPLIT($B2,":",";",TRUE),2,FALSE)),0)

到这里我们同样求出了开票总金额

上面有提到，方法三其实脱胎于方法二，精简了步骤，直接一个公式得到结果

=SUM(VALUE(TAKE(TEXTSPLIT($B2,":",";",TRUE),,-1)))

我们之前用TEXTSPLIT得到这样的结果

然后其实这一步我们就已经得到数字了，直接对他进行格式变换，文本变数字，再求和就完成了，但是问题是我们这个结果里面还有第一列文本，那怎么办？

这就用到TAKE或者CHOOSE函数，我这里用的是TAKE

=TAKE(TEXTSPLIT($B2,":",";",TRUE),,-1)

返回的是数组的倒数第一列

我们就得到了这样的结果，然后这个结果依然是文本，还需要嵌套一个VALUE，之后再SUM

之后我们直接填充数据，就得到最终的结果

用3种方式得到的结果可以合计之后对比一下，都是5925.7

三种方式各有利弊，也各有应用场景。

对于数据不多，时间充裕的时候，第一种就可以解决我们的问题

对于数据有点多，不同支付方式求和的时候，第二种就很有优势

至于第三种，他无敌好嘛，哈哈哈，我很喜欢第三种，对于这道题来说简单粗暴，主要是不易出错，第一种受困于数据多样性的原因，快捷键也无法提取的情况就需要很谨慎的检查数据的正确性，第二种则因为公式比较长，步骤比较多，针对这道题来说很多步骤没有必要，所以第三种于这道题无敌(＾－＾)V

完结，撒花✿✿ヽ(°▽°)ノ✿

对了，如果你还有其他的方法，欢迎评论区交流~