Excelでヒストグラムを描く二通りの方法。前へ次へ検索RSSFeedlyホーム>物流数学の基礎>統計で判断するExcelでヒストグラムを描く二通りの方法。安全在庫理論は出荷数が正規分布に従うことを仮定しています。今回は現実のデータを使って出荷数のヒストグラム(度数分布表)を作り、それが正規分布とどれくらい一致しているかを確かめるやり方を紹介します。Pythonでやる方法リンクリンクヒストグラムの作り方(その1)階級に分けるA社は物流センターから商品Xを毎日出荷していますが、昨年1月1日から4月30日までの出荷数は下記の通りでした。このヒストグラムを作ってみましょう。ヒストグラムとは各階級に何個のデータが含まれるかを示すグラフです。この例でいうと、沢山出荷している=階級が高いと考えます。次のように階級を設定します。1000から8999まで1000刻みに階級を設定していますが、理由は次の通りです。最小出荷数=1733、最大出荷数=8537なので、1000から8999で網羅される階級の数は少な過ぎても多過ぎてもダメ。普通は5~10くらいにするのが良い(理由は後に詳説)また各階級を代表する値は、各階級の最大値にします。各階級に含まれるデータ数を調べる次に各階級のデータに含まれるデータ数を調べます。1月1日から4月30日までのデータは120個ありますが、これを各階級別に分けるということです。これは手でも数えられますが、FREQUENCY関数を使えば一遍にできます。このように数えたデータを入れたいセル(F4~F11)を選択し、=FREQUENCY(C4:C123,E4:E11)を入力後、Ctrl+Shift+Enterを押します。このように複数セルを選択してCtrl+Shift+Enterで確定させるやり方は配列数式と呼ばれています。結果は次のようになります。棒グラフにするこれで各階級に含まれるデータ数が求められたので、後はこれを棒グラフにするだけです。D4~D11とF4~F11を選択してから、挿入⇒縦棒グラフの挿入⇒2-D縦棒を選択します。クリックすると拡大しますすると下記のようなグラフができますが、棒と棒の間が空いていてヒストグラムっぽくないので、棒の一つをダブルクリックして「データ要素の書式設定」画面を開きます。クリックすると拡大しますそして、一番下にある「要素の間隔」を0%に変えることによって下のように棒と棒の間の間隔がゼロになり、ヒストグラムになりました。ヒストグラムの作り方(その2)実はもっと簡単にヒストグラムを作れる方法もあります。それは「縦棒グラフの挿入」ではなく「統計グラフの挿入」を使う方法です。D4~D123を選択してから、挿入⇒統計グラフの挿入⇒ヒストグラムを選択します。クリックすると拡大しますすると次のようなヒストグラムが即座にできあがります。横軸を見ると、階級が1733~2683のように中途半端な数字で区切られています。これはExcelがいい感じのヒストグラムになるように自動で判断しているためですが、区切りのいい数字で階級を区切りたい場合もあります。その場合には横軸をダブルクリックすると、次のように「軸の書式設定」の画面が現れます。クリックすると拡大しますこれは階級の区切りを調整する画面です。デフォルトでは「自動」が選択されています。これを次のように変更することによって、先ほど作ったような階級の区切りに変更することができます。クリックすると拡大しますすると先ほどと同じヒストグラムができあがります。このように「統計グラフの挿入」を使えば簡単にヒストグラムを作ることができますが、あえて「縦棒グラフの挿入」で作るメリットもあります。それは正規分布曲線も併せて表示したい場合です。安全在庫理論では出荷数は正規分布に従うことを仮定しているので、ヒストグラムと正規分布曲線とのズレを見てみたい時もあります。その場合には、「縦棒グラフの挿入」で作ったシートに次の計算式を付け加えます。平均と標準偏差を計算します。次に各階級に全体のデータの何%が含まれるかを計算します(データ%)。全体のデータ数が120個ですので、各階級のデータ数を120で割ります。これを相対度数といいます。(120で割る前の値は絶対度数)また各階級の代表値に対する正規分布の確率分布も計算します。これは先ほど平均と標準偏差を求めていますので、これを使ってNORM.DIST関数で計算できます。クリックすると拡大します尚、正規分布の計算値を1000倍していますが、これは全体の和を1にするためで、こうすることによりデータ%とスケールが合うので同じグラフ上で比較し易くなります。実はこのNORM.DIST関数で計算される値は確率密度です。この確率密度に階級の幅(この場合は1000)を掛けると、先ほどの相対度数になるというわけです。これについては、下記記事の5章-棒グラフで自作する方法でも解説しています。次にこのデータ%と正規分布を組み合わせグラフに描いていきます。そのためにG4~H11を選択して、挿入⇒縦棒グラフの挿入⇒その他の縦棒グラフ⇒組み合わせをクリックします。クリックすると拡大しますすると次のようなグラフができあがります。青の棒グラフがヒストグラム(データ%)で、オレンジの折れ線グラフが正規分布曲線です。概ね一致していることがわかりますね。最後にきちんと清書しておきましょう。棒グラフの間隔をゼロにする。クリックすると拡大します横軸に階級の区切りを表示する。グラフの余白を右クリック⇒データの選択⇒編集⇒軸ラベルの範囲でD4~D11を選択クリックすると拡大します最終的には、このようなグラフができあがります。おまけ:階級の数はいくつにすれば良いのか?この記事の最初のところで、「階級の数は普通5~10にするのがよい」と書きました。今回の例では階級を8つにしています。これを20にすると、次のようなヒストグラムになります。歯抜けになってしまう箇所がありますね。逆に3に減らすと次のようになります。分布の特徴を表すヒストグラムにするために階級の数を何個にするかというのは結構重要です。この目安を示すのにスタージェスの公式が知られています。階級の数=1+log2n(nはデータ数)この公式に当てはめると、データ数が120個の場合は階級の数=7.9≒8になるというわけです。これが絶対というわけではなく、あくまで目安です。Pythonでやる方法Pythonを使えば、もっと簡単に美しく作ることができます。【区間推定の使い方】タイヤの摩耗を具体例にわかりやすく解説します。TOP28【作業分析6】MMCモデル|最適なトラックドック数をSimpyでシミュレーション【作業分析5】トラックの待機台数の多さを人の表情でアニメーション(2)カンボジアの物流でお困りのことがあればお気軽にご相談下さい。学生時代にアジアの雑踏にハマる。誰もが羨むメーカーを退職し「旅するように働く」ことを目指し物流業界に転職。理工学と経営学のダブル修士。2023年1月からカンボジアのオートレスビーチ在住。