WPS表格使用多条件求和功能来统计考试成绩.doc

WPS表格使用多条件求和功能来统计考试成绩 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.输入公式的单元格格式应选择“常规”或“数值”，而不能是“文本”,如果是文本格式，则单元格内显示的是公式本身而非计算结果。 用WPS表格四步统计分数段人数和算折合分（高手勿喷） 我们上级对教师教学质量的评价方法是用各分数段人数算折合分来考核，即用教师学科成绩中获得优秀（85分及以上、良好（75分及以上）、合格（60分及以上）的人数分别总人数的百分比，再分别30、50、20的得分之和。我想每次测试后都知道自己的折合分情况，于是制作了一个成绩册，输入成绩后自动统计各分数段人数和折合分，高手勿喷。 一、先用WPS表格制作好成绩册。 二、在姓名列下面空格内输需要统计的入分数段。 三、在测试1下面对应分数段B18单元格输入公式：=COUNTIF(B$2:B$17,$A18)，说明：COUNTIF函数用于统计区域内给写条件的单元格个数。括号内分两部分，区域即统计范围，然后是条件（可以是文本、数字或表达式）。 在这里还用到了绝对引用符号$，为便于公式向下填充，而统计的行数不变，所以在行的前面加了$。为了横向填充，在列数前面没有加$。现在我们点到B18单元格右下角，鼠标变成一个+的时候，按住鼠标左键向下拉到21行，再向右填充到需要统计的单元格。 四、在折合分后单元格输入公式：=B18/16*30+B19/16*50+B20/16*20，再向右填充。 现在你可以输入学生成绩来试试了。 WPS表格如何使用数据透