有網友問到：在 Excel 中的一個資料表(如下圖左)，如何篩選出人員為甲及非空白日期的資料，並且統計其數值的和？
如果你不想手動使用篩選工具，則可以藉助公式來篩選，但是必須經過「陣列公式」的處理。
【準備工作】
選取儲存格B1:D17，按 Ctrl+Shift+F3 鍵，選取「頂端列」，定義名稱：人員、數值、日期。
【公式設計】
1. 篩選：人員為甲
(1) 找出符合的列數
儲存格F3：｛=SMALL(IF(人員="甲",ROW(人員),999),ROW(1:1))-1｝
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。
其中參數「999」是隨意指定一個較大的數值而已。
(2) 根據符合的列數找出符合的內容
儲存格G3：｛=OFFSET(B$1,SMALL(IF(人員="甲",ROW(人員),999)-1,
ROW(1:1)),,)｝
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。
複製儲存格G3，貼至儲存格G3:I3。複製儲存格F3:I3，貼至儲存格F3:I8。
(3)計算篩選後的總和
儲存格H1：=SUMPRODUCT((人員="甲")*數值)
2. 篩選：非空白日期
(1) 找出符合的列數
儲存格F12：｛=SMALL(IF(日期<>"",ROW(日期),999),ROW(1:1))-1｝
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。
(2) 根據符合的列數找出符合的內容
儲存格G12：｛=OFFSET(B$1,SMALL(IF(日期<>"",ROW(日期),999)-1,ROW(1:1)),,)｝
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。
複製儲存格G12，貼至儲存格G12:I12。複製儲存格F12:I12，貼至儲存格F23:I23。
(3)計算篩選後的總和
儲存格H10：=SUMPRODUCT((日期<>"")*數值)
3. 調整日期格式
讀者如果實做時就會發現在 OFFSET 函數篩選日期的結果如果是空白儲存格，則會以「0」顯示，該如何將 0 顯示為空白呢？
為了兼顧正常日期要顯示為月二碼、日二碼，所以將所有日期的儲存格數值格式設定為：「mm/dd;;」
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
留言列表