Excel统计Sum函数的妙用.docVIP

PAGE  PAGE 2 Excel统计中Sum（）函数的妙用 甘肃渭源一中 姜学治 748200 Excel 统计中大家最熟悉、用得最频繁的函数就算sum()函数了，但是用它来做多条件的统计，用的人还是较少的，本人在成绩统计、单位报表统计中发现sum()函数能实现大多数的统计任务，在简单的统计中用它就足够了，如在成绩统计中的均分、实考人数、及格人数、优良人数的统计等；报表统计中某年龄段内的人数、学历、性别等多种条件的计数统计都能很容易的实现。下面是本人的一些SUM（）函数的用法，愿与大家交流。 如图一所示班级次序是打乱的，成绩有缺考的，也有作弊为0分的，现在我们要统计的任务是①求各班各科的均分（缺考人数不统计内），②实考人数③及格率④优良率等。 准备工作 先进行名称定义。因为班级较多，每班人数又较多的话，直接在公式中使用单元格区域，显得公式复杂，也容易出错，因此建议公式中使用名称定义。 以班级为名称的定义方法如下： 点击[插入]—[名称]—[定义]，在名称一栏中输入班级，在引用位置栏中输入=Sheet1!$i$2:$i$8，最后确定。同样办法进行性别、语文、物理、数学、信息各字段的名称定义。 二、输入公式进行计算 实考人数 实考人数的统计要求考虑0分的而不考虑空缺的，而count()和countif()两函数对单条件的统计非常方便，但是多条件的统计显得力不从心（当然与其它函数嵌套使用，也能实现多条件的统计，就是公式较复杂而已）。 例1：求1班语文的实考人数 公式为 = sum((班级=1)* not(语文=””))，然后压shift + ctrl + enter 转换为数组(后面的公式都要压shift + ctrl + enter进行数组的转换，这一步书写后面会省掉)。 2、均分计算 均分计算大家常用的是Average()函数，但这个函数在班级序号打乱的情况下，进行统计会显得无力，若进行嵌套，会把空当0计算，因而不适合某些单位的计算要求，此时用sum()函数就会很容易的实现。 例2：求1班语文的均分（均分=总分÷实考人数） 求总分的公式为= sum((班级=1)* (语文))，实考人数公式由例1已知。 所以均分的公式为 = （sum((班级=1)* (语文))）/ sum((班级=1)*not (语文=””)) 3、及格人数 例3：求1班语文的及格人数（及格标准为60） 公式为 = sum((班级=1)* (语文=60)) 4、及格率 例4：求1班语文的及格率（及格标准为60） 及格率=（及格人数/实考人数）*100%（引用例1和例3的单元格直接相除） 5、优良数 例5：求1班信息科的优良数（统计A和B的总数） 公式：= sum((班级=1)* (信息=”A”)) + sum((班级=1)* (信息=”B”)) 再除实考人数，就会求出优良率；同理可求出合格率。 6、男、女生数 例6：求2班的男生数 公式：= sum((班级=2)* (性别=”男”)) 7、分数段内人数 例7：求1班语文大于等于60分，小于90分的人数。 公式：= sum((班级=1)* (语文=60)* (语文90)) 8、计算年龄段内的人数 如图二，首先要计算年龄，这儿要用到一个Excel的隐藏函数datedif(),在E2单元格中输入=datedif(d2,today(),”y”)，然后用“填充柄”向下复制到下面的单元格中。 注：datedif()函数中Y代表两日期间的年数，M代表两日期间的月数，D代表两日期间的天数。 例8：计算在20岁和30岁之间的女生人数 公式：=sum((性别=”女”)*(年龄=20)*(年龄=30)) 例9：计算学历为本科的20岁和30岁之间的女生人数 =sum((学历=”本科”)*(性别=”女”)*(年龄=20)*(年龄=30)) 最后设计统计表，用此函数求出各班、各科的的统计??。一般中、小学的统计再加上Max()、Min()两函数，简单的统计工作就会搞定。