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

1、学习函数你可能还不知道的100件事第1季 by 铁匠学习函数你可能还不知道的100件事1,F1是学习函数用法最好的老师,F9是学习函数套嵌和数组思维最好的老师2,F1的帮助介绍极个别是有错误的,F9有个克星叫多维引用3,绝对引用和相对引用是函数基础,俗称任督二脉,不会这个,写公式的工作量和不会公式差不多,或许更多4,CONCATENATE可能是所有函数中最废柴的一个,因为它的功能完全能用&替代5,2016版新增的concat和textjoin函数是这个版本公认的2个实用函数,因为这是在目前函数阶段唯二支持文本型内存数组合并的函数6,char和code函数是并不能完全相互转化。7,code的结果

2、为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,没有什么函数是简单的,任何一个函数深入,都能发现很多有意思的内容。譬如你不测

3、试,能直接回答 =if(,) 的结果是多少吗?12,没有最强的函数,只有把适合的函数放在适合的地方13,函数入门是为了解决工作,但仅学到这个阶段来解决一些相对复杂的函数问题,我的感觉就是学了一二三后写万字,我见过5/60层if套嵌的14,函数中级是为了找到更易于维护和相对简洁的公式来解决工作,这个阶段的学习请暂时忘掉辅助列15,函数高级是一群探路者在寻路,这个阶段俗称烧脑,见过不少前辈为了省1个字符废寝忘食的16,很多函数的参数在参数为小数会具有自动取整性,譬如offset你不可能引用2.9个单元格大小的区域17,三大引用函数中,index是唯一支持数组的18,另外2个引用函数offset和i

4、ndirect虽然不支持数组,但这2个函数是支持多维引用的19,引用和数组是两个概念,支持引用的函数,不一定支持数组, 支持数组的函数,也不一定直接支持引用(部分日期类函数,需要把引用的日期区域加*1等转化为数组才能进行数组运算,直接引用区域会报错)20,and和or函数具有聚合性,故在数组的多列判断条件中一般用 * 作为且的关系(替代and),+ 作为或的关系(替代or),而列数较多时熟练使用mmult和多维引用是可以简化公式的21,lookup/sumproduct/mmult/frequency等函数本身进行的就是数组运算,所以经常不需三键就可以正确返回结果的22,一个被很多人忽略的函数

5、,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参数在为小数

