巧用EXCEL函数处理成绩表常见问题

王智伟

摘要：EXCEL软件是办公自动化软件之一，它的主要功能是对表格进行分析、计算、处理，并且可以将分析计算结果用各种图表形式展现出来。公式和函数是实现表格计算功能的强大武器，是学习《EXCEL电子表格制作》的重点和难点，该文就围绕成绩表中遇到的常见计算问题所涉及的EXCEL函数进行阐述。

关键词：函数；IF；AND；RANK；COUNTIF；SUMIF；AVERAGEIF

中图分类号：TP393 文献标识码：A 文章编号：1009-3044（2018）24-0249-03

《EXCEL电子表格制作》是中等职业学校计算机专业和非计算机专业学生都要学习和掌握的一门基础学科，其中公式和函数一章是《EXCEL电子表格制作》一书的重点和难点章节，也是历年来河南省素质能力大赛EXCEL数据处理考试的重要知识点之一，很多学生在学习函数的过程中感到难懂难记，该文就以成绩表处理中遇到的常见问题为例，来讲一下中职生在学习《EXCEL电子表格制作》这门课中应掌握的一些函数。

1 问题引入

如图1所示是一个学生成绩表，表中所对应的空白单元格是需要使用公式和函数来计算的部分。从图中我们可以看出，成绩表的计算可以分为横向计算和纵向计算两部分。

2处理过程

2.1 横向计算

对成绩表的横向计算通常包括：计算每个学生的总分、平均分，根据总分或平均分对学生求等级、求名次、求奖学金，根据各科成绩判断有无挂科以及挂科的科目等。

1）横向求总分、平均分

横向计算每个学生的总分、平均分分别使用求和函数SUM和求平均值函数AVERAGE，这两个函数几乎在所有的《计算机基础》教材或《EXCEL电子表格制作》教材中都有，使用也很简单。就本表格而言，求总分只需要在F3单元格中输入公式“=SUM（C3：E3）”后按回车键，或者单击F3单元格后，选择“公式”选项卡下面“函数库”组中的“自动求和”按钮下拉箭头，选择“求和”命令即可在F3单元格中自动出现公式“=SUM（C3：E3）”。这时只需要按回车键或单击编辑栏中的对号按钮就可以算出第一个学生的总分。使用同样的方法在G3单元格中输入公式“=AVERAGE（C3：E3）”后按回车键即可求出第一个学生的平均分。要求其他学生的总分和平均分只需要选中F3和G3单元格，拖动右下角的填充柄就可以完成。

2）根据总分求等级、奖学金

根据总分或平均分对学生求等级，以总分为例。成绩等级计算：要求总分大于等于260的为优秀，大于等于240的为良好。大于等于180的是中等，低于180的为差。这个问题需要用到IF函数的嵌套来完成。我们先来看看IF函数的格式及功能。

单击“公式”选项卡下的插入函数按钮，打开“插入函数”对话框，在“搜索函数”文本框中输入“IF”，单击“转到”按钮，在“选择函数”列表框中单击“IF”，在列表框的下方就会出现“IF”函数的格式及功能说明，如图2所示。

单击“确定”按钮，出现“函数参数”对话框，如图3所示。从IF函数的参数对话框中我们可以看出，它的功能是判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。它里面包含三个参数：Logical_test、Value_if_true、Value_if_false，单击每个参数后面的文本框，下方会出现关于该参数的详细说明。从函数参数对话框中我们可以看出参数Logical_test是表示判定条件的，是任何可被计算为true或false的数值表达式；参数Value_if_true是Logical_test为true时的返回值，如果忽略，则返回true，IF函数最多可嵌套7层；Value_if_false是Logical_test为false时的返回值，如果忽略，则返回false。从参数说明中我们可以得知，在参数Value_if_true和Value_if_false中是分别可以再进行IF嵌套使用的。

针对本题目的要求，把总分共分为了4个分数段，我们可以使用3个IF语句的嵌套来完成。在H3单元格中输入公式：=IF（F3>=260，"优秀"，IF（F3>=240，"良好"，IF（F3>=180，"中等"，"差"）））或=IF（F3<180，"差"，IF（F3<240，"中等"，IF（F3<260，"良好"，"优秀"）））。做这类题目时可以遵从两个原则：一是条件的设置，可以从高分向低分排列，也可以从低分向高分排列，但要注意等号的使用位置，公式中所有的标点符号必须是英文半角状态，分数段也必须是连续的；二是条件的数量与分数段的个数有关，一般来说IF条件嵌套的个数比实际的条件个数少1个。

