文本函数与字符的50条冷知识

流浪铁匠

http://club.excelhome.net/thread-1482849-1-1.html
好了,这贴的谜底该解开了

sum画画流镇楼
2019年脑洞最大的公式



我知道你们要的是1楼这个公式的详细教程与字符表,请见第30条左右内容或本楼附件

本文有2/3以上的性质为我测试文本函数与字符时的原创发现
算是补充下文本函数与字符的一些相关性质空白
之前有发过相关系列贴但没做成过合集,这次一网打尽合成一贴
希望给大家带来一点不一样的函数认识

流浪铁匠

0,通常b后缀函数与对应无b后缀的函数差异在于(以len与lenb为例)前者按字符数统计,后者按字节数统计 而全角符号与汉字等属于双字节字符，字母，数字和半角符号为单字节字符                                                                                                        
                                                                                                        
1,很多文本函数的性质和默认语言有关,  =lenb("铁")  结果为1不是bug                                                                                                        
                                                                                                        
2,len的结果和编码有关,unicode编码大于65535的字符的len结果都是2,当然看到单个字符len结果为2的更常见的原因是存在不可见字符     
                                                                                                   
(其实从len结果来说len结果为2的字符比结果为1的多得多,但很少碰到这个性质的原因在于通常情况下大家碰不到unicode编码大于65535的字符)                                                                                                                                                                 
3,excel的字符集针对Windows有ansi与unicode2类,前者对应char/code函数,后者对应unichar/unicode函数（2013新增）                                                                                                                                                                                                        
4,char函数只能产生3万+个相对常见的字符,其中完全等价(半角)空格的有127个(char(32)本身与char(129-254),这127字符的code结果均为32)                                                                                                                                                                                            
5,unichar函数能产生100+万个字符（数量与语言有关,不同语言环境下有略微差异）                                                                                                                                                                                                
6,char与code是不完全的可逆运算,其中最大的坑是code返回编码为63的字符,因为code会把所有不识别的字符全部默认为 ？,大约有100万+个                                                                                                        
                                             
                                                                                                        
7,trim的清除能力极其有限,因为它只能清除空格与全角空格,而且只是两端的,中间的不能清除干净会保留一个作为间隔符                                                                                                        
(这2个字符的code编码值为32与41377,如果你用的是unicode,那么能清除的半角与全角空格编码分别为32与12288)                                                                                                                                                                                          
8,clean只能清除code结果为char(1-31,128)合计32个字符,                                                                                                        
如果用unicode函数则结果为unichar(1-31,128-159[这31个在中文语言下code的结果是0])范围合计63个字                                                                                                                                                                                                      
9,上面提到的2个函数无法清除unichar(160)[这个字符在trim的函数帮助里有提及,不间断空格字符,常见于网页,微软自己一直知道这个坑]等不可见字符处理里常见的疑难杂症字符(下文有更隐蔽的)                                                                                                        
(针对这点如果你学过pq,可能会发现Text.Trim函数的清除能力比trim强太多了                                                                                                        
(pq里的Text.Trim能清除两端的unicode编码为9-13,32,133,160,5760,8192-8202,8232,8233,8239,8287,12288合计25个字符,其中有一些是下文将会提到的特殊字符,                                                                                                        
但还是不能清除中间的,中间的需要明确编码后使用Text.Remove清除)                                                                                                        
这间接也说明微软自己是知道这部分字符在不可见字符清除问题时造成的障碍的,只是在工作表函数范畴未进行解决)                                                                                                                                                                                                
10,excel里有一组合计3709个字符具有一些特殊性质:
1),字符串内是否存在这批字符等号判断均为相同,如同不存在一般,但len能检查出来

2),相互间也被忽略大小写的函数相互识别                                                                                                        
以上结果有版本等差异,有老师用365版本测试过在她的版本里结果是2000+        



11,char(9)在单元格字符串里不占宽度但量变引起质变,字符串前的char(9)达到1024个会造成整个字符串隐藏         

                                             
12,unichar(8204)/unichar(8205)等特殊字符同时符合上文code结果为63,不能用clean/trim清除,单元格内不占宽度,存在与否时字符串用等号判断相同等特殊性质
为新手很难检测与清除的不可见字符(注意上文Text.Trim也没能清除掉这2个不可见字符)
还好len结果是能检查出来的,明确编码就能用substitute+unichar准确清除了



13,excel存在大量简写方式但微软似乎没公布过,我之前帖子里也有老师说过规范写法才是正道
不过在测试时还是发现了几个比较有意思的

当然这部分最极致的发现是我之前用6个字符就弄疯一堆人的公式  =-" :"

