Excel テーブルが 2 つあります。テーブル A とテーブル B です。テーブル A には、顧客 ID、郵便番号、顧客名などの列が含まれており、これらの列はテーブル B の列と同じ列名になっています。テーブル B の列の順序が関係ないように、できれば構造化参照を使用して、テーブル A の現在行の顧客 ID と現在いる列の列値に一致するテーブル B の値を検索する数式を作成したいと考えています。
たとえば、数式がテーブル A の 3 番目の列にあり、顧客 ID が「123」の行にある場合、数式自体の列名 (顧客名) をチェックし、テーブル B で顧客 ID が「123」である顧客名の値を検索します。
次の数式は、顧客名列に対して適切に機能します。
=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))
しかし、[顧客名] 部分を現在の列の名前に動的に置き換える単一の数式を作成して、それをすべての列にコピーできるようにしたいと考えています。#Headers を使用して参照を作成し、間接的に使用しようとしましたが、Ref エラーが発生します。
=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")
答え1
INDEX MATCH正しいアプローチですが、それをどのように構成するかに注意する必要があります。
左側に表 A、右側に表 B があります。[顧客番号] を使用して [郵便番号] を検索します。
書き込む式は次のとおりですD2:
=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))
INDEXは、ご存知のとおり、行と列の交差点にあるセルの値を返します。MATCH配列内の値の相対位置を返します。
したがって、 への 2 つの入力については、まず、検索する垂直配列を入力して、INDEX使用している検索値 (顧客番号) に一致するソース テーブルの行番号 (これは の従来の前半です)を見つけます。次に、ソース列のヘッダー行を含む水平配列を入力して、現在の列の名前に一致する列番号を見つけます。INDEX MATCHMATCHMATCH
ソース テーブルにフィールドを追加し、数式テーブルのヘッダーを変更すると、数式を変更せずに新しい結果が得られることがわかります。
そして、この数式は横方向だけでなく下方向にもコピーされます。
ここでの 2 つの鍵は次のとおりです。
- MATCH は横方向だけでなく縦方向にもカウントされることを知っています。
- Excel が提供する構造化参照を実際の R1C1 スタイルのセル参照に手動で変更して、そのセル参照の列インデックスを固定ではなく動的にすることができます (つまり、 ではなく C$1 です。
TableA[[#Headers],[Post Code]]これは、現在いるフィールドの値をハードコードするため、コピーされませんが、参照式を含む列の名前を手動で変更すると機能します。また、宛先テーブルには参照フィールドが 1 つだけ必要です)。
注意この質問は 3 年以上前のものですが、良い質問であり、このINDEX MATCH技術の汎用性を示す素晴らしい例です。