Power Query 結合

1.Table.NestedJoin

　Excelで2つのテーブルを結合する時、ほとんどの人はVLOOKUPを使用すると思います。参照するテーブルが小さい場合は問題がありません。しかし、2つのテーブルが非常に大きい時、VLOOKUP関数がエクセルをクラッシュさせる原因となることがあります。

　参照元と参照先のテーブルが1対1の関係になっており、参照先のデータが変更されない場合、VLOOKUPの様なダイナミック参照ではなく、静的参照を行うほうが効果的です。

　例えば、給与テーブルに人事テーブルを参照して4月1日時点の年齢を列追加したい時、2つのテーブルは1対1になっており、4月1日時点年齢は作業中に変化はないと思います。この場合、VLOOKUPではなく、Power Queryをを使えば、人事テーブルを作業中のエクセルに持ち込まずに別ファイルにして作業できます。

　このような給与データに人事テーブルから氏名と雇用開始日を持ってきます。

　最期に列の品質、列の分布できちんと結合できているか確認しましょう。

構文

Table.NestedJoin構文

Table.NestedJoin(
    table1 as table, 
    key1 as any, 
    table2 as any, 
    key2 as any, 
    newColumnName as text, 
    optional joinKind as nullable number, 
    optional keyEqualityComparers as nullable list
) as table

JoinKind.Type

名前	値	説明
JoinKind.Inner	0	内部結合(既定)
JoinKind.LeftOuter	1	左外部結合
JoinKind.RightOuter	2	右外部結合
JoinKind.FullOuter	3	完全外部結合
JoinKind.LeftAnti	4	左反結合
JoinKind.RightAnti	5	右反結合

Table.Joinとの違い

　Table.Joinは、以下のような構文になります。

Table.Join構文

Table.Join(
    table1 as table, 
    key1 as any, 
    table2 as table, 
    key2 as any, 
    optional joinKind as nullable number, 
    optional joinAlgorithm as nullable number, 
    optional keyEqualityComparers as nullable list
) as table

　Table.Joinは、テーブル1の後にテーブル2の内容が追加されるのに対して、Table.NestedJoinは、新しい項目にテーブル2の内容がテーブル形式で作成されます。

VLOOKUP

VLOOKUP構文

=VLOOKUP (
    lookup_value, 
    table_array, 
    col_index_num, 
    [range_lookup]
)

lookup_value : 検索値
table_array : 範囲
col_index_num : 列番号
[range_lookup] : 検索の型
- 1/TRUE : 近似一致(既定)
- 0/FALSE : 完全一致

XLOOKUP

XLOOKUP構文

=XLOOKUP(
    lookup_value, 
    lookup_array, 
    return_array, 
    [if_not_found], 
    [match_mode], 
    [search_mode]
)

lookup_value : 検索値
lookup_array : 検索範囲
return_array : 戻り配列
[if_not_found] : 見つからない場合
[match_mode] : 一致モード
- 0 : 完全一致。見つからない場合は #N/A が既定で返される(既定)
- -1 : 近似一致。完全に一致するか、次の小さなアイテムが返されます
- 1 : 近似一致。完全に一致するか、次の大きなアイテムが返されます
- 2 : * , ? 及び ~ が特別な意味を持つワイルドカードの一致
[search_mode] : 検索モード
- 1 : 先頭の項目から検索を実行します(既定)
- -1 : 末尾の項目から逆に検索を実行します
- 2 : 昇順で並べられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていな場合、無効な結果が返されます。
- -2 : 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並び替えられていない場合、無効な結果が返されます。

　XLOOKUPは、垂直方向または水平方向の範囲で値を検索でき、近似一致と完全一致を実行でき、部分一致のためのワイルドカード（* ?）をサポートしています。さらに、XLOOKUPは、最初の値または最後の値からデータを検索できます。

　バイナリサーチは非常に高速ですが、データは必要に応じてソートされなければなりません。データが適切にソートされていない場合、バイナリサーチは一見完全に正常に見えても、無効な結果を返すことがあります。

XLOOKUPのメリット

XLOOKUPは、検索値の左側のデータも返すことができます。
XLOOKUPのデフォルトは完全一致です。VLOOKUPで起きる、検索の型を指定し忘れることから生じるミスを防ぐことができます。
XLOOKUPは、テーブルを参照する場合、返す列を番号ではなく列名で指定できます。
XLOOKUPは、1つの値だけでなく、範囲や行または列全体を返すことができます。

ワークブック間でXLOOKUPを使用する場合、両方のワークブックが開いている必要があります。

XLOOKUP関数は、大文字と小文字を区別しません。区別するようにするには、EXACT関数を使用します。
=XLOOKUP(TRUE,EXACT([@[Employee_Name]],Salary[Name]),Salary[Salary])

雑・Excel入門試論 - 脱VLOOKUPの思考 17 - Power Query - テーブルの結合