2015年5月15日 星期五

Excel-根據某欄摘要出符合條件的結果(陣列公式,OFFSET)

網友想要根據一個 Excel 資料表(下圖左),由某一欄位(點數)挑出符合條件者(>=60)的摘要表(下圖右)。

目前我的做法是要透過陣列公式來處理,如果資料數量很大時,或許速度會變慢,但仍不失一個好的做法。

先選取儲存格A1:A27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:序號。

先選取儲存格D1:D27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:點數。

 

【公式設計】

(1) 找出符合規則的序號

儲存格G2:{=SMALL(IF(點數>=60,ROW(序號)-1,9999),ROW(1:1))}

這是陣列公式,輸入完要 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

IF(點數>=60,ROW(點數)-1,9999):找出點數陣列中內容>=60者,符合者傳回列數減1,不符合者傳回一個很大的值(本例為9999,資料總列數不超過該數。)

透過 SMALL 函數,在G欄的每一列中由小到大依序列出符合的列數。

如果你的資料表中沒有「序號」這個欄位,則可以改用以下公式:

儲存格G2:{=SMALL(IF(點數>=60,ROW(點數)-1,9999),ROW(1:1))}

複製儲存格G2,貼至儲存格G2:G27。

 

(2) 藉由序號查表得到結果

儲存格H2:=OFFSET($A$1,$G2,COLUMN(A:A),,)

使用 OFFSET 函數,透過已經找出的符合之序號,將資料表的其他對應資料帶出。若是序號為 9999 者所對應的資料結果均為 0。

複製儲存格H2,貼至儲存格H2:K27。

沒有留言:

張貼留言

檢視其他文章

好康東東