☰

Excel - 30 分間隔ごとに連続データを合計する

tag-icon • tag-icon microsoft-excel worksheet-function
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の式は、SUMIFS30分間隔内のすべての連続した値を合計する関数のようなものを取得することでした。

30 分間隔内で連続したデータをチェックする何らかの方法を導入し、関数COUNTIFを考案しようとしましたが、物事をまとめてロジックを構築するのに苦労しています。SUMIFSSUMPRODUCT

ありがとう

答え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(TIMEVALUE(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タイムスタンプグループとMAXタイムスタンプグループ間の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 のリストを返しますが、デフォルトの 3 番目と 4 番目のパラメーターである 1 からシーケンスを開始して 1 ずつ増加させるのではなく、最小の TimestampGroup からシーケンスを開始して 30 分 (1 日の 48 分の 1) ずつ増加させます。

価値：

=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)

集計テーブルの現在の行の TimestampGroup によって示される行のデータ列を合計します。

関連情報