EXCEL学生成绩统计的常用函数公式.docx

EXCEL学生成绩统计的常用函数公式.docx

《EXCEL学生成绩统计的常用函数公式.docx》由会员分享，可在线阅读，更多相关《EXCEL学生成绩统计的常用函数公式.docx（11页珍藏版）》请在冰点文库上搜索。

EXCEL学生成绩统计的常用函数公式

1、统计不同分数段的学生人数

　　统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中A16~E16所示。

这里，假设需要统计90~100、80~89、70~79、60~69及低于60分五个不同分数段的人数。

　　通常，统计不同分数段最好的方法是利用COUNTIF（X，Y）函数。

其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参数Y为统计条件，要加引号。

　　对于小于60分的人数只要用一个COUNTIF（）函数，如在E16单元格中输入公式：

=COUNTIF（$C$2:

$C$13,"<60"）。

　　对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF（）函数相减。

如在A16单元格中输入公式：

=COUNTIF（$C$2:

$C$13,"<=100"）-COUNTIF（$C$2:

$C$13,"<90"），即用小于等于100的人数减去小于90的人数。

　　如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"<=100"与"<90"作相应的修改，就可以得到正确的结果。

　　2.保持学号顺序不变的前提下进行成绩排名

　　学生成绩排定在学生成绩统计中经常用到。

特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。

这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。

　　要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK（X，Y，Z）函数。

其中有三个参数，第一个参数X为某个学生的成绩所在单元格;第二个参数Y为整个班级成绩所在的区域;第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。

　　为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F2单元格中输入公式：

=RANK（C2,$C$2:

$C$13,0），然后，利用自动填充柄将其复制到下方的几个单元格。

注意，这里$C$2:

$C$13用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的。

　　3、将百分制转换成不同的等级分

　　将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”与“等级2”列所示。

这里，“等级1”列是将百分制的分数转换成A（90~100）、B（80~89）、C（70~79）、D（60~69）与E（低于60）五个等级;“等级2”列是将百分制的分数转换成优（90~100）、良（75~89）、中（60~74）与不及格（低于60）四个等级。

具体使用哪种等级划分方法可根据实际情况自己确定。

　　在百分制转换成不同的等级分时，一般使用IF（X，Y，Z）函数。

其中有三个参数，第一个参数X为条件，不能加引号;第二个参数为条件成立时的结果，如果是显示某个值，则要加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。

该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

　　为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：

=IF（C2>=90,"A",IF（C2>=80,"B",IF（C2>=70,"C",IF（C2>=60,"D","E"）））），然后，利用自动填充柄将其复制到下方的几个单元格。

　　为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：

=IF（C2>=90,"优",IF（C2>=75,"良",IF（C2>=60,"中","不及格"））），然后，利用自动填充柄将其复制到下方的几个单元格。

　　4、使不及格的分数以红色显示

　　统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分（如第12行）。

　　使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框，其中要求确认条件与相应的格式。

　　对于“成绩”列，可先选中C2：

C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

　　对于“等级1”列，可先选中D2：

D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

“等级2”列类似。

对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能，应该比较简单，本文此处不赘述。

5、保留几位小数？

选取要更改的单元格（一般都是整行或者整列选取），右键-->设置单元格格式-->数字，在里面选择就是了，上面都有这种格式的样板的。

比如你要的“数字变成3位有效数字，包括小数”，就选数值，把小数位数改成3就行了

那只能再把整数改回来，不过一般来说，整数后面也放同样位数的0，是正确的办法。

修改的时候，按住Ctrl键，再逐个点整数的单元格，最后一次性调整小数位，这样会稍微快点

6、求最高分和最低分

MAX函数：

求出一组数中的最大值。

MIN函数：

求出一组数中的最小值。

7、求和、条件求和

SUM函数：

计算所有参数数值的和。

SUMIF函数：

计算符合指定条件的单元格区域内的数值和。

（？

）

8、区域求和

区域求和常用于对一张工作表中的所有数据求总计。

此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式"=SUM（）"，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。

若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。

对于需要减去的单元格，则可以按住Ctrl键逐个选中它们，然后用手工在公式引用的单元格前加上负号。

当然你也可以用公式选项板完成上述工作，不过对于SUM函数来说手工还是来的快一些。

比如，H13的公式还可以写成：

=SUM（D3:

D12,F3:

F12）-SUM（G3:

G12）

9、四舍五入

在实际工作的数学运算中，特别是财务计算中常常遇到四舍五入的问题。

虽然，excel的单元格格式中允许你定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正的四舍五入，只是显示结果似乎四舍五入了。

