第六章 电子表格函数公式使用集锦.doc

电子表格函数公式使用集锦 怎么让excel表格公式只能看不能被改：选定不需要保护的的单元格，单击右键－设置单元格格式—取消锁定—选定需要保护的的单元格，单击右键－设置单元格格式—锁定—工具—保护—保护工作表—输入密码—确定。 电子表格中由身份证号自动导出年月日的公式 1、当身份证号是15位时 =IF(LEN(E1)=15,19MID(E1,7,2)-MID(E1,9,2)-MID(E1,11,2),MID(E1,7,4)-MID(E1,11,2)-MID(E1,13,2))转换出生年月如1986-05-23 说明： E：列数?? E1890：第E列第1890行。输入身份证号码 LEN（text）:返回本字符串的个数。LEN（“123”）=3 LEN(E1)=15表示如果身份证号码为15个数字 :表示相加 MID（字符串，M，N）：从该字符串第M位开始，取N位字符。 MID（A1，3，4）=3456，从“A1”单元格中的第“3”位起截取“4”个数 IF（条件表达式，语句1，语句2）：如果条件成立，那么就执行语句1，否则执行语句2 LEFT（A1,14）截取A1单元格前14位数 RIGHT（A1,14）截取A1单元格后14位数 身份证号码有两种，如“352124860213541”或如果E1是15个,为19加上从第7个开始取2个“86” 加上-加上从第9个开始取2个“02” 加上-加上从第11个开始取2个“13”合起来为“1986-02-13”，否则为从第7个开始取4个“1986” 加上-加上从第11个开始取2个“02” 加上-加上从第13个开始取2个“13” 合起来为“1986-02-13”。 19、-为直接写入的数。 =IF(LEN(A1)=15,19MID(A1,7,4),MID(A1,7,6))转换出生年月取如“198606” =IF(LEN(A1)=15,CONCATENATE(19,MID(A1,7,2),.,MID(A1,9,2)),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,4),.,MID(A1,11,2)),身份证错))转换出生年月取如1986.05 2、当身份证号是18位时 =IF(LEN(B2)=18,MID(B2,7,4)-MID(B2,11,2)-MID(B2,13,2),IF(LEN(B2)=15,19MID(B2,7,2)-MID(B2,9,2)-MID(B2,11,2),身份证号错误)) 以下是的公式TRUNC(YEARFRAC(C75,NOW())) 其中C75是出生日期所在单元格。 （）以下是提取“性别”的公式，将以下公式复制到单元格，将其中所有的“B2”更改为实际存放“身份证号”的单元格地址，然后回车即可。 =IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=0,女,男) 二、成绩在年级里的排名——RANK（）函数的使用 =RANK(N2,$N$2:$N$1501,0) N2为所要排名的单元格，$N$2:$N$1501为从N2列到N1501列，0表示为按照降序排列的列表，不为零为按照升序排列的列表 =RANK(C1,$C$1:$C$10)为10个学生中的第一个的排名 利用函数统计考试成绩 及格率、及格人数算法 在学校，经常要计算及格率、及格人数，而人工的算法有可能失误，用电子表格就比较容易了，它的计算公式如下： 及格人数： =COUNTIF(A1:A50,=60) 及格率：=COUNTIF(A1:A50,=60)/COUNT(A1:A50)*100 说明：A1:A50是取值的范围。 下面是关于COUNTIF的常用公式说明 =COUNTA(A1:A25)算有数值的单元格个数应考人数 =COUNT(B1:B25)和上面的一样用处算出考试人数 =COUNTBLANK(B1:B25)算出缺考人数 =COUNTIF(B1:B25,=90)算90分以上人数=COUNTIF(B1:B25,=80)- COUNTIF(B1:B25,=90)算80到90分人数 =MAX(C1:C25)算最高分 =MIN(C1:C25)算最低分 =AVERAGE(C1:C25)算平均分 =COUNTIF(C1:C25,=90)/COUNT(C1:C25)90分以上占百分比 =MEDIAN(B1:B25)算中位数 =MODE(B1:B25)算众数 =STDEVP(B1:B25)算标准差 四、文本格式转换成数值格式 在原单元格上转换：在任一空白单元格输入1—复制1—选定所有需要改变的单元格—右键—选择性粘贴—选乘，用0加计算也行 在新单元格上转换：选定新单元格，“=所要转换的文本单元格-0” 在新单元格上转换