Excel函數排序與篩選.doc

Excel:函數排序與篩選Execl本身具有很方便的排序與篩選功能，下拉“數據”菜單即可選擇排序或篩選對數據清單進行排序或篩選。但也有不足，首先無論排序或篩選都改變了原清單的原貌，特別是清單的數據從其它工作表鏈接來而源數據發(fā)生變化時，或清單錄入新記錄時必須從新進行排序或篩選。其次還有局限，例如排序只能最多對三個關鍵字(三列數據)排序，篩選對同一列數據可用“與”、或“或”條件篩選，但對不同列數據只能用“與”條件篩選。例如對某張職工花名冊工作簿，要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的，如同時要求性別是男的或女的也是可行的。但要求篩選出女的年齡在22歲到45歲，男的年齡在25歲到50歲時Execl本身具有的篩選功能則無能為力了。再者排序與篩選不能結合使用，即不能在排序時根據條件篩選出來的記錄進行排序。例如有一張職工資料清單，其中有的職工已經退休，對在職職工的年齡進行排序時無法剔除已退休職工的數據。本文試圖用Execl的函數來解決上述問題。一、用函數實現排序題目：如有一張工資表，A2:F501，共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎金、E1為工資、F1收入合計?，F要求對職工收入從多到少排序，且在職工總收入相同時再按工資從多到少排序，在職工總收入和工資相同時再按獎金從多到少排序，在職工職工總收入和工資、獎金相同時再按津貼從多到少排序。方法：G1單元格填入公式“=if(F2=0，10100，INT(CONCATENATE(999-f2，999-e2，999-d2，999-c2)”，CONCATENATE 是一個拼合函數，可以把30個以下的單元的數據拼合成一個數據，這些被拼合的數據之間用逗號分開。用f2、e2等被拼合的數據用999來減，是為了使它們位數相同。(假定任何一個職工的總收入少于899元)。被拼合成的函數是文本函數，CONCATENATE與INT函數套用是為了使文本轉換為數字。最外層的if函數是排序時用來剔除不進行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2=退休， 10100，.)”，即剔除了退休職工。)第二步把G1單元格的公式拖放到G500單元格(最簡便的方法是點擊G1單元格后向G1單元格右下方移動鼠標，見到黑十時雙擊鼠標就完成了G1到G500的填充)。第三步在在H2單元填入公式“=MATCH(SMALL(G:G，ROW(A1)，G:G，0)”與第二步一樣拖放到H501單元格。此公式實際上是把三列公式合成一列公式，ROW(A1)即為A1的行數是1，隨著向下拖放依次為2、3、4.，SMALL(G:G，ROW(A1)為 G列中最小的數隨著向下拖放依次為第2、第3、.小的數，MATCH(SMALL(G:G，ROW(A1)，G:G，0)即為G列各行的數據中最小、第2、第3小等的數據在第幾行。第四步把A1至F1單元格的表頭復制到I1至N1單元格，在I2單元格輸入公式“=INDEX($A$2:$F$501，$H2， COLUMN(A$1)”INDEX函數是一個引用函數，即把$A$2:$F$501單元格列陣第$H2行第COLUMN(A$1)列的數據放入I2單元格。然后把I2單元格的公式拖放到N2單元格，點擊N2單元格后向N2單元格右下方移動鼠標見到黑十時雙擊鼠標就完成了I2到N501單元格的填充到此全部完成。以上敘述看似繁雜實際非常簡單，只要把A1至F1的表頭復制到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然后向下拖放，即使對EXCEL應用不熟練的同志一分鍾內便能完成。對上述程序稍作變化還可得到更多用度。上面例子數據是從大到小排列的，如H列的函數中的SMALL改為LARGE，上面例子數據就從小到大排列了。如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上面例子數據是從大到小排列的，O1單元格輸入1以外的數上面例子數據就從小到大排列了。如在H列前插入若干列，如插入一列，則現在的H列輸入類似G列的公式，例如“=if(F2=0，10100，d2)”，現在的I列的公式改為“=IF(P1=1，MATCH(SMALL(G:G，ROW(A1)，G:G，0)，M