Excel数据透视表线性时间标度

本文介绍了Excel数据透视表线性时间标度的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个数据集（〜10000行），格式如下：

I have a data set (~10000 rows) with the following form:

+---------------------------+---------------+-------------+ | DateTimeCreated | Machine | ProductName | +---------------------------+---------------+-------------+ | 2009-03-03 00:00:12.217 | COMP001 | Product001 | +---------------------------+---------------+-------------+

我已使用数据透视表以及具有以下布局的图表

I have graphed this using a pivot table and chart with the following layout

Page: ProductName Column: Machine Row: DateTimeCreated - Group by Day, Hour, Minute Data: Count of DateTimeCreated

的结果图不是线性的。 对于我跟踪的事件没有发生的那一天的分钟，这些分钟不会显示在图表上。我跟踪的事件也可以在没有发生的情况下进行，导致更加非线性的图形。

This all works correctly however the timescale of the resulting graph is not linear. For those minutes of the day where the event I am tracking did not occur these minutes do not show on the graph. The event I'm tracking also can go hours without occurring which results in an even more non-linear graph.

这里尝试说明我的x轴图表：

Here is an attempt to illustrate the x-axis of my graph:

|09| 10 | 11 | 12 |14| 16 | 18 | 20 |23|

所需的结果是：

|00|01|02|03|04|05|06|07|08|etc

所以每个小时是相同的大小，并显示即使事件没有发生（所以在我的线图，线简单地变为0一段时间）

So each hour is the same size and is shown even if the event did not occurr (so on my Line graph the line simply goes to 0 for a while)

这可以实现吗？

推荐答案

我改变了我的数据集看起来像这样

I changed my data set to look like this

+---------------------------+---------------+-------------+-------+ | DateTimeCreated | Machine | ProductName | Count | +---------------------------+---------------+-------------+-------+ | 2009-03-03 00:02:00.000 | COMP001 | Product001 | 2 | +---------------------------+---------------+-------------+-------+

所以查询是按分钟对事件进行分组并在分钟中提供计数

so that the query is grouping the events by minute and providing the count in the minute

然后，我生成了第二个数据集，其中包含每天每分钟的条目，但是计数为0 由于我只使用24小时数据集，因此这不是不合理的

I then generated a second data set that contained an entry for every minute of the day but with a count of 0 As I was only working with 24hr data sets this was not unreasonable

组合两个数据集在枢轴表上提供了线性时间表。

Combining the two data sets gives a linear timeline on the pivot chart.