タグ別アーカイブ: M言語

【M言語実践】グループ別に累計を集計する方法

過去2回の記事でM言語を実践的に活用する事例を紹介しました

この2回の記事で共通するのは「グループ毎に連番を付与」する技術を使用していることです

今回も「グループ毎に連番を付与」する技術を有効活用して、グループ毎に累計を集計します

M関数のList.FirstNと連番をうまく組み合わせれば、意外と簡単に行えます

ポイント

List.FirstNとは?

List.FirstNは指定されたリストから、指定した条件のリストを作成します

文法としては「=List.FirstN(リスト,条件)」と書きます

例えば、

=List.FirstN(リスト,1)とした場合は1個の値が含まれるリストを作成します

=List.FirstN(リスト,2)とした場合は2個の値が含まれるリストを作成します

このList.FirstN関数の第二引数の条件のところに、グループ別の連番を指定することで、累計の元となるリストを作成します

元データ

今回解説に使用する元データは、下の画像のデータです

こちらのデータのグループに、エディタ内で連番をまずは付与します

上記の画像のようにM関数/Table.AddIndexColumnの第一引数にした列/テーブルは、後でList.FirstN関数の第一引数にしますので削除せずに残しておいてください

List.FirstNによるリスト作成

元データに連番を付与したところで、Power Queryエディタ(以降、エディタ)上で、詳細/テーブルの受注金額をM関数の第一引数、連番を第二引数にしてカスタマイズ列を作成してみます

するときちんとリストが作成できています

では、作成されたリストの中味を見てみます

連番(東京)が1の時には、作成した行の値のみが含まれています

では、連番(東京)が2の時はどうでしょう?

1つ上の行の値と該当行の値がリストに含まれています

今度は、大阪の連番1の場合を見てみましょう!

きちんと大阪の1番目の値/1700000のみが含まれています

作成されたリストの合計

List.FirstNにて各行に作成されたリストは累計の元になるリストになります

最後の仕上げとして、こちらのリストをM関数/List.Sumで合計します

すると、下の画像のように累計が算出されます

<まとめ>

 今回は、M関数/List.FirstNとグループ別の連番を組み合わせて、グループ別の累計を集計しました

今回の集計には、行別のリストが度々登場します

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

この辺りは、手を動かして直感的に理解していった方が習得が早いと思います

とにかく手を動かして身に付けていきましょう!
にほんブログ村

【M言語実践】顧客の2回目のリピート率を分析する方法

 パワークエリはとても便利ですが、M言語となると使い道がよく分からないと思います

今回は、M言語を使用した実践的な分析手法を紹介したいと思います

私は以前、EC通販会社に勤めていました

 EC通販では顧客の顔が見えないので、データからどういう顧客がいて・どういう行動をしているのか・を分析する必要があります

様々な分析を行いましたが、指標として一番重視していたのが「顧客の2回目リピート率」です

顧客の2回目のリピート率が何故重要なのか?

 これには様々な理由がありますが、一番の理由は2回目のリピート率を少しでも改善すれば、売上が長期的に増加するからです

読者の方でも外食をした際に「あの店には2度と行かない・・・」という経験をした方も多いと思います

 逆に同じ店で2度食事をして、その店に慣れてくると3回目、4回目とリピートする意欲する気が高くなると思います

 ビジネスでも「2回目のリピート」というハードルをクリアすることの意義はとても大きいのです

ただし、普通にエクセルで2回目のリピート率を算出しようとするとかなり面倒です

私は一時期、IF関数で注文回数を付与して、2回目の注文だけシートを分けたりしていました

ましてや、2回目のリピートの有無別に顧客分析をしようとするとかなりハードルが高いです 

ところが、M言語を使えば2回目のリピート率は意外と簡単に算出できます!

ポイント

今回使用するデータは会員別の注文データです

 上の画像では2回目の注文があった会員に黄色の印を付けましたが、2回目の注文があった会員もいれば、そうでない会員もいます

ここからまず、会員番号毎に注文回数を付与します

そして、上の画像の表から次のような表を作成します

上の画像では、その前の画像の表から注文回数を「1回」に絞り込んであります

結果として、会員番号の列は会員番号が重複なく並んでいます

そして「2回目判定用」なる列が追加されています

こちらは注文回数の差分です

こちらの差分は注文回数を「1回」に絞り込んだ場合にはになります

この列が1の会員は2回目の注文があった会員です

 つまり、「2回目判定用」の列の合計を「注文回数」の列の合計で割れば2回目のリピート率が出るという仕掛けになります

(注)尚、上記の画像のデータ以前に注文履歴は無いという前提で解説を行わせて頂きます。ですので、本当は初回の注文ではないのでは?という疑問は持つ必要はありません

注文回数の付与

ポイントで解説した通り、まずは会員番号別に注文回数を付与します

こちらについての詳細については、過去の記事をご確認をお願いします

大きく分けて2つ行うことがあります

1つ目は、グループ化です

会員ID別(会員番号別)に「すべての行」でグループ化を行い、会員ID別にテーブルを作成します

次にカスタム列・作成画面にて、テーブル別にM関数/Table.AddIndexColumnを使用して連番を付与します

すると、注文回数が連番で付与されます

注文回数の差分の算出

こちらについても詳細は過去の記事をご参照願います

この差分を算出するには、パワークエリでは本来は困難な「セル単位」や「行単位」の処理に踏み込む必要があります

ポイントとなるのは、こちらも「連番」です

上の画像のように連番をつけると、連番と画面左の行番号とが対応するようになります

ちなみに、M言語は0ベースなので、実際にM言語で使用する1行目は0になります

ここから波括弧:{}を使用します

波括弧は行番号を表します

例えば、注文回数{0}とした場合には、注文回数列の1行目のデータなります

この波括弧と連番をうまく組み合わせることで、注文回数の差分を算出します

上の画像にて黄色の印を付けた波括弧内は、注文回数の各行の1つ下の行番号の値になります([注文回数]の前のDataは前ステップの名称になります)

