学习函数你可能还不知道的100件事(第3季) [黑科技篇]

流浪铁匠

对函数没兴趣的请勿入内
函数新手请勿入内
学习函数仅为工作的请勿入内
因为这篇容易走火入魔
本篇作为《学习函数你可能还不知道的100件事》之终结季,着重于体现一些函数的黑科技和旁门左道,仅适合函数发烧友观看
新手由于观看此文造成的走火入魔等不良后果请自行承担!  /斜眼笑


在学函数之初,我就想做点让人瞠目结舌的事,现在,我想,我们做到了




看到管理的点评:
由于我等级较低，发帖时每日图片附件只能上传30个，发帖图片都不太够，所以当日无法上传文档附件,今日已把附件发在9楼
需要的请自行下载

以后不再打算做这个系列,有其他相关发现会在后面跟帖介绍,不再单独开帖

流浪铁匠

201,听说还有87.53%的人不会sum函数。


202,表现型是基因型与环境共同作用的结果,而函数公式的显示结果为字体与格式共同作用的结果。
[201的写法致敬祝老师常用的微信文章标题,而87.53%这个值是网上一个常见梗]
203,第2季已经提过,excel里一共有199个数字,这里指的是能直接被函数和计算识别为数值的字符,除了0-9这10个阿拉伯数字及对应全角形式外还有179个特殊字符(上面的sum就是摘取了其中的9个)
[该处图片较大影响后续阅读，图片放在后续楼层和文档内]

而英文字母有94个(这里指的是能被忽略大小写性质的函数识别),其中数量最多的是字母D，有6个字符可以识别为”D”，而F/Q/S/X这4个字母只有对应大小写合计2个字符

204,excel里至少有2组数字是没有对应的0的，一组是罗马数字，一组是埃塞俄比亚的阿姆哈拉语数字(不知道后者的都是没看完199个数字列表的(篇幅限制请见后面或者附件))

205,我一直坚信text的格式代码并未全部公布,在上述测试179个特殊数字字符时我都能找到18组新的格式代码(数字部分修改后结果可能变化,算下来都远远不止18组)

206, date函数具有内置上限,3参上限为32767(整数型),3参无论使用多大的数值也会自动受限在此数值内

207,mid也有内置上限,典型的长整型,超过则会报错

208,round还是,也是长整型,但超过该上限不会报错而是返回0值!（注意测试下round的临界点是整数位数和2参共同作用的结果）

209,虽然也具有舍入能力，但dollar，rmb与fixed 的2参最大值只到127

210,说到dollar这是个奇怪的函数, 中文下rmb函数在英文下转为dollar,而中文下的dollar函数在英文下会变身为usdollar,所以我们有理由相信dollar在不同语言下是2个不同函数

211,虽然单元格字符数最大32767,但substitute的4参极限还是长整型的特征

212, mod也有上限,对应计算式   MOD(n, d) = n - d*INT(n/d)  内若mod的2个参数 n/d的结果达到1125900000000则报错(翻贴时发现这条可能有版本区别,这是2016的测试上限)


213,excel里能录入的最小时间精度为hh:mm:ss.000, 即精度大约为1/24/60/60/1000=1.15740740740741E-08，换算为小数只到小数点后8位
214,之所以提到上一条是因为一个时间的临界点： 23:59:59.499/23:59:59.500,部分日期与时间函数是以这个临界点来判定日期
215,测试表明day/weekday/year/month/hour/minute/second/days360 这8个函数会把当天23:59:59.499(含)前的日期识别为当天，但23:59:59.500(后)的日期识别为第2天
而 days/edate/eomonth/weeknum/isoweeknum/workday(.intl)/networkdays(.intl)/yearfrac 等函数严格取整,只识别日期部分作为统计的日期,完全不考虑时间部分,即便此时你用的是小数写法0.99999999999……
这条在你的时间精度过高时还请了解


216,请知道一些特殊但能被识别的日期或日期格式写法(见上图)

217,如果从临界点考虑(小数形式)函数对时间的识别达到皮秒级别……

218,之前提过日期和时间的计算本质是数学计算，因此相关问题完全可以用数学函数处理,int取日期,mod判断周末,等等,能有效简化公式

