[字符系列2] 看不见,不代表不存在的不可见字符

流浪铁匠

昨日已经介绍了code和char函数的一些局限性 http://club.excelhome.net/thread-1397754-1-1.html

针对excel中的一类经常碰到的问题,在此贴概述下
谨以此文纪念无数被这类问题坑过的表亲们……

如果你的vlookup查找结果是#N/A但公式本身无误;
如果你的sum不能正确求和
如果你counta的结果和肉眼能看见有内容的单元格数量不一致
如果你发现四则运算时结果是#VALUE!
那么,有可能就是存在不可见字符,此时最好还是看下这篇文章。

流浪铁匠

我把常见的几类不可见字符，分成4类
1,空文本(俗称"假空")
2,空格
3,非打印字符
4,文本识别符
5,其他造成公式结果异常的可能原因
[第5点不属于不可见字符,第4点严格来说不属于不可见字符性质但总被人误解,而且一样可能造成公式结果异常,故归纳在一起]

1,空文本
当单元格中未输入任何数据或公式，或者单元格内容被清空时，该单元格被认为是"空单元格"，也就是俗称的"真空"单元格(和"假空"区分)
但在Excel公式中，经常会使用一对半角双引号（""）来返回无内容的结果,称为"空文本"，
另外T函数/cell函数/left/right/mid/rept/text/replace等等文本函数的结果也可能返回空文本
len对空文本所在单元格的返回结果和真空单元格一样为0，且二者在公式中有相似之处，
故空文本经常会被新手和真空单元格混淆，难以区分，
所以一般又把空位本俗称假空。

下侧为真空与假空单元格的各种判断结果



两类单元格的len结果均为0；
二者直接用 "=" 对比，结果为true(用exact对比的结果也是true)；
但使用istext判断，真空单元格结果为false，假空结果为true，说明假空单元格类型是文本
使用isblank判断，真空单元格结果为true，假空结果为false，说明isblank可以区分真空与假空
另外，使用等号直接引用真空单元格时结果通常识别为0，此时对真空单元格与0比较结果为true，但假空与0比较结果为false
但""与两者比较的结果均为true
综合以上，公式中的假空在部分情况下会体现出真空单元格的一些特性，但二者仍有差别。




假空单元格在上述已经说明，类型为文本，不支持四则运算，
此时统计结果会出现错误
故我们经常需要把空文本进行处理，
处理方法：
技巧方面，如果数据源只存在真/假空单元格，直接全选后清除内容即可
只针对假空，可以定位-常量后清除内容
但存在其他数据时，定位-空值仅能定位到真空单元格,不能定位假空
此时可以直接查找空单元格(查找项什么都不输)后ctrl+A全选查找到的内容
同时定位真空和假空单元格，清除内容
即可把假空单元格变成真空单元格。

函数方面，空文本无法识别为文本型数字来进行四则运算，所以报错
故可以使用iferror 或者 0& 或 text 函数的方式先将空文本转化为(文本型)数字再利用四则运算转化为excel可以识别的数值,即可进行计算
或者直接将iferror等公式指定返回的结果 "" 改成0,如果有0值不显示的限制可以使用自定义格式或选择设置实现

流浪铁匠

2，空格


文本型数字不属于不可见字符，但是在处理和统计时性质与数字两端有空格的情况类似，所以在这里也列举下


数字两端的空格，可以使用trim/substitute+char(32)清除，但注意字符串中间存在多个空格时，trim函数只会清除间隔中的多余空格，但保留一个空格作为分隔符
文本型数字和两端含空格的数字，在进行四则运算时均可正常计算，
但这2种情况下，使用sum等函数统计时会被识别为文本(直接引用,不是作为参数直接键入)，反而不能进行统计

处理方法：
技巧方面：空格的处理较为简单，输入空格在查找内容内，替换为空(什么都不输)即可处理
函数方面：trim函数可以处理两端多余空格，注意副作用是由于是文本函数，数字处理后类型还是文本型，需要另行处理
而且字符串中间的多个空格，处理时会保留一个作为分隔符
需要全部清除，需要使用substitute+" "(复制的空格后，粘贴在一对半角双引号内作为文本字符串),纯函数直接使用char(32)来指代空格

流浪铁匠

3，非打印字符
在Excel里面，极少数字符是肉眼看不见的，但是在字符串中是实际存在的，打印时也无法打印显示，称为非打印字符
这类字符的特性是使用len函数获取字符数比肉眼可见的字符数多,部分该类字符性质类似空格,但还是有一些差异
我把这类不可见字符主要分成3类
3.1- 类空格型
3.2- 类假空型
3.3.1- char(63)
3.3.2- char(255)

3.1- 类空格型:
这类字符和空格类似，不可见字符占用字符串的位置,肉眼看不见,但可以用鼠标选取发现

这类字符一般可以直接复制该字符后进行替换即可清除
这类不可见字符，常见类型可以直接使用trim/clean函数处理
个别顽固分子，函数法可以将该不可见字符复制后粘贴在substitute的参数中或使用替换功能进行替换处理
纯函数解法需要left/right/mid配合code获取其字符代码后使用对应的char函数配合substitute清除
该纯函数方法可以有效清除code值为127的顽固分子(该字符使用trim和clean均无法清除)
但无法有效处理下面3-3的char(63)和char(255)
另:clean同时直接清除31个code结果为0的不可见字符(详见姊妹篇《char与code的恩怨情仇》)

