学习函数你可能还不知道的100件事(第1季) by 铁匠

学习函数你可能还不知道的100件事

1,F1是学习函数用法最好的老师,F9是学习函数套嵌和数组思维最好的老师

2,F1的帮助介绍极个别是有错误的,F9有个克星叫"多维引用"

3,绝对引用和相对引用是函数基础,俗称"任督二脉",不会这个,写公式的工作量和不会公式差不多,或许更多

4,CONCATENATE可能是所有函数中最废柴的一个,因为它的功能完全能用&替代

5,2016版新增的concat和textjoin函数是这个版本公认的2个实用函数,因为这是在目前函数阶段唯二支持文本型内存数组合并的函数

6,char和code函数是并不能完全相互转化。

7,code的结果为63的unicode字符,利用遍历思路统计其数量有100万+个,比code的结果不是63的其他所有unicode字符的总数之和还多!

8,函数里等号的判断并不精准:对真空单元格a1,=a1=0,=a1="" ,这2个公式的结果都是true

9,等号也不能区分大小写,Excel能区分大小写的函数不多,exact,find(b),substitute,code,unicode等

10,工作表函数的能力和它所在的环境有关,像phonetic的文本合并,只支持中文版;lenb只在语言环境为中文,日文,朝鲜文时和len函数有差别

11,没有什么函数是简单的,任何一个函数深入,都能发现很多有意思的内容。譬如你不测试,能直接回答=if(,) 的结果是多少吗?

12,没有最强的函数,只有把适合的函数放在适合的地方

13,函数入门是为了解决工作,但仅学到这个阶段来解决一些相对复杂的函数问题,我的感觉就是学了一二三后写"万"字,我见过5/60层if套嵌的

14,函数中级是为了找到更易于维护和相对简洁的公式来解决工作,这个阶段的学习请暂时忘掉辅助列

15,函数高级是一群探路者在寻路,这个阶段俗称烧脑,见过不少前辈为了省1个字符废寝忘食的

16,很多函数的参数在参数为小数会具有自动取整性,譬如offset你不可能引用2.9个单元格大小的区域

17,三大引用函数中,index是唯一支持数组的

18,另外2个引用函数offset和indirect虽然不支持数组,但这2个函数是支持多维引用的19,引用和数组是两个概念,支持引用的函数,不一定支持数组, 支持数组的函数,也不一定直接支持引用(部分日期类函数,需要把引用的日期区域加*1等转化为数组才能进行数组运算,直接引用区域会报错)

20,and和or函数具有聚合性,故在数组的多列判断条件中一般用* 作为且的关系(替代and),+ 作为或的关系(替代or),而列数较多时熟练使用mmult和多维引用是可以简化公式的

21,lookup/sumproduct/mmult/frequency等函数本身进行的就是数组运算,所以经常不需三键就可以正确返回结果的

22,一个被很多人忽略的函数,aggregate,一参数为14-19时,在一定程度上本身也是支持数

组运算的。

23,aggregate是我见过的函数参数变化组合最多的函数(text+格式代码不算),有19*8=152种统计效果。

24,subtotal是07版和以前版本唯一支持忽略行隐藏数据的统计函数,直到2010出现了aggregate。

25,aggregate在多维引用思路下无法产生内存数组,这造成了aggregate无法完全替代subtotal。

26,第1个发现lookup的二分法规律并写出lookup(1,0/(条件)…… 结构的人,是函数界大神,如果哪位知道是谁,请告诉我,我要抱大腿

27,index的2/3参数在为小数时的舍入规律很奇怪,测试后发现貌似以小数点后第7位决定,且不是常规舍入规则,舍入点在该位数字为7-8的范围区间,所以建议慎用。

28,countif的二参数是否存在比较判断符">","<"等时,对数字(文本型数字,数值)的统计会存在明显的格式识别差异。(题外话:countif这个函数对纯数字和文本的统计规则有很大差异，这也是不少人身份证号统计出错的原因和解决方法(&”*”转成文本))

29,最大数字一般有9^323和9E+307 两个写法,在极少数情况下是有差别的。不过一般取不到这么大的数,很多时候9^9就行

30,函数公式中一般认为最大的汉字是“々”char(41385), 很多时候会用"座"替代,最小的汉字是"吖",这2个字在判断和提取字符中经常出现

31,vlookup拥有2种查找机理:遍历法和二分法,用第4参数决定,比他更多的是match,有3种。

32,vlookup的模糊匹配这个名字误导了很多新手,我觉得叫"范围取值"更适合。一般人需要的模糊查找,请百度通配符用法

33,vlookup在函数插入框的参数介绍,4参数的翻译直到2010版还是错的,误导了很多新手(题外话,我当年只会vlookup的时候就是)

34,vlookup据说是使用频率最高的函数之一,但它的兄弟hlookup知道的人却少很多35,vlookup可以反向查找,可以找最后一个(精确匹配时),可以一对多,前提是你会重构数组36,vlookup很强大,index+match更强大,index+small+if+row组合比二者还强大,当然这里说的是常见用途

37,vlookup+if({1,0},)的重构数组思路,在一些教程里是作为经典案例的,但实际自从lookup的套路平民化后，vlookup的if({1,0},)反向思路仅适合学习数组思路,实际工作并不建议(因为重构数组运算量大,区域数组除外)

38,vlookup的4个参数都可以使用数组,1参数的最复杂,需要借助n/t+if({1},)结构转化,如果1参数只是单纯的引用区域的话,无法形成内存数组,此时其实是绝对交集用法

39,深入学习后你会发现,即便是vlookup的常规公式(不用数组三键),在借助其他函数的配合下也是能完成一对多,去重,按指定次数重复,查找指定内容的第n次对应内容,提取字符等等效果的