有網友問到:在 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-下拉式清單選取月份列出該月日期

圖片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版新增函數的使用

    全站熱搜

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