6、时的舍入规律很奇怪,测试后发现貌似以小数点后第7位决定,且不是常规舍入规则,舍入点在该位数字为7-8的范围区间,所以建议慎用。28,countif的二参数是否存在比较判断符,等时,对数字(文本型数字,数值)的统计会存在明显的格式识别差异。(题外话:countif这个函数对纯数字和文本的统计规则有很大差异，这也是不少人身份证号统计出错的原因和解决方法(&”*”转成文本)29,最大数字一般有9323和9E+307 两个写法,在极少数情况下是有差别的。不过一般取不到这么大的数,很多时候99就行30,函数公式中一般认为最大的汉字是“”char(41385) , 很多时候会用座替代,最小的汉字是吖,这2

7、个字在判断和提取字符中经常出现31,vlookup拥有2种查找机理:遍历法和二分法,用第4参数决定,比他更多的是match,有3种。32,vlookup的模糊匹配这个名字误导了很多新手,我觉得叫范围取值更适合。一般人需要的模糊查找,请XX通配符用法33,vlookup在函数插入框的参数介绍,4参数的翻译直到2010版还是错的,误导了很多新手(题外话,我当年只会vlookup的时候就是)34,vlookup据说是使用频率最高的函数之一,但它的兄弟hlookup知道的人却少很多35,vlookup可以反向查找,可以找最后一个(精确匹配时),可以一对多,前提是你会重构数组36,vlookup很强大,

8、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的常规公式(不用数组三键),在借助其他

9、函数的配合下也是能完成一对多,去重,按指定次数重复,查找指定内容的第n次对应内容,提取字符等等效果的40,lookup三兄弟同时具有查找与引用的能力,lookup最灵活,但没有遍历法的查找能力,想真正理解这个函数请参考校长二分法的相关文章介绍。41,lookup的基础用法一定要记得2参数是需要升序的(关于这个问题已经见过太多新手忽略这个细节,特别是文本时的)42,Excel存在几个隐藏函数,datedif/numberstring/datestring,早期版本你根本找不到这几个函数的帮助介绍的,但不意味着你不能使用。43,datedif使用M/MD/YD等作为参数时统计规律较复杂且据说有版本

10、差异,结果可能不是你想要的,如果对这类统计的精确性要求很高,请慎用这函数或注意修正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,涉及时间日期的计算,时间是小数,日期是整数,理解这个,很多涉及日期时间的计算完全可以用数学思路

11、解决的48,数据源存在合并单元格时,公式的长度和难度会有大幅度提升,所以除了熟悉公式/工作被逼外,请尽量避免使用合并单元格49,格式刷假合并对函数新手来说是合并单元格的救星50,lookup可以拯救合并单元格,用lookup填充合并单元格结果产生内存数组的思路解决合并单元格相关统计问题是相对常见的思路。51,工作中规范数据源比能熟练掌握数组公式更重要,很多经典公式的思路都是被不规范的数据源逼出来的。52,学会数组你会发现sum不仅仅是个简单的求和函数的53,sum+三键配合其他函数可以达到很多你以前想象不到的求和效果的,隔列/多列/累计/条件/阶梯/先进先出/斜线等等,实在是太多了54,见过不

12、少自称擅长函数的却连数组三键是ctrl+shift+enter都不知道的55,sumproduct在很多时候可以达到sum数组一样的效果而且是不用三键的,但不代表这2个函数能100%相互替代56,sum数组公式用途很多,但简单的条件求和使用 sumif 运算会更快57,sumif 对二参数做文章,可以在一定程度上达到多条件求和的效果的。58,sumif不支持跨簿,或者说支持跨簿的函数不多,涉及跨簿的问题尽量不要使用函数方式59,sumif在三参数缺省的情况下具有类易失性效果,对表格的操作会引起自动重算,数据量大则影响效率,所以除了拼字符或本身需要这个自适应范围效果外,请尽量把三参数写全60,s

13、umif这类if后缀的数学函数,对条件的判断有时并不像sum数组一样有效识别文本型数字和数值的差异的61,这类if后缀的数学函数,1参数只支持引用,不支持数组,涉及内存数组的问题有时很难用这类函数解决62,一代版本一代神,每个版本都有新增函数,有雪中送炭的,有锦上添花的,使用新增函数请考虑对方的版本。一些低版本很麻烦的问题用高版本函数可能很简单63,一些函数可以改变原数组的结构,mmult/small/frequency/mode.mult等等64,个别函数只存在于特殊环境,像数据透视表的GETPIVOTDATA函数,图表的SERIES函数,以及多维数据库函数等等65,数据库类函数有很高的数据

14、结构要求,而且多数数据库函数很难或不能实现支持批量下拉的。不过在规范的数据结构下条件复杂使用数据库函数统计公式会相对简洁的。66,在一些特殊结构下,数据库函数/工程函数/三角函数/复数函数等具有奇效,或许能扩展思路以及简化公式67,一些特殊的函数,本身用途相对独特,例如trimmean,例如median,即便不会也能解决问题,但学会了在一些特殊应用下公式会相对简洁。68, n函数是最短的函数,唯一能和它相较量的只有t函数,这2个函数虽然短,但在一些特殊情况下很有用。69,函数入门必学sum,if,vlookup,所谓的函数三板斧。这3个函数将伴随你整个函数生涯。70,函数高级必须熟悉掌握mmu

15、lt,lookup,frequency,mode,text等等诸多函数71,那些连财务函数,工程函数,数学/统计函数,多维数据集函数(非该行业前提)都能熟练掌握的,才是函数大神72,text函数据说是可以学一辈子的73,函数进阶后建议补习下中学数学,因为你会发现很多问题的解决思路都是转化为数学问题处理的。74,权重法是常用的函数数学处理思路,经常会和small,large,mode等函数配合使用75,index+small+if+row/column 是函数中少有的具有专门称呼的套路(万金油),因为它的用途很多。熟悉后你会发现这也是数学思路的一个应用。76,indirect+text+r1c1

16、引用也是一类常见的数学转化思路77,多学习别人的思路,有些思路和用法可能是自己一辈子都想不到的。78,不是所有的函数都支持自动重算,譬如cell,譬如info。79,宏表函数不一定需要借助定义名称使用的,用ctrl+F11创建的宏表里面,宏表函数可以像工作表函数一样直接使用,缺点是必须选择区域使用ctrl+enter来批量填充和刷新80,合并单元格中是可以使用数组公式的: 1,使用本身支持数组运算的函数来避免使用三键; 2,借助定义名称; 3,取消合并三键录入公式再设置合并; 4,格式刷假合并。方法很多,不过难度差异很大81,很多函数的参数是可以缺省的,但各个函数仅保留逗号的缺省方式,不都识别

17、为0。vlookup第4参数和很多舍入函数的第2参数仅保留逗号会被视为0,但substitute等文本函数仅保留逗号不识别为0,fixed/eomonth/offset等函数写不写0的结果是不一样的82,find和search的三参或许是最容易被人忽略的,但不代表没有用。83,逻辑值和数值是有差别的,但有些函数的参数可以直接把逻辑值视为数字1和0,例如left,right,rept等函数的二参,以及直接键入在参数里的sum等函数84,sum和count等函数忽略参数为引用或数组里存在的文本型数字,但直接作为参数键入的文本型数字是被统计的 85,文本型数字和数值的差别不一定仅仅是只多一对半角双引

18、号(和格式差异),数字两端存在多余空格时的文本型数字,也是能进行四则运算的。而且部分统计函数忽略数字两端存在多余空格这个特殊差异的86,一组半角双引号()是文本字符串必须的,里面如果没内容就是假空(空文本),很多函数都能产生空文本,但函数无法产生真空的。87,假空(空文本)不可怕,可怕的是不知道什么是假空以及如何处理假空来避免统计出错。88,Excel需要2个半角双引号来识别为1个半角双引号符号的,所以想要在单元格直接显示1(含引号的),公式是 =1 ,一共6个双引号89,熟练掌握一些小技巧 0& , 519 , 1/17 , % , 1=1 ,是可以简化公式的,你可以不用,但希望能知道别人为

19、什么这么写90,Excel里面有一个情况叫浮点误差,这是由于计算机的进制转换和excel的数值精度限制等因素产生的,此时自己套嵌round修正精度吧91,空格的一个用法是作为交叉运算符的,当然这个使用频率较低,但不代表没用。92,区域运算符你们都见过: 冒号,不过=SUM(E1:E9:G2:H7) 这个公式的求和区域是什么你们知道吗?93,弄清=SUM(E1:E9 D3:G6,G9) 这个公式的求和区域,才算是理解各种引用运算符。94,很多函数在高版本时是有改进的,像if的套嵌在从7层到07版时的64层,sum的30个参数变成了255个,等等。95,循环引用不一定代表是错的,不过普通用户的循环

20、引用一般是公式的逻辑思路错误。循环引用的正确做法是要和迭代运算配合的。96,所有的文本函数都有个副作用是会把数据源的数值变成文本型的,后续有计算要求记得用*1,+0,-,value等等方式转化为数值。97,具有求和能力的函数有sum,sumif,sumifs,sumproduct,dsum,imsum,seriessum,sumsq,sumx2my2,sumx2py2,sumxmy2,sumsq,subtotal,aggregate,mmult 等等,知道的函数越多,一个问题你的解法也就更多。98,工作中如果需要使用多个连续的相同规律的数组公式,建议以区域数组方式录入,能减少运算量。99,透视表区域/图表区域等功能也是可以使用函数公式的,但不能直接使用,需要借助定义名称。100,Excel函数知识博大精深,我只是个没入门的渣渣,这100条总结主要是学习函数以来的很多总结和自己的一点发现,但区区100条内容连函数的皮毛都算不上的。