excel常用函数公式及技巧搜集.pdf

《excel常用函数公式及技巧搜集.pdf》由会员分享，可在线阅读，更多相关《excel常用函数公式及技巧搜集.pdf（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、excelexcel 常用函数公式及技巧搜集常用函数公式及技巧搜集【身份证信息？提取】【身份证信息？提取】从身份证号码中提取出生年月日从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1=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)=

2、18)*2),#-00-00)+0,)=IF(A2,TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)显示格式均为显示格式均为 yyyy-m-dyyyy-m-d。（最简单的公式，把单元格设置为日期格式）。（最简单的公式，把单元格设置为日期格式）=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&-=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&-

3、&MID(A2,11,2)&-&MID(A2,13,2)&MID(A2,11,2)&-&MID(A2,13,2)显示格式为显示格式为 yyyy-mm-ddyyyy-mm-dd。（如果要求为“。（如果要求为“1995/03/291995/03/29”格式的话，将”格式的话，将”-”换成”换成”/”即可）”即可）=IF(D4=,IF(LEN(D4)=15,TEXT(19&MID(D4,7,6),0000=IF(D4=,IF(LEN(D4)=15,TEXT(19&MID(D4,7,6),0000 年年 0000 月月 0000 日日),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0

4、000),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000 年年 0000 月月 0000 日日)显示格式为显示格式为 yyyyyyyy 年年 mmmm 月月 dddd 日。（如果将公式中“日。（如果将公式中“00000000 年年 0000 月月 0000 日”改成“日”改成“0000-00-0000-00-00”00”,则显示格式则显示格式为为 yyyy-mm-ddyyyy-mm-dd）=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6)=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1

5、:A2,7,6)显示格式为显示格式为 yyyymmddyyyymmdd。=TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=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)=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)

