タグ別アーカイブ: 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を宣言します

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

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

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

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

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

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

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

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

 「カスタム列」のメニューの中でIF文を効果的に使えば、一見、複雑に思える「この場合はこう処理して、この場合はこういう風に処理する」といった処理も一定のルールを覚えれば簡単に行えます!

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

売上金額

この記事を最後まで読み終えた時には、一段上のレベルでPower Queryを深く使いこなせるようになっているはずです!

*IF式内にAND条件やOR条件を設定する場合はこちらの記事を参照してください!

アイコン

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

...
 

*記事の最後に完成版のサンプルファイルを添付しています

 今回の記事では「IF文」を活用しながら、上の図の表「売上金額」を元にして来期の売上予測を2つのパターンで作成し、新たな列を2列追加します

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

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

ⅱ)それ以外 ➡ 5%増加

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

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

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

ⅲ)上記2つ以外 ➡ 5%増加

1つ目は1つのIF文にて、2つの計算式を組み合わせます

2つ目は2つのIF文を組み合わせることで、3つの計算式を組み合わせます

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

さて、

今回作成するIf文を使った計算式を説明しました

次に今回のポイントを解説させて頂きます

ポイント

構文

今回のポイントは何と言っても、IF文を使った構文を的確に記述できるかどうかです

If文を1つ使う場合の構文は次の通りとなります

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

次が2つのIF文を組み合わせた時の構文となります

if 条件式1 then 条件式1に合致する場合 else if 条件式2 then 条件式2に合致する場合 else いずれの条件式にも合致しない場合】

カスタム列

今回のIf文は「列の追加タブ」のカスタム列の画面で作成します

こちらの画面では「新しい列名」「カスタム列の式」を指定します

カスタム列の式には「<<挿入」ボタンにより列を挿入できます

こちらの詳細は以降の記事内で詳細に解説させて頂きます

大文字と小文字の区別

 こちらはM言語のシリーズで本格的に解説する内容なのですが、Power Queryの言語であるM言語では大文字と小文字を厳格に区別します

上記の構文の解説にあるように、「if」「then」「elseif」「else」はいずれも小文字で記入する必要があります

それでは、今回のポイントの3点を解説させて頂いたので、本格的な解説に入りたいと思います

目次

1つのIf文作成:消極的パターン

2つのIf文作成:積極的パターン

<まとめ>

1つのIf文作成:消極的パターン

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

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

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

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

③②を行う上での注意点

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

If文を記入したら、画面右下のOKボタンを押せばPower Queryエディタ画面に反映されます

上のGIF画面で消極パターンのIf文を書いた画面は、次の通りとなります

上の画像の内容をテキストにしたのが次の数式です

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

③②を行う上での注意点

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

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

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

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

IF、THEN、ELSE ➡ if、then、else

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

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

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

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

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

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

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

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

2つのIf文作成:積極的パターン

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

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

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

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

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


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