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.注意单元格地址的相对引用和绝对引用。

四、多条件求和——SUMPRODUCT函数

2.函数作用：汇总同时满足条件1、条件2到条件n的记录指定区域的和。

3.多条件求和实例——各班上线学生的单科平均分统计，同一函数多种功能混合使用。

例如：在上面的成绩表中要统计各班上线学生（总分600分上线）的单科平均分。我们可以先求出各班上线学生的单科的总分，再求出各班的上线的人数，最后将各班上线学生的单科的总分除以各班上线的人数，这就得将两个SUMPRODUCT两个函数结合起来用。各班语文上线学生的单科的总分的计算：“=SUMPRODUCT((B2:B7=B12)*(J2:J7>=$I$10)*I2:I7)”；各班上线人数的计算：“=SUMPRODUCT((B2:B7 =B12)*(J2:J7>=I10))”。但为了公式的复制，必须考虑单元格的引用问题，我们在C11中输入的公式为“=SUMPRODUCT(($B$2:$B$7=$B12)*($J$2:$J$7>=$I$10)*I$2:I$7)/SUMPRODUCT(($B$2:$B$7=$B12)*($J$2:$J$7>=$I$10))”，然后向右和向下复制公式就可以求出各班上线学生的单科平均分了。

最后，值得大家注意的有以下几点：1.公式输入必须在英文状态，避免输入非英文字符，引起函数的语法错误。2.复制公式时，需变化的用相对引用，不变化的用绝对引用。3.输入公式的单元格格式应选择“常规”或“数值”，而不能是“文本”,如果是文本格式，则单元格内显示的是公式本身而非计算结果。