6、&=MID(A1,7,4)&年年&MID(A1,11,2)&MID(A1,11,2)&月月&MID(A1,13,2)&MID(A1,13,2)&日日=IF(A1,TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)=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(MID(A1,15,3),2),男男,女女)（最简单公式）=IF(MOD(RIGHT(LEF

7、T(A1,17),2),=IF(MOD(RIGHT(LEFT(A1,17),2),男男,女女)=IF(A2=IF(A2”,IF(MOD(RIGHT(LEFT(A2,17),2),IF(MOD(RIGHT(LEFT(A2,17),2),”男”男”,”女”女”),),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)=1,=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)=1,男男,女女)从身份证号码中进行年龄判断从身份证号码中进行年龄判断=IF(A3=IF(A3”,DATEDIF(TEXT(LEN(A3)=15*19&MID(A3,7,6+(LEN(

8、A3)=18*2),DATEDIF(TEXT(LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-#-00-0000”),TODAY(),),TODAY(),”Y Y”),),)=DATEDIF=DATEDIF（A1A1，TODAYTODAY（），“Y”）（），“Y”）（以上公式会判断是否已过生日而自动增减一岁）（以上公式会判断是否已过生日而自动增减一岁）=YEAR(NOW()-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(NOW()-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY

9、()-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4)=YEAR(TODAY()-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4)=YEAR(TODAY()-VALUE(MID(B1,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)=YEAR(TODAY()-IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2),MID(B1,7,4

10、)按身份证号号码计算至今天年龄按身份证号号码计算至今天年龄=DATEDIF(TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00),TODAY(),y)=DATEDIF(TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00),TODAY(),y)以以 20062006 年年 1010 月月 3131 日为基准日日为基准日,按按身份证计算年龄按按身份证计算年龄(周岁周岁)的公式的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1,

11、2006-10-31,y)=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 周

12、岁为 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-=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)0,0,5,5*ISEVEN(MID(K1,17,1)=SUM(-=SUM(-(DATEDIF(MID(K1,7,4)&/&MID(K1,11,2)&/&MID(K1,13,

13、2),TODAY(),y)=0,16,45,55+0,0,5,5*M(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)OD(MID(K1,17,1),2)【年龄和工龄计算】【年龄和工龄计算】根据出生年月计算年龄根据出生年月计算年龄DATEDIF(A1,TODAY(),y)DATEDIF(A1,TODAY(),y)DATEDIF(A1,TODAY(),y)&DATEDIF(A1,TODAY(),y)&周岁周岁 DATEDIF(A1,NOW(),y)

14、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(=MID(猴鸡狗猪鼠牛虎兔龙蛇马羊猴鸡狗猪鼠牛虎兔龙蛇马羊,MOD(YEAR(A2),12)+1,1),MOD(YEAR(A2),12)+1,1)（20072007）如何求出一个人到某指定日期的周岁？

15、如何求出一个人到某指定日期的周岁？=DATEDIF(=DATEDIF(起始日期起始日期,结束日期结束日期,Y),Y)计算距离退休年龄的公式计算距离退休年龄的公式=IF(E2=,IF(E2=V2,=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),Y)&年年&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(

16、U2),YM)&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),YM)&个月个月&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Md)&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Md)&天天)其中 E2E2 为年龄为年龄（可用身份证号码的公式生成）；V2V2 为法定退休年龄为法定退休年龄（男 60，女 50）公式为：=IF(D2=,IF(D2=男,60,50)D2D2 为男或女为男或女（可用身份证号码的公式生成）；

17、U2U2 为出生年月日为出生年月日（可用身份证号码的公式生成）。求工齡求工齡=DATEDIF(B2,TODAY(),y)=DATEDIF(B2,TODAY(),y)=DATEDIF(B2,TODAY(),ym)=DATEDIF(B2,TODAY(),ym)=DATEDIF(B2,TODAY(),md)=DATEDIF(B2,TODAY(),md)=DATEDIF(B2,TODAY(),y)&=DATEDIF(B2,TODAY(),y)&年年&DATEDIF(B2,TODAY(),ym)&DATEDIF(B2,TODAY(),ym)&月月&DATEDIF(B2,TODAY(),md)&DATED

18、IF(B2,TODAY(),md)&日日 计算工龄计算工龄=DATEDIF(C6,C8,y)=DATEDIF(C6,C8,y)求两日期间的年数=DATEDIF(C6,C8,ym)=DATEDIF(C6,C8,ym)求两日期间除去整年数剩余的月数=DATEDIF(C6,C8,m)=DATEDIF(C6,C8,m)求两日期间的总月数如果只需要算出周年的话，可以用=datedif(1978-8,2006-5,Y)=datedif(1978-8,2006-5,Y)年龄及工龄计算年龄及工龄计算有出生年月如何求年龄？有工作时间如何求工龄？（求出的结果为多少年另几个月，如：0303 的形式，即 3 年零 3

19、 个月）。a1 是出生年月或工作时间：=datedif(a1,today(),y)=datedif(a1,today(),y)=text(datedif(a1,today(),y),00)&text(datedif(a1,today(),m),00)=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)=TEXT(DATEDIF(B2,TODAY(),y)

20、,00)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00)显示4009=TEXT(DATEDIF(B2,TODAY(),y),00=TEXT(DATEDIF(B2,TODAY(),y),00 年年)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00 月月)显示显示4040 年年 0909 月月如果你找不到 DATEDIF 函数，也可以不用 DATEDIF 函数,如 B2=1964-9-1 则:=TEXT(RIGHT(YEAR(NOW()-B2),2),00)&TE

21、XT(MOD(MONTH(NOW()-B2)-1,12),00)=TEXT(RIGHT(YEAR(NOW()-B2),2),00)&TEXT(MOD(MONTH(NOW()-B2)-1,12),00)显示 4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&=TEXT(RIGHT(YEAR(NOW()-B2),2)&年年&MOD(MONTH(NOW()-B2)-1,12)&MOD(MONTH(NOW()-B2)-1,12)&个月个月,),)显示 40 年09 个月自动算出工龄日期格式为自动算出工龄日期格式为(yyyy.mm.dd)(yyyy.mm.dd)能否用：（yyyy.mm

22、.dd）这种格式来计算出工龄有多长呢？以前用这样一段（=TEXT(RIGHT(YEAR(NOW()-A1),2)&=TEXT(RIGHT(YEAR(NOW()-A1),2)&年年&MOD(MONTH(NOW()-A1)-1,12)&MOD(MONTH(NOW()-A1)-1,12)&个月个月,),)）。但这种方法只能用：（yyyy-mm-dd）这样的日期格式才能实现！你不妨把“.”替换成“-”，不就行了吗，再说后者是日期的一种标准格式，=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,.,-),2)&=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A

23、1,.,-),2)&年年&MOD(MONTH(NOW()-SUBSTITUTE(A1,.,-&MOD(MONTH(NOW()-SUBSTITUTE(A1,.,-)-1,12)&)-1,12)&个月个月,),)【时间和日期应用】【时间和日期应用】自动显示当前日期公式自动显示当前日期公式=YEAR(NOW()=YEAR(NOW()当前年当前年=MONTH(NOW()=MONTH(NOW()当前月当前月=DAY(NOW()=DAY(NOW()当前日当前日如何在单元格中自动填入当前日期如何在单元格中自动填入当前日期 Ctrl+;Ctrl+;如何判断某日是否星期天如何判断某日是否星期天=WEEKDAY(

24、A2,2)=WEEKDAY(A2,2)=TEXT(A1,aaaa)=TEXT(A1,aaaa)=MOD(A1,7)2=MOD(A1,7)1)1)显示昨天的日期显示昨天的日期每天需要单元格内显示昨天的日期，但双休日除外。例如，今天是 7 月 3 号的话，就显示 7 月 2 号，如果是 7 月 9 号，就显示 7 月 6 号。=IF(TEXT(TODAY(),AAA)=IF(TEXT(TODAY(),AAA)=一一,TODAY()-3,IF(TEXT(TODAY(),AAA)=,TODAY()-3,IF(TEXT(TODAY(),AAA)=日日,TODAY()-2,TODAY()-1),TODAY

25、()-2,TODAY()-1)=IF(TEXT(TODAY(),AAA)=IF(TEXT(TODAY(),AAA)=一一,TODAY()-3,TODAY()-1),TODAY()-3,TODAY()-1)关于取日期关于取日期怎么设个公式使 A1 在年月日向后推 5 年,变成 2011-7-15=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)=EDATE(A1,12*5)=EDATE(A1,12*5)如何对日期进行上、中、下旬区分如何对日期进行上、中、下旬区分=LOOKUP(DAY(A1),0,11,21,3

26、1,=LOOKUP(DAY(A1),0,11,21,31,上旬上旬,中旬中旬,下旬下旬,下旬下旬)如何获取一个月的最大天数如何获取一个月的最大天数=DAY(DATE(2002,3,1)-1)=DAY(DATE(2002,3,1)-1)或=DAY(B1-1)=DAY(B1-1),B1 为2001-03-012001-03-01日期格式转换公式日期格式转换公式将将“01/12/2005”“01/12/2005”转换成“20050112”格式转换成“20050112”格式RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1

27、,2)YEAR($A2)&TEXT(MONTH($A2),00)&TEXT(DAY($A2),00)YEAR($A2)&TEXT(MONTH($A2),00)&TEXT(DAY($A2),00)该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法：1、先转换成文本,然后再用字符处理函数。2、数据-分列 日期-MDY将“将“20052005 年年 9 9 月”转换成“月”转换成“200509200509”格式”格式先用公式：=text(a1,yyyymm)+0=text(a1,yyyymm)+0然后将单元格格式为常规。将“将“2005-8-62005-8-6”格式转换为“”格式转换为

28、“2005080620050806”格式”格式用公式：=TEXT(A1,YYYYMMDD)=TEXT(A1,YYYYMMDD)反之反之，将 2005080620050806 转为日期转为日期 2005-8-62005-8-6 格式，可用公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)另四种公式另四种公式：text(a1,0000-00-00)text(a1,0000-00-00)显示：2005-08-06-TEXT(A1,#-00-00)-TEXT(A1,#-00-00)，把单

29、元格设置为日期格式显示：2005-8-6TEXT(20050806,0000-00-00)*1TEXT(20050806,0000-00-00)*1，单元格设置日期型显示：2005-8-6VALUE(LEFT(A1,4)&-&MID(A1,5,2)&-&RIGHT(A1,2)VALUE(LEFT(A1,4)&-&MID(A1,5,2)&-&RIGHT(A1,2)显示：2005-8-6将“将“2006050120060501”转换为“”转换为“2006-05-012006-05-01”格式”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)=DATE(LEFT(

30、A2,4),MID(A2,5,2),RIGHT(A2,2)将“将“199306199306”转换为“”转换为“1993-61993-6”公式 1：=LEFT(A3,4)&-&RIGHT(A3,2)*1=LEFT(A3,4)&-&RIGHT(A3,2)*1公式 2：=-TEXT(A3*100+1,#-00-00)=-TEXT(A3*100+1,#-00-00)公式 2 需要设置单元格格式，自定义：e-m公式 3：=TEXT(TEXT(A3&01,0000-00-00),e-m)=TEXT(TEXT(A3&01,0000-00-00),e-m)把把 198405198405 转换成转换成 1984

31、.051984.05一、查找一、查找19841984，替换，替换1984.1984.二、如果全部是年月的话，我个人建议，1、采取辅助mid(xxxxxx,1,4)&.&right(xxxxxx,2)2、选中这列，用数据中的分列。然后三、单元格格式数字自定义，类型下面输入：三、单元格格式数字自定义，类型下面输入：#.#.#将文本“2004.01.02”转换为日期格式：将文本“2004.01.02”转换为日期格式：2004-1-22004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1

32、,9,2)将将 2005-8-62005-8-6 转换为转换为 20052005 年年 8 8 月月 6 6 日格式日格式=TEXT(A1,yyyy=TEXT(A1,yyyy年年mm月月dd日日;);)象象 2222 怎样转换成怎样转换成 2222 日？转成当年当月的日子日？转成当年当月的日子公式为：=date(year(now(),month(now(),22)=date(year(now(),month(now(),22)将“将“20062006 年年 5 5 月”转换成“月”转换成“20062006 年年 0505 月”月”公式为：=TEXT(A8,yyyy=TEXT(A8,yyyy年年

33、mmmm月月;);)也可以这样处理：选中单元格，设置单元格公式数字自定义，将yyyy“年”m“月”改为：yyyy“年”mm“月”，即可。但这方法打印出来显示为：2006/5/将“将“19681968 年年 6 6 月月 1212 日”转换为“日”转换为“1968/6/121968/6/12”格式”格式=YEAR(A1)&/&MONTH(A1)&/&DAY(A1)=YEAR(A1)&/&MONTH(A1)&/&DAY(A1)显示：1968/6/12=TEXT(A1,yyyy/mm/dd)=TEXT(A1,yyyy/mm/dd)显示：1968/06/12将“将“19681968 年年 6 6 月月

34、 1212 日”转换为“日”转换为“1968-6-121968-6-12”格式”格式=YEAR(A1)&-&MONTH(A1)&-&DAY(A1)=YEAR(A1)&-&MONTH(A1)&-&DAY(A1)显示：1968-6-12=TEXT(A1,yyyy-mm-dd)=TEXT(A1,yyyy-mm-dd)显示：1968-06-12将将 1993-12-281993-12-28 的日期格式转换成的日期格式转换成 19931993 年年 1212 月月=CONCATENATE(YEAR(A1),=CONCATENATE(YEAR(A1),年年,MONTH(A1),MONTH(A1),月月)=

35、YEAR(A1)&=YEAR(A1)&年年&MONTH(A1)&MONTH(A1)&月月 也可以自定义格式$-404e$-404e年年mm月月 将“将“1978-5-21978-5-2”包含年月日的日期转换成“”包含年月日的日期转换成“197805197805”只有年月的格式”只有年月的格式year(A1)&text(month(A1),00)year(A1)&text(month(A1),00)要将“要将“99.08.1599.08.15”格式转换成“格式转换成“1999.08.151999.08.15”如何做”如何做选中列，数据菜单中选分列，分列过程中“格式”选“日期 YMD”，结束。要保

36、持要保持 2005/8/62005/8/6 格式格式当输入 2005/8/6 后系统自动变成 2005-8-6，要保持 2005/8/6 格式，可以使用强制文本（前面加号）或使用公式=TEXT(A1,YYYY/MM/DD)=TEXT(A1,YYYY/MM/DD)。也可以用另一种公式：=IF(ISERROR(TEXT(A1,yyyy/mm/dd),TEXT(A1,0000!/00!/00),TEXT(A1,yyyy/mm/dd)=IF(ISERROR(TEXT(A1,yyyy/mm/dd),TEXT(A1,0000!/00!/00),TEXT(A1,yyyy/mm/dd)将“二三年十二月二十五日

37、”转为“将“二三年十二月二十五日”转为“2003-12-252003-12-25”格式”格式，1、可以用数组公式将中文日期转化为日期系列数=14610+MATCH(SUBSTITUTE(A3,=14610+MATCH(SUBSTITUTE(A3,元元,一一),TEXT(ROW($14611:$55153),DBNum1yyyy),TEXT(ROW($14611:$55153),DBNum1yyyy 年年 m m 月月 d d 日日),0),0)该公式速度较慢。2、改进后的公式，速度要快的多：=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DB

38、Num10000),0),MONTH(MATCH(SUBSTITUTE=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DBNum10000),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),(MID(A7,6,7),元元,一一),TEXT(ROW($1:$366),DBNum1m),TEXT(ROW($1:$366),DBNum1m 月月 d d 日日),0),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),),0),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),元元,一一),

39、TEXT(ROW($1:$366),DBNum1m),TEXT(ROW($1:$366),DBNum1m 月月 d d 日日),0),0)要设置为 1900 年的日期格式。日期格式转换日期格式转换如 A 列是月份数为 8，B 列是日期数为 18，如何在 C 列显示“8 月 18 日”=A1&=A1&月月&B1&B1&日日 反之，要将 C 列的“8 月 18 日”直接分别到 D、E 列，显示月份和日期，月数份=LEFT(C5,FIND(=LEFT(C5,FIND(月月,C5)-1),C5)-1)日期数=MID(C5,FIND(=MID(C5,FIND(月月,C5)+1,FIND(,C5)+1,F

40、IND(日日,C5)-FIND(,C5)-FIND(月月,C5)-1),C5)-1)也可分别用公式：=month(-c5)=month(-c5)=day(-c5)=day(-c5)日期格式转换问题日期格式转换问题输入的日期是:04-07-26.与另一格的001合并,合并出来是:040726001.=TEXT(A1,YYMMDD)&001=TEXT(A1,YYMMDD)&001要想自动取得“编制日期：要想自动取得“编制日期：XXXXXXXX 年年 X X 月月 X X 日”日”可在该单元格输入=编制日期：编制日期：&TEXT(TODAY(),yyyy&TEXT(TODAY(),yyyy 年年 m

41、 m 月月 d d 日日)【排名及排序筛选】【排名及排序筛选】一个具有一个具有 1111 项汇总方式的函数项汇总方式的函数 SUBTOTALSUBTOTAL=SUBTOTAL(9,$B$2:B2)=SUBTOTAL(9,$B$2:B2)在数据筛选求和上有意想不到的功能，11 项功能为：1、求平均数，2、求计数，3、求计数值（自动筛选序列）4、求最大值，5、求最小值，6、求乘积，7、求总体标准偏差，8、求标准偏差、9、求和，10、求方差，11、求总体方差。自动排序自动排序=SUBTOTAL(3,$B$2:B2)*1=SUBTOTAL(3,$B$2:B2)*1=IF(A2A1,1,N(C1)+1)

42、=IF(A2A1,1,N(C1)+1)按奇偶数排序按奇偶数排序我想请教怎样按奇数顺序然后再按偶数顺序排序=IF(MOD(A1,2),0,1)=IF(MOD(A1,2),0,1)=IF(ROW()50,(ROW()*2)-100,(ROW()*2)-1)=IF(ROW()50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(ROW()50)*99=ROW()*2-1-(ROW()50)*99自动生成序号自动生成序号比如在第二列中输入内容回车后第一列的下一行自动生成序列号。=IF(B2,A2+1,)=IF(B2,A2+1,)如何自动标示如何自动标示 A A 栏中的数字大

43、小排序？栏中的数字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,$A$1:$A$5)=RANK(A1,A:A)=RANK(A1,A:A)如何设置自动排序如何设置自动排序A 列自动变成从小到大排列B=SMALL(A$2:A$28,ROW(1:1)B=SMALL(A$2:A$28,ROW(1:1)A 列自动变成从大到小排列B=LARGE(A$2:A$28,ROW(1:1)B=LARGE(A$2:A$28,ROW(1:1)重复数据得到唯一的排位序列重复数据得到唯一的排位序列想得到数据的出现总数吗（1,2,2,3,4,4,5 数据的出现总数为 5）？解答：不需要插列,不需要很多的函

44、数就行了.=RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1=RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1按字符数量排序按字符数量排序制作歌曲清单时，习惯按字符数量来排列分类，但是 EXCEL 并不能直接按字数排序。需要先计算出每首歌曲的字数，然后再进行排序。如 A、B 列分别为“歌手”和“歌名”，在 C1 输入“字数”，在 C2 输入公式：LEN(B2)LEN(B2)下拖，单击 C2，单击工具栏上的“升序排列”即可，删除 C 列。排序字母与数字的混合内容排序字母与数字的混合内容日常使用中，表格经常会有包含字母和数字混合的数据，对此类

45、数据排序时，通常是先比较字母的大小，再比较数字的大小，但 EXCEL 是按照对字符进行逐位比较来排序的，如下表：A7 排在第 5 位，而不是第 1 位。排序结果无法令人满意。A1A1222A29345678910A317A43A7B20B3C144C5C3312345678910AA7A29A43A122A317B3B20C5C33C144BA007A029A043A122A317B003B020C005C033C144如果希望 EXCEL 改变排序的规则，需要将数据做一些改变。在 B1 中输入公式：LEFT(A1,1)&RIGHT(000&RIGHT(A1,LEN(A1)-1),3)LEFT

46、(A1,1)&RIGHT(000&RIGHT(A1,LEN(A1)-1),3)下拖单击 B2，单击工具栏上的“升序排列”即可。随机排序随机排序如 A、B 列分别为“歌手”和“歌名”，在 C1 输入“次序”，在 C2 输入公式：RANDRAND（）（），下拖，单击 C2，单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。排序的问题排序的问题我想要这样的排序:2001-2003 2004-2006 2007-2009 2010-2012;其实不是数据排序，应该是数据填充。输入公式=LEFT(E3,4)+3&-&RIGHT(E3,4)+3=LEFT(E3,4)+3&-&RIGHT(E3,4)+3

47、 即可。怎样才能让数列自动加数怎样才能让数列自动加数怎样做才能让数列自动加数A A0001B B0001A A0002C C0001A A0003B B0002C C0002公式为公式为=A1&000&COUNTIF(A$1:A1,A1)=A1&000&COUNTIF(A$1:A1,A1)向下拖=TEXT(COUNTIF(A$1:A1,A1),!&A1&0000)=TEXT(COUNTIF(A$1:A1,A1),!&A1&0000)否则数字超过 9 就错误了。一个排序问题一个排序问题一个电子表格，格式是 101、102.999,10101、10102.99901,1010101,1020201

48、.9990101,请问如何将它排列成 101,10101,1010101,102,10201,1020101,.999,99901,9990101 的形式。根据规律的重复的姓名列产生自动序号根据规律的重复的姓名列产生自动序号姓名序号 张三1 张三 1 李四 2 李四 2 赵五 3 赵五 3 赵五 3 王六4 王六 4=(A1A2)+N(B1)=(A1A2)+N(B1)=IF(A3=A2,B2,B2+1)=IF(A3=A2,B2,B2+1)姓名已排序：B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:

49、A2)姓名未排序：B2=IF(COUNTIF(A$2:A2,A2)1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)B2=IF(COUNTIF(A$2:A2,A2)1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)排名的函数排名的函数用排名函数来对成绩进行排名，用起来非常地方便。=IF(ISERR(RANK(M3,M:M),RANK(M3,M:M)=IF(ISERR(RANK(M3,M:M),RANK(M3,M:M)A 列是成绩,B 列是排名=SUMPRODUCT(A

50、$1:A$9A1)/COUNTIF(A$1:A$9,A$1:A$9)+1=SUMPRODUCT(A$1:A$9A1)/COUNTIF(A$1:A$9,A$1:A$9)+1自动排名公式自动排名公式=RANK(C3,$C$3:$C$12)=RANK(C3,$C$3:$C$12)=RANK(A2,$A$2:$A$11,0)=RANK(A2,$A$2:$A$11,0)=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1百分比排名的公式写法为：百分比排名的公式写法为：=PERCENTRAN