Excel2013。ABCパレート図のデータを作ってみる。
ABCパレート図
以前もご紹介したことがありますが、今回はExcel2013を使って、ABCパレート図を作成するためのデータの作り方をご紹介していきます。
Excelの基本的な操作がアチコチにちりばめられていていますので、
スキルアップにちょうどいいかと思いますよ。
まずは、完成図ですが、
ABCパレート図をつくるには、このようにABCにわけた表を作らなければなりません。
では、最初から作っていきます。
下記の表をご覧ください。
まずは、金額の大きい順。すなわち降順で並び替えをしましょう。
一つの条件での並び替えは、降順ボタンをクリックすれば完成しますので、
B1の金額をクリックして、降順ボタンをクリックします。
すると、まず、金額が降順で並びました。
ついでに、見出し行のセルを塗りつぶして中央揃えを設定しておきましょう。
では、A21に総計と入力して、B21に金額の合計を算出しましょう。
算出には、オートSUMボタンを使いましょう。
また、B2:B21に、桁区切りも設定します。
ここまで、作成できましたので、次は、構成比を求めていきます。
C2をクリックして、数式を作成します。
梅田の金額は、総計、つまり金額の合計のうち何%占めているのかということを
算出したいわけです。
この比や率というときには、割り算で求めることになりますので、
B2とB21を使って算出することになります。
正解は
=B2/$B$21
ですね。Excelは、電卓やそろばんと異なり、割るほう・割られるほうを考えている暇があったら、
とりあえず、算出してみたほうが早いと思います。
あと、B21は固定しておきたいので、絶対参照の設定も忘れないようにしましょう。
では、オートフィルハンドルを使って、B21まで数式をコピーしましょう。
算出は出来ましたが、パーセント表示にしたほうがいいですね。
また、小数点第二位まで表示しておきます。
つづいて、累計を算出します。絶対参照とSUM関数を使って求めます。
D2をクリックして、数式を作成します。
=SUM($C$2:C2)
これで、オートフィルハンドルを使ってD20までコピーすると累計が簡単に作成できましたね。
そして、E列のランクを作成していきます。
80%未満がA
90%未満がB
それ以外がC
という三分岐の判定をさせますので、IF関数のネストを使って、算出しましょう。
E2をクリックして、
IF関数のダイアログボックスを表示しましょう。
論理式には、D2<80%
真の場合には、A
偽の場合には、IF関数を入れますので、名前ボックスの▼をクリックして、
IF関数のダイアログボックスを表示します。
論理式には、D2<90%
真の場合には、B
偽の場合には、C
と入力したらOKボタンをクリックします。
数式は、
=IF(D2<80%,"A",IF(D2<90%,"B","C"))
ですね。
あとは、オートフィルハンドルを使って数式をE20までコピーしましょう。
最後は、わかりやすいように、条件付き書式を使って、ABCで色分けをしましょう。
A2:E20まで範囲選択をしたら、条件付き書式の新しいルールをクリックします。
新しい書式ルールダイアログボックスが表示したら、
「数式を使用して、書式設定するセルを決定」を選択したら、ボックスに
=$E2="A"
と入力をして、書式ボタンをクリックします。
セルの書式設定ダイアログボックスが表示されたら、塗りつぶしタブで、
好きな色を選択して、OKボタンをクリックします。新しい書式ルールダイアログボックスで、
OKボタンをクリックします。
ランクAの行が塗りつぶされましたね。
あとは、BとCも設定していきます。
これで完成しましたね。
次回は、ABCパレート図を作っていきます。
