☰

如何在 Excel 中对多列进行“求和”

tag-icon • tag-icon microsoft-excel worksheet-function date-time
如何在 Excel 中对多列进行“求和”

我有一个逗号分隔的文件，其中包含两列日期/时间（导入为 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

这告诉我设备运行了 3 个小时，停机了 11 个小时，然后又运行了 34 个小时（跨越两个日历日）。但是，我想生成一个图表来显示每天我们运行或停机了多少小时。

考虑：

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

对我来说，似乎我需要生成一个按日历日按状态对小时数求和的数据集......但我似乎找不到数据透视表或嵌套SUM(IF(SUMIF(...)))组合来使其工作。

最麻烦的是考虑日期的变化......在上面的例子中，由于我的正常运行时间从 2012 年 1 月 1 日 14:00 开始跨越午夜，我需要知道 2012 年 1 月 1 日总计 10 个正常运行小时，2012 年 1 月 2 日总计 24 个正常运行小时。

我可能能够使用日历列表来驱动日期求和，但之后我需要一种方法来比较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输入它，然后将其复制下来：ShiftEnter

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

在里面休息时间柱子：

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

其中
stamps是指向时间戳范围（列A在我的例子中）
hours_on是一个命名范围，指的是在范围（列C）
hours 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)

等等...

相关内容