2017年10月2日 星期一

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

回答網友提問:如何在 Excel 中,由原始清單中挑出符合指定內容的清單?
如下圖,如何由原始清單中(如下圖左),挑出符合指定內容的清單(如下圖左)?
Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET).xlsx

【公式設計與解析】
如果不想大費周章的寫公式,篩選工具是你很好的選擇,而且很容易即可完成。本篇是要以撰寫公式來完成這個工作。
Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)
儲存格F4:{=IFERROR(OFFSET($D$1,SMALL(IF($B$2:$B$16=$F$2,
ROW($B$2:$B$16),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格F4,貼至儲存格F4:F16。
(1) IF($B$2:$B$16=$F$2,ROW($B$2:$B$16),"")
在陣列公式中判斷儲存格F2的內容是否和儲存格B2:B16中的每一個儲存格的內容相符,若是,則傳回該儲存格的列號;否則傳回空字串。在陣列公式中,ROW($B$2:$B$16)={2,3,4, ... , 14,15,16}。 
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數找出第(1)式傳回的列號之最小值。其中,ROW(1:1)向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→....。可以分別取出第 1,2,3,… 較小值。
(3) OFFSET($D$1,第(2)式-1,0)
將第(2)式依序取出的列號,置入 OFFSET 函數中,得到對應的儲存格內容。
(4) IFERROR(第(3)式,"")
因為第(3)式當公式向下複製時,有可能傳回錯誤訊,所以利用 IFERROR 函數將錯誤訊息以空字串顯示。
所以,當儲存格F2改變時,可以顯示對應的清單。
Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET).xlsx

沒有留言:

張貼留言

檢視其他文章

好康東東