複数項目でのVLOOKUP
次のようなことをしたいです。
左側A列～C列に、これから作業するデータがあります。右のE列～G列が、いわゆるマスターです。マスターを参照して「高橋の東京だったら10」「大西の神奈川だったら60」のように、"名前"と"地域"の2項目で表引きをしたいんです。これ、業務ではよくやる処理ですから「どうやるんですか？」という質問は鉄板です。もちろん、"みんな大好き"VLOOKUP関数を普通に使っていてはできません。ちなみにこれ、XLOOKUP関数でしたら下図のように一発です。今回は、2つの表ともにテーブルにしています。左側のテーブルが「Data」という名前で、右側が「Master」という名前です。
余談ですが、XLOOKUP関数は、VLOOKUP関数や「INDEX関数＋MATCH関数」の代用関数ではありません。もちろん、"どっちがいい"とかってレベルでもありません。今までのExcelでは不可能だった表引きを可能にする、画期的な関数です。詳しくは下記の動画をご覧ください。
ただ、残念なことに、まだすべてのExcelでXLOOKUP関数が使えるわけではありません。まぁ、そのうちみんな使えるようになるんですけど、今はまだ使えないという方のために、今回はこれを、Power Queryでやってみよう！という話です。昨夜ベッドの中で眠る前に、ふと「もしかして、こうやったら、できんじゃね？ムニャムニャ…」と思いつき、起きてからやってみたら実に簡単でしたのでご紹介します。なお、1項目だけで、普通にVLOOKUP関数みたいなことをやる方法は「VLOOKUP関数と同じことをする」をご覧ください。
まず、左側の作業するテーブルは、下図のようにC列がない状態にしておきましょう。表引きした結果の列は、Power Query側で挿入します。
左側の、作業するテーブル内にアクティブセルを移動して、[データ]タブ[データの取得と変換]グループにある[シートから]ボタンをクリックします。この[シートから]ボタンは、以前は[テーブルまたは範囲から]と記載されていました。みなさんのExcelでは、そう表記されているかもしれません。私が使っているInsider版では、気づいたらこうなっていました。まぁ、このへんはコロコロ変わりますから、そのうちまた戻るかもしれません。なので、[シートから]がない方は[テーブルまたは範囲から]と読み替えてください。
実行すると、左側のテーブル「Data」がPower Query エディタに読み込まれます。
さらにここへ、右側のテーブル「Master」も読み込みますので、今の「Data」をそのままシート上に展開しましょう。[閉じて次に読み込む]をクリックします。
表示される[データのインポート]ダイアログボックスで[テーブル]を選択し、[既存のワークシート]で「セルH1」を指定します。
[OK]ボタンをクリックすると、セルH1に新しいテーブルが読み込まれます。
右側のテーブル「Master」もPower Query エディタに読み込みますので、テーブル内にアクティブセルを移動して[データ]タブ[シートから]をクリックします。
「ブック内の複数テーブルを操作する」にも書きましたが、こうした「シート内に存在している複数のテーブルをPower Query エディタに読み込む」のは、何とも面倒くさいですね。このへんは改良されないような気がしますので、私はあきらめています。さて、実行すると、テーブル「Master」も読み込まれます。
Power Query エディタ左端で、先に読み込んでおいた「Data」を選択します。
この状態で[クエリのマージ]をクリックします。
実行すると[マージ]ダイアログボックスが表示されます。まず、マージするテーブルとして「Master」を選択します。
まずは、最初のキーとなる「名前」を、それぞれクリックします。
続いて、2つめのキーである「地域」を、Ctrlキーを押しながら、それぞれクリックします。
この「Ctrlキーを押しながらクリックすれば、もしかして複数列を指定できんじゃね？」というのが、昨夜ベッドの中で思いついた操作です。やってみたらできました。いやぁ～なんでも試してみるもんですね～。タイトルに"1"や"2"が表示されるのは、おそらく優先順位的なことでしょうね。[OK]ボタンをクリックしてダイアログボックスを閉じると、下図のようになります。
タイトルを適当に変更して、右上にある「上部が左右にひん曲がった矢印」ボタン(正式名称は知りません)をクリックして、次のように指定します。すみません、このへんの解説は割愛します。
完成しましたので、[閉じて次に読み込む]をクリックします。
ここ、すっごく分かりにくいところです。私のセミナーで詳しく解説していますが、ここで指定した「閉じて次に読み込む」は、まだExcel上に読み込んでいない[Master]のことです。このへんは、「元データ」→「Power Query」→「Excel」という"2ステップ"のイメージがないと混乱するでしょうね。
表示される[データのインポート]ダイアログボックスで[接続の作成のみ]を選択します。大事なことなのでもう一度書きますが、ここで指定した[接続の作成のみ]は[Master]のことです。ワークシート上に存在しているテーブル「Master」をPower Query エディタに読み込みましたが、その結果はシート上に展開する必要がありません。同じテーブルを2つ作っても意味がないからです。なので[Master]は[接続の作成のみ]にします。
これで完成です。「うん？あれ？H列に展開した結果のテーブルって、元のA列にあるテーブルと"並び順"が違うのでは？」と気づいた方もいるでしょう。そう、並び順は変わることがあります。てゆーか、こうした操作をすると、ほとんどのケースで毎回変わります。この件は「ブック内の複数テーブルを操作する」にも書きましたが、表計算とデータベースでの"考え方の違い"です。データベースの世界では、保管しているデータの"並び順"は重要ではありません。どんな順序で保管していてもいいのですから、変わっても何ら問題ありません。並び順が気になるのは、表計算ソフト的な考え方ですね。もし、元のような順番に並べて表示したいのでしたら、何らかのインデックスを振っておけば済む話です。このへんの発想を切り替えないと、Power Queryは使えません。