Excel で Power Query を使用する方法: 完全ガイド

Power Query は、ニーズに応じてデータをインポート、クリーンアップ、変換できる Excel の強力なビジネス インテリジェンス ツールです。データのクリーンアップに何時間も費やしたことがある場合 (私たちも経験しています)、またはさまざまなソースからのデータをマージするのに苦労したことがある場合 (私たちも経験しています)、この機能を使用すると作業が楽になります。

Excel で Power Query を使用する方法を学ぶ最良の方法は、データのインポートや Power Query エディターを使用した結果のテーブルの変換、データの結合などの一般的なタスクを実行することです。

この記事では、ステップバイステップのガイドとともに明確な Power Query の例を示して開始します。Power Query をマスターすることで、作業を次のレベルに高めることができます。

準備はできたか？これをやろう。

目次

• Power Query にアクセスできるかどうかを確認する方法
• Power Query を使用してデータをインポートする方法
• Power Query エディターの使用方法
  • データのプレビュー
  • プロパティ
• 複数のデータソースを操作する方法
• Power Query を使用してデータを追加する方法
• データを Power Query と結合する方法
• Power Query を使用してデータを変換する 5 つの方法
  • 1. データの拡張と圧縮
  • 2. データの並べ替え
  • 3. データのフォーマット
  • 4. 列の追加と削除
  • 5. データ型の変更
• 数式と M 言語の操作
  • 数式バー
  • M コードの作成と変更
• M 言語構文とは何ですか?
• データ接続を更新する方法
• Power Query をデータ モデルと統合する方法
• Power Query と VBA を使用して自動化する方法
• Power Query の高度な機能
• コラボレーションと共有
• これをまとめましょう

Power Query にアクセスできるかどうかを確認する方法

Power Query の使用を開始する前に、この機能を備えた Excel のバージョンがあることを確認する必要があります。

Power Query のバージョン別の概要を次に示します。

• Excel 2010 および Excel 2013: ダウンロードしてインストールする必要があるアドインとして利用可能です。

• Excel 2016: 「Get & Transform」として Excel に統合されました。

• Excel 2019: パフォーマンスと機能が向上しました。

• Excel 2021 および Excel for Microsoft 365: クラウド データ ソースとの統合。

• Excel for Mac: Microsoft 365 バージョン 16.69 以降でのみ利用可能です。

ツールが Excel ワークブック (2016 以降) に統合されていることを確認するには、[データ] タブに移動し、次の図の [データの取得と変換] セクションを探します。

Windows 上に Excel 2010 または 2013 がある場合は、Power Query アドインをダウンロード、インストールし、有効にすることができます。

Power Query を使用してデータをインポートする方法

Power Query ツールを使用すると、さまざまなデータ ソースをインポートして接続できます。

• CSV、XML、JSON 形式を含むファイル

• Web URL または API エンドポイント

• Access、SQL Server、Oracle、および最新のデータベースを含むデータベース

• Azure サービス、SharePoint ファイル、OData フィード

最も簡単なデータ ソースは、数行のデータを含む単純な CSV ファイルです。テキスト ファイルを作成し、次の 4 つの姓名をカンマで区切って追加します。

• ジョー、ブログス

• アン、ライアン

• ジェームス、スチュワート

• メアリー・ブラウン

必ず .CSV 拡張子を付けてファイルを保存してください。ファイルは次のようになります。

Power Query を使用してデータを読み込むには、次の手順に従います。

1. 「データ」タブに移動します。

2. 「データの取得と変換」タブの「ファイルの取得」ボタンをクリックします。

3. サンプル ファイルを選択します。

ツールがデータをプレビュー ペインに取り込むまで、数秒待つ必要がある場合があります。Power Query は、少量のインポートでもかなり多くのマシン リソースを使用するため、しばらくお待ちください。

ヒント: パフォーマンスを高速化するには、他のアプリケーションをシャットダウンしてください。

Power Query により、[テキスト/CSV インポート] ウィンドウが表示され、データのプレビューが表示されます。

データが正しいと思われる場合は、データのロードまたは変換に進むことができます。

• 「ロード」ボタンをクリックすると、データが Excel に直接ロードされます。

• [データの変換] ボタンを使用すると、データをロードする前にデータを変更できます。

サンプル ファイルにヘッダーがないことに気づきましたか? Excel にそのまま読み込んだ場合、ヘッダーは「列 1」と「列 2」になります。

代わりに、Power Query エディターにデータを取り込み、いくつかの変更を加えることができます。これを行うには、「データを変換」ボタンをクリックします。

Power Query エディターが開き、次のセクションに進みます。

Power Query エディターの使用方法

