EXCEL函數(shù)公式工程計算運用大全

1、excel常用函數(shù)公式及技巧搜集（常用的）【身份證信息？提取】從身份證號碼中提取出生年月日=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)【年齡和工工齡計算算】根據(jù)出生年年月計算算年齡DATEEDIFF(A11,TOODAYY(),y)DATEEDIFF(A11,TOOD

9、AYY(),y)&周歲DATEEDIFF(A11,NOOW(),yy)根據(jù)出生年年月推算生肖中國人有112生肖肖，屬什什么可以以推算出出來。即即用誕生生年份除除以122，再用用除不盡盡的余數(shù)數(shù)對照如如下：00猴，11雞，22狗，33豬，44鼠，55牛，66虎，77兔，88龍，99蛇，110馬，111羊例如如：XXXX出生生于19921年年，即用用19221年除除以122，商得得數(shù)為1160，余余數(shù)為11，對照照上面得得知余數(shù)數(shù)1對應應生肖是是雞，XXXX就就屬雞。=MID(猴雞雞狗豬鼠鼠?；⑼猛谬埳唏R馬羊,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)求求兩日期期間的年年數(shù)=DATEEDIFF(C66,C88,yym)求兩日日期間除除去整年年數(shù)剩余余的月數(shù)數(shù)=DATEEDIFF(C66,C88,mm)求求兩日期期間的總總月數(shù)如果只需要要算出周周年的話話，可以以用=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 函數(shù)數(shù)，也可可以不用用 DAATEDDIF 函數(shù),如 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）這樣樣的日期期格式才才能實現(xiàn)現(xiàn)！你不妨把“.”替換換成“-”，不不就行了了嗎，再再說后者者是日期期的一種種標準格格式，=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)如何對日期期進行上上、中、下下旬區(qū)分分=LOOKKUP(DAYY(A11),0,111,221,331,上上旬,中旬旬,下旬,下旬旬)如何獲取一一個月的的最大天天數(shù)=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) 該公公式不用用設置數(shù)數(shù)據(jù)有效效性,但要設設置儲存存格格式式。也可以用下下列兩方方法：1、先轉換換成文本本, 然后再再用字符符處理函函數(shù)。2、數(shù)據(jù)據(jù)-分列 日日期-MDDY將“20005年99月”轉換成成“20005099”格式先用公式：=teext(a1,yyyyymm

19、m)+0 然然后將單單元格格格式為常常規(guī)。將“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、選中這這列，用用數(shù)據(jù)中中的分列列。然后后三、單元格格格式數(shù)字自定義義，類型型下面輸輸入：#.#將文本“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月;)也可以這樣樣處理：選中單單元格，設設置單元元格公式式數(shù)字字自定定義，將將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”只有年年月的格格式y(tǒng)earr(A11)&ttextt(moonthh(A11),00)要將“999.088.155” 格式式轉換成成“19999.008.115”如何做做選中列，數(shù)數(shù)據(jù)菜單單中選分分列，分分列過程程中“格格式”選選“日期期YMDD”，結結束。要保持20005/8/66格式當輸入20005/8/66后系統(tǒng)統(tǒng)自動變變成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、可以用用數(shù)組公公式將中中文日期期轉化為為日期系系列數(shù)=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列是月月份數(shù)為為8，BB列是日日期數(shù)為為18，如如何在CC列顯示示“8月118日”=A1&月&B11&日日反之，要將將C列的的“8月118日” 直接分分別到DD、E列列，顯示示月份和和日期，月數(shù)份=LLEFTT(C55,FIIND(月,CC5)-1)日期數(shù)=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項匯匯總方式式的函數(shù)數(shù)SUBBTOTTAL=SUBTTOTAAL(99,$BB$2:B2)在數(shù)據(jù)篩選選求和上上有意想想不到的的功能，111項功功能為：1、求求平均數(shù)數(shù)，2、求求計數(shù)，33、求計計數(shù)值（自自動篩選選序列）4、求最大值，5、求最小值，6、求乘積，7、求總體標準偏差，8、求標準偏差、9、求和，10、求方差，11、求總體方差。自動排序=SUBTTOTAAL(33,

