Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

在 Excel 裡,如果要列出非空白項目的清單,該如何設計?

本篇將會學到:

要比較篩選函數和以陣列公式模擬篩選等不同方式,來練習如何列出非空白項目的清單。

Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

【設計與解析】

選取儲存格A3:A20,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目。

1.使用篩選函數

儲存格C4:=FILTER(項目,項目<>"")

Excel 2021 以上版本只要在一個儲存格中輸入公式(FILTER函數),Excel會自動溢出公式至其他儲存格。

 

2. 模擬篩選函數

儲存格C4:

{=IFERROR(INDEX(項目,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-3),"")}

這是陣列公式,Excel 2021 以下版本要按 Ctrl+Shift+Enter 鍵以產生「{}」。

複製儲格C4,貼至儲存格C4:C20。

(1) IF(項目<>"",ROW(項目),"")

在陣列公式中,判斷「項目」清單裡的每一個儲存格是否為空字串,如果不是傳回該儲存格列號,否則傳回空字串。ROW 函數可以傳回儲存格的列號。

(2) SMALL(第(1)式,ROW(1:1))

在陣列公式中,將第(1)式的傳回值利用 SMALL 函數由小至大取出內容。

(3) INDEX(項目,第(2)式-3)

在陣列公式中,將第(2)式的傳回值利用 INDEX 函數在「項目」清單中查詢,以傳回對應的儲存各內容。

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

利用 IFERROR 函數將傳回值若是錯誤訊時,改以空字串顯示。

 

3.模擬篩選函數

儲存格C4:

=IFERROR(INDEX(項目,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-3),"")

這雖然是陣列公式,Excel 2021 以上版本,不按 Ctrl+Shift+Enter 鍵也可以。

複製儲格C4,貼至儲存格C4:C20。

 

【延伸學習】

Excel-陣列的使用(比較2021版和先前的版本)

Excel-以製作九九乘法表說明陣列和非陣列公式

Excel-FILTER和OFFSET的動態陣列

Excel-輸入具陣列形式的公式

Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

Excel-使用ARRAYTOTEXT函數取得陣列文字

Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 

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

arrow
arrow

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