Power Query 第6回目です。今回から「クエリのマージ」について勉強していきます。
まずはじめは、マージクエリの概要を把握しましょう。
「クエリのマージ」についての初回は概要の把握です。
きちんと概要を知っておかないと、今後の作業効率にも大きく影響します。マージには沢山の結合種類があるので、しっかり勉強しましょう。
はい、わかりました。よろしくお願いしますm(__)m
【この記事でわかることは】
・マージクエリの概要について
・基本的な「クエリのマージ」の使い方と結合の種類
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
・前回の内容は「フォルダーから」ヘルパークエリなしで結合する方法でした。
記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
・マージクエリの概要
なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替をお願いします(^^)/
マージ（merge）とは
マージ（merge）とは、「結合する」「統合する」「融合する」「併合する」「合併する」などの意味を持つ英単語（動詞）です。複数のファイルやデータなどを、決められたルールに従って一つに統合することをマージ（merge）といいます。
簡単な例としては「店舗データ」と「販売データ」をマージする。とか「日次データ」をマージして「月次データ」にマージしたり「年次データ」にマージするというようなことです。
「店舗データと販売データのマージする」は、店舗データと販売データを紐付けるする作業です。
「日次データ」を「月次データ」にするマージは、前回までの記事でファイルのデータを「結合」した処理のことです。データを縦に結合するのもマージ処理ですが、マージとは言っていませんでした。
Power Query エディターのリボンでも「Fileの結合」は単独で設置されているということもあるのでしょうね。
「クエリのマージ」
Power Query エディターの[ホーム] タブ→ [結合] グループ→[クエリのマージ] があります。 ドロップダウンメニューには、次の2つのオプションが表示されます。
[クエリのマージ]を選択した場合、マージ操作の左側のテーブルとして選択したクエリを含む [マージ] ダイアログボックスが表示されます。左側テーブルのクエリは変更できません！左側のテーブルクエリにマージクエリを追加します。
[新規としてクエリをマージ]を選択した場合、同じように[マージ] ダイアログボックスを表示しますが、左側テーブルのクエリは自由に変更できます！マージ後に新規クエリを作成します。
「マージ」ダイアログボックス
・[クエリのマージ]で表示されるダイアログボックス（左側画像）
・[新規としてクエリをマージ]で表示されるダイアログボックス（右側画像）
「マージ」対象のテーブル選択・配置
・「マージ」ダイアログボックス画像（全体）がこちらです。
・左右のクエリテーブル選択後「照合列」をそれぞれ選択します。
・不親切なのは、どちらが左側なのかの表示がないことです。
・下図のMS-Docsの画像リンク（英語版）ではちゃんと表示されているんですよね(^^;
・上側が「左側」のクエリテーブルです。
・下側が「右側」のクエリテーブルです。
・左右のクエリテーブル選択後「照合列」をそれぞれ選択します。
列のペアの選択
左テーブルと右テーブルの両方を選択した後、テーブル間の結合を行う列を選択します。
左テーブル（上側）：販売データ
右テーブル（下側）：店舗マスタ
目的とするのは「販売データ」テーブルの[店舗コード]列を使用して「店舗マスタ」から[店舗名]を結合させることです。そのため、両テーブルの[店舗コード]列を選択します。
選択すると、ダイアログボックスの下部に選択範囲の一致（推定）数メッセージが表示されます。
複数の列を選択して結合を実行する
複数の列を選択するには、Ctrl キーを押しながら列を選択します。
列が選択された順序が、列見出しの横に1から順に小さな数値が表示されます。
マージされたテーブル列の展開（または集計）
[マージ] ダイアログボックス設定後、[OK] 選択でクエリのベーステーブルには左テーブルのすべての列が表示されます。そして、新しい列が「右テーブルの名前」で追加されます。
追加された「右テーブルの名前」列には、右テーブルに対応する値が行単位で格納されています。
この新しいテーブル列のヘッダーに表示されている「展開アイコン」をクリックするとダイアログが表示されます。表示したい列を選択し、フィールドを展開するか集計するかを選択できます。[OK]選択で右テーブルのフィールド列が表示されます。
[集計]オプションを選択できますが、右画像のとおり文字列は「カウント」「数値」は合計という選択肢となっています。
詳しくはこの先で勉強していきますが、どうやら「カウント」は Excel の CountIf(s)、
「合計」が Excel の SumIf(s) のような機能のようです。
結合の種類
結合の種類によって、マージ操作の実行方法を指定できます。
ここを間違えると、狙った結果を得ることができません。結果を確認してみて、もし違っているようならやり直せばいいんです。
プルダウンで表示される選択肢がこちらです。
めちゃくちゃわかりにくい表示ですよね(-_-;)
「最初」⇒「左側テーブル」、「2番目」⇒「右側テーブル」、「反」⇒「アンチ」
少し書き換えてみました。多少わかりやすくなったかなぁ(^^;
左外部結合（左側テーブルの行のすべて、右側テーブルのうち一致する行）
右外部結合（右側テーブルの行のすべて、左側テーブルのうち一致する行）
※それぞれの詳しい解説は別途解説するようにしたいと思います。
※準備ができたものはリンクを設定しますのでリンク先へからジャンプできます。
あいまい一致
ここまでのクエリのマージについては、通常は「完全一致」アルゴリズムでの動作です。
完全一致は、比較するキーとなる列の値が、文字数や大文字小文字など全てが完全に一致しなければ同一の値として扱われません。
列を比較するときに「あいまい一致」アルゴリズムを適用するには、[マージ] ダイアログボックスの [あいまい一致を使用してマージを実行する] チェックボックスをオンにします。 [あいまい一致オプション] を展開して、使用可能なすべての構成を表示します。
あいまい一致オプション
利用できるオプションはつぎのとおりです。
類似性のしきい値(省略可能)
・二つの列の値が一致として見なされるために必要な類似度を示す値を設定します。
| 規定値 | 最大値 | 最小値 | |
|---|---|---|---|
| 値 | 0.80 | 1.00 | 0.00 |
| 動作 | 0.90未満で文字数の違いによる 類似を結果に含む度合いを指定 | 完全一致と同じ | 無条件で全てが一致 |
※しきい値「0.00」で CROSS JOIN できるのかと思って試してみましたがうまくいきませんでした。やり方が間違っているのか、何か条件があるのか不明の状態です。CROSS JOIN「クロス結合」については別途勉強してみたいと思います。
※CROSS JOIN とは、両方のテーブルの全データを取り出して、すべての組み合わせでくっつける動きのことです。（作成行数 ＝ 左テーブル行数 × 右テーブル行数）
大文字と小文字を区別しない
有効にすると、例えば「PowerQuery」と「powerquery」を一致させることができます。
テキストの一部を結合して一致させます
有効にすると、例えば「Power Query」と「PowerQuery」を一致として返します。
（空白を無視するのと同じですね）
一致の最大数(省略可能)
入力行ごとに返される一致行の最大数を制御できます。
例えば、入力行として一致行を１行だけ検索する場合は、値1を指定します。
変換テーブル(省略可能)
一致プロセスで値のマップに使用する「変換テーブル」を指定できます。
「変換テーブル」の設定を使って一致させます。
変換テーブルとは
変換テーブルは、[From]列と[To]列で作られたテーブルです。
[From] が変換前の値、[To] が返還後の値です。
この変換テーブルを読み込ませることで、特定の値にカスタム値をマッピングします。
事前に変換テーブルを用意することで、略語や類似語などを特定の語句に関連付けることが出来るようになります。
右の画像は、その一例です。
元データの正確性をあらかじめ認識したうえで既知として使用するケースはあるかもしれません。
あいまい一致は便利な部分もありますが、使用する場合は一定のルールが必要でしょう。
また、Power Query と Excel とでは一致の条件が違うようなので注意しましょう。
まとめ（おわりに）
・以上で、「マージクエリの概略」の勉強は終了です。
・今回はあくまでも「概略」の部分でしたから、個々の細かい使用方法などについては改めて勉強していきましょう。
・今回も記事内で使ったクエリの元データは、前回記事と同じサンプルファイル（CSV）を使っています！
・是非サンプルを使って実際にファイルの結合を試してみてください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
「クエリのマージ」について概略を勉強しました。なんとなくわかっていただけたらOKです！ 次回以降で「個々の使用方法」について順番に勉強を継続していきますね。 次回は、「左外部結合」ついて詳しく勉強しましょう(^^)/
概略なので気が楽でした(^^;
でも、種類がいっぱいあるので覚えるの大変だなと思いました。あと「あいまい」の部分はすごく難しそうでした。
この先もついていけるように頑張ります(^^)
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回記事で使用したサンプルファイルは前回記事と同じものです！
過去の記事で使用したサンプルファイルもダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
【今回わかったことは】
・マージクエリの概要がわかりました
・基本的な「クエリのマージ」の使い方と結合の種類がわかりました