报表基础函数全接触之三.docVIP

报表基础函数全接触之三 　　通常做出工作报表之后需要做一些查询和统计排名的工作，上期我们介绍了如何查询数据库，这期我们一起来了解几个常用的ExceI统计排名方面的函数，让工作事半功倍。 　　 　　特定值统计 　　例如要计算某位选手的成绩，应该求评委给的平均分。但如何排除无效分数呢?这要用到COUNT函数。在表一中，B12单元格输入公式“＝COUNT(B2:B11)”，回车后就可以得到了。COUNT函数统计的是指定单元格区域中包含数字的单元格的个数。所以本例中的两个“FALSE”单元格就不会统计在内了。这样的例子还有很多，比如学校计算班级某学科的平均分时，也是应该将注明缺考的学生扣除，用COUNTA函数就行了。公式“＝COUNTA(B2:B11)”统计的就是B2:Bll单元格区域内非空单元格的数量。 　　实际应用中还需要找出最高分、最低分，那也简单。公式“=MAX(B2:B11)”可以得到B2:B11区域中的最高分，“＝MIN(B2:B11)”可以得到最低分。如果用“=LARGE(B2:Bll.3)”还可以得到从高到低指定名次的分数，第二个参数“3”就是指定的名次了。而“＝SMALL(B2:Bll.3)”则可以得到从低到高指定名次的分数(第三低分)。 　　 　　频率统计 　　六月到了，不仅是学校统计期末成绩，各个单位也要做半年总结，按数据段统计人数是非常重要的。比如要统计单位员工各年龄段人数，这就是频率统计。借助函数，这个工作也会简单很多。 　　在表三中学科成绩分布在B2:B18单元格区域。我们在D2:D9单元格区域设置各分数段，并在E2:E9单元格区域设置各分数段的分界点(上限)。然后选中F2:F9单元格区域，在编辑栏中输入公式“＝FREQUENCY($B$2:$B$18,$E$2:$E$9)”，再按下“Ctrl＋Shift＋Enter”结果就出来了。这个组合键是对函数返回的数组进行运算，表三中C2单元格的那对大括号是不能手工输入的，按下组合键后自然出现。 　　其实，用前面介绍的COUNTIF也可以进行频率统计，不过要手工编辑公式。比如统计80－89分数段人数。那么在相应单元格输入公式“＝COUNTIF($B$2:$B$18,＝80)-COUNTIF($B$2:$B$18,＝90)”，直接回车也可以得到结果。公式的解释相信你一定也会，那就是用单元格区域中大于等于80分的人数减去大于等于90分的人数，得到的当然就是80～89分的人数了，呵呵! 　　 　　排定名次 　　比赛成绩出来了，应该给参赛者排定名次。分数相同的话，他们的名次也应该是一样的。这要在Excel中实现也很容易――RANK函数就是干这个的。 　　在相应单元格输入“=RANK(B2,$B$2:$13$12.0)”，回车后就可以得到该数据的名次。公式中第一个参数指的是为哪个数据排名次，第二个参数是指在哪些数据中排名次，本例中该单元格区域为绝对引用是为了向下复制公式时不致出错，而第三个参数则是指定升序还是降序，“0”或忽略不写都是降序，而非零则为升序。 　　 　　表间查询 　　生活和工作中的报表之间经常会有很紧密的联系。在新的工作表中，我们可能会需要另一个工作表或者工作簿中的原有数据。怎么办?打开那个工作表然后复制粘贴吗?那么顺序不一样怎么办?数据量太大怎么办?这些问题，用上VLOOKUP函数，一切都会变得简单的。 　　在表四中，笔者把源工作表与新表放到了一起，源工作表在上方，而新表在下方。我们要做的，是根据在A14单元格输入的姓名，从源工作表中查询相应的数据。如果要查询“张2”所属科室，只要在B14单元格输入公式“=VLOOKUP($A14,$A$2:$I$10.2.FALSE)”，回车即可。 　　如果弄懂了VLOOKUP函数的语法规则，那么它就可以帮我们做很多事了。简单说VLOOKUP的语法规则是VLOOKUP(参数1，参数2，参数3，参数4)。参数1是要查询的数据，本例中要查的是“张2”。参数2则是告诉我们在哪些区域的第一列查询这个数据。所以，应该注意的是，要查询的数据应该位于数据列表中的第一列。本例中则为“A2:I10”单元格区域，其中A列即要查询数据(张2)所在列，位于这个区域的第一列。参数3是告诉我们希望返回的结果位于数据区域的第几列。参数4则是告诉我们是精确匹配还是大致匹配，FALSE是只返回精确匹配值。 　　有点模糊?看起来这个函数很复杂，但它的用处很大。其实简单地说上面我们所写的公式的意思是：在A2:110这个单元格区域的第一列查找一个叫做“张2”的人，如果找到了，那就把该区域它所在行从左至右的第二个单元格的数值给显示出来，如果找不到，那就给个错误提示。现在明白了吧?所以，要查找“张2”的住房公积金，那就应该写出公