EXCEL条件求和与条件计数函数在学生成绩统计分析中的应用(原文)

EXCEL条件求和与条件计数函数在学生成绩统计分析中的应用

古蔺县蔺阳中学薛亮

在学生成绩的统计与分析中，需要用到EXCEL条件求和函数和条件计数函数的地方非常之多，可以用的函数和方法也很多，但是有的简单有的复杂。我们怎样才能事半功倍，用最简单的方法解决复杂的问题呢？现就个人在EXCEL条件求和和条件计数方面的一些体会，用一些实际操作的实例跟大家分享一下。

一、单条件计数——COUNTIF函数

1.语法格式：=COUNTIF(区域,条件)

2.函数作用：用来计算区域中满足给定条件的单元格的个数。

例如：在上面的成绩表中要统计1、2、3、4班的参考人数。也许有人会在C10、C11、C12、C13中分别输入公式“=COUNTIF(B2:B7,"=1")”、“=COUNTIF(B2:B7,"=2")”、“=COUNTIF(B2:B7,"=3")”、

“=COUNTIF(B2:B7,"=4")”，这样做当然没有错，是常规方法，但是如果是30个班、40个班或者更多呢，那就有点麻烦了。我们其实有更简单的方法——直接引用班级单元格地址。首先，在C10中输入公式

“=COUNTIF($B$2:$B$7,"="&B10)”，然后双击C10右下角填充句柄复制公式就可以了，C11、C12、C13中公式就直接变成了“=COUNTIF($B$2:$B$7,"="&B11)”、“=COUNTIF($B$2:$B$7,"="&B12”、“=COUNTIF ($B$2:$B$7,"="&B13)”。因为B2：B7这个区域是固定不变的，为了复制公式时不发生变化，我们就直接写成绝对引用形式$B$2:$B$7。而后面的条件部分，我们直接用相应的单元格地址"="&B10代替了班级1，复制公式时自动变化，从而简化了操作，方便复制。但是一定注意三点：1.关系运算符必须用英文的" "撇起来，2. 关系运算符和单元格地址之间必须用&连接。3.注意单元格地址的相对引用和绝对引用。

二、多条件计数——SUMPRODUCT函数

1.语法格式：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))

2.函数作用：统计同时满足条件1、条件2到条件n的记录的个数。

3.多条件计数实例一——成绩表中班级单科入围人数统计，直接用单元格地址代替班级数据和入围分

数据。

例如：在上面的成绩表中要统计1、2、3、4班的各科的单入围人数。我们首先分析一下要满足的条件：1.班级数据区域对应的数据必须等于相应的班级；2.各科成绩区域对应的数据必须大于或等于相应的入围分。对于这种多个条件的计数，我们按照SUMPRODUCT函数的语法格式，我们可以直接在C11中输入“=SUMPRODUCT((B2:B7=1)*(D2:D7>=110))”,求出1班语文科的单入围人数。但是七科4个班就必须输入或修改28次公式，如果是更多的班级呢，这也太复杂了吧。我们完全可以吸取上面COUNTIF函数使用时的经验——直接引用相应的单元格地址代替相应数据，可以直接在C11中输入公式“=SUMPRODUCT(($B$2:$B$7=$B11)*(D$2:D$7>=C$10))”，公式说明：($B$2:$B$7=$B11) 指定条件——班级数据区域对应的数据必须等于相应的班级（地址），因为班级数据区域不变，所以用绝对引用，而后边的班级1、2、3、4班都在B列，在复制公式时必须保证班级单元格地址的列不变，加$；(D$2:D$7>=C$10)指定条件——语文科成绩区域对应的数据必须大于或等于语文科的入围分（地址），因为所有科目的数据都是从第2行到第7行，而所有科目的入围分都在第10行，在复制公式时必须保证这些行都不变，加$。在C11中输完公式后向右和向下复制公式就直接把1、2、3、4班的语文、数学、外语、政治、历史、地理、总分的单科入围情况统计出来了，再多的班级和科目也就输入一次公式就可以了。但是也要注意几点：1. SUMPRODUCT函数的条件用（）括起来并用*相连，不要忘了最外层括号。2. 关系运算符直接连接数据区域地址和单元格地址，而不需要" "和&。3.特别注意单元格地址的相对引用和绝对引用。

例如：在上面的成绩表中要统计1、2、3、4班的各科和总分的双入围人数。我们还是先来分析一下要满足的条件：1.班级数据区域对应的数据必须等于相应的班级；2.各科成绩区域对应的数据必须大于或等于相应的入围分。3.总分数据区域对应的数据必须大于或等于总分的入围分。跟单入围统计相比，仅仅多了一个条件而已。因此，直接在C11中输入公式“=SUMPRODUCT(($B$2:$B$7=$B11)*(D$2:D$7>=C$10) *($J$2:$J$7>=$I$10))”，然后向右和向下复制公式就OK了。

三、单条件求和——SUMIF函数

1.语法格式：=SUMIF(条件数据区域,条件,需求和区域)

2.函数作用：根据指定条件对若干单元格求和。

例如：在上面的成绩表中要统计各班的单科的平均分。我们可以先求出各班的单科的总分，再求出各班的人数，最后将各班的单科的总分除以各班的人数，这就得将SUMIF和COUNTIF两个函数结合起来用。我们直接在C10输入公式“=SUMIF($B$2:$B$7,"="&$B10,D$2:D$7)/COUNTIF($B$2:$B$7,"="&$B10)”，然后向右和向下复制公式就可以了。公式说明：SUMIF($B$2:$B$7,"="&$B10,D$2:D$7)——如果B2：B7中有等于B10（即1）的单元格，那么就求D2：D7中的对应的记录的和。COUNTIF($B$2:$B$7,"="&$B10)——求B2：B7中等于B10(即1)的单元格个数。

实际上，SUMIF函数的用法和COUNTIF一样，只不过在后边多了一个需求和的数据区域而已。SUMIF

函数直接引用单元格地址也和COUNTIF一样要注意：1.关系运算符必须用英文的" "撇起来，2. 关系运算符和单元格地址之间必须用&连接。3.注意单元格地址的相对引用和绝对引用。