29、$BB$2:B2)*1=IF(AA2A1,1,NN(C11)+11)按奇偶數(shù)排排序我想請教怎怎樣按奇奇數(shù)順序序然后再再按偶數(shù)數(shù)順序排排序=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欄中的的數(shù)字大大小排序序？=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)重復數(shù)據(jù)得得到唯一一的排位位序列想得到數(shù)據(jù)據(jù)的出現(xiàn)現(xiàn)總數(shù)嗎嗎（11,2,2,33,4,4,55 數(shù)數(shù)據(jù)的出出現(xiàn)總數(shù)數(shù)為5）？ 解答：不需需要插列列,不需要要很多的的函數(shù)就就行了. =RRANKK(B33,B$3:BB$122)+CCOUNNTIFF(B$3:BB3,BB3)-1按字符數(shù)量量排序制作歌曲清清單時，習習慣按字字符數(shù)量量來排列列分類，但但是EXXCELL并不能能直接按按字數(shù)排排序。需需要先計計算出每每首歌曲曲的字數(shù)數(shù)，然后后再進行行排序。 如

31、如A、BB列分別別為“歌手”和“歌名”，在CC1輸入入“字數(shù)”，在CC2輸入入公式：LEN(B2) 下拖，單單擊C22，單擊擊工具欄欄上的“升序排排列”即可，刪刪除C列列。排序字母與與數(shù)字的的混合內內容日常使用中中，表格格經常會會有包含含字母和和數(shù)字混混合的數(shù)數(shù)據(jù)，對對此類數(shù)數(shù)據(jù)排序序時，通通常是先先比較字字母的大大小，再再比較數(shù)數(shù)字的大大小，但但EXCCEL是是按照對對字符進進行逐位位比較來來排序的的，如下下表：AA7排在在第5位位，而不不是第11位。排排序結果果無法令令人滿意意。A1A1222A293A3174A435A76B207B38C1449C510C33AB1A7A0072A29A

32、0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EEXCEEL改變變排序的的規(guī)則，需需要將數(shù)數(shù)據(jù)做一一些改變變。在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;其實不是數(shù)數(shù)據(jù)排序序，應該該是數(shù)據(jù)據(jù)填充。輸入公式=LEFFT(EE3,44)+33&-&RRIGHHT(EE3,44)+33 即可可。怎樣才能讓讓數(shù)列自自動加數(shù)數(shù)怎樣做才能能讓數(shù)列列自動加加數(shù)A A000011B B000011A A000022C C000011A A000033B B000022C C000022公式為=AA1&0000&CCOUNNTIFF(A$1:AA1,AA1)向向下拖=TEXTT(COOUNTTIF(A$11:A11,A11),!&A1&

34、00000)否則則數(shù)字超超過9就就錯誤了了。一個排序問問題一個電子表表格，格格式是1101、1102. 9999,1001011、1001022. 9999011,100101101,102202001. 9999001011,請問問如何將將它排列列成1001,1101001,1101001011,1002,1102001,1102001011,. 9999,999901,999901001 的的形式。我在數(shù)字前前加了個個字母,比如d&數(shù)字字,然然后用排排序就可可以把它它們按你你的需求求排列了了.最后后再把字字母dd去掉掉。數(shù)字的自動動排序,插入后后不變?1 趙一一 總經理理2 趙二二 副經理

35、理3 趙三三 副經理理4 趙四四 技術員員5 趙五五6 趙六六 員工如上的一個個表,如如何實現(xiàn)現(xiàn)當我把把趙六這這一整行行(第66行)插插入到上上面的表表中時,A列的的序列號號不變?最后的的效果如如下:1 趙一一 總經理理2 趙二二 副經理理3 趙六六 員工 4 趙三三 副經經理5 趙四四 技術員員6 趙五五A1單元格格輸入公公式 =roww(),往下拉拉,然后后再插入入。=SUBTTOTAAL(33,$BB$2:$B22)在A1中輸輸入公式式：“=if(b1=,couuntaa($bb$1:b1)”后下下拉復制制至A列列各行即即可（“”不不必輸入入）根據(jù)規(guī)律的的重復的的姓名列列產生自自動序號號

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)排名的函數(shù)數(shù)用排名函數(shù)數(shù)來對成成績進行行排名，用起來來非常地地方便。=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)求名次排名名統(tǒng)計成績時