219,熟悉各种日期的识别与函数的性质,可以写出一些有趣的公式


220, May1和May99都能用month正确提取月份,但它们识别的日期规则是完全不一样的

221,日期函数经常能间接识别1900-1-0这个错误日期,由于这个设定造成了day的实际结果除了1-31外是可以返回0的

222,部分日期函数的参数很特殊,支持数组,但不直接支持单元格区域内的日期,引用区域必须添加四则运算转化为数组


223,那个鸡肋的QUOTIENT也是一样的性质,所以请不要说这函数不支持数组

224,工作表函数里ceiling的衍生函数其实比floor的多2个，资料显示都是2010新增的，2010版本到底发生了什么?! (iso.ceiling和ecma.ceiling在后续版本都被隐藏)

225,datevalue也是个有趣的函数,这个函数针对少数格式能否识别与默认语言也有关


226,函数的结果与版本也会有关系，因为一些函数可能进行过修正和优化

227,numbervalue在处理数字时能清除少数不可见字符但能力弱于clean,不过这函数的最大好处是处理欧式格式

228,但有趣的是numbervalue能把单独的%或半角逗号处理为0

229,datevalue也是个废柴函数,测试下对于绝大多数日期与时间格式的识别与int/-- (减负运算)类似,但对时间部分会进行清除只保留日期部分,仅日期前有空格时可识别的能力强于四则运算转化

230,timevalue的性质类似,对于日期+时间的格式仅提取时间部分且比四则运算唯一优秀的能力是忽略字符最前面的多余空格

231,value/datevalue/timevalue这几个函数对缺省写法的时间表达式的识别还不如直接四则运算与舍入函数


232,除了上面几个value类函数外,工程函数里的decimal也是个具有类似性质的函数,因为它能把空格和char(9)也视为0

233, ARABIC和numbervalue类似,除空格外其实还能把char(9),char(10),char(13)这3个字符也直接识别为0


234, ARABIC和ROMAN也是不能完全相互转化的, ARABIC可以识别罗马数字前的的负号返回负值，但ROMAN只识别正数

235,文本型的时间表达1小时整可以直接简写成”1:”

236, 看到这里再打击你一次，你还是不会sum！ 不信的话请看这招天外飞仙(友情提示:仅适用于2013及以上版本)


237,上面这条其实是我无意发现的一个(/类)excel特殊表达式，篇幅限制这里仅罗列表达式与对应计算式，相应的范围与特殊情况另外开帖介绍
在不同的写法下这类表达式的计算公式有较大差异


238,由于上述表达式的分母基本可以统计为86400且分子的换算关系与时间进制类似,所以基本可以认为这是微软未公布的一类时间表达式
但和普通的时间表达式区别较大

239,一个单元格的公式(不借助定义名称)最多可容纳1170个sum……
(=SUM(,)+SUM(,)+SUM(,)+SUM(,)+SUM(,)+ …… 测试出来的,我知道很多人会觉得这条没用,但请看下一条……)

240,但一个单元格的公式(不借助定义名称)最多只能容纳1024个if
(测试了部分短函数目前只发现if有数量限制,其他基本是由于公式8192字符总数的限制……
而且奇怪的是if的数量上限在群里有部分大佬测试过似乎在不同版本有不同上限数,我的是2016版本)


241,遍历统计在excel2016中文版有1111997（111万）个字符,其中code函数结果为63的有1087896个，
即只有24101个字符的code结果结果不是63，只占字符总数的2.2%,
这就是无论群里还是论坛对code结果为63的字符难以处理的原因，code函数把所有不识别的字符均默认为?,结果为63,实在是太多了,如果不借助2013新增的unicode和unichar无法定性与处理

242,强大的lookup只有1种查找方式(升序二分法),大众情人vlookup多1个机理最简单的遍历法,但其实查找方式最多的是match函数。
而被大家忽略的match降序二分法在有些情况下也是很强大的


243,我不知道函数的终点在哪里，因为大佬们总是能化腐朽为神奇，精通各种函数的应用
譬如上面这个web函数提取字符……

244,合并单元格的相关公式是函数进阶跳不过的一个坎……


245,mode函数在一定程度上也是支持数组运算而不需要三键的(详见上面的第6个解法)

