有網友問到:在 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版新增函數進行篩選、查詢、排序之綜合練習
留言列表