タグ別アーカイブ: ABC分析

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

 

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

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

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

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

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

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

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

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

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

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

使用データ

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

行いたいこと

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

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

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

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

今回のポイント

今回使用する主な技術は、主に以下の1~3の内容です

1と2は過去に紹介した内容です

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

1.並び替え/降順

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

2.条件列の追加

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

3.List関数


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

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

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

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

書き方は次の通りです

List.Select(リスト,条件)

上の画像では、1~4の値からなるリストから、2超の値を抽出した上でリストを再作成しています

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

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

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

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

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

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

並び替え

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

累計値

算出ロジック

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

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

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

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

List.Select関数

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

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

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

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

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

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

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

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

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

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

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

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

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

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

List.Sum関数

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

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

比率

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

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

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

ランク付け / 条件列

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

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

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

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

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

とします

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

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

<まとめ>

今回は、Power QueryでABC分析の表を作成しました

一番のポイントは、List関数を2つ組み合わせて「累計」の列を作成する点です

List関数の中には、常に「リスト」を指定します

M言語では、「リスト」と「」は明確に違います

今回の内容では、List関数の丸括弧の中は「ステップ名[列名]」で指定しました

この「ステップ名」を指定する場合があることを強く意識しておけば、後はそれ程難しい点はありません

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

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

分析からダッシュボード作成を体験する講座

【実践的エクセル力向上】エクセル便利機能をうまく活用する力➡エクセル力をあげてワンランクアップ上の仕事ができるようになりましょう!

お申込み

  *ストリートアカデミーのページに遷移します

エクセルの進化は凄まじいものがあります

驚くほど便利な機能が備わっています

重要なのはそれらの便利機能を「知る」かどうかです

「便利機能を知り、うまく便利機能を組み合わせて実際の仕事を効率化する」、それがエクセル力です

 今回のレッスンでは目から鱗の便利機能に沢山触れて頂き、エクセル力を上げる勘所を掴んで頂きます

エクセル力事例

エクセル力の2つの例を紹介します

1つ目はABC分析のランク分けです

従来のABC分析の場合には、ランク分けは次の3つの手順で行っていました

①データを降順で並べ替え➡

②ランク分けの基準作成➡

③IF関数で基準毎にランク分け

 この方法の場合は「ランク分けの基準を変更したり」「データを追加」した場合には①~③をまた繰り返さねばなりません

 今回のレッスンでは「VLOOKUP関数の隠れた便利機能との組み合わせ」により、この面倒なランク分けを自動化します!

 上のGIF画像では、画像上の表にて、ランク別に上位の比率を変更すると、元データのランクが自動的に変更されていきます!

2つ目は双方向グラフです

 このグラフデザインだとABCのランク別に、販売個数と商品数の比較が行い易くなります

一見、難しそうですが、グラフの標準機能を組み合わせるだけで行えます

実は、グラフの軸は反転できるのです!

この「軸の反転」機能を中心に双方向グラフの作成を体験して頂きます

 最終的には、今紹介した2つの事例等を組み合わせて、下の画像の様なダッシュボードの作成を体験して頂きます(全てのコンテンツを一から作成するわけではないので、その点は予めご了承お願いします)

 ダッシュボードの作成では、「スライサー/選択」をはじめとするピボットテーブルの有効活用方法についても解説させて頂きます

レッスンの特徴

個別にマンツーマンでレッスンを行います

 事前に個別に課題や要望等をすり合わせさせて頂き、レッスンプログラムについては調整を行わせて頂きます

 尚、レッスンは体験という位置づけであり、ダッシュボードのコンテンツを全て一から作成・演習するわけではない旨を予めご了承お願いします

では、レッスンでお会いしましょう!

 このレッスンを通じて飛躍的にエクセル力を向上させる機会になるようにしましょう!

お申込み

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

エクセル分析から「ダッシュボード」でのレポート化までを体験する講座

オンラインセミナーでエクセルダッシュボードの講座を行います!

エクセル分析から「ダッシュボード」でのレポート化までを体験する講座

