廣告贊助

網友問到:在 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,陣列公式)

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()