Excelでヒストグラムを描く二通りの方法。正規分布の近似曲線も併せて表示！
今回は現実のデータを使って出荷数のヒストグラム（度数分布表）を作り、それが正規分布とどれくらい一致しているかを確かめるやり方を紹介します。
ヒストグラムの作り方（その１）
階級に分ける
A社は物流センターから商品Xを毎日出荷していますが、昨年１月１日から４月３０日までの出荷数は下記の通りでした。
このヒストグラムを作ってみましょう。
ヒストグラムとは各階級に何個のデータが含まれるかを示すグラフです。
この例でいうと、沢山出荷している＝階級が高いと考えます。
ですので、次のように階級を設定します。
１０００から８９９９まで１０００刻みに階級を設定していますが、理由は次の通りです。
- 最小出荷数＝１７３３、最大出荷数＝８５３７なので、１０００から８９９９で網羅される
- 階級の数は少な過ぎても多過ぎてもダメ。普通は５～１０くらいにするのが良い（理由は後に詳説）
また各階級を代表する値は、各階級の最大値にします。
各階級に含まれるデータ数を調べる
次に各階級のデータに含まれるデータ数を調べます。
１月１日から４月３０日までのデータは１２０個ありますが、これを各階級別に分けるということです。
これは手でも数えられますが、FREQUENCY関数を使えば一遍にできます。
このように数えたデータを入れたいセル（F4～F11）を選択し、
＝FREQUENCY(C4:C123,E4:E11)
を入力後、Ctrl+Shift+Enterを押します。
このように複数セルを選択してCtrl+Shift+Enterで確定させるやり方は配列数式と呼ばれています。
結果は次のようになります。
棒グラフにする
これで各階級に含まれるデータ数が求められたので、後はこれを棒グラフにするだけです。
D4～D11とF4～F11を選択してから、挿入⇒縦棒グラフの挿入⇒2-D縦棒を選択します。
すると下記のようなグラフができますが、棒と棒の間が空いていてヒストグラムっぽくないので、棒の一つをダブルクリックして「データ要素の書式設定」画面を開きます。
そして、一番下にある「要素の間隔」を０％に変えることによって下のように棒と棒の間の間隔がゼロになり、ヒストグラムになりました。
ヒストグラムの作り方（その２）
実はもっと簡単にヒストグラムを作れる方法もあります。
それは「縦棒グラフの挿入」ではなく「統計グラフの挿入」を使う方法です。
D4～D123を選択してから、挿入⇒統計グラフの挿入⇒ヒストグラムを選択します。
すると次のようなヒストグラムが即座にできあがります。
しかし横軸を見ると、階級が１７３３～２６８３のように中途半端な数字で区切られています。
これはExcelがいい感じのヒストグラムになるように自動で判断しているためですが、区切りのいい数字で階級を区切りたい場合もあります。
その場合には横軸をダブルクリックすると、次のように「軸の書式設定」の画面が現れます。
これは階級の区切りを調整する画面です。
デフォルトでは「自動」が選択されています。
これを次のように変更することによって、先ほど作ったような階級の区切りに変更することができます。
すると先ほどと同じヒストグラムができあがります。
このように「統計グラフの挿入」を使えば簡単にヒストグラムを作ることができますが、あえて「縦棒グラフの挿入」で作るメリットもあります。
それは正規分布曲線も併せて表示したい場合です。
正規分布の近似曲線も併せて表示
安全在庫理論では出荷数は正規分布に従うことを仮定しているので、ヒストグラムと正規分布曲線とのズレを見てみたい時もあります。
その場合には、「縦棒グラフの挿入」で作ったシートに次の計算式を付け加えます。
まずは平均と標準偏差を計算します。
次に各階級に全体のデータの何％が含まれるかを計算します（データ％）。
全体のデータ数が１２０個ですので、各階級のデータ数を１２０で割ります。
また各階級の代表値に対する正規分布の確率分布も計算します。
これは先ほど平均と標準偏差を求めていますので、これを使ってNORM.DIST関数で計算できます。
尚、正規分布の計算値を1000倍していますが、これは全体の和を１にするためで、こうすることによりデータ％とスケールが合うので同じグラフ上で比較し易くなります。
次にこのデータ％と正規分布を組み合わせグラフに描いていきます。
そのためにG4～H11を選択して、挿入⇒縦棒グラフの挿入⇒その他の縦棒グラフ⇒組み合わせをクリックします。
すると次のようなグラフができあがります。
青の棒グラフがヒストグラム（データ％）で、オレンジの折れ線グラフが正規分布曲線です。
概ね一致していることがわかりますね。
最後にきちんと清書しておきましょう。
棒グラフの間隔をゼロにする。
横軸に階級の区切りを表示する。
グラフの余白を右クリック⇒データの選択⇒編集⇒軸ラベルの範囲でD4～D11を選択
最終的には、このようなグラフができあがります。
おまけ：階級の数はいくつにすれば良いのか？
この記事の最初のところで、「階級の数は普通５～１０にするのがよい」と書きました。
今回の例では階級を８つにしています。
これを２０にすると、次のようなヒストグラムになります。
歯抜けになってしまう箇所がありますね。
逆に３に減らすと次のようになります。
これも荒すぎますね。
ですので、分布の特徴を表すヒストグラムにするために階級の数を何個にするかというのは結構重要です。
この目安を示すのにスタージェスの公式が知られています。
スタージェスの公式
階級の数＝１＋log2ｎ（ｎはデータ数）
この公式に当てはめると、データ数が１２０個の場合は階級の数＝７.９≒８になるというわけです。
但しこれが絶対というわけではなく、あくまで目安です。