在 Excel 中要處理資料時常會用到篩選和進階篩選工具，可以讓你不用設計公式，即可獲想要的結果。本篇要反過來操作，來練習如何利用公式達到進階篩選的結果。我們根據一個資料表透過進階篩選工具，並使用 AND 或是 OR 的條件來篩選資料。
建議先參考前二篇文章再往下閱讀：
Excel-計算數列中合於多條件的個數(AND和OR運算,SUMPRODUCT)
Excel-挑出一欄中非空白的儲存格內容(SUMPRODUCT,OFFSET,非陣列公式)
1. 找出『國文>=60』且『數學>=60』的資料
如果使用進階篩選的工具，如下設定：
資料範圍：A1:F23；
準則範圍：H1:I2，在「準則範圍」中你可以選取篩選規則所在的儲存格。
複製到：H5:K5，在「複製到」中你可以選取想要顯示的欄位。
如果兩個(多個)條件放在同一列，則執行邏輯 AND 運算；
如果兩個(多個)條件放在不同列，則執行邏輯 OR 運算。
現在換用公式來模擬篩選結果，可能有些難度，當作是練習吧！
首先，選取A2:E23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：座號、姓名、國文、英文、數學、總分。
儲存格H6：=IFERROR(SUMPRODUCT(SMALL((國文>=60)*(數學>=60)*(座號),SUM(--((國文>=60)*(數學>=60)=0))+ROW(1:1))),"")
透過 SUMPRODUCT 函數來執行陣列的運算，而不使用陣列公式。
(1) (國文>=60)*(數學>=60)*(座號)：
找出『國文>=60』且『數學>=60』者的座號。
(2) SUM(--((國文>=60)*(數學>=60)=0))：
找出『國文>=60』且『數學>=60』不成立者的個數。其中「--」的作用為將傳回值 TRUE／FALSE 轉換為 1／0，才能由SUMPRODUCT 函數計算乘積和。
SMALL(第(1)式,第(2)式+ROW(1:1))：找出合於條件之非 0 的最小值。
使用 IFFERROR 函數將運算過程中產生的錯誤訊息轉換為空白。
在 SUMPRODUCT 函數中的『*』相當於執行邏輯 AND 的運算，並且在運算(乘法)過程中可以將條件傳回值 TRUE／FALSE 轉換為 1／0。
儲存格I6：=IF(H6<>"",OFFSET($A$1,$M6,MATCH(I$5,$A$1:$F$1,0)-1,,),"")
MATCH(I$5,$A$1:$F$1,0)：找出儲存格I5的內容位於儲存格A1:F1中的第幾個。
利用 OFFSET 函數將座位和上式的欄位代入，查詢得到各欄的內容。
複製儲存格I6，貼至儲存格I6:K6。
複製儲存格H6:K6，往下各列貼上。
2. 找出『國文>=80』或『數學>=80』的資料
儲存格H6：=IFERROR(SUMPRODUCT(SMALL((((國文>=80)+(數學>=80))>0)*
(座號),SUM(--((國文>=80)+(數學>=80)=0))+ROW(1:1))),"")
原理請參考「1. 找出『國文>=60』且『數學>=60』的資料」的說明。
在 SUMPRODUCT 函數中此例的『+』運算(加法)過程中可以將條件傳回值 TRUE／FALSE 轉換為 1／0。(注意：『+』運算並非執行邏輯 OR 運算)
儲存格I6：=IF(H6<>"",OFFSET($A$1,$M6,MATCH(I$5,$A$1:$F$1,0)-1,,),"")
複製儲存格I6，貼至儲存格I6:K6。
複製儲存格H6:K6，往下各列貼上。
3. 找出『國文>=60』且『英文>=60』且『數學>=60』的資料
儲存格H7：=IFERROR(SUMPRODUCT(SMALL((國文>=60)*(英文>=60)*(數學>=
60)*(座號),SUM(--((國文>=60)*(英文>=60)*(數學>=60)=0))+ROW(1:1))),"")
儲存格I7：=IF(H7<>"",OFFSET($A$1,$M7,MATCH(I$6,$A$1:$F$1,0)-1,,),"")
複製儲存格I7，貼至儲存格I7:K7。
複製儲存格H7:K7，往下各列貼上。
4. 找出『國文>=80』或『英文>=80』或『數學>=80』的資料
儲存格H7：=IFERROR(SUMPRODUCT(SMALL((((國文>=80)+(英文>=80)+(數學>=80))>0)*(座號),SUM(--((國文>=80)+(英文>=80)+(數學>=80)=0))+ROW(1:1))),"")
儲存格I7：=IF(H7<>"",OFFSET($A$1,$M7,MATCH(I$6,$A$1:$F$1,0)-1,,),"")
複製儲存格I7，貼至儲存格I7:K7。
複製儲存格H7:K7，往下各列貼上。
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
留言列表