錦子老師

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)))」

即在P1儲存格輸入1以外的值就實現了按獎金大小排序.這樣只要通過改變P1(原來的O1儲存格)儲存格內容的改變就能立即得到按不同要求的排序。

n  用函數實現篩選

題目：

有一張員工2017年工資表，A2:F501,共6欄500列3000個儲存格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為性別、D1為年齡、E1為學歷、F1收入。現要求對員工的性別、年齡、學歷、職稱進行交錯篩選，例如要求在同一張表上篩選出：

1、女的年齡在22歲到45歲，男的年齡在25歲到50歲。

2、女博士。

3、男博士後。

方法：

點取G2儲存格輸入公式：「=IF(OR(AND(C2="女",D2>=22,D2<=45),AND(C2="男",D2>=25, D2<=50)),ROW(A1),0)」，如下圖所示。

點取H2儲存格輸入公式：「=IF(AND(C2="女",E2="博士"),ROW(B1),0)」，如下圖所示。

點取I2儲存格輸入公式：「=IF(AND(C2="男",E2="博士後"),ROW(B1),0)」，如下圖所示。

點取J2儲存格輸入公式：「=IF(K$2=1,LARGE(G:G,ROW(A1)),IF(K$2=2,LARGE(H:H,ROW (A1)),IF(K$2=3,LARGE(I:I,ROW(A1)),0)))」，如下圖所示，然後用上述提到的方法向下複製公式。

G、H、I欄的公式的含義就是凡符合篩選條件的列記錄下列號否則為零，J欄的公式的含義根據K2的數值選擇G、H、I中的一欄進行排序並把不合條件的欄除去。

框選G2:J2儲存格範圍，將滑鼠指標移到J2儲存格右下角拖拉方塊上方，待指標變為「+」後，快按滑鼠左鍵二下，將公式複製到G3:J501儲存格，如下圖所示。

點取K1儲存格輸入「篩選選擇」字串，框選A1:F1儲存格範圍，點取「常用 > 剪貼簿 > 複製」鈕或CTRL + C鍵，點取L1儲存格，點取「常用 > 剪貼簿 > 貼上」鈕或CTRL + V鍵，將表頭複製到L1:Q1，如下圖所示。

在L2儲存格輸入公式「=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)))」，然後向右拖放到Q2。

框選L2:Q2儲存格範圍，將滑鼠指標移到Q2儲存格右下角拖拉方塊上方，待指標變為「+」後，快按滑鼠左鍵二下，將公式複製到L3:Q501儲存格，如下圖所示。

點取K2儲存格輸入1或2或3便可實現上述三種篩選。