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

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

ABC分析はエクセルを使えば簡単に算出することが
できます。ABC分析は、改善活動では特に重要です。
エクセルで作る方法をぜひマスターしてください。

複雑な関数は一切使いません。
グラフを作ったことがあれば、誰でも簡単にできます。

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

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

まずはデータを用意します。
今回は、在庫金額を調べるので用意するデータは、部品の単価と在庫数です。
ABC分析をエクセルでやる方法

次に各部品の合計在庫金額を算出します。
ABC分析をエクセルでやる方法
計算式(部品単価×在庫数)です。画像をご参照ください。

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

並び替えをするとこんなかたちになります。
在庫金額の大きなものから順番に並んでいるのがわかります。

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

累積合計と累積比率を求める

ここからちょっと難しくなります。
難しい関数は使わないので、覚えてしまえば簡単です。

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

ABC分析をエクセルでやる方法
例えば、部品Fは、部品Lと部品Fの合計です。
その次の部品Gの累積在庫金額は、部品Fまでの累積在庫金額と部品Gの合計になります。
最後の部品Jまで足すと、部品Lから部品Jまでの総合計になります。

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

ここまでの作業でデータは出そろいました。
ABC分析をエクセルでやる方法

グラフの作成

累積金額が算出できたら、棒グラフを作ります。
ABC分析をエクセルでやる方法
いらないデータを削除していきます。
残すデータは、在庫金額と累積比率です。
それ以外のデータは全て削除して下さい。

在庫金額と累積比率のグラフを2軸設定にして分けます。
累積比率を選択して、使用する軸を第2軸に変更します。
ABC分析をエクセルでやる方法

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

ほぼできあがりました。

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

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

このグラフから分かるように、部品Lが全体の在庫金額の70%を占めています。
単価が一番高い部品Jの占める割合は、在庫金額の1%くらいです。

改善活動を行う場合は、部品Lをターゲットにして行います。この改善が成功するとかなり効果が出ることが予想できます。

金額だけではなく、営業の成績や機械の効率など様々なところで
ABC分析を使うことができます。

在庫管理に学びの場を!

在庫管理改善支援センターは、在庫管理に解決策が見出せず
お悩みの経営者、担当者向けに「学びの場」を2つご用意しました。

在庫管理講座に行く時間が無い方
ご自宅で学びたい方に
在庫管理の教科書のご案内

講師から直接学びたい方に
短期間で学びたい方に
在庫管理セミナー



関連ページ