【Excel講座】Power Queryの使い方を簡単に解説！～データ変換作業を簡単に自動化～

この記事では、Excelで使えるPower Queryという機能で、データを自動で読込・整形するための、基本的な使い方を解説します。

Power Queryを使うと、Excelで列を追加したり、複数Fileを結合する等のデータ変換が簡単に自動化でき非常に有用なので、是非見てください！

Youtube動画で見たい方は、こちらからどうぞ！

Power Queryで何ができる？

Power Queryは、ExcelやPower BIで使える機能で、ExcelやCSV、Web等のデータを取り込み、分析用にデータを整形することができます。

例えば以下のように、Power Queryでデータ変換が可能です。

Power BIでできることは？データに基づく意思決定に役立つ！ Power BIの実際の使い方のデモは、こちらの動画を参考にしてください！ https://youtu.be/tFr...

Power Queryの主な機能は、以下の4つがあります。

それぞれの機能について簡単に紹介します。

データ変換の自動化

Power Queryを使う大きなメリットの1つは、データ変換の自動化ができる点です。

Power Queryで行ったデータ変換操作は、「適用したステップ」にクエリ（処理の命令文）として保存されています。

そのため、新たなデータが追加される等、データの更新があった際、Power Queryを使えば、更新ボタンを押すだけで、保存したデータ変換の処理を実施してくれます。

複数ファイルや複数シートの結合

複数ファイルや、シートを１つに結合できる機能もPower Queryの大きなメリットです。

Excelの複数シートに分かれたデータについても、同じように結合することができます。

異なるテーブルのマージ

Power Queryでは、異なるテーブルをマージすることもできます。

例えば左の表のような、売り上げデータ一覧と、仕入れデータ一覧があるとします。

PowerQueryでは、簡単な操作で売上データ一覧に、仕入れで第一覧の仕入れ額の列を追加することなどができます。

列の計算や列

Power Queryは、列の計算や、列の追加も簡単にできます。

例えば、売り上げデータ一覧の売上額から、仕入れ額を引いて、利益額という列を追加することなどができます。

その他にも、日付データから現在までの期間を計算したり、17歳以下なら子供、18歳以上ならば大人など、条件を設定して列を追加する等もできます。

Power Queryの使い方

次にPower Queryの基本的な使い方を解説します。

操作方法は、データ取り込み、データ変換、変換を適用の3ステップになります。

今回実現する内容

今回は実際に以下の、総務省統計局：世界の統計2022「国民経済計算」のデータを読み込み、変換してみます。

＜読み込むデータ＞

Excelを開くと、国民総所得（GNI）のデータは、3ー8（1）のシートと、3ー8（2）のシートの2シートに分かれています。

この2つのシートを一緒に取り込み、国ごと、年ごとのGNIと、一人あたりのGNI、そして国ごとの人口を計算して列を追加してみます。

↓

＜Power Queryでデータ変換後＞

データ取り込み

データ取り込みのステップでは、共有フォルダや、SharePoint、Web等からExcelやCSVファイルのデータを取り込みます。

１．総務省統計局から取得した「世界の国民総所得（GNI）のデータ」をダウンロードし、Excelファイルを、フォルダーに格納します。

２．新しいExcelを開き、データタブ＞新しいクエリ＞ファイルから＞フォルダからをクリックします。

３．GNIデータのExcelを格納したフォルダーパスを指定し、「開く」をクリックします。

「データの変換」をクリックすると、Power Queryエディターの画面が新しく開きます。

４．これでデータの読み込みができました。

このPower Queryエディターの画面で、データ変換操作を行います。

Power Queryエディタの画面は以下のような構成になっています。

Power Queryエディタ上で行ったデータ読込・データ変換の操作は、「適用したステップ」に記録されます。

そして、Excelのデータタブから、更新ボタンを押すだけで、同じ操作を実行することができます。

データ変換

Power Queryの操作は、このデータ変換のステップがメインになります。

今回取り込んだ世界のGNIのデータを、以下のように変換するのがゴールです。

Step0：データを整える

最初にデータ変換操作を使って、テーブルのフォーマットを整えます。

１．一番左に「クエリの一覧」が表示されますが、これはテーブル名と考えて良いです。

クエリの名前は最初に分かりやすく変えておきます。

２．ファイルのGNIデータのシート、3ー8（1）、3ー8（2）を一緒に取り込み、結合します。

Content列の右の「ファイルの結合」アイコンをクリックし、「パラメーター」を選択し、「OK」をクリックします。

３．先ほど表示されていたすべてのシートが取り込まれるので、ここから対象となる3ー8(1)、3ー8（2)に絞ります。

やり方は色々ありますが、今回はName列の右にある▽のアイコンをクリックし、「テキストフィルター」＞「指定の値を含む」を選択します。

４．値に、3-8と入力してOKをクリックすると、対象のシートに絞り込まれます。

５．対象のシートに絞り込んだため、Data列の右の「展開」アイコンをクリックします。

これにより、選択されたテーブルの各列のデータを展開します。

取り込む対象の列名は、全てにチェックを入れ「元の列名をプレフィックスとして使用します」のチェックを外し、「OK」をクリックします。

６．これで、対象の2シートのデータを取り込むことができました。

この後は列名を変更したいです。

現在はColumn1、Column2…などとなってい ますが、Column1は国（地域）列、Column2は2016の列にしたいです。

７．「ホーム」タブ＞「行の削除」＞「上位の行を削除」を選択します。

この操作で、上位の何行を削除しますと、行数を指定して削除することができます。

