最新Excel成绩统计及常用统计方法.doc

《最新Excel成绩统计及常用统计方法.doc》由会员分享，可在线阅读，更多相关《最新Excel成绩统计及常用统计方法.doc（10页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精品资料Excel成绩统计及常用统计方法.统计和分析学生成绩1、按学生成绩排列名次Excel中提供了一个用于统计名次的RANK( )函数，RANK( )函数可统计出一个单元格中的数值，在一个单元格区域所有数值中的排名（排序）结果。RANK( )函数的用法：RANK( x1,x2,x3) 。其中x1为要排名次的单元格，x2为排名次的单元格区域（即排列名次的范围），x3用于指定升序或降序排名次。用RANK( )函数可以极为方便地按学生成绩计算名次，例如对图5-13中的学生成绩表要按总分计算出每个学生的名次，并将名次结果分别填入J2:J101区域的各个单元格中。首先，按前面介绍的方法，利用SUM(

2、)函数在I2:I101中计算出全部学生的总分；单击单元格J1，在其中输入“总分名次”，单击单元格J2，在其中输入公式“=RANK (I2,I$2:I$101)”后按【Enter】键即可统计出表中第1个学生的名次。由于在对每个学生统计名次时，其范围始终应该是在区域I2:I101，所以公式的RANK( )函数中使用了“$”符号，用于标识固定不变的单元格区域，即“绝对引用”，以便下一步处理中利用复制公式的方法计算其他学生的名次。其次，单击选定单元格J2，然后拖动其“填充柄”标记至单元格J101，即可将公式复制到下面的各个单元格，计算出其余学生的名次。 2、按分数段统计人数分布利用Excel中的频度函

3、数FREQUENCY( )，可以较方便地统计分布于各个分数段的学生人数，统计出的分布数据对于分析试题难度是否适当是十分有用的。设计较合理的试题，其相应的成绩应该呈正态分布。FREQUENCY( )函数可以分别计算出一个单元格区域中的数据在各个不同数据段分布的数目，因此计算结果将是一组数值（即数组），这一组数值将分别填入工作表中同一列的若干个单元格中。FREQUENCY( )函数的用法：FREQUENCY( x1,x2)。其中的参数x1可用于指定要进行频度统计的单元格区域，x2指定要统计的各个数据段的分段点的数值。由于频度函数FREQUENCY( )的返回值是一组数据（数组），所以在输入由FRE

4、QUENCE( )函数组成的公式之前，首先要选定一个用于保存函数计算结果（多个数值）的单元格区域并按【F2】键，然后输入公式。在公式输入完成后，需按【Ctrl+Shift+Enter】组合键，以确认数组公式的输入。使用频度函数FREQUENCY( )统计成绩分布情况，首先确定要统计成绩分布的分数段，例如在图5-13所示的学生成绩表中，要统计出英语成绩“低于60”、“6069”、“7079”、“8089”、“90以上”共5个分数段中分布的人数，可选择59、69、79和89作为分数的分段点；所计算出的5个分数段的分布人数，将分别填入E105:E109中的5个单元格中，如图5-15所示。图5-15

5、统计各个分数段的人数首先，为了清楚地标明各个分数段的人数分布，可在D105:D109的5个单元格中分别输入“低于60分人数”、“6069分人数”、“7079分人数”、“8089分人数”、“90分以上人数”作为说明文字，如图5-15所示。其次，选定单元格区域E105:E109，然后按【F2】键，接着在选定的区域中输入公式“=FREQUENCY(E2:E101,59,69,79,89)”，在公式输入完成后按【Ctrl+Shift+Enter】组合键，即可在E105:E109的5个单元格中统计出分布于5个分数段的人数，如图5-15所示。3、按分数段评定考试等级在学生成绩的处理中，有时可能需要根据考试

6、成绩的分数为每个学生评定相应的成绩等级或等次。在Excel中，由工作表中的学生分数确定相应的等级，可用逻辑函数IF()实现。例如在图5-13所示的学生成绩工作表中，要将英语课考试成绩中低于60分的等级定为“不合格”、6084分定为“合格”、85分以上定为“优良”，并将每个学生的等级分别填入L2:L101的各个单元格中。首先，单击选定单元格L1，在其中输入“英语等级”。单击选定单元格L2，输入公式“=IF(E2=85,优良,合格)”，然后按【Enter】键，即可确定出第1个学生的等级。其次，单击选定单元格L2，然后拖动其“填充柄”标记至L101，即可将公式复制到下面L3:L101的各个单元格，从

