本帖最后由 心电感应 于 2018-4-28 12:15 编辑
函数二三年,也从一个小白成长到现在的水平,列了一些函数新手可能不知道的函数知识点
希望对新手有所帮助,谢谢
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,引用和数组是两个概念,支持数组作为参数的函数,都可以使用引用,但反之则不一定
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次对应内容,提取字符等等效果的
40,lookup三兄弟同时具有查找与引用的能力,lookup最灵活,但没有遍历法的查找能力,想真正理解这个函数请参考校长二分法的相关文章介绍。
41,lookup的基础用法一定要记得2参数是需要升序的(关于这个问题已经见过太多新手忽略这个细节,特别是文本时的)
42,Excel存在几个隐藏函数,datedif/numberstring/datestring,早期版本你根本找不到这几个函数的帮助介绍的,但不意味着你不能使用。
43,datedif使用"M"/"MD"/"YD"等作为参数时统计规律较复杂且据说有版本差异,结果可能不是你想要的,如果对这类统计的精确性要求很高,请慎用这函数或注意修正
44,Excel有个错误日期1900-2-29的,在没有使用1904日期系统前提的话这个错误日期是可以被日期类函数识别的。
45,在一些涉及日期的计算中,1900-1-0也可以被视为日期的
46,Excel里面的最大日期是9999-12-31,由于excel的日期是1900-1-0到当天的序列号,9999-12-31即2958465是一个7位数,所以20170814这种8位数不经过处理,永远是excel无法识别的日期的
47,涉及时间日期的计算,时间是小数,日期是整数,理解这个,很多涉及日期时间的计算完全可以用数学思路解决的
48,数据源存在合并单元格时,公式的长度和难度会有大幅度提升,所以除了熟悉公式/工作被逼外,请尽量避免使用合并单元格
49,格式刷假合并对函数新手来说是合并单元格的救星
50,lookup可以拯救合并单元格,用lookup填充合并单元格结果产生内存数组的思路解决合并单元格相关统计问题是相对常见的思路。
补充内容 (2018-5-1 16:17):
抱歉，今天群里聊weeknum1参发现点问题,19条更正下
支持数组的函数,不一定直接支持引用,而支持引用的函数,也不一定支持数组
(weeknum一参不直接支持引用一个区域a1:a10,但可以识别--a1:a10等很多方式产生的内存数组 |
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-4-28 09:38
楼主
发表于 2018-4-28 20:42