Excel成绩统计及常⽤统计⽅法
统计和分析学⽣成绩
1、按学⽣成绩排列名次
Excel中提供了⼀个⽤于统计名次的RANK( )函数，RANK( )函数可统计出⼀个单元格中的数值，在⼀个单元格区域所有数值中的排名（排序）结果。
RANK( )函数的⽤法：RANK( x1,x2,x3) 。其中x1为要排名次的单元格，x2为排名次的单元格区域（即排列名次的范围），x3⽤于指定升序或降序排名次。
⽤RANK( )函数可以极为⽅便地按学⽣成绩计算名次，例如对图5-13中的学⽣成绩表要按总分计算出每个学⽣的名次，并将名次结果分别填⼊J2:J101区域的各个单元格中。
⾸先，按前⾯介绍的⽅法，利⽤SUM( )函数在I2:I101中计算出全部学⽣的总分；单击单元格J1，在其中输⼊“总分名次”，单击单元格J2，在其中输⼊公式“=RANK (I2,I$2:I$101)”后按【Enter】键即可统计出表中第1个学⽣的名次。由于在对每个学⽣统计名次时，其范围始终应该是在区域I2:I101，所以公式的RANK( )函数中使⽤了“$”符号，⽤于标识固定不变的单元格区域，即“绝对引⽤”，以便下⼀步处理中利⽤复制公式的⽅法计算其他学⽣的名次。
其次，单击选定单元格J2，然后拖动其“填充柄”标记⾄单元格J101，即可将公式复制到下⾯的各个单元格，计算出其余学⽣的名次。
2、按分数段统计⼈数分布
利⽤Excel中的频度函数FREQUENCY( )，可以较⽅便地统计分布于各个分数段的学⽣⼈数，统计出的分布数据对于分析试题难度是否适当是⼗分有⽤的。设计较合理的试题，其相应的成绩应该呈正态分布。
FREQUENCY( )函数可以分别计算出⼀个单元格区域中的数据在各个不同数据段分布的数⽬，因此计算结果将是⼀组数值（即数组），这⼀组数值将分别填⼊⼯作表中同⼀列的若⼲个单元格中。
FREQUENCY( )函数的⽤法：FREQUENCY( x1,x2)。其中的参数x1可⽤于指定要进⾏频度统计的单元格区域，x2指定要统计的各个数据段的分段点的数值。
由于频度函数FREQUENCY( )的返回值是⼀组数据（数组），所以在输⼊由FREQUENCE( )函数组成的公式之前，⾸先要选定⼀个⽤于保存函数计算结果（多个数值）的单元格区域并按【F2】键，然后输⼊公式。在公式输⼊完成后，需按【Ctrl+Shift+Enter】组合键，以确认数组公式的输⼊。
使⽤频度函数FREQUENCY( )统计成绩分布情况，⾸先确定要统计成绩分布的分数段，例如在图5-13
所⽰的学⽣成绩表中，要统计出英语成绩“低于60”、“60～69”、“70～79”、“80～89”、“90以上”共5个分数段中分布的⼈数，可选择59、69、79和89作为分数的分段点；所计算出的5个分数段的分布⼈数，将分别填⼊
E105:E109中的5个单元格中，如图5-15所⽰。
图5-15 统计各个分数段的⼈数
⾸先，为了清楚地标明各个分数段的⼈数分布，可在D105:D109的5个单元格中分别输⼊“低于60分⼈数”、“60～69分⼈数”、“70～79分⼈数”、“80～89分⼈数”、“90分以上⼈数”作为说明⽂字，如图5-15所⽰。
其次，选定单元格区域E105:E109，然后按【F2】键，接着在选定的区域中输⼊公式“=FREQUENCY(
E2:E101,{59,69,79,89})”，在公式输⼊完成后按【Ctrl+Shift+Enter】组合键，即可在E105:E109的5个单元格中统计出分布于5个分数段的⼈数，如图5-15所⽰。
3、按分数段评定考试等级
在学⽣成绩的处理中，有时可能需要根据考试成绩的分数为每个学⽣评定相应的成绩等级或等次。在Excel中，由⼯作表中的学⽣分数确定相应的等级，可⽤逻辑函数IF()实现。例如在图5-13所⽰的学⽣成绩⼯作表中，要将英语课考试成绩中低于60分的等级定为“不
合格”、60～84分定为“合格”、85分以上定为“优良”，并将每个学⽣的等级分别填⼊L2:L101的各个单元格中。
⾸先，单击选定单元格L1，在其中输⼊“英语等级”。单击选定单元格L2，输⼊公式“=IF(E2<60,"不合格",IF(E2>=85,"优良","合格"))”，然后按【Enter】键，即可确定出第1个学⽣的等级。
其次，单击选定单元格L2，然后拖动其“填充柄”标记⾄L101，即可将公式复制到下⾯L3:L101的各个单元格，从⽽确定出其余学⽣的等级，如图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,"<60")
2. 在C59内输⼊公式统计90分⾄100之间的⼈数：
=CountIF(C2:C56,">=90")
3. 在C60内输⼊公式统计80⾄90之间的⼈数：
=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⾄30……90⾄100之间这么多个分数段都统计出来，就要写上⼗条公式了。
其实，Excel已经为我们提供了⼀个进⾏频度分析的FreQuency 数组函数，它能让我们⽤⼀条数组公式就轻松地统计出各分数段的⼈数分布。例如，我们要统计出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)*($B$2:$B$7>80))
101班⼤于80分的⼈数。
8、将百分制转换成不同的等级分
将百分制转换成不同的等级分有多种不同的划分⽅法，其所需结果如图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,"中","不及格")))，然后，利⽤⾃动填充柄将其复制到下⽅的⼏个单元格。
9、使不及格的分数以红⾊显⽰
统计学⽣成绩时经常需要将不及格的分数⽤红⾊显⽰，其结果如图1中红⾊显⽰部分(如第12⾏)。
使不及格的分数以红⾊显⽰需要使⽤“格式”菜单中的“条件格式”命令。该命令会弹出⼀个对话框，其中要求确认条件与相应的格式。
对于“成绩”列，可先选中C2：C13，然后使⽤“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使⽤默认的“单元格数值”，中间选“⼩于”，右边填写60，然后单击右边的“格式”按钮，从中选择红⾊，最后单击两次“确定”按钮。
对于“等级1”列，可先选中D2：D13，然后使⽤“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使⽤默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红⾊，
最后单击两次“确定”按钮。“等级2”列类似。
10、计算单列数据相同的数据所出现的次数：
如果数据都在A列显⽰，那么就在B列第⼀个位置处输⼊=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A:A,A1),"")公式，使⽤拖曳⽅
countif函数求占比法⼀直拖动到相应数据列的最后⼀⾏。那么所合计的总和只出现在相同数据的第⼀个单元格，其它相同数据单元格则不在显⽰总数之和。
11、多个表中如何调⽤相同数据后⾯的单元格内容：
=VLOOKUP(B1,Sheet1!$B$4:$N$275,12,FALSE)或者带上⽬录和⽂件名称只需要进⾏更改名称就⾏。
(其中sheet1!表⽰⼯作表，12表⽰要调⽤的数据单元格所在列的个数)
12、巧⽤Excel批量计算年龄
⼀位做保险业务的朋友接到⼀笔团队⼤订单，有近千条记录。他花了半天的时间⽤Word录⼊了客户的档案后，根据要求需要补充填写每个客户的年龄。如果⼀个个⽤笔算，⼜是需要花上⼤半天的时间，
还好客户档案中有每个⼈的⾝份证号，于是我⽤Excel很快帮她计算出了每位客户的年龄。
1. ⽤MID函数计算出第⼀个客户的出⽣年⽉。函数表达⽅式如下：MID（E2，7，2），表⽰第⼀个客户的⾝份证号在E列第⼆⾏中，要从这个位置中的第7个⽂本始返回2个长度的字符，计算即可。
13、excel整列多个单元格要添加相同的⽂字
假如要添加⽂字的数据在A列，请在B1输⼊公式: =A1&"要添加的⽂字"⽤填充柄将公式向下复制到相应单元格。再选定B列，点“复制”，在A1单元格点右键，点“选择性粘贴”，选中“数值”，点“确定”，然后删除B列。
注：如果要在前⾯添加⽂字，将公式改为： ="要添加的⽂字"&A1 选中需要复制的单元格，然后将⿏标移⾄该单元格右下⾓，成“细⼗字”细状时，这种状态我们称之为“填充柄”，⽤填充柄将公式向下复制到相应单元格。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。