2016年9月27日 星期二

Excel-從多欄中列出符合者清單(OFFSET,SMALL,ROW,陣列公式)

網友根據下圖左的 Excel 資料表,想要列出如下圖右的摘要結果,該如何處理?
在下圖左的資料表中每個人員可以參加 3 個組別(沒有重覆),要如何列出每個組的人員清單(如下圖右)?
Excel-從多欄中列出符合者清單(OFFSET,SMALL,ROW,陣列公式)

【公式設計與解析】
儲存格E2:{=OFFSET($A$1,SMALL(IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),
999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格E2,貼至儲存格E2:K23。
(1) IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),999)
在陣列公式中,若資料範圍儲存格B2:D23和儲存格F1相同,則傳回相符者的列號陣列,否則傳回『999』(這只是一個很大的數值,必須超過所以資料的列號最大值。)。
(2) SMALL(第(1)式,ROW(1:1))
根據第(1)式傳回的列號陣列,利用 SMALL 函數找出其最小值(ROW(1:1)=1)。若公式向下複製,則ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,可以依序找出第 1, 2, 3, ...較小者。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式傳回的列號代入 OFFSET 函數找出對應的儲存格內容。當公式向下複製時,可以由最小列至最大列,依序列出符合者的儲存格內容。
摘要結果的「0」是公式運算的結果,表示已超出資料範圍的傳回值。

沒有留言:

張貼留言

好康東東