☰

Excel INDEX MATCH 複数列のチェック

tag-icon • tag-icon microsoft-excel vlookup array
Excel INDEX MATCH 複数列のチェック

私が本質的に解決しようとしている問題は、列 A:E の値をチェックし、いずれかの列に見つかった場合に列 F に保持されている値を返す VLOOKUP です。

VLOOKUP では不十分なので、INDEX-MATCH 構文を調べましたが、単一の列ではなく値の配列に対してこれを完了する方法がわからず苦労しています。これを説明するために、以下のサンプル データ セットを作成しました。

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

チェック対象のセルに 1、2、3、4、または 5 が含まれている場合、数式の結果は Apple になります。12 または 13 の場合は Banana が返され、14 が含まれている場合は Carrot が返されます。

後半部分は、参照されるセルが単一の値ではなく、完全なテーブル自体であるという事実に由来します。そのため、この検索はさまざまな値に応じて何度も実行されます。

デモのために、これらの値を持つ別のテーブルが別の場所にあります (下図参照)。どの行を、つまり「Apple、Banana、Carrot」のどの値を各列に関連付けるかをシステムに識別させようとしています。テーブルは次のようになります。

こんにちは - - - - - -

1------(アップル)----

2------(アップル)----

12-----(バナナ)-

等。 - - - - - - - - -

括弧内の値は、数式でこれらの値が計算される場所です。

答え1

さまざまなケースがあります。1 つのケースを考えてみましょう。

列のどこかあを通してえ13を含むセルが1つだけある場合、列のセルの内容を返します。ふ同じ行に。

「ヘルパー」列を使用します。G1入力：

=COUNTIF(A1:E1,13)

下にコピーします。これにより行を識別できます。

ここに画像の説明を入力してください
今なら使えるマッチ()/インデックス():

セルを選択して入力します:

=INDEX(F:F,MATCH(1,G:G,0))

ここに画像の説明を入力してください

「ルール」が変更され、1 行に 13 が複数ある場合や、13 を含む行が複数ある場合は、ヘルパー列を変更します。

編集#1:

アップデートに基づいて、最初のステップはハードコードされたものをプルすることです13「ヘルパー」列の数式から取り出して独自のセルに配置します。（言うH1）. すると、1つのセルを変更するだけで、さまざまなケースを実行できます。

テーブルに多数のケースがある場合は、各ケースを設定するマクロを作成できます。（アップデートH1）結果を記録します。

答え2

私自身の調査と @Gary'sStudent との話し合いに基づいて、私が使用した解決策は、値が含まれる可能性がある各列に対して MATCH 式を作成し、空白をキャッチする "IFERROR" ステートメントを追加することでした。

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

ユーザーの混乱や操作を防ぐために、これらの列を非表示にできるようになりました。

次に、これらを 1 つの値に集計するインデックスを作成しました。この値は、問題の行と一致するはずです。ここでも、値がテーブル内に見つからない場合は、これを空白の値として入力するためのチェック (最初の SUM) があります。

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

インデックスマッチ配列 最後に、ユーザーが重複データを識別して置換/削除できるように、いくつかの条件付き書式設定式を入力しました。

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

これは、重複データを削除するようユーザーに示す単なる合図です。

ここに画像の説明を入力してください

答え3

H1 に単一の数式がある場合:

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

これは配列数式なので、参照をデータ セットのサイズに制限する必要があります。すべてこれINDEX(E:E,MATCH("ZZZ",F:F))を行います。これにより、テキストがある列 F の最後の行が返されます。次に、それを反復する最後の行として設定します。

@Gary'sStudent メソッドは配列数式を回避し、必要なメソッドである可能性があります。データセットと数式の数が増えると、計算にかかる時間も長くなります。ある時点で、Excel がクラッシュすることもあります。通常、これには数千かかりますが、警告を発したいと思います。

ここに画像の説明を入力してください

編集

配列数式の使用を避けて 1 つの数式にするには、次の手順を実行します。

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

これは OP の回答に基づいており、その方法を 1 つの式に組み合わせただけです。

この数式は重複するエントリを無視し、数字が見つかった最初の行を返します。

また、配列ではないため、完全な列参照は計算時間に影響を与えません。

![ここに画像の説明を入力してください

答え4

別の方法としては、補助テーブルをベースにする方法があります。補助テーブルは、そもそもこれがどのように構造化されるべきかを表します。これにより、デバッグや後からの変更が面倒なモンスター方程式を回避でき、5 つの参照列を持つというアイデアとは異なり、さまざまな数の列をきれいに解決できます。

上記がSheet1にある場合は、Sheet2を追加します。そこに行、列、ID、名前の4つの列を配置します。

の数式はRow次のようになります（疑似コードでは、「Last」は「sheet2 の上の行」を意味します）。

=IF(Column = 1, Last row + 1 , Last row)

式Column:

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

IDおよびの式Name:

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

次に、これを下方向に入力します (基本的には、row元のテーブルの行数を超えるまで)。

最後に、通常の vlookup または index/match を使用して新しいテーブルを使用します。

利点: 数式がはるかにシンプルで、使いやすく理解しやすい。

短所: 追加のテーブルが必要で、テーブルの長さを維持する必要があります。パフォーマンスの面では、値の「文字列」全体に対して単一のスレッドが必要になるため、リスクがあります。

また、いくつかのエラー行が問題ない場合、コードは多少単純化され、パフォーマンスが向上する可能性があり、行と列の両方を指定して、列の数は常に 5 であると想定できます。

関連情報