Excel学⽣成绩统计常⽤公式
Excel学⽣成绩、各种报表统计常⽤公式
1、分数段⼈数统计
90-100分：=COUNTIF(C4:C18,">=90")
80-89分：=COUNTIF(C4:C18,">=80")-COUNTIF(C4:C18,">=90")
70-79分：=COUNTIF(C4:C18,">=70")-COUNTIF(C4:C18,">=80")
60-69分：=COUNTIF(C4:C18,">=60")-COUNTIF(C4:C18,">=70")
50-59分：=COUNTIF(C4:C18,">=50")-COUNTIF(C4:C18,">=60")
41-49分：=COUNTIF(C4:C18,">40")-COUNTIF(C4:C18,">=50")
40分及以下：=COUNTIF(C4:C18,"<=40")
2、最⾼分：=MAX(C4:C18,G4:G18,K4:K18,O4:O13)
最低分：=MIN(C4:C18,G4:G18,K4:K18,O4:O13)
总分：=SUM(C4:C18,G4:G18,K4:K18,O4:O13)
平均分：=A VERAGE(B4:D4)
3、与考⼈数、记分⼈数：=COUNT(C4:C18,G4:G18,K4:K18,O4:O18)
优⽣⼈数：=COUNTIF(C4:C18,">=80")
差⽣⼈数：=COUNTIF(C4:C18,"<=40")
双科合格⼈数：=SUMPRODUCT((C4:C18>=60)*(D4:D18>=60))双科优秀⼈数：=SUMPRODUCT((C4:C18>=80)*
(D4:D18>=80)) 4、条件求和：=SUMIF(B2:B56,"男"，K2:K56) ——假设B列存放学⽣的性别，K列存放学⽣的分数，则此函数返回的结果表⽰求该班男⽣的成绩之和；
5、学⽣分数转化为等级评定：
=IF(A4>=80,"优",IF(A4>=70,"良",IF(A4>=60,"合格","待合格")))
=CHOOSE(IF(A1>=80,1,IF(A1>=70,2,IF(A1>=60,3,4))),"优","良","合格","待合格")
1、2、3、4要与优、良、合格、待合格相对应
=IF(C4>=80,"优",IF(C4>=70,"良",IF(C4>=60,"合格",IF(ISNUMBE R(C4),"待合",IF(ISBLANK(C4),"")))))
=LOOKUP(A1,{0,60,70,80},{"D","C","B","A"})
=LOOKUP(A1,{0,60,70,80,90},{"不及格","及格","中","良","优"}) =IF(E4<60,"D",IF(E4<70,"C",IF(E4<80,"B","A")))
=if(A1<60,"E",if(A1<70,"D",if(A1<80,"C",if(A1<90,"B","A")))) =IF(A1<60,"不及格",IF(A1<70,"及
格",IF(A1<80,"中",IF(A1<90,"良","优"))))
=IF(A1>=90,"优",IF(A1<90,"良","if(a1<70,"中",if(a1=<60,"及格","很差")))
=IF(AND(C5>=90,C5<=100),"A",IF(AND(C5>=80,C5<90),"B",IF (AND(C5>=70,C5<80),"C",IF(C5<70,"D",E)))).
多科⽬等级评定：
=CONCATENATE(IF(C2>=80,"A",IF(C2>=60,"B","C")),IF(D2>=80,"A",IF(D2>=60,"B","C")),IF(E2>=80,"A",IF(E2>=60,"B","C"))) =IF(C2>=80,"A",IF(C2>=60,"B","C"))&IF(D2>=80,"A",IF(D2>=60,"B","C"))&IF(E2>=80,"A",IF(E2>=60,"B","C"))
6、18位⾝份证号码转换成出⽣⽇期的函数公式：如果E2中是⾝份证，在F2中求出出⽣⽇
15个常用excel的if函数期，=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MI DB(E2,13,2)) 格式为yyyy-m-d ，可更改为yyyy-mm-dd 的格式。补充说明：这
两个函数指不录⼊成绩时，单元格为空。录⼊0时，评定为待合
（1）MID（A1，7，8
提取后格式为670520或19670520。
（2）如果⾝份证都是18位的，也可能直接⽤=MID（A1，7，8）
（3）提取后的出⽣⽇期是连续的，可以中间加“-”或“.”
（LEFT（A1，2），”年”，MID（A1，3，2），”⽉”，RIGHT（A1，2），”⽇”）格式为67年05⽉20⽇，年⽉⽇换成“-”就成为67-05-20
（4）也可以从18位⾝份证号码中直接提取
=CONCATENATE(MID(A1,9,2)，"."，MID(A1,11,2)) 格式为67.05
7、根据⾝份证号码⾃动录⼊男⼥：
=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,"⼥","男")
8、根据⾝份证号码转化在年龄：（适合于不计⽉份的）
=YEAR(TODAY())-V ALUE(MID(A1,7,4))
第2种利⽤⾝份证号码⽣成年龄：
=IF(LEN(D4)=18,YEAR(TODAY())-MID(D4,7,4),YEAR(TODAY())-MID(D4,7,2)-1 900)
9、学⽣年龄的公式应⽤：
=(YEAR(B2)-YEAR(A2))+IF((MONTH(B2)-MONTH(A2))<0,-1,0)
设置截⽌时间的区域，也可以设为绝对引⽤，只⽤⼀个单元格为
截⽌时间。学⽣的年龄计算时，截⽌时间设为如2013-08-31，⼤⼈的年龄计算截⽌时间设为如2013-12-31。出⽣年⽉区域
判断是⼏位的⾝份证是15位的，就从
第7位开始，提取
6位
注意检查括号是
否成对出现
其余的为18位的
⾝证，就从第9位
开始提取8位
将多个⽂本字符窜成⼀个
说明：⽣成年龄的单元格C2要设置成：[格式]→[单元格]→[数字]，选择“数值”，并将“⼩数位数”设为0，其中IF语名是判断学⽣的出⽣⽉份是否超过8⽉，如超过则要⽤年份之差再减1。第⼀个学⽣年龄计算出来后，将⿏标移到此单元格的右下⾓，光标变成“+”后，向下拖动复制单元格，快速完成每个学⽣“年龄”的计算。
10、男⼥混合的表格中，计算男或⼥的年龄和（条件求和）
要计算的区域性别年龄区域
11、计算男的个数（计算区域内符合条件的个数）
=COUNTIF(C479:C493,"男")
COUNTIFS符合多条件的个数，如，
=COUNTIFS(A2:A5,">=60",B2:B5,">=60")可计算语⽂合格，数学也合格（双科合格）的⼈数。和
=SUMPRODUCT((C4:C18>=80)*(D4:D18>=80))是⼀样的。但是在Excel 2003中没有COUNTIFS函数，在WPS20007、excel2007以上版本才有。
12、计算符合“男⽣、4岁”这两个条件的单元格的个数：
=SUMPRODUCT((C479:C493="男")*1,(E479:E493=4)*1)⽤
SUMPRODUCT函数代替COUNTIFS。符合多个条件的，⽅法相同。
（1）还可以⽤：=SUM(IF((C88:C102="男")*(E88:E102=4),1,0))
注意: 1、输⼊完公式后要按Ctrl+Shift+Enter 键，让它⾃动加上数组公式符号"{}"。2、也可以⽤于双科合格率的⼈数统计，还可以是符合多个条件的单元格⼈数统计。3、⽂本的参数加" "，数字的参数不加" "。4、年龄不能⽤⽂本格式，⽤常规，其它的符号如“√”⽤⽂本格式。
（2）还可以使⽤COUNT 函数和IF 函数嵌套计算：=COUNT(IF(((B 1:B3>80)*(C1:C3>80)*(D1:D3>80)*(E1:E3>80)*
(F1:F3>80)),A1:A3))， B 语⽂C 数学D 物理E 化学F 英语A 学⽣姓名，按Ctrl+Shift+Enter 键，让它⾃动加上数组公式符号"{}"。⼤于0且⼩于30的单元格个数
=SUM(IF(($V$4:$V$75<30)*($V$4:$V$75>0),1,0))
=COUNTIF(V4:V75,">=0")-COUNTIF(V4:V75,">=30")
=SUMPRODUCT((V4:V75<30)*(V4:V75>0))
=SUM((V4:V75>0)*(V4:V75<30))
13、直接计算⼥⽣平均年龄的三种⽅法：
=SUMIF(C2:C21,"⼥",B2:B21)/COUNTIF(C2:C21,"⼥")
=AVERAGEIF(C2:C21,"⼥",B2:B21)
=AVERAGE(IF(C2:C21="⼥",B2:B21))
14、计算20到30岁年龄段的⼥⽣⼈数（⽤12的两个公式也⾏） =COUNTIFS(C2:C21,"⼥",B2:B21,">20",B2:B21,"<30")
15、COUNT 和COUNTA 公式的区别（都是求录⼊内容的单元格的个数） 16、将两个单元格A1、B1的内容合并到⼀个单元格C1： C1输⼊=A1&B1 区域的条件是数区域的条件是⽂字
性别区域年龄区域

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