完整word版在excel中如何计算及格率和优秀率及统计各分数段人数docWord文件下载.docx

完整word版在excel中如何计算及格率和优秀率及统计各分数段人数docWord文件下载.docx

《完整word版在excel中如何计算及格率和优秀率及统计各分数段人数docWord文件下载.docx》由会员分享，可在线阅读，更多相关《完整word版在excel中如何计算及格率和优秀率及统计各分数段人数docWord文件下载.docx（14页珍藏版）》请在冰豆网上搜索。

CountIF（A1:

85"

）/CountIF（A1:

<

-100"

及格率:

60"

方法二:

在excel中，怎样统计学生的优秀率和及格率。

如何用EXCEL计算优秀率和及格率

我们在教学工作中经常要用EXCEL统计学生成绩，其中要统计学生的

优秀人数，及格人数以及优秀率，及格率，这些功能如果用菜单来操作比

较繁琐，其实完全可以直接在表格中输入函数，轻松搞定。

1

数据表——

参考人数

优秀人数

优秀率

公式编辑——

C19单元格中输入公式

=COUNTA（C2：

C16）——计算“参考人数”；

C20中单元格中输入公式

=COUNTIF（C2：

C16，"

=90"

）——计算“优秀人数”，设计

90及其

以上为“优秀”；

C21单元格中输入公式

=C20/C19——计算“优秀率”，单元格设置为"

百分比"

格式,自动保留

2位小数；

其实，可以把上述三个步骤综合成一个直接计算“优秀率”的公式

）/COUNTA（C2：

C16）

这样做起来是不是轻松多了？

比在工具和菜单中操作方便多了吧。

同

样的道理也可以用来计算及格率。

这里就不重复了。

1、COUNTIF函数

函数名称：

COUNTIF

主要功能：

统计某个单元格区域中符合指定条件的单元格数目。

使用格式：

COUNTIF（Range,Criteria）

参数说明：

Range代表要统计的单元格区域；

Criteria表示指定的条

件表达式。

应用举例：

在C17单元格中输入公式：

=COUNTIF（B1:

B13,"

），确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数

目。

特别提醒：

允许引用的单元格区域中有空白单元格出现。

2、AVERAGE函数

2

AVERAGE

求出所有参数的算术平均值。

AVERAGE（number1,number2,）

number1,number2,：

需要求平均值的数值或引用单元格（区域），参数不超过30个。

在B8单元格中输入公式：

=AVERAGE（B7:

D7,F7:

H7,7,8），确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。

如果引用区域中包含“0”值单元格，则计算在内；

如果引用区域中包含空白或字符单元格，则不计算在内

Excel统计学生成绩/用excel统计各分数段人数/统计学生的优秀率和及

格率

Excel统计学生成绩时的四个难题及解决

一、前言

对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见

问题难住。

碰到的难题主要有：

如何统计不同分数段人数、如何在保持学

号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显

示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨

论。

本文假设读者已对Excel的基本操作已经有一定基础，已经掌握如何

进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这

些一般性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。

3

二、Excel统计学生成绩时的四个难题

假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。

在

图1学生成绩统计所需要的结果图

这里，假设学号、姓名、成绩等列及行15都已经事先输好，需要让Excel统计其他的相关数据结果。

这时，成绩统计中主要难解决的问题及它们在图中的位置如下：

问题1：

如何统计不同分数段的学生人数?

（图中A16~E16）

问题2：

如何在保持学号顺序不变的前提下进行学生成绩名次排定?

（图

中F2~F13）

问题3：

如何将百分制转换成不同的等级分?

（图中“等级1”与“等级2”列）

问题4：

如何使不及格的分数以红色显示?

（图中红色显示部分，即第

12行）

三、解决统计学生成绩时的四个难题的方法

4

下面，针对上面提出的四个难题分别讨论解决的方法与技巧。

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,"

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

函数相减。

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

=100"

）-COUNTIF（$C$2:

90"

即用小于等于100的人数减去小于90的人数。

如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"

与"

作相应的修改，就可以得到正确的结果。

5

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”列是将百分制的

6

分数转换成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"

=70,"

C"

=60,"

D"

"

E

"

））））

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

优"

=75,"

良"

中"

不及格"

）））

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

7

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

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

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

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

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

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

D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中

间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

“等级2”列类似。

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

excel-Rank函数

8

返回一个数字在数字列表中的排位。

数字的排位是其大小与列表中其他值

的比值（如果列表已排过序，则数字的排位就是它当前的位置）。

语法

RANK（number,ref,order）

Number为需要找到排位的数字。

Ref为数字列表数组或对数字列表的引用。

Ref中的非数值型参数将被忽略。

Order为一数字，指明排位的方式。

如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref

为按照降序排列的列表。

如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照

升序排列的列表。

计各分数段人数需要用到--------------"

FREQUENCY函

数"

请老师们先来了解-------------、FREQUENCY函数

FREQUENCY

以一列垂直数组返回某个区域中数据的频率分布。

9

FREQUENCY（data_array,bins_array）

Data_array表示用来计算频率的一组数据或单元格区域；

Bins_array表示为前面数组进行分隔一列数值。

如图2所示，同时选中B32至B36单元格区域，输入公式：

=FREQUENCY（B2:

B31,D2:

D36），输入完成后按下“Ctrl+Shift+Enter”组合键进行确认，即可求出B2至B31区域中，按D2至D36区域进行分隔的各段数值的出现频率数目（相当于统计各分数段人数）。

上述输入的是一个数组公式，输入完成后，需要通过按

“Ctrl+Shift+Enter”组合键进行确认，确认后公式两端出现一对大括号（{}），此大括号不能直接输入。

A2=89

10

但有一个问题，如果学生缺考该怎样计分母，因为如果未输入分数（含0），结果将不一样，建议COUNT（A2:

A5）可直接输入实际人数符合学校评估要求！

我们在教学工作中经常要用EXCEL统计学生成绩，其中要统计学生的优秀人数，及格人数以及优秀率，及格率，这些功能如果用菜单来操作比较

11

繁琐，其实完全可以直接在表格中输入函数，轻松搞定。

举例如下：

C16，"

）——计算“优秀人数”，设计90及其以上

为“优秀”；

=C20/C19——计算“优秀率”，单元格设置为"

格式,自动保留2位

小数；

12

同样的

道理也可以用来计算及格率。

6、COUNTIF函数

Criteria表示指定的条件表达式。

），确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。

13

3、AVERAGE函数

如果引用区域中包含空白或字符单元格，则不计算在内。

14