日本ではエクセルダッシュボードについて学べるのはここだけです!

<エクセルダッシュボードとは?>

1目で組織が目標とする数字を共有する技術です

欧米ではVBAと同じぐらい学習されています

TableauなどのBIツールの簡易版です

エクセルでメンテナンスするので、低コストで作成できます

後、作成に必要な技術は、VBAなどを必要としませんので、情報システム部などの協力を仰がなくても手軽に作成できます

エクセルの便利機能を有効活用しますので、エクセルダッシュボードの作成を通じて便利機能を学ぶいい機会にもなります

<今回の講座の特徴>

分析手法としては古典的であり、即時に成果を出しやすいABC分析を題材にしてエクセルダッシュボードの作成を体験して頂きます

従来のABC分析の手法だと何が問題なのか?そして、どうやったらもっと効率的にABC分析を行えるのか?そしてどう結果を魅せればもっとも効果的なのか?についても解説します!

https://youtu.be/Pjggmgi2Qtw

 

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

最速集計の為のABC分析テンプレート

ABC分析で問題となるのはランク分けの手間です。IF関数主体でランク分けをするのでなく、VLOOKUP関数とランク表の組み合わせにより、最速でランク分けを実現します

特徴

・データ並べ替えの手間を省きました

・質(上位)と量のバランスを意識して設定できます(従来は集計してみないと質の状況がよくわからなかった)

・ランク分けの数を3から2、もしくは3から4などに手軽に変更できる

仕組み

・Dataシート(後でVLOOKUP関数について解説)とABC設定シート(後でPERCENTILE.INC関数について解説)の2シートで構成している

・”PERCENTILE.INC関数”を使用して引数1(ランク設定対象の数字が入力されている範囲)、引数2(下の画像であれば1-5%)ランク用販売個数を設定する(下の画像ではAランク:4729個以上、Bランク:280個以上、Cランク:7個以上、Dランク:7個未満)

・VLOOKUP関数(参照方法・近似値)にてPERCENTILE.INC関数にて設定した値を参照してランクを抽出する

アイコン

ABC分析テンプレート 109.00 KB 13 downloads

...

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

ABC分析をエクセル力で改善する9

今回は今まで解説してきた内容を基にしてダッシュボードを作成しましょう

これまで解説してきた内容は大きく分けて2つです。
①ABCランクの区分を自由自在に設定する

ABC分析をエクセル力で改善する3

Gif1

②ABCランクの質と量が見やすいグラフを作成する

ABC分析をエクセル力で改善する6

14

今回は上の2つの内容をダッシュボードで示せるようにしましょう(右半分は今回は割愛します)

アイコン

ABC分析 294.14 KB 41 downloads

...

29

 

1.ランク設定の内容を分かり易くする

ABCランクの設定条件を示しつつ(下の画像の上の枠)、詳細が見たくなったら直接、該当シートに飛べるようにします(下の画像の下の枠)

30

(下の画像の上の枠)

ABCランクを設定したシートからテキストボックスを使って参照します

(セルの参照はテキストボックスの中でなく数式バーでおこなってください)

31

ABC設定のシートではダッシュボードで参照できるようにTEXT関数を使ってデータを成形してあります

(TEXT関数を使わないと , も含めてうまく表示できません)

32

(下の画像の下の枠)

挿入タブからリンクをクリックし該当のシート、セルを指定してください

33

2.ピボットテーブルでABCランクの量と質を表示する

ぜひ以下の記事(特集)を参照してください。ピボットテーブルの見た目を変えるテクニックを紹介しています

ピボットテーブルの役割を変えよう

3.グラフを配置する

ABC、各グラフの位置が分かり易いように凡例の位置を上にして文字を大きくしましょう

35

以上です、また次回お会いしましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する7

前回の最後に予告したように、今回は、ABC分析のランク付けが量と質、双方の観点からどのようになっているのかが分かり易いグラフを作成します

15

最初に、今回のグラフ作成にあたっての最大のポイントを解説します

下のGIFの右下に注目してください

Gif11

軸を反転させているのです!

16

