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

1、EXCEL成绩统计分析常用函数EXCEL成绩统计分析常用函数1、总分 =SUM（）2、平均分 =AVERAGE（）3、+ - * / 加减乘除 括号（）4、分数段统计函数：FREQUENCY()以一列垂直数组返回某个区域中数据的频率分布。例如，使用函数 FREQUENCY 能够运算在给定的分数范畴内测验分数的个数。由于函数 FREQUENCY 返回一个数组，因此必须以数组公式的形式输入。语法FREQUENCY(data_array,bins_array)Data_array 为一数组或对一组数值的引用，用来运算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 返回零

2、数组。Bins_array 为间隔的数组或对间隔的引用，该间隔用于对 data_array 中的数值进行分组。如果 bins_array 中不包含任何数值，函数 FREQUENCY 返回 data_array 中元素的个数。讲明在选定相邻单元格区域（该区域用于显示返回的分布结果）后，函数 FREQUENCY 应以数组公式的形式输入。 返回的数组中的元素个数比 bins_array（数组）中的元素个数多 1。返回的数组中所多出来的元素表示超出最高间隔的数值个数。例如，如果要运算输入到三个单元格中的三个数值区间（间隔），请一定在四个单元格中输入 FREQUENCY 函数运算的结果。多出来的单元格将

3、返回 data_array 中大于第三个间隔值的数值个数。 函数 FREQUENCY 将忽略空白单元格和文本。 关于返回结果为数组的公式，必须以数组公式的形式输入。5、不及格红色显示 “格式”菜单中的“条件格式”命令6、排名显示 =RANK(number,ref,order) 其中number为需要找到排位的数字。 Ref为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。 Order为一数字，指明排位的方式。 如果 order 为 0 或省略，Microsoft Excel 将 ref 当作按降序排列的数据清单进行排位。 如果 order 不为零，Microsoft Excel 将

4、 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、返回包含三个字符内容的单

5、元格数量=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，“&AVE

6、RAGE(DATA)+STDEV(DATA)*3)15、返回包含值为或-3的单元格数量=COUNTIF（DATA，3）+COUNIF（DATA，-3）16、返回包含值;逻辑值为TRUE的单元格数量=COUNTIF（DATA，TRUE）8、数据透视表EXCEL中统计学生成绩常用的函数一、前言 关于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见咨询题难住。碰到的难题要紧有：如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些咨询题的解决方法与技巧加以分析和讨论。本文假设读者已对Excel的差不多操作差不多

7、有一定基础，差不多把握如何进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一样性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。二、Excel统计学生成绩时的四个难题假设在统计学生成绩时，我们需要统计出如图1所示的有关结果。图1学生成绩统计所需要的结果图那个地点，假设学号、姓名、成绩等列及行15都差不多事先输好，需要让Excel统计其他的有关数据结果。这时，成绩统计中要紧难解决的咨询题及它们在图中的位置如下：咨询题1：如何统计不同分数段的学生人数?(图中A16E16)咨询题2：如何在保持学号顺序不变的前提下进行学生成绩名次排定?(图中F2F13)咨询题3：如何将百分制转

8、换成不同的等级分?(图中“等级1”与“等级2”列)咨询题4：如何使不及格的分数以红色显示?(图中红色显示部分，即第12行)三、解决统计学生成绩时的四个难题的方法下面，针对上面提出的四个难题分不讨论解决的方法与技巧。1、统计不同分数段的学生人数统计不同分数段的学生人数是专门常见的需求，其所需结果如图1中A16E16所示。那个地点，假设需要统计90100、8089、7079、6069及低于60分五个不同分数段的人数。通常，统计不同分数段最好的方法是利用COUNTIF(X，Y)函数。其中有两个参数，第一个参数X为统计的范畴，一样最好用绝对引用;第二个参数Y为统计条件，要加引号。关于小于60分的人数只

