Excel小结——函数部分（一）

最近学习《别怕，Excel函数其实很简单》（人民邮电出版社）。主要介绍了Excel最常用的20个函数，主要以win7和Excel2010为写作环境。当然，针对03，07或13，16版，其使用大体是相同的。高版本自不用说，肯定兼容，低版本的话，有些函数可能没有，要实现功能需要细微的“曲线救国”，不过也并无大碍。作者是Excel home团队，这可以说是图书质量的保证吧。本文不做详细的基础功能展开，主要讲解我的学习体会和使用时需要注意避开的坑。

Excel功能比我们想象中强大，我们的技能比自己以为的渺小。我们只用真正掌握最常用的20个函数，就足够高效应付大多数工作了；如果深入掌握50个函数，就可以说相当厉害了，尽管这只占Excel函数总量的1/8左右。很多高手戏说，自己使用Excel多年，却没用上他全部功能的10%，可能吧。我也相信，如果掌握Excel全部功能的10%，一定可以驰骋职场。

先强调几个观点。1、Excel表格可分为数据表和报表。数据表类似于数据仓库，里面的数据是给自己看的，最好不要有任何的统计和计算、分类和汇总、格式设置和更改对齐方式等等（例如，文本格式默认是左对齐，数值默认右对齐，如果人为更改，容易掩饰数据格式错误），不要有任何的修饰（例如合并单元格）；每类数据类型占一列，不同列的数据类型不同。另一类是数据报表。报表是呈现的结果，给别人看的，通过公式与数据表关联，能通过数据表的更改自动更新。2、Excel就像一个设计好的面包机，我们只需要按规定输入原料，操作机器即可，并不需要了解其运作原理和过程。学Excel的函数，相当于只学习机器的使用说明书。机器都设计好了，只要我们学个用法，说明书还不愿意看吗？3、使用函数公式时，最好多用单元格引用，而不是直接输入数据。因为引用单元格的话，方便后期的修改，而不用改动公式本身，更灵活。

还在为低效而沮丧？快来跟我学吧，就现在！

再开始总结函数。Excel函数大体分为：逻辑函数、数学运算和统计函数，文本处理函数，查找类函数，日期与时间函数。

（1）逻辑类函数，用于信息判断，返回true或false。最常用的是IF函数了。一个if函数就是一次选择，只能二选一，其输入格式也较容易理解：=IF(条件判断, 结果为真返回值, 结果为假返回值)。原理很简单，但是如果能多想到、多去用，功能还是很丰富的！有两点值得一提：① 用iserror函数或IFERROR(表达式，出错时的返回值)能屏蔽错误结果。例如，if(iserror(C1/B1)，0，C1/B1)，正常的话显示C1/B1，若有错误，则相似0。②如果要用到if函数的嵌套，为了方便阅读和理清思路，“枝丫”最好往一个方向“生长”。例如：判断学生成绩，IF(B2>89, 'A', IF(B2>79,'B', IF(B2>69,'C', IF(B2>59,'D','E'))))，这样，阅读者思考时不会分散注意力。③and、or函数，分别求”且”和”或”，与if配套非常好用，能减少if嵌套的个数。

（2）数学运算和统计函数，功能比较强大了。最常用的统计函数包括：Sum函数、Sumifs函数，Countifs函数，Average函数、averageifs函数。这三者其实用法都非常相似。这里只以sum函数系列为例：SUMIF(判断区域，条件，求和区域)，注意，第1和第3个参数的相对位置关系一一对应；sumifs(实际求和区域，第一个条件区域，第一个对应的求和条件，第二个条件区域，第二个对应的求和条件，第N个条件区域，第N个对应的求和条件)，它是2007版及之后对sumif函数的升级版，真的是一个很棒的函数，可以设置127个求和条件，有了它，甚至可以放弃sumif啦。Count系列和average系列非常类似，具体可百度。

有几点要注意：①这三类函数，若参数为单元格引用，如=sum(A2：A10)，Excel会忽略文本、逻辑值和空单元格，但不可以有错误值；当然，若将文本和逻辑值直接设为函数的参数，如=sum(1,2,”abc”,true)，函数不会忽略。②使用sumif函数，可以处理包含错误值的区域，例如，输入=SUMIF(G1:G17,'<=9e+307')，这里，用9e+307（9×10的307次方）这一接近excel处理上限的数值，来避开逻辑值。因为逻辑值比所有数值都大。③有时，输入=sum(g1:g17,'>=I2')这种形式会报0，但是数据区域看上去是正常的。原因可能是，区域内数据不是数值类型，其中有一些看不到的字符，如空格等，所以就无法求和，显示结果为0。④sumif函数为例，第三个参数和第一个参数是一一对应关系，这里的对应，是指相对位置的对应，如影子一般，跟随着平移。Sumif函数允许第三个参数与第一个参数区域尺寸不匹配，会自动以所输入的求和区域最左上角的单元格为起点，扩展至能与第一个参数区域现状匹配，但是小心sumifs函数不能自动智能扩展区域。⑤注意区分count函数：用于计算区域中，包含数字的单元格的个数。使用格式：count(数组、单元格引用或单元格区域)，不要和条件计数countifs函数弄混淆了！另外，注意：不自己写，永远不知道雷区这么多。比如，要求非空单元格的个数，正确：=COUNTIF(B30:B36,'<>''')或正确：=COUNTIF(B30:B36,'=6')，错误：=COUNTIF(B30:B36,<>'')。

这里，值得一提的是对空单元格的理解。真空单元格：是指该单元格没有任何内容，没有公式、没有透明的字符、没有不可见的符号。 假空单元格：是专指用公式得出的空单元格，是指用公式设定的空值，即单元格不显示、有公式无内容。真空与假空的共性：都可以用''来表示。注意：='' 是假空，=' ' 不是假空，虽然同样看不到内容，但是这是得出一个空格字符，用LEN(A1)统计则得值为1。非真空：就是指不是真正的空单元格，其包含了假空和有内容的单元格，这两种都不是真空单元格，说白了就是单元格写入了内容，不管是公式还是其他内容。

Excel中的取舍函数。数值取舍分为3种：一是通过设置单元格格式，改变了数据的显示方式，但是并没有改变数据本身；二是四舍五入取舍，改变了数据，包括：ROUND函数；三是非四舍五入取舍，包括：INT，TRUNC函数。ROUND函数还包括Roundup函数：远离零值，绝对值增大舍入，和rounddown函数：指靠近零值，绝对值减小的方向，向下舍入。TRUNC函数直接按位数截取，INT函数对数值进行向下舍、取到最接近的整数。