【Excel】Power Queryのカスタム列で独自の計算式を追加する方法

Power Queryカスタム列の概要とできること

Power Queryのカスタム列機能は、既存の列の値を基に、独自の計算式を用いて新しい列を作成する機能です。これにより、データの前処理や分析に必要な中間的な指標を効率的に生成できます。

例えば、売上金額と数量から単価を計算したり、日付列から曜日を抽出したり、複数の条件を組み合わせてステータスを判定したりすることが可能です。これらの処理は、Power Queryエディター上で行われ、元のデータソースに影響を与えることなく、クエリのステップとして記録されます。

カスタム列を作成する際には、Power Query独自の「M言語」と呼ばれる関数群を使用します。M言語には、数値計算、文字列操作、日付操作、論理演算など、多岐にわたる関数が用意されています。また、Excelでお馴染みの関数と似たような働きをする関数も多く、比較的容易に習得できます。

この機能を利用することで、複雑なデータ加工もPower Query内で完結させることができ、Excelのワークシート上で数式を大量に入力・管理する手間を大幅に削減できます。最終的にExcelシートに読み込まれるデータは、すでに加工済みの状態になっているため、その後の分析作業がスムーズに進みます。

カスタム列を作成する前提として、Power Queryエディターが開いている状態である必要があります。通常は、Excelのリボンメニューから「データ」タブを選択し、「データの取得と変換」グループにある「テーブルまたは範囲から」などをクリックしてPower Queryエディターを起動します。

カスタム列の作成と独自の計算式追加手順

1. Power Queryエディターを開く
   Excelで操作したいデータが含まれるテーブルを選択し、「データ」タブの「テーブルまたは範囲から」をクリックしてPower Queryエディターを起動します。
2. 「カスタム列」の選択
   Power Queryエディターのリボンメニューで、「列の追加」タブをクリックします。「列の追加」タブの中にある「カスタム列」ボタンをクリックします。
3. カスタム列の追加ダイアログボックス
   「カスタム列の追加」というダイアログボックスが表示されます。ここで、新しい列の名前と計算式を指定します。
4. 新しい列名の入力
   ダイアログボックスの左上にある「新しい列名」欄に、作成したい新しい列の名前を入力します。例えば、「合計金額」や「ステータス」など、内容がわかる名前を付けましょう。
5. カスタム数式の入力
   ダイアログボックス中央にある「カスタム数式」欄に、計算式を入力します。左側の「利用可能な列」リストから列名を選択してダブルクリックすると、数式に挿入されます。
6. M言語による計算式の記述例
   ここで、具体的な計算式の例をいくつか紹介します。

   例1:2つの列の合計を計算する
   「単価」列と「数量」列がある場合、それらを掛けて合計金額を計算するには、以下のように記述します。

   [単価] * [数量]

   例2:IF関数を使った条件分岐
   「合計金額」が10000以上なら「高」、それ以外なら「低」というステータスを付けたい場合。

   if [合計金額] >= 10000 then "高" else "低"

   例3:日付から曜日を抽出する
   「注文日」列から曜日を抽出する場合。

   Date.DayOfWeekName([注文日])

   例4:文字列の連結
   「姓」列と「名」列をスペースで区切って連結する場合。

   [姓] & " " & [名]

   これらの例は基本的なものですが、M言語にはさらに多くの関数が用意されています。左側の「利用可能な関数」リストを参照しながら、目的に合った関数を組み合わせて使用してください。

7. 構文チェックとOK
   数式を入力したら、ダイアログボックスの下部に「構文エラーはありません」と表示されていることを確認します。エラーが表示されている場合は、数式を見直して修正してください。構文エラーがないことを確認したら、「OK」ボタンをクリックします。
8. 新しい列の追加確認
   指定した計算式に基づいて、新しい列がデータテーブルに追加されていることを確認します。
9. クエリの読み込み
   カスタム列の追加が完了したら、Power Queryエディターのリボンメニュー「ホーム」タブにある「閉じて読み込む」をクリックして、加工済みのデータをExcelシートに読み込みます。

カスタム列作成時の注意点とよくある失敗

データ型が原因で計算できない

カスタム列で計算を実行しようとした際に、エラーが発生する原因の一つに、列のデータ型が不適切であることが挙げられます。

例えば、数値として計算したい列が、実際にはテキスト型(文字列)として認識されている場合、数値演算を行うとエラーになります。Power Queryエディターでは、各列の左端にあるアイコンでデータ型を確認できます。

対処法:

1. データ型の確認
   カスタム列を作成する前に、計算に使用する列のデータ型を確認します。リボンメニューの「変換」タブにある「データ型」グループで、適切な型(例:「123」なら「10進数」、「ABC」なら「テキスト」、「yyyy-mm-dd」なら「日付」)に変更します。
2. 変換ステップの追加
   データ型を変更すると、そのステップが「適用したステップ」に記録されます。このデータ型変換ステップを、カスタム列の計算ステップより前に追加することで、正しく計算できるようになります。

関数名のスペルミスや引数の誤り

M言語の関数名は、大文字・小文字を区別するものがあります。関数名のスペルミスや、関数に必要な引数(カッコの中に入れる値)の数が間違っていると、エラーが発生します。

