excel统计公式大全
统计范围内班级人数=COUNTIF($C$2:$C$201,"=1班")
语文每班平均分：
=AVERAGEJGS($D$3:$D$656, $A$3: $A$656,"五（1）")
或=AVERAGE(五年级!D3:D62)  =AVERAGE(五年级!D63:D120)
年级语文总平均分：
=AVERAGE(五年级!D3:D62)
引用：
=五年级! $K$15
相对平均分=C4-$N$4
或 =AVERAGE(五年级!D3:D62)-五年级!D3:D656)
名次=RANK(C4,($C$4:$M$4))
班级优秀率=COUNTIF(五年级!G2:G57,">=90")/COUNT(五年级!G2:G57)
五年级优秀率 =COUNTIF(五年级!D3:D662,">=90")/COUNT(五年级!D3:D662)
班级及格率  =COUNTIF(五年级!G2:G57,">=60")/COUNT(五年级!G2:G57)
五年级及格率 =COUNTIF(五年级!D3:D662,">=60")/COUNT(五年级!D3:D662)
五年级各班语数平均分及其名次表
平均分 =AVERAGE(五年级!D3:E62)
统计员常用的excel公式
不及格人数 =COUNTIF(五年级!D3:D62,"<60")
语文百颗星人数=COUNTIF(语百颗星!$B$1:$B$186,"一（1）")
数学百颗星人数  =COUNTIF(数百颗星!$B$1:$B$186,"一（1）")
未参评人数=COUNTA(五年级!$C$2:$C$57)-COUNTA(五年级!$G$2:$G$57)
任课教师=五年级!L3
数学平均分=AVERAGE(五年级!E3:E62)
相对平均分  =AVERAGE(五年级!E3:E62)-AVERAGE(五年级!E:E)
=AVERAGE(五年级!E63:E122)-AVERAGE(五年级!E:E)
名次  =RANK(D17,(D17:N17))
优秀率  =COUNTIF(五年级!E3:E62,">=90")/COUNT(五年级!E3:E62)
=COUNTIF(五年级!E63:E122,">=90")/COUNT(五年级!E63:E122)
及格率  =COUNTIF(五年级!E3:E62,">=60")/COUNT(五年级!E3:E62)
=COUNTIF(五年级!E63:E122,">=60")/COUNT(五年级!E63:E122)
不及格人数  =COUNTIF(五年级!E3:E62,"<60")
=COUNTIF(五年级!E63:E122,"<60")
百颗星人数 =COUNTIF(百颗星表!N:N,"五（1）")
=COUNTIF(百颗星表!N:N,"五（2）")
后进生人评  =AVERAGE(SMALL(五年级!E3:E62,1),SMALL(五年级!E3:E62,2),SMALL(五年级!E3:E62,3),SMALL(五年级!E3:E62,4),SMALL(五年级!E3:E62,5))
=COUNTA(五年级!C63:C122)-COUNTA(五年级!E63:E122)
任课教师  =五年级!N3
统计班级学生人数：
=COUNTIFs(五年级学生人数! $A$4: $A$597,"二（1）"，五年级学生人数! $D$4: $D$597,"男")
=COUNTIFS(五年级619人!$C$4:$C$622,"一（1）"，五年级619人!$G$4:$G$622,"男")
身份证号提取年月日方法：
=MJD（C2，7，8）
身份证号提取年龄
=DATEDJG(TEXT(MJD(A1,7,8),＂#-00-00＂),TODAY(),＂Y＂)
引用公式：
语文平均分
1=AVERAGE(五年级!J2:J59)
2=AVERAGE(五年级!J60:J118)
3=AVERAGE(五年级!J119:J177)
4=AVERAGE(五年级!J178:J235)
5=AVERAGE(五年级!J236:J294)
6=AVERAGE(五年级!J295:J353)
7=AVERAGE(五年级!J354:J412)
8=AVERAGE(五年级!J413:J472)
9=AVERAGE(五年级!J473:J531)
10=AVERAGE(五年级!J532:J590)
年级=AVERAGE(五年级!J2:J590)
语文相对平均分=C5-$M$5
数学相对平均分=C16-$M$16
名次=RANK(C5,($C$5:$L$5))
语文优秀率
1=COUNTIF(五年级!F2:F59,">=90")/COUNT(五年级! F2:F59)
2=COUNTIF(五年级! F60:F118,">=90")/COUNT(五年级! F60:F118) 3=COUNTIF(五年级! F119:F177,">=90")/COUNT(五年级! F119:F177) 4=COUNTIF(五年级! F178:F235,">=90")/COUNT(五年级! F178:F235) 5=COUNTIF(五年级! F236:F294,">=90")/COUNT(五年级! F236:F294)
7=COUNTIF(五年级! F354:F412,">=90")/COUNT(五年级! F354:F412)
8=COUNTIF(五年级! F413:F472,">=90")/COUNT(五年级! F413:F472)
9=COUNTIF(五年级! F473:F531,">=90")/COUNT(五年级! F473:F531) 10=COUNTIF(五年级! F532:F590,">=90")/COUNT(五年级! F532:F590)
年级语文总优秀率
=COUNTIF(五年级!F2: F590,">=90")/COUNT(五年级!F2:F590)
五年级数学优秀率
1=COUNTIF(五年级!G2:G59,">=90")/COUNT(五年级! G2:G59)
2=COUNTIF(五年级! G60:G118,">=90")/COUNT(五年级! G60:G118)
3=COUNTIF(五年级! G119:G177,">=90")/COUNT(五年级! G119:G177) 4=COUNTIF(五年级! G178:G235,">=90")/COUNT(五年级! G178:G235) 5=COUNTIF(五年级! G236:G294,">=90")/COUNT(五年级! G236:G294) 6=COUNTIF(五年级! G295:G353,">=90")/COUNT(五年级! G295:G353) 7=COUNTIF(五年级! G354:G412,">=90")/COUNT(五年级! G354:G412) 8=COUNTIF(五年级! G413:G472,">=90")/COUNT(
五年级! G413:G472) 9=COUNTIF(五年级! G473:G531,">=90")/COUNT(五年级! G473:G531) 10=COUNTIF(五年级! G532:G590,">=90")/COUNT(五年级! G532:G590)五年级数学总优秀率
=COUNTIF(五年级!G2: G590,">=90")/COUNT(五年级!G2:G590)
五年级英语优秀率
1=COUNTIF(五年级!H2:H59,">=90")/COUNT(五年级! H2:H59)
2=COUNTIF(五年级! H60:H118,">=90")/COUNT(五年级! H60:H118)
3=COUNTIF(五年级! H119:H177,">=90")/COUNT(五年级! H119:H177) 4=COUNTIF(五年级! H178:H235,">=90")/COUNT(五年级! H178:H235) 5=COUNTIF(五年级! H236:H294,">=90")/COUNT(五年级! H236:H294)
7=COUNTIF(五年级! H354:H412,">=90")/COUNT(五年级! H354:H412) 8=COUNTIF(五年级! H413:H472,">=90")/COUNT(五年级! H413:H472) 9=COUNTIF(五年级! H473:H531,">=90")/COUNT(五年级! H473:H531) 10=COUNTIF(五年级! H532:H590,">=90")/COUNT(五年级! H532:H590)五年级英语总优秀率
=COUNTIF(五年级!H2: H590,">=90")/COUNT(五年级!H2:H590)
五年级英语及格率
1=COUNTIF(五年级!H2:H59,">=60")/COUNT(五年级! H2:H59)
2=COUNTIF(五年级! H60:H118,">=60")/COUNT(五年级! H60:H118)
3=COUNTIF(五年级! H119:H177,">=60")/COUNT(五年级! H119:H177) 4=COUNTIF(五年级! H178:H235,">=60")/COUNT(五年级! H178:H235) 5=COUNTIF(五年级! H236:H294,">=60")/COUNT(五年级! H236:H294) 6=COUNTIF(五年级! H295:H353,">=60")/COUNT(五年级! H295:H353) 7=COUNTIF(五年级! H354:H412,">=60")/COUNT(五年级! H354:H412) 8=COUNTIF(五年级! H413:H472,">=60")/COUNT(五年级! H413:H472) 9=COUNTIF(五年级! H473:H531,">=60")/COUNT(五年级! H473:H531) 10=COUNTIF(五年级! H532:H560,">=60")/COUNT(五年级! H532:H560)五年级英语总及格率
=COUNTIF(五年级!H2: H590,">=60")/COUNT(五年级!H2:H590)
五年级语及格率
1=COUNTIF(五年级!F2:F59,">=60")/COUNT(五年级! F2:F59)
2=COUNTIF(五年级! F60:F118,">=60")/COUNT(五年级! F60:F118)
3=COUNTIF(五年级! F119:F177,">=60")/COUNT(五年级! F119:F177)
4=COUNTIF(五年级! F178:F235,">=60")/COUNT(五年级! F178:F235)
5=COUNTIF(五年级! F236:F294,">=60")/COUNT(五年级! F236:F294)

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