397用Excel函數實現排序與篩選的方法 @ 錦子老師

397	用Excel函數實現排序與篩選的方法
示範檔	無	範例檔	397.XLSX	結果檔	無

Execl本身具有很方便的排序與篩選功能，點取「資料 > 排序與篩選 > 排序/篩選」即可對資料清單進行排序或篩選。但也有不足，首先無論排序或篩選都改變了原清單的原貌，特別是清單的資料從其它工作表鏈接而來，來源資料發生變化時或清單記錄新記錄時必須從新進行排序或篩選。其次還有一些局限，例如：排序2003版Excel只能最多對三個關鍵字(三欄資料)排序，但2007以後版本則無此限制，篩選對同一欄資料可用「與」、「或」任一條件篩選，但對不同欄資料只能用「與」條件篩選。例如：對員工名冊工作表，要求篩選出年齡大於25歲且小於50歲或年齡大於50歲或小於25歲都是可行的，如果同時要求性別是男的或女的也是可行的。但要求篩選出女的年齡在22歲到45歲，男的年齡在25歲到50歲時Execl本身具有的篩選功能則無能為力了。再者排序與篩選不能結合使用，即不能在排序時根據條件篩選出來的記錄進行排序。例如：員工資料清單，其中有的職工已經退休，對在職職工的年齡進行排序時無法剔除已退休職工的資料。

本單元試圖用Execl的函數來解決上述問題。

n 用函數實現排序

題目：

有一張員工2017年工資表，A2:F501,共6欄500列3000個儲存格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎金、E1為工資、F1收入合計。現要求對員工收入從多到少排序，且在員工總收入相同時再按工資從多到少排序，在員工總收入和工資相同時再按獎金從多到少排序，在員工總收入和工資、獎金相同時再按津貼從多到少排序。

方法：

第一步在G1儲存格填入公式：「=IF(F1=0,10^100,INT(CONCATENATE(999-F2,999-E2,999-D2 , 999-C2)))」後，按Enter鍵，如下圖所示。

CONCATENATE是一個相容性函數，可以把30個以下的單元的資料拼合成一個資料，這些被拼合的資料之間用逗號分開。用F2、E2等被拼合的資料用999來減，是為了使它們位數相同。(假定任何一個職工的總收入少於899元)。被拼合成的函數是文字函數，CONCATENATE與INT函數套用是為了使文字轉換為數值。最外層的IF函數是排序時用來剔除不進行排序的記錄，在本例中指收入為零的記錄。

第二步把G1儲存格的公式拖放到G501儲存格(最簡便的方法是點取G1儲存格，將滑鼠指標移到G1儲存格右下方拖拉方塊上方，待變為「+」後，按兩下滑鼠左鍵就完成了G2到G501的填充)。

第三步在在H1單元填入公式：「=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,$H1,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以外的值就實現了按獎金大小排序。

如果在H欄前插入若干欄，如果插入一欄，則現在的H欄輸入類似G欄的公式，例如：「=IF(F2=0, 10^100,d2)」,現在的I欄的公式改為「=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH (SMALL(H:H,ROW(A1)),H:H,0)))」