Microsoft Power QueryはExcel 2016で導入されましたが、それ以前のバージョンでもアドインを使ってPower Queryにアクセスできます。このツールを使えば、外部ソースからデータをインポート、スクラブ、編集し、Excelで使用したりPower BI用に準備したりできます。これは、ツールボックスに常備しておくと非常に便利なツールです。この記事では、Power Queryで3つのExcelデータ範囲を結合する方法をご紹介します。また、後ほどフィルタリングや分析を行う際に便利な新しい列を追加して、データを入力する方法もご紹介します。
参照: ソフトウェアインストールポリシー (TechRepublic Premium)
Windows 10 64ビットシステムでMicrosoft 365を使用しています。Power QueryはExcel 2016のインターフェースの一部として利用できます。Web版ExcelはPower Queryをサポートしていませんが、Excelブック内で既存のクエリを実行することは可能です。デモファイルをダウンロードできます。
Excelデータ
多くのユーザーは、関連する種類のデータを、データに意味のある何らかのエンティティごとに分割して、別々のシートに保存しています。例えば、月次売上シートや月次貸借対照表を作成したり、人員シート（各人につき1つのシート）で売上と手数料を追跡したりすることができます。
このタイプの設定は、全体像を把握したい場合、例えば月ごとの売上高とその詳細、あるいは全従業員の売上高と手数料などを確認したい場合まではうまく機能します。3D参照を使用している場合、すべてのデータを統合したシートがどこかにあるかもしれませんが、そのシートには詳細が表示されず、小計と合計のみが表示されます。さらに、この構成では、シートの構造が同一である必要があります。しかし、必ずしもそうとは限りません。
図Aは、 1月の従業員別の売上と手数料を示すシンプルなシートです。他に2月と3月のシートが2つあります。ここでPower Queryの出番です。Power Queryを使って、1月、2月、3月の売上と手数料を素早く結合します。
図A
Excelデータを読み込む方法
最初のステップは、3つのデータセットをPower Queryに読み込むことです。Tableオブジェクトを操作していない場合は、Power Queryはデータ範囲をTableに変換するように促します。Power QueryはExcelのTableオブジェクトのみで動作します。実際、テーブル名はそれぞれJanuary、February、Marchです。これらの名前は後ほどPower Queryで確認できます。
まず 1 月のデータをロードしてみましょう。
- 1 月のシートのテーブル内の任意の場所をクリックします。
- [データ]タブをクリックします。
- [データの取得と変換] グループで、[テーブル/範囲から] をクリックします。
これで完了です。図 B はPower Query のデータを示しています。
図B
この時点で、Power Query テーブルに月を追加することを検討してください。Excel では、月はタブごとに識別されます。しかし、そのタブ名はデータと一致していません。統合データを要求した人が月ごとの分析を希望する場合は、月を追加する必要があります。それでは、次にそれを実行してみましょう。
- Power Query で、[列の追加] をクリックします。
- [全般] 列で、[カスタム列] をクリックします。
- 表示されるダイアログに、わかりやすい名前を入力します。この名前は、他の2つのデータ範囲をPower Queryに読み込む際に再度使用します。「カスタム列の数式」に「January」と入力します（図C）。入力内容を文字列として示すため、二重引用符（” “）で囲む必要があります。
図C
- [OK]をクリックすると、新しい列が表示されます (図 D )。
図D
追加するデータセットごとに月を追加します。各新規テーブルに月フィールドを追加する際は、大文字と小文字が区別されるため、完全に同じ名前を入力するように注意してください。2回目に「MOnth」と入力すると、1つではなく2つの新しい列が作成されます。
Power Queryを終了し、プロンプトが表示されたら新しいテーブルを保存します。各月のデータを読み込む合間に、この操作を行う必要があります。Power QueryはPower Queryテーブルを新しいシートに読み込みます。今のところは、この操作は気にしないでください。
上記の最初の手順を繰り返して、2月のシートにデータを読み込みます。次に、2番目の手順を繰り返して「Month」列を追加し、「February」を入力します。これを3月のシートのデータでも繰り返し、「Month」列を追加し、「March」を入力します。
Power Query に 3 つのテーブルがすべて用意されたので、それらを 1 つに追加する準備が整いました。
Excelで表を追加する方法
Excel のテーブル名が January、February、March だと言ったのを覚えていますか? シート名も January、February、March です。
この時点で、3か月すべてがPower Queryにテーブルとして保存され、各テーブルに列が追加されました。「Month」フィールドは各月を識別するための列です。これらの新しいPower QueryテーブルはExcelにも保存され、それぞれJanuary (2)、February (2)、March (2)という名前が付けられています。これで、Excelで3つのPower Queryテーブルを追加する準備が整いました。Power Queryに戻る必要はありません。
3 つのテーブルを追加するには、Power Query テーブル (1 月 2 日) 内をクリックし、次の操作を行います。
- 右側のクエリ リストで、January クエリを右クリックします。この時点で、Power Query ではそれらをクエリと呼びます。
- 表示されたリストで、「追加」をクリックします (図 E )。
図E
- [追加] ダイアログで、[3 つ以上のテーブル] オプションをクリックします。
- [使用可能なテーブル] リストで 2 番目のテーブル (2 月) をクリックします (最初のテーブル (1 月) は既にクエリの一部です)。[追加] をクリックします。
- [使用可能なテーブル] リストの 3 番目で最後のテーブルをクリックし、[追加] をクリックします (図 F )。
図F
- 3 つのテーブルすべてを [追加するテーブル] リストに表示したら、[OK] をクリックします。
図F （少なくとも一部）からわかるように、1月、2月、3月の3つのデータセットはすべて同じテーブル（クエリ）の一部となり、各レコードは月ごとに識別されます。左側のペインでは、このクエリの名前は「Append 1」です。この時点で、「閉じる」グループの「閉じて読み込む」をクリックすると、新しいテーブルがExcelに保存されます。Power Queryによって新しいシートの名前は「Append 1」になりますが、これは変更できます。これで、3か月分のレコードすべてを好きなように並べ替え、フィルター処理、分析できるようになりました。すべてのデータを結合するのにかかった時間はわずか数分でした。Excelテーブルはシンプルですが、大量のデータを扱う際にそのメリットが実感できるでしょう。
