Excel-由資料清單中篩選一組

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

Excel-由資料清單中篩選一組

【設計與解析】

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

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

image

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),"")}

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

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