タグ別アーカイブ: List関数

【分析】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へ


にほんブログ村

M言語に慣れる_8回目~eachを使いこなしてM関数作成~

【M言語はむずかしくない!】 数式バーでよく見かけるコードの一つに、青字の「each」というコードがあります。青字ということは、システム予約語なので、数式バーに頻繁に登場します。ですので、「each」に慣れれば、自ずとM言語自体にも慣れ親しんでいけます。今回はこの「each」についての事例数多く紹介します。

多くの事例を通じて「each」自体についての意味合いを掴み、M関数内でのeachの使い方に慣れていきましょう!

目次

今回のポイント

「each」の使用例

「each」の中味について

M関数を作成する / List.Select

<まとめ>

今回のポイント

「each」とセットでよく出てくるコードに「アンダースコア / _」があります

「each」と 「アンダースコア / _」の2つは切っても切れない関係にあります

何故なら、「each」は「アンダースコア / _」を使用した関数を簡略したものだからです

ですので、今回は 「アンダースコア / _」についても解説します

 但し、「each」と「アンダースコア / _」の詳細な内容は難解なので、「こういうもの」だと割り切って読み進めてください

そして、最後にM関数「List.Select」を「each」と「アンダースコア / _」を使い作成します

実際に手を動かしてみて「each」と「アンダースコア / _」に対する感覚を掴んでみてください

M関数の作成にあたっては、前回も行ったようにM関数の使用例を活用します!

画像に alt 属性が指定されていません。ファイル名: LISTSELECT.jpg

「each」の使用例

まずは、「each」の理解の入り口として「each」の使用例を2つ紹介します

フィルタリング

下の画像のように、エディタ内に「氏名」「部門」という列名の列があるデータがあるとします

こちらの部門列から「営業」をフィルタリングします

すると、数式バーは以下の表示になり、「each」が条件式に合うものに対して使われているのが分かります

抽出

前述のデータにて、今度は「氏名」の列から「苗字」を抽出します

使用するメニューは「変換」タブ内の「抽出 / 区切り記号の前のテキスト 」になります

区切り文字として使用する記号は「半角スペース」を使用します