246,mmult是数组公式的催化剂,很多原本需要三键才能正确返回结构的公式,在恰当的地方套嵌mmult可以不使用三键就能正确返回。

247,从这个角度来说aggregate/lookup/frequency/sumproduct/index等等 也是催化剂

248,函数公式的水平技术是在不断进步的，譬如这个合并单元格的条件求和公式


249,没有2019以前多数常规需要数组三键的数组公式借助上述等特殊函数都能不三键实现结果

流浪铁匠

250,没有maxifs和minifs前条件最大最小一般都是用数组公式解决,由于数组运算产生的0,min一般会配合if，而其实2010的aggregate早就解决了这个统计问题
251,同时统计条件最大/最小值其实有四大金刚,而我习惯把aggregate称为”四大金刚之五”


252,然而2019动态数组函数的出现，数组三键成为历史
253,万金油也将成为历史

254,按照妖姐的说法新版本的动态数组对伪内存友好但并不友好于多维引用

255,rank也是少数支持区域联合用法的函数,利用括号把多个区域变成函数的单个参数,这个用法在你数据为隔列多列时统计排名很方便

256,函数大佬总是能把特殊函数用在最适合的地方


257,if的数组运算由于数组维度的影响结果也会有数组运算的优先级


258,当一个函数的多个参数均使用相同维度的数组时,各参数的数组对结果来说也会有计算的优先级

259,vlookup的查找和判定顺序是有优先级的，这点对一些函数来说可以以错误值的优先级别来验证和测试


260,vlookup的3参为负数时,不考虑无匹配内容的NA错误,还能产生3种其他错误值

261,条件格式的结果仅识别逻辑值和数值,错误值是自动忽略的,所以条件格式的公式可以不用特意处理错误值的

262,数据库函数注意自带一侧通配符


263,sum/max等聚合函数的能力之一是把数组变成常量(即便是单个元素的数组),单个元素的数组和常量在某些情况下也是有区别的,必须用这类聚合函数处理成常量

264,说到多维引用phonetic也在一定程度上支持多维的（篇幅限制请查看上面合并单元格求和的第3个解法）

265,数学思维是函数进阶的必备条件



266,虽然绝大多数函数不会忽略筛选和隐藏,但筛选等操作仍会造成公式的重算,故如果数据量大请在对工作表操作前先取消公式

267,对存在公式的表格进行排序时请注意排序可能造成公式的引用变化造成排序结果异常，排序前请取消公式或借助row()定位行号避免影响排序结果

268,对edate当日期为月底时,转化后的日期天数小于原日期天数,结果为该月月底日期

269,提2个没什么用的find和search的区别……


270,别以为浮点误差离你很远,(见上图)

271,函数自带表情包,不过这个似乎与语言和字体有关


272,说到%不得不提一个奇怪的格式 “%1”这个写法在运算里等价1%

273, 进制函数在进制转化时,数字只能转化为对应大写字母,但由字母转化为数字时可以识别小写字母

274, 有1个字符(貌似该字符影响审核，请直接看图)在数字两侧下都不影响对数字的识别,但$和￥只能识别在数字左侧的


275,在2019以前可以使用定义名称的方式避免使用三键,但在极少数情况下涉及多维的公式会发生直接录入下是(真)内存数组但使用定义名称后就仅是伪内存数组的情况，原因不明

276,函数里有个奇怪的临界点, 0.99999976146501 (15位精度下的临界点,实际精度更高), 这个值在很多函数都出现了……

277,以前没注意连数据库函数也存在这个临界点……


278,由于类似的特殊临界点造成index等少数函数在参数为极小负数时不会报错而是视为0 （图片数限制见上图）

279,excel有一些特殊的简写方式,   2**7等价2乘10的7次方

280, a1..b10等价a1：b10,这里”.”的数量上限是251(有时似乎不能直接调用但可以先用F9+rept产生251个”.”后正确返回)

281,rand的易失性重算能力能实现一些原本不能实现的数组效果


282,如果有朝一日你看到一个定义名称的公式却在名称管理器找不到这个定义名称,请知道定义名称也是能隐藏的

283,一些特殊的符号和字母是不能用于定义名称的

284,datedif属于隐藏函数不是没有理由的,虽然这个函数的开始日期不能小于结束日期,但在某些情况下的结果仍会是负数


