Excel。パレート図のABCの棒グラフを自動的に別々の色に変更したい
＜ABC分析とパレート図＞
データが全体のA～Cのどこにあるのかがわかる、ABC分析。
数式を確認しておきましょう。
C2の構成比は、B9のSUM関数で算出した、売上高合計をつかって、算出しています。
=B2/$B$9
B9の合計は、オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。
パーセント表示にして小数第一位まで表示することにします。
オートフィルで数式をコピーします。
D2の累計構成比は、C列に算出した構成比の累計値です。
D2の数式は、
=SUM($C$2:C2)
始点を絶対参照にして、終点は、そのまま相対参照のままにします。
これで累計が算出できます。オートフィルで数式をコピーします
E列のABCですが、今回は、累計構成比の70%以下がA。
90%以下がB。
それ以外がCとします。
それを踏まえて、E2の数式は、
=IFS(D2<=70%,"A",D2<=90%,"B",TRUE,"C")
IFS関数をつかっていますが、IF+IF関数のネストでもOKです。
この数式も、オートフィルで数式をコピーします。
これで、どの店舗の売上高が、ABCのどのカテゴリーにあるのかが、確認できました。
このデータを元に、パレート図をつくります。
A1:B8とD1:D8を範囲選択します。
挿入タブの「統計グラフの挿入」のヒストグラムにパレート図があります。
これで、パレート図が完成しました。
ただ、できれば、A・B・Cなのかがわかりにくいので、色分けをしたいのですが、色分けをするには、棒グラフを一つずつ選択して、色を変える必要があります。
今回のデータは、少ないので、対応できる数かもしれません。
しかし、本来は、Cランクのデータは非常に多いので、自力で、棒グラフを選択して、色を変更するというのは、ほぼ無理といえます。
グラフ専用のアプリなどでは出来るようですが、Excelでは出来ないのでしょうか。
そこで、自動的にA・B・Cで色分けできるパレート図を紹介いたします。
Excelのグラフは、そのグラフを作るための表が必要になりますので、次のように表を修正します。
A・B・Cという列を追加します。
F2に設定した数式は、
=IF(E2="A",B2,0)
G2の数式は、
=IF(E2="B",B2,0)
H2の数式は、
=IF(E2="C",B2,0)
すべて、オートフィルで数式をコピーします。
追加した3列は、色分けするための列です。
ランクAならば、売上高をそのまま転記します。それ以外ならば0とします。
これを、ランクB・ランクCとつくります。
この追加した列をつかって、改めてパレート図をつくります。
A1:A8とD1:D8とF1:H8を範囲選択します。
挿入タブのおすすめグラフをクリックします。
グラフの挿入ダイアログボックスが表示されます。
すべてのグラフタブの「組み合わせ」をクリックします。
累計構成比は、第２軸にチェックマークをいれて、折れ線グラフに変更します。
A・B・Cはすべて、積み上げ縦棒グラフに変更します。
そして、OKボタンをクリックします。
A・B・Cの棒グラフが別々の色にわけられたパレート図を作ることができました。
なぜ、棒グラフを、積み上げ縦棒にしたのかというと、棒グラフの太さを変更したい場合、積み上げ縦棒グラフにしておかないと、太さを変更することができないからです。
0という数字があるので、描かれていませんが、その0の縦棒グラフも太くなるのが原因です。
では、棒グラフを太くするのと、折れ線グラフの第2軸縦軸の上限を100%に変更していきます。
縦棒グラフを選択して、書式タブの「選択対象の書式設定」をクリックします。
データ系列の書式設定作業ウィンドウが表示されますので、系列のオプションにある「要素の間隔」を0にします。
つづいて、第2軸の縦軸をクリックします。
作業ウィンドウが、軸の書式設定にかわります。
軸のオプション の境界値にある「最大値」を1にします。
これで、100%までの縦軸にすることができます。
あとは、フォントサイズなどを整えたら完成です。
このように、パレート図用の表を用意することで、自動的にABCで色分けしたグラフをつくることができます。