今回は反転に関して、追加で2つポイントを解説して終了します

1.第二軸を設定する

まずは棒グラフを作成します

17

下の画像のように第二軸を指定します

18

2.軸の範囲を第一軸と第二軸で揃える

第一軸、第二軸ともに-100%~100%で揃えましょう!

19

今回は以上です!

次回からダッシュボード作成も絡めて実際にサンプルデータを使用してグラフを作成しましょう!
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する6

今回はこれまでのまとめから始めましょう

①実用的なエクセル力とは

ビジネスに修正はつきものです。エクセル機能を組み合わせて、修正にも即時に対応できるような能力を身に付けましょう!

ABC分析をエクセル力で改善する2

②分析にも修正はつきものです

分析の肝の一つは区分です。ABC分析は区分を最少単位で行います

但し、試行錯誤はつきものです。どこからどこまでを1つの区分とするかを変える度に、エクセルの処理を最初からやり直していたら効率が悪すぎます。VLOOKUP関数とPERCENTILE.INC関数をうまく組み合わせて修正作業を自動化しましょう

ABC分析をエクセル力で改善する4

③表への追加にも対応できるようにしておきましょう

従来のABC分析の場合にはデータを追加した場合には、並べ替え作業からやり直しです。②とあわせてテーブル機能も組み合わせて自動化しておきましょう

ABC分析をエクセル力で改善する5

④ABC分析は質と量の公式であらわせます

ABC分析において重要なのはビジネス上優先すべき質の高い区分(グループ)を特定する観点です

ところが、従来の方法だと質と量の公式の観点が分かりにくいのです

②で述べたVLOOKUP関数とPERCENTILEINC関数をうまく組み合わせて、質と量の双方向からランク付けを行っていきましょう

ABC分析をエクセル力で改善する5

 

さて、今回からこれまでとは違う方向の内容に取り組んでいきます

まず、下の画像を見てみてください

13

Aランクがどの位の質と量のなのか、理解できましたか?

それに質の78%と98%は手で入力しています

これはとても非効率です

次の回からはピボットテーブルと連動した双方向グラフについて解説します

14

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する5

前回は再び、ABC分析の肝に触れました

9

今回はこの点をもう少し詳細に解説するところからはじめましょう

 

1.分析の公式の一つに”質x量”があります

9

上記を質x量の公式にあてはめると

上位20%の商品⇒量は全体の20%(量)だが、売上の80%を占める(質)

となります

従来のABC分析の手法ですと、この質と量の関係が見えてこないのです

5

では、ここからVLOOKUP関数とPERCENTILEINC関数とテーブル機能を組み合わせると、この量と質の関係が見えてくるのかを実際の使用例でみてましょう

2.VLOOKUP関数とPERCENTILEINC関数とテーブル機能を実践的に組み合わせる

まず、”Data”のシートに加えて”ABC設定”の状況を管理するシートを用意しておきます

10

そして、前もってDataシートにある表はテーブル化しておきましょう

テーブル化しておくことで、表に値を追加しても数式が自動的にセットされます

(詳細は以下のページなどを参照してください)

テーブルを作成しよう~数式を自動入力~

ABC設定のシートは以下の様な構成になっています

11

そして、DataシートのVLOOKUP関数(近似値で検索)と連携させておきます

12

ここまで設定しておけば”量x質”の公式による観点でのランク設定が自由自在に行えます

(下の表はDataシートの内容ですが、上から2行目/販売個数:2,2136行目:1,876のランクが上の操作と連動して変わっています)

Gif5

データを追加した時もランク付けが自動的に行われます

Gif8

もし、ABC、3つのランクでなく、ABCD、4つのランクにしたい時もCランクの設定個数を変えれば自動的に作成できます

Gif10

これで大分、実践的になってきました。次回はこれまでの話しを少しまとめた上で、次の段階にいきたいと思います

ちなみに実際にサンプルを動かしてみたいという方は以下をご利用ください

アイコン

ABC分析 258.23 KB 44 downloads

...

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する4

前回はABC分析のデメリットに触れました

5

