excel常用函数公式及技巧搜集

整理文档很辛苦,赏杯茶钱您下走!

免费阅读已结束,点击下载阅读编辑剩下 ...

阅读已结束,您可以下载文档离线阅读编辑

资源描述

1excel常用函数公式及技巧搜集【身份证信息?提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1=IF(A2,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)显示格式均为yyyy-m-d。(最简单的公式,把单元格设置为日期格式)=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&-&MID(A2,11,2)&-&MID(A2,13,2))显示格式为yyyy-mm-dd。(如果要求为“1995/03/29”格式的话,将”-”换成”/”即可)=IF(D4=,,IF(LEN(D4)=15,TEXT((19&MID(D4,7,6)),0000年00月00日),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000年00月00日))))显示格式为yyyy年mm月dd日。(如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd)=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6))显示格式为yyyymmdd。=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=MID(A1,7,4)&年&MID(A1,11,2)&月&MID(A1,13,2)&日=IF(A1,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00))从身份证号码中提取出性别=IF(MOD(MID(A1,15,3),2),男,女)(最简单公式)=IF(MOD(RIGHT(LEFT(A1,17)),2),男,女)=IF(A2””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,男,女)从身份证号码中进行年龄判断=IF(A3””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)=DATEDIF(A1,TODAY(),“Y”)(以上公式会判断是否已过生日而自动增减一岁)=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-VALUE(MID(B1,7,4))&岁=YEAR(TODAY())-IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2)),MID(B1,7,4))按身份证号号码计算至今天年龄=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00),TODAY(),y)以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1,2006-10-31,y)按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在K列,年龄段在J列(身份证号为18位)男性16周岁以下为1男性16周岁(含16周岁)以上至50周岁为2男性50周岁(含50周岁)以上至60周岁为3男性60周岁(含60周岁)以上为4女性16周岁以下为12女性16周岁(含16周岁)以上至45周岁为2女性45周岁(含45周岁)以上至55周岁为3女性55周岁(含55周岁)以上为4=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),y),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&/&MID(K1,11,2)&/&MID(K1,13,2),TODAY(),y)={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【年龄和工龄计算】根据出生年月计算年龄=DATEDIF(A1,TODAY(),y)=DATEDIF(A1,TODAY(),y)&周岁=DATEDIF(A1,NOW(),y)根据出生年月推算生肖中国人有12生肖,属什么可以推算出来。即用诞生年份除以12,再用除不尽的余数对照如下:0→猴,1→鸡,2→狗,3→猪,4→鼠,5→牛,6→虎,7→兔,8→龙,9→蛇,10→马,11→羊例如:XXX出生于1921年,即用1921年除以12,商得数为160,余数为1,对照上面得知余数1对应生肖是鸡,XXX就属鸡。=MID(猴鸡狗猪鼠牛虎兔龙蛇马羊,MOD(YEAR(A2),12)+1,1)(2007)如何求出一个人到某指定日期的周岁?=DATEDIF(起始日期,结束日期,Y)计算距离退休年龄的公式=IF(E2=,,IF(E2=V2,已经退休,距离退休还有&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),Y)&年&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),YM)&个月&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),Md)&天))其中E2为年龄(可用身份证号码的公式生成);V2为法定退休年龄(男60,女50)公式为:=IF(D2=,,IF(D2=男,60,50))D2为男或女(可用身份证号码的公式生成);U2为出生年月日(可用身份证号码的公式生成)。求工齡=DATEDIF(B2,TODAY(),y)=DATEDIF(B2,TODAY(),ym)=DATEDIF(B2,TODAY(),md)=DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2,TODAY(),md)&日计算工龄=DATEDIF(C6,C8,y)求两日期间的年数=DATEDIF(C6,C8,ym)求两日期间除去整年数剩余的月数=DATEDIF(C6,C8,m)求两日期间的总月数如果只需要算出周年的话,可以用=datedif(1978-8,2006-5,Y)年龄及工龄计算有出生年月如何求年龄?有工作时间如何求工龄?(求出的结果为多少年另几个月,如:0303的形式,即3年零3个月)。a1是出生年月或工作时间:=datedif(a1,today(),y)3=text(datedif(a1,today(),y),00)&text(datedif(a1,today(),m),00)如[B2]=1964-9-1则:=TEXT(DATEDIF(B2,TODAY(),y),00)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00)'显示4009=TEXT(DATEDIF(B2,TODAY(),y),00年)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00月)'显示40年09月如果你找不到DATEDIF函数,也可以不用DATEDIF函数,如[B2]=1964-9-1则:=TEXT(RIGHT(YEAR(NOW()-B2),2),00)&TEXT(MOD(MONTH(NOW()-B2)-1,12),00)'显示4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&年&MOD(MONTH(NOW()-B2)-1,12)&个月,)'显示40年09个月自动算出工龄日期格式为(yyyy.mm.dd)能否用:(yyyy.mm.dd)这种格式来计算出工龄有多长呢~?以前用这样一段(=TEXT(RIGHT(YEAR(NOW()-A1),2)&年&MOD(MONTH(NOW()-A1)-1,12)&个月,))。但这种方法只能用:(yyyy-mm-dd)这样的日期格式才能实现!你不妨把“.”替换成“-”,不就行了吗,再说后者是日期的一种标准格式,=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,.,-)),2)&年&MOD(MONTH(NOW()-SUBSTITUTE(A1,.,-))-1,12)&个月,)㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【时间和日期应用】自动显示当前日期公式=YEAR(NOW())当前年=MONTH(NOW())当前月=DAY((NOW()))当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,aaaa)=MOD(A1,7)2某个日期是星期几比如2007年2月9日,在一单元格内显示星期几。=TEXT(A1,aaa)(五)=TEXT(A1,aaaa)(星期五)=TEXT(A1,ddd)(Fri)=TEXT(A1,dddd)(Friday)什么函数可以显示当前星期如:星期二10:41:56=TEXT(NOW(),aaaahh:mm:ss)求本月天数设A1为2006-8-4求本月天数A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))也有更簡便的公式:=DAY(EOMONTH(NOW(),0))需加載分析工具箱。当前月天数:4=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)用公式算出除去当月星期六、星期日以外的天数=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&:&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)1))显示昨天的日期每天需要单元格内显示昨天的日期,但双休日除外。例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7月6号。=IF(TEXT(TODAY(),AAA)=一,TODAY()-3,IF(TEXT(TODAY(),AAA)=日,TODAY()-2,TODAY()-1))=IF(TEXT(TODAY(),AAA)=一,TODAY()-3,TODAY()-1)关于取日期怎么设个公式使A1在年月日向后推5年,变成2011-7-15=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))=EDATE(A1,12*5)如何对日期进行上、中、下旬区分=LOOKUP(DAY(A1),{0,11,21,31},{上旬,中旬,下旬,下旬})如何获取一个月的最大天数=DAY(DATE(2002,3,1)-1)或=DAY(B1-1),B1为2001-03-01日期格式转换公式将“01/12/2005”转换成“20050112”格式=RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)=YEAR($A2)&TEXT(MONTH($A2),00)&TEXT(DAY($A2),00)该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法:1、先转换成文本,然后再用字符处理函数。2、[数据]-[分列][日期]-[MDY]

1 / 68
下载文档,编辑使用

©2015-2020 m.777doc.com 三七文档.

备案号:鲁ICP备2024069028号-1 客服联系 QQ:2149211541

×
保存成功