網友問到一個常見的問題:參考下圖,想要藉由下拉式清單來篩選不同類別的資料,該如何處理?

處理原則是下拉式清單可以使用「資料驗證」功能,而篩選資料通常會用到陣列公式。

【準備工作】

選取「級數」欄位中有資料的儲存格範圍,按一下 Ctrl+Shift+F3 鍵,定義名稱:級數。

【輸入公式】

儲存格G2:{=IFERROR(OFFSET($B$1,SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格G2,貼至儲存格G2:H2。複製儲存格G2:H2,往下各列貼上。

參考下圖,對公式加以分析:

IF(級數=$F$1,ROW(級數),FALSE):在陣列公式中,判斷陣列值是否和儲存格F1的內容相同,如果是則傳回測試列對應的 ROW 函數值(參考欄B和欄C的內容),否則傳回 FALSE

SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1)):使用 SMALL 函數找尋上式所對應的列號之第1,2,3,…個(參考欄D的內容)。例如:第一個的列號為1,第二個的列號為7,…。

將上式取得的第1,2,3,…個列號,代入 OFFSET 函數中,取得對應的儲存格內容。

OFFSET($B$1,SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,)

其中 COLUMN(A:A)=1,如果向複製時會變成 COLUMN(B:B)=2、COLUMN(C:C)=3、…。

可以控制在 OFFSET 函數中傳那一「欄」的位置。

這個公式無論幾欄的資料,只要向右複製,即可傳回對應的儲存格內容。

最後,再透過 IFERROR 函數,將沒有傳回儲存格內容的值(錯誤訊息#NUM!)改以空白顯示。

至於儲存格的下拉式選單,則在「資料驗證」中設定儲存格內允許:清單;來源:一、二、三、四、五、六。(參考下圖)

 

 

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

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

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

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

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

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

arrow
arrow
    全站熱搜

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