=90")80-89分:=COUNTIF(C4:C18,">=80")-COUNTIF(C4:C18,">=90")70-79分:"> =90")80-89分:=COUNTIF(C4:C18,">=80")-COUNTIF(C4:C18,">=90")70-79分:" />
Excel学生成绩统计常用公式.doc

Excel学生成绩统计常用公式.doc

ID:56393907

大小:72.50 KB

页数:7页

时间:2020-06-23

Excel学生成绩统计常用公式.doc_第1页
Excel学生成绩统计常用公式.doc_第2页
Excel学生成绩统计常用公式.doc_第3页
Excel学生成绩统计常用公式.doc_第4页
Excel学生成绩统计常用公式.doc_第5页
Excel学生成绩统计常用公式.doc_第6页
Excel学生成绩统计常用公式.doc_第7页
资源描述:

《Excel学生成绩统计常用公式.doc》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库

1、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

2、,">=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)平均分:=AVERAGE(B4:D4)3、与考人数、记分人数:=COUNT(C4:C18,G4:G18,K4:K18,O4:

3、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>=

4、70,"良",IF(A4>=60,"合格","待合格")))1、2、3、4要与优、良、合格、待合格相对应=CHOOSE(IF(A1>=80,1,IF(A1>=70,2,IF(A1>=60,3,4))),"优","良","合格","待合格")这两个函数指不录入成绩时,单元格为空。录入0时,评定为待合=IF(C4>=80,"优",IF(C4>=70,"良",IF(C4>=60,"合格",IF(ISNUMBER(C4),"待合",IF(ISBLANK(C4),"")))))=LOOKUP(A1,{0,60,70,80},{"D"

5、,"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,"良","

6、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>=8

7、0,"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中求出出生日期,=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))格式为yyyy-m-d,可更改为yyyy-mm-dd的格式。补充说明:注意检查括号是否成对出现其余的为18位的身证,就从第9位开始提取8位是15位的,就从第7位

8、开始,提取6位(1)=IF(LEN(A1)=15,MID(A1,7,6),MID(A1,7,8))注意检查括号是否成对出现判断是几位的身份证提取后格式为670520或19670520。将多个文本字符窜成一个(2)如果身份证都是18位的,也可能直接用=MID(A1,7,8)(3)提取后的出生日期是连续的,

当前文档最多预览五页,下载文档查看全文

此文档下载收益归作者所有

当前文档最多预览五页,下载文档查看全文
温馨提示:
1. 部分包含数学公式或PPT动画的文件,查看预览时可能会显示错乱或异常,文件下载后无此问题,请放心下载。
2. 本文档由用户上传,版权归属用户,天天文库负责整理代发布。如果您对本文档版权有争议请及时联系客服。
3. 下载前请仔细阅读文档内容,确认文档内容符合您的需求后进行下载,若出现内容与标题不符可向本站投诉处理。
4. 下载文档时可能由于网络波动等原因无法下载或下载错误,付费完成后未能成功下载的用户请联系客服处理。