《excel最常用函数公式技巧资料》由会员分享，可在线阅读，更多相关《excel最常用函数公式技巧资料（100页珍藏版）》请在金锄头文库上搜索。
1、excel常用函数公式及技巧搜集目录1. 【身份证信息相关数据提取】第2页2. 【年龄和工龄计算】第4页3. 【时间和日期应用】第6页4. 【排名及排序筛选】第9页5. 【文本与页面设置】第21页6. 【字符截取与增减】第41页7. 【数据拆分与合并】第47页8. 【条件自定义格式】第50页9. 【汇总计算与统计】第60页10. 【数值取整及进位】第75页11. 【大小值或中间值】第80页12. 【查询和查找引用】第84页13. 【输入数据的技巧】第89页1.【身份证信息相关数据提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)
2、+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”格式的话，将”-” 换成”/”即可）=
3、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)=
4、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
5、(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(MI
6、D(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列，年
7、龄段在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)&/&M
8、ID(K1,11,2)&/&MID(K1,13,2),TODAY(),y)=0,16,45,55+0,0,5,5*MOD(MID(K1,17,1),2)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
9、对应生肖是鸡，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)
10、,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)求两日期间
11、的年数=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
12、(),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(
13、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)&个月,)3.【时间和日期应用】自动显示当前日期公式=YEAR(NOW() 当前年=MONTH(NOW() 当前月=DAY(NOW() 当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,aaaa)=MOD(A1,7)2某个日期是星期几比如2007年2月9日，在一单元格内显示星期几。=TEXT(A1,aaa)
