パワークエリエディタ上でSUMIFS関数～M関数使用

　今回は、M関数を使用して「SUMIFS関数」と同じ「複数条件付き合計処理」をPower Queryエディタ（以降エディタ）上で行う方法を解説したいと思います

　M言語を学ぶ目的の一つは、Power Queryのメリットである「行列一括で処理を行う」とワークシートでの「細かい操作・計算」を両立させることです

SUMIFS関数は細かい計算を行う関数なので、M言語の良さを学ぶという点で凄くいいテーマです！

　特に記事の中で出てくる「別クエリのテーブル化/入れ子のテーブル作成」と「Table.Select関数による特定の行抽出」はM言語の醍醐味の一つといっていいと思います！

記事の最期にはサンプルファイルも付いていますので、ぜひ、後で実際に手を動かしてみて下さい！

今回のポイント

別クエリのテーブル化/入れ子のテーブル作成

下の画像はテーブル「Condition」のクエリです

　一番右の列にある列/詳細はエディタ上でカスタム列・作成画面を使用して追加しました

Conditionのテーブルの中に、別なテーブルの内容が入れ子で作成されています

　画面下にあるように、詳細列の各行には「AllData」テーブルの内容がそれぞれ紐づけられています

　この各行に紐づけたテーブルから①条件に合う行のみを抽出し、②合計値を出します

２つのM関数の使用

今回の記事では２つM関数を使用します

①条件に合う行のみを抽出

➡Table.SelectRows

書き方：Table.SelectRows(テーブル名, 条件)

②合計値を抽出

➡List.Sum

書き方：List.Sum(リスト名)

カスタム関数の使用

　上記のM関数/Table.SelectRowsの第二引数/条件においては、カスタム関数を使用します

カスタム関数を作成する要領としては、下の図のような要領です

(a,X,b) =>ax + b

まず、()内において式に使用するもの/a,X,bを宣言します

その後、実際に宣言した内容を使用した数式を示します

では、今回行いたい事とポイントを確認したところで本格的な解説を始めたいと思います

カスタム列・作成画面から入れ子テーブル作成

解説は、前述の「AllData」テーブルと「Condition」テーブルをエディタ上に読込んだ状態から始めたいと思います

　まずは「Condition」クエリを開き、列の追加タブから「カスタム列・作成画面」を開きます

カスタム列・作成画面を開いたら、次のように指定します

数式には、もう一つのクエリ名/AllDataを指定します

　上のGIF画像のように、もう一つのクエリ名を指定してOKボタンを押すと、新たな列が追加されます

　新たに追加された列にて、「Table」というテキストの横をクリックすると、画面下に「テーブルの中味」が表示されています

テーブルから条件に合う行を抽出

　上記の状態だと、Conditionの各行には全く同じ内容のテーブルが作成されています

ここから各行のⅰ）年度、ⅱ）製品別の条件にあう行のみを抽出します

条件に合う行を抽出するには、M関数/Table.SelectRowsを使用します

　そして、今回のポイントで記述したように、条件を指定する第二引数にはカスタム関数を使用します

今回は私の好みになってしまいますが、()の中に「myRow」を指定します

上の図がM関数/Table.SelectRowsを、カスタム関数も使用した上で作成した画面です

カスタム関数の部分だけを抜き出すと次のようになります

(myRow)=>myRow[年度]=[年度] and myRow[製品別]=[製品別]

myRowを「AllData」クエリの代用だと割り切って理解すれば、理解は簡単になると思います

　要はカスタム関数内にて、年度と製品別の２つの条件が一致するものを指定しています

　では、上記の指定をした後にカスタム列・作成画面のOKボタンを押すとどうなるでしょうか？

実は、一目見ただけでは違いは分かりません

　下のGIF画像のように各行のTable/テーブルの中味を見ると、違いは一目瞭然です

各行のテーブルを合計処理

合計処理は2段階で行います

　まずは、前述の条件に合うように抽出したテーブルを「売上金額・列」でリスト化します

その後、リスト化した内容をList.Sum関数で合計処理します

では、まずは最初に「売上金額・列」でリスト化します

リスト化はカスタム列・作成画面で次のように指定します

　リスト化を行うと、エディタ上の各行は次のGIF画像のような表示になっています

　では、リスト化は行ったので、リスト化した内容をM関数/List.Sum関数で合計処理します

　上の画像のように、List.Sum関数で前述のリストを()で閉じれば、各行の合計が処理されます

　記事冒頭のGIF画像は、上の画像のエディタの内容から次の２つの処理をおこなってエクセルシートに読込んだものです

・「売上金額の列」➡削除

・「詳細の列名」➡SUMIFSへ列名変更

＜まとめ＞

今回は、「SUMIFS関数」のように複数条件付きの合計処理を行いました

　M関数は２つ使用しましたが、最大のポイントはM関数を使用する前に、カスタム列・作成画面で別クエリの内容を「テーブル」として紐づけた点です

　この点が従来のエクセル操作とは大きく違う点であり、M言語の醍醐味とも言える部分だと思います

今回はM言語の中味を理解するのに、いい内容だったと思います

ぜひ、手を動かして試してみてください

参考までに今回のサンプルファイルも添付しておきます

SUMIFS関数と同じクエリ

1 ファイル 23.17 KB

ダウンロード

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

にほんブログ村

*****Sponsered Link***** ************************

Excellent仕事術

Power Queryエディタ上で複数条件付き合計処理～M関数使用

目次