Accessのクエリ。パレート図でお馴染みのABC判定を算出するにはどうしたらいいの？
＜ABC分析＞
パレート図でお馴染みのABC分析を、Accessにデータがあるならば、わざわざ、Excelにエクスポートして算出よりも、Access上でつくってしまったほうがいいのではと、考えます。
ただ、ちょっと演算フィールドを追加しないといけないので、大変というか面倒ではあります。
作りたいのは、次のクエリ。
今回は、構成比の累計が80%未満なら、「A」80%～90%未満なら「B」。
それ以外は「C」とします。
元になるテーブルが次のテーブルです。
クエリを作っていきます。作成タブのクエリデザインをクリックします。
構成比の算出からABC分析は始まります。
あと、売上高を降順にしておきます。
構成比を算出するための、演算フィールドを確認してみましょう。
構成比: [売上高]/DSum("売上高","T商品A売上")
構成比を算出するには、売上高を売上高の総合計で除算しますので、DSum関数をつかって、売上高の総合計を算出します。
DSum関数の引数は、
DSum(フィールド名,テーブル&クエリ名)
です。
それと、小数点以下の桁数が除算しきれないために、多くの桁数が表示されてしまうので、パーセント表示に変更します。
「構成比」の演算フィールドをクリックしておいて、クエリツールのデザインタブにある「プロパティシート」をクリックします。
プロパティシート作業ウィンドウが表示されるので、標準の書式を「パーセント」にして小数点以下表示桁数を「1」にします。
構成比を算出できたので、構成比の降順とした累計値を算出したいのですが、Excelのように、行という感覚ではなく、レコードで管理しているため、降順で表示されているまま累計値を算出することができません。
そのため、順位を算出して、その順位を使って、累計値を算出していきます。
演算フィールドは、
順位: DCount("構成比","Q商品A構成比","構成比>" & [構成比])+1
ExcelのようにRANK.EQ関数のような順位を算出する関数が、Accessにはないので、Dcount関数を使います。
Dcount関数の引数は、
Dcount(フィールド名,テーブル&クエリ名,条件)
自分自身を越える値があれば、数えるという計算式ですから、値を越えない、つまり「1位」のときは、「0件」ということで「0」を算出してきますが、「0」では困るので、「+1」することで順位を算出できます。
【累計構成比を算出】
算出した順位をつかって、累計構成比を算出します。
累計値は合算ということですから、使う関数は、Dsum関数です。
演算フィールドは、
累計構成比: DSum("構成比","Q商品A構成比","順位<=" & [順位])
ここで、ちょっと注意というかポイントがあって、算出された値は、『文字数値型』ということで、「テキスト型」になっています。
算出できた累計構成比をつかって、ABC判定をつくっていきます。
【IIf関数をつかって、ABC判定を算出】
改めてですが、今回の判定は、構成比の累計が80%未満なら、「A」80%～90%未満なら「B」。それ以外は「C」とします。
判断分岐をするには、IIf関数をつかいます。
さらに、三分岐ですからIIf+IIf関数というネストで算出していきます。
ABC判定の演算フィールドは、
ABC判定: IIf([累計構成比]<0.8,"A",IIf([累計構成比]<0.9,"B","C"))
これで、完成しましたので、実行ボタンをクリックします。
ご覧のように、様々な関数を駆使して、ABC判定を算出することができました。
やっていること自体は、Excelとあまり差はありませんが、Accessの演算フィールドで関数をつかって算出するのが、わかりにくい場合には、ExcelにエクスポートしてExcel上で算出するという方法でいいと思います。