今回から何故、この2つのプロセスが問題かについての解説と、克服方法について解説します

1.並び変えは時間の浪費を生む

下の図は”販売個数”に従って

⇒構成率を計算⇒累計・構成率を計算⇒ランク付け(IF関数でランク付け)

を行っています

この表に販売個数/5,000を付け加えたとします

(商品別に行が構成されているという前提です)

そうするとどうなるでしょう?

6

再び同じ作業を繰り返した後に、Aランクが増えます

Aランクが微妙に多すぎたりします

すると、Aランクの基準を変えたくなります、つまり、IF関数の条件を変更するということです、

そしてBランクも・・・

これでは時間の浪費です

もっといい方法がないものでしょうか?

ここがエクセル力の見せどころです

<エクセルの基本機能を組み合わせて実務的な活用をする力>

2.VLOOKUP関数とPERCENTILE.INC関数、とテーブル機能を組み合わせる

ここで違和感を感じる人がいるかもしれません

”何故VLOOKUP関数”なのか

”PERCENTILE.INC関数”とは?

では次のファイルで上記2つの関数のエクササイズをしてみましょう!疑問が解けると思います

アイコン

ABC分析 258.23 KB 44 downloads

...

目次から”VLOOKUP関数&Percentile関数”のシートに飛んでください

7

まず、VLOOKUP関数ですが、70を検索値としてEからF列にある表を参照していますが、エラーになっています

何故なら、表に70が無いからです

では参照方法を0から1に指定し直したらどうでしょう?

Gif3

検索値を超えない範囲(今回で言えば70を超えない範囲)で最大の値を参照します(今回は10)

つまり、近似値で検索をするのです

このVLOOKUP関数の近似値検索を利用すれば、ランク付けの基準を変える時に、

数式を変更しなくても、

並び変えを行わくても、

表を変えさえすれば、ランク付けは変更されます

他の言い方をすると、累計構成率ではなく、直接的に値(今回の事例では”販売個数”)を参照してランク付けが行えます

VLOOKUP関数の詳細な使い方は以下をぜひ参照してください

VLOOKUP関数とIF関数だけを使い倒す#1

次にPercentile関数です

下のGIF画像の右側にある値の一覧の中から、指定した基準の値を拾ってくくれます

例えば50%と指定すれば1から10の間で上位50%に位置する値を拾います

Gif4

数式の指定の仕方自体はとてもシンプルです

参照する値の範囲を指定し、加えて基準を指定します

(ここで80%を20%に置き換えていることにご注意ください)

8

PERCENTILEINC関数の概要が明確になってきたと思います

VLOOKUP関数とPERCENTILEINC関数を組み合わせることで、

ABC分析の肝・・・例:上位20%の商品で売上の80%を稼いでいる

この肝に準じて表作成を自動的に行っていけるのです

 

次回は再びこの肝から解説をはじめて、より詳細に解説を行っていきます
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する3

ところABC分析とは何でしょう?

辞書を見ると下記のように記述がありました

4
https://kotobank.jp/word/ABC%E5%88%86%E6%9E%90-1360
要はビジネスの対象を3つに分け、対策が効率良く打てそうなところを抜き出すということです

背景には20-80%ルールがあり(売上の8割は2割の顧客で稼いでいるなど)、とにかく対象を3つに分けてしまうのがポイントです

分析の本質はこのグループ分けです

グループ分けをすることで比較が行い易くなり、優先順位が決めやすくなります

ABC分析を行うメリットは”優先順位を決めやすい”というところにあると思います

ビジネスにはコストと時間がつきものです

いくら時間をかけて分析して立派な実行計画を経てても実行できなかったら意味がありません

その点、ABC分析では、量と質の観点からシンプルに優先順位を決めることができます

一方、ABC分析のデメリットとは何でしょう

それはABC分析を実施する過程にあります

ABC分析を実施するに当たっては、以下の2つのプロセスを踏みます

1.データを降順(売上金額など)で並べ替える

2.データにランクを付ける

この2つのプロセスが何故、デメリットになるのかを次回、詳細に解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