☰

如何在 Excel 中對多列進行“SUM”

tag-icon • tag-icon microsoft-excel worksheet-function date-time
如何在 Excel 中對多列進行“SUM”

我有一個逗號分隔的文件，其中包含兩列日期/時間（以 Excel 的mm/dd/yyyy hh:mm自訂格式匯入）和狀態 1 或 0。我正在嘗試產生一個圖表，顯示每天的上升時間和下降時間。

考慮：

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

這告訴我，設備運行了三個小時，關閉了十一個小時，然後運行了三十四小時（跨兩個日曆日）。但是，我想產生一個圖表來顯示我們每天上升或下降的小時數。

考慮：

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

對我來說，我似乎需要生成一個資料集，按日曆日按狀態匯總小時......但我似乎找不到資料透視表或嵌套組合SUM(IF(SUMIF(...)))的風格來使其工作。

最麻煩的是考慮日期變化...在上面的範例中，由於我的正常運行時間從2012 年1 月1 日14:00 開始跨越午夜，所以我需要知道10 個正常運行時間是2012 年1 月1 日和24 小時的總和正常運作時間總計為 2012 年 1 月 2 日。

我也許可以用日曆列表做一些事情來驅動日期求和，但是我需要一種方法來比較01/01/2012相等01/01/2012 03:00。一定有辦法，if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0)但到目前為止還沒有任何效果。

有什麼建議麼？我一整天都在與這個問題作鬥爭，需要一個新的視角。

謝謝

答案1

天哪，這太有挑戰性了！我希望我能更好地解釋這一點，但請耐心等待。

我的解決方案需要一些額外的空間（準確地說是 3 列），並且基於一些假設：

  1. 時間戳資料將具有交替的 1 和 0 行，模擬您所描述的開/關開關（請參閱下圖 1）。
  2. 時間戳按從最舊到最新的順序排列。

在此輸入影像描述

為了簡單起見，我使用了整個小時。不過，如果您願意，可以添加分鐘時間戳記。

E欄追蹤每個時間戳當天剩餘的小時數。我用它來「分割」經過午夜的時間。

開/關列

以下是 ON 列中的公式：

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

此公式將兩個值相加：

1.如果條目不是當天的最後一個時間戳，則開關打開的小時數；否則，0。

2.剩餘小時數直到午夜，如果這是最後的當日的時間戳或者已過的小時數自從午夜，如果這是第一的當日的時間戳記。

OFF 列中的公式類似，但它檢查 Status 是否 = 0。

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

為了從時間戳列中提取日期，我使用了該INT()函數。 Excel 使用整數表示日期，使用小數表示時間（一天或 24 小時的一小部分）。例如，06/01/2012 18:00等於41061.75（午夜過後 18 小時為 18/24，即 0.75）。我相信這回答了你的最後一段。

鞏固時間

這是公式中的營業時間列（見下圖）。這是一個陣列公式，所以需要使用Ctrl++輸入Shift，Enter然後複製下來：

=SUM((INT(stamps)=$G3)*hours_on)

在裡面休息時間柱子：

=SUM((INT(stamps)=$G3)*hours_off)

其中
stamps是指的命名範圍時間戳範圍（列A在我的範例中）
hours_on是一個命名範圍，指的是在範圍（列C)
hours off 是一個命名範圍，指的是離開範圍（列D）

圖表

請注意，對於每個日期，ON 和 OFF 小時數總計為 24。

在此輸入影像描述

如果您想學習公式和工作簿，這裡有一份副本：http://db.tt/KZgH7SFV

答案2

假設您按照 Kaze 的建議在 A3:A24 中有時間戳，在 B3:B24 中有狀態，那麼您可以使用ON以下公式獲取 D3 中日期的[十進制] 小時數

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

不需要「助手」列......

答案3

這並不漂亮，但這裡有一個解決方案：

在兩列之後新增第三列，定義為 day([datecolumn])

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

然後您可以將總和列定義為

sumif(D1:D99, 1, C1:C99)

在上面的例子中，僅當 D 列（之前定義為日期時間的“日”部分）為 1 時，才會顯示 1 的總和。熱衷），你應該有你的價值觀。

當月 1 日的時數總和

sumif(D1:D99, 1, C1:C99)

該月 2 日的時數總和

sumif(D1:D99, 2, C1:C99)

本月 3 日的總小時數

sumif(D1:D99, 3, C1:C99)

等等...

相關內容