【今期最終】第9次申請分(申請締切日12/22)受付中。

平均値を信じてはダメ!エクセルでヒストグラムを簡単に作る方法

ヒストグラム

在庫管理システムで得られた入出庫データなどで行う在庫分析は、発注数の見直しや、過剰在庫の在庫削減などの適正在庫には欠かせません。

おそらくあなたが真っ先にやるのは、平均値を出したり、折れ線グラフを使って傾向を見ることではないでしょうか?

どちらも大切ですが残念ながら片手落ちです。場合によっては平均値を安易に信じて使ってはいけない場合もあります。

そういった気付きを与えてくれるのが、ヒストグラムです。今回はエクセルのピボットテーブルを使ってヒストグラムを簡単に作る方法と、度数分布表を作成する方法をご紹介します。

サンプルデータを用意しました。説明もこのサンプルファイルに従ったものになっていますので、ぜひ作ってみてください。

 

ヒストグラムとは?

ヒストグラムはデータの分布(ばらつき具合)を見える化する方法です。

先ほど、平均値を安易に信じてはいけないとお伝えしました。

ヒストグラムでデータの分布(ばらつき具合)を見てみると、この理由が一目でわかります。

ヒストグラムの代表的な形は、大きく分けて3種類です。

代表的なヒストグラムの3タイプ
  1. 中央に山が一つ
  2. 右または左に山が寄っている
  3. 2つ以上の山がある

それぞれに平均値と書きましたが、データの分布が右か左に偏っていたり、山が2つ以上あるような場合は、データの分布と平均値の実態が全然違います。このように、単に計算だけで平均値を信じてしまうと、誤った判断をしてしまうことになります。

極端な例を挙げると、数学のテストで0点が50人、100点が50人いれば、平均値は50点です。どう考えても平均値50点を信じてはいけませんね。

ヒストグラムを使ってデータの分布を見ればこのような間違いは絶対に起こりません。

ヒストグラムはデータの基礎的な本質を教えてくれます。

ピボットテーブルを使ってヒストグラムを作る方法

しかし、この記事を読んでいるあなたもこれまでにヒストグラムを一度も作ったことが無い・・・

のではないでしょうか?

 

一瞬で作れる時系列グラフと違って、なんだか難しそう・・・と思っているかもしれませんが、そんなことはありません。

ちょっとした下準備が必要ですがエクセルを使えば簡単に作成できます。

エクセルにもヒストグラムを作る機能がありますが、ちょっと使いづらいのが難点です。

そこで、今回は私が考案したピボットテーブルを使って作る方法と手順を解説します。

ヒストグラムの構成要素

ヒストグラムの構成要素は、次の2点です。

  1. 度数
  2. 階級

ヒストグラムは、あるデータの範囲にいくつデータが含まれるかを示したものです。わかりやすい例でいうと、

テストの得点51点~60点の範囲に、10人がいるといった具合です。

ここでポイントになるのは、階級の数と1つの階級のデータの範囲です。ここで悩む方がとても多いです。

これについても後ほど解決方法をご紹介します。

ヒストグラムを作る手順は、

  1. 下準備データを用意する
  2. 階級数の目安を決める
  3. 階級幅を決める
  4. ピボットテーブルを使ってデータを集計する(ヒストグラムの元になるデータ)
  5. 4をヒストグラム化する(グラフにして可視化する)

ヒストグラムを作る下準備

ヒストグラムを作るを作る下準備として、次のデータをヒストグラムを作りたいデータを使って用意します。

  • 最小値
  • 最大値
  • データ範囲(最大値ー最小値)
  • データの個数

これもエクセルを使えば簡単に計算できます。サンプルデータの計算結果は以下の通りです。

ヒストグラムの下準備データ

最大値、最小値、データ数は、関数を使って計算します。

ヒストグラムの階級数と階級幅を決める

次に基本情報を使って、ヒストグラムの階級数と階級幅を決めます。

ヒストグラムの階級数と幅を決める

一番悩むのが、階級数の決め方です。特に決まりは無いので自由ですが、スタージェスの式を使って目安を決めます。

