利用Excel电子表格制作学生成绩登记册中常用函数、公式应用介绍.doc

利用Excel电子表格制作学生成绩登记册中常用函数、公式应用介绍1、EXCEL表格如何根据另一列中的数字自动生成序号？=COUNTIF($B$1:B1,B1)2、excel中如何排名次？=RANK(C4,$C$4:$C$26)3、excel中如何排序?点击“数据”菜单“排序”在“主要关键字”下拉框内选定“某一选项”，并选定“递增”或“递减”单选钮确定。4、怎么用Excel统计成绩的总分？=sum(B1:G1)5、怎样用EXCEL计算学生成绩的平均分？=ROUND(AVERAGE(B1:B20),2)6、怎样用EXCEL计算学生成绩的及格率？=ROUND(COUNTIF(B:B,=60)/COUNT(B:B)*100,2)&%7、怎样用EXCEL计算学生成绩的优秀率、？=ROUND(COUNTIF(B:B,=80)/COUNT(B:B)*100,2)&%8、怎么计算最高分和最低分？ 最高分:=MAX(A:A)最低分:=MIN(B:B)9、怎样测算应考人数和实考人数? 应考人数：=COUNTA($B:$B)(姓名绝对区域) (数值、文本、文字都算个数)实考人数： =COUNT(B:B) (数值才算个数)10：怎样测算分数段的个数？0分：=COUNTIF(B:B，” =0”)029分：=SUMPRODUCT(B2:B400)*(B2:B40=30)*(B2:B40=60)*(B2:B40=80)*(B2:B40=100”)11、excel中怎样计算学生成绩前30%的平均成绩？例成绩放A1:A14 先建一列(如B列)放名次,用公式 =rank(该生成绩,所有成绩的绝对地地址) 例 在B1中写 =RANK(A1,$A$1:$A$14),然后填充柄填充该列 算学生的30%人数,总人数可count算,也可直接写 例 在A16中写 =ROUND(COUNT(C2:C38)*0.3,0)建一列(如C列)放前30%学生的成绩,用=if(该生名次=总人数的30%,该生成绩) 例 在C1中写 =IF(B1=$A$16,A1),然后填充柄填充该列,如中间有空行稍注意一下 以上三步可以合并起来直接写=IF(RANK(A1, $A$1: $A$14)= ROUND(COUNT(C2:C38)*0.3,0),A1),然后填充最后= ROUND( average(C1:C14),2)即可,如觉得C列不美观可隐藏该列(格式-列-隐藏) 12、在Excel中,有什么办法让函数公式自动生存的零,一下让变为空单元格,打印出来不显示0,只显示空格?1、工具选项视图窗口选项零值 不勾选2、选择单元格格式为“数字”13、根据出生年月计算年龄DATEDIF(A1,TODAY(),y)DATEDIF(A1,TODAY(),y)&周岁DATEDIF(A1,NOW(),y)14、日期格式转换公式将“2005年9月”转换成“200509”格式先用公式：=text(a1,yyyymm)+0 然后将单元格格式为常规。将“2005-8-6”格式转换为“20050806”格式用公式：=TEXT(A1,YYYYMMDD)反之，将20050806转为日期2005-8-6格式，可用公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)另四种公式：text(a1,0000-00-00) 显示：2005-08-06-TEXT(A1,#-00-00)，把单元格设置为日期格式 显示：2005-8-6TEXT(20050806,0000-00-00)*1，单元格设置日期型 显示：2005-8-6VALUE(LEFT(A1,4)&-&MID(A1,5,2)&-&RIGHT(A1,2) 显示：2005-8-6将“20060501”转换为“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)将“199306”转换为“1993-6”公式1：=LEFT(A3,4)&-&RIGHT(A3,2)*1公式2：=-TEXT(A3*100+1,#-00-00) 公式2需要设置单元格格式，自定义：e-m公式3：=TEXT(TEXT(A3&01,0000-00-00),e-m)把198405转换成1984.05 将文本“2004.01.02” 转换为日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)将2005-8-6转换为2005年8月6日格式=TEXT(A1,yyyy年m月d日;)象22怎样转换成22日？转成当年当月的日子公式为：=date(year(now(),month(now(),22)将“2006年5月”转换成“2006年05月” 公式为：=TEXT(A8,yyyy年mm月;)也可以这样处理：选中单元格，设置单元格公式数字自定义，将yyyy“年”m“月”改为：yyyy“年”mm“月”，即可。但这方法打印出来显示为：2006/5/将“1968年6月12日”转换为“1968-6-12”格式=YEAR(A1)&-&MONTH(A1)&-&DAY(A1) 显示：1968-6-12=TEXT(A1,yyyy-mm-dd)显示：1968-06-12将1993-12-28的日期格式转换成1993年12月=CONCATENATE(YEAR(A1),年,MONTH(A1),月) =YEAR(A1)&年&MONTH(A1)&月也可以自定义格式 $-404e年m月将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式year(A1)&text(month(A1),00)要将“99.08.15” 格式转换成“1999.08.15”如何做选中列，数据菜单中选分列，分列过程中“格式”选“日期YMD”，结束。将“二三年十二月二十五日”转为“2003-12-25”格式，1、可以用数组公式将中文日期转化为日期系列数=14610+MATCH(SUBSTITUTE(A3,元,一),TEXT(ROW($14611:$55153),DBNum1yyyy年m月d日),0)该公式速度较慢。2、改进后的公式，速度要快的多：=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DBNum10000),0),MONTH