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

ABC分析をエクセルでやる方法

ABC分析をエクセルでやる方法

ABC分析とは、重要度の評価や優先順位を決めるに使われる分析方法です。

パレートの法則(全体の大多数の結果は、ごく少数の要素が生み出す)にもとづいたもので、在庫を品目や金額などに分けて分類したとき、データの占める割合の大きさに応じて、重点管理をする在庫を決めることができます。

その結果、在庫管理における改善や対策の時に取り組む優先度を決めるときに使える分析方法が、ABC分析です。

今回解説するABC分析をエクセルで作る方法は最もポピュラーな方法です。

複雑な関数は使いません。折れ線グラフと棒グラフを作ったことがあれば、大丈夫です。

それでは、ABC分析を行う手順を解説します。

在庫管理の専門家がお答えします!

ABC分析を行う目的を決める

まず、ABC分析を行う目的を決めます。

目的に応じてABCにグループ分けする対象が異なります。

今回は、在庫削減することを目的として、どの部品の在庫削減から行えばよいかをABC分析で行います。

ABC分析する対象は各部品の在庫金額になります。

ABC分析に必要なデータ(在庫削減の場合)

今回は、在庫削減活動に取り組みたいオカモト工業の部品A-Lについて調べてみます。

まずは各部品の在庫金額データを用意します。


各部品の在庫数と、それぞれの部品の単価データです。
ABC分析をエクセルでやる方法

次に各部品の合計在庫金額(在庫金額=単価×在庫数)を計算します。
ABC分析をエクセルでやる方法

在庫金額の高い順番に並び替える

合計在庫金額が高い順に並び替えます。
並び替えタブを選び、在庫金額を選択し、降順(値が大きいもの順)を選択してOKを押してください。
ABC分析をエクセルでやる方法

並び替えをすると下記の表のように、在庫金額の高い部品Lから順番に並んだ表ができあがります。

ABC分析をエクセルでやる方法

無料サンプルも配布中!

累積在庫金額と累積比率を求める

次に、この表に累積在庫金額と累積比率の2つのデータを追加します。
計算といっても、難しい関数は使わないので、覚えてしまえば簡単です。

累積在庫金額とは、1つ前の部品までの全合計に自分自身の合計を足したものです。

ABC分析をエクセルでやる方法
例えば、部品Fまでの累積在庫金額は、

部品Lの在庫金額(\120,000)+部品Fの在庫金額(\72,000)=\192,000
同じように最後の部品Jまでの累積在庫金額を計算します。

なお、部品Jまでの累積在庫金額は全部品の在庫金額の合計と同じです。

次に累積比率を計算します。
累積比率の計算方法は、各部品の累積在庫金額を全部品の総金額で割ったものです。
ABC分析をエクセルでやる方法

部品Jの累積在庫金額は、全部品の送金額と同じなので、1(100%)になります。

ここまでの作業でABC分析をするためのデータは準備できました。
ABC分析をエクセルでやる方法

実はABC分析はこれで完成です。しかし、表だとわかりづらいので、一目でわかりやすいようにグラフを作ることが多いです。

グラフの作成

ABC分析のグラフは、棒グラフと折れ線グラフを組み合わせた2軸グラフです。

図のように「挿入」から棒グラフを選択してください。
ABC分析をエクセルでやる方法
いらないデータを削除します。
残すデータは、在庫金額と累積比率で、それ以外のデータは全て削除してください。

棒グラフを在庫金額とし、累積比率を折れ線グラフにします。
在庫金額と累積比率のグラフを2軸設定にして分けます。

2軸グラフにするために「データ系列の書式設定」から、「第2軸」を選びます。
ABC分析をエクセルでやる方法

次に、累積比率を選択して、今度は「系列グラフの種類の変更」をクリックします。
グラフの中から折れ線を選びます。
ABC分析をエクセルでやる方法

ほぼできあがりました。

あとは、第2軸の表示を調整します。
第2軸の「軸の書式設定」を選択します。
第2軸の最大値を1に、表示形式をパーセンテージに
変更します。
ABC分析をエクセルでやる方法

完成です!
ABC分析をエクセルでやる方法

このABC分析の結果から、改善活動を行う場合は、部品Lをターゲットにして行います。この改善が成功するとかなり効果が出ることが予想できます。今回は、在庫金額を取り上げましたが、他にも作業時間の短縮や重点管理部品の選定など、様々な方法に使えます。

在庫分析・在庫管理に関するお問合せ

自社の在庫分析をしてほしい、在庫管理の相談がしたいなど、在庫管理に
関することであれば、何でもご相談ください。

div class=”gray‐box” style=”text-align: center;”>