Excel - 每30分钟间隔汇总连续数据

Excel - 每30分钟间隔汇总连续数据

我从雨量计收集了以下格式的数据:

索引数据时间戳

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")

在此处输入图片描述

分为几个步骤:

  1. 要让 Excel 将文本识别为日期/时间,请删除 T:

    =SUBSTITUTE(C2,"T"," ")

  2. 要获取该日期/时间的日期:

    =日期值(D2)

  3. 要获取该日期/时间的时间:

    =ROUND(时间值(D2),5)

(下面我将解释为什么这里需要 ROUND)

  1. 计算下一个 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 函数。

要在没有数据透视表的情况下进行聚合,您可以执行以下操作(假设您没有预先存在的增量列表):

  1. 计算 MIN TimestampGroup 和 MAX Timestamp Group 之间的 30 分钟增量数
  2. 创建一个包含那么多增量的序列(以便源数据中没有的增量也能被表示出来)
  3. 使用 SUMIF 引用源数据并按 TimestampGroup 对数据求和
  4. (可选)为聚合表创建 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 指示的行的数据列求和。

相关内容