1. 自营销首页
  2. 软件

新同事发来一张Excel报账表,气的想跳楼

如果你是做会计,也许会遇到这样奇葩的同事:

它给你列的报账清单中,汉字和数字是混在一起的,你却需要计算出数字的和。

这种汉字和数字混合一起的表格,用sum是无法求和的。难道要一个个的输入数字求和?

以前兰色分享过用3种方法提取数字:

  • Ctrl+E : 在第一个单元格中输入第一行中的数字,然后按Ctrl + E。可以快速提取数字,但数据源变动后还需要重复操作
  • 内容重排:通过缩小到1字列宽 – 内容重排,把汉字和数字分离,然后再复制出数字,操作有点麻烦也无法随数据源更新。
  • power query:通过它的拆分列功能,可以拆分出数字。它可以通过刷新更新结果,但对Excel版本有要求,操作步骤有点多。

今天,兰色分享一个万能求和公式,无论数据源怎么变,都可以自动提取数字求和。

公式:

=”总花费:”

&SUMPRODUCT(–MIDB(B2:B9,SEARCHB(“?”,B2:B9),2*LEN(B2:B9)-LENB(B2:B9)))&”元”

公式看上去好复杂?不要晕。兰色带大家一起剖析一下这个公式后,你就会发出感慨:Excel中原来还有这么巧妙的公式。

解题思路:

如果数字在字符串的位置和长度是固定的,可以直接用mid(字符串,开始截取位置,截取个数)函数完成。

但问题时数字位置是不定的,长度是不定的。这种情况下我们可以用公式计算出来。

1、找出数字的开始位置

如果是汉字和数字混合,可以用Searchb函数来查找数字位置

=SEARCHB(“?”,B2)

注:带B的函数是按字节数计算的(一个汉字占两个字节,数字点一个)

2、计算出数字的位置

利用2*字节数 – 字符数,倒推出数字的个数,即:

=2*LEN(B2)-LENB(B2)

3、提取单元格的数字

既然数字开始位置和长度有了,就可以用midb函数提取了

=MIDB(B2,SEARCHB(“?”,B2),2*LEN(B2)-LENB(B2))

注:因为是字节数计算,所以也要用midb函数,而不能用mid函数

4、提取出所有单元格数字并求和

把B2单元格换成B2:B9,就可以提取出整个区域的数字,然后用sumproduct函数求和。

=SUMPRODUCT(–MIDB(B2:B9,SEARCHB(“?”,B2:B9),2*LEN(B2:B9)-LENB(B2:B9)))

注:因为midb截取出来的是文本,所以用–(两个减号转换为数字)后才能求和。

最后连接上“总花费”和“元”,公式为:

=”总花费:”

&SUMPRODUCT(–MIDB(B2:B9,SEARCHB(“?”,B2:B9),2*LEN(B2:B9)-LENB(B2:B9)))&”元”

兰色说:可能又有同学说,工作中这种情况很少遇到,这么麻烦的公式就不用学了吧。No!一旦遇到这样的奇葩问题,如果不会简单方法会让你崩溃,二者从今天的公式中你会学到几个神奇的字符串处理思路。可以用来解决其他疑难问题。

来源:Excel精英培训,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/39031

发表评论

电子邮件地址不会被公开。 必填项已用*标注

侵权联系
分享本页
返回顶部