285,中文版隐藏着一些似乎是泰历相关的函数,除BAHTTEXT正常显示外其他均为隐藏函数，且在中文版(即便使用泰文语言补丁)似乎并不能正常返回结果(有大佬说是泰文版的函数,中文版可能没删干净),譬如你输入=thaiyear(1)   ,结果虽然报错但不是name错误

286,借助分数形式有时也能简化公式的

287,注意excel的分数形式上限为32768

288,char(1)是个奇怪的字符,用于search等时也具有通配性质

289,~ 也是个奇怪的字符,作为search1参也能找到全部字符,单个这个字符在这类查找里有时可以被忽略


290,极端的不可见字符使用记事本或word时请知道一件事,在单元格里在字符串右侧,但记事本或word内这个字符有可能在可见字符左侧,这是我在第2季时提到的镜像字符的又一个特性

291,单元格内的某3个单独字符会出现countif直接统计自己的结果为0的情况，这3个字符分别是“>”，“=”，“<”，此外别忘了引用真空单元格

292,虽然上面说了字母有94个，但工程函数等虽然说也忽略大小写但其实也只识别其中的52个常规字母。

293,countif忽略数据类型的性质不只数字有，逻辑值也是

294,PI函数结果的实际精度高于15位,EXP也是

295,单元格内字符串开头的半角单引号如果在单元格内不可见，请注意此时该字符是文本识别符，性质属于格式（强制识别数据类型为文本）而不是字符，故此时你无论用什么函数都无法清除该字符的，强迫症患者请使用格式刷或者清除格式解决。

296,财务函数在非财务岗位有时也是有奇效的


297,少数情况下text格式代码里大小写是有区别的,最简单的例子是字母e

298,虽然countif识别错误值,但不代表它能对错误值判断大小,错误值之间似乎无大小顺序

299,subtitute/replace/find/search对unicode编码大于65535的字符的识别与转化遵循mid的字符编码规则,即也是按照字符编码处理而不是整个字符

300,多维数据集函数的资料极少,目前我只知道用olap多维数据集创建的透视表或者pp关联建模创建的透视表转换为公式的2种环境下能调用该函数

流浪铁匠

199个数字字符集与94个字母如下图





本来没打算做第3季的，肚里墨水太少，但由于2019的出现，函数格局将发生变化，可能大家熟悉的万金油也会退出历史舞台
所以把之前发现的一些偏僻知识、论坛和群里看到的有趣应用、以及为了再凑100条的部分测试发现整理出来又凑了100条，以黑科技与bug为主，报复一下微软     /斜眼笑
感谢群友湖州-憾(桃李春风一杯酒)、李逸雪 等提供的素材，也感谢各个e群前辈日常的吹水素材给我提供的灵感!

丢丢表格

269,提2个没什么用的find和search的区别……


  FIND  与  Search   是有区别的，


  FIND 不支持 通配符， Search 它支持通配查找 。

流浪铁匠

丢丢表格 发表于 2018-12-9 15:13
269,提2个没什么用的find和search的区别……

还在等审核 /抠鼻,,,第3季的精髓在图片
有2个sum公式估计很多人想揍我,哈哈

流浪铁匠

丢丢表格 发表于 2018-12-9 15:13
269,提2个没什么用的find和search的区别……

第2段图片上传完毕
老师请看269条的截图，哈哈

find和search的基本区别我也是清楚的

丢丢表格

流浪铁匠 发表于 2018-12-9 18:02
第2段图片上传完毕
老师请看269条的截图，哈哈

~   就是一个 限制符
它专门 限制通配符为它本身的字符
它对别的字符无效，只相当于空值   ""

流浪铁匠

前2季链接第1季  http://club.excelhome.net/thread-1410664-1-1.html
第2季  http://club.excelhome.net/thread-1413747-1-1.html
1-2季word文档附件在第2季里
第1季主题现在看来为扫盲
第2季为套路

由于会员等级限制每日附件上传数量受限，发帖时图片都不太够，无法及时上传原文word文档现第3季附件上传于本层
由于时间原因，个别条写得不是太满意，由于不会再写这个系列
如果以后有其他特殊发现会继续更新在本帖

beanchan

学习,支持老铁!