我从雨量计收集了以下格式的数据:
索引数据时间戳
1. 0 2020-01-05T00:00:00
2. 0 2020-01-05T01:00:00
3. 1 2020-01-05T01:20:15
4. 0 2020-01-05T02:00:00
5. 1 2020-01-05T02:09:00
6. 1 2020-01-05T02:09:45
7. 1 2020-01-05T02:20:00
8. 1 2020-01-05T02:20:01
9. 0 2020-01-05T03:00:00
我想每 30 分钟汇总一次连续数据。如下所示:
索引数据时间戳
1. 0 2020-01-05T00:00:00
2. 0 2020-01-05T00:30:00
3. 0 2020-01-05T01:00:00
4. 1 2020-01-05T01:30:00
5. 0 2020-01-05T02:00:00
6. 4 2020-01-05T02:30:00
7. 0 2020-01-05T03:00:00
我已经从时间戳中获得了 24 小时的时间,并尝试根据以下公式计算减去 2 个时间戳是否小于 30 分钟:
=IF(((time)-(time-1))<=0.020833,"ok","not within 30min interval")
我想到的 Excel 公式是获得一个函数,SUMIFS
该函数将 30 分钟间隔内的所有连续值相加
我试图采取某种方法COUNTIF
来检查 30 分钟间隔内的连续数据,并试图找出一个SUMIFS
和一个SUMPRODUCT
函数,但我很难将所有东西整合在一起并符合逻辑。
谢谢
答案1
您可以使用以下公式:
=DATEVALUE(SUBSTITUTE(C2,"T"," "))+CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")
分为几个步骤:
要让 Excel 将文本识别为日期/时间,请删除 T:
=SUBSTITUTE(C2,"T"," ")
要获取该日期/时间的日期:
=日期值(D2)
要获取该日期/时间的时间:
=ROUND(时间值(D2),5)
(下面我将解释为什么这里需要 ROUND)
计算下一个 30 分钟增量:
=CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")
然后将日期与 30 分钟增量连接起来,即可得到答案顶部的公式。然后,您可以透视表以按增量求和。
请注意步骤 3 中对 ROUND 的调用:
我第一次编写公式时,没有在步骤 3 中使用 ROUND,但我发现 2:00:00 的增量是 2:30:00,这是不对的。经检查,我发现 TIMEVALUE 函数没有返回正确的精度级别。四舍五入到小数点后 5 位解决了这个问题。
编辑:
由于您拥有 Excel 365,我假设您也可以访问 SEQUENCE 函数。
要在没有数据透视表的情况下进行聚合,您可以执行以下操作(假设您没有预先存在的增量列表):
- 计算 MIN TimestampGroup 和 MAX Timestamp Group 之间的 30 分钟增量数
- 创建一个包含那么多增量的序列(以便源数据中没有的增量也能被表示出来)
- 使用 SUMIF 引用源数据并按 TimestampGroup 对数据求和
- (可选)为聚合表创建 GroupIndex
汇总表中的公式为:
组索引:
=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1)
这只是告诉您最小的 TimestampGroup 和最大的 TimestampGroup 之间有多少个 30 分钟的增量,并返回这些增量的整数序列。
时间戳组:
=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1,1,MIN($D$3:$D$11),1/48)
再次使用序列函数,返回 TimestampGroups 列表,但不是从 1 开始序列并以 1 为增量(这是默认的第 3 和第 4 个参数),而是从最小 TimestampGroup 开始序列并以 30 分钟为增量,即一天的 1/48。
价值:
=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)
对聚合表当前行上 TimestampGroup 指示的行的数据列求和。