2016年12月8日 星期四

Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

同時有二個讀者問到類似的問題。在下圖左是一個 Excel 清單,如何計算日期區間中各個項目的數量小計(如下圖右)?
Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

【公式設計與解析】
選取儲存格A1:C29,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目、數量。
儲存格F5:=SUMPRODUCT((日期>=$F$1)*(日期<=$F$2)*(項目=E5)*數量)
條件一:(日期>=$F$1)*(日期<=$F$2)
設定日期區間條件,其中『*』運算子相當於執行邏輯 AND 運算,兩個條件都成立時才會傳回 TRUE,否則傳回 FALSE。即日期介於儲存格F1和儲存格F2之間者才會傳回 TRUE
條件二:(項目=E5)
項目陣列中和儲存格E5相同者傳回 TRUE,否則傳回 FALSE
在 SUMPRODUCT 函數中執行『*』運算,會將 TRUE/FALSE 轉換為 1/0
參考以下的示意圖:
Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

沒有留言:

張貼留言

好康東東