科技数码

excel公式应用技巧：文字和数字混合的单元格，如何求和？

科技数码佚名2023-09-21

编按：有些单位部分账目不够规范，譬如报销表，报销内容中文字和金额数据是记录在一起的，怎么求和得到报销金额呢？通常的做法是需要增加一列，把数据单独提取出来，然后进行求和。当然也可以用公式一步到位直接求和。学习更多技巧，请收藏关注部落窝教育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）即可。