學不完．教不停．用不盡

網友問到：在 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個報表。