BDAstyle

ビジネスデータ分析ツールの作成 with Excel

ヒストグラムの作成 with Excel Tips5 [時刻で階級をとる]

1.イントロダクション

Step 0シチュエーションの設定

ビジネス街の弁当店Xは 限定300食の「日替わり298円弁当」を扱っています。これは毎営業日11:45から販売を開始し,ほぼ13:00までには全品が捌ける売り切れ御免の人気商品です。

下表は,ある日における「日替わり298円弁当」のタイムスタンプをPOSから取り出し,エクセルで「時刻」として扱えるよう加工したものです(yyyy/m/d h:mm:ss)。なおタイムスタンプの1つが,「日替わり298円弁当」が1つ売れたことと同義です。

初期データ

このデータをもとに,エクセルにおいては一般的と思われる手続きにしたがってヒストグラムを描いてみます(下図)。ただし,次の条件はここでヒストグラムを描くにあたっての前提とします。

  1. 11:45(販売開始時間)~13:00までの幅において5分刻みで販売個数をカウント

結果に鑑みるに,ひとつに階級の中心値をラベルに振るのは,時間の経過において度数を表現するといった趣旨からは,あまり良い選択にはなりえないような気もします。

たとえば下図のように境界値をラベルに取った方が,時間の流れをはるかに分かりやすく感じます。

ふたつに分析ツールやFrequency関数を用いた製法では,ここでいう境界の値(11:50, 12:00, 12:10, …)が下図の赤色の柱でカウントされてしまうといった点があります。たとえば12:00:00ちょうどのタイムスタンプは,12:00~12:05の柱ではなく11:55~12:00の柱へと振られます。

一連の時間の流れをこと強調する軸においては,この仕様にしたがうのは気分的にもあまりスッキリしないかもしれません。境界の値は,できれば下図青色の柱でカウントしたいところです。

以上の点をふまえ,ここでは

  1. 境界値で目盛をとる
  2. 「以上未満」でカウントする

ことを念頭にヒストグラムを作っていきます。

なお小数点以下の情報を含むシリアル値を扱う以上,度数分布表の作成(カウントの処理)に関して,一般的な手続きでは誤差によって引き起こされる予見の難しい不利益もつきまといます。したがって,ここではその不利益の可能性を少しでも減らさんとする意図において,時刻データをそのまま用いず加工して手続きを進めることにします。

ただし,そのあたりの慎重さが重要でない場合には,下記前段の手続きに縛られることなく,時刻データそのままに一般的な方法で度数分布表を作成してください。

2.手順|表部分

Step 1時刻から正の整数を作成

前提条件A.に則り,ヒストグラムの作成に必要な“時”および“分”のみタイムスタンプから抽出します。前提条件C.の場合は,“秒”の抽出は不要です。

[式の内容]hmmの構成で時刻を文字列化3ないしは4桁の数値の作成

B2 =VALUE(TEXT(A2, "h") & RIGHT(TEXT(A2, "h:mm"), 2))

Step 2階級幅の設定

階級幅を時刻形式で指定します。

前提条件A.より,ここでは0:05とします(5分刻み)。

Step 3境界値の設定(1)

下のような見出しをつくり,「下境界」直下に軸の開始時刻を時刻形式で入力します。

前提条件A.より,販売開始時間が11:45なので,ここでは1階級分余裕をとって11:40とします。

つづいて,「上境界」直下で左隣のセルに階級幅を加えます。

I2 =H2+D2

Step 4境界値の設定(2)

「下境界」2段目に「上境界」1段目を転記する式を,「上境界」2段目には同1段目に階級幅を加算する式を入力します。

これらを「上境界」に終了時刻が登場するまでコピーします。この作例では,前提条件A.にもとづき13:00までのコピーとなります。

H3 =I2
I3 =I2+$D$2

Step 5度数のカウント(1)

「計算用下限」直下にStep 1と同様の性格の式を入力します。

これを「計算用上限」列も含め度数分布表の最下行までコピーします。

F2 =VALUE(TEXT(H2, "h") & RIGHT(TEXT(H2, "h:mm"), 2))

Step 6度数のカウント(2)

前提条件C.にもとづき,CountIfs関数により度数を求めます。

「度数」直下に次の計算式を入力した後,これを度数分布表の最下行までコピーします。

[式の内容]「計算用下限」以上 「計算用上限」未満の「Val(h:mm)」列データをカウント

J2 =COUNTIFS($B$2:$B$301, ">=" & F2, $B$2:$B$301, "<" & G2)

Step 7ダミー変数の作成

「ダミー」直下に最大度数を求めます。

これは,前提条件B.を満たすにあたっての技術的な辻褄合わせに利用する以外,特段の意味を持つものではありません。

cf. ヒストグラムの作成 with Excel Tips1[境界値としてのラベルを階級の間に表示させる](このサイト)

K2 =MAX(J2:J17)

3.手順|グラフ部分

Step 8グラフの作成(1)

「上境界」列と「度数」「ダミー」列をあわせて選択し,棒グラフを作成します。

Step 9グラフの作成(2)

グラフの種類を変更します。

「度数」系列を「第2軸」にしたうえで,「ダミー」系列の種類を「散布図」に変更します(必ずこの順番に)。

Step 10グラフの作成(3)

「度数」系列の「要素の間隔」を0にします。

つづいて,「目盛の種類」を任意のものに(「外向き」推奨),

ラベルの向きを,判読に易いと感じるものに調整します。

さらには,「ダミー」系列(グラフ上の“マーカー”)の塗り色・線色とも「なし」に設定し不可視とします。

Step 11グラフの作成(4)

「Interval(階級幅)」直下・「下境界」直下・「上境界」最下行のセルを選択し,書式を「標準」に変更します(基本的には一時的な処置です。グラフが完成し,必要ならばこれらは元に戻しても あるいは 戻さなくてもグラフそのものへの影響はありません)。

これらの値を,横軸の最小値・最大値・目盛として設定します(シートの値をコピー[Ctrl + C]+テキストボックスにペースト[Ctrl + V]。これを繰り返し)。

Step 12グラフの作成(5)

2縦軸をなくすorラベルを「なし」にして不可視とします。

Step 13ヒストグラムの完成

必要に応じて他の書式を任意に調整し,ヒストグラムの完成です。

Step 14付記

時間軸が日付をまたぐ場合,下図のようにB, F, G列をyymmddhhmm10桁に加工すればリクツの上ではカウントは可能です。もっとも,どこで出るかも分からないオバケのような不利益について,延々と配慮を重ねるのもキリがないので,時間軸が日付をまたぐような処理が必要な場合には,ある意味割り切ってシリアル値のまま処理することをオススメします(誤差によって本来の分布の特徴点まで崩壊するような結果が導かれる可能性は,扱うデータや設定内容にもよりますが,一般的にはきわめて低いと言っていいかと思います)。

その他の参照