すると、数式バーは以下の画像のような表示になり、「each」と「アンダースコア」(“BeforeDelimiter(“の後に表示)が使用されています

上の画像だと分かりずらいと思いますので、文字列に直して表示し直したものも確認しておいてください

= Table.TransformColumns(フィルターされた行, {{“氏名”, each

Text.BeforeDelimiter(_, ” “), type text}})

以降は有料ページ/課金サービスへ

M言語に慣れる_16回目_List関数

【M言語は難しくない】今回は、List関数について1つ事例を解説します。内容自体としては「M言語に慣れる_3回目~ダイナミックフィルタリング~」と同じ様な事を行います。

この3回目の時には、まずは変換タブにて最高値を算出しました

 そして、こちらの「”Cal-Max”ステップ」を参照してダイナミックフィルタリングを行いました

今回は、最高値を「変換タブ」からではなく、カスタム列・作成画面からList関数で計算します

目次

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

カスタム列の作成

まとめ

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

今回は、前述の3回目と同じデータを使用します

こちらのデータで各行に、「最高値-各受注金額」の列を作成します

以降は有料ページ/課金サービスへ

不規則に散らばっているデータを一括取得~M関数~

 今回は上の画像のようにデータが複数シートに散らばっており、しかも行見出し位置/ヘッダー行不規則なデータを一括で取得します

今回の最大のポイントは、List関数で行見出しの位置を取得する点です

そこさえクリアすれば、意外と簡単にできます

では、今回使用するデータと行いたい事の確認から解説を始めます

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

今回使用するのは次のファイルです

このファイルの中には3つのシートがあります

但し、前述のように3つのシートそれぞれの行見出し位置が違います

 2020年1月のデータは1行目から始まっていますが、2月のデータは2行目から始まります

そして、3月のデータは3行目からです

 この3つのシートからM関数を組み合わせてデータを一括で取得するのが、今回行いたい事です

今回のポイント

 今回の最大のポイントは、前述のようにList関数で行見出し位置を取得することです

 行見出し位置を取得するには、行見出しにある「客先コード」をキーにして、M関数「List.PositionOf」を使います

List.PositionOf関数の他にも2つM関数を組み合わせます

List.PositionOf

List.PositionOf関数はリストの中から、指定の値の位置を取得します

書き方は、次の通りとなります

List.PositionOf(リスト名,取得したい値)

ここで、注意点が1点あります

M言語は0ベースである点です

例えば、List.PositionOf関数を使用すると、次の画像の「客先コード」の位置はではなく「0」とでます

これは、M言語が0で始まる仕組みになっているからです

Table.RemoveFirstN

Table.RemoveFirstNは、テーブルから指定した行数を削除するM関数です

書き方は次のように書きます

Table.RemoveFirstN(テーブル名, 削除する行数)

Table.PromoteHeaders

Table.PromoteHeadersは、1行目を見出しに昇格させるM関数です

Power Queryエディタ内にも同じメニューがあります

今回の内容は、後述しますがM関数で行った方がベターです

このM関数の書き方は、次の通りとなります

Table.PromoteHeaders(テーブル名)

今回のポイントである、3つのM関数の内容を確認したところで、本格的な解説をはじめます

ファイルの読込

まず最初に、前述のサンプルファイルの読込処理を行います

サンプルファイルとは別のファイルで、下の画像の処理を行います

 上の画面の「ブックから」で前述のサンプルファイルを指定した後、Power Queryエディタ(以降、エディタ)が開くと次の画像のような状態になっています

上の画像の左から2つ目の列にある「Data」が今回のポイントの一つです

 各行にある「Table」の文字の横をクリックすると、次のGIF画像のように中味が見れます

次からはこの「Data」列を活用して、カスタム列をM関数により作成します

カスタム列の作成

List.PositionOf関数で行位置を抽出

では、カスタム列・作成画面を開きましょう

 ここで確認ですが、行位置を取得する対象は下の画像の「客先コード」の文字です

 上の画像を見ると、「客先コード」にはⅰ)Data/Table➡ⅱ)Column1➡ⅲ)客先コードの順で指定するとたどり着けるようになっています

 カスタム列・作成画面でも、M関数/List.PositionOfでの引数設定時に上の流れを使用して指定します

上の画像のようにList.PositionOf関数の引数を次の様に指定します

一引数:[Data][Column1]

第二引数:”客先コード”

そして、カスタム列・作成画面の下にあるOKボタンを押すと次の画像のように客先コードの位置が出力されます

あくまでM言語は0から始まるので、その点についてはご注意ください

 1行目の内容では、客先コードは1行目にありますがM言語のベースに従って0と出力されています

Table.RemoveFirstNで不要な行の削除

 List.PositionOf関数により行見出しの位置は抽出できましたので、「行見出しの位置-1」分の行数をM関数/Table.RemoveFirstNを使用して削除します

Table.RemoveFirstN関数の書き方は、前述の通り次の通りです

Table.RemoveFirstN(テーブル名, 削除する行数)

今回は上の第一引数のテーブル名には、[Data]を入力します

そして、第二引数には上のList.PositionOf関数をー1をせずにそのまま設定します

 上の画像の通りに数式を指定してOKボタンを押すと、「本来、行見出しにするべき行/客先コードのある行」が次の画像のように1行目に来ています

但し、このまま作成されたテーブルを展開すると不都合な点が1点あります

 本来、行見出しにするべき行が行見出し/ヘッダーになっていませんので、このまま展開作業をすると不要な行見出しがデータとして入り込んでしまいます

ですので、もう一つのM関数で不要な行を削除できるようにします

Table.PromoteHeadersで見出し行を作成

では、最後の仕上げとして各テーブルの1行目を見出し行/ヘッダーにします

 こちらは、M関数/Table.PromoteHeadersの()の中にこれまでの内容を入れるだけです

Table.PromoteHeaders関数の内容を反映した結果が次の画像です

 上の画像の内容で、各テーブルを展開処理すればいいのですが、その前に既存の列は削除しておきましょう

