EXCEL成绩统计分析常用函数.docx
《EXCEL成绩统计分析常用函数.docx》由会员分享，可在线阅读，更多相关《EXCEL成绩统计分析常用函数.docx（28页珍藏版）》请在冰豆网上搜索。
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"作相应的修改，就可以得到正确的结果。
学生成绩排定在学生成绩统计中经常用到。
特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。
这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图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，其他版本可以参照使用。
一、求各种分数
求总分：
主要用SUM函数，其语法格式为SUM（Ref），此处Ref为参与计算的单元格区域。
例如：
SUM（B2:
E2）是表示求B2、C2、D2、E2四个单元格内数字的和。
另外还用到SUMIF函数，语法格式为SUMIF（Range,Criteria,Sum_range），其功能是根据指定条件对假设干单元格求和，参数Range表示引用，用于条件判断的单元格区域。
Criteria表示数字、表达式或文本，指出哪些单元格符合被相加求和的条件。
Sum_range表示引用，需要求和的实际单元格。
注意：
Criteria如果是文本，那么引号应该是半角的，而不是全角的，否那么会出错！
求平均分：
用AVERAGE函数，其语法格式为AVERAGE（Ref），此处Ref为参与计算的单元格区域。
例如AVERAGE（F2:
F50）是求F2:
F50区域内数字的平均值。
默认情况下，Excel2002会忽略掉空白的单元格，但是它不忽略数值为0的单元格，要想忽略数值为0的单元格需要用到COUNTIF函数，其语法为COUNTIF（Range,Criteria），其含义是计算某个区域中满足给定条件的单元格数目。
本例求F2:
F50的平均分，如果忽略数值为0的单元格可以这样计算：
SUM（F2:
F50）/COUNTIF（F2:
F50,"<>0"）。
另外如果要求去掉几个最高分和几个最低分然后取平均分的话，用到LARGE和SMALL函数，其语法格式为LARGE（array,k），含义是返回数组中第k个最大值，SMALL（array,k）的含义是返回数组中第k个最小值。
如果我们求F2:
F50中去掉两个最高分和两个最低分之后的平均分可以这样计算：
“SUM（F2:
F50）-LARGE（F2:
F50,1）-LARGE（F2:
F50,2）-SMALL（F2:
F50,1）-SMALL（F2:
F50,2））/COUNTIF（F2:
F50-4）〞。
求最高分、最低分：
MAX和MIN函数，语法格式分别为MAX（Ref）和MIN（Ref），如上例中求F2:
F50的最高分和最低分，应该这样：
MAX（F2:
F50）和MIN（F2:
F50）。
二、求及格率、优秀率
求及格率：
及格率即一个班级中某一科大于等于60分的比例，例如：
B2:
B50中是某一个班的语文成绩，可以这样求及格率：
COUNTIF（B2:
B50,">=60"）/COUNT（B2:
B50）。
求优秀率：
例如：
B2:
B50存放的是初一一班的语文期末考试成绩，B2:
B500存放的是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率应该这样计算：
“COUNTIF（B2:
B50,">="&&LARGE（B2:
B500,INT（0.2COUNT（B2:
B500））））/COUNT（B2:
B50）〞，其中LARGE（B2:
B500,INT（0.2COUNT（B2:
B500）））所求的是全年级语文分数前20%中最低的一个同学的分数，COUNTIF（B2:
B50,">="&&LARGE（B2:
B500,INT（0.2*COUNT（B2:
B500））））那么是求出了初一一班语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数COUNT（B2:
B50），所得就是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。
用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），回车所得即为及格率。
选中M3:
M6单元格，拖动填充句柄向右填充公式至Q6单元格，松开鼠标，各学科的统计数据就出来了。
再选中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绘制表格
具体过程如下：
以1班语文学科的分数段统计为例。
将鼠标定位于统计表中的N3单元格，在编辑栏输入如下公式“=COUNTIFS（$A$2:
$A$108,$L3,$C$2:
$C$108,"100"）〞，按下回车键，那么可以得到1班语文成绩为100分的学生人数。
还是先说说这个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中执行替换
其它几个学科照此办
升级会员 