ブック内の複数リストを結合する
先日のPower Queryセミナーで、受講者の庄司さんから質問されました。「ブック内の複数シートにたくさんのデータがあるんです。それらをすべて結合できませんか？」という内容でした。つまり、こんなイメージです。
実務では、超あるあるの話ですね。ちなみにこれ、最新のExcelではワークシート関数だけでも実現可能です。詳しくは、下記のコンテンツをご覧ください。
今回は、これをPower Queryでやる方法を解説します。まず準備として、結合したい各データをテーブル形式に変換します。
こんな感じです。
テーブルの名前は何でもいいです。標準の「テーブル1」「テーブル2」…でかまいません。Ctrl + LかCtrl + Tを押すとテーブルに変換できます。次に、Power Queryエディタだけを起動してください。何かのデータを取得するのではなく、Power Queryエディタだけを単体で起動します。
Power Queryエディタ[ホーム]タブ右端にある[新しいソース]をクリックして[その他のソース]-[空のクエリ]をクリックします。
実行すると、新しい空のクエリが挿入されます。
標準では「クエリ1」という名前が設定されますので、この名前を変更します。画面右端の[クエリの設定]ペインの[名前]ボックスに新しいクエリの名前を入力してください。
今回は「結合」としました。この名前は何でもいいのですが、ここで指定した名前を後で使います。また、ここで指定した名前が、複数リストを結合した結果のワークシート名になります。そのへんを踏まえて、できれば分かりやすい名前を指定してください。
数式バー(みたいなところ)に、次のように入力します。
= Excel.CurrentWorkbook()
Enterキーを押すと、次の画面になります。
Excel.CurrentWorkbook というのは、M言語の関数で、現在のブック内に存在しているすべてのテーブルを取得します。さて、ここからの操作が、ちょっと分からないかもしれませんけど、後のことを想定して先に手を打っておきます。[Name]列の▼をクリックして、[テキストフィルター]の[指定の値と等しくない][指定の値を含まない]をクリックします。
※2023年5月追記※
ここ、仕様が変わったようです。本稿執筆時点では、クエリ[結合]をワークシート上に読み込むと、そこで作成されるテーブルの名前が「結合」となりました。だから、この時点で「"結合"という名前のテーブルを除外する」という処理を追加しておきました。後述しますが、そうしないと、クエリを更新したとき、テーブル[結合]が再度合体してしまうからです。ところが、この「ブック内の全テーブルを結合する」というデモを各所で行ったとき、なぜか失敗するようになったんです。テーブル「結合」がダブって合体してしまいます。たいていはデモ中なので、何とかトークでごまかしてきましたが(笑)、ちょっと調べてみたら原因が判明しました。仕様変更ですね。新しく作成されるテーブルの名前が「テーブル_結合」となっていました。おそらく、名前定義を行う際に、具合が悪かったのでしょう。なので、当初は上記で「[指定の値と等しくない]をクリックします」と書きましたが、「[指定の値を含まない]をクリックします」に修正しました。これ以降は、画像と本文を直接修正しておきます。ちなみに、このへんの仕様変更は、今後もあり得るので注意が必要ですね～
※2023年10月追記※
また仕様が変わったようです。てゆーか、元に戻りました。クエリ「結合」から読み込んだテーブルの名前が「テーブル_結合」ではなく元の「結合」になってます。まぁ、Insiderを使っているので、しかたありませんけど。どちらにしても[指定の値を含まない]にしておけば間違いないので、本稿もこのままにしておきます。ったく...┐(￣ヘ￣）┌
実行すると[行のフィルター]ダイアログボックスが開きます。[指定の値と等しくない]ボックスの右にある[値の入力または選択]ボックスに"結合"と入力します。この"結合"は、先の操作で変更したクエリの名称です。
[OK]ボタンをクリックしてください。実行しても、何も変わりません。
この操作がなぜ必要かという理由は、あとで実際の画面を表示して解説しますが、先に少しだけ書いておきます。Excel.CurrentWorkbook という関数は、ブック内の全テーブルを取得します。今はまだ[テーブル1][テーブル2][テーブル3]しかありませんから、これら全部のテーブルを結合するのですが、その結果をExcelに読み込むと、読み込まれた結果も[結合]という名前のテーブルとしてブック内に作られます。なので、その後でクエリを更新すると、[テーブル1][テーブル2][テーブル3][結合]の全テーブルが結合されてしまいます。でも、ここでテーブル[結合]は含めたくないんです。2倍になってしまいますから。だから、この時点で[結合]を除いておくわけです。
さて、せっかくフィルタをかけた[名前]列ですが、もう役目を終えましたので削除します。
残った[Content]列の[展開]ボタンをクリックします。
[元の列名をプレフィックスとして使用します]チェックボックスをオフにして、[OK]ボタンをクリックします。
これで、ブック内の全テーブルが結合されます。
「セルの値でクエリを変化させる(日付)」でも解説していますが、セルに入力されている日付(シリアル値)をPower Queryで取得すると、標準で「日付/時刻」のデータ型になります。時刻を使わないのでしたら、データ型を「日付」に変更するといいでしょう。
これで完了です。[閉じて読み込む]ボタンをクリックすると、新しく[結合]シートが挿入されて、そこにブック内の全テーブルが結合されます。
本稿を執筆していた気づいたのですが、このときなぜか、テーブルの一番下に空の行が含まれます。変ですね。たぶん、Power Queryの不具合的なヤツではないかと。まぁ、一度でもこのクエリを更新すれば空の行は除去されますので、それほど気にしなくていいでしょう。もしかしたら、そのうち直るかもしれませんし、直らなくても大きな実害はありません。
さて、正しく動作するか確認してみましょう。新しいワークシートを挿入して、新しいリストを作成します。
クエリ[結合]を更新すると、新しく挿入したリストが自動的に結合されます。
さて、先の操作で「[結合]という文字を含むテーブルを除外」しました。そこを確認してみましょう。クエリ「結合」をPower Queryエディタで開きます。
Excel.CurrentWorkbook 関数は、ブック内の全テーブルを取得します。当然それらには[テーブル_結合]も含まれます。このままクエリを更新すると、更新された[テーブル_結合]テーブルの中に、更新前の[テーブル_結合]テーブルの値が含まれるという、Excelの"循環参照"みたいなことが起こります。なので、次のステップで[テーブル_結合]を除外するわけです。
まとめ
このやり方は、自分でM言語を入力しなければならないので、私のセミナーでは解説してきませんでした。しかし、質問されて、あらためて感じましたが、この操作って実務では"超あるある"ですし、これができたら相当に便利です。なので今回、詳しく解説しました。もちろん、言うまでもありませんが、いくつかの制約もあります。まず「結合する元データはテーブル形式に変換されている」のが前提です。もしかすると、M言語を駆使することで、テーブルではない普通のワークシートでも取得できるかもしれません。でも、そんなことを期待するべきではありません。複数のデータをひとつに結合するのですから、当然それら複数のデータは「データベースとして作成」されていなければなりません。であるなら、それらをテーブル形式に変換できるはずです。それから、もうひとつ制約があります。それは「結合する元データの項目(列見出し)が統一されている」という点です。試してみましたが、一部のデータだけ項目が増えていたり、項目名(列見出し)にブレや揺れがあると、望んだ結果にはなりません。だから、項目(列見出し)は統一してください。これほど便利な機能なのですから、そのくらいは運用でカバーしてくださいね。