つまり上の画像内では、注文回数列をA列だとすると<=A3-A2>と同様の計算が行われています

ちなみに、try~otherwiseとすることでエラーを回避しています

注文回数の絞り込み

 上記まで行ったところで、エディタを確認すると以下の画像のような状態になっています

ここから注文回数の列にフィルターをかけます

こうすれば注文回数の列は「1」のみ、2回目判定用の列は0か1になります

ここまでくれば、後は各列を合計して2回目のリピート率を算出するだけです

合計にはM関数/List.Sum関数を使用します

 上の画像では、2つの合計値を/で割り算をすることでリピート率を算出しています

%の表示にしたい場合には、List.Sum関数でリスト化した内容をテーブル化した上で%に型式を変えます

<まとめ>

今回はM言語の仕組みを使用して、2回目のリピート率を算出しました

2回目のリピート率を算出するのに、主に2つのM言語の技術を活用しました

1つ目はグループ化を通じて各グループ毎に連番を付与する仕組みです

2つ目は注文回数の差分を連番と波括弧を組み合わせて算出する仕組みです

 この2つの技術を通じて、注文回数と2回目の注文有無を0か1で表現できるようにしました

 今回は2回目のリピート率を算出しましたが、本当に大事なのは2回目のリピートがあった会員とそうでない会員との違いは何かを調べることです

 今回作成したクエリを複製して途中のステップを削除すれば、会員番号別に注文の有無が表示できます

 他のデータを会員別に紐づければ、2回目の注文があった会員とそうでない会員の購入している商品の違いなども調べることができるはずです

 更に注文回数の差分を抽出する仕組みを応用して、初回から2回目までの日数なども調べることができます

M言語が本当に有効なのはこういった複雑な分析が必要な場面だと思います

 特にグループ化から連番を付与する仕組みと差分を算出する仕組みは、分析作業にて使う場面も多いと思いますので、しっかり活用できるようにしておきましょう!


にほんブログ村

【M言語実践】グループ別に指定桁数にて連番を付与してIDを設定する裏技

 パワークエリはとても便利です。利用者もどんどん増えていると思います。ただ、パワークエリの言語のM言語となると「何ができるの?」となると思いますので、今回は実践での活用例を紹介したいと思います

今回は下の画像の表にある商品に、「グループ別に1から始まる番号」で採番した連番を付与した商品IDを付けたいと思います

今回のポイント

今回はM言語を使用して「グループ別に連番」を付与した後に、”0”で桁数を揃えます

グループ別に連番を付与する方法については過去の記事を参照してください!

こちらは、グループ化機能とM関数のTable.AddIndexColumnを組み合わせます!!

では「”0”で桁数を揃える」という点について、詳細に解説します

例えば、連番が2の場合には「002」にします。もし10の場合には「010」とします

 こちらの桁数を揃える方法については、「例からの列」機能を活用する方法もありますが、今回はM関数の「Text.PadStart」を使用します

このText.PadStartは下のように使います

=Text.PadStar(文字列,桁数,”補う文字列”)

今回は、”0”を補って下の画像のように指定の桁数にします

上の画像では、”1”に”0”を4つ補って5桁にしています

それでは、今回のポイントとなるM関数/Text.PadStartを解説したところで、実際の商品コードの設定に入りたいと思います

文字コードの付与

 今回は、商品グループがCDの場合には「A」、DVDの場合には「B」という風に、商品コードの一桁目を商品グループで表現します

この場合は、条件列を使用するのが一番、手っ取り早いです

例えば、商品グループ名がCDに等しい場合は、Aを出力するといった具合で条件列を設定します

グループ別連番の付与

こちらについての詳細については、過去の記事をご参照願います

概要だけを解説しますと、まずは「すべての行」にてグループ化を行います

すると、グループ別にテーブルが作成されます

その後、カスタム列・作成画面でTable.AddIndexColumnを追加してグループ別に連番を作成します

上の画面でカスタム列を作成した結果が次の画像です

M関数により”0”を補う

 では、グループ別に連番を作成できたので、グループコード/A,B,Cと連番を組み合わせて4桁の商品コードを作成します

グループコード以降は”0”を補って3桁(全体で4桁)にします

M関数/Text.PadStartは次のように使用します

=Text.PadStart(Text.From([連番]),3,”0″)

1番目の引数で「Text.From関数」にて文字列にするのがポイントです

これで”0”を補った3桁が作成できました

この3桁をグループコード/と結合(列のマージ)させれば4桁の商品コードは完成です

次の画像が4桁の商品コードの完成後です

<まとめ>

今回はM言語を使用して、グループ別に1からはじまる連番を採番した上で商品コードを作成しました

ポイントとしてはM関数/Text.PadStartを使用して”0”を指定桁数になるように補う点になります

M関数を使用しない方法もありますが、M関数を使用した方がミスなく作成が行えます

最後に、グループ別に連番を採番する技術はとても実践的な技術です

ぜひ早めに習得しておきましょう!


にほんブログ村

マスタデータの履歴管理を行う方法

パワークエリの「クエリのマージ機能」はとても便利で、エクセル関数のVLOOKUP関数より使いやすいです

「クエリのマージ機能」を有効活用すれば、参照表(マスタデータ)の活用もかなり手軽に行えます

 今回の記事では、「クエリのマージ機能」に加えて「グループ化機能」も活用して、マスタデータを更に有効に活用できるようにする方法を解説します!

マスタデータの履歴管理

マスタデータには顧客マスタや商品マスタなど様々なものがあります

種類は様々でも、絶対的なルールが一つあります

これは、マスタデータには重複があってはならないということです

もし、マスタデータのIDが「1,2,3・・・」と採番されていたとしたら、IDの1が2つあってはならないということです

ところが、

マスタデータの変更履歴を残す必要がある場合があります

例えば、以下の画像のケースです

顧客マスタ

