学习函数你可能还不知道的100件事(第2季)

流浪铁匠

半个月前分享了自己去年整理的100条函数知识点(不算最后1条其实只有99条)
谢谢大家的鼓励与支持

花了点时间搜肠刮肚,把一些100条内写不下以及这段时间对函数的发现整理出来,又凑了100条。
一家之言,姑妄听之

101,
单元格格式和数据类型是两码事。
单元格格式和数据类型是两码事。
单元格格式和数据类型是两码事。
(重要的事说3遍。群里见过无数新手修改了格式以为就能处理数据类型。
文本型数字转为可计算的数值,除了之前说的--,*1,value等等,分列、剪切板过度、选择性粘贴-运算、错误提示里的”转化为数字”等等基本功能都可以有效解决这个问题。
数值转成文本型数字可以使用分列(第3步选文本)、剪切板过度、&“”、文本函数(视数值规律)等方式处理。
102,sum在03版支持30个参数,07开始支持255个,而当参数数量过多时,sum可以使用sum((a1:a2,a3),a4) 即使用括号将多组参数变成单个参数的方式(区域联合)突破255个参数的数量限制(知道这方法就行了,一般公式不可能用到255个参数,汗)

103,if在03版支持7层套嵌,在07版开始支持64层,当条件过多时,if可以使用&或+的方式突破套嵌层数(这也只是个思路,当然实际工作推荐使用其他函数的方式化简公式而不是大量if套嵌)

104,rand的结果区间为[0,1）,注意开闭区间差异,另外论坛有大佬介绍过其周期为6.95万亿,简单说就是你用rand产生的一组数出现重复的几率绝对比买彩票中大奖低(由于说法不一暂不考虑周期内能否出现重复的可能性)

105,rand的结果实际精度高于15位。

106,测试表明rank,frequency,vlookup,match, lookup(二分法),mode.mult,max,min,small,large,subtotal,aggregate,quartile,percentile,delta 等函数对数值精度的识别也是高于15位的(部分未列举的函数很难验证其对精度的识别问题,并不代表其他函数不存在这性质)

107,lookup比vlookup/hlookup强大的原因之一就是lookup查找方向可行可列,数组形式下查找方向由数组区域的行列数决定:

108,向量形式下lookup的3参类似sumif的3参,具有自适应性,但请注意方向,只写1个单元格时识别为行方向,想识别为列方向,请至少写上2个单元格组成的区域来指明方向


109,注意lookup,match这类函数在升序时的二分法的取值区间是左闭右开的

110,frequnecy的2参的取值区间是左开右闭的

111,关于大小写,upper可以把633个字符转成大写,而lower可以把665个字符转为小写,2个函数的大小写能力转化不是完全 一 一对应的

112,proper函数(针对首字母)可以把675个字符转为大写,(针对非首字母)可以把665个字符转为小写,即proper转大写能力比upper强,但转小写能力和lower一致。

113,小写转大写,proper比upper多转化42个字符从小写到所谓大写,但其中有38个被proper(针对首字母)转为所谓大写后不能再被lower或proper(针对非首字母)转为小写

114,有26组一大一小的字符,upper和lower可正常相互大小写转化,但proper函数只能将其从大写形式转化为小写形式,无论这些字符是否在单词首字母,不能从小写形式转化为大写形式。

115,从上面几个函数的大小写转化角度来说大写字母不一定大于对应的小写字母的


116,测试下excel的忽略大小写性质不仅限于英文字母,希腊/罗马/西里尔文乃至个别汉字甚至似乎无关联的字符都可能存在这个性质



117,trim可以清除char(32),char(129-254)与char(41377)合计128个字符,用unicode字符集看是unichar(32)和unichar(12288)合计2个unicode字符,而char(41377)/unichar(12288)即全角形式的空格,所以说这函数是专门清除空格的

118,字符串中间间隔的不定数量空格,trim会保留1个作为间隔符,所以在清除字符串中间的多个不定数量的间隔符时可使用trim+substitute处理。如果要清除全部空格,请使用substitute+char(32)

119, clean可以清除char(1-31),char(128)合计32个ANSI字符,用unicode字符集看是unichar(1-31),unichar(128-159)合计63个unicode字符,这函数一般用于清除常见的非打印字符

120,trim+clean配合下还是有很多不可见字符清除不了的,譬如char(127)和unichar(160),后者很有名,微软的函数帮助里提到了这个字符的名字:”不间断空格字符”,常见于网页。请小心code函数返回结果为63的不可见字符,实际可能的字符编码实在太多了

121,纯函数法清除不可见字符的稳妥方式要使用2013的unichar/unicode+substitite处理(个人观点,因为目前看过的代码和插件都会有少数特殊不可见字符无法清除的情况,毕竟少数字符的不可见性与字体等其他因素有关)

122,说完转化大小写,说到忽略大小写性质的函数,遍历下有1835组字符相互间可被忽略大小写的函数相互识别的(排除了几个通配符和后面的一组特殊字符)

123,全半角转化的2个函数widechar与asc的转化能力可一一对应,合计95组字符可以相互转化为全/半角(默认语言为中文时),在其他语言下略有差异

124,函数里有2个常见的通配符 * 和 ?,需要正常查找和统计这2个符号请使用不支持通配符的函数或使用第3个通配符~转义,而支持通配符的函数都是忽略大小写的

125,排除上述这条的通配性后还是至少有8个字符vlookup自己找不到自己的

126,统计和查找时请小心一些特殊字符,有一组3709个字符在部分函数统计下被视为相同的

127,excel里有极少数字符是默认右对齐的(非数字),在公式里与数字&会打乱单元格内的显示顺序



128,这类字符里有2个最特殊,一定程度上也具有通配符的性质(经群友测试需要先在excel内录入字符激活所谓通配性且有其他局限性)


129,除了0-9的默认字符外,有189个字符(含0-9的全角字符)在函数公式中计算时直接识别为数字的(疑为一些特殊语言内的数字字符)



130,除了已知的3个隐藏函数,微软也干过把自己函数隐藏的事,后续版本隐藏了ceiling/floor.precise这2个2010新增函数,有意思的是,2010版本自己隐藏了自己在2010新增的函数iso.ceiling
(这里说的隐藏是在插入函数内找不到且只能在录入完整个函数+左括号后才有参数提示)

131,微软不是只会隐藏一些有局限性或可被替代的函数,也可能在后续版本删除某个函数,譬如貌似power query前身的SQL.REQUEST函数,看介绍仅存在于2007版本

132,widechar是一个具有变身能力的函数(用于半角字符转化为全角字符),因为在默认语言为中文时的函数名为widechar,为英文时为dbcs,为日文时为jis,为朝鲜文时为junja,中文繁体(台湾)时的函数名为big5


133,dollar(英文)/rmb(中文)/YEN(日文)/WON(朝鲜文)  为另一组具有这个能力的函数

134,int,round这批舍入函数忽略单元格里的数字的数据类型直接视为数值进行舍入的

135,但这批舍入函数也会把逻辑值直接识别为数值也进行计算……


136,日期函数也具有上面2条性质(熟悉以上3条对于某些问题并不需要把数据源的数据类型进行处理)

137,各个舍入函数除了舍入规则的区别,主要请注意1/2参数的符号区别和数值为负数时的舍入规则,熟悉这些性质,你会发现几种常见的舍入效果使用各个舍入函数基本都能实现

138,别以为只有舍入函数能取整,部分日期函数/文本函数/工程函数以及fact/lcm等函数照样能把数值取整(仅列举函数性质,但平时尽量别这么用,有局限性)
139,统计时countif这类if后缀函数连数字的全半角差异也忽略(注意是数字,目前唯一发现的一类直接忽略全半角差异性质的情况)

140,len函数的结果对unicode编码大于65535的字符的结果为2

141,mid与left/right对上面这条的字符的提取结果是不一样的,与其他结论配合可以证明mid的提取机理与left/right是不一样的,left/right是按照字符数提取的

142,mid提取的是字符编码,因为甚至可以把提取后重组为新字符而且提取的编码测试证明可以用于进行后续查找与统计(支持通配符)


143,midb这类函数在默认语言为中文时对汉字仅提取1个字节,返回的是空格,这也是判断汉字的一个常用手段(注意是汉字不是上面unicode编码大于65535的特殊字符)

144,searchb配合？可以找到字符串内第1个单字节字符（默认语言为中文时）

145,transpose等函数必须使用三键才能正确返回结果的

146,count很强大,因为它能忽略(内存数组内的)错误值的性质是sum不具备的

147,lookup/match(二分法)/aggregate(1参14-19时)等函数也能忽略内存数组里的错误值

148,有些函数可以识别错误值譬如error.type ,countif,数据库函数等等而不会由于错误值的存在而报错。

149,subtotal与aggregate在统计时可以忽略本身函数，这个能力在一些特殊统计时很有用


150,适当使用常量数组，可以有效简化公式。

流浪铁匠

151,subtotal的筛选序号请注意略微处理公式,修改区域或者添加 - -(减负运算)或者*1等进行四则运算,避免影响筛选结果


152,条件格式里的重复值选项,测试表明其判断机理类似countif,所以也要注意其忽略数字的数据类型差异/忽略大小写/数字精度只有15位等等情况
153,数据库函数也是支持多维引用的

154,高级筛选也是支持公式的

155,无论是二分法还是遍历法,查找时文本与数值都是分开进行的

156,同类项合并问题,早期版本在排序下可以使用phonetic+offset+countif+mid+substitute等配合的一勺汇,但这个公式有很多限制。这个问题在2016有很多解法:工作表函数的textjoin,power query的Text.Combine,power pivot的CONCATNATEX,EH论坛的自定义函数contxt等等
所以其实很多问题的解决方案是不仅局限于工作表函数的。

157,index的4参是也是很容易被人无视的

158,index在有多行多列时,如果2/3参数只使用一个,则index返回的是一行或一列的数组


159,informula是个很有用的函数,因为它也不怕循环引用


160,请注意“5-1”这种写法可被excel 识别为m-d格式且默认为本年的,这种方式有助于简化公式且不需要借助易失性函数

161,“30-4”则会被识别为d-m格式，如果是“30-13“或者“13-30”，对不起，这是字符串

162,短日期格式“18/5/1”被认为是2018/5/1，如果是“90/5/1”则是1990/5/1，一般00-29识别为20XX年，30-99识别为19XX年，但这不是excel的设置，是电脑的日期相关设置

163,请规范数据源的录入,除了20180501这种外,“2018.5.1”这种写法也永远无法直接识别为日期的,规范的数据源对数据统计有很大帮助

164,不是只有文本函数能对不足位数的数字用0占位,因为还有工程函数


165,offset的2-5参数都是可以使用负数的(函数帮助说4/5参数只能正数,错的。实际可以缺省但不能为0)

166,在我看来工作表函数公式的唯一必要组成元素是等号(函数本身不算),而括号不是,因为查看微软帮助内全部函数时才注意到true和false也是函数


167,括号很有意思,在单元格直接键入的 (1) 识别为 -1,函数参数内的 (1) 为 1,但如果加上 ""则为-1

168,很多函数没有介绍但参数实际是支持小数的,其中不少为严格取整的,但除了取整外还存在一些特殊舍入点。恰当使用这个性质是可以简化公式的。
(翻论坛帖子似乎这个性质针对不同版本的部分函数是有差异的,另外借助这个性质之前才会有我日期转季度的135种解法)

169,对上面这条的探索有几个函数是总忍不住要吐槽的(round，isodd)


170,使用HYPERLINK函数(以及indirect)等时请注意在工作表名存在一些特殊符号时地址要加单引号。

171,表格(智能表)的公式是结构化引用的,看到这类公式请不要大惊小怪

172,sumif/countif这类函数的1参请记得是不支持数组的

173,想用sumif对日期型数据进行对应月份(/年份)的求和统计,要么处理2参,要么去学下多维引用

174,但是sumif这类函数在使用比较运算符时会忽略1参的文本型日期,此时想按月统计要么用通配符要么还是靠多维引用(直接使用数组公式其实还简单点……)

175,这类if后缀的统计函数坑太多,譬如想统计内容为”>9”的个数,请记得加 "="

176,2010的networkdays.intl与workday.intl比对应函数的最大优点是可以自定义周末参数来达到一些特殊统计效果


177,2013的ceiling.math和floor.math比对应的ceiling/floor的最大优点是1/2参存在符号差异不会报错且能指定负数时的舍入方向

178,涉及日期的计算,知道2个函数对公式简化很有帮助,edate和eomonth。如果你还在用2003,这2个函数是需要加载的

179, max/maxa的最大区别在于对单元格内的逻辑值的识别问题。

180,small的2参是向下舍入的

181,large的2参是向上舍入的。这2条请注意2参的向上舍入与取整的结果都必须满足1参的数据个数范围内(1-N),否则出错


182,quartile的2参也是取整的,实际取值范围为[0,5）,在这个范围内这函数能且只能返回5个结果(即2参为0,1,2,3,4时的5个值)

183,有趣的是aggregate在1参数使用对应参数时具有上述3条相同的性质

184, delta是个奇怪的函数,对数值的精度识别也高于15位,但不识别数字的格式差异,即便是单元格内的

185,excel里排名一般有3种,美式排名，中式排名和不重复排名，从函数角度考虑最难的是中式排名,不考虑差异的话含条件的排名一个countifs就完事了

186,countif和rank的排名结果的区别在于后者也是能识别超过15位的精度的,而前者经常辅助用于文本的排名大小

187,对于数字位数较多的数据进行/1%%的放大倍数加权排序会损失数据精度可能造成结果异常,此时就可以先把数据处理成排名再加权

188,date和time的2参和3参也是支持负数的。在一些涉及时间和日期的计算中你可能需要这个性质。另外记得对date函数1个月的第0天即为上个月最后1天。

189,randbetween的1/2参数为小数时很有意思:1),1参不能大于2参; 2)1参为小数向上取整; 3)2参为小数时向下取整; 4)当2/3条结果造成1参结果大于2参结果时,返回1参向上取整的结果;5)以上几条在参数为负数时仍然满足