3.2- 类假空型
该类型不可见字符，使用len可以确定其存在，但是，编辑栏使用抹黑方式无法找到(疑似字符宽度过窄)
故我把这类字符称为"类假空"，因为编辑栏无法直接将其现原形
这类字符的处理，可以把字符粘贴在记事本或word中现形并获取(有些老师称为照妖镜),复制后替换处理或者使用3-1的纯函数法先用code函数获取字符代码后，使用对应的char配合substitute清除
具有这类性质的字符有char(9)和unicode部分字符
char(9)等直接使clean清除,但部分unicode字符的顽固分子请参考3.3的处理方式


3.3.1- char(63)
之所以单独列举这个code值的字符,是想吐槽一下
由于unichar/unicode这对函数在2013版才出现
早期版本由于无法深究这个字符,之前看过几位前辈的帖子，也有介绍过这个字符，结论基本是对于code值为63的不可见字符，推荐使用替换大法处理
在我的2016版(默认语言中文)，一共有1087896个数字使用unichar函数获取的字符,用code获取的数字代码是63 !!!
太多的字符的unicode值是不一样的，但由于code函数不识别这些字符,默认为"？",所以code值均为63
仅1-255对应的unicode字符中,unichar(160)这个字符的code结果即为63,且为不可见字符,但unicode(160)无法使用clean,trim,substitute+char(63)等各种常规函数手段处理
所以，这类字符的处理方式就是直接复制替换,纯函数需要使用2013新增的unicode确定字符代码配合substitute处理
该部分内容详细介绍请见http://club.excelhome.net/thread-1397754-1-1.html

3.3.2-char(255)
原本char(255)为可见字符： ÿ
这里介绍的是类似char(63)性质的字符
姐妹篇中幸运的发现char(65280-65535)产生的合计256个字符的code值均为255,且均为不可见字符
且这256个字符对应的unicode代码均为63733
这256个字符无法使用trim/clean/substitute+char(255) 清除
可以使用sunstitute+unichar(63733) 清除

流浪铁匠

4,文本识别符 ( ' )
半角单引号，使用这个符号作为字符串前置的单元格，会被Excel识别为文本格式
正确的文本识别符在单元格内是不可见的
但是在编辑栏会显示出来

这个符号的作用类似格式而不是字符
所以是无法使用替换等方式处理的
已经介绍了这个符号作用于格式,不影响字符匹配，
但对于有强迫症的童鞋，可以使用清除格式或格式刷来清除这个符号

如果是该符号的存在造成函数统计差异，请注意，这是因为部分函数的统计是识别数字的数据类型(真数值/文本型数字)的缘故造成的
记住我这里说的是数据类型,不是格式，这是两码事
文本型数字使用--,value,+0,*1 等等方法转化为可计算的数值即可
数值转文本型使用 &""
技巧法注意单独修改格式,无法造成数字的数据类型的变化,推荐使用分列/剪切板过度/选择性粘贴(前2个操作可相互转化,选择性粘贴用于文本型数字转数值)

流浪铁匠

5,其他造成公式结果异常的可能原因:
除以上各类不可见字符与空文本/格式造成的统计结果异常外
目前对函数的了解,发现两种特殊情况也可能会造成公式出错
5.1,对于数值精度达到15的数值,请注意，实际测试结果表明:vlookup/match/frequenry等部分函数,对数值的识别精度是超过15位的

所以如果对时间等数值精度能达到15位的数值类使用公式，如果结果异常,建议修正数值精度或者进行一次计算将原数字的精度破坏成单元格数值最大15位精度

5.2,注意unicode值大于65535的unicode字符
这是编写姐妹篇《char与code的恩怨情仇》时的发现

unicode代码大于65535的unicode字符，len的结果均为2，共有1048544个
这类字符的存在会影响len的结果与mid函数的提取结果,进而对不可见字符存在的判断,数据的对比提取等造成误差

1. =COUNT(0/(UNICODE(RIGHT(LEFT(A1,MMULT(ROW(INDIRECT(""1:""&LEN(A1))),1))))>65535))

复制代码

可以使用上述公式检查,a1为需要检测的单元格,该公式结果大于0说明存在该类型字符
该公式繁琐仅为不用数组三键及一定程度上减少多余运算量，高手请自行简化


5.3,字符总数限制
今天正好碰到一个问题,测试后发现，当字符数超过255个时,vlookup/match/countif/find/search等函数都会返回value错误
请尽量避免在函数统计中出现过长的字符串

流浪铁匠

最后发1个彩蛋

流浪铁匠

最后发1个彩蛋(更新，管理员看到这句请删除7楼原图片,谢谢)

aman1516

流浪铁匠 发表于 2018-3-28 13:12
最后发1个彩蛋(更新，管理员看到这句请删除7楼原图片,谢谢)

谢谢楼主分享，实际工作中经常是这类“小鬼难缠”，长姿势了

天涯凝望

谢谢楼主，彩蛋有excel版的吗