在单元格公式中加入文字中有数字有文字有字母能不能套公式？

来源：蜘蛛抓取(WebSpider) 时间：2023-09-25 07:20 标签： 在单元格公式中加入文字
2020-10-31 07:00
来源:
部落窝教育
编按：有些单位部分账目不够规范，譬如报销表，报销内容中文字和金额数据是记录在一起的，怎么求和得到报销金额呢？通常的做法是需要增加一列，把数据单独提取出来，然后进行求和。当然也可以用公式一步到位直接求和。 学习更多技巧，请收藏关注 部落窝教育excel图文教程 。
数据求和原本很简单，可是如果数据录入不规范，譬如账目中的文字和数据记录在一起，那求和就复杂了！请看下面的例子。这是一个简单的流水账，费用内容文字和具体金额是记录在一个单元格内的，现在我们要计算出总的消费金额。
如何求和呢？常规操作，是先建立辅助列把金额数据提取出来，然后进行求和。
数据提取，大家的第一反应可能是用Ctrl+E。然而此处，用Ctrl+E会遇到问题，因为有小数和负数存在。下图是用Ctrl+E的效果，存在错误：
当前数据少，我们可以Ctrl+E后再手动修改个别错误数据。
如果数据有成百上千条呢？显然我们需要另外的方法来处理。
一、用公式提取数据，然后再求和
我们一起来分析这组数据的特点。总结后有以下几点：
l 数据和文本混合，文本全部为汉字，没有字母。
l 每个单元格中只包含了一个数据。
l 存在小数和负数。
l 在数据（含负号在内）前后没有其他单字节字符。
l 数据在字符串中所在位置不同，也没有规律。
l 数据的字符长度不固定。
针对以上的几个特点，我们可以考虑使用MIDB函数、SEARCHB函数来提取数字。
在单元格C2中输入“=--MIDB(B2,SEARCHB("?",B2),2*LEN(B2)-LENB(B2))”并向下拖曳即可。
公式思路和函数解释： 把数据从字符串中提取出来就是一个文本提取操作。当前的难点是每个单元格中数据开始提取的位置和长度不固定。从前面的分析中，我们得到一个可以利用的规律： 在数据（含负号在内）前后没有其他单字节字符 。 这条规律的价值在于，单字节字符部分就是我们需要的数据（含负号）。那只要能用函数自动获取每个单元格中单字节字符的开始位置和长度，一切问题就解决了。
l MIDB函数是根据指定字节数提取文本。
它与我们常用的MID函数用法一致，唯一的区别就是：MID根据字符数来提取文本，MIDB根据字节数来提取文本。
l SEARCHB函数用于解决从哪里开始提取文本。
SEARCHB函数是查找第一个字符串在第二个字符串中的起始位置。它返回的起始位置用字节数表示。
SEARCHB("?",B2)表示在B2单元格（第二个字符串）中查找第一个单字节（“？”）字符的字节位置。其中“？”在本处表示任意单字节字符。SEARCHB("?",B2)实际查找的就是数字6（它是B2单元格中第一个单字节字符）在B2中的位置。在公式中选中SEARCHB("?",B2)按F9，可以看到返回结果是7。
这部分函数公式帮我们解决了从哪里开始提取文本。
l 2*LEN(B2)-LENB(B2)这部分函数公式解决了文本的提取长度。
LEN(B2)统计的是B2单元格字符串的字符数；LENB(B2)统计的是B2单元格字符串的字节数。两倍字符数减去字节数就可以得到单元格中单字节字符的字节数，对本例来说就是得到数据的字节数。
现在，数字提取出来了，最后再利用SUM函数求和即可。 学习更多技巧，请收藏关注 部落窝教育excel图文教程 。
二、直接用一个公式完成求和
这次我们来增加一些难度。要求在一个单元格内直接用公式求出总费用。
在单元格D2中输入“=SUM(--MIDB($B$2:$B$10,SEARCHB("?",$B$2:$B$10),2*LEN($B$2:$B$10)-LENB($B$2:$B$10)))”，三键回车（Shfit+Ctrl+Enter）即可。
公式思路和函数解释：要直接用SUM求和，那就必须让MIDB函数返回一组数而不是单个的值。因此我们把上一个公式中的B2更换成了B2:B10，再套入到SUM函数中，输入完毕按三键回车。
好了，以上就是今天分享给大家的内容，希望朋友们都可以掌握。 学习更多技巧，请收藏关注 部落窝教育excel图文教程 。
这个案例，大家可以学到的技能和教训：
l 技能：混合文本中的单字节字符都是一个数据时的数据提取与求和方法
l 教训：不规范录入将增加工作难度
＊＊＊＊部落窝教育-excel文字数据混合求和＊＊＊＊
原创：Excel应用之家/部落窝教育（未经同意，请勿转载）
更多教程：部落窝教育
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
相关推荐：
提取数字的万能公式：Excel数字提取技巧:从包含文字的单元格中提取所有数字的万能公式
SUM求和高阶用法：SUM函数的进阶用法：快速对交叉区域、应收款项、小计行求和！
最简单的条件求和函数：DSUM，最简单的条件求和函数！你知道不？
按颜色求和：Excel教程：Excel根据单元格填充颜色求和的三种方法返回搜狐，查看更多
责任编辑：

我要回帖

更多关于 在单元格公式中加入文字 的文章

 

随机推荐