190,base比早期版本的工程函数的最大优点在我看来不是可以指定进制数而是可用的数据范围大了(现在最大可以到2^53)

191,极小的数在excel内录入时可以被视为0,函数阶段目前我的所有测试都没能找到9^-323和0的区别

192,支持通配符的函数有个限制是字符数不能超过255否则结果经常出错。对于长字符请使用不支持通配符的函数或数组公式处理

193,vlookup的反向查找,if({1,0}重构 很多人见过,choose({1,2} 见过的人也不少,但是有多少人知道text({1,-1}的,有多少知道index或者多维引用重构数组实现的vlookup反向查找?想要提升请多思考多学习多看别人的套路。

194,counta,isblank,countblank 这3个函数中,countblank是唯一不区分真空和假空的

195,涉及多表汇总的公式最简单的是sum(‘*’！a1) 这个通配符的sum连续多表三维引用,除了这个公式外其他公式的难度和长度会高很多,所以请尽量避免多表结构

196,逻辑关系除了and,or,not(与,或,非)外常见的其实还有第4种:“异或”,这个函数xor到2013才出现

197,对sumproduct函数记得3点：1，参数仅统计数值,忽略文本和逻辑值;2,把逻辑值转化为数值时记得文本不支持四则运算,3,各参数区域大小一致,看懂这3条,你的sumproduct才不容易错,这也是数组公式涉及的几个常识

198,文本有文本的排序与大小规则,“11”就是没“2”大,想正确比较与排序请使用text填位或转化为数值

199,有时候打破函数常规用法,你才能更进一步


(很喜欢的1个思路,除了lookup和countif的非基础用法,还不会由于数据源是错误值产生错误)

200,截至2016合计出现481个工作表函数,不同函数的组合套嵌应用正是函数的魅力所在。函数之路漫漫,欢迎大家一起探索
(不含宏表函数,函数总量统计自excel帮助内函数列表,加上了datestring和numberstring这2个隐藏函数(别问我datedif,后续版本已经有这个函数的帮助了),另外132和133这2条内的函数由于函数性质与测试结果,每组仅算2个函数,另外lenb/len这种函数由于存在区别计为2个)

丢丢表格

T199     可以写成：
                       =SUMIF(B1:B4,E1,C1:C4)
    对 T101  有点异议：
                   单元格格式和数据类型  还是有一定的关系的 ：
                                  例如 A1 单元格格式为 数值时 ， 当你录入是  2018-5-1 日期时， 结果为 值 与显示值 都是 43221 这个时间序列数值 。
             还有 当单元格格式 为常规 或数值时 ：  你录入 18 位身从证号时 (不包括含有 X 的证号） ， 从第16 位起 自动约成 0 。
    例如  A1 为常规格式 ， 在 A1 录入 =10^8   后，你把 单元格 宽度拉小， 就自动显示为 科学计数法  

1E+08

sayhi95

来学习~~~~

流浪铁匠

丢丢表格 发表于 2018-5-15 13:19
T199     可以写成：
                       =SUMIF(B1:B4,E1,C1:C4)
    对 T101  有点异议：

哈哈,谢谢老师的指正
第101条表达的是对文本型数字和真数值转化的正确的处理方式，
对于存在文本型数字和真数值的差异造成的计算和查找异常时
在群里,很多新手都是使用修改单元格格式的方法来处理，但并不能解决问题，因为修改格式，数据原本的数据类型(文本型数字/真数值）并未变化)，光这个问题上上周我在群里就解答了3次，所以特意列为本篇第1条

199条，谢谢老师取巧思路，这条如果查找内容为文本则sumif就无效了。
这条其实想表达的是lookup(1,0/ 这个经典应用,countif的1参只用1个单元格,2参使用区域的数组思路
以及countif识别错误值 3个思路的配合
也正是这条想表达的观点

km122712

楼主辛苦了，感谢分享！

流浪铁匠

应管理员要求
1/2季原稿发在本楼
另外说明下,第2季大约有30%为本人之前对文本/舍入/查找等函数的测试与统计结果,部分结果可能存在版本差异(我基本是在2016的中文版测试的)
涉及字符系列探索与测试的一些结果的附件就不加在这里，请大家直接在对应帖子下载，谢谢
http://club.excelhome.net/thread-1409175-1-1.html

七夕、

一条一条看下来,学习到不少新套路.感谢老铁~

laowuwyx

流浪铁匠 发表于 2018-5-15 20:54
应管理员要求
1/2季原稿发在本楼
另外说明下,第2季大约有30%为本人之前对文本/舍入/查找等函数的测试与统 ...

整理总结个人觉得是很累人，铁匠辛苦！

dwgzy

流浪铁匠 发表于 2018-5-15 20:54
应管理员要求
1/2季原稿发在本楼
另外说明下,第2季大约有30%为本人之前对文本/舍入/查找等函数的测试与统 ...

学习到不少新套路.感谢老铁