2014电子表格函数公式使用集锦.docVIP

电子表格函数公式使用集锦 怎么让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),身份证号错误)) （2）以下是通过身份证号计算年龄的公式， 第一种： 将公式复制到年龄所在列，将D4单元格换成身份证号所在的单元格地址。 =DATEDIF(IF(LEN(D4)=18,MID(D4,7,4)-MID(D4,11,2)-MID(D4,13,2),IF(LEN(D4)=15,19MID(D4,7,2)-MID(D4,9,2)-MID(D4,11,2),身份证号错误)),TODAY(),Y) 第二种： =2010-MID(B1,1,4)-IF((MID(B1,5,2)-0)8,1,0)计算年龄 第三种： =IF(LEN(A1)=15,YEAR(NOW())-1900-VALUE(MID(A1,7,2)),IF(LEN(A1)=18,YEAR(NOW())-VALUE(MID(A1,7,4)),身份证错))计算年龄，月数全部不算如24岁2个月和24岁11个月都是24岁 第四种 如果已经知道日期格式如下：2011-2-5 那么可以用下面公式： YEAR（NOW（））-YEAR（D2）-IF（MOUTH（D2）8,1,0） 其中D2指出生日期所在的单元格。公式能够区分8 月前和8月后的。如果不要 也可以：TRUNC(YEARFRAC(C75,NOW())) 其中C75是出生日期所在单元格。 （3）以下是提取“性别”的公式，将以下公式复制到单元格，将其中所有的“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) 及格率