2017年4月24日 星期一

Excel-分時小計(SUMPRODUCT,TIME)

網友問到:在 Excel 中有一個時間的清單,如何分時小計?
在下圖中,每個時間有一個對應的數值,如果要以每一個小時為單位,來計算總和,該如何處理?
分時小計(SUMPRODUCT,TIME)

【公式設計與解析】
假設本題中的數值欄位的儲存格內容遠大於 1。
儲存格I2:=SUMPRODUCT((A2:E21>=TIME(7+ROW(1:1),0,0))*(A2:E21<=
TIME(8+ROW(1:1),0,0))*B2:F21)
複製儲存格I2,貼至儲存格I2:I11。
(1) 條件一:A2:E21>=TIME(7+ROW(1:1),0,0)
在 SUMPRODUCT 函數中判斷儲存格內容是否大於或等於某一時數(本例為8:00),傳回 TRUE/FALSE 陣列。
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ …。
(2) 條件二:A2:E21<=TIME(8+ROW(1:1),0,0)
在 SUMPRODUCT 函數中判斷儲存格內容是否小於或等於某一時數(本例為9:00),傳回 TRUE/FALSE 陣列。
(3) SUMPRODUCT((條件一)*(條件二)*B2:F21)
公式中的『*』運算,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再和儲存格B2:F21相乘,相當於取出合於條件的「數值」。最後,透過 SUMPRODUCT 函數予以加總。
 

沒有留言:

張貼留言

檢視其他文章

好康東東