根据总分求奖学金的方法类似。比如总分大于等于260的，奖学金为300元，大于等于240的，奖学金为100元，其余的没有奖学金。我们可以在J3单元格中输入公式：=IF（F3>=260，"300元"，IF（F3>=240，"100元"，""））或=IF（F3<240，""，IF（F3<260，"100元"，"300元"））。

3）根据各科成绩求是否有挂科和挂科科目

求有无挂科问题需要使用IF函数和AND函数嵌套来完成。使用AND函数可以一次判定多个条件是否都满足，如果都满足，说明无挂科，否则说明有挂科。我们可以在K3单元格中输入公式：=IF（AND（C3>=60，D3>=60，E3>=60），"无"，"有"）。

求挂科科目問题需要使用多个IF函数及IF函数和AND函数运算结果联接在一起来完成。我们可以在L3单元格中输入公式：=IF（C3<60，"语文"，""）&IF;（D3<60，"数学"，""）&IF;（E3<60，"英语"，""）&IF;（AND（C3>=60，D3>=60，E3>=60），"无"，""）。

4）根据总分求名次

求名次问题需要使用RANK函数来实现。我们在“插入函数”对话框“搜索函数”文本框中输入RANK，单击“转到”按钮，再单击“确定”按钮，进入RANK函数参数对话框，如图4所示。

从这个对话框中我们可以看出RANK函数的功能是返回某数字在一列数字中相对于其他数值的大小排名。它包含三个参数：Number、Ref、Order，单击每个参数后的文本框，下方会出现关于这个参数的详细说明。从参数说明中我们可以得知，参数Number是要查找排名的数字，可以是具体的数字或单元格引用，参数Ref是一组数或对一个数据列表的引用，非数字值将被忽略。参数Order是在列表中排名的数字，如果为0或忽略，降序；非0值，升序。针对本题目，我们可以在I3单元格中输入公式：=RANK（F3，F3：F12，0），求出第一个学生在全班的排名，但是如果要求其他学生的排名，使用这个公式就不能使用拖动填充柄的方法准确地求出，這里面牵涉到单元格引用的问题，在EXCEL中单元格的引用有绝对引用、相对引用、混合引用。在这里主要用到绝对引用和相对引用，使用绝对引用时如果复制公式，单元格的名称不会发生变化；如果使用相对引用，则复制公式时，单元格的名称会随着目标单元格的不同而发生变化。在这个公式中，第一个参数是要排名的学生，是需要不断变化的，所以必须使用相对引用，第二个参数是要排名的范围，这个是不能发生变化的，每个学生都要在这个范围内排名，所以必须使用绝对引用。我们可以这样对参数进行修改：=RANK（F3，$F$3：$F$12，0），这样再用拖动填充柄的方法求其他学生的名次就不会出错了。

2.2 纵向计算

1）求单科总分、平均分、最高分、最低分

纵向求单科总分、平均分、最高分、最低分分别使用求和函数SUM、求平均值函数AVERAGE、求最大值函数MAX、求最小值函数MIN，这几个函数的使用非常简单，在这里我只给出公式，不再详述。

求语文单科总分，在C13单元格中输入公式：=SUM（C3：C12）。求语文单科平均分，在C14单元格中输入公式：=AVERAGE（C3：C12）。求语文单科最高分，在C15单元格中输入公式：=MAX（C3：C12）。求语文单科最低分，在C16单元格中输入公式：=MIN（C3：C12）。

求其他科目的总分、平均分、最高分、最低分只需要选中C3：C16单元格，拖动右下角的填充柄就可完成。

2）求单科及格率、优秀率、优秀人数及各分数段人数

处理这类问题就要用到统计类函数COUNT和COUNTIF。要求及格率，需要先求出及格人数和总人数。求及格人数使用条件统计函数COUNTIF，求总人数使用统计函数COUNT。及格率公式是及格人数除以总人数，把求得的结果转换为百分比样式。针对本题目，求语文科目的及格率，需要在C17单元格中输入公式：=COUNTIF（C3：C12，">=60"）/COUNT（C3：C12）。

