棱锥的侧面积公式Excel学生成绩统计常用公式

2020年9月21日发(作者：倪松茂)

Excel学生成绩、各种报表统计常用公式
1、分数段人数统计
90 -100分：=COUNTIF(C4:C18,
80-89分：=COUNTIF(C4:C18,
70-79分：=COUNTIF(C4:C18,
60-69分：=COUNTIF(C4:C18,
50-59分：=COUNTIF(C4:C18,
41-49分：=COUNTIF(C4 :C18,
40分及以下：=COUNTIF(C4:C18,
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,
差生人数：=COUNTIF(C4:C18 ,
双科合格人数：=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,优良合格待合格
=CHOO SE(IF(A1>=80,1,IF(A1>=70,2,IF(A1>=60,3,4))),优良
合格待合格
1、2、3、4要与优、良、合格、待合格相对应

=IF(C4>=8 0,优良合格
R(C4),待合
=LOOKUP(A1,{0,60,70,80},{
=LOOKUP(A1,{0,60,70,80,90},{不及格及格中良优
=IF(E4<60 ,
=if(A1<60,
=IF(A1<60,不及格及格中良
优
=IF(A1>=90,优良中及格
很差
=IF(AND(C5>=90,C5<= 100),
(AND(C5>=70,C5<80),
多科目等级评定：
=CONC ATENATE(IF(C2>=80,

=IF(C2>=80,

6、18位 身份证号码转换成出生日期的函数公式：如果E2中是身份
证,在F2中求出出生日期，=DATE(M IDB(E2,7,4),MIDB(E2,11,2),MI
DB(E2,13,2)) 格式为yyyy-m-d，可更改为yyyy-mm-dd的格式。
补充说明：
这两个函数指不录入成绩时，单元格为空。
录入0时，评定为待合

（1）=IF(LEN(A1)=15，MID（A1，7，6），MID（A1，7，8）)


提取后格式为670520或19670520。
（2）如果身份证都是18位的，也可能直接用=MID（A1，7，8）
将多个文本字符
（3）提取后的出生日期是连续的，可以中间加“-”或“.”
窜成一个
是15位的，就从
第7位开始，提取
6位
其余的为18位的
身证，就从第9位
开始提取8位
注意检查括号是
否成对出现
判断是几位的身
份证
=CONCAT ENATE（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(e 2)=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)-1
900)
9、
学生
年龄的公式应用：
=(YEAR(B2)-YEAR(A2))+ IF((MONTH(B2)-MONTH(A2))<0,-1,0)
设置截止时间的区域，也可以设为绝对引用，只用一个单元格为

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

出生年月区域


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

10、男女混合的表格中，计算男或女的年龄和（条件求和）
=SUMIF(C5:C19,男

要计算的区域
性别 年龄区域
11、计算男的个数（计算区域内符合条件的个数）
=COUNTIF(C479:C493 ,男
COUNTIFS符合多条件的个数，如，
=COUNTIFS(A2:A5,可计算语文 合格，数学也合
格（双科合格）的人数。和
=SUMPRODUCT((C4:C18>=80 )*(D4:D18>=80))是一样的。 但是在
Excel 2003中没有COUNTIFS函数，在WPS20007、excel2007以上
版本才有。
12、计算符合“男生、4岁”这两个条件的单元格的个数：
=SUMPRODUCT((C479:C493=男 用
SUMPRODUCT函数代替COUNTIFS。符合多个条件的，方法相同。
（1）还可以用：=SUM(IF((C88:C102=男

注意: 1、输 入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组
公式符号。2、也可以用于双 科合格率的人数统计，还可以是符
合多个条件的单元格人数统计。3、文本的参数加,数字的参数不加< br>。4、年龄不能用文本格式，用常规，其它的符号如“√”用文本
格式。
（2）还可以 使用COUNT函数和IF函数嵌套计算：=COUNT(IF(((B
1:B3>80)*(C1:C 3>80)*(D1:D3>80)*(E1:E3>80)*(F1:F3>80)),A1:A3))， < br>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)) < br>=COUNTIF(V4:V75,
=SUMPRODUCT((V4:V75<30)*(V4 :V75>0))
=SUM((V4:V75>0)*(V4:V75<30))
13、直接计算女生平均年龄的三种方法：
=SUMIF(C2:C21,女女
=A VERAGEIF(C2:C21,女
=AVERAGE(IF(C2:C21=女

性别区域
年龄区域
14、计算20到30岁年龄段的女生人数（用12的两个公式也行）
=COUNTIFS(C2:C21,女
15、COUNT和COUNTA公式的区别（都是求录入内容的单元格的
个数）
区域的条件是数区域的条件是文字
16、将两个单元格A1、B1的内容合并到一个单元格C1：
C1输入=A1&B1

将一个单元格A1的内容如“福建古田杉洋”，分解到两个单元格
B1、C1： B1输入=LEFT（A1，2），C1输入=RIGHT（A1，4）

左2个字符
右4个字符

17、平方、立方的计算：=5^2, =5^3
也可以用函数=POWER（4，2）计算4的平方。
18、时间显示牌：如距放假还有 =DATE(2013,6,29)-TODAY()&天

19、计算教龄：=DATEDIF(A1，B1，”Y”)
工作时间=

参加工作时间
今年的年月日
或用TODAY（）
或设定截止时
间。
代表年，改为“M”则算月，
改为“D”则算天
20、计算年龄=YEAR($$A$$1 )-YEAR（B1） 不计月份
[年龄的单元格要设置为常规]


TODAY（）差生的
日期放置的单元
出生的日期单元
格
21、生日提醒函数：
=IF(AND(MONTH(TODAY())=MONTH(E3 ),DAY(TODAY())=DAY(E
3)),生日非生日
=IF($$A$$1=DATE (YEAR($$A$$1),MONTH(B1),DAY(B1)), 生日， )


TODAY（）差生的
日期放置的单元
出生的日期单元
格
生日显示，非生日
不显示
22、电子表格的列中自动添加相同数字：单元格格式→自定义，在

G通用格式 前边输入你要加的字就可以了（用半角引号括起来），
就是这样通用格式。
数字的和自动加1 ：假设数据在A列，在表外任意位置（假设M1单
元格）输入1，建立N列为辅助列并并进行如下操作： 点击M1单元
格——复制。将需要增加1的单元格刷黑，编辑——选择性粘贴——
加——确定。
Love is not a maybe thing. You know when you love
someone.