Excelでデータを時間帯別テーブルにマッピングする

はじめに

データの集計を行う際によく使う「時間帯別でどのデータがどれくらいあったのか」について、集計の方法について記載しておく。
※1回作ってしまえば後はデータを集めるだけなので便利

環境

Windows 11 Professional
Excel 2016

やりたいこと

下記のデータを使って

data-sheet-01

以下の時間帯別の集計テーブルを作ること

complete-01

概要

まずどのようなデータを、どのように加工するのかを見ておこう。

集計対象データの形式

データは以下の通りとなる。

集計対象データ構造

項目名データ型説明
時間yyyy/m/d hh:mm:ss (文字列)商品の記録日時2024/12/13 00:30:00
商品文字列商品名りんご
価格数値商品の価格(円単位)300

集計対象データ仕様

  • 時間 (yyyy/m/d hh:mm:ss)

    • 年月日 (yyyy/m/d) と時分秒 (hh:mm:ss) を含む形式
    • 例: 2024/12/13 00:30:00
    • 24時間表記 (0:00:0023:59:59)
  • 商品 (文字列)

    • 商品名は りんご, みかん, バナナ など
    • 任意の日本語商品名が入る可能性あり
  • 価格 (数値)

    • 商品の価格(単位:円)
    • 最小値・最大値の制約は特になし(例: 150, 300, 500 など)

集計対象サンプルデータ

時間商品価格
2024/12/13 00:30:00りんご300
2024/12/13 12:45:00みかん150
2024/12/14 08:10:00りんご500
2024/12/14 22:30:00バナナ200
2024/12/15 01:00:00りんご400

加工後のデータの形式

加工後のデータは以下の通りとなる。

データ構造

項目説明
日付日付形式 (yyyy/m/d 形式)集計対象日。例: 2024/12/13
平均価格数値各日の平均価格
総数数値各日の総件数(収集されたデータの数)
時間帯別件数(0:00~23:00)数値時間帯ごとの件数を示す。

データ仕様

  • 日付(yyyy/m/d

    • 年月日 (yyyy/m/d)の形式
    • 例: 2024/12/13
  • 平均価格(数値)

    • 各日付ごとに、その日のデータを集計して平均価格を算出。
  • 総数(数値)

    • 各日付ごとの件数の総和を算出する。
  • 時間帯別件数(数値)

    • 各日付ごとに、0:00から23:00までの時間帯ごとの件数を集計する。

サンプルデータ

※ スペースの都合上、3:00~22:00部分は省略

日付平均価格総数0:001:002:0023:00
2024/12/01250301012
2024/12/02320350200
2024/12/03210251101
2024/12/04295282012
2024/12/05310320102
  • 日付
    収集されたデータの日付 (yyyy/m/d形式)。

  • 平均価格
    その日の平均価格。例: 250

  • 総数
    その日のデータの総件数。例: 30

  • 時間帯別件数 (0:00, 1:00, 2:00, … , 23:00)
    各指定された時間帯の件数。
    3:0022:00のカラムは省略し、「…」で示している。

準備

まずは、テストデータを用意しておこう。

集計するデータについては、適当にChatGPTに作成してもらった。
以下からダウンロードできる。

test_data.csv

完成予想図

complete-01

作成

では、早速データをまとめていこう。

「データ」シートの作成

データをそのまま貼り付けるだけ。
データシートは、そのまま「集計対象データの形式」となる。

今回は以下のようにデータシートを作成した。

  • シート名
    「データ」

  • B列 (時間)
    販売された時間。例えば、2024/12/13 0:00:00, 2024/12/13 1:00:00 など。

  • C列 (商品)
    売れた商品の名前。例: 商品A, 商品B など。

  • D列 (価格)
    その商品の価格。例: 250, 300 など。

data-sheet-01

※データの中身は先ほどの「test_data.csv」を貼り付けたものとなる。

「集計」シートの作成

こちらは完成図のように作成する。

  • シート名
    「集計」

  • B列 (日付)
    販売された日付。例えば、2024/12/13, 2024/12/14 など。

  • C列 (平均価格)
    その日付で販売された商品の平均価格。例: 390, 420など。

  • D列 (総数)
    その日付で販売された商品の総数。例: 23, 34 など。

  • E~AB列(時間帯別数)
    その時間帯に販売された商品の数。例: 1, 3 など。

各列について、入力していくデータや関数を記載していく。

B列(日付)

B列には日付が入っている。
これは、集計を行う日付なので、そのまま入力して問題ない。
ここでは、2024/12/132024/12/31まで入力している。

※Excelのセルの書式設定として、「ユーザー定義」→「yyyy/m/d」としている。

C列(平均価格)

こちらは関数として以下のように定義する。

=IFERROR(
   SUMIFS(
     データ!$D:$D,
     データ!$C:$C, "りんご",
     データ!$B:$B,">=" & $B5,
     データ!$B:$B,"<" & $B5+1
   )
    /D5,
  "-")

簡単に説明すると、

  • データシートのD列(価格)を合計の対象
  • 以下の条件のデータを集計する
    • データシートのC列(商品名)が「りんご」であること
    • データシートのB列(日付) が 「集計日付」内のものであること

データの範囲については固定としたいので絶対参照としており、集計日付については 列だけ固定し、行は相対参照としている($B5)の部分。

以下に、詳しい説明を記載する。

IFERROR 関数
IFERROR(式, エラーの場合の返り値)

  • 計算式や関数で、結果がエラーになるかもしれない部分を指定する。

  • エラーの場合の返り値
    式がエラー(例えば、ゼロで割るなど)になる場合、"-"(ハイフン)を表示する。

今回は、その日付で販売数が0だった場合に、0割りとなってしまうのでIFERRORを用意している。

SUMIFS 関数
SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)