上の画像は顧客マスタです

ID:1番の中尾さんのマスタデータが重複しています

何故かというと、IDが1番の中尾さんが名古屋市から東京に引っ越ししているからです

ただし、データ管理上は前の住所も残しておく必要があります

この場合は、日付が最新(最大)のもののみマスタデータとして表示できるように工夫する必要があります

では次から、日付が最新(最大)のもののみをマスタデータとして表示する為のポイントを2つ紹介します

ポイント

グループ化

パワークエリにはグループ化という機能があり、重複を排除してグループ化しつつ合計処理などの操作を行えます

 今回はグループ化機能の操作を「最大」で指定して、IDの重複がある場合には「最大の日付」のものを抽出できるようにします

複数キーによるマージ

エクセル関数のVLOOKUP関数では、検索値は一つのみ指定できます

パワークエリのマージ機能では、実は、複数列を照合列として指定できます

手順

マスタデータからクエリ作成

解説は、前述のマスタデータをテーブル化してエディタを開くところからははじめさせて頂きます

エディタを開いたら、下の画像の日付の列が時刻表示になってしまっています

こちらは、日付形式に直しておいてください

日付形式に直したら、クエリ名を「顧客マスタ_元」としてください

グループ化による最大日付の抽出

まずは「顧客マスタ_元」クエリを「複製」してクエリ名を「顧客マスタ_グループ化」に変更してください

この後、ポイントで前述したように下の画像のような設定でグループ化を行ってください

グループ化(最大)

グループ化を行うと、前述のID:1番の方の日付が最新の日付になっているはずです

クエリのマージ処理

 では、次にクエリのマージを指定しますが、下の画像の「新規としてクエリをマージ」を指定して、新規にクエリを作成できるようにします

マージ対象のクエリは下の画像のように「顧客マスタ_元」を指定します

ポイントの章で前述したように、複数の列を照合列として指定します

 今回のケースの場合は、IDだけでなく「日付」も指定することで、IDに重複がある場合には、日付も一致したマスタデータのみをマージできるようにします

下の画像のように照合列として複数列を指定する場合には、Ctrlキーを押しながら指定します

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

マージする条件を指定した後は、マージされたクエリの中かから「ID列と日付」以外を展開します

マージされた列を展開した後は、列の順番等を整えてください

<まとめ>

 今回は、パワークエリのグループ化機能とマージ機能をうまく組み合わせてマスタデータの履歴管理を行う方法を解説しました

今回の最大のポイントはグループ化機能です

グループ化を行う際に、操作を「最大」にして日付が最大のもののみを抽出できるようにします

今回解説した内容で一点、注意点があります

マージする際に、グループ化した内容は1番目にくるようにしてください

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

元の顧客マスタは、上の画像にて黄色の印をつけた箇所のように、2番目に来るようにしてください

この順番が狂うと、IDが重複したままになってしまいますので注意が必要です


にほんブログ村

M言語に慣れる_1回目~コード構造の把握~

【M言語は難しくない!】M言語への理解を深めて、Power Queryの「まだ触れたことのない便利機能」を有効活用できるようになりましょう!!

https://youtu.be/HMcsg_B-gm0

 パワークエリは直感的なクリック操作で一括処理が行えるのが大きなメリットです。ところが、エクセルシート上では簡単に行えていた「セル単位での操作」や、「別シートの参照」、「関数を組み合わせた処理/ネスト」に相当する処理が行えません

要は、Power Queryエディタ内のメニューにある、行列単位の一括処理しか行えないのです

 Power Queryエディタ内のメニューにある処理以外の事をしようとすると、M言語の領域にまで踏み込むしかありません

ところが、詳細エディタや数式バーを見ると、小難しそうなコードが並んでいます

M言語は一見、取っ付き難いのは確かです

但し、特定のルールや使用パターンさえ押さえてしまえば、実はそんなに難しくないはずです

まずは簡単な例にて、M言語を活用することが必ずしも難しくないことを紹介したいと思います

下の画像は、あるクエリのPower Queryエディタ内の画像です

このエディタ内で<赤印の「販売金額」列の1行目の「54000」>の箇所を抽出してみます

エクセルシート上で言えば、「=セル名(例:A9など)」を数式バーに入れる処理です

ちなみに適用したステップには、まだ1つのステップ「ソース」しかありません

この場合、値の抽出は数式バーに簡単な1行を入れるだけで行えます

下のGIF画像の数式バーに注目してください!

=ステップ名[列名]{行位置}という単純なコードを入力しただけです

 但し、コード内の角括弧:[]と波括弧:{}の使い方のルールが分からないと小難しく感じてしまうただそれだけなのです

逆に、[]{}の意味が分かるだけでも「かなりのことが行えそう!」と感じませんか?

少し種明かしをすると、[]がセル名のA列やB列に相当するものです

{}が「A9セル」や「B2セル」の行番号に相当するものです

そしてコード内のステップ名が、セル名の前にを伴って付くシート名(例:平均値の計算!A2)みたいなものです

ルールやパターンが分かれば、エクセルシート上での処理とそれほど違いはないのです

この回では、M言語に慣れるためのウォーミングアップとして、M言語の特徴を3つに絞り解説します

そして、最後にPower Queryの「まだ触れたことのない便利機能」の事例を1点解説します

今回解説する「まだ触れたことのない便利機能」 の便利さを肌で理解した時には、「M言語理解へのハードル」が確実に少し下がっているはずです

1.コードのカラー

まず、Power Queryエディタ(以降、エディタ)で「列のマージ」をしてみます

マージする内容は、下の画像の「姓」と「名」です

「姓」と「名」の間には、スペースを入れます

すると、数式バーが次のような表示になります

上の画像には、黒を除いた「3色」が含まれています

この3色にはそれぞれ意味があります

①赤➡文字列

列をマージする際に、新しく追加される列の「列名」として指定した文字列「氏名」が赤色になっています

②青➡システム予約語