如果采用这种四舍五入方法的话，在财务运算中常常会出现几分钱的误差，而这是财务运算不允许的。

那是否有简单可行的方法来进行真正的四舍五入呢？

其实，Excel已经提供这方面的函数了，这就是ROUND函数，它可以返回某个数字按指定位数舍入后的数字。

在Excel提供的"数学与三角函数"中提供了一个名为ROUND（number,num_digits）的函数，它的功能就是根据指定的位数，将数字四舍五入。

这个函数有两个参数，分别是number和num_digits。

其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。

如图3所示：

单元格B2中为初始数据0.123456，B3的初始数据为0.234567，将要对它们进行四舍五入。

在单元格C2中输入"=ROUND（B2,2）"，小数点后保留两位有效数字，得到0.12、0.23。

在单元格D2中输入"=ROUND（B2,4）"，则小数点保留四位有效数字，得到0.1235、0.2346。

10、及格率、优秀率的计算

=COUNTIF（a1:

a10,">90"）/COUNT（a1:

a10）计算优秀率

=COUNTIF（a1:

a10,">=60"）/COUNT（a1:

a10）计算及格率

11、打印时添加大标题、顶端标题

文件/页面设置/页眉页脚/自定义页眉页脚

如果要求打印时每一页都出现第一行：

文件/页面设置/工作表/顶端标题栏内输入$1:

$1

12、为工作表加密

文件/另存为/工具/常规选项/加密/确定

学习小结：

EXCEL学生成绩统计的常用函数公式

1.总分公式：

=sum（C2:

C59）；

2.平均分公式：

=AVERAGE（C2:

C59）；

3.最高分公式：

=MAX（C2:

C59）；

4.及格率公式：

=（COUNTIF（C2:

C59,">=60"）/COUNT（C2:

C59））；

5.优秀率公式：

=（COUNTIF（C2:

C59,">=80"）/COUNT（C2:

C59））；　　　　　

6.90分以上人数公式：

=COUNTIF（C2:

C59,">=90"）；

7.80分~85分人数公式：

=COUNTIF（C2:

C59,">=80"）-COUNTIF（C2:

C59,">=85"）；　

8.60分以下人数公式：

=COUNTIF（C2:

C59,"<60"）。

平均分只需保留一位小数：

鼠标右键单击→设置单元格格式→数值→小数位数设置为1位。

不及格的学科突出显示：

选择全部成绩→格式→条件格式→条件格式对话框→把条件设为小于60分的用红色显示→点击"格式"按钮→把颜色设为红色→确定。

小数变百分比：

鼠标右键单击→设置单元格格式→百分比→小数位数设置为1位→确定。

如何计算

[2.1]计算考号1的总分

表格都是由行和列组成的，行由数字表示，列用字母表示。

当活动单元格在F4位置时键入“＝”然后输入SUM（C4:

E4）（表示从C4单元格加到E4单元格），再回车，即得总分。

另一种方法是选择C4到E4这三个单元格后单击工具栏上“自动求和”按钮。

[2.2]其他总分的得出

用鼠标选取已算出的学号1的总分，当出现“+”符号后往下拖动鼠标，所有其他的总评分会自动出现。

3.如何计算学生平均分

[3.1]学生1的平均分：

活动单元格放在需要填写平均分的位置，输入“＝F4/3”（用总分除以科目3）回车，即算出平均分。

[3．2]其他平均分的得出：

用鼠标选取已算出的学生1的平均分，当出现“+”符号后往下拖动鼠标，所有其他的平均分会自动出现。

4.如何排列名次

[4．1]利用菜单排序：

排列名次，首先按总分排序。

从列标题那行一直选取到最后（本表是从第3行到55行），单击菜单“数据”，再单击“排序”，在主要关键字下拉列表中选择“总分”，再选“降序”，按下“确定”键，分数即由高至低排列。

这时再用“自动填充”方法将名次的号（数字）填上。

缺点是并列的名次没有显示出来。

然后再选全表，单击“数据”再单击“排序”，在下拉列表中选主要关键字“考号”，再选“升序”，最后“确定”，即出现按考号排列的名次格式。

[4．2]利用RANK函数排序：

另外一种方法是利用RANK函数进行排名，解决了以上统计方法无并列名次的缺点。

RANK（X，Y，Z）函数，其中有三个参数，第一个参数X为某个学生的总分所在单元格，第二个参数Y为整个班级总分所在的区域，第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。

为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F4单元格中输入公式：

=RANK（F4,$F$4:

$F$55,0）

然后，利用自动填充柄将其复制到下方的单元格，统计出所有学生的名次。

注意，这里$F$4:

$F$55用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级总分所在的区域，这个区域是相同的。

再按名次排序，可以看出并列的名次也统计出来了。

（见下图）

5.进行科目统计

[5．1]计算科目总分

选取所要求和的“总分”一栏的数据，单击“Σ”按钮，或者是在其中输入公式“＝SUM（C4：

C55）”后回车即算出科目总分。

然后，利用自动填充柄将其复制到右方的单元格，数学、英语总分即可得出。

[5．2]计算科目平均分：

方法同上，只是函数不一样

在要填入语文平均分的单元格内输入公式：

“=AVERAGE（C4:

C55）”即得出语文平均分，然后，利用自动填充柄将其复制到右方的单元格，数学、英语平均分即可得出。

[5．3]统计科目及格人数：

通常，统计不同分数段最好的方法是利用COUNTIF（X，Y）函数。

其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参数Y为统计条件，要加引号。

对于大于等于60分的人数只要用一个COUNTIF（）函数，如在C59单元格中输入公式：

=COUNTIF（$C$4:

$C$55,">=60"）

回车即可得出。

其它两科复制公式后，把统计范围即其中绝对地址分别改为$D$4:

$D$55和$E$4:

$E$55，数学和英语科目及格人数也统计完成。

[5．4]计算单科及格率：

单科及格率等于及格人数除以参考人数。

语文科目已在C59单元格统计出及格人数，参考人数在列标题上方E2单元格内填入，因此在统计及格率C60单元格内写入公式：

=C59/E2回车即得出。

单元格格式可设为“百分比”保留2位小数。

其它两科同样的方法可算出。

[5．5]其它分数段的统计

方法同统计及格人数，优生人数的统计只需把统计条件改为“>=80”差生人数的统计把统计条件改为“<40”。

如果要统计80——89、70——79与60——69分数段的人数，需要用两个COUNTIF（）函数相减。

如统计80——90分数段人数用公式：

=COUNTIF（$C$4:

$C$55,"<90"）-COUNTIF（$C$4:

$C$55,"<80”）

即用小于90的人数减去小于80的人数。

其它分数段统计方法相同只要把统计条件改一下。

[5．6]统计科目最高分和最低分：

利用函数MAX、MIN分别统计最大值和最小值。

如统计语文科目中最高分在C65单元格内输入公式：

＝MAX（C4:

C55）回车即得到87.00。

最低分在C66单元格内输入公式：

=MIN（C4:

C55）回车即可。

[5．7]将百分制转换成不同的等级分：

将百分制转换成不同的等级分有多种不同的划分方法，1是将百分制的分数转换成A（90——100）、B（80——89）、C（70——79）、D（60——69）与E（低于60）五个等；2是将百分制的分数转换成优（90——100）、良（75——89）、中（60——74）与不及格（低于60）四个等级。

具体使用哪种等级划分方法可根据实际情况自己确定。

在百分制转换成不同的等级分时，一般使用IF（X，Y，Z）函数。

其中有三个参数，第一个参数X为条件，不能加引号；第二个参数为条件成立时的结果，如果是显示某个值，则要加引号；第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。

该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

为了得到第一种方法所要的等级结果，如语文科目可以在D4单元格中输入公式：

=IF（C4>=90,"A",IF（C4>=80,"B",IF（C4>=70,"C",IF（C4>=60,"D","E"））））然后，利用自动填充柄将其复制到下方的所需单元格。

为了得到第二种方法所要的等级结果，语文科目可以在E4单元格中输入公式：

=IF（C4>=90,"优",IF（C4>=75,"良",IF（C4>=60,"中","不及格"）））

然后，利用自动填充柄将其复制到下方的所需单元格。

[5．8]使不及格的分数以红色显示

统计学生成绩时经常需要将不及格的分数用红色显示，其结果如上图中红色显示部分。

使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框，其中要求确认条件与相应的格式。

对于“语文”列，可先选中C4：

C55，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

对于“等级一”列，可先选中D4：

D55，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

“等级二”列类似。

Excel电子表格功能强大，界面直观易学易用，可以轻松地完成学科的总分、平均分的计算；利用RANK函数排序，可以在学生顺序不变的前提下进行学生成绩名次排定，并且可以显示出名次并列的情况，一目了然；COUNTIF（X，Y）函数是统计不同分数段人数的一种好方法；使用IF（X，Y，Z）函数能轻松将百分制转换成不同的等级分；用“条件格式”命令可以用特殊的颜色显示符合条件的一组分数段或某等级；总之，用Excel统计学生成绩，将会大大提高教师的工作效率和工作质量，让我们的教学工作变得更轻松。