EXCEL成绩统计分析常用函数.docx

EXCEL成绩统计分析常用函数.docx

《EXCEL成绩统计分析常用函数.docx》由会员分享，可在线阅读，更多相关《EXCEL成绩统计分析常用函数.docx（23页珍藏版）》请在冰豆网上搜索。

EXCEL成绩统计分析常用函数

EXCEL成绩统计分析常用函数

1、总分=SUM（）

2、平均分=AVERAGE（）

3、+-*/   加减乘除括号（）

4、分数段统计

函数：

FREQUENCY（）

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

例如，使用函数FREQUENCY能够运算在给定的分数范畴内测验分数的个数。

由于函数FREQUENCY返回一个数组，因此必须以数组公式的形式输入。

语法

FREQUENCY（data_array,bins_array）

Data_array     为一数组或对一组数值的引用，用来运算频率。

如果data_array中不包含任何数值，函数FREQUENCY返回零数组。

Bins_array     为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。

如果bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数。

讲明

在选定相邻单元格区域（该区域用于显示返回的分布结果）后，函数FREQUENCY应以数组公式的形式输入。

返回的数组中的元素个数比bins_array（数组）中的元素个数多1。

返回的数组中所多出来的元素表示超出最高间隔的数值个数。

例如，如果要运算输入到三个单元格中的三个数值区间（间隔），请一定在四个单元格中输入FREQUENCY函数运算的结果。

多出来的单元格将返回data_array中大于第三个间隔值的数值个数。

函数FREQUENCY将忽略空白单元格和文本。

关于返回结果为数组的公式，必须以数组公式的形式输入。

5、不及格红色显示

“格式”菜单中的“条件格式”命令

6、排名显示=RANK（number,ref,order）

其中number为需要找到排位的数字。

Ref为包含一组数字的数组或引用。

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

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

·如果order为0或省略，MicrosoftExcel将ref当作按降序排列的数据清单进行排位。

·如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位。

7、CountIF

代替人工数数

COUNTIF函数的16种公式设置

1、返加包含值12的单元格数量

=COUNTIF（DATA,12）

2、返回包含负值的单元格数量

=COUNTIF（DATA，"<0"）

3、返回不等于0的单元格数量

=COUNTIF（DATA，"<>0"）

4、返回大于5的单元格数量

=COUNTIF（DATA，">5"）

5、返回等于单元格A1中内容的单元格数量

=COUNTIF（DATA，A1）

6、返回大于单元格A1中内容的单元格数量

=COUNTIF（DATA，“>”&A1）

7、返回包含文本内容的单元格数量

=COUNTIF（DATA，“*”）

8、返回包含三个字符内容的单元格数量

=COUNITF（DATA，“”）

9、返回包含单词"GOOD"（不分大小写）内容的单元格数量

=COUNTIF（DATA，“GOOD”）

10、返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量

=COUNTIF（DATA，“*GOOD*”）

11、返回包含以单词"AB"（不分大小写）开头内容的单元格数量

=COUNTIF（DATA，“AB*”）

12、返回包含当前日期的单元格数量

=COUNTIF（DATA，TODAY（））

13、返回大于平均值的单元格数量

=COUNTIF（DATA，">"&AVERAGE（DATA））

14、返回平均值上面超过三个标准误差的值的单元格数量

=COUNTIF（DATA，“>"&AVERAGE（DATA）+STDEV（DATA）*3）

15、返回包含值为或-3的单元格数量

=COUNTIF（DATA，3）+COUNIF（DATA，-3）

16、返回包含值;逻辑值为TRUE的单元格数量

=COUNTIF（DATA，TRUE）

8、数据透视表

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

一、前言

　　关于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见咨询题难住。

碰到的难题要紧有：

如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些咨询题的解决方法与技巧加以分析和讨论。

　　本文假设读者已对Excel的差不多操作差不多有一定基础，差不多把握如何进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一样性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。

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

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

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

　　那个地点，假设学号、姓名、成绩等列及行15都差不多事先输好，需要让Excel统计其他的有关数据结果。

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

　　咨询题1：

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

（图中A16~E16）

　　咨询题2：

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

（图中F2~F13）

　　咨询题3：

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

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

　　咨询题4：

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

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

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

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

　　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”列类似。

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

