2017年6月16日 星期五

Excel-位於日期範圍者加總(SUMPRODUCT)

網友問到 Excel 的問題:在工作表中的一個日期和金額清單,網友想要將合於某個日期區間中的金額予以加總,該如何處理?
如下圖,以「01月03日」為例,觀察起日和迄日的日期區間,有 7 個區間中含有「01月03日」,要將這些的金額予以加總。
Excel-位於日期範圍者加總(SUMPRODUCT)

【公式設計與解析】
選取儲存格A1:C21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:起日、迄日、金額。
儲存格F2:=SUMPRODUCT((起日<=E2)*(迄日>=E2)*金額)
在 SUMPRODUCT 函數中,利用二個條件來判斷E欄的日期是否位於某個日期區間中,其中 (起日<=E2)*(迄日>=E2) 的『*』,相當於執行邏輯 AND 運算,會將條件判斷的傳回值 TRUE/FALSE 轉換為 1/0,再和金額陣列執行「乘積和」運算。

【延伸學習】
如何將金額欄位中含有指定日期者顯示為紅色?(以儲存格E4為例)
1. 選取儲存格C1:C21。
2. 進入設定格式化的條件的對話框,並設定:
類型:使用公式來決定要格式化哪些儲存格。
規則:=(A2<=$E$4)*(B2>=$E$4)
Excel-位於日期範圍者加總(SUMPRODUCT)

沒有留言:

張貼留言

檢視其他文章

好康東東