８．「行数」に3を指定して「OK」をクリックすると、上位3行が削除されます。

これでColumn2以降の、一番上の行に、2016、2017…というデータが来ました。

９．「ホーム」タブ＞「1行目をヘッダーとして使用」をクリックすると、年の列がヘッダー列になります。

１０．「適用したステップ」の「削除された最初の行」を選択すると、上位３行を削除したステップに戻ります。

Column3列を選択し、「ホーム」タブ＞「値の置換」をクリックします。

１１．ステップの挿入で「挿入」をクリックし、途中にステップを追加します。

１２．「検索する値」を「null」、「置換後」の値を「国（地域）」と設定し、「OK」をクリックすると、nullの値が、国（地域）に変更されました。

１２．「変更された型1」のステップに戻ると、エラーとなっていますが、これは元々Column3だった列名が、国（地域）となってしまったためです。

このステップを削除してしまっても良いですが、変更する箇所が1カ所だけなので、今回は関数式の、「Column3」の箇所を、「国（地域）」に修正します。

これで、ヘッダー列が適切になりました。

１３．データの中身を見てみると、アジアや、北アフリカ等の地域の列はnullになっています。

「2017」の列のフィルター＞「空の削除」をクリックし、nullのデータ行を削除します。

１４．まだ世界のデータや、国（地域）や年が入っている不要な行があるため、「国（地域）」の列のフィルター＞「テキストフィルター」＞「指定の値を含まない」を選択します。

１５．「値に」「世界」、もう一つ「指定の値を含まない」を選択し、「地域」と入力して、「OK」をクリックします。

１６．また不要な列も削除するため、「ホーム」タブ＞「列の選択」＞「列の選択」をクリックします。

１７．必要な列にチェックを入れ「OK」をクリックすると、不要な列は削除されます。

Step1：テーブルを2つに分割

Step0で変換したテーブルを、GNIと、一人当たりのGNIの2つのテーブルに分割します。

１．「GNI」のクエリを右クリック＞「参照」を選択します。

同じ操作をもう1回行い、3つのテーブルを増やします。

２．参照したテーブルの一つを「GNIデータ」、もう一つを「一人当たりのGNI」と、ダブルクリックして名前を変更します。

３．それぞれのテーブルで、GNI、データ1人当たりのGNIのデータの列を残し、「ホーム」タブ＞「列の選択」で、不要な列を削除します。

４．一人当たりのGNIデータの年の列は、アンダーバー以降を削除し、2016～2019の列に変更します。

Step2：2つのテーブルそれぞれをピボット解除

次に、2つに分けたテーブルそれぞれで、「年」の列と「GNI」の列、「年」の列と「1人あたりのGNI」の列ができるように変換します。

１．「GNIデータ」の「国（地域）」列を選択し、「変換」タブ＞「列のピボット解除」＞「その他の列のピボット解除」を選択します。

２．すると「属性」と「値」の列に変換されるため、列名を「年」と「GNI」に変更します。

３．「一人当たりのGNI」テーブルの方も、同様の操作でピボット解除を行います。

これでピボット解除ができました！

Step3：2つのテーブルをマージ

次に、2つのテーブルをマージし、再び一つのテーブルにします。

１．「GNIデータ」を選択し「ホーム」タブの＞「クエリのマージ」をクリックします。

２．ドロップダウンで「一人当たりのGNI」データを選択します。

国(地域)列と、「年」列の2列が結合キーとなっているため、Ctrlキーを押しながら、国(地域)列、年列をそれぞれ選択し、結合の種類は「左外部結合」を選択し、「OK」をクリックします。

３．「一人当たりのGNI」のテーブルが結合されたので、右の「展開」アイコンをクリックし、「1人あたりのGNI」列にチェックを入れて、「OK」をクリックします。

これで2つのテーブルがマージできました。

Step4：計算列の追加で人口を計算

最後に、計算で人口列を追加します。

１．数値データ同士の計算をするため、データ型を変換しておきます。

２．「GNI」と「一人当たりのGNI」の2列を選択し、「変換」タブ＞「データ型」＞「整数」を選択します。

３．人口データの列を追加するため、「GNI」列を選択し、Ctrlキーを押しながら「一人当たりのGNI」列を選択します。

「列の追加」タブ＞「標準」＞「除算」を選択すると、人口を計算して列を追加することができます。

４．列名を「人口」に変えておきます。

５．ところで元データは、GNIが100万ドル単位、1人当たりのGNIはドル単位、人口は100万人単位となり、1人あたりのGNIだけが単位が異なるため、100万で除算しておきます。

列を追加しない計算の場合は、「一人当たりのGNI」列を選択した後、「変換」タブ＞「標準」＞「除算」を選択します。

６．「1000000」と入力して、OKをクリックします。

以上で、Power Queryによるデータ変換の作業は完了です。

データ変換の適用

最後に、データ変換の適用を行います。

１．Power Queryでのデータ変換が終わったら、「ホーム」タブ＞「閉じて読み込む」を選択します。

２．するとデータが読み込まれ、今までの操作が適用されます。

３．不要なテーブルのシートは削除し、必要なテーブルのシート名を変更しておきます。

これでPower Queryによるデータ変換の適用も完了しました。

最後に

本日は、Excel Power Queryの基本的な使い方を解説しました。

Excel Power Queryを使えば、データ読込・データ変換を簡単に自動化することができます。

元データを修正、更新した際も、同じデータ変換操作を適用するので、定期的に出力する帳票等のデータ整形に適しています。

分析用に使うデータだけでなく、複数のファイルを結合したりして、Excelのリストを作成したい場合にも有用です。