後の回で、個々の用語の意味などは詳細を解説しますが、「each」や「type」などが青色になっています

これらは「システム予約語」と言われるものです

他に「if」「else」なども該当します

システム予約語は、システム言語として、予め使用することが決まっている用語です

③緑➡ハードコード(自動記録)された値

こちらは、エディタ内でステップを追加した際に、ハードコード(自動記録)されたです

上の画像は、列をマージした時、自動的にデータ形式が「文字列」として自動記録されたことを表しています

2.大文字と小文字の区別

M言語を扱う際に、意識しておかなくてはならないことの一つとして「大文字と小文字」の区別があります

仮に、下の画像の「T」を小文字の「t」に置き換えてみます

すると、下のGIF画像のようにエラーになります

M言語を扱う際には、大文字と小文字の区別は特に意識しましょう!

3.ステップの引継ぎ

前述の1.で列のマージを行ったエディタ内には、下の画像のように2つの適用したステップがあるものとして以降の解説を行います

上の画像の2つ目のステップ「挿入された結合列」の数式バーの表示には、「ソース」という名前があるのが確認できます

では、3つ目のステップとして、既存の列を削除したらどうなるでしょうか?

3つ目のステップの中には、2つ目のステップの名前「挿入された結合列」が含まれています

つまり、各適用したステップは、前のステップ名を通じ、基本的には<一つ前ステップの内容>を自動的に引き継いでいきます

4.参照ステップ作成

前述の3.ではエディタ内で<一つ前のステップの内容>を自動的に引き継ぐというPower Queryの特徴について解説しました

但し、これはあくまでも”基本的には”、”自動的には”という話しであり、前ステップをスキップして参照することもできます

では、この「参照のスキップ」を利用した、新たな数字の集計方法を紹介します

エディタ内の話しに戻り、一つステップを追加します

上の画像の「注文金額」の列から全体平均を算出します

すると下の画像のように、「削除された列」ステップを引き継いだ「計算された平均」というステップが追加されます

この「計算された平均」で算出した値「53609」を、他のステップで参照して活用できるようにします

この、他のステップで参照できるようにしたステップを、以降「参照ステップ」と呼ぶことにします

では、この「計算された平均」ステップを「参照ステップ」として確定するための処理を数式バーで行います

下のGIF画像のように「関数のマーク」を左クリックすると、新たなステップが追加されます

「カスタム1」というステップが新たに追加されましたが、このステップはある種、ダミーのステップです

更にこのステップから、下の図のように「前のステップ」をスキップして「削除された列」ステップを参照します

前のステップをスキップして参照するには、下のGIF画像のように数式バーでステップ名「削除された列」を入力し、Enterを押します

すると、エディタの画面が「削除された列ステップ」の内容になります

エディタ画面からは下の画像の「平均金額」は消えましたが、これで「平均金額」を他の画面で参照して活用できるようになりました

今回は、「平均金額」を参照ステップとして活用したカスタム列を作成してみます

作成する内容は「個々の注文金額-平均」です

注文金額については、カスタム列作成の右側から挿入できます

「平均」については、前述のステップ名「計算された平均」を参照します

つまり、「計算された平均」ステップで算出された「53609」が「注文金額」から差し引かれます

カスタム列を作成した後のエディタ画面は、次の画像のようになります

このカスタム列を作成するまでに、ステップを新たに追加したり、ステップをスキップしたりしたので、図で整理して今回の処理を振り返ります

ⅰ)カスタム1の列をダミーとして追加

この処理で追加されたステップにより、「計算された平均」ステップを残したまま、エディタ画面を「削除された列」ステップの画面に戻せています

つまり、「削除された列」➡「計算された平均」というステップの順序が、「計算された平均」➡「削除された列」という順序に変えることができました

ⅱ)カスタム列の作成

ⅰ)で変えた順序を活かして、個々の注文金額マイナス全体の平均を計算します

<まとめ>

今回は「M言語に慣れる」の1回目として、M言語の特徴を3つに絞って解説しました

1.コードのカラー

2.大文字と小文字の区別

3.ステップの引継ぎ

 上の3つを意識してPower Queryを活用してもらえると、M言語が必ずしも難解なわけではないことが、分かって頂けると思います

そして、3つの特徴の後には3.ステップの引継ぎを応用した「参照ステップ」について解説しました

こちらでは、ステップの順序を入れ替えて「ステップで算出した値」を有効活用できるようにしました

ⅰ)一旦、注文金額の平均値を計算➡参照ステップ

ⅱ)カスタム列・作成画面で「各行の注文金額-平均値/参照ステップ」を計算

この上のⅰ)ⅱ)の処理はエクセルシート上で例えるなら、以下の画像のような処理です

 データが存在するシート/画像左とは別のシート/画像右で平均値を計算しておき、元のデータが存在するシートで注文金額から平均値を引いています

ですので、今回使用した参照ステップの値はエクセルシートでの操作でいえば別シートでの計算です 

 M言語を使用していけば上の事例と同じ様に、エクセルシート上での細かい操作に近いことが一括で行えることが理解できてきたと思います

 では、次回からはM言語を有効活用してPower Queryの魅力を新たに引き出す方法について、もっと具体的に解説していきます

M言語の記事一覧については、ここから見ることができます

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

参考までに解説に使用したエクセルファイルを添付します

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

にほんブログ村

受付終了➡モダンエクセル勉強会_0903

9月3日(金)に「モダンエクセルを実際に使いこなしている方々」にお集まり頂き、「モダンエクセル活用の場を広げる」ための意見交換をしたいと思います!

・主催:Excellent仕事術/当ブログ管理人

・日時:2021年9月3日19時半~20時半(今回は60分です)

・場所:オンライン(ZOOM)

・内容:①主催者発表/約20分、②意見交換及び質疑応答

①主催者発表:パワークエリ/M言語の活用術について

・費用:参加費用は無料です

・申込:以下参照

