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)

平均分：=AVERAGE(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))),"优","良","合格","

待合格") =IF(C4>=80,"优

",IF(C4>=70,"良",IF(C4>=60,"合格",IF(ISNUMBER(C4),"待

合=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>=7

0,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"))

1、2、3、4要与优、良、合格、待合格相对应

6、18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E 2,13,2)) 格式为yyyy-m-d，可更改为yyyy-mm-dd的格式。

补充说明：

（1）=IF(LEN(A1)=15

MID（A1，7，8）)

提取后格式为670520或。

（2）如果身份证都是18位的，也可能直接用=MID（A1，7，8）

-”或“.”

=CONCATENATE（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)) 格式为

7、根据身份证号码自动录入男女：

=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,"女","男") 8、根据身份证号码转化在年龄：（适合于不计月份的）

=YEAR(TODAY())-VALUE(MID(A1,7,4))

第2种利用身份证号码生成年龄：

=IF(LEN(D4)=18,YEAR(TODAY())-MID(D4,7,4),YEAR(TODAY())-MID(D4,7,2)-1900)

9、学生年龄的公式应用：

=(YEAR(B2)-YEAR(A2))+IF((MONTH(B2)-MONTH(A2))<0,-1,0)

说明：生成年龄的单元格C2要设置成：[格式]→[单元格]→[数字]，选择“数值”，并将“小数位数”设为0，其中IF语名是判断学生的出生月份是否超过8月，如超过则要用年份之差再减1。第一个学生年龄计算出来后，将鼠标移到此单元格的右下角，光标变成“+”后，向下拖动复制单元格，快速完成每个学生“年龄”

的计算。

10、男女混合的表格中，计算男或女的年龄和（条件求和）

=SUMIF(C5:C19,"男",E5:E19)

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岁”这两个条件的单元格的个数：

设置截止时间的区域，也可以设为绝对引用，只用一个单元格为

截止时间。学生的年龄计算时，截止时间设为如2013-08-31，大

人的年龄计算截止时间设为如2013-12-31。}

出生年月区域

/

要计算的区域

性别年龄区域

=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(((B1:B

3>80)*(C1:C3>80)*(D1:D3>80)*(E1:E3>80)*(F1:F3>80)),A1:A3))，

B 语文

C 数学

D 物理

E 化学

F 英语A 学生姓名，按Ctrl+Shift+Enter

13、直接计算女生平均年龄的三种方法：

15、COUNT 和COUNTA 公式的区别（都是求录入内容的单元格的个

数）

16、将两个单元格A1、B1的内容合并到一个单元格C1：

C1输入=A1&B1

将一个单元格A1的内容如“福建古田杉洋”，分解到两个单元格

B1、C1： B1输入=LEFT （A1，2），C1输入=RIGHT （A1，4）

"

17、平方、立方的计算：=5^2, =5^3

也可以用函数=POWER （4，2）计算4的平方。

18、时间显示牌：如距放假还有 =DATE(2013,6,29)-TODAY()&"天"

;

19、计算教龄：

工作时间=

20、计算年龄=YEAR($A$1 )-YEAR （B1） 不计月份

[年龄的单元格要设置为常规]

21、生日提醒函数：

=IF(AND(MONTH(TODAY())=MONTH(E3),DAY(TODAY())=DAY(E3)),"生日

","非生日")

"

22、电子表格的列中自动添加相同数字：单元格格式→自定义，在G/通用格式前边输入你要加的字就可以了（用半角引号括起来），就是这样"2021205"G/通用格式。

数字的和自动加1：假设数据在A列，在表外任意位置（假设M1单元格）输入1，建立N列为辅助列并并进行如下操作：点击M1单元格——复制。将需要增加1的单元格刷黑，编辑——选择性粘贴——加——确定。