SUMIFS 関数は、複数の条件に一致する値を合計するための関数となる。 引数は以下のようにしている。

  • 合計範囲
    データ!$D:$D
    データ シートのD列にある値を合計対象として指定する。(価格を集計対象としている)

以下は、どのようなデータに対して合計をするのかの条件となる。

  • 条件範囲1 データ!$C:$C
    データ シートのC列を条件範囲として指定する。 この列には商品名が入っており、特定の商品(この場合は「りんご」)に一致するデータを対象とする。

  • 条件1
    "りんご"
    この条件では、「りんご」という文字列がC列に含まれる行を対象とする。

  • 条件範囲2
    データ!$B:$B
    データ シートのB列を条件範囲として指定する。 (日付 時間)

  • 条件2
    ">=" & $B5
    B5 セルの値(日付または時間)以上の日付・時間を対象とする。 & 演算子は、比較演算子(>=)とB5セルの値を結合して条件を作成する。

  • 条件範囲3
    データ!$B:$B
    B列を指定し、同様に日付や時間を条件にします。

  • 条件3
    "<" & $B5 + 1
    B5 セルの値より1単位(1日または1時間など)小さい値(B5+1)を対象とする。
    例えば、B5 が「2024/01/01」の場合、B5+1 は「2024/01/02」となる。
    これにより、指定された日のデータを対象にすることができる。

/D5

この部分は、SUMIFS で計算された合計値を、セル D5 の値で割る。

AVERAGEIFS関数でよいかもしれない。

D列 (総数)

こちらは関数として以下のように定義する。

=SUM($E5:$AB5)
  • E列~AB列までは時間帯別の数なので、それを合計する。

こちらは、列だけ固定し、行は相対参照としている。

以下に、詳しい説明を記載する。

SUM 関数
SUM(数値1, 数値2, ...)

SUM 関数は、指定した範囲や数値をすべて合計するための関数となる。
引数として複数の数値やセル範囲を指定することができる。

今回は、E列~AB列 を指定して総数を算出している。

E列~AB列(時間帯)

こちらは関数として以下のように定義する。

=COUNTIFS(
データ!$B:$B,">="&$B5+TIME(HOUR(E$4),0,0),
データ!$B:$B,"<"&$B5+TIME(HOUR(E$4)+1,0,0),
データ!$C:$C, "りんご"
)

簡単に説明すると、

  • 以下の条件のデータをカウントする
    • データシートのB列が 集計日付 + 時間帯の範囲内であること
    • データシートのC列(商品名)が「りんご」であること

データの範囲については固定としたいので絶対参照としている。
集計日付については 列だけ固定し、行は相対参照としている。($B5の部分。)
また、時間帯については E列~AB列の4行目に 00:00, 01:00の時間帯があるので、E$4とし、列は相対参照とし、行は固定とした。

