☰

如果相鄰儲存格不為空，則每隔一個儲存格求和

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
如果相鄰儲存格不為空，則每隔一個儲存格求和

我在 Excel 中建立了一個時間表來追蹤我的工作時間。一切都很好，但我有一個小問題。

我的工作表具有以下佈局（In=開始工作，Out=停止工作）：

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 | 13:30 | 13:45 | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 | 17:30 |       |
...

現在我想計算一下我每天工作的時間。這很容易：

= SUM($C2; $E2; $G2) - SUM($B2; $D2; $F2)

如果我有時不吃午餐，這甚至會起作用：

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 |       |       | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 | 17:30 |       |
...

但白天輸入開始工作時間（ ）時不起作用In，但尚未輸入停止工作時間（Out）：

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 |       |       | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 |       |       |
...

因為06.03.2014這會導致負時間。

我想要的是我早上實際工作的時間，04:30即06.03.2014。這意味著，我只需將那些相鄰列不為空的列包含到第二個（負）總和中。我怎麼能以通用的方式做到這一點，同時仍然支援跳過午餐時間？我所說的「一般方式」是指一個公式，它也適用於 F、G 列，也可能適用於 H、I 列……所以它不應該是一個冗長的IF語句公式。

我想使用SUMIForSUMIFS函數，但據我了解，它只接受類似 的範圍B1:G1，但不接受類似 的範圍B1;D1;F1，即跳過每隔一列。我不知道如何做到這一點，因為我根本不是 Excel 專家...這是我到目前為止所嘗試的，但它不起作用，它只給了我#VALUE：

= SUM($C2; $E2; $G2) - SUMIF(($C2; $E2; $G2); "<>"; ($B2; $D2; $F2))

這應該意味著：如果 C、E 和 G 中的對應單元格不為空，則對 C、E 和 G 求和並減去 B、D 和 F 的總和。然而，範圍周圍的括號似乎不起作用。或是問題是別的什麼？

答案1

我從 Madball 的方法中獲得了一些靈感，但不需要額外的一行。將其用於向下複製的第 2 行

=C2+E2+G2-B2-D2-F2+MOD(COUNT(B2:G2),2)*MAX(B2:G2)

它根據需要添加/減去時間，但添加後退插入奇數次時的最新時間（因此忽略它）。

答案2

這就是我要做的事。如果插入更多列，則可以輕鬆調整公式：

  1. 在第一行增加一系列 1,-1,1,-1... 這樣做是為了讓我們知道它是加法還是減法。
  2. 在最右邊（範例中的 H），使用下列公式： =IF(ISODD(COUNT(B3:G3)),SUMPRODUCT($B$1:$G$1,B3:G3)+MAX(B3:G3),SUMPRODUCT($B$1:$G$1,B3:G3))

這在您的範例中適用於每種情況（除了奇怪的事情，例如連續離開兩次）： 在此輸入影像描述

說明：SUMPRODUCT 將陣列中的每個單元格與另一個陣列中的對應單元格相乘，然後將它們全部加在一起。因此，在 3/6 上，它會執行 8*-1+12:30*1+13:00*-1+17:30*1+0*-1+0*1。

如果有奇數個進/出/，它會加回最後（最高）的次數來抵消它。

答案3

我不確定我完全理解你的問題，但這可行

在此輸入影像描述

我使用了一個簡單的 if 語句

=SUM(IF(C2<>"",C2-B2,0),IF(E2<>"",E2-D2,0))

這甚至可以完成部分天的工作（例如第 4 行只工作了一個下午）。

如果您要新增第三組輸入/輸出，請根據

=SUM(IF(C2<>"",C2-B2,0),IF(E2<>"",E2-D2,0),IF(G2<>"",G2-F2,0))

答案4

你嘗試替換這個怎麼樣：

= SUM($C2; $E2; $G2)

有了這個：

= SUM(IF($C2="";TEXT(NOW(); "hh:mm");$C2); IF($E2="";TEXT(NOW(); "hh:mm");$E2); IF($G2="";TEXT(NOW(); "hh:mm");$G2))

此公式應將當前時間放入空白單元格（$C2、$E2、$G2）中，這將為您提供更多實際工作時間。畢竟，你一直工作到現在，不是嗎？

相關內容