-記事下のフォームに記入して頂いた後に、ZOOMのURLを送ります

*すぐにURLを送れない事もありますので、その点はご容赦ください

– 先着20名まで

-参加者はモダンエクセルを使いこなしている方に限定させて頂きます

*録画はご遠慮願います、後、途中の入退出もご遠慮ください(やむを得ない事情が発生した場合には主催者にご連絡ください)

*基本は顔出しでお願いします

-頂いた個人情報について、今回開催する勉強会の連絡等の目的には使用せず、厳重に管理致します

参加申し込みの程、お待ちしております

尚、勉強会に関する問い合わせはこちらからお願いします


にほんブログ村

M言語に慣れる_3回目~ダイナミックフィルタリング~

M言語は難しくない!】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう!。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。

例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです

 ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします

 今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません

 あくまで、Power Queryエディタ(以降エディタ)にて、既に数式バーに記録されているM言語を修正するだけです

マクロ作成で言えば、記録マクロ修正するような内容です

記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです!

目次

今回のポイント

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

演習1/受注金額1位の担当者を抽出

演習2/最高値の半分未満の担当者抽出

<まとめ>

今回のポイント

ステップ名変更

実は、エディタのステップ名は自分好みに修正していけます

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります

参照ステップ

 前々回、ステップをスキップして参照することで「参照ステップ」を作成しました

前々回は平均値を参照しましたが、今回は「最高値」を参照します

 クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします

数式バーへの参照ステップ組み入れ

一度、数式バーにてダミー値でフィルタリングを行います

ダミーで設定をした箇所に、前述の参照ステップを組み入れます

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

今回は次のデータを使用します

演習データ

担当者別に受注金額を管理する表です

 こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします

演習の中で、こちらの表にデータを新たに追加したりします

今回のポイントと使用するデータを確認したところで、本格的な演習に入ります!

演習1/受注金額1位の担当者を抽出

ステップ名を変更

まず、表をテーブル化してエディタを開きます

表の上で次の画像のように右クリックし、「テーブルまたは範囲からデータを取得」をクリックします

エディタが開いたら、「今回のポイント」で前述したステップ名の変更を行います

下の画像の「変更された型」を「DATA」に変更しましょう

クエリ名を変更するのと同じ要領でステップ名も変更できます

「変更された型」の上でF2キーを押せば、下のGIF画像のようにステップ名を変更することができます

ステップ名を変更

なお、前ステップを参照する時に「#”前ステップ名”」と自動的に表示される時があります

この点は後述します

参照ステップを作成/全体最高値の算出

次に、受注金額の列を選択して「変換タブ」の「統計」から最大値をクリックします

すると上のGIF画像のように、受注金額の最大値が算出されます

ここでも、ステップ名は変更しましょう

この時点では、適用したステップの欄は次の画像のようになっています

最後のステップの「計算された最大」を「Cal-Max」に変更しておきましょう

さて、

ここから、「Cal-Max」を参照ステップとして確定する処理をします

確定処理のために、下の画像の数式マークをクリックします

すると、「Cal-Max」のステップが1つ繰り上がります

上のGIF画像のように「Cal-Maxステップ」の後に「カスタム列1」というステップができます

追加された「カスタム列1」ステップの数式バーは下の画像のように、最大値を算出してある前ステップを参照しています

なお、数式バーが「Cal-Max」ではなく「#”Cal-Max”」というステップ名になっています

こちらは、前述の「ステップ名の変更」でも少し触れましたが、ステップ名が空欄やーなどの記号で文字列が繋げられている場合、#”ステップ名”になります

この点は、必ず意識しておきましょう

「最高値」の担当者を抽出

この<受注金額が「最高値」の担当者を抽出>では主に、3つのことを行います

①前ステップをスキップ ➡ ②最高値をフィルタリング ➡ ③シートへの読込

上記の3つを通じ、前述の参照ステップを活用してダイナミックにフィルタリングを行えるようにします

①前ステップをスキップ

参照ステップを確定した段階では、エディタ内の表示は「Cal-Max」ステップで算出した「最高値」だけが表示されています

最終ステップの「カスタム1」ステップの数式バーの内容を、1つ前の「Cal-Max」をスキップした、「DATA」ステップの参照に変更します

これで、上のGIF画像のようにエディタの表示が、参照ステップを確定する前の表示に戻りました

②最高値をフィルタリング

では、記事の冒頭でも紹介したように、まずはダミー値で受注金額をフィルタリングします

受注金額をフィルタリングするには、まずは下の画像の▼マークをクリックします

それから、下の画像の黄色の箇所にダミーとして、表示されている金額の内の「どれか1つ」を入力します

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

但し、上の画像の右側にある、緑色で自動記録された「50000」はあくまでダミーです

こちらの「50000」を参照ステップの内容に書き換えます

前述したように、#”ステップ名”にしないとエラーになりますので注意が必要です

これで、フィルタリングがダイナミックになったので、エクセルシートに読込みます

③シートへの読込

エクセルシートに読込む前に、担当者名以外の列は削除しておきます

下のGIFが、エクセルシートに読込んだ時の内容です

下の図の左、元データが降順で並んでいるので、受注金額が最高の担当者が正しく抽出されていることがよく分かると思います

では、元データに新たに「既存の最高値」を超える担当者/TESTを加えてみます

そして、エクセルシートに読込んだクエリを更新します

きちんとダイナミックフィルタリングができていることが、上のGIFで確認できました!

演習2/最高値の半分未満の担当者抽

こちらについては、演習1で作成したクエリを複製し、フィルタリング条件を変更するだけで完成です

クエリ複製

エディタ内で、演習1で作成したクエリの上で右クリックし、複製をクリックします

ちなみに、上の画像にあるように演習1のクエリは「Max-Member」というクエリ名で作成してあります

フィルタリング条件変更

前述のクエリの複製を行ったら、下の画像のようにクエリ名をF2キーで変更しておきましょう

