☰

使用 Excel 2010 数据透视表将并发开始和结束时间透视为时间段

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table
使用 Excel 2010 数据透视表将并发开始和结束时间透视为时间段

我有一个进程表及其各自的开始和结束时间（根据开始时间和持续时间计算），按进程 ID 列出：

Run ID  Duration    Start Time  End Time
200878  464     5/16/11 4:15    5/16/11 11:59
200879  76      5/16/11 4:22    5/16/11 5:38
200880  165     5/16/11 6:29    5/16/11 9:14
200881  44      5/16/11 9:44    5/16/11 10:28

我需要一种方法来将数据透视到“时间段”，以便使用 Excel 的内置数据透视表计算每个小时运行的进程数。

对于上面的数据摘录，我想要此图表：

Timeslot Start  Timeslot End    Running Processes
5/16/11 3:00    5/16/11 4:00    0
5/16/11 4:00    5/16/11 5:00    2
5/16/11 5:00    5/16/11 6:00    2
5/16/11 6:00    5/16/11 7:00    2
5/16/11 7:00    5/16/11 8:00    2
5/16/11 8:00    5/16/11 9:00    2
5/16/11 9:00    5/16/11 10:00   3
5/16/11 10:00   5/16/11 11:00   2
5/16/11 11:00   5/16/11 12:00   1

我目前的解决方法是使用 SUMIFS 函数来计算适合每个时间段的行数。这实际上报告了我想要的精确统计数据，但有以下缺点：

  • 必须手动输入“时间段开始”时间，并扩展以覆盖原始表中所示的时间范围。
  • Excel 原生数据透视表的所有过滤机制均不起作用，例如切片器或按其他参数排列（我的实际数据除了“运行 ID”外还有其他字段，为简洁起见，已将其删除）

因此，如果可能的话，我希望通过某种方式获取带有“真实”数据透视表的相同数据。

我也尝试过在数据透视表中按小时/天/月/年添加分组，但即使使用“显示没有数据的项目”选项，由于流程可能持续一个多小时，因此会导致报告不足（跨越特定时间段但没有在该时间段内开始或结束的流程不会被计入该时间段。）

为了方便您进行实验，这里有一个 xlsx 文件，其中包含一些数据和我自己基于 SUMIFS 的数据透视表：http://dl.dropbox.com/u/123900/timeslot%20pivot.xlsx

答案1

说实话，我看不出有办法用内置的 Excel 数据透视表来处理这个问题。但是，您可以创建自己的表来显示所需的数据。您唯一需要手动做的就是创建您想要进程计数的槽开始和结束时间列。一旦有了这些，就可以使用数组公式来获取进程计数。

=COUNT(IF((H2>=$C$2:$C$50)*(H2<=$D$2:$D$50)+(I2>=$C$2:$C$50)*(I2<=$D$2:$D$50)>0,1,""))

按 Ctrl+Shift+Enter 将其作为数组公式输入。H 列包含时间段开始，I 列包含时间段结束，C 列包含进程表中的开始时间，D 列包含进程表中的结束时间。请注意，您可以将公式设置为远远超出进程表底部的范围，因此如果进程列表不断变化，您的计数公式仍将返回正确的结果。

相关内容