用函数求文本中所有数字的和
作者:小窝来源:部落窝教育发布时间:2023-11-05 09:30:25点击:796
柳之老师曾讲过带文字的数字求和。这里小窝再为大家介绍两种分别运用工作表函数和PQ的M函数对文本中的数字进行求和的方法。
下方是报销表格,报销的数字金额与项目文字都写在同一单元格中,如何求和?
柳之老师的教程曾讲过这个问题,有伙伴问能否给出用函数求和的方法。
今天小窝就分享两个函数方法:Excel工作表函数法和PQ的M函数法。
一、Excel工作表函数法
下方是数组公式,非2021等新版本,需要按三键完成输入。
=SUMPRODUCT(IFERROR(--MID(SUBSTITUTE(TRIM(CONCAT(IF(IFERROR(FIND(MID(B2,ROW($1:$50),1),"9876543210."),0)=0," ",MID(B2,ROW($1:$50),1))))," ",REPT(" ",99)),COLUMN(A1:D1)*99-98,99),0)*1)
说明:这是个套路公式,适合求文本中由"9876543210."组成的任何数字的和。如果感觉太难,可以收藏,后续使用只需把“B2”单元格换成实际的单元格,根据数字最大个数修改COLUMN(A1:D1)中的D即可。
二、M函数法
建立查询将数据连接到PQ中。
当前日期数据自动变成了日期/时间格式,可以修改其编辑栏中的公式,让其不变。
单击编辑栏上“fx”新增公式,输入如下M函数公式创建求和列,完成求和。
= Table.AddColumn(更改的类型, "求和", each List.Sum(List.Transform(Text.SplitAny([报销明细],Text.Remove([报销明细],{"0".."9","."})),Number.From)))
最后关闭并上载即可。
说明:
①Text.Remove([报销明细],{"0".."9","."}),删除文本中0到9的数字和小数点。
②Text.SplitAny([报销明细],①),以删除数字和小数点后的每个文字和符号作为分隔符拆分文本,留下空行和数字。
③List.Transform(②,Number.From),将拆分后的文本数据转成数字。Number.From,数字格式。
④List.Sum(③),求数字的和。
⑤Table.AddColumn(更改的类型, "求和", each ④),新建名为“求和”的列,列值为④中得到的每个和。
关于用函数方法求文本中数字和就介绍到这里。
多多点赞多多分享~~周末愉快!!
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。