Excel中如何对一组文字与数值混合的单元格区域求和?【D24 |
您所在的位置:网站首页 › 文本和数值混合怎么求和公式 › Excel中如何对一组文字与数值混合的单元格区域求和?【D24 |
今日学习来源:微信公众号“ Excel精英培训” 原文链接:https://mp.weixin.qq.com/s/3K26KdyavVSauTMRqfPtVQ 今天学习的是利用【sumproduct + midb + searchb】函数对文字与数值无规则混合的单元格区域进行求和 【案例】下表B列汉字和数字是混在一起的,需要计算出数字的和。 这种汉字和数字混合一起的表格,用sum是无法求和的。 关键是:提取混合单元格中的数字部分 【提取数字的三种技巧】1、Ctrl+E在第一个单元格中输入第一行中的数字,然后按Ctrl + E,可以快速提取数字。 弊端是数据源变动后数值不会自动更新,需要重复操作 2、内容重排:通过缩小到1字列宽 - 内容重排,把汉字和数字分离,然后再复制出数字,操作有点麻烦也无法随数据源更新。 3、power query:通过它的拆分列功能,可以拆分出数字。它可以通过刷新更新结果,但对Excel版本有要求,操作步骤有点多。(没测试) 【提取数字的万能公式】="总花费:"&SUMPRODUCT(--MIDB(B2:B9,SEARCHB("?",B2:B9),2*LEN(B2:B9)-LENB(B2:B9)))&"元" 解题思路:如果数字在字符串的位置和长度是固定的,可以直接用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截取出来的是文本,所以用--(两个减号转换为数字)后才能求和。 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |