excel最常用函数公式技巧

第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页第2页1.【身份证信息相关数据提取】从身份证号码中提取出生年月日=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)第3页按身份证号分男女年龄段按身份证号分男女年龄段，身份证号在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)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜第4页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年零第5页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)&个月,)㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜第6页3.【时间和日期应用】自动显示当前日期公式=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))需加載分析工具箱。当前月天数：=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))第7页=IF(TEXT(TODAY(),AAA)=一,TODAY()-3,TODAY()-1)关于取日期怎么