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

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

2、MIID(AA2,99,2)&-&MMID(A2,11,2),MIDD(A22,7,4)&-&MIID(AA2,111,22)&-&MIDD(A22,133,2) 显示格式为为yyyyy-mmm-ddd。（如果要求为“1995/03/29”格式的话，将”-” 换成”/”即可）=IF(DD4=,IIF(LLEN(D4)=155,TEEXT(119&MIDD(D44,7,6),000000年00月00日),IF(LENN(D44)=118,TTEXTT(MIID(DD4,77,8),000000年00月00日)显示格式为为yyyyy年mmm月ddd日。（如如果将公公式中“00000年00月00日”

3、改成“00000-00-00”,则显显示格式式为yyyyy-mm-dd）=IF(LLEN(A1:A2)=188,MIID(AA1:AA2,77,8),119&MIDD(A11:A22,7,6)显示格式为为yyyyymmmdd。=TEXTT(LLEN(A1)=155)*119&MMID(A1,7,66+(LLEN(A1)=188)*22),#-000-000)+0=IF(LLEN(A2)=188,MIID(AA2,77,4)&-MMID(A2,11,2),19&MIDD(A22,7,2)&-MIID(AA2,99,2)=MID(A1,7,44)&年&MIDD(A11,111,2)&月月&MMID

4、(A1,13,2)&日=IF(AA1,TEXXT(LENN(A11)=115)*19&MIDD(A11,7,6+(LENN(A11)=118)*2),#-00-00)从身份证号号码中提提取出性性别=IF(MMOD(MIDD(A1,115,33),22),男,女) （最最简单公公式）=IF(MMOD(RIGGHT(LEFFT(AA1,117),2),男男,女)=IF(AA2” ”,IFF(MOOD(RRIGHHT(LLEFTT(A22,177),2),”男”,”女”),)=IF(VVALUUE(LLEN(ROUUND(RIGGHT(A1,1)/2,22)=1,男,女女)从身份证号号码中进进行年龄

5、龄判断=IF(AA3”,DDATEEDIFF(TEEXT(LEEN(AA3)=15*19&MIDD(A33,7,6+(LENN(A33)=118*22),”#-000-000”),TTODAAY(),”Y”),)=DATEEDIFF（A1，TODDAY（），“YY”）（以上公式式会判断断是否已已过生日日而自动动增减一一岁）=YEARR(NOOW()-MMID(E2,IF(LENN(E22)=118,99,7),2)-19900=YEARR(TOODAYY()-IFF(LEEN(AA1)=15,199&MMID(A1,7,22),MMID(A1,7,44)=YEARR(TOODAYY()-VAA

6、LUEE(MIID(BB1,77,4)&岁=YEARR(TOODAYY()-IFF(MIID(BB1,118,11)=,CCONCCATEENATTE(19,MIID(BB1,77,2),MMID(B1,7,44)按身份证号号号码计算算至今天天年龄 =DATEEDIFF(TEEXT(LEEN(AA1)=15)*199&MIID(AA1,77,6+(LEEN(AA1)=18)*2),#-000-000),TODDAY(),y)以20066年10月31日为基准准日,按按身身份证计计算年龄龄(周岁)的公式式=DATEEDIFF(TEEXT(MIDD(A11,7,6+(LENN(A11)=118)*2

7、),#-00-00)*11,220066-100-311,y)按身份证号号分男女女年龄段段按身份证号号分男女女年龄段段，身份份证号在在K列，年年龄段在在J列（身身份证号号为188位）男性16周周岁以下下为1男性16周周岁（含含16周岁岁）以上上至500周岁为为 22男性50周周岁（含含50周岁岁）以上上至600周岁为为 33男性60周周岁（含含60周岁岁）以上上为 4女性16周周岁以下下为1女性16周周岁（含含16周岁岁）以上上至455周岁为为 22女性45周周岁（含含45周岁岁）以上上至555周岁为为 33女性55周周岁（含含55周岁岁）以上上为 4=MATCCH(DDATEEDIFF(DA

8、ATE(MIDD(K11,7,4),MIDD(K11,111,2),MIID(KK1,113,22),TODDAY(),y),00,166,500,600-0,00,5,5*ISEEVENN(MIID(KK1,117,11)=SUM(-(DATTEDIIF(MMID(K1,7,44)&/&MIDD(K11,111,2)&/&MMID(K1,13,2),TODDAY(),y)=0,116,445,555+0,0,55,5*MOOD(MMID(K1,17,1),2)【年龄和工工龄计算算】根据出生年年月计算算年龄DATEEDIFF(A11,TOODAYY(),y)DATEEDIFF(A11,TOOD

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

10、指定定日期的的周岁？=DATEEDIFF(起始始日期,结束日日期,Y)计算距离退退休年龄龄的公式式=IF(EE2=,IIF(EE2=V2,已经经退休,距距离退休休还有&DAATEDDIF(TODDAY(),DDATEE(YEEAR(U2)+(VV2),MONNTH(U2),DAAY(UU2),YY)&年&DAATEDDIF(TODDAY(),DDATEE(YEEAR(U2)+(VV2),MONNTH(U2),DAAY(UU2),YYM)&个个月&DATTEDIIF(TTODAAY(),DAATE(YEAAR(UU2)+(V22),MMONTTH(UU2),DAYY(U22),Mdd)&天)其中

11、E2为为年龄（可可用身份份证号码码的公式式生成）；V2为法定定退休年年龄（男男60，女女50）公公式为：=IFF(D22=,IFF(D22=男男,660,550)D2为男或或女（可可用身份份证号码码的公式式生成）；U2为出生年月日（可用身份证号码的公式生成）。求工齡=DATEEDIFF(B22,TOODAYY(),y) =DATEEDIFF(B22,TOODAYY(),ymm) =DATEEDIFF(B22,TOODAYY(),mdd) =DATEEDIFF(B22,TOODAYY(),y)&年&DDATEEDIFF(B22,TOODAYY(),ymm)&月&DDATEEDIFF(B22,TO

12、ODAYY(),mdd)&日 计算工龄=DATEEDIFF(C66,C88,yy)求求两日期期间的年年数=DATEEDIFF(C66,C88,yym)求两日日期间除除去整年年数剩余余的月数数=DATEEDIFF(C66,C88,mm)求求两日期期间的总总月数如果只需要要算出周周年的话话，可以以用=ddateediff(119788-8,220066-5,YY)年龄及工龄龄计算有出生年月月如何求求年龄？有工作时间间如何求求工龄？（求出出的结果果为多少少年另几几个月，如如：03303的的形式，即即3年零3个月）。a1是出生生年月或或工作时时间：=dateediff(a11,toodayy(),y)

13、=textt(daateddif(a1,todday(),y),000)&teext(dattediif(aa1,ttodaay(),mm),000)如 B22=119644-9-1 则则:=TEXTT(DAATEDDIF(B2,TODDAY(),y),000)&TEEXT(MODD(DAATEDDIF(B2,TODDAY(),m),122),00) 显显示 440099=TEXTT(DAATEDDIF(B2,TODDAY(),y),000年)&TTEXTT(MOOD(DDATEEDIFF(B22,TOODAYY(),m),112),000月)显示 40年年09月月如果你找不不到 DDATEE

14、DIFF 函数数，也可可以不用用 DAATEDDIF 函数,如 B22=119644-9-1 则则:=TEXTT(RIIGHTT(YEEAR(NOWW()-B2),2),000)&TEEXT(MODD(MOONTHH(NOOW()-B22)-11,122),00) 显显示 440099=TEXTT(RIIGHTT(YEEAR(NOWW()-B2),2)&年年&MMOD(MONNTH(NOWW()-B2)-1,12)&个个月,) 显显示 440年009个月月自动算出工工龄日期期格式为为(yyyyy.mm.dd)能否用：（yyyyyy.mmm.ddd）这种种格式来来计算出出工龄有有多长呢呢？ 以以

15、前用这这样一段段（ =TTEXTT(RIIGHTT(YEEAR(NOWW()-A1),2)&年年&MMOD(MONNTH(NOWW()-A1)-1,12)&个个月,) ）。但这种方法法只能用用：（yyyyyy-mmm-ddd）这样样的日期期格式才才能实现现！你不妨把“.”替换换成“-”，不不就行了了吗，再再说后者者是日期期的一种种标准格格式，=TEXTT(RIIGHTT(YEEAR(NOWW()-SUBBSTIITUTTE(AA1,.,-),2)&年&MMOD(MONNTH(NOWW()-SUBBSTIITUTTE(AA1,.,-)-1,112)&个月月,)【时间和日日期应用用】自动显示当当前

16、日期期公式=YEARR(NOOW() 当当前年=MONTTH(NNOW() 当前前月=DAY(NOOW() 当前日日如何在单元元格中自自动填入入当前日日期Ctrl+;如何判断某某日是否否星期天天=WEEKKDAYY(A22,2)=TEXTT(A11,aaaaaa)=MOD(A1,7)11)显示昨天的的日期每天需要单单元格内内显示昨昨天的日日期，但但双休日日除外。例如，今天天是7月月3号的的话，就就显示77月2号号，如果是是7月99号，就就显示77月6号号。=IF(TTEXTT(TOODAYY(),AAAA)=一一,TTODAAY()-3,IF(TEXXT(TTODAAY(),AAAA)=日,T

17、TODAAY()-2,TODDAY()-11)=IF(TTEXTT(TOODAYY(),AAAA)=一一,TTODAAY()-3,TODDAY()-11)关于取日期期怎么设个公公式使AA1在年年月日向向后推55年,变变成20011-7-115=DATEE(YEEAR(A1)+5,MONNTH(A1),DAAY(AA1)=EDATTE(AA1,112*55)如何对日期期进行上上、中、下下旬区分分=LOOKKUP(DAYY(A11),0,111,221,331,上上旬,中旬旬,下旬,下旬旬)如何获取一一个月的的最大天天数=DAYY(DAATE(20002,33,1)-1)或=DAAY(BB1-11

18、),B1为为20001-03-01日期格式转转换公式式将 “011/122/20005” 转换成成“20005001122”格式式RIGHHT(AA1,44)&MMID(A1,4,22)&LLEFTT(A11,2)YEARR($AA2)&TEXXT(MMONTTH($A2),000)&TEEXT(DAYY($AA2),000) 该公公式不用用设置数数据有效效性,但要设设置储存存格格式式。也可以用下下列两方方法：1、先转换换成文本本, 然后再再用字符符处理函函数。2、数据据-分列 日日期-MDDY将“20005年99月”转换成成“20005099”格式先用公式：=teext(a1,yyyyymm

19、m)+0 然然后将单单元格格格式为常常规。将“20005-88-6”格式转转换为“2000508806”格式用公式：=TEXXT(AA1,YYYYYMMMDD)反之，将22005508066转为日期20005-8-66格式，可可用公式式：=DATEE(LEEFT(A1,4),MIDD(A11,5,2),RIGGHT(A1,2)另四种公式式：textt(a11,000000-000-000) 显示：20005-008-006-TEEXT(A1,#-00-00)，把把单元格格设置为为日期格格式 显显示：220055-8-6TEXTT(2000508806,00000-00-00)*11，单元元格设

20、置置日期型型 显示示：20005-8-66VALUUE(LLEFTT(A11,4)&-&MMID(A1,5,22)&-&RIGGHT(A1,2) 显示示：20005-8-66将“2000605501”转换为为“20006-005-001”格式=DATEE(LEEFT(A2,4),MIDD(A22,5,2),RIGGHT(A2,2)将“19993066”转换为为“19993-66”公式1：=LEFFT(AA3,44)&-&RIGGHT(A3,2)*1公式2：=-TTEXTT(A33*1000+11,#-000-000) 公式2需要设设置单元元格格式式，自定定义：ee-m公式3：=TEXXT(TT

21、EXTT(A33&001,00000-00-00),e-mm)把1984405转转换成119844.055 一、查找19884，替替换19884.二、如果全全部是年年月的话话，我个个人建议议， 1、采取辅辅助mmid(xxxxxxxx,1,4) & . & rrighht(xxxxxxxx,2) 2、选中这这列，用用数据中中的分列列。然后后三、单元格格格式数字自定义义，类型型下面输输入：#.#将文本“220044.011.022” 转转换为日日期格式式：20004-1-22=DATEE(MIID(AA1,11,4),MIID(AA1,66,2),MIID(AA1,99,2)将20055-8-6

22、转换换为20005年年8月6日格式=TEXTT(A11,yyyyyy年年mm月月dd日日;)象22怎样样转换成成22日日？转成成当年当当月的日日子公式为：=datte(yyearr(noow(),mmontth(nnow(),22)将“20006年55月”转换成成“20006年005月” 公式式为：=TEXXT(AA8,yyyyy年mm月;)也可以这样样处理：选中单单元格，设设置单元元格公式式数字字自定定义，将将yyyyy“年”m“月”改为：yyyyy“年”mm“月”，即可可。但这这方法打打印出来来显示为为：20006/5/将“19668年66月122日”转换为为“19668/66/122”格

23、式=YEARR(A11)&/&MONNTH(A1)&/&DDAY(A1) 显示：19668/66/122=TEXTT(A11,yyyyyy/mmm/ddd) 显示示：19968/06/12将“19668年66月122日”转换为为“19668-66-122”格式=YEARR(A11)&-&MMONTTH(AA1)&-&DDAY(A1) 显示示：19968-6-112=TEXTT(A11,yyyyyy-mmm-ddd) 显显示：119688-066-122将19933-122-288的日期期格式转转换成119933年122月=CONCCATEENATTE(YYEARR(A11),年,MONNTH(

24、A1),月月) =YEARR(A11)&年&MONNTH(A1)&月月也可以自定定义格式 $-4404e年年m月将“19778-55-2”包含年年月日的的日期转转换成“19778055”只有年年月的格格式yearr(A11)&ttextt(moonthh(A11),00)要将“999.088.155” 格式式转换成成“19999.008.115”如何做做选中列，数数据菜单单中选分分列，分分列过程程中“格格式”选选“日期期YMDD”，结结束。要保持20005/8/66格式当输入20005/8/66后系统统自动变变成20005-8-66，要保保持20005/8/66格式，可可以使用用强制文文本（前

25、前面加号）或或使用公公式=TTEXTT(A11,YYYYYY/MMM/DDD)。也也可以用用另一种种公式：=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),DDBNuum1yyyyy年mm月d日日

26、),0)该公式速度度较慢。2、改进后后的公式式，速度度要快的的多：=DATTE(118999+MAATCHH(LEEFT(A7,4),TEXXT(RROW($19900:$21100),DBNNum1100000),00),MMONTTH(MMATCCH(SSUBSSTITTUTEE(MIID(AA7,66,7),元元,一),TEEXT(ROWW($11:$3366),DBNNum11m月月d日),00),DAYY(MAATCHH(SUUBSTTITUUTE(MIDD(A77,6,7),元,一一),TEXXT(RROW($1:$3666),DDBNuum1m月dd日),0)要设置为119000

27、年的日日期格式式。日期格式转转换如A列是月月份数为为8，BB列是日日期数为为18，如如何在CC列显示示“8月118日”=A1&月&B11&日日反之，要将将C列的的“8月118日” 直接分分别到DD、E列列，显示示月份和和日期，月数份=LLEFTT(C55,FIIND(月,CC5)-1)日期数=MMID(C5,FINND(月,CC5)+1,FFINDD(日日,CC5)-FINND(月,CC5)-1)也可分别用用公式：=montth(-c55)=day(-cc5)日期格式转转换问题题输入的日期期是:004-007-226. 与另一一格的0011合并并,合并并出来是是:044072260001.=T

28、EXTT(A11,YYYMMMDD)&0011要想自动取取得“编制日日期：XXXXXX年X月月X日”可在该单元元格输入入 =编制日日期：&TEEXT(TODDAY(),yyyyy年m月d日)【排名及排排序筛选选】一个具有111项汇汇总方式式的函数数SUBBTOTTAL=SUBTTOTAAL(99,$BB$2:B2)在数据筛选选求和上上有意想想不到的的功能，111项功功能为：1、求求平均数数，2、求求计数，33、求计计数值（自自动筛选选序列）4、求最大值，5、求最小值，6、求乘积，7、求总体标准偏差，8、求标准偏差、9、求和，10、求方差，11、求总体方差。自动排序=SUBTTOTAAL(33,

29、$BB$2:B2)*1=IF(AA2A1,1,NN(C11)+11)按奇偶数排排序我想请教怎怎样按奇奇数顺序序然后再再按偶数数顺序排排序=IF(MMOD(A1,2),0,11)=IF(RROW()550,(ROWW()*2)-1000,(RROW()*22)-11)=ROW()*22-1-(ROOW()500)*999自动生成序序号比如在第二二列中输输入内容容回车后后第一列列的下一一行自动动生成序序列号。=IF(BB2,A2+1,)如何自动标标示A栏中的的数字大大小排序序？=RANKK(A11,$AA$1:$A$5)=RANKK(A11,A:A)如何设置自自动排序序A列自动变变成从小小到大排排

30、列B=SMAALL(A$22:A$28,ROWW(1:1)A列自动变变成从大大到小排排列B=LARRGE(A$22:A$28,ROWW(1:1)重复数据得得到唯一一的排位位序列想得到数据据的出现现总数吗吗（11,2,2,33,4,4,55 数数据的出出现总数数为5）？ 解答：不需需要插列列,不需要要很多的的函数就就行了. =RRANKK(B33,B$3:BB$122)+CCOUNNTIFF(B$3:BB3,BB3)-1按字符数量量排序制作歌曲清清单时，习习惯按字字符数量量来排列列分类，但但是EXXCELL并不能能直接按按字数排排序。需需要先计计算出每每首歌曲曲的字数数，然后后再进行行排序。 如

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

32、0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EEXCEEL改变变排序的的规则，需需要将数数据做一一些改变变。在B1中输输入公式式：LEEFT(A1,1)& RIGGHT(0000& RIGGHT(A1,LENN(A11)-11),33) 下拖单击B2，单单击工具具栏上的的“升序排排列”即可。随机排序如A、B列列分别为为“歌手”和“歌名”，在CC1输入入“次序”，在CC2输入入公式：RANDD（），下拖，单单击C22，单击击工具栏栏上的“降序排排列”即可对对歌曲清清单进行行随机排排序。排序的问题

33、题我想要这样样的排序序: 220011-20003 20004-220066 20007-220099 20010-20112;其实不是数数据排序序，应该该是数据据填充。输入公式=LEFFT(EE3,44)+33&-&RRIGHHT(EE3,44)+33 即可可。怎样才能让让数列自自动加数数怎样做才能能让数列列自动加加数A A000011B B000011A A000022C C000011A A000033B B000022C C000022公式为=AA1&0000&CCOUNNTIFF(A$1:AA1,AA1)向向下拖=TEXTT(COOUNTTIF(A$11:A11,A11),!&A1&

34、00000)否则则数字超超过9就就错误了了。一个排序问问题一个电子表表格，格格式是1101、1102. 9999,1001011、1001022. 9999011,100101101,102202001. 9999001011,请问问如何将将它排列列成1001,1101001,1101001011,1002,1102001,1102001011,. 9999,999901,999901001 的的形式。我在数字前前加了个个字母,比如d&数字字,然然后用排排序就可可以把它它们按你你的需求求排列了了.最后后再把字字母dd去掉掉。数字的自动动排序,插入后后不变?1 赵一一 总经理理2 赵二二 副经理

35、理3 赵三三 副经理理4 赵四四 技术员员5 赵五五6 赵六六 员工如上的一个个表,如如何实现现当我把把赵六这这一整行行(第66行)插插入到上上面的表表中时,A列的的序列号号不变?最后的的效果如如下:1 赵一一 总经理理2 赵二二 副经理理3 赵六六 员工 4 赵三三 副经经理5 赵四四 技术员员6 赵五五A1单元格格输入公公式 =roww(),往下拉拉,然后后再插入入。=SUBTTOTAAL(33,$BB$2:$B22)在A1中输输入公式式：“=if(b1=,couuntaa($bb$1:b1)”后下下拉复制制至A列列各行即即可（“”不不必输入入）根据规律的的重复的的姓名列列产生自自动序号号

36、姓名序号号张三1张三1李四2李四2赵五3赵五3赵五3王六4王六4=(A1A22)+NN(B11)=IF(AA3=AA2,BB2,BB2+11)姓名已排序序：B2=SUUMPRRODUUCT(1/CCOUNNTIFF(A$2:AA2,AA$2:A2)姓名未排序序：B2=IFF(COOUNTTIF(A$22:A22,A22)11,VLLOOKKUP(A2,A:BB,2,0),SUMMPROODUCCT(11/COOUNTTIF(A$22:A22,A$2:AA2)排名的函数数用排名函数数来对成成绩进行行排名，用起来来非常地地方便。=IF(IISERRR(RRANKK(M33,M:M),RAANK(M

37、3,M:MM)A列是成绩绩,B列是是排名=SUMPPRODDUCTT(AA$1:A$99A11)/CCOUNNTIFF(A$1:AA$9,A$11:A$9)+1自动排名公公式=RANKK(C33,$CC$3:$C$12)=RANKK(A22,$AA$2:$A$11,0)=RANKK(C22,$CC$2:$C$65)+COOUNTTIF($C$2:CC2,CC2)-1百分比排名名的公式式写法为为：=PERCCENTTRANNK($C$33:$CC$122,C33)平均分及总总分排名名=AVERRAGEE(B22:E22)=RANKK(F22,$FF$2:$F$655536)求名次排名名统计成绩时

38、时遇到一一个分别别求班级级和年级级总分名名次排名名的问题题，不晓晓得应该该运用什什么公式式来实现现。班级名次：=SUMPPRODDUCTT(BBJ=AA2)*(ZFFE22)+1年级名次：=RANKK(E22,ZFF) 公式式下拖。排名次根据总分值值大小，只只将姓名名排序后后, 降序序结果=INDEEX(AA$2:A$66,RAANK(D2,D$22:D$6)根据总分值值大小，只只将姓名名排序后后, 升序序=INDEEX(AA$2:A$66,RAANK(D2,D$22:D$6,11)根据分数进进行普通通排名=RANKK(A22,$AA$2:$A$12)=RANKK(A22,A$2:AA$122

39、)+CCOUNNTIFF(A$2:AA2,AA2)-1=SUMPPRODDUCTT(1*($EE$3:$E$12=E33)=RANKK(K33,$KK$3:$K$26)=RANKK(A22,A$2:AA$122)=SUM(A$2:AA$122=AA2)/COUUNTIIF(AA$2:A$112,AA$2:A$112)=COUNNTIFF($KK$3:$K$26,&K33)+11=INDEEX($A$22:$AA$7,MATTCH(LARRGE($C$2:$C$77,ROOW(AA1),$CC$2:$C$7,00),11)=SUMPPRODDUCTT($A$22:$AA$122A22)/CCOU

40、NNTIFF($AA$2:$A$12,$A$2:$A$112&)+1=RANKK(D22,OFFFSEET($A$11,MAATCHH($AA2,$A:$A,00)-11,3,COUUNTIIF($A:$A,$A2),1)对于普通排排名分数数相同时时，按顺顺序进行行不重复复排名=RANKK(K332,$K$332:$K$555)+COUUNTIIF($K$332:$K322,K332)-1=COUNNTIFF($KK$322:K332,KK32)-1+COUUNTIIF($K$33:$KK$266,&KK32)+1=SUMPPRODDUCTT(1*($E$33:$EE$122+ROOW($E$

41、33:$EE$122)/1100=($E3+ROWW(E33)/1100)=RANKK(E33,$EE$3:$E$12)+COOUNTTIF($E$3:EE3,EE3)-1=SUMPPRODDUCTT(1*($E$33:$EE$122+$BB$3:$B$12/1000)=(E33+B33/1000)依分数比高高低名次次成绩排排名=RANKK($EE3,$E$33:$EE$222) 內建方方式排名名=SUMPPRODDUCTT(1*($EE$3:$E$12=E33) 一一般方式式排名=RANNK(EE3,$E$33:$EE$222)+SSUM(IF($E$3:$E$222EE3,11/COOUN

42、TTIF($E$3:$E$222,$E$33:$EE$222),00)-COUUNTIIF($E$33:$EE$222,&EE3) 一般般方式排排名=RANKK(E33,$EE$3:$E$12)+COOUNTTIF($E$3:EE3,EE3)-1不重重复排名名=SUMPPRODDUCTT(1*($E$33:$EE$122+ROOW($E$33:$EE$122)/1100=($E3+ROWW(E33)/1100)=SUMPPRODDUCTT(1*($E$33:$EE$122+$BB$3:$B$12/1000)=(E33+B33/1000) 不不重复排排名=SUMPPRODDUCTT(1*($E$

43、33:$EE$122+$BB$3:$B$12/1000+$CC$3:$C$12/100000)=(E3+B3/1000+C33/1000000) 不重重复排名名=RANKK($EE3,$E$33:$EE$222,1) 倒排序序美国式排名名=RANKK(K2247,$K$2477:$KK$2770) =RANKK(B11,$BB1:$H1)中国式排名名=RANKK(B22,$BB$2:$B$21,0)=RANKK(B11,$BB1:$H1)+COOUNTTIF($B$1:BB1,BB1)-1=SUM(IF($A$1:$E$11=AA1,11/COOUNTTIF($A$1:$E$11,$AA$1:

44、$E$1),)=SUMPPRODDUCTT($B$22:$BB$211=BB2)/COUUNTIIF($B$22:B$21,B$22:B$21)=SUMPPRODDUCTT(BB$3:B$221BB3)*(1/COUUNTIIF($B$33:$BB$211,$BB$3:$B$21)+1 （升序序）=SUMPPRODDUCTT(BB$3:B$221B2)+1=SUMM(IFF($BB$3:$B$21=B33,1/(COOUNTTIF($B$3:BB$211,B$3:BB$211)+11（升序序）=SUMM(IFF($BB$3:$B$21B2,1/CCOUNNTIFF($BB$2:B$221,BB

45、$2:B$221)+11=SUMM(IFF($AA$1:$E$1=A1,1/CCOUNNTIFF($AA$1:$E$1,$A$11:$EE$1),)=SUMM($B$22:$BB$211B22)*(MATTCH($B$2:BB$211,B$2:BB$211,)=ROWW($11:$220)+11=SUMM(IFF($BB$1:$H$1=E22)/CCOUNNTIFF($EE$2:$E$21,$E$2:$E$221),第第DBBNUMM1GG/通用用格式名名)排序后排名名=SUMM(IFF($BB$2:$B$15=B22,1/COUUNTIIF($B$22:$BB$155,$BB$2:$B$15

46、)=SUMPPRODDUCTT(BB$2:B$115=B2)/COOUNTTIF(B$22:B$15,B$22:B$15)位次排名=IF($B22:$OO2=0,RRANKK($BB2:$O2,$B22:$OO2,00),)根据双列成成绩进行行共同排排名=RANKK(C3345,($CC$3445:$C$3356,$H$3455:$HH$3556)在双列间排排名=RANKK(B22,($B$22:$BB$266,$EE$2:$E$16)等次排名由大到小排排名=RANKK(B33,$BB$3:$B$12)=SUMPPRODDUCTT($A$116:$A$225=AA16)*($B$116:$B$

47、225BB16)+11由小到大排排名=RANKK(B33,$BB$3:$B$12,1)=SUMPPRODDUCTT($A$116:$A$225=AA16)*($B$116:$B$225B116-RROW(B166)/1100000)+1由小到大=RANKK(B33,$BB$3:$B$12,1)+COUUNTIIF($B$33:B33,B33)-11=SUMPPRODDUCTT($A$116:$A$225=AA16)*($B$116:$B$225+RROW($B$16:$B$25)/1000000B116+RROW(B166)/1100000)+1由小到大=COUNNT($B$33:$BB$12

48、2)-RRANKK(B33,$BB$3:$B$12)-COOUNTTIF($B$3:BB3,BB3)+2=SUMPPRODDUCTT($A$116:$A$225=AA16)*($B$116:$B$225-RROW($B$16:$B$25)/1000000BB3)*(1/COUUNTIIF(BB$3:B$112,BB$3:B$112)+11=SUMPPRODDUCTT($A$116:$A$225=AA16)*($B$116:$B$225BB16)/COOUNTTIF($K$16:$K$25,$K$16:$K$25)+11由小到大=SUMPPRODDUCTT(BB$3:B$112BB3)*(1/C

49、OUUNTIIF(BB$3:B$112,BB$3:B$112)+11=SUMPPRODDUCTT($A$116:$A$225=AA16)*($B$116:$B$225B3)+11=COUNNTIFF($BB$3:$B$21,&B33)+11=SUMM(IFF($BB$3:$B$21B3,1,00)+1=19-FFREQQUENNCY($B$3:$B$221,BB3)+1=SUMPPRODDUCTT($B$22:$BB$200=BB2)/COUUNTIIF($B$22:$BB$200,$BB$2:$B$20)无并列排名名=RANKK(B33,$BB$3:$B$21)+COOUNTTIF($B$3

50、:$B3,B3)-1=SUMPPRODDUCTT(BB3-RROW()/110000B3-ROWW()/10000,11,0)+11有并列分段段排名=SUMPPRODDUCTT($A$33:$AA$211=A33)*($C$3:$C$221CC3)+1=19-FFREQQUENNCY($AA$3:$A$21=A3)*($C$33:$CC$211),CC3)+1=MATTCH(C3,LARRGE(OFFFSETT($CC$2,IF($A$3:$A$221=AA3,RROW($A$3:$A$221)-2),),RROW(INDDIREECT(1:&CCOUNNTIFF($AA$3:$A$21,A3

51、),0)=MATTCH(C3,LARRGE(IF($A$3:$A$221=AA3,$C$33:$CC$211),RROW(INDDIREECT(1:&CCOUNNTIFF($AA$3:$A$21,A3),0)=SUMMPROODUCCT($A$3:$A$221=AA3)*($CC$3:$C$21C3)/COOUNTTIF($N$3:$N$221,$N$33:$NN$211)+1（需辅辅助列）无并列分段段排名=SUMMPROODUCCT($A$3:$A$221=AA3)*($CC$3:$C$21-ROWW($CC$3:$C$21)/1000000C33-ROOW(CC3)/100000)+11=

52、19-FFREQQUENNCY($AA$3:$A$21=A3)*($C$33:$CC$211-ROOW($C$33:$CC$211)/110000),CC3-RROW()/110000)+11成绩排名序号姓名语文数学英语1杨增海1351361462郭爱玲1381371413华志锋1341381414袁文飞134143135能否用一个个公式直直接找出出所用考考生中语语文成绩绩中第1100名名的成绩绩是多少少?=LARGGE(CC2:CC4177,1000)=PERCCENTTILEE(C22:C4417,(4116-1100)/4116)=PERCCENTTILEE($CC$2:$C$4177,

53、(CCOUNNTA($C$2:$C$4417)-1000)/COUUNTAA($CC$2:$C$4177)能否用一个个公式直直接找出出所用考考生中语语文成绩绩中按与与考人数数的355%切线线中位于于第355%的成成绩是多多少?升冪=SMALLL(CC2:CC4177,4116*00.355)=PERCCENTTILEE($CC$2:$C$4177,0.35) 降降冪=LARGGE(CC2:CC4177,4116*00.355)=PERCCENTTILEE($CC$2:$C$4177,1-0.335)如何排名1、对英语语进行排排名，缺缺考不计计算在内内。2、对对英语进进行排名名，缺考考计算在在内

54、。英语英语排名429623721485485721544429缺考缺考458467缺考不计算算在内b2=IFF(A22=缺缺考,RANNK(AA2,$A$22:$AA$133) 然后按按照B列列排序缺考计算在在内=IF(AA2=缺考,COUUNTIIF($A$22:$AA$133,=0)+11,RAANK(A2,$A$2:$A$113)=IF(AA2=缺考,COUUNT($A$2:$A$113)+1,RRANKK(A22,$AA$2:$A$13,0)数据排名（隔隔几行排排名）=IF(AA2=,RRANKK(A22,$AA$2:$A$11,0)如果隔几行行排名，如如下表，第第五行、第第九行和和第

55、十二二行不参参与排名名。单位数据排名A18A57A66小计12B84B93B75小计24C181C112小计29=IF(AA2=小计,RANNK(BB2,(B$22:B$4,BB$6:B$88,B$10:B$111) 下拉根据分数进进行倒排排名=RANKK($EE3,$E$33:$EE$222,1)=RANKK(K660,$K$660:$K$883,11)=COUNNTIFF($KK$600:$KK$833,0,OK,)名次筛选名次=RAANK(K5,K$22:K$4355)班名次=RRANKK(K66,OFFFSEET(KK$2,MATTCH(A6,A:AA,)-2,COUUNTIIF(AA

56、$1:A$5500,A6)如何实现快快速定位位（筛选选出不重重复值）=IF(CCOUNNTIFF($AA$2:A2,A2)=1,A2,)=IF(COUUNTIIF($A$22:A22,A22)=11)=TTRUEE,A22,)=INDEEX(AA:A,SMAALL(IF(MATTCH(A$11:A$20,A$11:A$20,)=RROW($1:$200),RROW(A$11:A$20),6555366),RROW()&（数组组公式）如何请在NN列中列列出A11:L99中每列列都存在在的数值值=IF(ROWW()SUMM(-x),INDDEX(A:AA,SMMALLL(IFF(x,ROWW($A

57、A$1:$A$9),ROOW()自动为性别别编号的的问题有一个编码码,5位位，第1位位,1为为男,22为女，后面44位,代代表他的的编号,从00001-99999，如何达达到下表表：性别编编码男 1000011男 1000022女 2000011男 1000033女 2000022男的也是从从00001-999999女的也是从从00001-999999如果你是已已经输入入了其它它信息，仅仅仅为快快速输入入编码的的话。用用筛选可可以实现现吧。先先以“男男”为关关键字进进行排序序，然后后在第一一个男的的编码输输入1000011，下拉拉复制到到最后一一单即可可。同理理再以“女女”排序序。完成成目标。

58、用公式：=IF(A2=,TEXXT(CCOUNNTIFF(A$2:AA2,AA2),1000000),TEXXT(CCOUNNTIFF(A$2:AA2,AA2),2000000)向下拖拖【文本与页页面设置置】EXCELL中如何何删除*号在录入帐号号是录入入了*号号，如何何删除。可以用函数数 SUUBSTTITUUTE(a1,*,)查找*，替替换为空空。将字符串中中的星号号“*”替替换为其其它字符符在查找栏输输入*替换为“-”即可可。去空格函数数如何删去单单元格中中的空格格，如姓姓名前，中中，后的的空格，即即单元格格中是两两个字的的人名中中间有一一个空格格，想删删去有何何方法。如：中国，改为：中

59、国。1、用公式式：=SSUBSSTITTUTEE(A22, ,) 注：第一对对双引号号中有一一空格。而第二个“”中是无空格的。2、利用查查找替替换，一一次性全全部解决决。“编辑”“替换”（或CCtrll+H），在在“查找”栏内输输入一空空格，“替替换”什什么也不不输入（空空白）。然后“全部替换”即可。3、有一个个专门删删除空格格的函数数: TTRIMM()在EXCEEL编辑辑栏里，不不管输中中文还是是英文只只能输一一个字节节的空格格，但如如果字与与字中间间是两个个字节的的空格，那那么TRRIM（）就就不起作作用了，它它就不认认为是一一个空格格，而是是一个汉汉字，怎怎么去“TRIIM”也没用用。

60、如：单元格格A1中中有“中中心心是是”，如如果用TTRIMM则变成成“中心是是”， 想将空空格全去去掉，只只能用SSUBSSTITTUDEE（）函函数，多多少空格格都能去去掉。如何去掉字字符和单单元格里里的空格格890000795501 8990000795501190000788801 1990000788802=SUBSSTITTUTEE(B22,)怎样快速去去除表中中不同行行和列的的空格编辑-定位位-定位位条件-空值,可选中中所有空空单元格格, 再再删除。如何禁止输输入空格格在Exceel中如如何通过过编辑“有效数数据”来禁止止录入空空格？烦烦请大侠侠们费心心解答。解答：有效效性公式式。