網友常問的問題：在 Excel 的資料清單中，如何挑選勾選／不勾選的項目？本篇要以陣列公式和非陣列公式二種方式來處理。(在 Excel 中使用陣列公式時，常會因為儲存格陣列的數量龐大，而造成系統效能下降，所以不得已要放棄陣列公式的使用，所以得發展不需使用陣列公式的方式。)
【列出篩選已勾選的項目】
在下圖中，A欄是資料清單，在B欄中有以『V』標示為勾選的項目，如何以公式來篩選這些『V』的項目？
先選取儲存格A1:B23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：清單、勾選。
1. 陣列公式
儲存格D2：{=IFERROR(OFFSET($A$1,SMALL(IF(勾選="V",ROW(清單),""),
ROW(1:1))-1,0),"")}
這是陣列公式，輸入公式後要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格D2，貼至儲存格D2:D20。
(1) IF(勾選="V",ROW(清單),"")
在勾選陣列中判斷具有「V」的儲存格，傳回列號的陣列。ROW 函數可以儲存格的列號。
(2) SMALL(IF(勾選="V",ROW(清單),""),ROW(1:1))
將第(1)式傳回的列號陣列，利用 SMALL 函數由小至大取出其列號。公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,SMALL(IF(勾選="V",ROW(清單),""),ROW(1:1))-1,0)
將第(2)式傳回的列號代入 OFFSET 函數，傳回對應的儲存格內容。
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。
2. 非陣列公式
儲存格D2：=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((勾選<>"")*
ROW(勾選),ROW(1:1)+COUNTIF(勾選,"")))-1,0),"")
複製儲存格D2，貼至儲存格D2:D20。
(1) (勾選<>"")*ROW(勾選)
在 SUMPRODUCT 函數中找出不具有「V」的列號。
(2) SMALL((勾選<>"")*ROW(勾選),ROW(1:1)+COUNTIF(勾選,""))
COUNTIF(勾選,"")：計算在勾選欄位中共有幾個空白(非「V」儲存格)。
本式可依列號由小至大依序傳回第1, 2, 3, ... 個具有「V」的儲存格列號。
(3) SUMPRODUCT(SMALL((勾選<>"")*ROW(勾選),ROW(1:1)+COUNTIF(勾選,"")))
在 SUMPRODUCT 函數中，第(1)式和第(2)式可以執行相當於陣列公式的功能。
(4) OFFSET($A$1,第(3)式-1,0)
最後，將第(3)式的列號代入 OFFSET 函數，傳回對應的儲存格內容。
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。
【列出篩選未勾選的項目】
在下圖中，A欄是資料清單，在B欄中有以『V』標示為勾選的項目，如何以公式來篩選這些『V』以外的項目？
先選取儲存格A1:B23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：清單、勾選。
1. 陣列公式
儲存格D2：{=IFERROR(OFFSET($A$1,SMALL(IF(勾選<>"V",ROW(清單),""),
ROW(1:1))-1,0),"")}
這是陣列公式，輸入公式後要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格D2，貼至儲存格D2:D20。
參考列出篩選已勾選的項目的1.陣列公式，其差異為將「勾選="V"」，更新為「勾選<>"V"」。
2. 非陣列公式
儲存格D2：=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((勾選="")*
ROW(勾選),ROW(1:1)+COUNTIF(勾選,"V")))-1,0),"")
複製儲存格D2，貼至儲存格D2:D20。
參考列出篩選已勾選的項目的2.非陣列公式，其差異為將「COUNTIF(勾選,"")」更新為「COUNTIF(勾選,"V")」。
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
留言列表