38、時遇到一一個分別別求班級級和年級級總分名名次排名名的問題題，不曉曉得應該該運用什什么公式式來實現(xiàn)現(xiàn)。班級名次：=SUMPPRODDUCTT(BBJ=AA2)*(ZFFE22)+1年級名次：=RANKK(E22,ZFF) 公式式下拖。排名次根據(jù)總分值值大小，只只將姓名名排序后后, 降序序結果=INDEEX(AA$2:A$66,RAANK(D2,D$22:D$6)根據(jù)總分值值大小，只只將姓名名排序后后, 升序序=INDEEX(AA$2:A$66,RAANK(D2,D$22:D$6,11)根據(jù)分數(shù)進進行普通通排名=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)對于普通排排名分數(shù)數(shù)相同時時，按順順序進行行不重復復排名=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)依分數(shù)比高高低名次次成績排排名=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),)根據(jù)雙列成成績進行行共同排排名=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成績排名序號姓名語文數(shù)學英語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)能否用一個個公式直直接找出出所用考考生中語語文成績績中按與與考人數(shù)數(shù)的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)數(shù)據(jù)排名（隔隔幾行排排名）=IF(AA2=,RRANKK(A22,$AA$2:$A$11,0)如果隔幾行行排名，如如下表，第第五行、第第九行和和第

55、十二二行不參參與排名名。單位數(shù)據(jù)排名A18A57A66小計12B84B93B75小計24C181C112小計29=IF(AA2=小計,RANNK(BB2,(B$22:B$4,BB$6:B$88,B$10:B$111) 下拉根據(jù)分數(shù)進進行倒排排名=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)如何實現(xiàn)快快速定位位（篩選選出不重重復值）=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()&（數(shù)組組公式）如何請在NN列中列列出A11:L99中每列列都存在在的數(shù)值值=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如果你是已已經輸入入了其它它信息，僅僅僅為快快速輸入入編碼的的話。用用篩選可可以實現(xiàn)現(xiàn)吧。先先以“男男”為關關鍵字進進行排序序，然后后在第一一個男的的編碼輸輸入1000011，下拉拉復制到到最后一一單即可可。同理理再以“女女”排序序。完成成目標。

58、用公式：=IF(A2=,TEXXT(CCOUNNTIFF(A$2:AA2,AA2),1000000),TEXXT(CCOUNNTIFF(A$2:AA2,AA2),2000000)向下拖拖【文本與頁頁面設置置】EXCELL中如何何刪除*號在錄入帳號號是錄入入了*號號，如何何刪除?？梢杂煤瘮?shù)數(shù) SUUBSTTITUUTE(a1,*,)查找*，替替換為空空。將字符串中中的星號號“*”替替換為其其它字符符在查找欄輸輸入*替換為“-”即可可。去空格函數(shù)數(shù)如何刪去單單元格中中的空格格，如姓姓名前，中中，后的的空格，即即單元格格中是兩兩個字的的人名中中間有一一個空格格，想刪刪去有何何方法。如：中國，改為：中

59、國。1、用公式式：=SSUBSSTITTUTEE(A22, ,) 注：第一對對雙引號號中有一一空格。而第二個“”中是無空格的。2、利用查查找替替換，一一次性全全部解決決?！熬庉嫛薄疤鎿Q”（或CCtrll+H），在在“查找”欄內輸輸入一空空格，“替替換”什什么也不不輸入（空空白）。然后“全部替換”即可。3、有一個個專門刪刪除空格格的函數(shù)數(shù): TTRIMM()在EXCEEL編輯輯欄里，不不管輸中中文還是是英文只只能輸一一個字節(jié)節(jié)的空格格，但如如果字與與字中間間是兩個個字節(jié)的的空格，那那么TRRIM（）就就不起作作用了，它它就不認認為是一一個空格格，而是是一個漢漢字，怎怎么去“TRIIM”也沒用用。

60、如：單元格格A1中中有“中中心心是是”，如如果用TTRIMM則變成成“中心是是”， 想將空空格全去去掉，只只能用SSUBSSTITTUDEE（）函函數(shù)，多多少空格格都能去去掉。如何去掉字字符和單單元格里里的空格格890000795501 8990000795501190000788801 1990000788802=SUBSSTITTUTEE(B22,)怎樣快速去去除表中中不同行行和列的的空格編輯-定位位-定位位條件-空值,可選中中所有空空單元格格, 再再刪除。如何禁止輸輸入空格格在Exceel中如如何通過過編輯“有效數(shù)數(shù)據(jù)”來禁止止錄入空空格？煩煩請大俠俠們費心心解答。解答：有效效性公式式。