Excel で Power Query を使用する方法

2024/03/04

Microsoft Excel はデータを操作するためのツールと機能をいくつか提供していますが、Power Query はその中で最も優れたものの 1 つです。このビジネス分析ツールを使用すると、さまざまなソースからデータをインポートし、必要に応じて Excel で簡単に変換および操作できます。基本的に、反復的なタスクが排除され、労力が軽減され、時間の節約に役立ちます。

Power Query の主な利点は、使用するためにコーディングの専門知識や知識が必要ないことです。これを使用して Microsoft Excel のデータを操作する方法を見てみましょう。

Power Query へのアクセス

Power Query は、Excel 2010 以降、Microsoft Excel のすべてのバージョンで利用できます。Excel 2016 からは、アプリケーションに直接組み込まれています。

Excel 2016 以降の場合

新しい Excel ワークシートを起動し、メニューバーの [データ] タブをクリックします。

「データ」タブのオプションから、メニューバーの下の左上にある「データの取得」オプションをクリックします。

これには、データのインポートと変換のためのすべての Power Query ツールとオプションが含まれています。

Excel 2013 および 2010 の場合

Excel バージョン 2013 および 2010 の場合、Power Query は Microsoft Web サイトからダウンロードできる無料のアドオンとして利用できます。

Power Query のダウンロードページに移動し、[ダウンロード] ボタンをクリックしてツールのダウンロードを開始します。

[ダウンロード] ボタンをクリックすると、システムに応じて適切なオプションを選択できるいくつかのオプションが表示されます。

正しいオプションを選択したら、「ダウンロード」ボタンをクリックしてツールをダウンロードします。

Power Query ツールの使用

Excel ワークシートを開いた状態で、[データ] タブから [データの取得] オプションから Power Query ツールにアクセスできます。

データのインポート

[データの取得] オプションをクリックすると、データをインポートできるさまざまなソースが表示されます。これらには、Excel ワークブック、テキストまたは CSV ファイル、XML、およびJSON ファイルが含まれます。これらに加えて、SQL Server や Microsoft Access などのオンラインデータベースからデータをインポートすることもできます。データをインポートできるその他のソースには、Microsoft Azure や、Salesforce や Facebook などのオンラインサービスなどがあります。
データをインポートするには、「ファイルから」、「データベースから」、「Azure から」、「オンラインサービスから」、「他のソースから」などのオプションのいずれかをクリックします。

データをインポートすると、Excel には、ロードされるデータのプレビューを表示するポップアップが表示されます。下部にある「ロード」ボタンをクリックしてデータのインポートを完了します。

これで、Excel ワークシートにデータが表示され、それにさまざまな変換を適用できるようになります。

Power Query エディターのコンポーネント

インポートされたデータを必要に応じて変換するには、Power Query エディターが必要です。「データの取得」ボタンをクリックした後、「Power Query エディターの起動」をクリックします。

これにより、6 つの主要コンポーネントで構成される「Power Query エディター」が起動します。上部には「クエリエディターリボン」があり、さまざまなタブの下にさまざまなコマンドが含まれています。

左側の「クエリエディターリボン」の下には「クエリリスト」があり、ワークブック内のすべてのクエリが表示されます。中央には「データプレビュー」セクションもあり、データに適用されたすべての変換が表示されます。

「式バー」を使用すると、変換ステップの M コードを編集できます。すべての変換は記録され、「適用されたステップ」領域にステップとして表示されます。

「プロパティ」セクションでは、クエリに名前を指定できます。

変換の適用

Power Query エディターにインポートされたデータにさまざまな変換を適用できます。これらには、テキストの形成、トリミング、転置などが含まれます。

テキストの変換

テキストをエディタにインポートした後、テキストを大文字または小文字に変換できます。

Power Query エディターで、上部の [変換] タブに移動すると、[転置]、[値の置換] などのいくつかのオプションが表示されます。

「フォーマット」オプションは中央の「列の分割」オプションの隣にあります。それをクリックすると、利用可能な書式設定オプションが表示されます。

「小文字」または「大文字」などのオプションをクリックして、選択した列のテキストを小文字または大文字に変換します。同様に、他のオプションをクリックすると、それに応じてテキストが変換されます。

「フォーマット」オプションでは、「トリム」オプションを使用してすべての空白を削除することもできます。「トリミング」ボタンをクリックすると、テキストから余分な空白がすべて削除されます。

列の分割

テキストの変換とは別に、Power Query エディターではさまざまな方法で列を分割できます。