このセクションでは、Power Query エディターの操作に関するいくつかの重要な側面について説明します。

データのプレビュー

データ プレビュー領域では、インポートされたデータを表示して操作できます。ニーズに合わせて、行をフィルターしたり、列のデータ型を変更したり、データを再形成したりできます。

完了すると、Excel は変換されたデータをワークシートに読み込みます。

サンプル ファイルを使用すると、このペインで列名を直接編集できます。

そうするために：

1. 列ヘッダーを右クリックします。

2. ドロップダウンメニューの「名前の変更」をクリックします。

3. ヘッダー ボックスに希望の名前を入力します。

変更を加えると、プレビュー ペインの右側にある「適用されたステップ」セクションが更新されて各変換が反映され、必要に応じてデータ処理ステップを追跡および変更できるようになります。

プロパティ

クエリのプロパティを設定することは、作業を整理および追跡するために不可欠です。

[クエリ設定] ウィンドウは、Power Query ウィンドウの右側にあります。

1. 「すべてのプロパティ」リンクをクリックします。

2. クエリの名前をわかりやすいように編集します。

3. さらに詳しい情報を提供するには説明を追加します。

先週のクエリが何をするために設定されていたのかを忘れがちです。この場合、クエリ名が変更され、クエリの目的を示す説明が追加されています。

使用可能なオプションの詳細については、Power Query エディターの概要をご覧ください。

複数のデータソースを操作する方法

Power Query エディターでは、複数のソースからのデータを結合するための 2 つの主な方法、追加とマージが提供されます。

追加すると、テーブルが垂直に結合され、互いの上に積み重ねられます。マージを使用すると、共通の列に基づいてテーブルを結合できます。

これがどのように機能するかを確認するには、最初の CSV ファイルと同じ構造を持つ 2 番目の小さな CSV ファイルを作成します。今回は、ファイルに 2 つの名前 (姓と名) を入力します。

• ジョー、ブログス

• ローラ・ケイン

これらの名前の 1 つが前のファイルから繰り返されていることに注意してください。

次の手順に従って、データを Microsoft Excel ワークブックに取り込みます。

1. Power Query エディター ウィンドウを閉じて、変更を保存します。

2. 新しいワークシートを追加します。

3. 前のセクションの手順に従って、2 番目のファイルをインポートします。

4. 「変形」ボタンをクリックします。

5. 列ヘッダーの名前を「名」と「姓」に変更します。

Excel ワークシートの右側のペインには、現在のクエリと接続が表示されます。ペインに 2 つのクエリが表示されます。

Power Query を使用してデータを追加する方法

このセクションでは、2 番目のサンプル CSV ファイルを最初のサンプル CSV ファイルに追加します。

データを追加するには、次の手順に従います。

1. [クエリと接続] ペインの最初のテーブル (顧客名) を右クリックします。

2. [編集] を選択して、Power Query エディターでテーブルを開きます。

3. 「結合」セクションで「クエリを追加」をクリックします。

4. 追加するテーブルのドロップダウン リストから 2 番目のテーブルを選択します。

結果として、最初のテーブルに 2 行が追加されます。

1 つの行が繰り返されていることに注意してください。これが追加操作の仕組みです。データがすでに存在するかどうかに関係なく、あるテーブルのデータを別のテーブルに追加します。

次の手順で、Power Query エディターで重複データを削除できます。

1. 「名」列ヘッダーをクリックします。

2. 「Ctrl」キー（Macの場合は「Cmd」キー）を押したままにします。

3. 両方の列ヘッダーをクリックします。

4. Power Query エディターのリボンの [ホーム] タブで [行の削除] をクリックします。

5. ドロップダウン メニューから「重複の削除」を選択します。

データを Power Query と結合する方法

データを結合するときは、クエリ間で一致する列を選択する必要があります。

カンマ区切りの 2 つのファイルがあり、1 つは顧客の電話番号を含み、もう 1 つは住所を含むとします。2 つのファイルは次のようになります。

前のセクションの手順に従って、各ファイルを独自のパワー クエリにロードします。その後、テーブルを結合する練習をすることができます。

サンプル データの場合、両方の列 (名と姓) が一致する列として選択される必要があります。Ctrlキーを押したままにして、複数の列を選択します。

次の手順を実行します：

1. [クエリと接続] ペインの最初のテーブルを右クリックします。

2. [編集] を選択して、Power Query エディターでテーブルを開きます。

3. 「結合」グループから「クエリを結合」を選択します。

4. テーブルのドロップダウン リストで 2 番目のテーブルを選択します。

5. 最初のテーブルの最初の列をクリックします。

