# EXCEL常用函数公式及技巧搜集之一

2017-01-29

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,)

=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))

=IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"00000000"),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"00000000"))))

=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6))

=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”),)

=DATEDIFA1TODAY（），“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")

20061031为基准日,按按身份证计算年龄(周岁)的公式

=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")

=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")

=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")&""))

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")求两日期间的总月数

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")  '显示4009

[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)&"个月","")       '显示 4009个月

以前用这样一段（   =TEXT(RIGHT(YEAR(NOW()-A1),2)&""&MOD(MONTH(NOW()-A1)-1,12)&"个月","")     ）。

=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

=TEXT(A1,"aaa")     （五）

=TEXT(A1,"aaaa")    （星期五）

=TEXT(A1,"ddd")     Fri

=TEXT(A1,"dddd")    Friday

=TEXT(NOW(),"aaaa  hh:mm:ss")

A12006-8-4  求本月天数

A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))

=IF(TEXT(TODAY(),"AAA")="",TODAY()-3,IF(TEXT(TODAY(),"AAA")="",TODAY()-2,TODAY()-1))

=IF(TEXT(TODAY(),"AAA")="",TODAY()-3,TODAY()-1)

=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]

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

text(a1,"0000-00-00")                                    显示：2005-08-06

--TEXT(A1,"#-00-00")，把单元格设置为日期格式             显示：2005-8-6

TEXT(20050806,"0000-00-00")*1，单元格设置日期型          显示：2005-8-6

VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))   显示：2005-8-6

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

198405转换成1984.05

1、采取辅助＝mid(xxxxxx,1,4) & "." & right(xxxxxx,2)

2、选中这列，用数据中的分列。然后……………

=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))

2005-8-6转换为200586格式

=TEXT(A1,"yyyy""""m""""d"""";@")

22怎样转换成22日？转成当年当月的日子

公式为：=TEXT(A8,"yyyy""""mm"""";@")

=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1)    显示：1968/6/12

=TEXT(A1,"yyyy/mm/dd")                       显示：1968/06/12

=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)    显示：1968-6-12

=TEXT(A1,"yyyy-mm-dd")                       显示：1968-06-12

1993-12-28的日期格式转换成199312

=CONCATENATE(YEAR(A1),"",MONTH(A1),"")

=YEAR(A1)&""&MONTH(A1)&""

year(A1)&text(month(A1),"00")

1、可以用数组公式将中文日期转化为日期系列数=14610+MATCH(SUBSTITUTE(A3,"",""),TEXT(ROW(\$14611:\$55153),"[DBNum1]yyyymd"),0)

2、改进后的公式，速度要快的多：

=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW(\$1900:\$2100),"[DBNum1]0000"),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),"",""),TEXT(ROW(\$1:\$366),"[DBNum1]md"),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),"",""),TEXT(ROW(\$1:\$366),"[DBNum1]md"),0)))

A列是月份数为8B列是日期数为18，如何在C列显示“818

=A1&""&B1&""

=month(--c5)

=day(--c5)

=TEXT(A1,"YYMMDD")&"001"

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

【排名及排序筛选】

=SUBTOTAL(9,\$B\$2:B2)

=SUBTOTAL(3,\$B\$2:B2)*1

=IF(A2<>A1,1,N(C1)+1)

=IF(MOD(A1,2),0,1)

=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)

=ROW()*2-1-(ROW()>50)*99

=IF(B2<>"",A2+1,"")

=RANK(A1,\$A\$1:\$A\$5)

=RANK(A1,A:A)

A列自动变成从小到大排列

B=SMALL(A\$2:A\$28,ROW(1:1))

A列自动变成从大到小排列

B=LARGE(A\$2:A\$28,ROW(1:1))

AB列分别为“歌手”和“歌名”，在C1输入“字数”，在C2输入公式：

LEN(B2)   下拖，单击C2，单击工具栏上的“升序排列”即可，删除C列。

 A 1 A122 2 A29 3 A317 4 A43 5 A7 6 B20 7 B3 8 C144 9 C5 10 C33

 A B 1 A7 A007 2 A29 A029 3 A43 A043 4 A122 A122 5 A317 A317 6 B3 B003 7 B20 B020 8 C5 C005 9 C33 C033 10 C144 C144

B1中输入公式：LEFT(A1,1)& RIGHT("000"& RIGHT(A1,LEN(A1)-1),3) 下拖

AB列分别为“歌手”和“歌名”，在C1输入“次序”，在C2输入公式：

RAND（），下拖，单击C2，单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。

2004-2006

2007-2009

2010-2012;

A0001

B0001

A0002

C0001

A0003

B0002

C0002

=TEXT(COUNTIF(A\$1:A1,A1),"!"&A1&"0000")否则数字超过9就错误了。

赵一               总经理

赵二               副经理

赵三               副经理

赵四               技术员

赵五

赵六               员工

赵一               总经理

赵二               副经理

赵六               员工

赵三                副经理

赵四               技术员

赵五

A1单元格输入公式 =row(),往下拉,然后再插入。

=SUBTOTAL(3,\$B\$2:\$B2)

=(A1<>A2)+N(B1)

=IF(A3=A2,B2,B2+1)

B2=SUMPRODUCT(1/COUNTIF(A\$2:A2,A\$2:A2))

B2=IF(COUNTIF(A\$2:A2,A2)>1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A\$2:A2,A\$2:A2)))

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))

A列是成绩,B列是排名

=SUMPRODUCT((A\$1:A\$9>A1)/COUNTIF(A\$1:A\$9,A\$1:A\$9))+1

=RANK(C3,\$C\$3:\$C\$12)

=RANK(A2,\$A\$2:\$A\$11,0)

=RANK(C2,\$C\$2:\$C\$65)+COUNTIF(\$C\$2:C2,C2)-1

=PERCENTRANK(\$C\$3:\$C\$12,C3)

=AVERAGE(B2:E2)

=RANK(F2,\$F\$2:\$F\$65536)

=SUMPRODUCT((BJ=A2)*(ZF>E2))+1

=RANK(E2,ZF)   公式下拖。

=INDEX(A\$2:A\$6,RANK(D2,D\$2:D\$6))

=INDEX(A\$2:A\$6,RANK(D2,D\$2:D\$6,1))

=RANK(A2,\$A\$2:\$A\$12)

=RANK(A2,A\$2:A\$12)+COUNTIF(A\$2:A2,A2)-1

=SUMPRODUCT(1*(\$E\$3:\$E\$12>=E3))

=RANK(K3,\$K\$3:\$K\$26)

=RANK(A2,A\$2:A\$12)

=SUM((A\$2:A\$12>=A2)/COUNTIF(A\$2:A\$12,A\$2:A\$12))

=COUNTIF(\$K\$3:\$K\$26,">"&K3)+1

=INDEX(\$A\$2:\$A\$7,MATCH(LARGE(\$C\$2:\$C\$7,ROW(A1)),\$C\$2:\$C\$7,0),1)

=SUMPRODUCT((\$A\$2:\$A\$12>A2)/COUNTIF(\$A\$2:\$A\$12,\$A\$2:\$A\$12&""))+1

=RANK(D2,OFFSET(\$A\$1,MATCH(\$A2,\$A:\$A,0)-1,3,COUNTIF(\$A:\$A,\$A2),1))

=RANK(K32,\$K\$32:\$K\$55)+COUNTIF(\$K\$32:\$K32,K32)-1

=COUNTIF(\$K\$32:K32,K32)-1+COUNTIF(\$K\$3:\$K\$26,">"&K32)+1

=SUMPRODUCT(1*((\$E\$3:\$E\$12+ROW(\$E\$3:\$E\$12)/100>=(\$E3+ROW(E3)/100))))

=RANK(E3,\$E\$3:\$E\$12)+COUNTIF(\$E\$3:E3,E3)-1

=SUMPRODUCT(1*((\$E\$3:\$E\$12+\$B\$3:\$B\$12/100)>=(E3+B3/100)))

=RANK(\$E3,\$E\$3:\$E\$22) 內建方式排名

=SUMPRODUCT(1*(\$E\$3:\$E\$12>=E3))   一般方式排名

=RANK(E3,\$E\$3:\$E\$22)+SUM(IF(\$E\$3:\$E\$22>E3,1/COUNTIF(\$E\$3:\$E\$22,\$E\$3:\$E\$22),0))-COUNTIF(\$E\$3:\$E\$22,">"&E3)  一般方式排名

=RANK(E3,\$E\$3:\$E\$12)+COUNTIF(\$E\$3:E3,E3)-1不重复排名

=SUMPRODUCT(1*((\$E\$3:\$E\$12+ROW(\$E\$3:\$E\$12)/100>=(\$E3+ROW(E3)/100))))

=SUMPRODUCT(1*((\$E\$3:\$E\$12+\$B\$3:\$B\$12/100)>=(E3+B3/100))) 不重复排名

=SUMPRODUCT(1*((\$E\$3:\$E\$12+\$B\$3:\$B\$12/100+\$C\$3:\$C\$12/10000)>=(E3+B3/100+C3/10000))) 不重复排名

=RANK(\$E3,\$E\$3:\$E\$22,1) 倒排序

=RANK(K247,\$K\$247:\$K\$270)

=RANK(B1,\$B1:\$H1)

=RANK(B2,\$B\$2:\$B\$21,0)

=RANK(B1,\$B1:\$H1)+COUNTIF(\$B\$1:B1,B1)-1

=SUM(IF(\$A\$1:\$E\$1>=A1,1/COUNTIF(\$A\$1:\$E\$1,\$A\$1:\$E\$1),""))

=SUMPRODUCT((\$B\$2:\$B\$21>=B2)/COUNTIF(\$B\$2:B\$21,B\$2:B\$21))

=SUMPRODUCT((B\$3:B\$21>B3)*(1/COUNTIF(\$B\$3:\$B\$21,\$B\$3:\$B\$21)))+1  （升序）

=SUMPRODUCT((B\$3:B\$21 （降序）

=SUM(--(IF(FREQUENCY(B\$2:B\$21,B\$2:B\$21),B\$2:B\$21>B2)))+1

=SUM(IF(\$B\$3:\$B\$21<=B3,"",1/(COUNTIF(\$B\$3:B\$21,B\$3:B\$21))))+1（升序）

=SUM(IF(\$B\$3:\$B\$21<=B3,1/(COUNTIF(\$B\$3:B\$21,B\$3:B\$21)),"")) （降序）

=SUM(IF(\$B\$2:\$B\$21>B2,1/COUNTIF(\$B\$2:B\$21,B\$2:B\$21)))+1

=SUM(IF(\$A\$1:\$E\$1>=A1,1/COUNTIF(\$A\$1:\$E\$1,\$A\$1:\$E\$1),""))

=SUM((\$B\$2:\$B\$21>B2)*(MATCH(\$B\$2:B\$21,B\$2:B\$21,)=ROW(\$1:\$20)))+1

=SUM(IF(\$B\$1:\$H\$1<=B1,"",1/(COUNTIF(\$B\$1:\$H\$1,\$B\$1:\$H\$1))))+1

0条评论