2016年9月17日 星期六

Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)

網友問到:在 Excel 工作表中有一個社團選取結果的資料表(如下圖左),如何才能分社團/分星期的列出各個報表?
例如:在下圖中有多個班級的選社結果,其中星期一至星期五,每個學生每天都有一個要參加的社團,如何能分別依各星期和各社團列出社團人員的清單?
Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)
【公式設計與解析】
1. 定義儲存格範圍名稱
選取D欄至H欄中有資料的範圍(例如:儲存格D1:H200),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:星期一、星期二、星期三、星期四、星期五。
2. 輸入公式
儲存格J5:{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
(1) INDIRECT($K$1)
將儲存格K1中的內容("星期一")透過 INDIRECT 函數轉換為儲存格範圍(已定義好「星期一」的儲存格範圍)。
(2) ROW(INDIRECT($K$1))
藉由 ROW 函數取得由儲存格K1內容轉換的儲存格範圍的列號。例如:ROW(C2)=2、ROW(C3)=3、...。
(3) IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT($K$1)),999)
在陣列公式中,判斷儲存格K1內容(星期幾)的儲存格範圍和儲存格K2內容(社團名稱)相同者,傳回其列號陣列,不相同者傳回『999』(這只是一個很大的數)。
(4) SMALL(第(3)式,ROW(1:1))
利用 SMALL 函數找出第(3)傳回的列號陣列中的最小者。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=3→ROW(3:3)=3→...。可以依序找出第1,2,3, ... 較小者。
(5) OFFSET($A$1,第(4)-1,0)
根據第(4)式傳回的列號利用 OFFSET 函數查詢對應的儲存格內容。
同理:
儲存格K5:{=OFFSET($B$1,SMALL(IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
儲存格L5:{=OFFSET($C$1,SMALL(IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格J5:L5,往下各列貼上。

【補充說明】
利用資料驗證功能建立星期幾和社團名稱的下拉式清單,可以變成動態查詢系統。一個資料表可以製作25個報表。
Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)
Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)

沒有留言:

張貼留言

好康東東