Not Only Diagram But BI Visualization

這是這次新版本的Excel新增函數中，筆者最喜歡的函數之一。可以將資料來源裡，符合準則需求的資料統統擷取出來。昔日必須透過［資料］［篩選］的操作，或［進階篩選］的操作，設定準則規範以顯示所要查找的每一筆資料記錄。可是，當資料來源有所變動時，篩選的操作步驟又必須再來一回。可是，有了FILTER函數以後，動態陣列參照的特性，只要資料來源有所變動或篩選準則已經調整，正如函數會自動運算的天性，也都能夠即時自動更新篩選結果。

語法：

=FILTER (array, include, [if_empty])

參數：

• array – 欲進行篩選的資料範圍或陣列。
• include – 布林陣列，類似準則區的定義與支援。
• [if_empty] – 若找不到符合條件的資料時，所要傳回的訊息。

範例：

此實作範例的資料表，事先已經重新命名為「資料記錄」，已經內含126筆資料記錄，記載著每一筆交易記錄的交易編號、交易日期、交易地區、交易金額以及經手人姓名等資訊：

單一條件的篩選

透過FILTER函數，便可以輕鬆擷取所需的資料。函數裡的第一個參數array便是表達資料來源的所在處，而第二個參數include便是變化多端的準則定義，可以直接將篩選準則描述在參數裡，也可以透過儲存格參照，將準則條件設定在指定的儲存格範圍裡。而最一個參數[if_empty]可設定若找不到符合準則的資料時，想要顯示的提示訊息，若未設定此參數，則未尋獲資料時將顯示「#CALC!」。以此實作範例為例，在儲存格H2輸入以下函數：

=FILTER(交易紀錄,交易紀錄[地區]="台中市")

即可自儲存格H2開始，列出「地區」欄位隸屬於台中市的資料記錄。更厲害的是當原始料來源的內容有增減或異動時，FILTER函數的篩選結果也是會同步更新的喔！

多條件的篩選

如果要考量兩個或兩個以上的多條件篩選，那就要好好思考一下這些條件的邏輯關係。譬如：若規劃了兩個篩選條件，就必須明確表明這兩個篩選條件要同時都符合(AND邏輯)才篩選資料，還是只要其中一個條件符合(OR邏輯)就篩選資料。筆者在一篇文章中有分享這個領域的介紹與實作，有興趣的話可以前去瀏覽與研習。此實作範例我們想要同時考量兩個篩選條件，因此，在FILTER的第二個參數include裡藉由邏輯算式進行篩選準則的定義。例如，要達到篩選交易「地區」位於「台中市」，則條件判斷式可以寫成：

(交易紀錄[地區]= "台中市")

是要達成「經手人」為「林憶如」，則條件判斷式可以寫成：

(交易紀錄[經手人]= "林憶如")

而這兩個條件式都是針對整個資料欄位(「地區」欄與「經手人」欄)裡每一筆內容，對指定的地區名稱與經手人姓名進行逐一關係判斷，因此，傳回的結果都是屬於陣列架構，意即結果陣列裡都是充滿了TRUE或FALSE的值。假設上述這兩個條件判斷式要同時符合才進行資料的篩選，也就是期望既要交易「地區」等於「台中市」，而且「經手人」也必須是「林憶如」的資料才能被篩選出來，那麼這兩條件的邏輯關係便稱之為AND(且)邏輯。因此，我們需要針對這個兩個陣列結果進行AND邏輯陣列運算，而AND邏輯運算是一種乘法運算類型，可以使用數學運算符號「*」乘號來表示，所以，根據上述兩個條件判斷式，參數include可以撰寫成：

(交易紀錄[地區]= "台中市")*(交易紀錄[經手人]= "林憶如")

如此就可以產生一個充滿0與1的結果陣列，其中，1便是隸屬於「交易地區」等於「台中市」且「經手人」也是「林憶如」的資料。所以，只要合乎上述邏輯算式便是我們要篩選的資料準則，因此，再將先前的FILTER函數進行以下的修改：