6. Ctrl キーを押したまま、最初のテーブルの 2 番目の列をクリックします。

7. Ctrl キーを押したまま、2 番目のテーブルの両方の列をクリックします。

8. デフォルトの結合演算子を「左外部」のままにします。

9. 「OK」をクリックして変更を適用します。

「左外部」結合演算子により、重複データが確実に削除されます。

Power Query エディターに、2 番目のクエリからの結合されたデータを含む "テーブル" または "入れ子になったテーブル" という名前の新しい列が表示されます。

1. この列のヘッダーにある展開アイコン (2 つの矢印) をクリックします。

2. 「元の列をプレフィックスとして使用」ボックスのチェックを外します (これにより、長い列名が回避されます)。

3. 「OK」をクリックします。

4. 「閉じてロード」をクリックします。

結合されたテーブルには 4 つの列があり、電話番号と住所の詳細が結合されました。まだ少し整理する必要があるかもしれません。

たとえば、下の図のテーブル名 (Merge1) とアドレス列を編集するとします。

Power Query を使用してデータを変換する 5 つの方法

Power Query を使用すると Excel ファイル内のデータの変換が簡単になり、データを効率的にクリーンアップ、整理、操作できるようになります。

データを変換する最も一般的な方法は次のとおりです。

• データの拡張と圧縮

• データの並べ替え

• データのフォーマット

• 列の追加と削除

• データ型の変更

1. データの拡張と圧縮

データセットをより管理しやすくするには、データの拡張と圧縮が重要です。Power Query を使用すると、テーブルを展開したり折りたたんだりして、より整理されたビューを作成できます。

前のセクションでは、関連テーブルのデータを含めて拡張する方法を示しました。

また、合計、数、パーセンテージなどの選択した集計に基づいて要約データのみを含めるように圧縮することもできます。

2. データの並べ替え

データを並べ替えるには、値を昇順または降順にすることで順序をすばやく変更できます。

列名の横にある下矢印をクリックし、「昇順で並べ替え」または「降順で並べ替え」を選択します。

3. データのフォーマット

Power Query エディターの [変換] タブには、多くのオプションが用意されています。

たとえば、[形式] ドロップダウン メニューを使用すると、列内のすべてのデータまたはテーブル全体を大文字または小文字に変更できます。

4. 列の追加と削除

Power Query を使用すると、列の追加、削除、変更のプロセスが簡素化されます。

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

たとえば、計算と比較を容易にするために、小数点をパーセンテージに変換したり、テキストベースのパーセンテージを数値に変換したりする必要がある場合があります。

数式と M 言語の操作

Excel で Power Query を使用すると、高度な数式の作成、データの操作、強力なクエリの作成の中核となる M 言語が使用されます。このセクションでは、以下について学習します。

• 数式バー

• Mコード

• M 言語構文の重要な要素

数式バー

Excel の Power Query エディターの数式バーは、数式と M コードを入力する領域です。

これを使用するには、[クエリと接続] ペインからクエリを選択して Power Query エディターに移動します。数式バーはデータ プレビュー ペインの上にあります。

M コードの作成と変更

Excel で多用途で強力なクエリを作成するには、M コードとして知られる Power Query 言語を作成して変更する必要があります。開始方法に関するヒントをいくつか紹介します。

• [適用されたステップ] ペインを使用してステップバイステップのコードを作成すると、クエリのロジックの追跡と変更が簡単になります。

• M コードを Excel テーブル、ピボット テーブル、Azure やテキスト ファイルなどの外部データ ソースと組み合わせて、動的で自動化されたレポートを作成します。

• クエリ オプションを調整してデータ読み込み設定を調整し、パフォーマンスを向上させます。

M コードに慣れるにつれて、多くのプロセスを自動化および最適化し、Excel でのデータ分析をより効率的かつ強力にすることができることがわかります。

クエリの背後にあるコードを表示するには、次の手順に従います。

1. Power Query エディターでクエリを開きます。

2. 「ホーム」タブの「詳細エディタ」をクリックします。

これは、マージされたテーブルのサンプルの背後にあるコードです (実際のものは異なる場合があります)。

M 言語構文とは何ですか?

M 言語の構文を理解することは、効果的な Power Query 数式を作成するために重要です。次の点に留意してください。

• M 言語では大文字と小文字が区別されるため、大文字と小文字の区別には注意してください。

• 列、レコード、リストを参照するには角括弧 [] を使用します。

