电子表格函数公式使用集锦[修改版].docVIP

电子表格函数公式使用集锦 怎么让excel表格公式只能看不能被改：选定不需要保护的的单元格，单击右键－设置单元格格式—取消锁定—选定需要保护的的单元格，单击右键－设置单元格格式—锁定—工具—保护—保护工作表—输入密码—确定。 一、电子表格中由身份证号自动导出年月日的公式 =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 =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岁 =IF(LEN(A1)=15,IF(MOD(VALUE(RIGHT(A1,3)),2)=0,女,男),IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,15,3)),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个学生中的第一个的排名 三、利用函数统计考试成绩 =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” 在新单元格上转换：复制—选择性粘贴—数值—点击下拉—转换成数值 五、的应用 =B2C2表示把两个单元格的数值合为一个如“45”、“67”合为“4567” =$F$17C1表示在一个数值前加一个数，在任意单元格输入123设为绝对值，再加所要添加的单元格 六、IF的应用（满足其中一个条件的判断） =IF(A289,优+,IF(A279,优,IF(A269,良+,IF(A259,及格,不及格))