網友問到:在 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 函數予以加總。

文章標籤
創作者介紹

學不完.教不停.用不盡

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