因为工作需要，花了点时间整理出在EXCEL中成绩统计所用到的公式，统计过程中解决不到的问题一定能在这里受到启发。因为有学生成绩，所以excel文件不便上传，如果有问题或想交流，请qq:10082823讨论 或youufis@sina.com
============================================================
公式函数：
注：数组公式 ctrl+shift+enter 结束输入
1、总分：sum
三科总分：=SUM(D2:F2)
2、级排名：rank
三科级排名：=RANK(G2,G$2:G$630)
3、班排名：sumproduct
=SUMPRODUCT((A$2:A$630=A2)*(B$2:B$630<>B2)*(G$2:G$630>G2))+1
* 号表示and的意思，就是条件"与"
条件一：A$2:A$630=A2 表示所在班
条件二：B$2:B$630<>B2 除了自己
条件三：G$2:G$630>G2 大于自己成绩
加1就是表示自己所在班上的名次
4、级平均分：average
=AVERAGE(D2:D628)
5、班平均分 averageif (2007版）
=AVERAGEIF(全级!$A$2:$A$628,"=1",全级!D2:D628)
或
班平均分 average （2003版）数组函数
｛=AVERAGE(IF(全级!$A$2:$A$628=1,全级!$D$2:$D$628))｝
注：多条件平均 {=AVERAGE(IF((全级!$A$2:$A$628=1)*(全级!$D2:$D628>90),全级!$E$2:$E$628))}
6 级及格人数 countif
=COUNTIF(D2:D628,">=90")
7 班及格人数 countifs (2007版)
=COUNTIFS(全级!$A$2:$A$628,"=1",全级!D2:D628,">=90")
或
班及格人数 sumproduct(2003版) 相当于多条件计数
=SUMPRODUCT((全级!$A$2:$A$628=1)*(全级!$D$2:$D$628>=90))
或{=count(if(全级!$A$2:$A$628=1)*(全级!$D$2:$D$628>=90),全级!$B$2:$B$628))}
8 级及格率 =COUNTIF(D2:D628,">=90")/COUNT(D2:D628)
班及格率 =COUNTIFS(全级!$A$2:$A$628,"=1",全级!D2:D628,">=90")/COUNTIF(全级!$A$2:$A$628,"=1")
9 级优秀人数 =COUNTIF(D2:D628,">=120")
班优秀人数 =COUNTIFS(全级!$A$2:$A$628,"=1",全级!D2:D628,">=120")
10 级优秀率 =COUNTIF(D2:D628,">=120")/COUNT(D2:D628)
班优秀率 =COUNTIFS(全级!$A$2:$A$628,"=1",全级!D2:D628,">=120")/COUNTIF(全级!$A$2:$A$628,"=1")
11 级最高分 =MAX(D2:D628)
班最高分 ｛=MAX(IF(全级!$A$2:$A$628=1,全级!D2:D628))｝数组公式
12 级标兵 OFFSET
=OFFSET(全级!$V1,MATCH(MAX(全级!$V$2:$V$628),全级!$V2:$V628,0),-19)
：返回最大值所在行号，MATCH(MAX(全级!$V$2:$V$628),全级!$V2:$V628,0)
：全级!$V1 指出参照列是
：-19 相对参照列 全级!$V1 向左偏移19列
*指定条件：｛=OFFSET(全级!$V1,MATCH(MAX(IF(全级!$A$2:$A$628>1,全级!$V$2:$V$628)),全级!$V$2:$V$628,0),-19)｝
13 班标兵 VLOOKUP 多条件返回值（学习了半天才完全明白什么意思）
=VLOOKUP(MAX(IF(全级!$A$2:$A$628=1,全级!$V$2:$V$628))&1,IF({1,0},全级!$V$2:$V$628&全级!$A$2:$A$628,全级!$C$2:$C$628),2,FALSE)
：返回指定条件（1班）的最大值：MAX(IF(全级!$A$2:$A$628=1,全级!$V$2:$V$628))
：&1 增加一个条件（1班），相当于是1班的最大值
：IF({1,0},全级!$V$2:$V$628&全级!$A$2:$A$628 生成一个新的数组。
：全级!$C$2:$C$628 为Vlookup多增加一列数据
：2，返回新数组的第二列，指C列
=====================================================
2013-7-12补简化公式 =INDEX(全级!$C$2:$C$627,MATCH(MAX(IF(全级!$A$2:$A$627=2,全级!$V$2:$V$627)),全级!$V$2:$V$627,0))
1、找出班最高分：MAX(IF(全级!$A$2:$A$627=2,全级!$V$2:$V$627)),
2、MATCH根据最高分返回最高分所在的行号
3、INDEX根据行号返回需要的所在列的数据
=====================================
14 单科金牌（多个返回的数据）
=IF(COUNTIF(全级!$D$2:$D$628,MAX(全级!$D$2:$D$628))>=ROW()-1,INDEX(INDIRECT("全级!$c$2:$c$628"),SMALL(IF(INDIRECT("全级!$d$2:$d$628")=MAX(全级!$D$2:$D$628),ROW(INDIRECT("全级!$d$2:$d$628"))-1,65536),ROW(1:1))),"")
：COUNTIF(全级!$D$2:$D$628,MAX(全级!$D$2:$D$628)) 有几个要返回的数据，即相同的值
：COUNTIF(全级!$D$2:$D$628,MAX(全级!$D$2:$D$628))>=ROW()-1 做为if的条件：如果超过个数，就返回一个空值"",否则把返回的数据求出来。
：INDIRECT("全级!$c$2:$c$628") 返回C列的所有姓名，这里等同 全级!$c$2:$c$628
：INDIRECT("全级!$d$2:$d$628") 返回D列的所有成绩
：IF(INDIRECT("全级!$d$2:$d$628")=MAX(全级!$D$2:$D$628),ROW(INDIRECT("全级!$d$2:$d$628"))-1,65536) 如果找到条件中的值，返回它的行号，否则返回65536，成为small的第一个参数，数据数组
：ROW(1:1)返回值是1，表示第一满足条件的数据，向下填充公式，就变成ROW(2,2),返回值就是2，表示要找第二个满足条件的数据
：index根据行号返回满足条件的数据。数据在全级!$c$2:$c$628中，
15 优胜奖（比如总成绩级前60名为成绩优胜一等奖，级60-120之间二等奖）重点是要考虑相同成绩的
=OFFSET(全级!$V$1,MATCH(LARGE(全级!$V$2:$V$628+1/ROW(全级!$V$2:$V$628),ROW()-2),全级!$V$2:$V$628+1/ROW(全级!$V$2:$V$628),0),-19)
LARGE(全级!$V$2:$V$628+1/ROW(全级!$V$2:$V$628),ROW()-2)：因为large只能返回相同值中的第一个数据，所以在这里要把成绩变成是唯一的小技巧：把成绩加上成绩所在行号的倒数。这时成绩就变成唯一值
在MATCH中的第二参数一定也加上行号的倒数，否则就会找不到出错。
===================================
补充 优胜奖（多条件）：主要用IF的多条件：
*号是and，条件1*条件2*条件3
+号是or , 条件1+条件2+条件3
=OFFSET(全级!$V$1,MATCH(LARGE(IF((全级!$A$2:$A$627>=5)*(全级!$A$2:$A$627<=8),全级!$V$2:$V$627+1/ROW(全级!$V$2:$V$627)),ROW()-2),全级!$V$2:$V$627+1/ROW(全级!$V$2:$V$627),0),-19)
=OFFSET(全级!$V$1,MATCH(LARGE(IF((全级!$A$2:$A$627<5)+(全级!$A$2:$A$627>8),全级!$V$2:$V$627+1/ROW(全级!$V$2:$V$627)),ROW()-32),全级!$V$2:$V$627+1/ROW(全级!$V$2:$V$627),0),-19)
============================================
2013-1-11补
1、查找满足条件列：AE列中数据是本科的，即原始!$AE$3:$AE$318")="本科
2、返回满足条件列相对应列的数据，比如A列，B列，本例返回是A列数据，即原始!$A$3:$A$318
3、向下拖动即可。
=INDEX(INDIRECT("原始!$A$3:$A$318"),SMALL(IF(INDIRECT("原始!$AE$3:$AE$318")="本科",ROW(INDIRECT("原始!$AE$3:$AE$318"))-2,65536),ROW(1:1)))
==========================
2013-8-26记 IF函数的返回需要的数据区域
INDEX SMALL IF ROW
以上公式简化成：
=INDEX(原始!A:A,SMALL(IF(原始!$AE$3:$AE$318="本科",ROW(原始!$AE$3:$AE$318),65536),ROW(A1)))
1、INDEX的第一个参数是A:A 指A列，不用范围表示
2、ROW根据IF的条件返回是绝对行号，不用相对值表示
3、ROW(A1)表示返回第一个要找的结果
=========================================
2013-9-8 match函数可以做两个数组区域相减操作（也叫去重复值操作）
如：从第93行起取依次取25名1或2或3或4班的学生作为一等奖，然后再从93行起依次取30名学生作为二等奖，但不包括前25名的一等奖。
其实就是从第93行起减去已取的25名一等奖的学生，依次再取30学生（已取25学生的数据区域为：$J$4:$J$28）
公式：=INDEX(理排!C:C,SMALL(IF(ISERROR(MATCH(理排!$C$93:$C$318,$J$4:$J$28,0)),ROW($93:$318),65536),ROW(C1)))
1 ISERROR(MATCH(理排!$C$93:$C$318,$J$4:$J$28,0)) 找不到的值就是不重复值
2、返回ROW($93:$318),即所在相应的行号，否则用65536填充
去$B$2:$B$11列中的重复值,把不重复值依次求出来
=INDEX(B:B,SMALL(IF(MATCH($B$2:$B$11,$B$2:$B$11,0)=ROW($B$2:$B$11)-1,ROW($B$2:$B$11),65536),ROW(1:1)))
1、MATCH($B$2:$B$11,$B$2:$B$11,0) 找到相应值的第一个行号
2、=ROW($B$2:$B$11)-1 和自己本身所在的行号比较，如果相等，则为不重复的值，并返回该值的行号ROW($B$2:$B$11)，否则用65536填充。
=====================================================
2013-9-12：查找D列中的多个最大值，并返回相对应的A列值（返回多值问题）
F1单元格公式：=INDEX(A$1:A$14,SMALL(IF((D$1:D$14)=MAX(D$1:D$14),ROW(D$1:D$14),65535),ROW(1:1)))
match只能返回第一个找到值的行号，当有多个值需要返回的时候用IF构造数组，返回对应值的所在行号。
================================================
2013-10-14：记
A2=2013-12-20-伦教中学-B3.DOC 要求从字符中取汉字
=MID(A2,MATCH(1,N(MID(A2,ROW($1:$99),1)>"吖"),),LENB(A2)-LEN(A2)) 数组公式 吖最小的汉字 ??畲蟮暮鹤?/p>