数式を自由に入力できる「カスタム列」の使い方

前回の連載では、「標準」コマンドを使って計算を行う方法を紹介した。しかし、実際に計算するときは「カスタム列」を使用するケースの方が多いと思われる。「カスタム列」はM言語を自由に記述できるコマンドで、ここに数式を入力して計算を行うことも可能となっている。今回は「カスタム列」を使って数値計算を行う方法を紹介していこう。

• 

  数式を自由に入力できる「カスタム列」

「カスタム列」を使った数式の入力

以下の図は、ある店舗の売上伝票を記録したデータ表だ。この店舗では「割引クーポン」を配布しており、500円単位の割引を受けられるようになっている。

• 

  「Power Query エディター」に取得したデータ表

この場合、割引後の金額は「税抜金額」から「割引クーポン」の金額を引き算した値になる。この計算を「カスタム列」で実行してみよう。「列の追加」タブにある「カスタム列」をクリックする。

• 

  「カスタム列」コマンド

このような設定画面が表示されるので、まずは“計算結果”を表示する列の「列名」を入力する。今回の例では「割引後の税抜金額」という名前を指定した。

• 

  「新しい列名」の入力

次は、数式を「列名」と「演算子」で入力していく。設定画面の右側に一覧表示されている「列名」をダブルクリックすると、列名を[ ]で囲んだ記述がカーソル位置に自動入力される。このようにパワークエリ（M言語）では、列名を“半角の大カッコ”で囲んで記述する決まりになっている。

• 

  数式に[列名]を入力する操作（1）

続けて、計算方法を指定する「演算子」を入力する。こちらはExcelと同様に、+（足し算）、-（引き算）、*（掛け算）、/（割り算）といった記号を半角で入力すればよい。ただし、^（べき乗）の記号は使えないので注意すること。今回の例では（引き算）を行うので、半角の「-」を入力する。

• 

  計算用の演算子の入力

さらに、数式の続きを入力していく。このとき、キーボードを使って[列名]を入力することも可能だ。半角で「[」と入力すると、列名の一覧がポップアップ表示される。この中から列名を選択して[列名]を入力してもよい。

• 

  数式に[列名]を入力する操作（2）

これで数式の入力は完了。「OK」ボタンをクリックすると、データ表の右端に“新しい列”が追加され、そこに計算結果が表示される。

• 

  入力した数式の実行

• 

  計算結果として追加された列

これで「割引後の税抜金額」を算出できた。この値に1.1を乗算（掛け算）すると、10％の消費税を加えた「税込金額」を求めることができる。

なお、最終的に求めたい数値が「税込金額」だけであった場合は、上記の計算を含めた形で数式を一括入力しても構わない。この場合は、「= ([税抜金額]-[割引クーポン])*1.1」と数式を入力すればよい。このように、計算の優先順位をカッコで指定した数式にも対応している。

• 

  カッコを使った数式の入力

• 

  計算結果として追加された列

Excelに慣れている方なら、前回の連載で紹介した「標準」コマンドよりも、「カスタム列」に数式を入力する方が理解しやすく、また応用が効く、と感じるのではないだろうか。各列を[列名]と記述することさえ覚えておけば、問題なく数式を入力できるはずだ。

数式の入出力に関する注意点

パワークエリで計算を行うときは、“入出力されるデータ”についても学んでおく必要がある。状況を把握しやすいように、[税抜金額]-[割引クーポン]の数式で「割引後の税抜金額」を求めたデータ表を使って解説していこう。

このデータ表をExcelに出力すると、以下の図のようになる。ここで覚えておくべきポイントは、「割引後の税抜金額」の列には“計算結果”だけが出力されている、ということだ。[税抜金額]-[割引クーポン]の数式が出力される訳ではない。

• 

  Excelに出力したデータ表

このため、出力後のデータ表で数値を変更しても「割引後の税抜金額」は再計算されない。たとえば、C3セルの値を「0」→「1,000」に変更しても、D3セルの以前と同じ「14,299」のままである。よって、間違いのあるデータ表になってしまう。

• 

  出力後のデータを変更した場合

「Power Query エディター」で指定した式は、それぞれの「セル」ではなく、処理工程を示す「ステップ」に記録される仕組みになっている。このため、正しく処理するには“取得元のデータ表”で数値を変更する必要がある。

• 

  取得元のデータを変更した場合

その後、「すべて更新」をクリックすると、

（1）データ表の取得
（2）データ表の加工（計算処理）
（3）データ表の出力

といった処理が再実行され、正しい計算結果を得ることができる。

• 

  クエリの更新

このように、「Power Query エディター」から出力したデータ表には「数式が含まれていない」ということを覚えておく必要がある。

これは、データ表を「Power Query エディター」に取得するときも同様だ。以下のデータ表を例に見ていこう。このデータ表の「合計」の列には関数SUMが入力されている。

• 

  取得元のデータ表

このデータ表を「Power Query エディター」に取得すると、関数SUMではなく、その計算結果だけが“数値データ”として取得される。

• 

  「Power Query エディター」に取得されたデータ

これまでにも何回か説明していると思うが、パワークエリは「数式」や「関数」を引き継ぐのではなく、その「結果」だけを入出力する仕組みになっている。Excelに慣れている方ほど勘違いしやすい部分なので、間違えないように注意しておこう。