第5回 VLOOKUP関数より簡単! クエリのマージ

作業工数が半分になる?!

実務で重宝するPower Queryの機能の一つが[クエリのマージ]です。マージ(Merge)とは、統合する、結合するという意味です。

この[クエリのマージ]という機能は、VLOOKUP関数のように使え、それ以上に便利です。

VLOOKUP関数の場合、二つのデータファイルを見比べながらVertical=垂直にLookup=検索するわけですが、実務を考えた場合、

  • “受注ファイル”でVLOOKUP関数を使い、“出荷ファイル”に検索をかける場合
  • 逆に、“出荷ファイル”から“受注ファイル”に検索をかける場合

両者で結果が異なることがあります。

そのため、両者それぞれに対して、VLOOKUP関数を使い検索しなければならないという“二度手間”があるわけです。

しかし[クエリのマージ]の[結合の種類]で[完全外部(両方の行すべて)]を使えば、一発で双方にVLOOKUP関数を使った検索結果を得ることが可能です。

これは、単純計算で、作業工数が半分になることを意味します。

かなり魅力的!

[クエリのマージ]のポイント

この[クエリのマージ]は、Power Queryエディターの[ホーム]タブの[結合]グループにあり、[結合の種類]は全部で6つあります。

[マージ]の画面の見方ですが、ちょっと特殊で、

上の箱が【左】、下の箱が【右】になります(図表1)。


図表1 マージの画面(上の箱が【左】、下の箱が【右】となることに留意)

実はここが、つまずきポイントの一つです! 必ず覚えてください。

上の箱が【左】

下の箱が【右】

「会計」でいうところの、右側が“貸方”、左側を“借方”と言うのに近いものがあると思っていただけば分かるかも知れませんね。単なる言いかえにすぎませんが、意外に重要なポイントです。

ここでは(取引番号や日付、得意先コードなど)“番号”の同じものを比較するため、“【左】番号”と“【右】番号”をクリックして[結合の種類]で6つのいずれかを選択し、[OK]をクリックします。

ちなみに、“【左】番号”は1~3、“【右】番号”は2~4というデータ内容になっていて、1は“【左】番号”だけ、2と3は両方に、4は“【右】番号”だけに存在しています。6つの[マージ]の結果は、図表2を参照してください。

①左外部(【左】の全行、【右】は一致する行だけ)
②右外部(【左】は一致する行だけ、【右】は全行)
③完全外部(【左】【右】の全行)
④内部(【左】【右】の一致する行だけ)
⑤左反(【左】にあるが【右】にない行)
⑥右反(【左】にないが【右】にある行)

図表2 結合の種類は全部で6つ

上記のように、一つの該当項目同士を比較・検索するというVLOOKUP関数のような使い方もできますし、図表3のように(アイテム)列と(取引日)列の複数列を選択して該当項目を比較・検索することもできるというようにVLOOKUP関数よりも便利に使えるのが[クエリのマージ]の特徴でもあります。

これを従来Excelで行おうとすれば、“アイテム”と“取引日”の複数列を対象に[&演算子]を使い一つのデータ項目列にまとめ、これと同じ作業を検索されるデータ側でも行う必要があるなど、かなり工数が多くて面倒でした。

しかし、上記のようにPower Queryエディターの[クエリのマージ]を使い複数の対象列を選択すれば良いだけ、これはかなり便利です。

図表3 複数列をクリックしマージすることもできる

【ワンポイント】 [マージ]画面の一番下に注目!

[マージ]画面の一番下をみると、緑色の✔マークとともに「選択は、最初の24757行からの24757と一致しています。」のように表示されます。ここを見ればマージ(結合)した結果の概略を把握できます。

今回のポイント

・[クエリのマージ]はVLOOKUP関数のように使える。

・[マージ]画面の上の箱が【左】、下の箱が【右】。これが、全部で6種類ある[結合の種類]の左外部、右外部、完全外部、内部、左反、右反の【左】【右】を指す。

・[完全外部]は双方のテーブルからVLOOKUP関数で検索をかけるイメージで使えて実務で重宝する。

モダンExcel研究所

フォローお待ちしてます!

2件のピンバック

コメントは現在停止中です。

error: Content is protected !!