Excel-篩選循列顯示的資料再循欄顯示

網友根據這篇:Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

問到,如果篩選結果要循欄顯示,該如何處理?

Excel-篩選循列顯示的資料再循欄顯示

【設計與解析】

選取儲存格B3:D18,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:戶號、姓名、資料。

 

1. 使用 Excel 2021 公式

儲存格F7:=INDEX(FILTER(資料,戶號=$F$4),COLUMN(A:A))

複製儲存格F7,貼至儲存格F7:J7。

FILTER(資料,戶號=$F$4)公式產生如下陣列:

Excel-篩選循列顯示的資料再循欄顯示

再透過 INDEX 函數和 COLUMN 函數在公式向右複製時,分欄取出對應的儲存格內容。

Excel-篩選循列顯示的資料再循欄顯示

 

2. 使用傳統陣列公式

儲存格F7:{=IFERROR(OFFSET($D$3,SMALL(IF($B$4:$B$18=$F$4,ROW($B$4:$B$18),""),COLUMN(A:A))-3,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格F7,貼至儲存格F7:J7。

詳細說明參考:Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

(1) IF($B$4:$B$18=$F$4,ROW($B$4:$B$18),"")

在陣列公式中判斷儲存格F4的內容是否和儲存格B4:B18中的每一個儲存格的內容相符,若是,則傳回該儲存格的列號;否則傳回空字串。在陣列公式中,ROW($B$4:$B$18)={4,5,6, ... , 16,17,18}。 

(2) SMALL(第(1)式,COLUMN(A:A))

利用 SMALL 函數找出第(1)式傳回的列號之最小值。其中,ROW(1:1)向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。可以分別取出第 1,2,3,… 較小值。

(3) OFFSET($D$3,第(2)式-3,0)

將第(2)式依序取出的列號,置入 OFFSET 函數中,得到對應的儲存格內容。

「-3」是因為資料來源中的第一筆資料是由第4列開始。

(4) IFERROR(第(3)式,"")

因為第(3)式當公式向下複製時,有可能傳回錯誤訊,所以利用 IFERROR 函數將錯誤訊息以空字串顯示。

 

【參考資料】

FILTER 函數參考微軟提供的說明網頁:FILTER 函數

OFFSET 函數參考微軟提供的說明網頁:OFFSET 函數

 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel 篩選資料 FILTER
    全站熱搜

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