2015年11月10日 星期二

Excel-4種模擬進階篩選(AND和OR運算,SUMPRODUCT,OFFSET)

在 Excel 中要處理資料時常會用到篩選和進階篩選工具,可以讓你不用設計公式,即可獲想要的結果。本篇要反過來操作,來練習如何利用公式達到進階篩選的結果。我們根據一個資料表透過進階篩選工具,並使用 AND 或是 OR 的條件來篩選資料。
建議先參考前二篇文章再往下閱讀:

1. 找出『國文>=60』且『數學>=60』的資料
Excel-模擬進階篩選(AND和OR運算,SUMPRODUCT,OFFSET)
如果使用進階篩選的工具,如下設定:
資料範圍:A1:F23;
準則範圍:H1:I2,在「準則範圍」中你可以選取篩選規則所在的儲存格。
複製到:H5:K5,在「複製到」中你可以選取想要顯示的欄位。
如果兩個(多個)條件放在同一列,則執行邏輯 AND 運算;
如果兩個(多個)條件放在不同列,則執行邏輯 OR 運算。
Excel-模擬進階篩選(AND和OR運算,SUMPRODUCT,OFFSET)
現在換用公式來模擬篩選結果,可能有些難度,當作是練習吧!
首先,選取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』的資料
Excel-模擬進階篩選(AND和OR運算,SUMPRODUCT,OFFSET)
儲存格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』的資料
Excel-模擬進階篩選(AND和OR運算,SUMPRODUCT,OFFSET)
儲存格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』的資料
Excel-模擬進階篩選(AND和OR運算,SUMPRODUCT,OFFSET)
儲存格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,往下各列貼上。

沒有留言:

張貼留言

好康東東