(这表达式有版本和语言差异,出现自2013版本,该表达式与衍生类型推算计算式后发现疑似时间的一类特殊表达式但算法很特殊
这里只作为特殊案例介绍,不要深究,可能是bug,微软也没有解释过相关规则与用途


14,b后缀文本函数与通配符组合的特殊用途是searchb+? 定位字符串内第1个单字节字符



15,b后缀函数还有个特殊性质是提取双字节字符(如汉字)时如果提取长度为1则提取结果并不是半个汉字,而是空格



16,以上针对的是unicode编码小于65536的常规字符,因为如果用mid提取编码大于65535的字符
它是按照字符的编码提取的(工作表函数只有mid有这个能力,left和right提取的是整个字符)
利用这个性质mid可以把2个这种字符重组为新字符
                     

17,mid与left/right在很多方面有较大的参数性质差异        
除了上一条的提取编码还是整个字符的机理差异外        
还有:
1)mid参数为严格取整而left/right还是之前帖子提及的0.99999976146501这个临界点        
2)mid 有上限值(长整型特征2147483647)但left/right能达到excel数值上限        

这些结果都说明了mid本质和left/right有很大的本质区别        

        
18,支持通配符的函数都有一个性质是字符串的字符数不能超过255否则报错        
        
19,具有通配性的字符不只3个,目前发现其他3个字符在一定程度上也有所谓通配性        

        
20,上面一条已经涉及了，在excel里有极少数字符不是数值但默认右对齐,在单元格显示中会出现特殊性质        
由于上文提到的2个与常规的"*"与"？"呈镜像(/手性)对称,因此我称这部分字符为镜像字符        

流浪铁匠

21,char(1)在支持通配性的函数里也很有意思                                                                                
                                                                                                                                                        
                                                                                                               
22,支持通配符的函数还有个性质是忽略大小写,而在我看来这个说法并不准确                                                                                
因为excel里一共支持相互识别忽略差异的字符组有1835组(没含上面3709个的字符组,这组太特殊了)                                        除了英文字母范畴,拉丁/希腊/西里尔文乃至汉字与特殊符号都可能存在这个性质   


而其中的英文字符测试下来26个英文字符分别对应的忽略大小写性质下相互识别的字符数并不是相同的                                       
最多的是D,有6个,最少的是FQSX,分别只有2个                                                                                
26个字母利用忽略大小写性质能相互识别的字符合计94个         
                                                               
                                                                                
                                                                                
23,极少数单个字符是呈欺骗性质的,如果第1次接触很容易出错                                                                                       
没错,这就是单个字符(或者说符号)        
                                                                        
                                       
24,能识别大小写的函数不多，exact，substitite，code，unicode与find，                                                                                
由于上面几条性质成就了这个公式                                                                                
                                                      
是的对excel来说少数汉字也存在大小写                                                                                
                                                                                
25,不是只有数学函数有四舍五入的能力,rmb，dollar，fixed与text这些文本函数一样能进行四舍五入                                                                                
其中rmb是最特殊的一个(见27条相关性质)                                                                                
                                                                                