• クエリの前のステップを参照するには、その変数名を使用します (例: #”Step Name”)。

• 「in」ステートメントを使用してクエリ式ステップを出力します。

Power Query 言語フローの経験を積むにつれて、データを変換および分析する新しい方法を発見し、Excel ブックをさらに価値のある資産にすることができます。

下のビデオをご覧ください。たわしを取り出して、乱雑なデータをクリーンアップしましょう。

データ接続を更新する方法

Power Query の重要な機能の 1 つは、Power Query 接続を簡単に更新できることです。

データ ソースが更新され、ワー​​クブック内の最新情報が必要な場合は、[クエリと接続] ペインでデータ接続を更新できます。

ワークシートの右側にペインが表示されない場合は、データ テーブルに移動し、[クエリと接続] をクリックしてペインを開くことができます。

[クエリと接続] ペインが開くと、すべてのクエリがリストされます。次の手順を実行します：

1. 更新するクエリを右クリックします。

2. コンテキスト メニューから [更新] を選択します。

これにより、接続されたデータ ソースからの最新データでワークブックが更新されます。

Power Query をデータ モデルと統合する方法

Excel のデータ モデルは、データの関係に基づいてピボット テーブルなどのビジネス インテリジェンス ツールを作成できる強力な機能です。

データ モデルは基本的に、インポートされたデータとテーブル間の関係を保持する隠しデータベースです。

これは、現在のバージョンの Excel に組み込まれている Power Pivot 機能の一部です。以前のバージョンで Power Pivot にアクセスするには、アドインとして Power Pivot をインストールする必要がある場合があります)。

次の手順で、Power Query データ接続をデータ モデルと統合できます。

1. 前のセクションで説明したように、Power Query を使用してデータを読み込みます。

2. ワークブックに直接ロードする代わりに、「ロード」メニューから「ロード先」を選択します。

3. [データのインポート] ダイアログ ボックスの [このデータをデータ モデルに追加] チェックボックスを選択します。

Power Query と VBA を使用して自動化する方法

VBA (Visual Basic for Applications) の統合により、カスタム データ接続を作成し、Power Query でのデータ変換手順を自動化できます。

既存の VBA スキルを活用して、Microsoft Access、SharePoint、Analysis Services などのプラットフォームからのデータを組み込むことで、高度なデータ モデルを構築できます。

Power Query には VBA のネイティブ サポートがありませんが、VBA コードを使用して、Power Query クエリを開いたり、更新したり、間接的に操作したりできます。

これにより、強力な自動化されたデータ ワークフローを作成し、データ分析プロセスを合理化できます。

現在のワークブックのパワー クエリを更新するサンプル コードを次に示します。

:

ワークブックとしての wb、オブジェクトとしての pq を暗くする

wb = ThisWorkbook を設定します

Set pq = wb.Queries(“顧客名”)

 pq.リフレッシュ

Excel VBA マクロ内のコードは次のとおりです。

Power Query の高度な機能

Excel の Power Query は、データ分析プロセスを簡素化し、強化するための高度な機能を多数提供します。これらの機能を効果的に使用する方法を学ぶことで、データ分析スキルをまったく新しいレベルに引き上げることができます。

組み込みのデータ分析ツールには次のものが含まれます。

• データ変換に複雑なロジックを適用するための条件付き列。

• 意味のある方法でデータを要約するためのグループ化および集計関数。

• 分析を容易にするためにデータを再形成するピボット操作とアンピボット操作。

これらの機能の多くは、Power Query エディターの [変換] タブにあります。

さらに、Power Query をLuckyTemplates などのビジネス インテリジェンス ツールに接続して、より高度なデータ分析と視覚化のオプションを実現できます。

コラボレーションと共有

データ接続、クエリ、変換をチーム メンバーと共有することは、効果的なコラボレーションに不可欠です。

ワークブックをネットワーク ドライブ、 SharePoint、またはその他の共有ストレージの場所に保存することで、クエリを含むワークブックを共有できます。

次の手順で、単一のクエリの背後にある M コードをエクスポートすることもできます。

1. Power Query エディターでクエリを編集します。

2. 「ホーム」タブの「詳細エディタ」をクリックします。

3. Advanced Editor ウィンドウで M コード全体をコピーします。

4. テキスト エディタ (メモ帳など) を開き、コピーした M コードをテキスト エディタに貼り付けます。

5. テキスト ファイルを保存し、同僚に送信します。

これをまとめましょう

さまざまな機能と使いやすいインターフェイスを備えたPower Query は、外部データを効果的に操作したいと考えている Excel ユーザーにとって不可欠なツールです。

この貴重な機能は、レポートの作成、データ クレンジング、およびデータ テーブルに対する複雑な分析の実行のプロセスを合理化するのに役立ちます。使用する

Power Query は最終的に時間を節約し、Excel ファイルを操作する際の効率を高めます。