在庫管理システムで得られた入出庫データなどで行う在庫分析は、発注数の見直しや、過剰在庫の在庫削減などの適正在庫には欠かせません。
おそらくあなたが真っ先にやるのは、平均値を出したり、折れ線グラフを使って傾向を見ることではないでしょうか？
どちらも大切ですが残念ながら片手落ちです。場合によっては平均値を安易に信じて使ってはいけない場合もあります。
そういった気付きを与えてくれるのが、ヒストグラムです。今回はエクセルのピボットテーブルを使ってヒストグラムを簡単に作る方法と、度数分布表を作成する方法をご紹介します。
サンプルデータを用意しました。説明もこのサンプルファイルに従ったものになっていますので、ぜひ作ってみてください。
目次
ヒストグラムとは？
ヒストグラムは、あるデータの範囲にいくつデータが含まれるかをグラフ化したもので、ヒストグラムはデータの分布（ばらつき具合）を見える化する方法です。
先ほど、平均値を安易に信じてはいけないとお伝えしました。
ヒストグラムでデータの分布（ばらつき具合）を見てみると、この理由が一目でわかります。
ヒストグラムの代表的な形は、大きく分けて３種類です。
- 中央に山が一つ
- 右または左に山が寄っている
- 2つ以上の山がある
それぞれに平均値と書きましたが、データの分布が右か左に偏っていたり、山が２つ以上あるような場合は、データの分布と平均値の実態が全然違います。このように、単に計算だけで平均値を信じてしまうと、誤った判断をしてしまうことになります。
極端な例を挙げると、数学のテストで０点が５０人、１００点が５０人いれば、平均値は５０点です。どう考えても平均値５０点を信じてはいけませんね。
ヒストグラムを使ってデータの分布を見ればこのような間違いは絶対に起こりません。
ヒストグラムはデータの基礎的な本質を教えてくれます。
ピボットテーブルを使ってヒストグラムを作る方法
しかし、この記事を読んでいるあなたもこれまでにヒストグラムを一度も作ったことが無い・・・
のではないでしょうか？
一瞬で作れる時系列グラフと違って、なんだか難しそう・・・と思っているかもしれませんが、そんなことはありません。
ちょっとした下準備が必要ですがエクセルを使えば簡単に作成できます。
エクセルにもヒストグラムを作る機能がありますが、ちょっと使いづらいのが難点です。
そこで、今回は私が考案したピボットテーブルを使って作る方法と手順を解説します。
ヒストグラムの構成要素
ヒストグラムの構成要素は、次の２点です。
- 階級：度数を集計する数値の区間
- 度数：階級に含まれるデータの個数
上記のヒストグラムを例にとると、
階級「33~37」の範囲に４つのデータが含まれるということです。（４つのデータは、３３、３４、３５、３６、３７のいずれか）
ヒストグラムを作る具体的な手順は以下の通りです。
- 下準備データを用意する
- 階級数の目安を決める
- 階級幅を決める
- ピボットテーブルを使ってデータを集計する（ヒストグラムの元になるデータ）
- ４をヒストグラム化する（グラフにして可視化する）
それでは、手順に従って解説します。
ヒストグラムを作る下準備
ヒストグラムを作るを作る下準備として、次のデータをヒストグラムを作りたいデータを使って用意します。
- 最小値
- 最大値
- データ範囲（最大値ー最小値）
- データの個数
これもエクセルを使えば簡単に計算できます。サンプルデータの計算結果は以下の通りです。
最大値、最小値、データ数は、関数を使って計算します。
ヒストグラムの階級数と階級幅を決める
次に基本情報を使って、ヒストグラムの階級数と階級幅を決めます。
一番悩むのが、階級数の決め方です。特に決まりは無いので自由ですが、スタージェスの式を使って目安を決めます。
スタージェスの式は、「階級数＝log2データ数＋１」です。logの計算もエクセル関数でできます。
式は次の通りです。
これを使ってスタージェスの式を計算すると、約6.93になるので階級数の目安は６個か７個くらいにすればいいかな？
ということになります。
ヒストグラムの左端と右端を決める
次にデータの左端（最小値）を決めます。ここには、データの最小値が含まれなければいけません。今回は、最小値33をそのまま左端に使うことにします。
ただ、これにも決まりが無いので、区切りよく30からスタートしてもかまいません。
次の右端ですが、これには最大値が含まれなければいけません。
先ほど、階級の目安が６か７、階級幅の目安が４か５くらいということが分かりました。
階級の幅は、区切りが良い方が分かりやすいので５、階級数を７にしてみます。
33から５刻みで階級を作ると、最大値が68になります。基本情報を見ると、データの最大値は67なのでちょうどよいです。
まとめるとこんな感じです。
ピボットテーブルでヒストグラムの基礎データを作る
ピボットテーブルはデータを集計するエクセルの機能です。（ピボットテーブルはとても便利で強力なので、今回を機会にぜひ使えるようになってください！）
これを使って、ヒストグラムの基礎データを作ります。
行と値の両方に販売数をセットします。
次に「値」の方を右クリックして、値フィールドの設定を出して、合計から個数に変更します。
こうすると、左の集計表が完成します。これは販売データの数量の集計表です。例えば、54個販売したのが、期間中２回あったということが分かります。
そして、先ほど計算した階級数と階級幅を設定します。
行ラベルのデータのどれでも良いので、選択して右クリックします。その中からグループ化選択します。
そして、次のように設定します。
- 先頭の値に３３（左端の値）
- 末尾の値に６８（右端の値）
- 単位に、５（階級幅）
すると、データがグループ化され、その範囲内にあるデータの個数が分かります。
次に、ピボットテーブル分析のタブを選択して、その中からピボットグラフを選択します。
完成です！
本来は、各階級の間を隙間なく詰めるのがルールですが、自分で使うだけであれば気にしなくても良いでしょう。
これを見ると、階級数は6つでも良かったかもしれないですね。（理由：63~68に含まれるデータ数が１つしかないため）
せっかくなのでこのデータを活用して度数分布表を作ってみましょう。度数分布表は、ABC分析をする際に作る表です。
ピボットテーブルを使って度数分布表を作る（データの発生頻度）
ピボットテーブルを使ってヒストグラムを作る利点は、データ加工がしやすくなる点です。
今回のデータを活用して度数分布表を作ることができます。度数分布表を作れば、データの発生頻度が分かるようになります。
値に販売数を２つ追加します。同じ集計データが2つ追加されます。
次に、個数／販売数２の上で右クリックします。
計算の種類→親行に対する比率を選択します。
同じように個数／販売数３を選択して、次は「比率の累積」を選択します。
最後に、データの個数が多い順番（降順）に並び替えて完成です。
自分で使うだけであればこれで良いですが、人に見せたりする場合は表題を変えると、見栄えがよくなります。
いかがでしたでしょうか？
ピボットテーブルを使えば、面倒なヒストグラムも度数分布表も比較的簡単に作ることができます。
平均値や時系列グラフだけではなく、様々な角度からデータを見ることは大切です。
在庫管理システムを導入すれば、作業の効率化だけではなく蓄積したデータを活用することが大切です。
データは使って初めて価値が出ます。今回のようなデータ分析で今まで見えなかったことが見え、
気づきにつながります。さらなる効率化や適正在庫にも役立ちますので、ぜひ試してみてください。
在庫管理やシステム導入、データ活用に関するご相談はお気軽にどうぞ！