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

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

概要
まずどのようなデータを、どのように加工するのかを見ておこう。
集計対象データの形式
データは以下の通りとなる。
集計対象データ構造
項目名 | データ型 | 説明 | 例 |
---|---|---|---|
時間 | 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:00
~23: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:00 | 1:00 | 2:00 | … | 23:00 |
---|---|---|---|---|---|---|---|
2024/12/01 | 250 | 30 | 1 | 0 | 1 | … | 2 |
2024/12/02 | 320 | 35 | 0 | 2 | 0 | … | 0 |
2024/12/03 | 210 | 25 | 1 | 1 | 0 | … | 1 |
2024/12/04 | 295 | 28 | 2 | 0 | 1 | … | 2 |
2024/12/05 | 310 | 32 | 0 | 1 | 0 | … | 2 |
日付
収集されたデータの日付 (yyyy/m/d
形式)。平均価格
その日の平均価格。例:250
総数
その日のデータの総件数。例:30
時間帯別件数 (
0:00
,1:00
,2:00
, … ,23:00
)
各指定された時間帯の件数。3:00
~22:00
のカラムは省略し、「…」で示している。
準備
まずは、テストデータを用意しておこう。
集計するデータについては、適当にChatGPT
に作成してもらった。
以下からダウンロードできる。
完成予想図

作成
では、早速データをまとめていこう。
「データ」シートの作成
データをそのまま貼り付けるだけ。
データシートは、そのまま「集計対象データの形式」となる。
今回は以下のようにデータシートを作成した。
シート名
「データ」B列 (時間)
販売された時間。例えば、2024/12/13 0:00:00
,2024/12/13 1:00:00
など。C列 (商品)
売れた商品の名前。例:商品A
,商品B
など。D列 (価格)
その商品の価格。例:250
,300
など。

※データの中身は先ほどの「test_data.csv
」を貼り付けたものとなる。
「集計」シートの作成
こちらは完成図のように作成する。
シート名
「集計」B列 (日付)
販売された日付。例えば、2024/12/13
,2024/12/14
など。C列 (平均価格)
その日付で販売された商品の平均価格。例:390
,420
など。D列 (総数)
その日付で販売された商品の総数。例:23
,34
など。E~AB列(時間帯別数)
その時間帯に販売された商品の数。例:1
,3
など。
各列について、入力していくデータや関数を記載していく。
B列(日付)
B列には日付が入っている。
これは、集計を行う日付なので、そのまま入力して問題ない。
ここでは、2024/12/13
~ 2024/12/31
まで入力している。
※Excelのセルの書式設定として、「ユーザー定義」→「yyyy/m/d
」としている。
C列(平均価格)
こちらは関数として以下のように定義する。
=IFERROR(
SUMIFS(
データ!$D:$D,
データ!$C:$C, "りんご",
データ!$B:$B,">=" & $B5,
データ!$B:$B,"<" & $B5+1
)
/D5,
"-")
簡単に説明すると、
- データシートのD列(
価格
)を合計の対象 - 以下の条件のデータを集計する
- データシートのC列(
商品名
)が「りんご」であること - データシートのB列(
日付
) が 「集計日付」内のものであること
- データシートのC列(
データの範囲については固定としたいので絶対参照としており、集計日付については 列だけ固定し、行は相対参照としている($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列(
商品名
)が「りんご」であること
- データシートのB列が
データの範囲については固定としたいので絶対参照としている。
集計日付については 列だけ固定し、行は相対参照としている。($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)
:$B5
にE$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

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