☰

如果相邻单元格不为空，则对每个第二个单元格求和

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语句公式。

我想使用SUMIF或SUMIFS函数，但据我所知，它只接受像这样的范围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)，这样可以为您提供更多实际工作时间。毕竟，您一直工作到现在，不是吗？

相关内容