以下に、詳しい説明を記載する。

COUNTIFS 関数
COUNTIFS(範囲1, 条件1, 範囲2, 条件2, ...)

COUNTIFS は、複数の範囲とそれに対応する条件を指定し、それらのすべての条件を満たすセルの数をカウントする関数である。
条件は複数指定でき、各条件が満たされるセルをカウントする。

  • 範囲1
    データ!$B:$B
    データ シートのB列を条件範囲として指定する。 (日付 時間)

  • 条件1
    ">="&$B5+TIME(HOUR(E$4),0,0)

    • ">=" は「以上」を意味し、指定した日時より大きいか等しい値を求める条件。
    • $B5: ここでは、基準となる日付(セル B5)を指定する。この日付に時間を加えた値を比較する。
    • TIME(HOUR(E$4), 0, 0): E$4 セルの時間部分を取得し、その時間に0分0秒を追加する。
      • HOUR(E$4) は、E$4 セルに入力された時間から「時間」部分を取り出す。
      • TIME(HOUR(E$4), 0, 0) は、その時間に0分0秒を付けて「時間だけ」の値にする。例えば、E$4 が「8:30」なら、HOUR(E$4) は「8」になり、TIME(8,0,0) で「08:00:00」となる。
    • その結果、$B5 + TIME(HOUR(E$4), 0, 0) は、基準日付($B5)に、E$4 セルの時間を加算した日付・時間を表す。この条件は、指定した日時が $B5 以降のデータを抽出する。
  • 範囲2
    データ!$B:$B
    データ シートのB列を条件範囲として指定する。 (日付 時間)

  • 条件2
    "<"&$B5+TIME(HOUR(E$4)+1,0,0)

    • "<" は「未満」を意味し、指定した日時より小さい値を求める条件。
    • $B5 + TIME(HOUR(E$4) + 1, 0, 0): $B5E$4 の時間に1時間を足した時刻を加算する。E$4 の時間帯に1時間後の時刻を指定する。
    • HOUR(E$4) + 1 は、E$4 の時間部分を1時間加算する。たとえば、E$4 が「8:30」なら、HOUR(E$4) + 1 は「9」となり、TIME(9, 0, 0) で「09:00:00」となる。
    • その結果、$B5 + TIME(HOUR(E$4) + 1, 0, 0) は、基準日付に1時間後の時間を加算した日時を表す。
    • この条件により、条件1と合わせると「指定した日時より小さい1時間以内のデータ」となるので、2024/08/31 00:00:00 <= [データ] < 2024/08/31 01:00:00のデータを検索するようになる。
  • 範囲3
    データ!$C:$C
    データ シートのC列を条件範囲として指定する。 (商品名)

  • 条件3
    "りんご"
    りんご という文字列を条件として指定する。
    この条件により、「データ」シートのC列が「りんご」である行のみをカウント対象とする。

実際の動作例

例えば、次のようなデータが「データ」シートにあるとする(B列は日時、C列は商品名)
※価格列は省略

B列 (日時)C列 (商品名)
2025/02/01 08:15りんご
2025/02/01 08:45りんご
2025/02/01 09:30バナナ
2025/02/01 08:10りんご

B5 に「2025/02/01」日付、E4 に「08:00」時刻が設定されている場合、この関数は以下のように動作する

  • 2025/02/01 08:00:00」以降、「2025/02/01 09:00:00」未満の時間帯で、「りんご」の商品をカウントする。
  • 条件を満たす行は2行(「2025/02/01 08:15」および「2025/02/01 08:45」)なので、関数はその結果として「2」を返す。

集計シートのデータ拡張

上記のように定義したので、後はオートフィルとかで増やしていけばOK

complete-02

以上で完成となる。

おわりに

久しぶりにExcelの関数とかを駆使してデータの集計というのをやってみた。
これ、SQLとかだったらもっと簡単に書けるのにExcelだと関数をいろいろ駆使しないといけないのが結構つらい。
とはいえ、一度テンプレをつくれば使いまわせるので、一回だけ苦労すれば後は楽だね。

Hugo で構築されています。
テーマ StackJimmy によって設計されています。