本帖最后由 流浪铁匠 于 2018-12-9 17:37 编辑
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以前多数常规需要数组三键的数组公式借助上述等特殊函数都能不三键实现结果
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-12-9 14:46
楼主
