Excel 使用久了的话,大家会发现日常遇到的问题大都就是那几类。
比如,最常见的问的症结就是数据源不规范,如果不从源头清洗干净,后续不管是公式、图表,还是数据透视表,都会出现错误。
清洗数据这件事情,如果全凭眼力和手工调整,那工作量会很惊人,所以我们需要掌握一些批量处理不同数据格式问题的方法。
不久前我曾教过大家如何规范日期格式,具体可参见 Excel – 将各种伪日期批量转化为真日期
今天,我要教大家用两个函数解决另外两大痛点:
- 删除单元格内多余空格
- 删除单元格内多余换行
案例:
下面两张图中,单元格中存在垃圾空格或换行,以至数据透视表结果不准确。请批量清除垃圾字符。
![133eb03e1ca82f427aa5008df9eac3c7.png](https://i-blog.csdnimg.cn/blog_migrate/e1095ab79fabf0355187ccf043a30c9d.jpeg)
![7bd97b17c86d9253671a41da73f67afc.png](https://i-blog.csdnimg.cn/blog_migrate/1e3a183fccd7ea161164eea086cbf71a.jpeg)
解决方案 1:清除空格
对图 1 拉个数据透视表,就可以清楚地看到问题出在单元格中存在多余空格。删除空格需要用到 trim 函数。
![59e16d3b9a135a451663a15a1b06ed65.png](https://i-blog.csdnimg.cn/blog_migrate/99c174f23326af004dce76bc63dc39c2.jpeg)
语法:
TRIM(text)
- Text:必需;要从中移除空格的文本
作用:
删除文本中的所有空格,单词之间正常的单个空格除外
1. 在 B2 单元格输入以下公式,下拉整列复制公式:
=trim(A2)
![dd10ef10a4ed2cd4aba68706b737950c.png](https://i-blog.csdnimg.cn/blog_migrate/59807dfbccd20fe13db7a695507bf37a.jpeg)
![7253bc45bb02bfb0d7011ceb1cd5aac4.png](https://i-blog.csdnimg.cn/blog_migrate/cda157aafc114dd4ba57881122cd72b5.jpeg)
2. 复制 B2:B6 区域 --> 选中 A2:A6 区域 --> 选择性粘贴为数值
![18f2b4fb47fdb2462a3bcb185b83e398.png](https://i-blog.csdnimg.cn/blog_migrate/1963f2250cc2bac8cbd969902c2dd881.jpeg)
3. 肉眼可见 A 列数据的多余空格已经去除了,为了检验真伪,我们选中数据透视表的任意数据 --> 右键单击 --> 选择 Refresh(刷新)
![f4435bf47e96a07aa43de594547d3289.png](https://i-blog.csdnimg.cn/blog_migrate/bbd84be563a0aeadb93424558e1eb514.jpeg)
4. 空格确实已经批量删除
![3d9c4c7eb9d7e1228127fd2076282672.png](https://i-blog.csdnimg.cn/blog_migrate/8fa84406a58cd5d0f28f1c6fd9d819e6.jpeg)
解决方案 2:清除换行
图 2 的数据问题是单元格内有一个或多个换行,对于这种情况,需要用另一个函数 clean
![bb9f525c36a2e5b4db436f435db242b5.png](https://i-blog.csdnimg.cn/blog_migrate/3cbedc4eb8fb195d2881c9a46c538eb1.jpeg)
语法:
CLEAN(text)
- Text:必需; 要从中删除非打印字符的任何工作表信息
作用:
删除文本中所有不能打印的字符。
那么到底删除了哪些字符呢?微软官方是这么解释的:CLEAN 函数用于删除文本中 7 位 ASCII 码的前 32 个非打印字符(值为 0 到 31)。 在 Unicode 字符集中,有附加的非打印字符(值为 127、129、141、143、144 和 157)
为了便于大家理解,我在下表中列举了ASCII 码的前 32 个非打印字符,供参考:
![8581fa13bbed47bd595ee4d05d5e7261.png](https://i-blog.csdnimg.cn/blog_migrate/ffa885c8bb4c6acb370eac5cd8845606.jpeg)
现在回到案例,开始解决步骤:
1. 在 B2 单元格输入以下公式,下拉整列复制公式:
=clean(A2)
![3995a1591f2c2aedd3bfa9a4648284d1.png](https://i-blog.csdnimg.cn/blog_migrate/a017f0ee39af8f2e4ae0d1279c5ebf62.jpeg)
2. 复制 B2:B6 区域 --> 选中 A2:A6 区域 --> 选择性粘贴为数值
![753a87b2bf02abc10460c4cc0166cfa0.png](https://i-blog.csdnimg.cn/blog_migrate/7e04285fe43a58c21a03c5fd0c4445ad.jpeg)
3. 刷新数据透视表检验结果,正确无误
![d2193a395e23498f6021d08bc3173379.png](https://i-blog.csdnimg.cn/blog_migrate/c311dfb117edb2633cae19376469be5c.jpeg)