クエリ名を変更したら、クエリを開き次の画像の「フィルターされた行」ステップをクリックします

こちらの数式バーにある、条件式の部分を変更します

演習2でフィルタリングに設定する条件は「最高値の半分未満」ですので、上の画像の赤丸部分を次の画像のように変更します

符号についても、変更を行うのがポイントです

シートに読み込み

フィルタリング条件を変更したところで、読込処理を行います

読込先は演習1の下にします

最高値は担当者/TESTの120,000なので、最高値の半分/60,000未満の担当者が正確に抽出されています

では、最高値を130,000に引き上げてみましょう!

そうすると、受注金額が60,000の担当者も抽出対象になるはずです

クエリを更新すると、下のGIFのように受注金額60,000の担当者があらたに抽出されています

これで、演習2で作成したクエリもダイナミックフィルタリングが行えていることを確認できました

<まとめ>

 今回は、前々回に解説した「参照ステップ」をフィルタリングに活用してダイナミックフィルタリングを行うクエリを2つ作成しました

演習1のクエリでは、「最高値」の担当者を自動で抽出できるようにしました

演習2のクエリでは、「最高値」の半額未満の担当者を自動で抽出できるようにしました

 2つのクエリともに、数式バーに記録されたM言語を編集するだけでフィルタリングをダイナミックなものにしました

 今回の解説を通じて、M言語を学習する効果の大きさを感じて頂けたら幸いです

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

次の回は、カスタム列・作成機能の裏技を使用して、特殊なテンプレートを作成します

https://analytic-vba.com/power-query/m-code/begin-merge-table/

参考までに今回解説したファイルの完成版を添付します

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

にほんブログ村

パワークエリ記事一覧

初級編

タイトル 内容
Power Queryで操作はどう変わる?~初級講座1回目 パワークエリとはなにか?そのメリットをスマホに例えて解説します
シートからデータ取得~ 初級講座2回目~ 別シートのデータをテーブル化して取得する方法を解説します
ファイルからデータ取得 初級講座3回目 別ファイルのデータをテーブル化して取得する方法を解説します
フォルダから複数ファイルデータを一括取得~初級講座4回目 フォルダ内にある複数のファイルからデータをまとめて取得する方法を解説します

中級編

タイトル 内容
複数データの組み合わせ/クエリのマージ~中級編1回目~ クエリのマージ技術の概要について解説します
クエリのマージ/項目追加~中級編2回目~ 複数のクエリをマージする方法について、実際のデータで解説します
クエリのマージ/計算処理~中級編3回目~ マージした項目を活用して計算処理を行う方法を解説します
クエリの追加作成~中級編4回目~ 同じ項目のクエリを追加して結合する方法を解説します
クエリの追加・結合~中級編5回目~ クエリの追加をピボットテーブルと連携して行う方法を解説します
ピボット解除/縦横並びの変換処理~中級編6回目 縦横の使いにくいデータを、使いやすい並びに変える方法を解説します
セル分割他・変換処理~中級編7回目 複数のデータが1つのセルに入っている場合に分割する方法を解説します
セル分割他・変換処理2~中級編8回目 前回の内容を使いやすい形式にして出力する方法を解説します
エディター内のやり直し操作~中級編9回目~ ワークシートにおけるCtrl+Zに相当する処理の方法を解説します
変換した列の追加~中級編10回目~ 連番、条件列(IF関数に相当)及び列削除・移動他を解説します
グループ化1~中級編11回目~ データを階層化して集計する方法を解説します
複数条件でグループ化~中級編12回目~ データを複数の条件で階層化する方法を解説します

上級編

タイトル 内容
行削除とヘッダー行の調整~上級編1回目~ 見出し行/ヘッダーがおかしい場合の対処方法を解説します
セル結合により見出しが2行の表をデータ活用1~上級編2回目~ セル結合された使いにくいデータを使いやすくする方法を解説します
セル結合により見出しが2行の表をデータ活用2~上級編3回目~ セル結合された使いにくいデータを使いやすくする方法を解説します
各種集計/合計から四捨五入まで~上級編4回 四則演算の計算から四捨五入まで、様様な集計方法を解説します
エラー修正_処理ステップエラー~上級編5回目 ファイル保存先変更などに伴うエラーの対処方法について解説します
途中のステップを削除した場合のエラー修正処理~上級編6回目 途中のステップを削除した場合のエラー対処処理について解説します
エラー修正_データ自体のエラー~上級編7回目 データ自体がエラーになっている場合の対処方法について解説します
カスタム関数を自身で登録して使い回す~上級編8回目 ユーザーが独自で作成するカスタム関数の作成方法を解説します
カスタム関数を自身で登録して使い回す2~上級編9回目 作成したカスタム関数を呼び出して活用する方法を解説します
IF式を組み合わせて列作成~上級編10回目 条件付きカスタム列を作成する方法を解説します
「例からの列」にて変換パターン自作~上級11回目~ 入力した変換例を汲み取って変換を行う方法を解説します
「 例からの列」による桁数が規則列への対応~上級12回目~ 「例からの列」による変換方法の応用方法を解説します
エディタを開かずにソース変更処理~上級13回 「パラメーター」機能を使用してデータソースを変更する方法を解説します
シート上からデータソースを変更する方法~上級編14回目 シート上の入力を変更するだけでデータソースを変更する処理を解説します
セルの値を変更するだけで読み込みを変更する方法~上級編15回 セルの変更内容をクエリに組み入れて、クエリの内容を変更できるようにする方法を解説します
クエリのコピー・バックアップ・削除他~上級編16回 クエリのコピーやバックアップ及び削除、その他グループ化などの方法を解説します
文字列の抽出~上級編17回 RIGHT関数やLEFT関数、MID関数に相当する内容に加え、特定の文字列を抽出する裏技を解説します
文字列の追加、置換~上級編18回 前回の特定の文字列の抽出に加え、文字列の追加や置換方法について解説します
エラー発生の予防/列のデータ形式変更と削除について~上級19回~ エディタ内の操作のちょっとした工夫で、エラー発生を予防する方法を解説します

