excel常用公式习和技巧公式.docVIP

PAGE PAGE 19 常用函数公式及技巧搜集 【身份证信息提取】 从身份证号码中提取出生年月日 =IF(LEN(A2)=15,19MID(A2,7,2)-MID(A2,9,2)-MID(A2,11,2),MID(A2,7,4)-MID(A2,11,2)-MID(A2,13,2)) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),男,女) 从身份证号码中进行年龄判断 以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) 根据出生年月推算生肖 中国人有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） ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 如何统计各年龄段的数量 如何计算20-50岁的人数? =COUNTIF(C3:C17,=20)-COUNTIF(C3:C17,50) 如何统计40-50岁的人的个数 ＝countif(a:a,40)-countif(a:a,50) =COUNTA(A1:A10,D1:D10) 如何让EXCEL自动从头统计到当前单元格 情况如下： C列要根据A列的内容来统计B列的数据，范围从A1：An，即当A列中An有数据时，Cn自动根据An的值，统计B1：Bn的数据。 ｛=SUM(INDIRECT(B1:B LARGE((A1:A65535)*(ROW(A1:A65535)),1)))｝ 统计人数 见表： 性别 年龄 男 6 女 35 男 3 男 55 男 21 男 53.5 女 55 女 56 男 65 女 45 女 53 男 51 如何计算20-50岁的人数? =COUNTIF(C3:C17,=20)-COUNTIF(C3:C17,50) 如何计算男20-50岁的人数? =SUMPRODUCT((B3:B17=男)*(C3:C17=20)*(C3:C17=50)) nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn 【时间和日期应用】 自动显示当前日期公式 =YEAR(NOW()) 当前年 =MONTH(NOW()) 当前月 =DAY((NOW())) 当前日 如何在单元格中自动填入当前日期 Ctrl+; 显示昨天的日期 每天需要单元格内显示昨天的日期，但双休日除外。 例如，今天是7月3号的话，就显示7月2号，如果是7月9号，就显示7月6号。 =IF(TEXT(TODAY(),AAA)=一,TODAY()-3,IF(TEXT(TODAY(),AAA)=日,TODAY()-2,TODAY()-1)) =IF(TEXT(TODAY(),AAA)=一,TODAY()-3,TODAY()-1) 关于取日期 怎么设个公式使A1在年月日向后推5年,变成2011-7-15 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) =EDATE(A1,12*5) 日