![Excel - 每30分钟间隔汇总连续数据](https://linux22.com/image/1627464/Excel%20-%20%E6%AF%8F30%E5%88%86%E9%92%9F%E9%97%B4%E9%9A%94%E6%B1%87%E6%80%BB%E8%BF%9E%E7%BB%AD%E6%95%B0%E6%8D%AE.png)
我从雨量计收集了以下格式的数据:
索引数据时间戳
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 指示的行的数据列求和。