データを Power Query エディターにインポートしたら、列見出しをクリックして列全体を選択します。

次に、「書式設定」ボタンの左側にある「列の分割」ボタンをクリックします。これにより、選択した列をさまざまな方法で分割できるオプションのリストが表示されます。

列を区切り文字で分割するには、それぞれのオプションをクリックします。これにより、区切り文字による分割ポップアップが表示され、カンマ、コロン、等号などの区切り文字を選択できます。

「OK」ボタンをクリックして必要に応じて列を分割すると、列が分割されたことがわかります。

データの転置

「転置」オプションを使用すると、ユーザーはデータを行から列に、またはその逆に切り替えることができます。これを行うには、前に説明したように、まずデータを Power Query エディターにインポートします。

データをロードした後、上部の「変換」タブに移動すると、「転置」オプションがあります。

「転置」オプションをクリックして行を列に変換します。

クエリの結合

Power Query では、[結合] および [追加] オプションを使用して複数のデータセットを簡単に結合できます。

結合オプションの使用

マージ操作を使用すると、既存のクエリを結合して新しいクエリを作成できます。

まず、ファイル、データベース、またはその他のソースから Excel ワークシートにデータをインポートします。この場合、Power Query エディターにデータを読み込む必要はありませんが、複数のデータセットをインポートする必要があります。

データをインポートするオプションの下に、「クエリの結合」という別のオプションが表示されます。このオプションにカーソルを置くと、追加とマージの 2 つのオプションが利用可能になります。

「マージ」ボタンをクリックすると、マージする必要があるデータセットを選択できる新しいポップアップが表示されます。

データセットを選択すると、プレビューが表示されます。 [OK] ボタンをクリックする前に、左下でデータセットを結合する方法を選択できます。

追加オプションの使用

「追加」オプションを使用すると、前のクエリの行を結合して新しいテーブルを作成できます。

上記と同じ手順に従って Excel ワークシートにデータセットを追加し、[クエリの結合] セクションの [追加] オプションに移動します。

表示されるポップアップで、データを結合する必要があるテーブルを選択してから、「OK」ボタンをクリックします。ユーザーは、2 つのテーブルまたは 3 つ以上のテーブルのデータを結合できます。

結合されたデータは Power Query エディターウィンドウに表示され、そこから左上の [閉じてロード] ボタンを使用してワークシートにデータをインポートできます。

ワークシートへのデータのロード

Power Query エディターですべての操作が完了したら、データを Excel ワークシートにロードする必要があります。

変換されたデータを Excel ワークシート (ピボットチャート、ピボットテーブル、テーブル、クエリの接続など) に読み込む方法はいくつかあります。左上の「閉じてロード」オプションをクリックすると、「閉じてロード」と「閉じてロード」の 2 つのオプションが表示されます。

2 番目のオプションをクリックすると、データをワークシートにロードするためのさまざまなオプションが表示されます。

Excel では、既存のワークシートのセルや自動的に作成される新しいシートなどの場所を選択できます。「このデータをデータモデルに追加」オプションもあります。

数式と関数の使用

Power Query では、Excel ワークシートと同様の数式や関数を使用することもできます。これには、数式や関数を追加できるカスタム列を追加する必要があります。

[データの取得] タブから Power Query エディターを起動し、上部の [列の追加] タブに移動します。

左側にクエリが表示されます。いずれかをクリックして選択すると、「カスタム列」がアクティブになります。「カスタム列」オプションをクリックして、新しい列を作成します。

カスタム列を作成するダイアログボックスで、列の名前を指定します。

「カスタム列の式」セクションで、列を作成するための式を追加します。たとえば、のような数式を使用します[First Name]&""&[Last Name]。 Power Query エディターは、数式にエラーがあるかどうかを確認します。

エラーがない場合は、「OK」ボタンをクリックすると、エディターによって列が作成されます。

関数を使用するには、「カスタム列」ポップアップが表示されるまで手順を繰り返します。「カスタム列の式」セクションで、Text.Upper([Full Name])すべての名前を大文字で作成するなどの関数を追加します。

列の追加を完了するには、「OK」ボタンをクリックして、名前が大文字の列を作成します。

Power Query を使い始めるために知っておく必要があるのはこれだけです。このツールを使用すると、必要に応じて Microsoft Excel でデータを非常に簡単に変換できるため、最小限の労力で分析して結論を導き出すことができます。これを使用して、さまざまなデータセットを結合したり、その形式を変更したり、その他のアクションを実行したりできます。さらに、エディターで Excel の関数や数式を使用することもできるため、さらに便利になります。