Excel函數(shù)排序與篩選教程
Execl本身具有很方便的排序與篩選功能，下拉“數(shù)據(jù)”菜單即可選擇排序或篩選對(duì)數(shù)據(jù)清單進(jìn)行排序或篩選。但也有不足，首先無(wú)論排序或篩選都改變 了原清單的原貌，特別是清單的數(shù)據(jù)從其它工作表鏈接來(lái)而源數(shù)據(jù)發(fā)生變化時(shí)，或清單錄入新記錄時(shí)必須從新進(jìn)行排序或篩選。其次還有局限，例如排序只能最多對(duì) 三個(gè)關(guān)鍵字(三列數(shù)據(jù))排序，篩選對(duì)同一列數(shù)據(jù)可用“與”、或“或”條件篩選，但對(duì)不同列數(shù)據(jù)只能用“與”條件篩選。
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)?，F(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A
Execl本身具有很方便的排序與篩選功能，下拉“數(shù)據(jù)”菜單即可選擇排序或篩選對(duì)數(shù)據(jù)清單進(jìn)行排序或篩選。但也有不足，首先無(wú)論排序或篩選都改變 了原清單的原貌，特別是清單的數(shù)據(jù)從其它工作表鏈接來(lái)而源數(shù)據(jù)發(fā)生變化時(shí)，或清單錄入新記錄時(shí)必須從新進(jìn)行排序或篩選。其次還有局限，例如排序只能最多對(duì) 三個(gè)關(guān)鍵字(三列數(shù)據(jù))排序，篩選對(duì)同一列數(shù)據(jù)可用“與”、或“或”條件篩選，但對(duì)不同列數(shù)據(jù)只能用“與”條件篩選。
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)?，F(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A
Execl本身具有很方便的排序與篩選功能，下拉“數(shù)據(jù)”菜單即可選擇排序或篩選對(duì)數(shù)據(jù)清單進(jìn)行排序或篩選。但也有不足，首先無(wú)論排序或篩選都改變 了原清單的原貌，特別是清單的數(shù)據(jù)從其它工作表鏈接來(lái)而源數(shù)據(jù)發(fā)生變化時(shí)，或清單錄入新記錄時(shí)必須從新進(jìn)行排序或篩選。其次還有局限，例如排序只能最多對(duì) 三個(gè)關(guān)鍵字(三列數(shù)據(jù))排序，篩選對(duì)同一列數(shù)據(jù)可用“與”、或“或”條件篩選，但對(duì)不同列數(shù)據(jù)只能用“與”條件篩選。
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)。現(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A
Execl本身具有很方便的排序與篩選功能，下拉“數(shù)據(jù)”菜單即可選擇排序或篩選對(duì)數(shù)據(jù)清單進(jìn)行排序或篩選。但也有不足，首先無(wú)論排序或篩選都改變 了原清單的原貌，特別是清單的數(shù)據(jù)從其它工作表鏈接來(lái)而源數(shù)據(jù)發(fā)生變化時(shí)，或清單錄入新記錄時(shí)必須從新進(jìn)行排序或篩選。其次還有局限，例如排序只能最多對(duì) 三個(gè)關(guān)鍵字(三列數(shù)據(jù))排序，篩選對(duì)同一列數(shù)據(jù)可用“與”、或“或”條件篩選，但對(duì)不同列數(shù)據(jù)只能用“與”條件篩選。
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)。現(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A$2:$F$501，$H2， COLUMN(A$1))”INDEX函數(shù)是一個(gè)引用函數(shù)，即把$A$2:$F$501單元格列陣第$H2行第COLUMN(A$1)列的數(shù)據(jù)放入I2單 元格。然后把I2單元格的公式拖放到N2單元格，點(diǎn)擊N2單元格后向N2單元格右下方移動(dòng)鼠標(biāo)見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了I2到N501單元格的填充到此全部完成。
以上敘述看似繁雜實(shí)際非常簡(jiǎn)單，只要把A1至F1的表頭復(fù)制到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然后向下拖放，即使對(duì)EXCEL應(yīng)用不熟練的同志一分鍾內(nèi)便能完成。
對(duì)上述程序稍作變化還可得到更多用度。上面例子數(shù)據(jù)是從大到小排列的，如H列的函數(shù)中的SMALL改為L(zhǎng)ARGE，上面例子數(shù)據(jù)就從小到大排列了。如 H2單元格的公式改為“=IF(O1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(LARGE(G: G，ROW(A1))，G:G，0))”并把H2單元格的公式向下拖放。這樣在O1單元格輸入1上面例子數(shù)據(jù)是從大到小排列的，O1單元格輸入1以外的數(shù) 上面例子數(shù)據(jù)就從小到大排列了。
如在H列前插入若干列，如插入一列，則現(xiàn)在的H列輸入類(lèi)似G列的公式，例如 “=if(F2=0，10^100，d2)”，現(xiàn)在的I列的公式改為 “=IF(P1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(SMALL(H:H，ROW(A1))，H:H，0)))” 即在P單元格輸入1以外的值就實(shí)現(xiàn)了按獎(jiǎng)金大小排序.這樣只要通過(guò)改變P1(原來(lái)的O1單元格)單元格內(nèi)容的改變就能立即得到按不同要求的排序。
:$F1，$H2， COLUMN(A class="main">
:$F1，$H2， COLUMN(A class="main">
Excel函數(shù)排序與篩選教程
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)?，F(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A
Execl本身具有很方便的排序與篩選功能，下拉“數(shù)據(jù)”菜單即可選擇排序或篩選對(duì)數(shù)據(jù)清單進(jìn)行排序或篩選。但也有不足，首先無(wú)論排序或篩選都改變 了原清單的原貌，特別是清單的數(shù)據(jù)從其它工作表鏈接來(lái)而源數(shù)據(jù)發(fā)生變化時(shí)，或清單錄入新記錄時(shí)必須從新進(jìn)行排序或篩選。其次還有局限，例如排序只能最多對(duì) 三個(gè)關(guān)鍵字(三列數(shù)據(jù))排序，篩選對(duì)同一列數(shù)據(jù)可用“與”、或“或”條件篩選，但對(duì)不同列數(shù)據(jù)只能用“與”條件篩選。
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)?，F(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A$2:$F$501，$H2， COLUMN(A$1))”INDEX函數(shù)是一個(gè)引用函數(shù)，即把$A$2:$F$501單元格列陣第$H2行第COLUMN(A$1)列的數(shù)據(jù)放入I2單 元格。然后把I2單元格的公式拖放到N2單元格，點(diǎn)擊N2單元格后向N2單元格右下方移動(dòng)鼠標(biāo)見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了I2到N501單元格的填充到此全部完成。
以上敘述看似繁雜實(shí)際非常簡(jiǎn)單，只要把A1至F1的表頭復(fù)制到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然后向下拖放，即使對(duì)EXCEL應(yīng)用不熟練的同志一分鍾內(nèi)便能完成。
對(duì)上述程序稍作變化還可得到更多用度。上面例子數(shù)據(jù)是從大到小排列的，如H列的函數(shù)中的SMALL改為L(zhǎng)ARGE，上面例子數(shù)據(jù)就從小到大排列了。如 H2單元格的公式改為“=IF(O1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(LARGE(G: G，ROW(A1))，G:G，0))”并把H2單元格的公式向下拖放。這樣在O1單元格輸入1上面例子數(shù)據(jù)是從大到小排列的，O1單元格輸入1以外的數(shù) 上面例子數(shù)據(jù)就從小到大排列了。
如在H列前插入若干列，如插入一列，則現(xiàn)在的H列輸入類(lèi)似G列的公式，例如 “=if(F2=0，10^100，d2)”，現(xiàn)在的I列的公式改為 “=IF(P1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(SMALL(H:H，ROW(A1))，H:H，0)))” 即在P單元格輸入1以外的值就實(shí)現(xiàn)了按獎(jiǎng)金大小排序.這樣只要通過(guò)改變P1(原來(lái)的O1單元格)單元格內(nèi)容的改變就能立即得到按不同要求的排序。
:$F1，$H2， COLUMN(A class="main">
:$F1單元格列陣第$H2行第COLUMN(A class="main">
Excel函數(shù)排序與篩選教程
以上敘述看似繁雜實(shí)際非常簡(jiǎn)單，只要把A1至F1的表頭復(fù)制到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然后向下拖放，即使對(duì)EXCEL應(yīng)用不熟練的同志一分鍾內(nèi)便能完成。
對(duì)上述程序稍作變化還可得到更多用度。上面例子數(shù)據(jù)是從大到小排列的，如H列的函數(shù)中的SMALL改為L(zhǎng)ARGE，上面例子數(shù)據(jù)就從小到大排列了。如 H2單元格的公式改為“=IF(O1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(LARGE(G: G，ROW(A1))，G:G，0))”并把H2單元格的公式向下拖放。這樣在O1單元格輸入1上面例子數(shù)據(jù)是從大到小排列的，O1單元格輸入1以外的數(shù) 上面例子數(shù)據(jù)就從小到大排列了。
如在H列前插入若干列，如插入一列，則現(xiàn)在的H列輸入類(lèi)似G列的公式，例如 “=if(F2=0，10^100，d2)”，現(xiàn)在的I列的公式改為 “=IF(P1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(SMALL(H:H，ROW(A1))，H:H，0)))” 即在P單元格輸入1以外的值就實(shí)現(xiàn)了按獎(jiǎng)金大小排序.這樣只要通過(guò)改變P1(原來(lái)的O1單元格)單元格內(nèi)容的改變就能立即得到按不同要求的排序。
:$F1，$H2， COLUMN(A class="main">
Excel函數(shù)排序與篩選教程
例如對(duì)某張職工花 名冊(cè)工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時(shí)要求性別是男的或女的也是可行的。但要求篩選出女的年 齡在22歲到45歲，男的年齡在25歲到50歲時(shí)Execl本身具有的篩選功能則無(wú)能為力了。再者排序與篩選不能結(jié)合使用，即不能在排序時(shí)根據(jù)條件篩選出 來(lái)的記錄進(jìn)行排序。例如有一張職工資料清單，其中有的職工已經(jīng)退休，對(duì)在職職工的年齡進(jìn)行排序時(shí)無(wú)法剔除已退休職工的數(shù)據(jù)。
本文試圖用Execl的函數(shù)來(lái)解決上述問(wèn)題。
一、用函數(shù)實(shí)現(xiàn)排序
題目：如 有一張工資表，A2:F501，共6列500行3000個(gè)單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎(jiǎng)金、E1為工資、 F1收入合計(jì)?，F(xiàn)要求對(duì)職工收入從多到少排序，且在職工總收入相同時(shí)再按工資從多到少排序，在職工總收入和工資相同時(shí)再按獎(jiǎng)金從多到少排序，在職工職工總 收入和工資、獎(jiǎng)金相同時(shí)再按津貼從多到少排序。
方法：G1單元格填入公式
“=if(F2=0，10^100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)))”，
CONCATENATE 是一個(gè)拼合函數(shù)，可以把30個(gè)以下的單元的數(shù)據(jù)拼合成一個(gè)數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號(hào)分開(kāi)。用f2、e2等被拼合的數(shù)據(jù)用999來(lái)減，是為了使它們 位數(shù)相同。(假定任何一個(gè)職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外 層的if函數(shù)是排序時(shí)用來(lái)剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休"， 10^100，.....)”，即剔除了退休職工。)
第二步把G1單元格的公式拖放到G500單元格(最簡(jiǎn)便的方法是點(diǎn)擊G1單元格后向G1單元格右下方移動(dòng)鼠標(biāo)，見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了G1到G500的填充)。
第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1))，G:G，0)”與第二步一樣拖放到H501單元格。此公式實(shí)際上是 把三列公式合成一列公式，ROW(A1)即為A1的行數(shù)是1，隨著向下拖放依次為2、3、4...，SMALL(G:G，ROW(A1))為 G列中最小的數(shù)隨著向下拖放依次為第2、第3、..小的數(shù)，MATCH(SMALL(G:G，ROW(A1))，G:G，0)即為G列各行的數(shù)據(jù)中最小、 第2、第3小等的數(shù)據(jù)在第幾行。
第四步把A1至F1單元格的表頭復(fù)制到I1至N1單元格，在I2單元格輸入公式 “=INDEX($A$2:$F$501，$H2， COLUMN(A$1))”INDEX函數(shù)是一個(gè)引用函數(shù)，即把$A$2:$F$501單元格列陣第$H2行第COLUMN(A$1)列的數(shù)據(jù)放入I2單 元格。然后把I2單元格的公式拖放到N2單元格，點(diǎn)擊N2單元格后向N2單元格右下方移動(dòng)鼠標(biāo)見(jiàn)到黑十時(shí)雙擊鼠標(biāo)就完成了I2到N501單元格的填充到此全部完成。
以上敘述看似繁雜實(shí)際非常簡(jiǎn)單，只要把A1至F1的表頭復(fù)制到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然后向下拖放，即使對(duì)EXCEL應(yīng)用不熟練的同志一分鍾內(nèi)便能完成。
對(duì)上述程序稍作變化還可得到更多用度。上面例子數(shù)據(jù)是從大到小排列的，如H列的函數(shù)中的SMALL改為L(zhǎng)ARGE，上面例子數(shù)據(jù)就從小到大排列了。如 H2單元格的公式改為“=IF(O1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(LARGE(G: G，ROW(A1))，G:G，0))”并把H2單元格的公式向下拖放。這樣在O1單元格輸入1上面例子數(shù)據(jù)是從大到小排列的，O1單元格輸入1以外的數(shù) 上面例子數(shù)據(jù)就從小到大排列了。
如在H列前插入若干列，如插入一列，則現(xiàn)在的H列輸入類(lèi)似G列的公式，例如 “=if(F2=0，10^100，d2)”，現(xiàn)在的I列的公式改為 “=IF(P1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(SMALL(H:H，ROW(A1))，H:H，0)))” 即在P單元格輸入1以外的值就實(shí)現(xiàn)了按獎(jiǎng)金大小排序.這樣只要通過(guò)改變P1(原來(lái)的O1單元格)單元格內(nèi)容的改變就能立即得到按不同要求的排序。
:$F1單元格列陣第$H2行第COLUMN(A class="main">
Excel函數(shù)排序與篩選教程
以上敘述看似繁雜實(shí)際非常簡(jiǎn)單，只要把A1至F1的表頭復(fù)制到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然后向下拖放，即使對(duì)EXCEL應(yīng)用不熟練的同志一分鍾內(nèi)便能完成。
對(duì)上述程序稍作變化還可得到更多用度。上面例子數(shù)據(jù)是從大到小排列的，如H列的函數(shù)中的SMALL改為L(zhǎng)ARGE，上面例子數(shù)據(jù)就從小到大排列了。如 H2單元格的公式改為“=IF(O1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(LARGE(G: G，ROW(A1))，G:G，0))”并把H2單元格的公式向下拖放。這樣在O1單元格輸入1上面例子數(shù)據(jù)是從大到小排列的，O1單元格輸入1以外的數(shù) 上面例子數(shù)據(jù)就從小到大排列了。
如在H列前插入若干列，如插入一列，則現(xiàn)在的H列輸入類(lèi)似G列的公式，例如 “=if(F2=0，10^100，d2)”，現(xiàn)在的I列的公式改為 “=IF(P1=1，MATCH(SMALL(G:G，ROW(A1))，G:G，0)，MATCH(SMALL(H:H，ROW(A1))，H:H，0)))” 即在P單元格輸入1以外的值就實(shí)現(xiàn)了按獎(jiǎng)金大小排序.這樣只要通過(guò)改變P1(原來(lái)的O1單元格)單元格內(nèi)容的改變就能立即得到按不同要求的排序。
Excel函數(shù)排序與篩選教程
上一篇：EXCEL圖形和圖表編輯技巧介紹
下一篇：Excel刪除重復(fù)行教程
精選文章
-
EXCEL圖形和圖表編輯技巧介紹Excel的重要功能之一就是能快速方便地將工作表數(shù)據(jù)生成柱狀、圓餅、折線(xiàn)等分析圖形。要想在Web發(fā)布這類(lèi)以及用于報(bào)表裝飾示意類(lèi)圖形，首先單擊工具選
-
Excel快速檢索教程
由于重復(fù)輸入或粘貼等原因，Excel 2007工作表往往存在重復(fù)的數(shù)據(jù)或記錄。如果工作表的規(guī)模比較大，手工查找和刪除重復(fù)數(shù)據(jù)很難做到完全徹底。不過(guò)這個(gè)
-
將Excel表格轉(zhuǎn)換成Word文檔教程
excel強(qiáng)大的制表功能令很多辦公一族都感受到過(guò)它的方便與實(shí)用?？墒窃谝恍┣闆r下,卻需要我們將excel表格轉(zhuǎn)換成word文檔。 對(duì)于用慣了excel的人來(lái)說(shuō),直接用
-
Excel快捷鍵集合匯總
Excel快捷鍵大全: 在工作表中移動(dòng)和滾動(dòng) 移動(dòng)到當(dāng)前數(shù)據(jù)區(qū)域的邊緣 CTRL+ 箭頭鍵 移動(dòng)到行首 HOME 移動(dòng)到工作表的開(kāi)頭 CTRL+HOME 移動(dòng)到工作表的最后一個(gè)單元
