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 ，把单元格设置为日期格式 座机电话号码,0000-00-00 *1，单元格设置日期型VALUE LEFT A1,4 -MID A1,5,2 -RIGHT