Excel数据透视表 - 重叠的日期范围 [英] Excel Pivot Table - overlapping date ranges

问题描述

我有一组数据与日期相关联：

1 Mar a

2 Mar b

3 Mar c

4月3日d $
5 Mar e

6 Mar f

尽管数据透视表将允许日期范围分组），这些只是不同的范围：

1-3三月

4-6三月

我想要做的是让分组重叠：

1-3 Mar

2-4 Mar

3-5 Mar

4-6三月

对于Excel 2010来说这是可能的。这一切的重点是找出不同条目的数量（a，b，c，d，e，f）在某一滚动日期范围内，因为某些条目将在不同的日期落入和退出范围。

解决方案

您将不得不修改SQL查询来执行此操作。我相信你必须使用几个UNION ALL查询来重复两次主查询。在第一个UNION ALL位中，从日期减去一个。在第二个UNION ALL位中，从日期减去2。生成的数据（如果以表格形式返回）并以示例数据为例，将如下所示：

不需要做任何分组：根据这些UNION全部重复的内容，它实际上已经被分组成你想要的。忽略这些数字的价值...这是有兴趣的数字的存在或不存在。其实你甚至可以申请一个自定义数字格式的Yes;;;这将返回以下内容：

< img src =https://i.stack.imgur.com/8uNJP.pngalt =在此输入图像说明>

I have a set of data with dates associated:
1 Mar a
2 Mar b
3 Mar c
4 Mar d
5 Mar e
6 Mar f

While a pivot table will allow date ranges (groupings) to be set up, these are only distinct ranges:
1-3 Mar
4-6 Mar

What I would like to be able to do is have the groupings overlap:
1-3 Mar
2-4 Mar
3-5 Mar
4-6 Mar

Is this possible for Excel 2010. The whole point of this is to find the number of distinct entries (a,b,c,d,e,f) in a rolling date range as some entries will fall into and out of the range at different days.

解决方案

You're going to have to amend the SQL query to do this. I believe you'll have to use a couple of UNION ALL queries to duplicate the 'main' query twice. In the first UNION ALL bit, subtract one from the date. In the second UNION ALL bit, subtract 2 from the date. The resulting data - if returned as a Table, and using your sample data as an example - would look like this:

When you Pivot it, put Date in the ROWS pane, filter out the first two dates for the 28th and 29th of Feb, put Item in the COLUMNS pane, and put Item again in the Values area, like so:

No need to do any grouping: it is effectively already grouped how you want it on account of those UNION ALL duplicates. Ignore the values of those numbers...it is the presence or absence of a number that is of interest. In fact, you can even apply a custom number format of "Yes";;; that will return the following:

这篇关于Excel数据透视表 - 重叠的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！