=FILTER(交易紀錄,(交易紀錄[地區]="台中市")*(交易紀錄[經手人]="林憶如"))

在儲存格H2為首的篩選輸出區就可以列出「地區」欄位為「台中市」且「經手人」欄位為「林憶如」的資料記錄了。

 

迷思：AND邏輯運算是使用AND函數？還是乘法「*」算式？

有些朋友可能會迷惘於要進行AND邏輯運算時，到底是要使用AND函數？還是建立乘法「*」算式？其實，這端賴您所需要的輸出結果來決定。如果您對於AND邏輯與乘法「*」算式有些模糊，可以瀏覽筆者的其他相關文章：Excel的邏輯運算模擬實作與陣列公式裡的布林運算實作。

 

我們再修改一下函數裡的準則內容，改成篩選「地區」為「台中市」且「經手人」為「吳佩芬」的交易資料：

=FILTER(交易紀錄,(交易紀錄[地區]="台中市")*(交易紀錄[經手人]="吳佩芬"))

而此次的篩選結果顯示了「#CALC!」，即表示並沒有找到合乎準則的資料。

如果您希望沒有篩選結果的訊息可以自行設定，那麼就是FILTER函數的第三個參數[if_empty]該上場的時機了。可以將此參數設定成一個字串，若函數執行結果是找不到符合條件的資料時就可以傳回該字串。例如：

=FILTER(交易紀錄,(交易紀錄[地區]="台中市")*(交易紀錄[經手人]="吳佩芬"),"查無資料")

我們再試試數值性資料的篩選比對，例如，輸入以下的FILTER函數：

=FILTER(交易紀錄,交易紀錄[交易金額]>500000)

即可篩選出「交易金額」>500000的資料記錄。

若同時要篩選兩個條件準則，找出「交易金額」>500000，而且交易日期是屬於107年度的資料記錄。這又是一個AND邏輯的準則算式了。其中，原始資料的「交易日期」欄位的內容格是7位數的數字，前三碼(左)是民國年，中間兩碼是月份、最後(右)兩碼是月中的日期。因此，根據上述的需求，可以透過LEFT函數的搭配，撰寫以下的關係判斷：

=LEFT(交易紀錄[交易日期],3)="107"

完整的FILTER函數便可以撰寫成：

=FILTER(交易紀錄,(交易紀錄[交易金額]>500000)*(LEFT(交易紀錄[交易日期],3)="107"))

如果我們要同時篩選某指定的「地區」某「經手人」之交易資料，那麼，可以在工作表上建立這兩個篩選準則的儲存格操控介面。例如：儲存格O1可選擇或輸入指定的「地區」；儲存格O2可選擇或輸入指定的「經手人」。因此，FILTER函數可以撰寫成：

=FILTER(交易紀錄,(交易紀錄[地區]=O1)*(交易紀錄[經手人]=O2),"沒有資料")

搭配UNIQUE函數設計選項介面

接著，我們可以利用O1、O2這兩個儲存格來建立「地區」與「經手人」的下拉式選單，作為篩選準則定義的參照，而下拉式選單的內容，理所當然就是原始資料裡「地區」欄位內容的唯一值，以及「經手人」欄位內容的唯一值！至於找出這兩個欄位內容的唯一值陣列，可就是UNIQUE函數的專長囉！例如：我們先在儲存格R1與S1輸入標題文字後，在儲存格R2輸入以下函數：

=UNIQUE (交易紀錄[地區])

順利在此建立了的地區欄位內容的唯一值動態陣列。在儲存格S2輸入以下函數：

=UNIQUE (交易紀錄[經手人])

順利在此建立了的經手人欄位內容的唯一值動態陣列。

爾後便可以透過資料驗證的功能操作，藉此建立篩選資料的操作介面，例如：儲存格O1設定為資料驗證清單來自R2的唯一值動態陣列內容；儲存格O2設定為資料驗證清單來自S2的唯一值動態陣列內容。

註：此Excel文章內文實作活頁簿檔案下載。