タグ別アーカイブ: EXCEL

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

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

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

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

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

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

目次

今回使うデータと行いたい事

今回のポイント

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

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

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

<まとめ>

今回は下の図のように、2つのテーブル/A列からとF列からを使用します

画面左は元データとなっており、年度は2016年と2017年の2年分が含まれています

テーブル名は「AllData」で設定してあります

 画面右はH列に「SUMIFS関数」が入力されており、F列とG列にSUMIFS関数の条件が設定されています

例えば、H2セルには297,628と表示されています

 H2セルは画面左の「AllData」テーブルから「2017年度」「軽自動車」の2つの条件を満たす行を抽出し、売上金額の合計値を集計しています

こちらのテーブル名は「Condition」で設定されています

 今回は、上のSUMIFS関数と同じ計算をエディタ上で行い、エクセルシート上に読込めるようにします

 上のGIF画像では、「Condition」テーブルの下に今回作成するクエリの内容を読み込んであります

 「Condition」テーブルの2行目の年度を変え、更新を押したら「Condition」テーブルの下に読込んであるクエリの内容も変わっています

今回のポイント

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

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

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

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

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

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

2つのM関数の使用

今回の記事では2つ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」というテキストの横をクリックすると、画面下に「テーブルの中味」が表示されています

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

IF式を組み合わせて列作成~上級編10回目

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 Power Queryは関数やVBAの知識がなくても、直感的にクリックしていくことで処理を進めていけるのが魅力です

 ただ、どうしてもIF文をネスト(組み合わせ)し、複数の条件式を組み合わせた新たな列を挿入する必要がでる時もあります

 そんな時のためにPower Queryには「列の追加」タブに「カスタム列」というメニューがきちんとあります

 今回は、下の図の売上金額の集計表をもとにして、IF式を組み合わせて新たな列を作成する方法を解説します

売上金額
アイコン

上級10回目_演習 16.89 KB 41 downloads

...
 

上の図の表「売上金額」を元にして、来期の売上予測を2つのパターンで作成し、新たな列を2列追加します

来期の売上予測のパターンの1つ目は消極的な予測です

・地域がアメリカ ➡ 10%増加

・それ以外 ➡ 5%増加

2つ目は積極的な予測です

・地域がアメリカ ➡ 15%増加

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加

・上記2つ以外 ➡ 5%増加

1つ目は1つのIF文により、2つの計算式を作成します

2つ目は2つのIF文により、3つの計算式を作成します

 しかも、2つ目のパターンの3番目の計算式は「且つ」なので「AND」を組み合わせます

作成する計算式を説明したところで、本格的な解説に入ります

消極的パターン

解説は、Power Queryエディターを開いたところから始めます

主な解説内容は、次の①~③があります

①はIF文を作成するメニューの場所

②IF文を実際に作成する画面の詳細

③②を行う上での注意点

では、まずは①から解説します

① IF文を作成するメニューの場所

まず「列の追加」タブの「カスタム列」をクリックします

すると次のような画面が開きます

②IFを実際に作成する画面の詳細

まず、この「カスタム列」画面の解説を3か所に分けて行います

・新しい列名➡IF文を使用して新たに作成する列の名称を記入

・カスタム列の式➡「=」から右にIF文を記入

・<<挿入➡上の「使用できる列」で指定された「列」を式の中に挿入

3番目の「<<挿入」の使い方については、以下、詳細に解説します

まずは上の「使用できる列」の中から該当する「列」をカーソルで選択します

次に画面右下の挿入ボタンを押すと、下のGIF画像のように前述の「カスタム列の式」の欄に選択された列が挿入されます

この「カスタム列画面」の3か所について解説したところで、実際にIF文を記入していきます

IF文の構文は以下の通りとなります

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

内容はVBAでIF文を書く場合と、ほぼ一緒です

最後に「End IF」を付けないところだけが違います

実際の消極パターンを書いた画面は、次の通りとなります

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

上の構文に、消極的パターンを当てはめて数式が書いてあります

if [地域]=”アメリカ” then [売上金額]*1.1 else [売上金額]*1.05】

③②を行う上での注意点

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

上の画像の下に「トークン Eof・・・」なるエラーが出ています

これは、式の構文の中に大文字が入っているからです

式の構文は、下のテキストのように、全て小文字で記入するようにしてください

IF、THEN、ELSE ➡ if、then、else

では、正しく数式を記入したところで下のOKボタンを押します

そうすると、Power Queryエディター画面に新たな列が挿入されます

では、念の為に新たに挿入された列が正しいかどうかをPower Queryエディター上で確認します

まず「売上金額」と「予測式_消極」の列をカーソルで入れ替えます

次に上の両列をカーソルで選択したまま「列のタブ」の「標準」から除算をクリックします

すると、次の様に「(左側)予測式_消去」÷「(右側)売上金額」の結果が出力されます

「標準」機能の計算は、列の位置が左にあるものから自動的に計算されてしまうので、列の位置を入れ替えることを事前に行いました

さて、出力結果は次の通り、正確に出力されていたので「積極パターン」の解説に移ります

積極パターン

まず、積極パターンの内容を振り返りましょう

・地域がアメリカ ➡ 15%増加(A

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加(B)

・上記2つ以外 ➡ 5%増加(C)

では上記のA~Cを、「カスタム列を作成する画面」に当てはめると次のような式になります

まず、前述のA( 地域がアメリカ ➡ 15%増加 )はの式のように、1.の消極パターンと同様の書き方です

【if [地域]=”アメリカ” then [売上金額]*1.15】

次にBの箇所を書き始めるところが1.の消極パターンと違います

「else」の後にそのまま2つ目のIF文を「AND」を交えて、の式のように書きます

【else if [地域]=”ヨーロッパ” and [製品別]=”トラック” then [売上金額]* 1.1】

そして最後にCを「else」の後に書きます

else [売上金額]*1.05

上の3つの数式をつなげると次のようになります

if [地域]=”アメリカ” then [売上金額]*1.15 else if [地域]=”ヨーロッパ” and [製品別]=”トラック” then [売上金額]* 1.1 else [売上金額]*1.05

「カスタム列を設定する画面」の下のOKボタンを押すと、次の様に出力されます

1.の消極パターンでも行ったように、検算を行うと次のようになりました

 Bの 条件【製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加】もしっかり反映されています

 上のPower Queryエディタの内容をエクセルシートに読込処理を行うと、次の画像のようになります

<まとめ>

今回は、IF文を使用した列を追加する方法を解説しました

IF文の構文の書き方はVBAとほぼ同じになっており、次の通りとなっています

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

 IF文を更に組み合わせる場合には、上記の「else」の後にまたIF文を追加すればOKです

 尚、構文を間違えていたり、前述した大文字で記入を行ったりすると 「トークン Eof・・・」なるエラーが出てしまいますので注意が必要です

 ちなみに、正確に数式を記入できている場合には、以下のメッセージが画面下にでます!

今回の解説は以上です

この記事の内容をマスターしたら、M言語を使用した「カスタム列」にもぜひチャレンジしてみてください!

https://analytic-vba.com/power-query/m-code/mcode-begin-custom/

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

参考までに、今回の解説で使用したデータと完成したデータを添付しておきます

アイコン

上級10回目 24.44 KB 9 downloads

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


にほんブログ村