EXCEL函数公式大全.docx
《EXCEL函数公式大全.docx》由会员分享，可在线阅读，更多相关《EXCEL函数公式大全.docx（113页珍藏版）》请在冰豆网上搜索。
EXCEL函数公式大全
excel常用函数公式及技巧搜集（常用的）
【身份证信息？
提取】
从身份证号码中提取出生年月日
=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周岁以下为 1
女性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"）
=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（）,"aaaa hh:
mm:
ss"）
求本月天数
设A1为2006-8-4 求本月天数
A1=DAY（DATE（YEAR（A1）,MONTH（A1）+1,0））
也有更簡便的公式：
=DAY（EOMONTH（NOW（）,0））需加載分析工具箱。
当前月天数：
=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））
显示昨天的日
升级会员 