☰

我应该如何设计时间序列数据以便于 Excel 数据透视表使用？

tag-icon • tag-icon microsoft-excel pivot-table
我应该如何设计时间序列数据以便于 Excel 数据透视表使用？

我有一个 Excel 电子表格，其中包含几年的每日数据，其形式如下：

日期 | 类别1 | 类别2 | ... | 类别30
------------------------------------------------------------
dd/mm/yyyy | 值 | 值 | ... | 值
... | ... | ... | ... | ...
------------------------------------------------------------

我还有以下形式的列表，其中第一个表中的每个类别都分配了五种类型描述之一：

| 类型 | 类别 |
----------------------
| 类型1 | 类别1 |
| 类型2 | 类别2 |
| 类型2 | 类别3 |
| ... | ... |
| 类型5 | 类别30 |
----------------------

我希望能够使用数据透视表来组织和汇总这些数据，并使用数据透视图来绘制时间序列。但是，由于数据的制表方式，我无法按类别、类型或连续月份对它们进行分组（我感兴趣的是按月/年绘制数据图表，而不是仅按跨年的月份对它们进行分组）。

如果我按照下表的顺序排列数据，那么所有的问题都可以解决，但是我会重复几列的值，次数与类别数一样多（我非常反对重复数据）：

日期 | 年 | 月 | 日 | 类型 | 类别 | 值
--------------------------------------------------------------------------
日期(i) | yyyy | mmm | dd | 类型(j) | 类别(k) | 值(i,j,k)
--------------------------------------------------------------------------

我的问题是，我应该如何设计我的表格，以便我可以快速用数据透视表处理数据，而不必复制所有其他信息？

答案1

为了使数据透视表按预期工作，您必须像数据库一样排列数据：一行是一条记录，一列是提供该记录信息的字段。

首先，根据我对情况的理解，多个列包含特定日期的多个值似乎是错误的。这意味着将许多记录（值）塞进一条记录中。根据定义，为不同类别设置多个列会阻止您在数据透视表中有意义地使用这些数据。

如果您可以永久修改源数据，以下是建议的布局：

| 日期 | 类别 | 价值
 | 2011 年 1 月 1 日 | 1 | x
 | 2011年1月1日 | 2 |是
 | 2011 年 1 月 1 日 | 25 | z
 | 2011 年 1 月 2 日 | 1 | x

您可以使用 VLOOKUP 公式将 TYPE 附加到其中，该公式会为您的类别获取正确的类型，以避免重复并自动反映 TYPE 表中的任何更改。（请注意，这假设您的类型表中有倒排列：CATEGORY | TYPE，以便 VLOOKUP 正常工作）

| 日期 | 类别 | 类型 | 价值
 | 2011 年 1 月 1 日 | 1 | VLOOKUP(B2, 'TypeTable', 2, 0) | x
 | 2011 年 1 月 1 日 | 2 | VLOOKUP(C2, 'TypeTable', 2, 0) | y
 | 2011 年 1 月 1 日 | 25 | VLOOKUP(D2, 'TypeTable', 2, 0) | z
 | 2011 年 1 月 2 日 | 1 | VLOOKUP(E2, 'TypeTable', 2, 0) | x

这样的表格会包含很多行，但将其提供给数据透视表将允许您以各种方式对其进行操作：按日期、按类别、按类型。

事实上，您的源数据将是按日期汇总值的数据透视表的良好结果，列标签为类别！上帝知道将数据透视表结果转换回可用于制作另一个数据透视表的数据可能很困难。解决方案包括手动操作、宏或一堆中间公式。

希望有所帮助。

相关内容