EXCEL中的滑动时间窗使用

之前参加一个测试的时候，遇到一个试题，说怎么样计算在过去24小时时间内的销售数据。注意这里是过去的24小时，不是过去的一天也不是昨天。这里就是一个滑动时间窗，然后对滑动时间窗内部的数据进行一个聚合运算。

先看初始数据：

Date	Hour	Amt
2016-02-18	11	50
2016-02-18	14	30
2016-02-18	19	20
2016-02-18	7	100
2016-02-18	10	40
2016-02-18	19	20
2016-02-18	21	60

这里我们有当天的小时时间，但是没有完整的datetime时间，所以需要 转换一下：

1. 使用下面的命令新建一个列DateTime：

=TEXT(G25,"yyyy-mm-dd ")&TEXT(H25&":00:00","hh:mm:ss")

Date	Hour	DateTime	Amt
2016-02-18	11	2016-02-18 11:00:00	50
2016-02-18	14	2016-02-18 14:00:00	30
2016-02-18	19	2016-02-18 19:00:00	20
2016-02-18	7	2016-02-18 07:00:00	100
2016-02-18	10	2016-02-18 10:00:00	40
2016-02-18	19	2016-02-18 19:00:00	20
2016-02-18	21	2016-02-18 21:00:00	60

2. 上一步之后，我们需要了解怎么做时间窗，好，我们再新建一列，叫做过去一天之前的现在时间 YesTime：

=DateTime-1，同时保证DateTime是时间，可以Ctrl+c，然后alt+v+v，直接赋值，如果没有最后am或者pm的后缀就使用 =DateTime-0

Date	Hour	DateTime	YesTime	Amt
2016-02-18	11	2016-02-18 11:00:00	2016-02-17 11:00:00	50
2016-02-18	14	2016-02-18 14:00:00	2016-02-17 14:00:00	30
2016-02-18	19	2016-02-18 19:00:00	2016-02-17 19:00:00	20
2016-02-18	7	2016-02-18 07:00:00	2016-02-17 07:00:00	100
2016-02-18	10	2016-02-18 10:00:00	2016-02-17 10:00:00	40
2016-02-18	19	2016-02-18 19:00:00	2016-02-17 19:00:00	20
2016-02-18	21	2016-02-18 21:00:00	2016-02-17 21:00:00	60

3. 然后我们使用YesTime作为时间窗的threshhold去做一个sum

=SUMIFS($G$51:$G$57,$E$51:$E$57,">"&F52, $E$51:$E$57,"<="&E52)

其中$G$51:$G$57 是Amt列，$E$51:$E$57是DateTime列，F52是昨天的同一时间，E52是现在。

Date	Hour	DateTime	DateTime-0	YesTime	Amt	SumRoll
2016-02-17	11	2016-02-17 11:00:00	2016-02-17 11:00:00	2016-02-16 11:00:00	50	150
2016-02-17	14	2016-02-17 14:00:00	2016-02-17 14:00:00	2016-02-16 14:00:00	30	180
2016-02-17	19	2016-02-17 19:00:00	2016-02-17 19:00:00	2016-02-16 19:00:00	20	200
2016-02-17	7	2016-02-17 07:00:00	2016-02-17 07:00:00	2016-02-16 07:00:00	100	100
2016-02-18	10	2016-02-18 10:00:00	2016-02-18 10:00:00	2016-02-17 10:00:00	40	140
2016-02-18	19	2016-02-18 19:00:00	2016-02-18 19:00:00	2016-02-17 19:00:00	20	60
2016-02-18	21	2016-02-18 21:00:00	2016-02-18 21:00:00	2016-02-17 21:00:00	60	120

OK。点到为止。

谢谢