【扩展】Excel不可见字符会给我们带来无尽的困扰,这样搞定!

最近推送的五篇文章:

·  正  ·  文  ·  来  ·  啦  ·

前言

上个月写了二篇文章,介绍了新手经常会遇到、让人百思不得其解的一些问题:

【第1季】没被这些问题坑过,绝对不是合格的表哥表妹!

【第2季】没被这些问题坑过,绝对不是合格的表哥表妹!

今天介绍一个更离奇的:一些单元格明明无内容、也无空格,却无法加减。这些单元格中似乎住着一些未知的、不可见、不可触摸的幽灵,CCAV科学探索发现栏目记者龙逸凡将带领你窥探这些“表格幽灵”的奇特现象,揭开它们最神秘的面纱,了解事情的真相。

一、神秘现象

某年某月某日逸凡公司的财务人员龙表哥,从财务软件中导出各子公司的财务报表,然后对它们进行加总,一个奇怪的问题出现了,如果某科目所有公司都有数字,那么加总求和没问题,只要有一个公司无数字,就无法加总。如下图中的货币金,各公司都有数字,能加总求和,但存货项目由于江西分公司无数字,加总求和出错。

二、探寻真相

为查明情况,龙表哥选中江西分公司C13单元格,然后在公式编辑栏查看了一下,没有任何内容,又用LEN函数,计算了一下其字符数,计算结果为0,说明单元格内无内容。

然后又用公式=C14+J10,可以得到计算结果,说明J10没有看不见的幽灵,

再用公式=C13=J10和=EXACT(C13,J10)比较两单元格,计算结果都是TRUE,说明二个单元格是相等的。

既然J10为空单元格,里面没内容,J10又等于C13。那么C13也应该没有内容才对。

龙表哥左思右想,百思不得其解,后来抱着死马当活马医的心态,用ISBLANK函数看一下C13单元格是否为空。奇迹发生了,刚才用LEN计算出字符长度为0的单元格,用ISBLANK函数的计算结果居然为FALSE。

也就是说C13并不是空,C13中的确有内容,只是它象幽灵一样,看不见,摸不着,它没有字符长度,不能用LEN来判断其是否为空。但可以用照妖镜ISBLANK函数让其显出原形

三、解决办法

既然已经查明单元格中住着这些不可见的幽灵,如果要让汇总公式能正常加总,选中单元格按DeLete键删除一下就可以了。

但是,问题又来了,报表中那么多单元格,要批量选中空白单元格,我们使用按F5快捷键,“定位-空值”,会提示“未找到单元格”。

这是因为它是假空,里面有看不见的“幽灵”,所以不能用“定位-空值”来批量选定这些假空单元格。那怎么办呢?用查找!

具体操作如下:

Ctrl+F,打开查找对话框,查找栏什么都不输,然后点击查找全部,再按Ctrl+A,就可选定所有假空单元格,关掉查找对话框,按DEL键灭杀“幽灵”。

查找替换很强大,我们平时把它忽略了,它可以实现定位的很多功能,比如定位-常量、定位-公式、定位-批注、定位可见单元格。我们知道,在工作表受保护时,是无法使用定位功能的,这时就是查找替补上场拯救世界的时候了。另外,查找还能批量选定指定区间的数字、实现多列筛选等等。

点击本文左下角的“阅读原文”可直接跳转到该网页。

知识点小结:

1

一般情况下可用LEN函数来判断单元格是否为空。 

2

判断单元格是否为空的标准方法是用ISBLANK函数(函数名释义:is是+blank空白) 

3

比较二单元格是否相等可以用等于号=或EXACT函数。But,如本文所述,这二位也不是那么靠谱。另外一个不告谱的例子可以参见下面链接最后一个例子:

【扩展】使用VLOOKUP函数的常见错误及解决方法

4

定位无法选定假空单元格;

5

定位无法在工作表保护时使用,可以用查找替换来替补上场。

四、其他不可见字符

前面介绍完了最让人不能理解的一种不可见字符,下面我们来看两种比较简单的情况。

第一

在编辑栏可显出原形的透明人

这类不可见字符一般都是空格。看起来为空,选中后,点击编辑栏,发现光标离最后一个数字有一定的距离。

比如《“偷懒”的技术:打造财务Excel达人》第二章的表2-4:

处理方法:

这个比较简单,直接在编辑栏复制,然后用查找替换批量删除就是了。另外还有其他的方法,大家详见《”偷懒“的技术:打造财务Excel财务达人》第二章的操作,不赘述。没有书的朋友,可点击下面的链接购买:

第二

看不见,但有长度

在编辑栏看不见,光标是紧邻最后一个字符的,但用LEN可测得其长度。

比如上面用LEN计算A3单元格的字符数为6个。我们试着用删除前导或尾部空格的TRIM函数是无法清除的,用公式=LEN(TRIM(A3))的计算结果还是6。

然后用RIGHT取最右二个字符,只看到8,也就是8后面还有一个不可见字符。

然后用CODE函数看最后那个不可见字符,其ASCII 代码为63。公式:

=CODE(RIGHT(A3,1))

处理方法:

象上面这种,不可见但可测量的不用函数如何清除呢?

可以在编辑栏将光标移到最后一个数字前,比如A3单元格将光标放在最后的5和8之间,然后拖动鼠标,选定8和后面的不可见字符。再将其复制粘贴到查找栏,将光标放在8之前,按DEL删除掉8,留下不可见字符,再全部替换,就OK。详见操作GIF图

本部分小结

1

对待空格这样的“透明人”,可在编辑栏让其露出原形,复制后,用查找替换删除。 

2

TRIM函数可清除前导、尾部的空格,不会删除字符串中间的空格,也不可删除其他不可见字符。CODE函数可计算字符的ASCII 代码。 

3

可以通过与不可见字符前的数字一起复制,来复制尾部的不可见字符,然后,用DEL删除数字,留下不可见字符。最后用查找替换批量删除不可见字符。

扩展

也可以用查找和替换来批量删除换行符。只是换行符需要手工输入,可用下面三种方法来输入:

方法1、在查找栏按Ctrl+Enter

方法2、在查找栏按Ctrl+J

方法3、在查找栏按Alt+10

我们以《“偷懒”的技术:打造财务Excel达人》第2章的《表2-5》做示例:

如果本文对你有帮助,走时别忘了点一下文章底部的广告↓和右下角的大拇指↘

(0)

相关推荐