例えば、日付の曜日名を取得する関数は `Date.DayOfWeekName()` ですが、`Date.DayOfWeek()` と間違えるとエラーになります。また、`Text.Combine()` 関数で連結したい列を指定する際に、リスト形式で指定する必要があるのに、単純な列名をカンマ区切りで指定してしまうといった間違いもあります。

対処法:

1. 関数名の正確な入力
   「利用可能な関数」リストから関数を選択して挿入するのが最も確実です。手入力する場合は、スペルミスがないか、大文字・小文字が正しいか十分に確認してください。
2. 引数の確認
   関数のヘルプや、Power Queryエディターの「利用可能な関数」リストで、各関数の引数(必要な情報)とその順番を確認してください。
3. 構文チェックの活用
   数式入力欄の下に表示される「構文エラーはありません」のメッセージを常に確認し、エラーが出たらその部分を修正します。

IF関数などの論理演算で条件が正しく評価されない

IF関数などで複数の条件を設定した場合、意図した結果にならないことがあります。これは、条件の評価順序や、比較演算子の誤り、または文字列の比較で大文字・小文字を区別してしまうなどが原因で起こります。

例えば、「合計金額」が10000以上なら「A」、5000以上なら「B」、それ以外なら「C」としたい場合、IF関数のネスト(入れ子)の順番を間違えると、5000以上でも「A」と判定されてしまうことがあります。また、文字列比較で「東京」と「とうきょう」を区別してしまう場合もあります。

対処法:

1. 条件の評価順序の確認
   IF関数をネストする場合は、最も厳しい条件(例:上限値)から先に評価し、徐々に条件を緩めていく(例:下限値)ように記述すると、意図した結果になりやすいです。
2. 比較演算子の確認
   「=」(等しい)、「<>」(等しくない)、「>」(より大きい)、「<」(より小さい)、「>=」(以上)、「<=」(以下)などの比較演算子が正しいか確認します。
3. 文字列比較の注意
   大文字・小文字を区別せずに比較したい場合は、`Text.Lower()` や `Text.Upper()` 関数を使って、比較する前に両方の文字列を小文字または大文字に変換してから比較すると良いでしょう。
4. CASE文の利用
   複数の条件分岐が複雑になる場合は、CASE文に似た `if … then … else if … then … else …` の形式をネストして使用するか、より高度な関数(例:`Table.AddColumn` の一部としてラムダ式を使うなど)を検討します。

エラーが発生した場合の処理を定義していない

計算式によっては、特定の条件下でエラーが発生する可能性があります。例えば、ゼロ除算エラー(0で割ろうとした場合)や、存在しない値への参照などです。

これらのエラーが発生すると、カスタム列全体が正しく生成されず、データが破損する原因となります。エラーが発生した場合に、特定の値を表示したり、その行をスキップしたりする処理を定義しておくと、データの整合性を保てます。

対処法:

1. try…otherwise構文の使用
   M言語には、エラー処理を行うための `try…otherwise` 構文があります。これにより、エラーが発生した場合の代替処理を定義できます。例えば、ゼロ除算エラーを回避して、エラー時には「0」を表示するには、以下のように記述します。

   try [売上] / [数量] otherwise 0

   この構文を使うことで、エラーが発生する可能性のある計算も安全に行えます。

2. IF関数での事前チェック
   ゼロ除算のような特定の条件でエラーが発生する場合、IF関数を使って事前にその条件をチェックすることも有効です。

   if [数量] = 0 then 0 else [売上] / [数量]

   ただし、予期せぬエラー(例:データ型の不一致)には `try…otherwise` の方が汎用的に対応できます。

Power Queryカスタム列とExcel数式の比較

Power Queryのカスタム列機能と、Excelワークシート上で直接入力する数式機能は、どちらも計算を行うためのものですが、いくつかの重要な違いがあります。

Excel数式は、セル参照に基づいてリアルタイムに計算結果を更新しますが、大量のデータや複雑な計算を行うと、ブックのパフォーマンスが低下する原因となります。また、数式が複雑になると、管理やデバッグが困難になることもあります。

一方、Power Queryのカスタム列は、データ変換プロセスの一部として機能します。一度クエリを設定すれば、データソースが更新された際に、Power Queryが自動的に変換処理を実行し、加工済みのデータをExcelシートに読み込みます。これにより、手動での数式再入力やデータ更新の手間が省け、データの一貫性と正確性を保ちやすくなります。

項目	Power Queryカスタム列	Excelワークシート数式
主な用途	データソースからの取り込み・加工、ETL処理	表計算、リアルタイムなデータ集計・分析
処理方法	M言語によるクエリステップとして定義	セル参照による数式入力
データ更新	クエリの再実行で自動更新	元データ変更時に自動(または手動)で再計算
パフォーマンス	大量データ・複雑処理に強い(クエリ実行時)	データ量・複雑さによりパフォーマンス低下の可能性
管理・デバッグ	クエリステップとして履歴が残る	数式が複雑化すると管理・デバッグが困難
データソース	多様なデータソース(DB、Web、ファイル等)に対応	主にExcelファイル内のデータ
学習コスト	M言語の学習が必要	Excel関数の知識があれば比較的容易

どちらの機能も、状況に応じて使い分けることが重要です。Excel数式は手軽な集計や分析に、Power Queryカスタム列は、より大規模で定型的なデータ変換・加工処理に適しています。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】