學不完．教不停．用不盡

參考下圖，在 Excel 要將一個資料清單裡篩選出其中一組，如果採用 Excel 2021 以後版本的 FILTER 函數，則公式較簡短且簡單。如果使用傳統的陣列公式，除了冗長且不易理解。

【設計與解析】

選取儲存格A3:H28，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：品名、售價、忠店、孝店、仁店、愛店、信店、義店。

在儲存各J4中設定資料驗證為下拉式清單，來源為：=$C$3:$H$3

1. 使用 Excel 2021 版以上的 FILTER 函數

(1) 列出品名

儲存格L5：=FILTER(品名,INDIRECT(J4)<>0,"")

利用 INDIRECT 函數將儲存格內容轉換為儲存格範圍。

利用條件：INDIRECT(J4)<>0，篩選符於條件者對應的品名。

(2) 列出售價

儲存格M5：=FILTER(售價,INDIRECT(J4)<>0,"")

利用條件：INDIRECT(J4)<>0，篩選符於條件者對應的售價。

(3) 列出數量

儲存格N5：=FILTER(INDIRECT(J4),INDIRECT(J4)<>0,"")

利用條件：INDIRECT(J4)<>0，篩選符於條件者對應的店品(欄位內容為數量)。

2. 使用傳統陣列公式

(1) 列出品名

儲存格L5：{=IFERROR(INDEX(品名,SMALL(IF(INDIRECT($J$4)<>0,ROW(品名),""),ROW(1:1))-3),"")}

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

複製儲存格L5，貼至儲存格L5:L15。

IF(INDIRECT($J$4)<>0,ROW(品名),"")

判斷店品欄位裡的內容是否為0，若是則傳回列號，否則傳回空字串。

SMALL(IF(INDIRECT($J$4)<>0,ROW(品名),""),ROW(1:1))

利用 SMALL 函數由小至大取出列號。

再藉由 INDEX 函數取出對應的儲存格內容。

透過 IFERROR 函數將錯誤訊息以空字串顯示。

(2) 列出售價

儲存格M5：{=IFERROR(INDEX(售價,SMALL(IF(INDIRECT($J$4)<>0,ROW(品名),""),ROW(1:1))-3),"")}

(3) 列出數量

儲存格N5：{=IFERROR(INDEX(INDIRECT($J$4),SMALL(IF(INDIRECT($J$4)<>0,ROW(品名),""),ROW(1:1))-3),"")}