2015年3月17日 星期二

Excel-查表後篩選資料並計算總和(OFFSET,SUMPRODUCT,陣列公式)

有網友問到:在 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;;」

沒有留言:

張貼留言

檢視其他文章

好康東東