網友根據下圖左的 Excel 資料表，想要列出如下圖右的摘要結果，該如何處理？
在下圖左的資料表中每個人員可以參加 3 個組別(沒有重覆)，要如何列出每個組的人員清單(如下圖右)？
【公式設計與解析】
儲存格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」是公式運算的結果，表示已超出資料範圍的傳回值。
【延伸閱讀】
Excel-資料篩選的好幫手-UNIQUE函數(2021版以上)
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
全站熱搜
留言列表