7、而确定出其余学生的等级，如图5-16所示。图5-16 根据成绩评定等级4、同名次排序:5、b4科语文成绩占全年级总名次:6、Excel统计出学生成绩各分数段内的人数分布用Excel怎样统计出学生成绩各分数段内的人数分布呢？很多文章都推荐使用CountIF函数，可是每统计一个分数段都要写一条函数，十分麻烦。例如，要在C58:C62内统计显示C2:C56内小于60分、60至70之间、70至80之间、80至90之间、90至100之间的分数段内人数分布情况，要输入以下5条公式：1. 在C58内输入公式统计少于60分的人数：=CountIF(C2:C56,=90) 3. 在C60内输入公式统计80至90

8、之间的人数： =CountIF(C2:C56,=80)-CountIF(C2:C56,=90)，4. 在C61内输入公式统计70到80之间的人数：=CountIF(C2:C56,=70)-CountIF(C2:C56,=80)，5. 在C62内输入公式统计60到70之间的人数：=CountIF(C2:C56,=60)-CountIF(C2:C56,=70)。如果要把0至10之间、10至20之间、20至3090至100之间这么多个分数段都统计出来，就要写上十条公式了。其实，Excel已经为我们提供了一个进行频度分析的FreQuency数组函数，它能让我们用一条数组公式就轻松地统计出各分数段的人数

9、分布。例如，我们要统计出C2:C56区域内0至100每个分数段内的人数分布：1. 在B58:B68内输入：0、9.9、19.9、9.9、99.9、100。2. 用鼠标选择区域C58至C69，在编辑栏内输入“=FreQuency(C2:C56,B58:B69)”。3. 按“Crtl+Shift+Enter”组合键产生数组公式“=FreQuency(C2:C56,B58:B69)”，这里要注意“ ”不能手工键入，必须按下“Crtl+Shift+Enter”组合键由系统自动产生。完成后C58:C69将显示如图所示的分数分布情况。7、带班级统计方法=SUMPRODUCT($A$2:$A$7=101)*

10、($B$2:$B$780)101班 大于80分的人数。8、将百分制转换成不同的等级分将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”与“等级2”列所示。这里，“等级1”列是将百分制的分数转换成A(90100)、B(8089)、C(7079)、D(6069)与E(低于60)五个等级;“等级2”列是将百分制的分数转换成优(90100)、良(7589)、中(6074)与不及格(低于60)四个等级。具体使用哪种等级划分方法可根据实际情况自己确定。在百分制转换成不同的等级分时，一般使用IF(X，Y，Z)函数。其中有三个参数，第一个参数X为条件，不能加引号;第二个参数为条件成立

11、时的结果，如果是显示某个值，则要加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。该函数可以嵌套，即在第二个或第三个参数处可以再写一个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,中,不及格)，然后，利用自动填充柄将其复制到下方的几个单元格。9、使不及格的分数以红色显示统计学生

12、成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分(如第12行)。使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。该命令会弹出一个对话框，其中要求确认条件与相应的格式。对于“成绩”列，可先选中C2：C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。对于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的

13、“格式”按钮，从中选择红色，最后单击两次“确定”按钮。“等级2”列类似。10、计算单列数据相同的数据所出现的次数：如果数据都在A列显示，那么就在B列第一个位置处输入=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A:A,A1),)公式，使用拖曳方法一直拖动到相应数据列的最后一行。那么所合计的总和只出现在相同数据的第一个单元格，其它相同数据单元格则不在显示总数之和。11、多个表中如何调用相同数据后面的单元格内容：=VLOOKUP(B1,Sheet1!$B$4:$N$275,12,FALSE)或者带上目录和文件名称只需要进行更改名称就行。(其中sheet1!表示工作表，12表示要

14、调用的数据单元格所在列的个数)12、巧用Excel批量计算年龄一位做保险业务的朋友接到一笔团队大订单，有近千条记录。他花了半天的时间用Word录入了客户的档案后，根据要求需要补充填写每个客户的年龄。如果一个个用笔算，又是需要花上大半天的时间，还好客户档案中有每个人的身份证号，于是我用Excel很快帮她计算出了每位客户的年龄。1. 用MID函数计算出第一个客户的出生年月。函数表达方式如下：MID（E2，7，2），表示第一个客户的身份证号在E列第二行中，要从这个位置中的第7个文本始返回2个长度的字符，计算即可。13、excel整列多个单元格要添加相同的文字假如要添加文字的数据在A列,请在B1输入公式: =A1&要添加的文字用填充柄将公式向下复制到相应单元格。再选定B列，点“复制”，在A1单元格点右键，点“选择性粘贴”，选中“数值”，点“确定”，然后删除B列。注：如果要在前面添加文字，将公式改为： =要添加的文字&A1选中需要复制的单元格，然后将鼠标移至该单元格右下角，成“细十字”细状时，这种状态我们称之为“填充柄”，用填充柄将公式向下复制到相应单元格。