網友問到：在 Excel 工作表中有一個社團選取結果的資料表(如下圖左)，如何才能分社團／分星期的列出各個報表？
例如：在下圖中有多個班級的選社結果，其中星期一至星期五，每個學生每天都有一個要參加的社團，如何能分別依各星期和各社團列出社團人員的清單？
【公式設計與解析】
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個報表。
留言列表