26,全角转半角的函数为asc,但是半角转全角的函数widechar却是文本函数里具有少有变身性质的函数之一                                                                                
(这函数在简体中文名为widechar,英文下为dbcs，日文下为jis，朝鲜文为junja，中文繁体(台湾)为big5
(为了验证这个性质装了七八个语言包,实在是测试不下去了,只测试了该函数帮助介绍里提及的语言种类和个别其他语言(目前测试的其他语言结果与英文时一致）                                                                                                                                                     
另外中文版的全半角转化只有95组字符且为完全的逆运算  




27,rmb(中文)是另一个具有这个性质的函数,英文时为dollar,日文时为yen,朝鲜文为won                                                                                
                                                                                
28,然而中文下的dollar在英文下为usdollar,所以我们有理由相信dollar是2个函数        

流浪铁匠

29,数字字符一共有199个,然而很多人只认识其中的20个                  

                                             
   
                                                                                       
30,由其他179个数字字符一共挖掘出18组格式代码(阿拉伯数字与对应全角形式合计20个数字字符)对应格式编码见上一条的199个数字字符表                                                                                                
当初借助这些格式代码与字体和sum等函数的性质结合,写出了这类让无数人质疑的公式                                                                                                        
                  
                                                                                                
其实很简单,sum作为常量的数字字符由于sum性质仍被识别为数值计算,所以sum等函数的公式结果根本就是个数值                                                                                                
而这种可被识别的数字字符合计199个                                                                                                
所以公式里随意抽取这批字符，因为公式本身添加这批字符就是个障眼法,目的只是用sum产生一个需要的数值(例如上图动物要的是23456)                                                                                                
数值在利用上述特殊格式作用下显示还原为这批特殊字符,利用wingdings字体返回对应特殊图案                                                                                                
所以公式结果的图案是利用数值在特定格式与字体配合下的特殊显示图案实现的                                                                                                
说我公式是PS的自己去面壁吧,哈哈哈
                                                                                                
31,这18组字符里最特殊的是没有对应0值的那组,在某些表达式(特别是日期)下会有不同结果,原因不明                                                                        
               

32,unichar函数的参数在一定范围内是可以用函数做表情包的                                                                                                
利用这点我们早就凑齐了十二生肖                                                                                                
                                                                                                
                                                   
                                                                                                
33,value虽然是文本函数但是是用来处理数值的                                                                                                
                                                                                                
34,numbervalue的能力比value强大不少                                                                                                
专门开过一个贴这里不再解释 http://club.excelhome.net/thread-1482509-1-1.html                                                                                                
                                                                                                
35,数字两端的多余空格在计算时可以被直接忽略的,利用这个性质经常可以少套嵌1次trim   

36,大量文本函数结果可以返回空文本"",len结果为0但本质还属于文本                                                                                                
空文本是新手很容易对空单元格操作与计算出错的重要因素之一                                                                                                
                                                                                                
37,replace和text都能在字符串中间直接插入其他字符,然而text只适合数字                                                        

38,能用0占位填充的不只文本函数，因为2013增加了工程函数base   

                                                                                            
39,text无愧是所有函数里最难的函数之一,因为发现上述179个新的数字字符时都能挖出18个新格式代码(18组特殊数字字符复制键入到单元格里会变成样子不变但本质为数值的情况,此时单元格格式为自定义的,即为该组字符默认的格式代码)                     
因此也可以用text+对应格式代码返回这批特殊的数字字符                                                                                                
所以可以说text函数的格式代码绝对没有被挖干净的                                                                                                
                                                                                                
40,concatenate可能是最废柴的函数之一，因为它的功能完全被&替代，然而2016时这个函数的衍生函数concat却是最强大的函数之一        





补充内容 (2019-9-30 21:12):
第29条里数字字符的格式代码里缺少的全角型数字字符格式代码已经无意发现，请见15楼补充

流浪铁匠

41,2013新增了一堆实用函数,但2016唯二具有不可替代性的实用函数只有textjion与concat                                                                                                
2016的ifs，switch，maxifs与minifs都相对鸡肋，能被其他函数替代，而textjoin与concat是填充函数空白领域                                                                                                
区别在于前者更适合有间隔符而后者更适合无间隔符的情况                                                                                                
                                                                                                
42,find与search的3参是很多人忽略的参数，但不代表没有作用                                                                                                                                                                   
(别纠结公式里为什么没引用,案例一直在调整所以为避免展示的单元格位置变化尽量使用常量)                                                                                                
                                                                                                
43,有不少函数是能忽略数据类型差异把文本型数字视为数值直接计算的,哪怕是作为数组或引用下的参数                                                                    
                                                                                                
44,字符的性质千奇百怪,其中甚至有8个字符是vlookup自己v不到自己的                                                                                                
本条结果受到版本等差异影响,有些人的版本能正常返回值的,疑似bug级发现                                                                                                


                                                            
45,大小写转化的3个函数也是不完全的逆运算,且相互转化规则极其复杂                                                                                                
遍历下upper 可将 633个字符进行大写转化,而 proper 可将675个字符进行大写转化(首字母)而lower的能力与proper(第2个字母开始)的转化能力相同,均可转化665个字符为(所谓)小写                                                                                                                                                                    
46,由于转化顺序和对应性,能被转化的字符组合计747组(有一组26个字符由于proper特殊转化结果我计入了2次)                                                                                
三个函数的转化关系之复杂堪称函数界的雷雨……                                                                                                
请直接见图        
                                                                        
                                                                                
                                                                                                
47,大小写转化关系里最特殊的2组为:                                                                                                
1)少数转化为小写的字母最终可转化为相同的大写字符(借助proper的转化结果),这种情况合计16组34个字符
2)有一组26个字符upper与lower能正常相互转化但proper只能转为对应小写,无论在不在首字母                                                                                                
                                                                                                                                                                     
48,文本函数里BAHTTEXT是一个比较突兀的函数,因为中文版隐藏了10+个泰历相关函数却保留了这货   

                                             
                                                                                                
49,phonetic堪称中文版的一股清流,因为利用语言差异的区别这个函数在中文版具有文本合并的特殊能力而且甚至支持多维                                        在没有textjoin与concat前解决了不少似乎无法解决的函数问题



50,用文本函数或者其他函数提取字符问题在excel里是一大类问题，我专门整理过相关案例                                                                                                
但是，如果你学过pq，会发现很多字符提取问题使用m函数比工作表函数简单很多(只提取数字Text.Select+{"0".."9"},字符串颠倒Text.Reverse等等太多)                                                                                                
函数只是excel的一部分功能,power query 也是                                                                                                
pq里还有700+个m函数等着探索,(=#shared的数量,有些其实属于参数,而且pbi一直在更新),dax也很强大                                                                                                
很多问题不要仅限于函数,excel里还有很多其他方式能解决                                                                                                
所以我要去潜水学习m函数去了,再见        

sbdk007

铁匠老师辛苦啦，期待学习你的M函数研究成果

jivy1212

搞研究的人，果然骨骼惊奇，赞

某某t

大神，追随你的脚步而来

weiqi56

膜拜大神！

流浪铁匠

好了,配图上传完成

1835组大小写性质相互识别的字符组
747组大小写转化相关字符
199个数字字符
3709个特殊字符
95组全半角转化字符
8个vlookup自己v不到自己的字符
6个通配符（其中3个新的）("当函数学会卍解"这贴里相关特殊通配符作用的公式名为六道轮回就是因为目前发现具有通配性的字符合计6个)
以及太多其他发现

从学函数开始,总想做出点令人瞠目结舌的事,直到之前函数你不知道的100件事第3季期间脑洞大开创出sum实现画画的公式,我想,我们做到了