EXCEL函数公式工程计算运用大全

1、excel常用函数公式及技巧搜集（常用的）【身份证信息？提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXXT(MIDD(A1,77,6+(LEEN(A1)=18)*2),#-000-000)*1=IF(A2,TEEXT(LEEN(AA2)=15)*199&MIID(AA2,77,6+(LEEN(AA2)=18)*2),#-000-000)+0,)显示格式式均为yyyyyy-m-d。（最简单单的公式式，把单元元格设置置为日期期格式）=IF(LENN(A22)=115,19&MIID(AA2,77,2)&-&MMID(

2、A2,9,22)&-&MIDD(A22,111,2),MIID(AA2,77,4)&-&MMID(A2,11,2)&-&MIID(AA2,113,22) 显示格式式为yyyyy-mm-dd。（如果果要求为为“19995/003/229”格式的的话，将将”-” 换成成”/”即可）=IF(D4=,IF(LENN(D44)=115,TTEXTT(19&MIID(DD4,77,6),00000年00月00日),IF(LENN(D44)=118,TTEXTT(MIID(DD4,77,8),000000年00月00日)显示格式式为yyyyy年年mm月月dd日日。（如如果将公公式中“00000年00月00日

3、”改成“00000-00-00”,则显显示格式式为yyyyy-mm-dd）=IF(LENN(A11:A22)=118,MMID(A1:A2,7,88),19&MIID(AA1:AA2,77,6)显示格式式为yyyyymmmddd。=TEXXT(LENN(A11)=115)*19&MIDD(A11,7,6+(LENN(A11)=118)*2),#-00-00)+00=IF(LENN(A22)=118,MMID(A2,7,44)&-MIDD(A22,111,2),199&MIID(AA2,77,2)&-MMID(A2,9,22)=MIDD(A11,7,4)&年&MIID(AA1,111,22)&月

4、&MIDD(A11,133,2)&日日=IF(A1,TEEXT(LEEN(AA1)=15)*199&MIID(AA1,77,6+(LEEN(AA1)=18)*2),#-000-000)从身份证证号码中中提取出出性别=IF(MODD(MIID(AA1,115,33),22),男,女)（最最简单公公式）=IF(MODD(RIIGHTT(LEEFT(A1,17),22),男,女)=IF(A2”,IFF(MOOD(RRIGHHT(LLEFTT(A22,177),2),”男”,”女”),)=IF(VALLUE(LENN(ROOUNDD(RIIGHTT(A11,1)/2,2)=11,男男,女)从身份证证号

5、码中中进行年年龄判断断=IF(A3”,DAATEDDIF(TEXXT(LENN(A33)=115*119&MMID(A3,7,66+(LLEN(A3)=188*2),”#-000-000”),TTODAAY(),”Y”),)=DATTEDIIF（A1，TODDAY（），“Y”）（以上公公式会判判断是否否已过生生日而自自动增减减一岁）=YEAAR(NNOW()-MIDD(E22,IFF(LEEN(EE2)=18,9,77),22)-119000=YEAAR(TTODAAY()-IIF(LLEN(A1)=155,119&MIDD(A11,7,2),MIDD(A11,7,4)=YEAAR(TTODA

6、AY()-VVALUUE(MMID(B1,7,44)&岁=YEAAR(TTODAAY()-IIF(MMID(B1,18,1)=,CONNCATTENAATE(199,MMID(B1,7,22),MIDD(B11,7,4)按身份证证号号码码计算至至今天年年龄 =DATTEDIIF(TTEXTT(LLEN(A1)=155)*119&MMID(A1,7,66+(LLEN(A1)=188)*22),#-000-000),TOODAYY(),y)以20006年10月31日为基准准日,按按身身份证计计算年龄龄(周岁)的公式式=DATTEDIIF(TTEXTT(MIID(AA1,77,6+(LEEN(AA1

7、)=18)*2),#-000-000)*1,20006-110-331,y)按身份证证号分男男女年龄龄段按身份证证号分男男女年龄龄段，身身份证号号在K列，年年龄段在在J列（身身份证号号为188位）男性166周岁以以下为11男性166周岁（含166周岁）以上至至50周岁岁为 2男性500周岁（含500周岁）以上至至60周岁岁为 3男性600周岁（含600周岁）以上为为 4女性166周岁以以下为11女性166周岁（含166周岁）以上至至45周岁岁为 2女性455周岁（含455周岁）以上至至55周岁岁为 3女性555周岁（含555周岁）以上为为 4=MATTCH(DATTEDIIF(DDATEE(M

8、IID(KK1,77,4),MIID(KK1,111,22),MMID(K1,13,2),TOODAYY(),y),0,116,550,660-0,0,55,5*ISSEVEEN(MMID(K1,17,1)=SUMM(-(DAATEDDIF(MIDD(K11,7,4)&/&MIID(KK1,111,22)&/&MIDD(K11,133,2),TOODAYY(),y)=0,16,45,55+00,0,5,55*MMOD(MIDD(K11,177,1),2)【年龄和和工龄计计算】根据出生生年月计计算年龄龄DATTEDIIF(AA1,TTODAAY(),yy)DATTEDIIF(AA1,TTODAA

9、Y(),yy)&周岁岁DATTEDIIF(AA1,NNOW(),y)根据出生生年月推推算生肖中国人有有12生生肖，属属什么可可以推算算出来。即用诞诞生年份份除以112，再再用除不不尽的余余数对照照如下：0猴，11鸡，22狗，33猪，44鼠，55牛，66虎，77兔，88龙，99蛇，110马，111羊例如如：XXXX出生生于19921年年，即用用19221年除除以122，商得得数为1160，余数为为1，对对照上面面得知余余数1对对应生肖肖是鸡，XXXX就属鸡鸡。=MIDD(猴猴鸡狗猪猪鼠牛虎虎兔龙蛇蛇马羊,MOOD(YYEARR(A22),112)+1,11) （220077）如何求出出一个人人到

10、某指指定日期期的周岁岁？=DATTEDIIF(起起始日期期,结束日日期,Y)计算距离离退休年年龄的公公式=IF(E2=,IF(E2=V22,已已经退休休,距离退退休还有有&DDATEEDIFF(TOODAYY(),DATTE(YYEARR(U22)+(V2),MOONTHH(U22),DDAY(U2),Y)&年年&DDATEEDIFF(TOODAYY(),DATTE(YYEARR(U22)+(V2),MOONTHH(U22),DDAY(U2),YM)&个月&DAATEDDIF(TODDAY(),DDATEE(YEEAR(U2)+(VV2),MONNTH(U2),DAAY(UU2),MMd)&天

11、天)其中E22为年龄龄（可用用身份证证号码的的公式生生成）；V2为法法定退休休年龄（男600，女550）公公式为：=IFF(D22=,IFF(D22=男男,660,550)D2为男男或女（可用身身份证号号码的公公式生成成）；U2为为出生年年月日（可用身身份证号号码的公公式生成成）。求工齡=DATTEDIIF(BB2,TTODAAY(),yy) =DATTEDIIF(BB2,TTODAAY(),yym) =DATTEDIIF(BB2,TTODAAY(),mmd) =DATTEDIIF(BB2,TTODAAY(),yy)&年&DDATEEDIFF(B22,TOODAYY(),ymm)&月&DDAT

12、EEDIFF(B22,TOODAYY(),mdd)&日 计算工龄龄=DATTEDIIF(CC6,CC8,y)求两日日期间的的年数=DATTEDIIF(CC6,CC8,ym)求两两日期间间除去整整年数剩剩余的月月数=DATTEDIIF(CC6,CC8,m)求两日日期间的的总月数数如果只需需要算出出周年的的话，可可以用=dattediif(19778-88,20006-55,Y)年龄及工工龄计算算有出生年年月如何何求年龄龄？有工作时时间如何何求工龄龄？（求求出的结结果为多多少年另另几个月月，如：03003的形形式，即即3年零3个月）。a1是出出生年月月或工作作时间：=dattediif(aa1,t

13、todaay(),yy)=texxt(ddateediff(a11,toodayy(),y),00)&ttextt(daateddif(a1,todday(),m),000)如 BB2=19664-99-1 则:=TEXXT(DDATEEDIFF(B22,TOODAYY(),y),00)&TTEXTT(MOOD(DDATEEDIFF(B22,TOODAYY(),m),112),000) 显示 40009=TEXXT(DDATEEDIFF(B22,TOODAYY(),y),00年年)&TEXXT(MMOD(DATTEDIIF(BB2,TTODAAY(),mm),12),000月)显示示 400年

14、099月如果你找找不到 DATTEDIIF 函函数，也也可以不不用 DDATEEDIFF 函数数,如 BB2=19664-99-1 则:=TEXXT(RRIGHHT(YYEARR(NOOW()-B22),22),00)&TTEXTT(MOOD(MMONTTH(NNOW()-BB2)-1,112),000) 显示 40009=TEXXT(RRIGHHT(YYEARR(NOOW()-B22),22)&年&MODD(MOONTHH(NOOW()-B22)-11,122)&个月,) 显示 40年年09个个月自动算出出工龄日日期格式式为(yyyyyy.mmm.ddd)能否用：（yyyyy.mm.dd）这

15、种格格式来计计算出工工龄有多多长呢？以前用这这样一段段（ =TTEXTT(RIIGHTT(YEEAR(NOWW()-A1),2)&年年&MMOD(MONNTH(NOWW()-A1)-1,12)&个个月,) ）。但这种方方法只能能用：（yyyyy-mmm-ddd）这这样的日日期格式式才能实实现！你不妨把把“.”替换成成“-”，不就就行了吗吗，再说说后者是是日期的的一种标标准格式式，=TEXXT(RRIGHHT(YYEARR(NOOW()-SUUBSTTITUUTE(A1,.,-),2)&年年&MMOD(MONNTH(NOWW()-SUBBSTIITUTTE(AA1,.,-)-1,112)&个月月

16、,)【时间和和日期应应用】自动显示示当前日日期公式式=YEAAR(NNOW() 当前前年=MONNTH(NOWW() 当前前月=DAYY(NNOW() 当前日日如何在单单元格中中自动填填入当前前日期Ctrll+;如何判断断某日是是否星期期天=WEEEKDAAY(AA2,22)=TEXXT(AA1,aaaaa)=MODD(A11,7)1)显示昨天天的日期期每天需要要单元格格内显示示昨天的的日期，但双休休日除外外。例如，今今天是77月3号号的话，就显示示7月22号，如果是是7月99号，就就显示77月6号号。=IF(TEXXT(TTODAAY(),AAAA)=一,TTODAAY()-3,IF(TEX

17、XT(TTODAAY(),AAAA)=日,TTODAAY()-2,TODDAY()-11)=IF(TEXXT(TTODAAY(),AAAA)=一,TTODAAY()-3,TODDAY()-11)关于取日日期怎么设个个公式使使A1在在年月日日向后推推5年,变成220111-7-15=DATTE(YYEARR(A11)+55,MOONTHH(A11),DDAY(A1)=EDAATE(A1,12*5)如何对日日期进行行上、中中、下旬旬区分=LOOOKUPP(DAAY(AA1),0,11,21,31,上旬,中旬旬,下旬,下旬旬)如何获取取一个月月的最大大天数=DAAY(DDATEE(20002,3,1

18、1)-11)或或=DDAY(B1-1),B11为220011-033-011日期格式式转换公公式将 “001/112/220055”转换成成“20005001122”格式式RIGGHT(A1,4)&MIDD(A11,4,2)&LEFFT(AA1,22)YEAAR($A2)&TEEXT(MONNTH($A22),00)&TTEXTT(DAAY($A2),000) 该公公式不用用设置数数据有效效性,但要设设置储存存格格式式。也可以用用下列两两方法：1、先转转换成文文本, 然后后再用字字符处理理函数。2、数数据-分列列 日期-MMDY将“20005年年9月”转换成成“20005099”格式先用公式式

19、：=ttextt(a11,yyyyyymm)+00 然然后将单单元格格格式为常常规。将“20005-8-66”格式转转换为“2000508806”格式用公式：=TEEXT(A1,YYYYYMMMDDD)反之，将将2000508066转为日期20005-8-66格式，可可用公式式：=DATTE(LLEFTT(A11,4),MIID(AA1,55,2),RIIGHTT(A11,2)另四种公公式：texxt(aa1,00000-000-000)显示：20005-008-006-TTEXTT(A11,#-000-000)，把单元元格设置置为日期期格式 显示：20005-88-6TEXXT(200050

20、8806,00000-00-00)*11，单元元格设置置日期型型显示：20005-88-6VALLUE(LEFFT(AA1,44)&-&MIDD(A11,5,2)&-&RIIGHTT(A11,2)显示示：20005-8-66将“20006005011”转换为为“20006-005-001”格式=DATTE(LLEFTT(A22,4),MIID(AA2,55,2),RIIGHTT(A22,2)将“19993006”转换为为“19993-66”公式1：=LEEFT(A3,4)&-&RIIGHTT(A33,2)*1公式2：=-TTEXTT(A33*1000+11,#-000-000)公公式2需要设设

21、置单元元格格式式，自定定义：ee-m公式3：=TEEXT(TEXXT(AA3&01,000000-000-000),e-m)把19884055转换成成19884.005一、查找找19884，替替换19884.二、如果果全部是是年月的的话，我我个人建建议， 1、采取取辅助midd(xxxxxxxx,11,4) & . & rigght(xxxxxxxx,2) 2、选中中这列，用数据据中的分分列。然然后三、单元元格格式式数字字自定定义，类类型下面面输入：#.#将文本“20004.001.002” 转换为为日期格格式：220044-1-2=DATTE(MMID(A1,1,44),MMID(A1,6,

22、22),MMID(A1,9,22)将20005-88-6转转换为220055年8月6日格式=TEXXT(AA1,yyyyy年m月d日;)象22怎怎样转换换成222日？转转成当年年当月的的日子公式为：=daate(yeaar(nnow(),monnth(noww(),222)将“20006年年5月”转换成成“20006年005月”公式为：=TEEXT(A8,yyyyy年mmm月月;)也可以这这样处理理：选中中单元格格，设置置单元格格公式数字自定义义，将yyyyyy“年”m“月”改为：yyyyy“年”mm“月”，即可可。但这这方法打打印出来来显示为为：20006/5/将“19968年年6月112日

23、”转换为为“19668/66/122”格式=YEAAR(AA1)&/&MOONTHH(A11)&/&DAYY(A11)显示示：19968/6/112=TEXXT(AA1,yyyyy/mmm/ddd)显示：19668/006/112将“19968年年6月112日”转换为为“19668-66-122”格式=YEAAR(AA1)&-&MMONTTH(AA1)&-&DDAY(A1)显示：19668-66-122=TEXXT(AA1,yyyyy-mmm-ddd) 显显示：119688-066-122将19993-112-228的日日期格式式转换成成19993年112月=CONNCATTENAATE(YE

24、AAR(AA1),年,MOONTHH(A11),月) =YEAAR(AA1)&年&MOONTHH(A11)&月也可以自自定义格式 $-4404e年年m月将“19978-5-22”包含年年月日的的日期转转换成“19778055”只有年年月的格格式yeaar(AA1)&texxt(mmontth(AA1),000)要将“999.008.115” 格式式转换成成“19999.008.115”如何做做选中列，数据菜菜单中选选分列，分列过过程中“格式”选“日日期YMMD”，结束。要保持220055/8/6格式式当输入220055/8/6后系系统自动动变成220055-8-6，要要保持220055/8/6

25、格式式，可以以使用强强制文本本（前面面加号号）或使使用公式式=TEEXT(A1,YYYYY/MM/DD)。也也可以用用另一种种公式：=IFF(ISSERRROR(TEXXT(AA1,yyyyyy/mmm/ddd),TEEXT(A1,000000!/000!/00),TTEXTT(A11,yyyyyy/mmm/ddd)将“二三年年十二月月二十五五日”转为“20003-112-225”格式，1、可以以用数组组公式将将中文日日期转化化为日期期系列数数=1446100+MAATCHH(SUUBSTTITUUTE(A3,元,一一),TEXXT(RROW($1446111:$5551553),DDBNuu

26、m1yyyyy年mm月d日日),0)该公式速速度较慢慢。2、改进进后的公公式，速速度要快快的多：=DAATE(18999+MMATCCH(LLEFTT(A77,4),TEEXT(ROWW($119000:$221000),DBBNumm1000000),0),MONNTH(MATTCH(SUBBSTIITUTTE(MMID(A7,6,77),元,一),TTEXTT(ROOW($1:$3666),DBBNumm1mm月d日日),0),DAAY(MMATCCH(SSUBSSTITTUTEE(MIID(AA7,66,7),元元,一),TEEXT(ROWW($11:$3366),DBNNum11m月月

27、d日),00)要设置为为19000年的的日期格格式。日期格式式转换如A列是是月份数数为8，B列是是日期数数为188，如何何在C列列显示“8月118日”=A1&月&B11&日日反之，要要将C列列的“8月118日”直接分分别到DD、E列列，显示示月份和和日期，月数份=LEFFT(CC5,FFINDD(月月,CC5)-1)日期数=MIDD(C55,FIIND(月,CC5)+1,FFINDD(日日,CC5)-FINND(月,CC5)-1)也可分别别用公式式：=monnth(-cc5)=dayy(-c5)日期格式式转换问问题输入的日日期是:04-07-26.与另一一格的0011合并并,合并并出来是是:0

28、44072260001.=TEXXT(AA1,YYMMMDDD)&0001要想自动动取得“编制日日期：XXXXXX年X月月X日”可在该单单元格输输入 =编制制日期：&TTEXTT(TOODAYY(),yyyyy年年m月d日)【排名及及排序筛筛选】一个具有有11项项汇总方方式的函函数SUUBTOOTALL=SUBBTOTTAL(9,$BB$2:B2)在数据筛筛选求和和上有意意想不到到的功能能，111项功能能为：11、求平平均数，2、求求计数，3、求求计数值值（自动动筛选序序列）44、求最最大值，5、求求最小值值，6、求乘积积，7、求总体体标准偏偏差，88、求标标准偏差差、9、求和，10、求方差差

29、，111、求总总体方差差。自动排序序=SUBBTOTTAL(3,$B$22:B22)*11=IF(A2A11,1,N(CC1)+1)按奇偶数数排序我想请教教怎样按按奇数顺顺序然后后再按偶偶数顺序序排序=IF(MODD(A11,2),0,1)=IF(ROWW()50,(ROOW()*2)-1000,(ROWW()*2)-1)=ROWW()*2-11-(RROW()550)*99自动生成成序号比如在第第二列中中输入内内容回车车后第一一列的下下一行自自动生成成序列号号。=IF(B2,A22+1,)如何自动动标示AA栏中的的数字大大小排序序？=RANNK(AA1,$A$11:$AA$5)=RANNK(

30、AA1,AA:A)如何设置置自动排排序A列自动动变成从从小到大大排列B=SMMALLL(A$2:AA$288,ROOW(11:1)A列自动动变成从从大到小小排列B=LAARGEE(A$2:AA$288,ROOW(11:1)重复数据据得到唯唯一的排排位序列列想得到数数据的出出现总数数吗（1,22,2,3,44,4,5 数据的的出现总总数为55）？解答：不不需要插插列,不需要要很多的的函数就就行了. =RRANKK(B33,B$3:BB$122)+CCOUNNTIFF(B$3:BB3,BB3)-1按字符数数量排序序制作歌曲曲清单时时，习惯惯按字符符数量来来排列分分类，但但是EXXCELL并不能能直

31、接按按字数排排序。需需要先计计算出每每首歌曲曲的字数数，然后后再进行行排序。如A、BB列分别别为“歌手”和“歌名”，在CC1输入入“字数”，在CC2输入入公式：LENN(B22) 下拖拖，单击击C2，单击工工具栏上上的“升序排排列”即可，删除CC列。排序字母母与数字字的混合合内容日常使用用中，表格格经常会会有包含含字母和和数字混混合的数数据，对对此类数数据排序序时，通通常是先先比较字字母的大大小，再再比较数数字的大大小，但但EXCCEL是是按照对对字符进进行逐位位比较来来排序的的，如下下表：AA7排在在第5位位，而不不是第11位。排排序结果果无法令令人满意意。A1A12222A293A3177

32、4A435A76B207B38C14449C510C33AB1A7A00772A29A02993A43A04334A1222A12225A3177A31776B3B00337B20B02008C5C00559C33C033310C1444C1444如果希望望EXCCEL改改变排序序的规则则，需要要将数据据做一些些改变。在B1中中输入公公式：LLEFTT(A11,1)&RIIGHTT(0000&RIGGHT(A1,LENN(A11)-11),33)下拖拖单击B22，单击击工具栏栏上的“升序排排列”即可。随机排序序如A、BB列分别别为“歌手”和“歌名”，在CC1输入入“次序”，在CC2输入入公式：

33、RANND（），下拖，单单击C22，单击击工具栏栏上的“降序排排列”即可对对歌曲清清单进行行随机排排序。排序的问问题我想要这这样的排排序: 20001-22003320044-2000620077-20009 220100-20012;其实不是是数据排排序，应应该是数数据填充充。输入公式式=LEEFT(E3,4)+3&-&RIGGHT(E3,4)+3 即即可。怎样才能能让数列列自动加加数怎样做才才能让数数列自动动加数A AA00001B BB00001A AA00002C CC00001A AA00003B BB00002C CC00002公式为=A1&0000&COUUNTIIF(AA$1:

34、A1,A1)向下拖拖=TEXXT(CCOUNNTIFF(A$1:AA1,AA1),!&A11&000000)否否则数字字超过99就错误误了。一个排序序问题一个电子子表格，格式是是1011、1002. 9999,101101、101102. 999901,101101001,1102002011. 999901101,请问如如何将它它排列成成1011,1001011,100101101,1022,1002011,100201101,. 9999,9999001,9999001011 的形形式。我在数字字前加了了个字母母,比如如d&数数字,然后用用排序就就可以把把它们按按你的需需求排列列了.最最后

35、再把把字母d去去掉。数字的自自动排序序,插入入后不变变?1 赵赵一 总经经理2 赵赵二 副经经理3 赵赵三 副经经理4 赵赵四技术术员5 赵赵五6 赵赵六 员工工如上的一一个表,如何实实现当我我把赵六六这一整整行(第第6行)插入到到上面的的表中时时,A列列的序列列号不变变?最后后的效果果如下:1 赵赵一 总经经理2 赵赵二 副经经理3 赵赵六 员工工 4 赵赵三 副副经理5 赵赵四 技术术员6 赵赵五A1单元元格输入入公式 =roow(),往下下拉,然然后再插插入。=SUBBTOTTAL(3,$B$22:$BB2)在A1中中输入公公式：“=iff(b11=,coountta($b$11:b11

36、)”后后下拉复复制至AA列各行行即可（“”不不必输入入）根据规律律的重复复的姓名名列产生生自动序序号姓名序序号张三11张三11李四22李四22赵五33赵五33赵五33王六44王六44=(A11AA2)+N(BB1)=IF(A3=A2,B2,B2+1)姓名已排排序：B2=SSUMPPRODDUCTT(1/COUUNTIIF(AA$2:A2,A$22:A22)姓名未排排序：B2=IIF(CCOUNNTIFF(A$2:AA2,AA2)1,VVLOOOKUPP(A22,A:B,22,0),SUUMPRRODUUCT(1/CCOUNNTIFF(A$2:AA2,AA$2:A2)排名的函函数用排名函函数来对

37、对成绩进进行排名名，用起来来非常地地方便。=IF(ISEERR(RANNK(MM3,MM:M),RRANKK(M33,M:M)A列是成成绩,BB列是排排名=SUMMPROODUCCT(A$11:A$9AA1)/COUUNTIIF(AA$1:A$99,A$1:AA$9)+11自动排名名公式=RANNK(CC3,$C$33:$CC$122)=RANNK(AA2,$A$22:$AA$111,0)=RANNK(CC2,$C$22:$CC$655)+CCOUNNTIFF($CC$2:C2,C2)-1百分比排排名的公公式写法法为：=PERRCENNTRAANK($C$3:$C$112,CC3)平均分及及总

38、分排排名=AVEERAGGE(BB2:EE2)=RANNK(FF2,$F$22:$FF$6555366)求名次排排名统计成绩绩时遇到到一个分分别求班班级和年年级总分分名次排排名的问问题，不不晓得应应该运用用什么公公式来实实现。班级名次次：=SUMMPROODUCCT(BJ=A2)*(ZZFEE2)+1年级名次次：=RANNK(EE2,ZZF)公公式下拖拖。排名次根据总分分值大小小，只将将姓名排排序后, 降序序结果=INDDEX(A$22:A$6,RRANKK(D22,D$2:DD$6)根据总分分值大小小，只将将姓名排排序后, 升序序=INDDEX(A$22:A$6,RRANKK(D22,D$2

39、:DD$6,1)根据分数数进行普普通排名名=RANNK(AA2,$A$22:$AA$122)=RANNK(AA2,AA$2:A$112)+COUUNTIIF(AA$2:A2,A2)-1=SUMMPROODUCCT(11*($E$33:$EE$122=EE3)=RANNK(KK3,$K$33:$KK$266)=RANNK(AA2,AA$2:A$112)=SUMM(AA$2:A$112=A2)/COOUNTTIF(A$22:A$12,A$22:A$12)=COUUNTIIF($K$33:$KK$266,&KK3)+1=INDDEX($A$2:$A$77,MAATCHH(LAARGEE($CC$2:

40、$C$7,RROW(A1),$C$22:$CC$7,0),1)=SUMMPROODUCCT($A$2:$A$112AA2)/COUUNTIIF($A$22:$AA$122,$AA$2:$A$12&)+11=RANNK(DD2,OOFFSSET($A$1,MMATCCH($A2,$A:$A,0)-1,33,COOUNTTIF($A:$A,$A22),11)对于普通通排名分分数相同同时，按按顺序进进行不重重复排名名=RANNK(KK32,$K$32:$K$55)+COOUNTTIF($K$32:$K332,KK32)-1=COUUNTIIF($K$332:KK32,K322)-11+COOUNTT

41、IF($K$3:$K$226,&K322)+11=SUMMPROODUCCT(11*($E$3:$E$112+RROW($E$3:$E$112)/1000=($E33+ROOW(EE3)/1000)=RANNK(EE3,$E$33:$EE$122)+CCOUNNTIFF($EE$3:E3,E3)-1=SUMMPROODUCCT(11*($E$3:$E$112+$B$33:$BB$122/1000)=(EE3+BB3/1100)依分数比比高低名名次成绩绩排名=RANNK($E3,$E$3:$E$222)內內建方式式排名=SUMMPROODUCCT(11*($E$33:$EE$122=EE3)一般

42、方方式排名名=RAANK(E3,$E$3:$E$222)+SUMM(IFF($EE$3:$E$22E3,1/CCOUNNTIFF($EE$3:$E$22,$E$3:$E$222),0)-COOUNTTIF($E$3:$E$222,&E3)一般般方式排排名=RANNK(EE3,$E$33:$EE$122)+CCOUNNTIFF($EE$3:E3,E3)-1不不重复排排名=SUMMPROODUCCT(11*($E$3:$E$112+RROW($E$3:$E$112)/1000=($E33+ROOW(EE3)/1000)=SUMMPROODUCCT(11*($E$3:$E$112+$B$33:$BB

43、$122/1000)=(EE3+BB3/1100) 不重复复排名=SUMMPROODUCCT(11*($E$3:$E$112+$B$33:$BB$122/1000+$C$33:$CC$122/1000000)=(E33+B33/1000+CC3/1100000) 不不重复排排名=RANNK($E3,$E$3:$E$222,11)倒排排序美国式排排名=RANNK(KK2477,$KK$2447:$K$2270)=RANNK(BB1,$B1:$H11)中国式排排名=RANNK(BB2,$B$22:$BB$211,0)=RANNK(BB1,$B1:$H11)+CCOUNNTIFF($BB$1:B1,

44、B1)-1=SUMM(IFF($AA$1:$E$1=A1,1/CCOUNNTIFF($AA$1:$E$1,$A$11:$EE$1),)=SUMMPROODUCCT($B$2:$B$221=B2)/COOUNTTIF($B$2:BB$211,B$2:BB$211)=SUMMPROODUCCT(B$33:B$21B3)*(11/COOUNTTIF($B$3:$B$221,$B$33:$BB$211)+1（升序）=SUMMPROODUCCT(B$33:B$21B22)+1=SUUM(IIF($B$33:$BB$211=BB3,11/(CCOUNNTIFF($BB$3:B$221,BB$3:B$221

45、)+1（升序序）=SUUM(IIF($B$33:$BB$211B22,1/COUUNTIIF($B$22:B$21,B$22:B$21)+1=SUUM(IIF($A$11:$EE$1=A11,1/COUUNTIIF($A$11:$EE$1,$A$1:$E$11),)=SUUM($B$2:$B$221BB2)*(MAATCHH($BB$2:B$221,BB$2:B$221,)=ROOW($1:$20)+1=SUUM(IIF($B$11:$HH$1=EE2)/COUUNTIIF($E$22:$EE$211,$EE$2:$E$21),第DDBNUUM1G/通通用格式式名)排序后排排名=SUUM(II

46、F($B$22:$BB$155=BB2,11/COOUNTTIF($B$2:$B$115,$B$22:$BB$155)=SUMMPROODUCCT(B$22:B$15=B22)/CCOUNNTIFF(B$2:BB$155,B$2:BB$155)位次排名名=IFF($BB2:$O2=0,RANNK($B2:$O22,$BB2:$O2,0),)根据双列列成绩进进行共同同排名=RANNK(CC3455,($C$3345:$C$3566,$HH$3445:$H$3356)在双列间间排名=RANNK(BB2,($B$2:$B$226,$E$22:$EE$166)等次排名名由大到小小排名=RANNK(BB

47、3,$B$33:$BB$122)=SUMMPROODUCCT($A$16:$A$25=A166)*($B$16:$B$25B166)+1由小到大大排名=RANNK(BB3,$B$33:$BB$122,1)=SUMMPROODUCCT($A$16:$A$25=A166)*($B$16:$B$25BB16-ROWW(B116)/100000)+11由小到大大=RANNK(BB3,$B$33:$BB$122,1)+COOUNTTIF($B$3:BB3,BB3)-1=SUMMPROODUCCT($A$16:$A$25=A166)*($B$16:$B$25+ROWW($BB$166:$BB$255)/1

48、100000BB16+ROWW(B116)/100000)+11由小到大大=COUUNT($B$3:$B$112)-RANNK(BB3,$B$33:$BB$122)-CCOUNNTIFF($BB$3:B3,B3)+2=SUMMPROODUCCT($A$16:$A$25=A166)*($B$16:$B$25-ROWW($BB$166:$BB$255)/1100000B3)*(11/COOUNTTIF(B$33:B$12,B$33:B$12)+1=SUMMPROODUCCT($A$16:$A$25=A166)*($B$16:$B$25B166)/CCOUNNTIFF($KK$166:$KK$255

49、,$KK$166:$KK$255)+1由小到大大=SUMMPROODUCCT(B$33:B$12B3)*(11/COOUNTTIF(B$33:B$12,B$33:B$12)+1=SUMMPROODUCCT($A$16:$A$25=A166)*($B$16:$B$25B33)+1=COUUNTIIF($B$33:$BB$211,&BB3)+1=SUUM(IIF($B$33:$BB$211B33,1,0)+1=19-FREEQUEENCYY($BB$3:$B$21,B3)+1=SUMMPROODUCCT($B$2:$B$220=B2)/COOUNTTIF($B$2:$B$220,$B$22:$BB

50、$200)无并列排排名=RANNK(BB3,$B$33:$BB$211)+CCOUNNTIFF($BB$3:$B33,B33)-11=SUMMPROODUCCT(B3-ROWW()/10000B33-ROOW()/10000,1,00)+1有并列分分段排名名=SUMMPROODUCCT($A$3:$A$221=AA3)*($CC$3:$C$21C3)+11=19-FREEQUEENCYY($A$33:$AA$211=A33)*($C$3:$C$221),C3)+1=MAATCHH(C33,LAARGEE(OFFFSEET($C$22,IFF($AA$3:$A$21=A3,ROWW($AA$3:

51、$A$21)-2),),ROWW(INNDIRRECTT(11:&COUUNTIIF($A$33:$AA$211,A33),00)=MAATCHH(C33,LAARGEE(IFF($AA$3:$A$21=A3,$C$3:$C$221),ROWW(INNDIRRECTT(11:&COUUNTIIF($A$33:$AA$211,A33),00)=SUUMPRRODUUCT($AA$3:$A$21=A3)*($C$33:$CC$211C33)/CCOUNNTIFF($NN$3:$N$21,$N$3:$N$221)+1（需辅辅助列）无并列分分段排名名=SUUMPRRODUUCT($AA$3:$A$21

52、=A3)*($C$33:$CC$211-ROOW($C$33:$CC$211)/1100000CC3-RROW(C3)/1000000)+1=19-FREEQUEENCYY($A$33:$AA$211=A33)*($C$3:$C$221-RROW($C$3:$C$221)/10000),C3-ROWW()/10000)+1成绩排名名序号姓名语文数学英语1杨增海1351361462郭爱玲1381371413华志锋1341381414袁文飞134143135能否用一一个公式式直接找找出所用用考生中中语文成成绩中第第1000名的成成绩是多多少?=LARRGE(C2:C4117,1100)=PERRC

53、ENNTILLE(CC2:CC4177,(4416-1000)/4416)=PERRCENNTILLE($C$22:$CC$4117,(COUUNTAA($CC$2:$C$4177)-1100)/COOUNTTA($C$22:$CC$4117)能否用一一个公式式直接找找出所用用考生中中语文成成绩中按按与考人人数的335%切切线中位位于第335%的的成绩是是多少?升冪=SMAALL(C2:C4117,4416*0.335)=PERRCENNTILLE($C$22:$CC$4117,00.355)降冪=LARRGE(C2:C4117,4416*0.335)=PERRCENNTILLE($C$22:

54、$CC$4117,11-0.35)如何排名名1、对英英语进行行排名，缺缺考不计计算在内内。2、对英语语进行排排名，缺缺考计算算在内。英语英语排名名429623721485485721544429缺考缺考458467缺考不计计算在内内b2=IIF(AA2=缺考,RANNK(AA2,$A$22:$AA$133)然然后按照照B列排排序缺考计算算在内=IF(A2=缺考考,CCOUNNTIFF($AA$2:$A$13,=0)+1,RANNK(AA2,$A$22:$AA$133)=IF(A2=缺考考,CCOUNNT($A$22:$AA$133)+11,RAANK(A2,$A$2:$A$113,00)数据排

55、名名（隔几几行排名名）=IF(A2=,RANNK(AA2,$A$22:$AA$111,0)如果隔几几行排名名，如下下表，第第五行、第九行行和第十十二行不不参与排排名。单位数据排名A18A57A66小计12B84B93B75小计24C181C112小计29=IF(A2=小计计,RRANKK(B22,(BB$2:B$44,B$6:BB$8,B$110:BB$111)下拉根据分数数进行倒倒排名=RANNK($E3,$E$3:$E$222,11)=RANNK(KK60,$K$60:$K$83,1)=COUUNTIIF($K$660:$K$883,0,OKK,)名次筛选选名次=RRANKK(K55,K$

56、2:KK$4335)班名次=RANNK(KK6,OOFFSSET(K$22,MAATCHH(A66,A:A,)-2,COOUNTTIF(A$11:A$5000,A66)如何实现现快速定定位（筛筛选出不不重复值值）=IF(COUUNTIIF($A$22:A22,A22)=11,A22,)=IF(COOUNTTIF($A$2:AA2,AA2)=1)=TRUUE,AA2,)=INDDEX(A:AA,SMMALLL(IFF(MAATCHH(A$1:AA$200,A$1:AA$200,)=ROWW($11:$220),ROWW(A$1:AA$200),6655336),ROWW()&（数数组公式式）如何

57、请在在N列中中列出AA1:LL9中每每列都存存在的数数值=IFF(ROOW()SUUM(-x),INNDEXX(A:A,SSMALLL(IIF(xx,ROOW($A$11:$AA$9),RROW()自动为性性别编号号的问题题有一个编编码,55位，第1位位,1为为男,22为女，后面44位,代代表他的的编号,从00001-99999，如何达达到下表表：性别编码男 1100001男 1100002女 2200001男 1100003女 2200002男的也是是从00001-99999女的也是是从00001-99999如果你是是已经输输入了其其它信息息，仅仅仅为快速速输入编编码的话话。用筛筛选可以以实

58、现吧吧。先以以“男”为关键键字进行行排序，然后在在第一个个男的编编码输入入100001，下拉复复制到最最后一单单即可。同理再再以“女女”排序序。完成成目标。用公式：=IFF(A22=,TEEXT(COUUNTIIF(AA$2:A2,A2),1100000),TEEXT(COUUNTIIF(AA$2:A2,A2),2200000)向下下拖【文本与与页面设设置】EXCEEL中如如何删除除*号在录入帐帐号是录录入了*号，如如何删除除。可以用函函数 SSUBSSTITTUTEE(a11,*,)查找*，替换换为空。将字符串串中的星星号“*”替换换为其它它字符在查找栏栏输入*替换为“-”即即可。去空格函函

59、数如何删去去单元格格中的空空格，如如姓名前前，中，后的空空格，即即单元格格中是两两个字的的人名中中间有一一个空格格，想删删去有何何方法。如：中中国国，改为为：中国国。1、用公公式：=SUBBSTIITUTTE(AA2, ,)注：第第一对双双引号中中有一空空格。而而第二个个“”中中是无空空格的。2、利用用查找替换，一次性性全部解解决。“编辑”“替换”（或CCtrll+H），在“查找”栏内输输入一空空格，“替换”什么也也不输入入（空白白）。然然后“全部替替换”即可。3、有一一个专门门删除空空格的函函数: TRIIM()在EXCCEL编编辑栏里里，不管管输中文文还是英英文只能能输一个个字节的的空格，

60、但如果果字与字字中间是是两个字字节的空空格，那那么TRRIM（）就不不起作用用了，它它就不认认为是一一个空格格，而是是一个汉汉字，怎怎么去“TRIIM”也没用用。如：单元格格A1中中有“中中心心是是”，如如果用TTRIMM则变成成“中心是是”，想将空空格全去去掉，只只能用SSUBSSTITTUDEE（）函函数，多多少空格格都能去去掉。如何去掉掉字符和和单元格格里的空空格8900007995011890000779500119000078880111900007788002=SUBBSTIITUTTE(BB2,)怎样快速速去除表表中不同同行和列列的空格格编辑-定定位-定定位条件件-空值值,可选选