網友根據下圖左的 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」是公式運算的結果,表示已超出資料範圍的傳回值。

創作者介紹

學不完.教不停.用不盡

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