excel中常用函数公式及技巧搜集大全文档格式.docx

excel中常用函数公式及技巧搜集大全文档格式.docx

《excel中常用函数公式及技巧搜集大全文档格式.docx》由会员分享，可在线阅读，更多相关《excel中常用函数公式及技巧搜集大全文档格式.docx（113页珍藏版）》请在冰豆网上搜索。

MID（A2,11,2）&

MID（A2,13,2））

显示格式为yyyy-mm-dd。

（如果要求为“1995/03/29”格式的话，将”-”换成”/”即可）

=IF（D4="

"

IF（LEN（D4）=15,TEXT（（"

MID（D4,7,6））,"

0000年00月00日"

）,IF（LEN（D4）=18,TEXT（MID（D4,7,8）,"

））））

显示格式为yyyy年mm月dd日。

（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd）

=IF（LEN（A1:

A2）=18,MID（A1:

A2,7,8）,"

MID（A1:

A2,7,6））

显示格式为yyyymmdd。

=TEXT（（LEN（A1）=15）*19&

MID（A1,7,6+（LEN（A1）=18）*2）,"

=IF（LEN（A2）=18,MID（A2,7,4）&

-MID（A2,11,2）,19&

-MID（A2,9,2））

=MID（A1,7,4）&

年"

MID（A1,11,2）&

月"

MID（A1,13,2）&

日"

=IF（A1<

TEXT（（LEN（A1）=15）*19&

））

从身份证号码中提取出性别

=IF（MOD（MID（A1,15,3）,2）,"

男"

女"

）（最简单公式）

=IF（MOD（RIGHT（LEFT（A1,17））,2）,"

）

””,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,"

MID（A1,7,2）,MID（A1,7,4））

=YEAR（TODAY（））-VALUE（MID（B1,7,4））&

岁"

=YEAR（TODAY（））-IF（MID（B1,18,1）="

CONCATENATE（"

MID（B1,7,2））,MID（B1,7,4））

按身份证号号码计算至今天年龄

=DATEDIF（TEXT（（LEN（A1）=15）*19&

）,TODAY（）,"

y"

以2006年10月31日为基准日,按按身份证计算年龄（周岁）的公式

=DATEDIF（TEXT（MID（A1,7,6+（LEN（A1）=18）*2）,"

）*1,"

2006-10-31"

按身份证号分男女年龄段

按身份证号分男女年龄段，身份证号在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（）,"

）,{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（）,"

）>

={0,16,45,55}+{0,0,5,5}*MOD（MID（K1,17,1）,2）））

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【年龄和工龄计算】

根据出生年月计算年龄

＝DATEDIF（A1,TODAY（）,"

）&

周岁"

＝DATEDIF（A1,NOW（）,"

根据出生年月推算生肖

中国人有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））,"

YM"

个月"

Md"

天"

其中E2为年龄（可用身份证号码的公式生成）；

V2为法定退休年龄（男60，女50）公式为：

=IF（D2="

IF（D2="

60,50））

D2为男或女（可用身份证号码的公式生成）；

U2为出生年月日（可用身份证号码的公式生成）。

求工齡

=DATEDIF（B2,TODAY（）,"

）

ym"

md"

DATEDIF（B2,TODAY（）,"

计算工龄

=DATEDIF（C6,C8,"

）求两日期间的年数

）求两日期间除去整年数剩余的月数

m"

）求两日期间的总月数

如果只需要算出周年的话，可以用=datedif（"

1978-8"

2006-5"

年龄及工龄计算

有出生年月如何求年龄？

有工作时间如何求工龄？

（求出的结果为多少年另几个月，如：

0303的形式，即3年零3个月）。

a1是出生年月或工作时间：

=datedif（a1,today（）,"

=text（datedif（a1,today（）,"

）,"

00"

text（datedif（a1,today（）,"

如[B2]=1964-9-1则:

=TEXT（DATEDIF（B2,TODAY（）,"

TEXT（MOD（DATEDIF（B2,TODAY（）,"

）,12）,"

） 

'

显示4009

00年"

00月"

显示40年09月

如果你找不到DATEDIF函数，也可以不用DATEDIF函数,

=TEXT（RIGHT（YEAR（NOW（）-B2）,2）,"

TEXT（MOD（MONTH（NOW（）-B2）-1,12）,"

=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日，在一单元格内显示星期几。

aaa"

）（五）

）（星期五）

ddd"

）（Fri）

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））

显示昨