スタージェスの式は、「階級数=log2データ数+1」です。logの計算もエクセル関数でできます。

式は次の通りです。

logのエクセル関数

これを使ってスタージェスの式を計算すると、約6.93になるので階級数の目安は6個か7個くらいにすればいいかな?

ということになります。

ヒストグラムの左端と右端を決める

次にデータの左端(最小値)を決めます。ここには、データの最小値が含まれなければいけません。今回は、最小値33をそのまま左端に使うことにします。

ただ、これにも決まりが無いので、区切りよく30からスタートしてもかまいません。

次の右端ですが、これには最大値が含まれなければいけません。

ヒストグラムの階級数と幅の目安を決める

先ほど、階級の目安が6か7、階級幅の目安が4か5くらいということが分かりました。

階級の幅は、区切りが良い方が分かりやすいので5、階級数を7にしてみます。

33から5刻みで階級を作ると、最大値が68になります。基本情報を見ると、データの最大値は67なのでちょうどよいです。

まとめるとこんな感じです。

ヒストグラムの階級数と階級幅

ピボットテーブルでヒストグラムの基礎データを作る

ピボットテーブルはデータを集計するエクセルの機能です。(ピボットテーブルはとても便利で強力なので、今回を機会にぜひ使えるようになってください!)

これを使って、ヒストグラムの基礎データを作ります。

ヒストグラムを作るためのピボットテーブルの設定

行と値の両方に販売数をセットします。

次に「値」の方を右クリックして、値フィールドの設定を出して、合計から個数に変更します。

こうすると、左の集計表が完成します。これは販売データの数量の集計表です。例えば、54個販売したのが、期間中2回あったということが分かります。

そして、先ほど計算した階級数と階級幅を設定します。

ピボットテーブルのヒストグラム化

行ラベルのデータのどれでも良いので、選択して右クリックします。その中からグループ化選択します。

そして、次のように設定します。

  • 先頭の値に33(左端の値)
  • 末尾の値に68(右端の値)
  • 単位に、5(階級幅)

すると、データがグループ化され、その範囲内にあるデータの個数が分かります。

次に、ピボットテーブル分析のタブを選択して、その中からピボットグラフを選択します。

ピボットテーブルを使って作ったヒストグラム

完成です!

本来は、各階級の間を隙間なく詰めるのがルールですが、自分で使うだけであれば気にしなくても良いでしょう。

これを見ると、階級数は6つでも良かったかもしれないですね。(理由:63~68に含まれるデータ数が1つしかないため)

せっかくなのでこのデータを活用して度数分布表を作ってみましょう。度数分布表は、ABC分析をする際に作る表です。

ピボットテーブルを使って度数分布表を作る(データの発生頻度)

ピボットテーブルを使ってヒストグラムを作る利点は、データ加工がしやすくなる点です。

今回のデータを活用して度数分布表を作ることができます。度数分布表を作れば、データの発生頻度が分かるようになります。

度数分布表の作成

値に販売数を2つ追加します。同じ集計データが2つ追加されます。

次に、個数/販売数2の上で右クリックします。

計算の種類→親行に対する比率を選択します。

同じように個数/販売数3を選択して、次は「比率の累積」を選択します。

最後に、データの個数が多い順番(降順)に並び替えて完成です。

度数分布表の完成

自分で使うだけであればこれで良いですが、人に見せたりする場合は表題を変えると、見栄えがよくなります。

度数分布表の完成

いかがでしたでしょうか?

ピボットテーブルを使えば、面倒なヒストグラムも度数分布表も比較的簡単に作ることができます。

平均値や時系列グラフだけではなく、様々な角度からデータを見ることは大切です。

在庫管理システムを導入すれば、作業の効率化だけではなく蓄積したデータを活用することが大切です。

データは使って初めて価値が出ます。今回のようなデータ分析で今まで見えなかったことが見え、

気づきにつながります。さらなる効率化や適正在庫にも役立ちますので、ぜひ試してみてください。

在庫管理やシステム導入、データ活用に関するご相談はお気軽にどうぞ!

    お名前必須
    メールアドレス必須
    電話番号任意
    お問い合わせ種類必須
    お問い合わせ内容必須