9、要用一个COUNTIF( )函数，如在E16单元格中输入公式：=COUNTIF($C$2:$C$13,60)。关于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF( )函数相减。如在A16单元格中输入公式：=COUNTIF($C$2:$C$13,=100)-COUNTIF($C$2:$C$13, 90)，即用小于等于100的人数减去小于90的人数。如果要统计8089、7079与6069分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把=100与= 90,A,IF(C2=80,B,IF(C2=70,C,IF(C2=60,D,E)，然后，利用自动填充柄将其复制到下方的

10、几个单元格。为了得到“等级2”列所要的等级结果，能够在E2单元格中输入公式：=IF(C2=90,优,IF(C2=75,良,IF(C2=60,中,不及格)，然后，利用自动填充柄将其复制到下方的几个单元格。4、使不及格的分数以红色显示统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分(如第12行)。使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。该命令会弹出一个对话框，其中要求确认条件与相应的格式。关于“成绩”列，可先选中C2：C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然

11、后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。关于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。“等级2”列类似。关于其他的一些统计运算要求，如如何样运算各分数段的百分比、如何运算机优良率与合格率等功能，应该比较简单，本文此处不赘述。Excel中用五种函数统计学生考试成绩分数段先看看原始的学生成绩表。五门功课的成绩分布在C2:G47单元格区域，如下图所示。一、利用COUNTIF函数COUNTIF函数能

12、够统计单元格区域内满足指定条件的单元格数目，因此用来统计分数段人数顺理成章。我们用它来统计C列的语文成绩分数段。如图2所示，我们需要在N2单元格统计语文分数在90分以上的学生数。那么只需要在N2单元格输入公式“=COUNTIF(C2:C47，=90)”就能够了。其含义确实是统计C2:C47单元格区域中满足大于等于90的单元格数目。因此，要统计80分至89分这一段的学生数，那么就需要输入公式“=COUNTIF(C2:C47，=80)-COUNTIF(C2:C47，=90)”。专门明显，大于等于80分的人数减去大于等于90分的人数正是我们想要的人数。其他分数段能够依此类推。二、利用FREQUENC

13、Y函数这是一个专门用于统计单元格区域中数据的频率分布的函数，用它来统计分数段自然名正言顺。以D列的数学成绩的统计为例。我们先在M8:M12设置好分数段，再在L8:L12单元格区域设置好各分数段的分隔数(即为该分数段的上限数字)，如图3所示。选中N8:N12单元格，在编辑栏输入公式“=FREQUENCY($D$2:$D$47，$L$8:$L$12)”，然后按下“Ctrl+Shift+Enter”组合键确认，即可在公式的两端添加数组公式的标志“”，同时能够看到各分数段的人数差不多统计完成了。需要注意的是公式输入完成后必须按“Ctrl+Shift+Enter”组合键确认以产生数组公式，而且数组公式的

14、标志“”也不能够手工输入。三、利用DCOUNT函数DCOUNT函数平常我们不大用，它能够从满足指定条件的数据库记录的字段中运算数值单元格数目。这话听起来看起来不太容易明白，但用它统计分数段是专门不错的，例如统计E列的英语成绩。我们需要事先做一个设置。如图4所示，以统计80分至89分的人数为例：我们需要在Q16和R16单元格分数输入“英语”(必须与原始成绩表的列标题一致)，然后在Q17和R17单元格分不输入“=80”，“=90)*($F$2:$F$47=70)*($F$2:$F$47=90)*($G$2:$G$47=80)*($G$2:$G$47=85)/COUNT(C$2:C$95)，回车所得

15、即为语文学科的优秀率。点击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”快捷键，能够看到在公式的最外层加上了一对大括号。现在，我们就差不多

16、得到了语文学科各分数段人数了。在K列中的那些数字，确实是我们统计各分数段时的分数分界点。现在再选中M8:M15单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人数也赶忙显示在我们眼前了。最终的结果如图4所示。如果觉得K列的数据有碍观瞻，那么能够选中它们，然后设置它们的字体颜色为白色就能够了。Excel 2007巧妙统计混合编班分数段期末考试之后，自然确实是成绩的统计和分析了。为了使成绩的录入和分析更为公平，我们学校差不多上由专人统一录入的。平行班级的成绩都录入在Excel的同一工作表中。然而如此做之后，也为我们后期的成绩分析制造了苦恼。由于工作表中混合编班，那么在统计各班各学科各分数段

17、的人数时，就不得不进行多次按班、按学科进行排序，然后人工数出相应分数段人数，专门苦恼。事实上，如果我们是用Excel 2007进行相应的工作的话，那么就能够利用新增的COUNTIFS函数来完成这项任务，专门方便。 一、1班语文学科分数段统计图1 学生成绩表图1所示为一次考试的学生成绩表。在不对成绩进行排序等操作的前提下，我们将完成图2所示分数段统计表。图2 Excel 2007绘制表格具体过程如下：依旧先讲讲那个COUNTIFS函数吧。COUNTIFS函数的作用是统计某个区域中满足多重条件的单元格个数。其语法格式是：COUNTIFS(range1, criteria1,range2, crit

18、eria2)。其中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)”即可。拖动该单元格的填充

19、句柄向右复制公式，至S3单元格。然后将公式中的分数作出相应的调整，那么就会迅速得到各分数段的相应人数了。至于最后的那个50分以下的人数统计，专门明显，其公式应该是“=COUNTIFS($A$2:$A$108,$L3,$C$2:$C$108,50)”。二、其它学科及班级分数段统计选中N3:T3单元格区域，向下拖动填充句柄至T7，则能够将1班的分数段表格迅速填充完成。然而，现在每一行所填充的数据，差不多上语文学科相应的分数段人数。我们还需要将公式中的“$C”（语文成绩所在列）改成各学科相应的列号。只是，那个工作也不是太苦恼。以数学科为例。只要选中N4:T4单元格区域，然后按下“Ctrl+F”快捷键

20、，打开“查找和替换”对话框。点击“替换”选项卡，在“查找内容”的输入框中输入“$C”，在“替换为”输入框中输入“$D”（数学成绩所在的列号），如图3所示。然后点击“全部替换”按钮，那么就能够得到数学学科的各分数段了。图3 Excel 2007中执行替换其它几个学科照此办理，将公式中的“$C”分不再替换为“$E”、“$F”、“$G”，专门快就能够得到1班各学科的分数段统计。图4 Excel 2007完成表格提醒一下：L列中的班级为辅助列，是为了我们在公式中设置班级方便而加的，不能删除。从表格美观的角度动身，能够选中整个L列后，点击右键，在弹出的菜单中点击“隐藏”命令，将其隐藏。用Excel统计各

21、分数段学生数教师常常要统计各学科相应分数段的学生人数，以方便对考试情形作全方位的对比分析。在Excel中，有多种函数能够实现这种统计工作，笔者以图1所示的成绩表为例，给出多种统计方法。文章末尾提供.xls文件供大伙儿下载参考。 文章导读：方法一：用COUNTIF函数统计这是最常用、最容易明白得的一种方法，我们用它来统计“语文”学科各分数段学生数。 如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们也能够用COUNTIF函数来统计各等级的学生人数。 方法二：用DCOUNT函数统计那个函数不太常用，但用来统计分数段学生数成效专门不错。我们用它统计“数学”学科各分数

22、段学生数。 方法三：用FREQUENCY函数统计这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。 方法四：用SUM函数统计我们明白SUM函数通常是用来求和的，事实上，他也能够用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。方法一：用COUNTIF函数统计这是最常用、最容易明白得的一种方法，我们用它来统计“语文”学科各分数段学生数。函数功能及用法介绍 分不选中C63、C67单元格，输入公式：=COUNTIF(C3:C62,=90)，即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。 分不选中C64、C65和C66单元格，

23、输入公式：=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函数来统计各等级的学

24、生人数。如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们能够用COUNTIF函数来统计各等级的学生人数。 在K64至K67单元格中，分不输入成绩等级字符（参见图2）。选中L64单元格，输入公式：=COUNTIF($L$3:$L$62,K64)，统计出“优秀”的学生人数。 再次选中L64单元格，用“填充柄”将上述公式复制到L65至L67单元格中，统计出其它等级的学生人数。 上述全部统计结果参见图1。 方法二：用DCOUNT函数统计那个函数不太常用，但用来统计分数段学生数成效专门不错。我们用它统计“数学”学科各分数段学生数。 分不选中M63至N72单元格区域（不一定非得不那个区域），输入学科