☰

2つのテーブルを2つのキーで結合する

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
2つのテーブルを2つのキーで結合する

私は Microsoft Excel 2016 を使用しています。そして、異なるシートに 2 つの表があります。

sheet1.tbl1 {
key1,
key2,
val1,
fun1,
fun2
}

sheet2.tbl2 {
key1,
key2,
val1,
val2
}

数式または他の方法を使用して、値を from に取り込みたいです。具体的には、それぞれ join bysheet1.tbl1とBring from to fromです。キーは両方のテーブルで一意であるため、最初に一致したもので十分です。sheet2.tbl2key1, key2val1, val2sheet2.tbl2fun1, fun2sheet1.tbl1

私はフォーミュラを試しました

=INDEX(rls, MATCH(tbl1[[key1]:[key2]], tbl2[key1]:[key2]], 0),3)

しかし、それは機能しません。

また、拡張機能は使用せず、組み込みの機能を使用して実現したいと考えています。できれば、テーブル列構文 ( tbl1[[key1]:[key2]]) を使用します。

答え1

VLOOKUP()難しいのは、2 番目の表のキーの組み合わせです。たとえば、使いにくくなります。ただし、INDEX/MATCHまたはについては特に問題はありませんXLOOKUP()。

例えば：

=XLOOKUP($A1:$A9&"|"&$B1:$B9,  $H$20:$H$28&"|"&$I$20:$I$28,  J20:J28,,0)

ユニオン演算子 を使用してキーを結合し&、 のような既存のデータ文字に予期しない文字が存在しないように区切ります|。(文字を選択してから、キー内でその文字を検索し、未使用であることを確認できます。) これは、予期しない重複が発生しないようにするためです。例: 1 つのキー ペアは と ですAddが、ressもう 1 つはAdと ですdress。間に文字があると、Add|ressのAd|dressインスタンスが 2 つではなく、 と になりますAddress。

XLOOKUP()およびMATCH()(それを実行する の半分)の場合、結合は簡単ですINDEX/MATCH。どちらも、それらの部分の配列を使用して簡単に処理できるため、単一の数式を作成できます。

現在のバージョンでは、SPILL問題なく動作します。古いバージョン (2016 のタグが付けられているもの) では動作しないため、{CSE}必要に応じていずれかのエントリを使用するか、列をコピーして貼り付ける必要があります。

新しいバージョンでは、XLOOKUP()シンプルでわかりやすい（メンテナンスに便利な）式になっています。古いバージョンでは、INDEX/MATCH犠牲になるものはほとんどありません。

=INDEX($J$20:$J$28,  MATCH($A1:$A9&"|"&$B1:$B9,  $H$20:$H$28&"|"&$I$20:$I$28,,0))

両方を処理する必要がある場合は、これを使用します。

どちらの数式も 2 列のターゲット範囲は好ましくないため、(これらの数式では) J20:K28 は使用できません。

（しかし当然、誰かがそれを利用可能にする良い方法を知っているかもしれません！）

便利な方法がありますFILTER/FILTERが、Excel 2016 ユーザーには使用できないため、現時点では役に立ちません。ただし、将来のワークブックでは役立つテクニックになる可能性があります。

=FILTER(FILTER(H20:K28,SORT(A1:A9&"|"&B1:B9)=SORT(H20:H28&"|"&I20:I28)),{0,0,1,1})

内部FILTER()でデータを選択します。もちろん、キーのリストはほぼ確実に同じ順序ではありません (または、"=F12、=F13、=F14 などでもほぼ機能します)。したがって、SORT()各キー結合配列で を使用して、キーを同じ順序にします。2 番目のテーブルに最初のテーブルにないキー ペアがある場合は、それらを削除するか、別のパスを選択する必要があります。

外側は、配列定数を使用して出力する列 (または行) を選択する場合とFILTERほぼ同じように機能します (代わりに を使用することもできますが、それほど簡単ではありません)。は、単純な配列定数を「列を表示する/列を表示しない」機能として使用します。したがって、出力したい 2 つの列のみが出力されます。INDEX()INDEX()FILTER()

これには、 -ingを含む多次元出力を提供するという利点があるためSPILL、1 つのセルに 1 つの数式を入力するだけで、出力全体が得られます。

2016 ユーザーが持っているものを使用することもできますFILTERXML()が、これは非常に巧妙な使用法ですが (特に、このメソッドは単一の XPATH で両方の列の配列を構築するため) usually、この場合、非常に基本的な低レベルの数値計算演習に過ぎません。不格好...「最大限に。」そして、内部配列の文字列の長さに関する問題が発生する可能性があります。

これは、ヘルパー列ルート (結合キー用) や、結合キーを名前付き範囲に入れて簡単にアドレス指定できるようにする類似のルートについては言うまでもありません。

INDEX/MATCHさまざまなユーザーの組み合わせに適した方法をお勧めします。

関連情報