Excel中用五种函数统计学生考试成绩分数段

先看看原始的学生成绩表。

五门功课的成绩分布在C2:

G47单元格区域，如下图所示。

一、利用COUNTIF函数

　　COUNTIF函数能够统计单元格区域内满足指定条件的单元格数目，因此用来统计分数段人数顺理成章。

我们用它来统计C列的语文成绩分数段。

　　如图2所示，我们需要在N2单元格统计语文分数在90分以上的学生数。

那么只需要在N2单元格输入公式“=COUNTIF（C2:

C47，">=90"）”就能够了。

其含义确实是统计C2:

C47单元格区域中满足大于等于90的单元格数目。

因此，要统计80分至89分这一段的学生数，那么就需要输入公式“=COUNTIF（C2:

C47，">=80"）-COUNTIF（C2:

C47，">=90"）”。

专门明显，大于等于80分的人数减去大于等于90分的人数正是我们想要的人数。

其他分数段能够依此类推。

二、利用FREQUENCY函数

　　这是一个专门用于统计单元格区域中数据的频率分布的函数，用它来统计分数段自然名正言顺。

以D列的数学成绩的统计为例。

　　我们先在M8:

M12设置好分数段，再在L8:

L12单元格区域设置好各分数段的分隔数（即为该分数段的上限数字），如图3所示。

选中N8:

N12单元格，在编辑栏输入公式“=FREQUENCY（$D$2:

$D$47，$L$8:

