☰

Excel 数据透视表日期范围作为列

tag-icon • tag-icon microsoft-excel microsoft-excel-2003 pivot-table
Excel 数据透视表日期范围作为列

我有以下想要旋转的数据：

学生加入日期释放日期
约翰 01/05/2011 05/05/2011
詹姆斯 02/05/2011 04/05/2011

我想创建一个数据透视表，显示给定日期范围内的学生人数：

2011 年 5 月 1 日 2011 年 5 月 2 日 2011 年 5 月 3 日 2011 年 5 月 4 日 2011 年 5 月 5 日
1 2 2 2 1

现在，我遇到的问题是，我希望数据透视表能够以某种方式扩展日期，但我不知道如何使用我当前的数据结构来实现这一点。

我可以用以下结构来实现它：

学生加入日期释放日期枢轴日期
约翰 01/05/2011 05/05/2011 01/05/2011
约翰 01/05/2011 05/05/2011 02/05/2011
约翰 01/05/2011 05/05/2011 03/05/2011
约翰 01/05/2011 05/05/2011 04/05/2011
约翰 01/05/2011 05/05/2011 05/05/2011
詹姆斯 02/05/2011 04/05/2011 02/05/2011
詹姆斯 02/05/2011 04/05/2011 03/05/2011
ETC...

但是，考虑到所使用的数据量，这种技术的扩展性不是很好，我很容易就达到工作簿中单个工作表的最大行数。

有谁能建议我如何创建所需的数据透视表，而不需要为学生“活跃”的每个日期设置一行？

答案1

如果您愿意尝试不涉及数据透视表的解决方案：

删除“数据透视表日期”列。在标题中排列日期范围，并使用和（）公式来确定该日期是否在学生的任期内。您的数据将如下所示：

在此处输入图片描述

以下是公式D2看起来像（注意绝对引用）。只需将公式拖放或复制到其余的列和行，自动填充应该会处理其余的事情：

=AND(D$1>=$B2,D$1<=$C2)+0

数字格式设置为："+";;

每个日期范围内学生总数的公式非常简单。以下是D26多于。

=sum(D2:D25)

要添加列，只需复制最后一列并更改上面的日期。要添加学生，请在最后一名学生上方插入一个空白行（在本例中为上方）女妖）。录制宏，以便您更轻松地更新数据。这样，您就不必手动调整和底行的公式。您可以按学生姓名、加入日期或释放日期对数据进行排序；右侧的公式应该没问题。

这是上面显示的电子表格的副本（需要 MS Excel 2007）：http://ge.tt/46auqAN

答案2

您可以使用数组公式获取这些计数。根本不需要 Pivot。

假设 JoinDate 在 中column B，ReleaseDate 在 中column C，并且此示例仅包含两行数据。
要求和的日期在 中columns E：公式在 中E2。输入数组公式（按Ctrl-Shift-Enter而不是直接按 Enter 来完成公式）

=SUM(((($B$2:$B$4<=E$1)*1+($C$2:$C$4>=E$1)*1)=2)*1)

复制第 2 行，列出需要总计的每个日期

如果对日期数据使用命名范围，公式看起来会更好，也更容易维护

=SUM((((JoinDate<=E$1)*1+(ReleaseDate>=E$1)*1)=2)*1)

日期范围可以超出现有数据，以允许添加更多数据。例如，$B$1:$B$100
整列（$B:$B）也可以，但会减慢重新计算的速度

顺便说一句，如果你使用Excel 2007或者后者有一个新的函数可用（作为常规公式输入，不需要数组）：

=COUNTIFS(JoinDate,"<="&E$1,ReleaseDate,">="&E$1)

答案3

可能不适合你的应用程序，但你应该尝试 MS Access 中比 Excel 更出色的 Pivot 函数

相关内容