Excel统计 Sum()函数的妙用.docVIP

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 两函数，简单的统计工作就会搞定。 1