ヒストグラムの作図Tips 5/7［時刻で階級を切る］
階級を時間で切って度数をとりたい
イントロダクション
Step 0シチュエーションの設定
ビジネス街の弁当店Xは，1日あたり限定300食の「日替わり298円弁当」を扱っています。これは毎営業日11:45から販売を開始し，ほぼ13:00までには全品が捌ける売り切れ御免の人気商品です。
下表は，ある日における「日替わり298円弁当」のタイムスタンプをPOSから取り出し，Excelで「時刻」として扱えるよう加工したものです(yyyy/m/d h:mm:ss)。なおここでは，1つのタイムスタンプは「日替わり298円弁当」が1つ売れたことと同義であるとして手続きを進めます。
DL
Excelを使う場合に一般的とされる手続きにしたがって，上のデータをソースに，とりあえず，すぐ直下にヒストグラムを描いてみます。ただし，次の条件はここでヒストグラムを描くにあたっての前提とします。
- 11:45(販売開始時間)～13:00までの幅において5分刻みで販売個数をカウント
上の図に鑑みるに，ひとつに階級の中心値をラベルに振るのは，時間の経過において度数を表現するといった趣旨からはあまり良い選択にはなりえないような気もします。
たとえば，下図のように境界値をラベルに取った方が，時間の流れは分かりやすく感じます。
ふたつに分析ツールやFrequency関数を用いた描画法は，仕様的な意味からはここでいう境界の値(11:50, 12:00, 12:10, …)を下図の赤色の柱でカウントするといった点に注意が必要です。たとえば12:00:00ちょうどのタイムスタンプは，12:00～12:05の柱ではなく11:55～12:00の柱へと振られます。
一連の時間の流れをこと強調する軸においては，この仕様にしたがうのは気分的にもあまりスッキリしないかもしれません。境界の値は，できれば下図青色の柱でカウントしたいところです。
以上の点をふまえ，ここでは
- 境界値で目盛をとる
- 「以上未満」でカウントする
ことを念頭にヒストグラムを描いていきます。
もっとも，実のところはこの頁で扱うデータに限って言えば，境界の値上の条件 C. については，Frequency関数のみでも仕様に反して正確なカウントができます。このあたりの挙動についての正確なところが，かねてより筆者には不詳です。
おそらくシリアル値，言いかたを替えれば誤差と隣り合わせの小数点以下の数字を扱うことに起因するものかと思うところですが，いずれにせよ，不詳であれば予見のし難い不利益を許容せざるを得なくなります。したがって，処理にあたっては時刻データをそのまま利用するのを避けて，不利益の可能性を迂回できるルートをとってみたいと思います。すなわち，元データに若干の加工を施しながら手続きを進めることにします。
ただし，そのあたりの慎重さが重要でない場合には，下記前段の手続きに縛られることなく，時刻データそのままに一般的な方法で度数分布表を作成してもよいかと思います。
工程
表部分
Step 1時刻から正の整数を作成
前提条件 A. に則り，ヒストグラムの作成に必要な“時”および“分”のみタイムスタンプから抽出します。前提条件 C. の場合は，“秒”の抽出は不要です。
| B2 | =VALUE(TEXT(A2, "h") & RIGHT(TEXT(A2, "h:mm"), 2)) |
|---|
［式の内容］hmmの構成で時刻を文字列化3ないしは4桁の数値の作成
Step 2階級幅の設定
階級幅を時刻形式で指定します。
前提条件 A. より，ここでは0:05とします(5分刻み)。
Step 3境界値の設定(1)
下のような見出しをつくり，「下境界」直下に軸の開始時刻を時刻形式で入力します。
前提条件 A. より，販売開始時間が11:45なので，ここでは1階級分余裕をとって11:40とします。
つづいて，「上境界」直下で左隣のセルに階級幅を加えます。
| I2 | =H2+D2 |
|---|
scrollable
Step 4境界値の設定(2)
「下境界」2段目に「上境界」1段目を転記する式を，「上境界」2段目には同1段目に階級幅を加算する式を入力します。
これらを「上境界」に終了時刻が登場するまでコピーします。この作例では，前提条件 A. にもとづき13:00までのコピーとなります。
| H3 | =I2 |
|---|---|
| I3 | =I2+$D$2 |
scrollable
Step 5度数のカウント(1)
「計算用下限」直下にStep 1と同様の性格の式を入力します。
これを「計算用上限」列も含め度数分布表の最下行までコピーします。
| F2 | =VALUE(TEXT(H2, "h") & RIGHT(TEXT(H2, "h:mm"), 2)) |
|---|
scrollable
Step 6度数のカウント(2)
前提条件 C. にもとづき，CountIfs関数により度数を求めます。
「度数」直下に次の計算式を入力した後，これを度数分布表の最下行までコピーします。
| J2 | =COUNTIFS($B$2:$B$301, ">=" & F2, $B$2:$B$301, "<" & G2) |
|---|
［式の内容］「計算用下限」以上 「計算用上限」未満の「v(h&mm)」列データをカウント
scrollable
Step 7ダミー変数の作成
「dummy」直下に最大度数を求めます。
これは，前提条件 B. を満たすにあたっての技術的な辻褄合わせに利用する以外，特段の意味を持つものではありません。
cf. ヒストグラムの作図Tips 1/7［数値ラベルを階級の境界に表示する］(このサイト)
| K2 | =MAX(J2:J17) |
|---|
グラフ部分
Step 8グラフの作成(1)
「上境界」列と「度数」「dummy」列をあわせて選択し，棒グラフを作成します。
Step 9グラフの作成(2)
グラフの種類を変更します。
「度数」系列を「第2軸」にしたうえで(先)，「dummy」系列の種類を「散布図」に変更します(後)。
Step 10グラフの作成(3)
「度数」系列の「要素の間隔」を0にします。
つづいて，「目盛の種類」を任意のものに(「外向き」推奨)，
ラベルの向きを，判読に易いと感じるものに調整します。
さらには，「dummy」系列(グラフ上の“マーカー”)の塗り色・線色とも「なし」に設定し不可視とします。
Step 11グラフの作成(4)
「interval(階級幅)」直下・「下境界」直下・「上境界」最下行のセルを選択し，書式を「標準」に変更します。
これらの値を，横軸の最小値・最大値・目盛間隔として設定します(シートの値をコピー［Ctrl + C］＋テキストボックスにペースト［Ctrl + V］。これを繰り返し)。
scrollable
Step 12グラフの作成(5)
第2縦軸をなくすorラベルを「なし」にして不可視とします。
Step 13ヒストグラムの完成
必要に応じて他の書式を任意に調整し，ヒストグラムの完成です。
Step 14付記
時間軸が日付をまたぐ場合，下図のようにB, F, G列をyymmddhhmmの10桁に加工すれば，リクツの上ではカウントも不可能ではありません。もっとも，どこで出るかも分からないオバケのような不利益について，延々と配慮を重ねるのもキリがないので，時間軸が日付をまたぐような処理が必要な場合には，ある意味割り切ってシリアル値のまま処理する方がいろんな意味で穏当かと思います(誤差によって真の分布の特徴点まで崩壊するような結果が導かれる可能性は，サイズが大きければ常識的には微小でしょう)。
Next
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」のヒストグラムに関するHow-toです。