M言語編

タイトル 内容 使用するM関数
M言語に慣れる_1回目~コード構造の把握~ Mコードの基礎的なルール/文字の色、大文字の区別、ステップの参照について解説します List.Average/平均値・計算
M言語に慣れる_2回目~カスタム列~ カスタム列・作成画面でM関数を使用して新たな列を作成する方法を解説します Text.From/文字列への変換
M言語に慣れる_3回目~ダイナミックフィルタリング~ セルの変更内容をフィルタイングの内容に反映する方法を解説します List.Max/最大値の計算
M言語に慣れる_4回目~特殊テンプレート作成~ 2つのクエリを組み合わせて、1担当者に対して複数の勤務日がある表を作成する方法を解説します
M言語に慣れる_5回目~M関数を検索する方法~ ワークシート内の関数のように、M関数を参照する方法を解説します Text.PositionOf/特定の文字列の位置抽出
M言語に慣れる_6回目~3種類の括弧~ 丸括弧と角括弧そして波括弧とテーブル、リスト、レコードの関係を解説します
M言語に慣れる_7回目~空のクエリからテーブル作成~ 空のクエリから、リストやレコード、テーブルを作成する方法解説します #table/テーブル作成、Table.RenameColumns/列名変更
M言語に慣れる_8回目~eachを使いこなしてM関数作成~ 数式バーに良く出てくる「each」の用法について解説します Table.SelectRows/テーブルから特定の行を抽出、List.Select/リストから特定の値を抽出
M言語に慣れる_9回目~複数シートをコード1行で結合~ ファイル内のシートからデータをまとめるクエリを作成する方法を解説します Excel.CurrentWorkbook/ワークブックの内容を一括抽出
M言語に慣れる_10回目~グループ毎に連番作成~ 「グループ化の操作/全ての行」を活用してグループ毎に連番を作成する方法を解説します Table.AddIndexColumn/連番作成
M言語に慣れる 11回目~前行を参照して計算 連番と波括弧:{}を組み合わせて活用して前行を参照する方法を解説します
M言語に慣れる~12回目例外処理 エラーが発生した際の対処処理を行う方法を解説しています
M言語に慣れる13回~ダイナミックに連続した日付作成 ワークシート内にある連続性のない日付から、連続する日付を作成した後、他データも紐づける方法を解説します #date/「年、月、日」から日付を作成、#duration/「日、時間、分、秒」から期間値を作成します、List.Dates/「開始日、リスト個数、増分」から日付のリストを作成、Number.From/値を数値型式にして返します
M言語に慣れる14回目~複数行を1つのセルにまとめる グループ毎のテーブルを作成後、グループ別に1つのセルにテーブルの内容をまとめる方法を解説します
M言語に慣れる15回目~TEXT関数/文字列関数 テキスト関数の事例について解説します Text.Contains/文字列の有無を判定、Text.Replace/文字列の置換
M言語に慣れる16回目~List関数 リスト関数の事例について解説します List.Max/最大値の計算
不規則に散らばっているデータを一括取得~M関数~ 見出し行/ヘッダーがそれぞれ違うシートの内容を一括でもとめる方法を解説します List.PositionOf/リストの中から特定文字列のリスト内位置を抽出、Table.RemoveFirstN/テーブルの先頭から指定行数を削除します、Table.PromoteHeaders/先頭行を新しい列見出しにします
参照先バッファー化によるパワーマンス向上~M関数~ クエリのパフォーマンスを向上させる方法を解説します Table.Buffer/参照テーブルのバッファー化
クエリの列名をダイナミックに変更する~M関数使用~ 入れ子のリストを使用して、ワークシート上の内容でクエリの列名を変更する方法を解説します Table.RenameColumns/列名変更、Table.ToColumns/テーブルからリスト(入れ子)作成
Power Queryエディタ上で複数条件付き合計処理~M関数使用 M関数を組み合わせて「SUMIFS関数」と同様の計算をエディタ上で行う方法を解説します Table.SelectRows/条件に合う行のみを抽出、List.Sum/合計値を抽出
指定した範囲の値を使用してフィルタリング M関数を利用して、ワークシート上の表にある値に一致するもののみをフィルタリングする方法を解説します List.Contains/リストに指定の値が入っているかを判定
複数リストを1対1の関係でうまく組み合わせる 1行に複数の値を持つリストを複数組合わせる場合に、リスト間で1対1の関係になるようにします(例:8月2日/月、火、水➡8月2日/月) Table.FromColumns/複数のリストからテーブルを作成
列名をダイナミックにしたピボット解除を行う 新たな列を追加した場合でも、並べ替える列とそうでない列をコントロールできるようにする Table.ColumnNames/列名リストを作成

分析編

タイトル 内容
POWER QUERYで手軽にABC分析 パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます
RFM分析の「R」を集計する 注文データから注文者(会員)
別に最終注文データを集計できるようにします
RFM分析の「F」を集計する 注文データから注文者(会員)
別に注文頻度を集計できるようにします
RFM分析の「M」を集計してまとめる 注文データから注文者(会員)別に注文金額を集計できるようにします

コラム

