【分析】Power Queryで手軽にABC分析

　ABC分析は「重点分析」とも言われ、分析手法の中でも最も基本的なものです。ところが、実際にエクセルでABC分析用の表作成を行おうとすると「データのバケツリレー」の手間を要します。データのバケツリレーは「列から列」へと続き、数式を細かく設定する手間もかかります。今回紹介する方法であれば、Power Queryエディタ画面（以降、エディタ）でシンプルに処理していけます！

以下が、通常のABC分析の表を作成するときの手順です

①重点管理する項目（金額など）を降順で並び替え

②①を累計で表示 ➡「＝」から始まる数式を入力

③②を比率に変換➡ 事前に累計の値を算出 ➡累計の値で各行を割り算

④③をランク付け➡IF式を入力

もし、データの追加などがあったら、①～④の修正作業が必要です

もちろん、データ自体が変われば、①～④の更新処理が必要です

Power Queryであれば、一度、クエリを作成してしまえばデータ更新時に「ソース変更」「クエリの更新」をクリック処理をするだけで済みます

今回使用するデータと行いたいこと

使用データ

今回は、下の画像にある２つの列から構成されるデータを使用します

行いたいこと

前述の使用データの各行に、次の内容でランクを付けます

A ➡全体・販売個数の50%以下

B ➡ 全体・販売個数の80%以下

C ➡ 全体・販売個数の80%超

今回のポイント

今回使用する主な技術は、主に以下の１～３の内容です

１と２は過去に紹介した内容です

最後の３．List関数が今回の一番のポイントになります

１．並び替え/降順

まずは元データを降順で並び替えます

２．条件列の追加

「列の追加タブ」の「条件列」メニューでランク付けを行います

３．List関数

このList関数で累計値と比率を計算します

List関数は2種類使用します

まず1つ目は、List.Select関数です

リストから条件に沿う値を抽出し、リストを再作成します

書き方は次の通りです

List.Select(リスト,条件)

上の画像では、１～４の値からなるリストから、２超の値を抽出した上でリストを再作成しています

2つ目は、List.Sum関数です

こちらの関数は、直感的にも分かり易いと思います

List.Sum()の丸括弧の中に入れたリスト内の各値を合計します

但し、丸括弧の中に入るのはあくまでリストであり、列ではないことに注意しましょう！

では、今回のポイントを紹介したところで本格的な解説を始めます

尚、解説はエディタ内のみの内容になります

並び替え

まずは、エディタ内で販売個数を降順で並び替えます

累計値

算出ロジック

List関数を組み合わせて累計値を計算するのが、今回の記事の最大ポイントです

累計値を計算するロジックについては、次の画像を基にして解説します

通常のABC分析では、N行目の値とN-1行目の累計値の合計を計算します

今回の記事では、上の画像のようにⅰ）N行目の値以上の値のリストを作成、ⅱ）ⅰのリストを合計、というⅰ）⇒ⅱ）の流れで行います

List.Select関数

まず、カスタム列・作成画面で「販売個数」列を挿入してA列を作成してみます

この処理だと下の画像のように、販売個数と同じ列ができるだけです

では、カスタム列・作成画面で前ステップ名（カスタム列を作成するステップの1つ前）を入れてみましょう

この段階では、前述の「降順に並び替えられたステップ」が前ステップです

下の画像が、前ステップ名を入れたカスタム列・作成画面の画像です

上の画像の内容でカスタム列を作成すると、次の画像のように各行にてリストが作成されます

このリストの中味は、一律に販売個数の列の内容です

この各行のリストから、前述のロジックの通り「各行の値以上」のリストを各行に再作成していきます

こちらのリストの再作成は、List.Select関数で行います

条件式は、一部、過去の記事で解説したカスタム関数の内容を使います

上の画像のように、「(x)=>」にてxを変数として宣言し、「x>=」を条件式とします

この内容でカスタム列を作成すると、行毎に作成されるリストの内容が変わっています

例えば、上の画像のように2番目の行であれば。２つの値しかリストの中にありません

では、前述の紹介したロジックの通りにリストが再作成できたので、List.Sumの解説に移ります

List.Sum関数

では、List.Select関数で作成したリストをList.Sum関数で合計し、累計を作成します

上の画像のようにカスタム列・作成画面に数式をセットしてOKボタンを押すと、累計の列がエディタ内に追加されます

比率

では、累計を計算したので「比率 =各行の累計値÷販売個数の合計値」 を計算します

販売個数の合計値の算出には、前述のList.Sum関数を使います

ちなみに、上の画像の「追加されたカスタム」とは前ステップ名です

ランク付け / 条件列

では、最後にランク付けを行います

ランク付けは「列のの追加タブ」にある「条件列」で行います

記事の冒頭にあったように、ランク付けの条件を「条件列の追加」画面に設定します

比率が0.5以下であれば ➡ A

比率が0.8以下であれば ➡ B

とします

上記の条件以外はCとして出力するようにします

この条件列を作成すれば、ABC用の表作成は完成です

　私は以前、企画の仕事をしている時にABC分析表を毎月作成しておりましたが、面倒で仕方ありませんでした

　今回、紹介したPower Queryの仕組みであれば、一度クエリを作成してしまえば、毎月ほぼ「ソース変更」「クエリ更新」をクリック処理で行うだけで済みます

分析で一番大事なのは、数字を解釈することです

　今回紹介した仕組みで、表作成を効率化して有意義な分析を行えるようにしていきましょう！

次回からはRFM分析の解説をはじめます！

にほんブログ村