Excel常用公式函数使用.docVIP

常用函数公式及技巧搜集 【身份证信息提取】 从身份证号码中提取出生年月日 =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)按身份证号分男女年龄段=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)=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))=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)关于取日期=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) =EDATE(A1,12*5) 日期格式转换公式 将 “01/12/2005” 转换成格式 RIGHT(A1,4)MID(A1,4,2)LEFT(A1,2) ＝YEAR($A2)TEXT(MONTH($A2),00)TEXT(DAY($A2),00) 该公式不用设置数据有效性,但要设置储存格格式。 也可以用下列两方法： 1、先转换成文本, 然后再用字符处理函数。 2、[数据]-[分列] [日期]-[MDY]将=text(a1,yyyymm)+0 然后将单元格格式为常规2005-8-6”格式转换为格式 用公式：=TEXT(A1,YYYYMMDD) 反之，日期=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) 另四种公式： ＝text(a1,0000-00-00) 显示：2005-08-06 ＝--TEXT(A1,#-00-00)，把单元