タイトル 内容
【M言語実践】グループ別に累計を集計する方法 グループ別に累計値を集計する方法を解説します
【M言語実践】顧客の2回目のリピート率を分析する方法 通常のエクセル処理であれば相当煩雑な分析処理をM言語を活用して簡略化します
M言語実践】グループ別にIDを設定 グループ別に連番を採番した上で桁数を揃えてIDを作成します
マスタデータの履歴管理を行う方法 マスタデータが重複している場合に、日付が最新のもののみを表示する方法を解説します
POWER QUERYのステップ数を減らす3つの工夫 「変更された型」のステップを減らすなどの工夫を解説します
エディタからデータ入力してクエリ作成 エディタ上で直接データを入力する方法を解説します
【豆知識】どこの行が変わったかを調べる方法 クエリのマージの仕方を工夫しつつ、条件列を組み合わせて、元データと修正があった箇所を特定する方法を解説します
【豆知識】ピボット/縦縦並び⇒縦横に並び替え集計 ピボット解除と別な方向で並べ替える方法を解説します
【豆知識】ピポット解除時の空欄の扱い グループ化
【分析作業用】大量データを1目で把握する パワークエリの表示タブでは大量のデータを1目で分かるようにする為の工夫がありますので、そちらを解説します
【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法 グループ化機能を2段階で行うことで、グループ単位の集計と個々の集計とを同時に行う方法を解説します
クエリの複製、参照、そしてコピーとの違い~ クエリを右クリックした時に出てくる「複製」「参照」「コピー」の違いについて解説します
POWER QUERYって何?~エディタ上でのショートカットキー エディタと他ファイルを並行して開く方法も含めて、操作を早く行う方法を解説します
POWER QUERYって何??~時刻を曜日と日付に変換 時刻データを曜日や、日付データに変換する方法を解説します
POWER QUERYって何??~経過時間を一括で計算する ワークシート関数では、直接計算できない経過時間を簡単に計算する方法を解説しています
POWER QUERYって何??~日付を月や四半期単位に変換~ 日付データを簡単に月や四半期単位に変換する方法を解説します
POWER QUERYって何??~読込んだデータに行番号追加~ シートに読込んだクエリに行番号を追加する方法を解説します
文字列を記号毎に分割し、行方向に展開する方法 コンマなどの記号をもとにして文字列を分割しつつ、列方向ではなく、行方向に展開する方法を解説します
POWER QUERYって何~既存クエリのステップを一括で削除する方法 適用してステップの欄で一気に複数ステップを整理する方法を解説します
POWER QUERYって何?12~重複のないリストを一瞬で作成 一気に重複を排除したリストを作成する方法を解説します
POWER QUERYって何??11~ユニークな数の集計~ パワークエリならではの集計方法を1点だけ解説します
POWER QUERYって何?~ピボット解除(縦横並び替え)と入れ替えの違い~ 紛らわしい「ピボット解除」と「行列の入れ替え」の違いについて
POWER QUERYって何??9~WEBページからデータ取得~ Webページからエクセルデータを直接取得する方法を解説します
POWER QUERYって何8???~PDFファイルをエクセルに変換~ PDFファイルからエクセルデータを直接取得する方法を解説します
POWER QUERYって何?~列の結合/複合キー活用~ 複数の列を結合して複合キーを作成・活用する方法を解説します
文字列から空白を取り除く_POWER QUERY 文字列の前後や、文字列間に入り込んでいる空白を取り除く方法を解説します
パワークエリエディターで行コピーを行う方法 M関数を使用して行をコピーして複数にする方法を解説します
MICROSOFT POWER QUERY~ACCESSとEXCEL連携~ 特殊な方法でAccessと連携する方法を解説します
POWER QUERYって???合計を計算する グループ化による合計金額の計算方法を解説します
POWER QUERYって何???連番作成 連番を作成する方法を解説します
POWER QUERYって何??パワークエリの使用メリット パワークエリを使うメリットの一つは履歴の管理が便利な点があげられます
POWER QUERYって何???~ACCESSとエクセルの連携~ 通常の方法にてAccessと連携する方法を解説します
ピボット解除を複数列を軸にして実行する 複数列を軸にして縦縦並びを縦横並びに変える方法を解説します

パワークエリエディターで行コピーを行う方法

今回は、通常は行列単位で一括処理を行うパワークエリで「行コピー」を行う方法を解説します

行コピーを行うのに「M言語」が出てきますが、M言語の概念に触れるのにいい題材だと思います

ところで、

空のクエリ波括弧:{}を使用したらどのようになるでしょう?

ちなみに、空のクエリはここから起動できます

*データタブ➡データの取得➡その他のデータソースから➡空のクエリ

では、

試しに、下の画像のように空のクエリの数式タブに{2020,2021}と入力してみます

すると、上の画像のように2020と2021の値から構成されるリストが作成されます

このリストを作成する仕組みを行コピーにも活用します

行コピー

では、実際に行コピーをしてみたいと思います

上のデータがエディタに表示されている状態からカスタム列・作成画面をクリックします

そして、先ほどの波括弧:{2020,2021}を入力します

すると、2020と2021の2つの値から構成されるリストが含まれた列が新たに作成されます

次に、下の画像にて黄色く印を付けた箇所をクリックしてリストを展開します

すると、2020と2021の値に対応するように行コピーが作成されます

もし、波括弧の中味を「2020と2021」でなく「2020,2021,2022」とすれば1行でなく2行がコピーされます

<まとめ>

今回は波括弧:{}を使用してリストを作成した上で、行コピーを行いました

とても簡単な内容ですが、パワークエリをサポートするM言語の仕組みに触れる上ではとてもいい内容です

特に、波括弧の使用の仕方に慣れるだけでもM言語の根幹に触れることができます!

ぜひ実際に手を動かして試してみてください


にほんブログ村

テーブル列操作関数

列結合

Table.NestedJoin

文法

キー列を基にして2つのテーブルの内容を結合します

=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)

*結合タイプが省略された場合には、左外部結合が指定される

使用方法

列名をリストとして返す

Table.ColumnNames

文法

ハードコード/自動記録された列名リストをTable.ColumnNamesで置き換えると、列が増えても列名のリストが網羅される

使用前
使用後

実際の使用例

 ➡【豆知識】ピポット解除時の空欄の扱い

リストからテーブル作成

Table.FromColumns

文法

リストを組み合わせてテーブルを作成します

例:Table.FromColumns({[勤務日],[曜日]})

使用例

列変換(研究中)

Table.TransformColumns

文法

元データ
Table.TransformColumns
列変換後


にほんブログ村