「他の列の削除」が終了したら、後は各テーブルを展開するだけ終了です

<まとめ>

 今回は、List.PositionOf関数をはじめとする3つのM関数を組み合わせて、不規則なデータを一括で処理できるようにしました

最大のポイントは、不規則な行見出しの位置をM関数で取得する点です

 それさえできれば、後は機械的にM関数を組み合わせるだけで一括処理ができるようになりました

今回の題材は、M関数の魅力、そしてM言語の魅力を知るのにいい題材だったと思います

ぜひ手を動かして体験してみてください

長文に最後までお付き合いいただきありがとうございました

参考までに、今回の内容の完成版のファイルを添付します

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

にほんブログ村

指定した範囲の値を使用してフィルタリング

 今回はM関数を使用して、事前にエクセルシートに記入した複数の値を基にしてフィルタリングを行う方法を解説します

M関数に慣れるきっかけになったら幸いです

今回使用するM関数は「List.Contains」という関数です

ちなみにM言語では、大文字小文字を認識するのでContainsのCの入力には注意が必要となります

今回の使用するデータとやりたいこと

今回使用するデータは、次の画像のデータです

*下の画像では業種IDが4番までしか写っていませんが、実際は12番まであります

上のデータの「業種ID」をいくつかエクセルシート上にフィルタ項目として指定し、指定した業種IDのレコードのみをシートに読み出したいと思います

 上のGIF画像では、事前に「2」「4」をシートに読込んである状態から、フィルタ項目に足すことで「2」「4」「」を読み込みました

今回の処理のポイントは、一度ダミーの値にてフィルタリングしてから、作成されたコードにM関数を組み込むことになります

では、解説を本格的にはじめたいと思います

ダミーの値でフィルタリング

まずは、前述の元データをPower Queryエディタ(以降エディタ)に読込ます

次に、新たにできたクエリを「参照」して更に新たなクエリを作成しておきます

 参照して作成したクエリの「業種ID・列」にて、次の画像のように適当な値をフィルタリングしておきます

すると、数式バーが次の画像のようになっているはずです

フィルタリングを行うためのリスト作成

 エディタ上でフィルタリングを行うためのリストは、エクセルシート上にある前述のフィルタ項目を「ドリルダウン」して作成します

まず、エクセルシート上の業種IDを指定したフィルタ項目をエディタ上に読込みます

次に上の画像の見出しの箇所/フィルタ項目で、右クリックをします

ドリルダウン」という項目が出てくるので、こちらをクリックします

すると、次の画像のようにエディタ上にリストが作成されています

*下の画像では作成したリストの名前を「Filter」にかえてあります

数式バーにリストの組み込み

 本題に入る前に、M関数の「List.Contains」がどういうものかを確認しておきましょう!

上の図は、M関数「List.Contains」の使用例です(空のクエリから見れます

2番目の引数の「3」が1番目のリストの中に含まれていれば、TRUEを返します

 つまり、今回は「List.Contains」を使用することでTRUEが返ってきたを表示する形にすればいいということです

 ではもう一度、前述のダミーの値でフィルタリングした後の数式を確認してみましょう!

画像に alt 属性が指定されていません。ファイル名: image-31.png

上の画像の「each」に続く「」の中は、「List.Contains」で置き換えます

List.Containsの書き方は次の通りになります

=List.Contains(リスト,値)

今回は上記のリストについては、前述のドリルダウンにより作成した「Filter」を指定し、には「業種ID列/[業種ID]」を指定します

 上の画像のように数式を指定すれば、事前に「フィルタリング項目」で指定してあった業種IDの行のみが表示されます

<まとめ>

 今回はドリルダウンの仕組みを使用して、エクセル上で複数指定した値にてダイナミックにフィルタリングを行う方法を解説しました

M関数としては、「List.Contains」を使用しました

 一度、ダミーの値でフィルタリングしておいた箇所に「List.Contains」を組み入れただけなので、無理なくM関数の活用が行えたと思います

この記事がM関数に慣れるきっかけになれば幸いです

最後まで記事を読んで下さり、誠にありがとうございました

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

にほんブログ村