$L$12）”，然后按下“Ctrl+Shift+Enter”组合键确认，即可在公式的两端添加数组公式的标志“{｝”，同时能够看到各分数段的人数差不多统计完成了。

需要注意的是公式输入完成后必须按“Ctrl+Shift+Enter”组合键确认以产生数组公式，而且数组公式的标志“{｝”也不能够手工输入。

　　三、利用DCOUNT函数

　　DCOUNT函数平常我们不大用，它能够从满足指定条件的数据库记录的字段中运算数值单元格数目。

这话听起来看起来不太容易明白，但用它统计分数段是专门不错的，例如统计E列的英语成绩。

　　我们需要事先做一个设置。

如图4所示，以统计80分至89分的人数为例：

我们需要在Q16和R16单元格分数输入“英语”（必须与原始成绩表的列标题一致），然后在Q17和R17单元格分不输入“>=80”，“<90”。

这四个单元格是作为指定条件使用的。

　　在N16单元格输入公式“=DCOUNT（$E$1:

$E$47，"英语"，Q16:

R17）”，完成后按下回车键就能够得到正确结果了。

要注意的是公式中第一个参数为成绩所在单元格区域，必须包括列标题。

依此类推，统计其他各分数段人数，只需要改变一下公式中第三个参数，即条件所在的单元格区域就行了。

　　四、利用SUM函数

　　SUM函数是用来求和的，这地球人都明白。

然而，它也能够用来进行多条件计数，因此也能够用来统计分数段。

因此，还得借助于数组公式才行。

以F列的理论成绩为例。

　　如图5所示，在N24单元格输入公式“=SUM（（$F$2:

$F$47>=90）*（$F$2:

$F$47<=100））”，然后按下“Ctrl+Shift+Enter”组合键产生数组公式，就能够得到大于等于90分且小于等于100分的学生数了。

同样，数组公式“{=SUM（（$F$2:

$F$47>=70）*（$F$2:

$F$47<80））}”能够得到70～79分这一段的人数。

其他各分数段能够依此类推。

　　五、利用SUMPRODUCT函数

　　SUMPRODUCT函数能够返回相应的数组或区域的乘积之和，因此正好能够用它来统计分数段。

以G列的实践课为例。

　　如图6所示。

要统计90分以上的人数，那么能够在N30单元格输入公式“=SUMPRODUCT（（$G$2:

$G$47>=90）*（$G$2:

$G$47<=100））”，按下回车键后就能够得到G2:

G47单元格区域大于等于90分小于等于100分的人数了。

　　专门明显，要统计80～89分的人数，那能够使用公式“=SUMPRODUCT（（$G$2:

$G$47>=80）*（$G$2:

$G$47<90））”，其他依此类推就行了。

　　好了，五种方法统计分数段人数，相信一定有一种方法适合你。

以上各种方法使用环境均为Excel2007，其他版本能够参照使用。

用Excel函数快速统计学生期中考试成绩

期中考试终止，各学科考试成绩迅速汇总到班主任那个地点。

这时候，班主任最忙的，确实是要迅速统计各学科成绩的各项指标，例如平均分、最高分、优秀率、及格率以及各学科分数的频率统计等等。

尽管现在普遍都在使用Excel进行这项工作，只是，要想使这项工作能够高效准确地完成，那还得请Excel函数来帮忙才行。

　　汇总到班主任那个地点的成绩表如图1所示，各科成绩分布在C2:

C95单元格区域。

我们先在K2:

Q15单元格区域建立如图2所示表格用以存放各项统计结果。

　　先点击M3单元格，输入如下公式：

=AVERAGE（C2:

C95），回车后即可得到语文平均分。

　　点击M4单元格，输入公式：

=MAX（C$2:

C$95），回车即可得到语文成绩中的最高分。

　　优秀率是运算分数高于或等于85分的学生的比率。

点击M5单元格，输入公式：

=COUNTIF（C$2:

C$95,">=85"）/COUNT（C$2:

C$95），回车所得即为语文学科的优秀率。

　　点击M6单元格，输入公式：

=COUNTIF（C$2:

C$95,">=60"）/COUNT（C$2:

C$95），回车所得即为及格率。

再选中M5:

Q6单元格区域，点击菜单命令“格式→单元格”，打开“单元格格式”对话框。

点击“数字”选项卡，在左侧“分类”列表中选择“百分比”，如图3所示，确定后可将M5:

Q6单元格区域的数据转变成百分比形式。

　　至于各科分数段人数的统计，那得先选中M8:

M15单元格，在编辑栏中输入公式：

=FREQUENCY（C$2:

C$95,$K$8:

$K$15）。

然后按下“Ctrl+Shift+Enter”快捷键，能够看到在公式的最外层加上了一对大括号。

现在，我们就差不多得到了语文学科各分数段人数了。

在K列中的那些数字，确实是我们统计各分数段时的分数分界点。

　　现在再选中M8:

M15单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人数也赶忙显示在我们眼前了。

　　最终的结果如图4所示。

如果觉得K列的数据有碍观瞻，那么能够选中它们，然后设置它们的字体颜色为白色就能够了。

Excel2007巧妙统计混合编班分数段

期末考试之后，自然确实是成绩的统计和分析了。

为了使成绩的录入和分析更为公平，我们学校差不多上由专人统一录入的。

平行班级的成绩都录入在Excel的同一工作表中。

然而如此做之后，也为我们后期的成绩分析制造了苦恼。

由于工作表中混合编班，那么在统计各班各学科各分数段的人数时，就不得不进行多次按班、按学科进行排序，然后人工数出相应分数段人数，专门苦恼。

事实上，如果我们是用Excel2007进行相应的工作的话，那么就能够利用新增的COUNTIFS函数来完成这项任务，专门方便。

　　一、1班语文学科分数段统计

图1学生成绩表

　　图1所示为一次考试的学生成绩表。

在不对成绩进行排序等操作的前提下，我们将完成图2所示分数段统计表。

图2Excel2007绘制表格

　　具体过程如下：

　　依旧先讲讲那个COUNTIFS函数吧。

COUNTIFS函数的作用是统计某个区域中满足多重条件的单元格个数。

其语法格式是：

COUNTIFS（range1,criteria1,range2,criteria2…）。

其中Range1,range2,……是指对哪些区域在统计时施加条件。

Criteria1,criteria2,…则是指具体施加的条件是什么。

例如上例的公式的含义则是统计A2:

A108单元格区域中等于L3单元格（即1班）且C2:

C108单元格区域中等于100分的单元格个数。

　　COUNTIFS所支持的施加条件个数为127个，足够我们使用的。

因此，统计1班语文分数在90分至100分之间的人数时，我们只需要再增加一个条件。

在O3单元格输入如下公式：

“=COUNTIFS（$A$2:

$A$108,$L3,$C$2:

$C$108,">=90",$C$2:

$C$108,"<100"）”即可。

　　拖动该单元格的填充句柄向右复制公式，至S3单元格。

然后将公式中的分数作出相应的调整，那么就会迅速得到各分数段的相应人数了。

　　至于最后的那个50分以下的人数统计，专门明显，其公式应该是“=COUNTIFS（$A$2:

$A$108,$L3,$C$2:

$C$108,"<50"）”。

二、其它学科及班级分数段统计

　　选中N3:

T3单元格区域，向下拖动填充句柄至T7，则能够将1班的分数段表格迅速填充完成。

然而，现在每一行所填充的数据，差不多上语文学科相应的分数段人数。

我们还需要将公式中的“$C”（语文成绩所在列）改成各学科相应的列号。

只是，那个工作也不是太苦恼。

以数学科为例。

只要选中N4:

T4单元格区域，然后按下“Ctrl+F”快捷键，打开“查找和替换”对话框。

点击“替换”选项卡，在“查找内容”的输入框中输入“$C”，在“替换为”输入框中输入“$D”（数学成绩所在的列号），如图3所示。

然后点击“全部替换”按钮，那么就能够得到数学学科的各分数段了。

图3Excel2007中执行替换

　　其它几个学科照此办理，将公式中的“$C”分不再替换为“$E”、“$F”、“$G”，专门快就能够得到1班各学科的分数段统计。

图4Excel2007完成表格

　　提醒一下：

L列中的班级为辅助列，是为了我们在公式中设置班级方便而加的，不能删除。

从表格美观的角度动身，能够选中整个L列后，点击右键，在弹出的菜单中点击“隐藏”命令，将其隐藏。

用Excel统计各分数段学生数

教师常常要统计各学科相应分数段的学生人数，以方便对考试情形作全方位的对比分析。

在Excel中，有多种函数能够实现这种统计工作，笔者以图1所示的成绩表为例，给出多种统计方法。

文章末尾提供.xls文件供大伙儿下载参考。

　　文章导读：

　　方法一：

用COUNTIF函数统计

　　这是最常用、最容易明白得的一种方法，我们用它来统计“语文”学科各分数段学生数。

　　如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们也能够用COUNTIF函数来统计各等级的学生人数。

　　方法二：

用DCOUNT函数统计

　　那个函数不太常用，但用来统计分数段学生数成效专门不错。

我们用它统计“数学”学科各分数段学生数。

　　方法三：

用FREQUENCY函数统计

　　这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。

　　方法四：

用SUM函数统计

　　我们明白SUM函数通常是用来求和的，事实上，他也能够用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。

　　方法一：

用COUNTIF函数统计

　　这是最常用、最容易明白得的一种方法，我们用它来统计“语文”学科各分数段学生数。

函数功能及用法介绍

　　①分不选中C63、C67单元格，输入公式：

=COUNTIF（C3:

C62,"<60"）和=COUNTIF（C3:

C62,">=90"），即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。

　　②分不选中C64、C65和C66单元格，输入公式：

=COUNTIF（C3:

C62,">=60"）-COUNTIF（C3:

C62,">=70"）、=COUNTIF（C3:

C62,">=70"）-COUNTIF（C3:

C62,">=80"）和=COUNTIF（C3:

C62,">=80"）-COUNTIF（C3:

C62,">=90"），即可统计出成绩在60-69分、70-79分、80-89分区间段的学生人数。

　　注意：

同时选中C63至C67单元格，将鼠标移至C67单元格右下角，成细十字线状时，按住左键向右拖拉至I列，就能够统计出其它学科各分数段的学生数。

　　如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们能够用COUNTIF函数来统计各等级的学生人数。

　　如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们能够用COUNTIF函数来统计各等级的学生人数。

　　①在K64至K67单元格中，分不输入成绩等级字符（参见图2）。

　　②选中L64单元格，输入公式：

=COUNTIF（$L$3:

$L$62,K64），统计出“优秀”的学生人数。

　　③再次选中L64单元格，用“填充柄”将上述公式复制到L65至L67单元格中，统计出其它等级的学生人数。

　　上述全部统计结果参见图1。

     方法二：

用DCOUNT函数统计

　　那个函数不太常用，但用来统计分数段学生数成效专门不错。

我们用它统计“数学”学科各分数段学生数。

　　①分不选中M63至N72单元格区域（不一定非得不那个区域），输入学科