同样要求优秀率，需要先求出优秀人数和总人数，优秀人数也是使用条件统计函数COUNTIF，在C19单元格中输入公式：=COUNTIF（C3：C12，">=80"）。优秀率公式是优秀人数除以总人数，把求得的结果转换为百分比样式。在C18单元格中输入公式：=COUNTIF（C3：C12，">=80"）/COUNT（C3：C12）。

60分以下的人数，在C20单元格输入公式：=COUNTIF（C3：C12，"<60"）。

要求60～70分数段的人数需要先求出大于等于60分的人数和大于等于70分的人数，二者相减即可。在C21单元格中输入公式：=COUNTIF（C3：C12，">=60"）-COUNTIF（C3：C12，">=70"）。用同样的方法可以求其他分数段的人数，大家可以尝试着做一下。求其他科目的及格率、优秀率及各分数段的人数，都可以使用拖动填充柄的方法直接求出。

3）按性别求总分、平均分

求男生语文单科总分和平均分，需要使用条件求和函数SUMIF，条件求平均值函数AVERAGEIF。我们先来看一下SUMIF函数的格式及功能说明，在“插入函数”对话框的“搜索函数”文本框中输入SUMIF，单击“转到”按钮和“确定”按钮，打开如图5所示的对话框。

从图中我们可以看出，SUMIF函数的主要功能是对满足条件的单元格求和，它包含三个参数：Range、Criteria、Sum_range。单击每个参数后的文本框，下方会出现关于这个参数的详细说明。从参数说明中我们可以得知，参数Range是要进行计算的单元格区域即条件区域；参数Criteria是以数字、表达式或文本形式定义的条件；参数Sum_range是用于求和的实际单元格即求和区域。针对本题目，条件区域为性别列，即B3：B12；条件是性别为男的任意单元格；求和区域为语文列，即C3：C12。如果要使用填充柄向右复制公式的话，则前两个参数是不能改变的，需要使用绝对引用，第三个参数需要变化，需要使用相对引用。我们可以在C25单元格中输入公式：=SUMIF（$B$3：$B$12，$B$3，C3：C12）。

条件求平均值函数AVERAGEIF的格式及参数个数与SUMIF函数都是一样的，只是功能不一样，AVERAGEIF函数查找给定条件单元格的平均值。前两个参数Range和Criteria的含义与SUMIF函数是一样的，第三个参数Average_range是用于求平均值的实际单元格区域即求平均区域。针对本题目，要求男生的语文平均分，需要在C26单元格输入公式：=AVERAGEIF（$B$3：$B$12，$B$3，C3：C12）。

男生的语文总分、平均分求出后，女生的语文总分、平均分也可以很容易的求出，只需要将公式中的第二个参数改为性别是女生的单元格绝对引用即可，大家可以尝试着去做。其余未计算的相应单元格数据都可以使用拖动填充柄的方式计算出来。

3 问题总结

综上所述，要处理成绩的常见计算问题，我们需要掌握下面这些函数：SUM、AVERAGE、MAX、MIN、COUNT、IF、AND、RANK、COUNTIF、SUMIF、AVERAGEIF。前五个函数是常用函数，在所有的计算机应用基础教材及EXCEL电子表格制作教材中都有，使用方法也很简单。后几个函数是需要重点学习的。学习一个新的函数，需要了解这个函数的四个方面：函数名、函数的功能、函数中所包含的参数个数及各个参数所表示的含义。如果后两项记不住的话也没关系，只要记住函数名和函数的功能就可以了，遇到问题知道用哪个函数就行，其他的记不住可以使用插入函数对话框帮你解决。最后要强调的是，不管用到哪个函数，一定得记住函数中涉及的所有符号一定是英文半角状态下的标点符号，否则就会不断地出现错误提示信息。

参考文献：

[1]王小林，郭燕.EXCEL 2010电子表格制作案例教程》[M].北京：北京金企鹅文化发展中心策划主编，航空工业出版社，全国计算机应用技术证书考试（NIT）推荐教材，2017.7.

[2]刘可，李显进.EXCEL 2010从入门到精通[M].北京：清华大学出版社，2014.

【通联编辑：王力】