☰

Microsoft Excel で Index and Match または同様の機能を使用して、同じ列にある 2 セットの数字の最後に入力された数字行を見つけることは可能ですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vlookup
Microsoft Excel で Index and Match または同様の機能を使用して、同じ列にある 2 セットの数字の最後に入力された数字行を見つけることは可能ですか?

サンプルテーブル画像:

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

目的: サンプル テーブル イメージで、列 C から、最後に入力された CST 番号 1235 と最後に入力された Vat 番号 204 を抽出します。

=VLOOKUP("CST",B2:C5,2,FALSE) を使用すると、最初の CST 値、つまり 1234 が返されます。同様に、VAT の場合、VLOOKUP を使用すると 203 が返されます。

Match を試すと、CST では正常に機能しますが、VAT の場合は、lookup_value が 250 などの小さい値 (VAT 値が 250 未満であるため) を使用した同じ数式では、結果として #N/A が返されます。

スクリーンショット (同じ列で異なる lookup_value を持つ MATCH を使用):

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

答え1

このような場合には、MATCH よりも AGGRAGATE を好みます。

=INDEX($B:$B,AGGREGATE(14,6,ROW($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))=D$1),1))

実際の基準を D1 と D2 に配置して、それらを直接参照できるようにし、数式にハードコードし"CST"てドラッグ可能にしました。"VAT"

2 つは$A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))配列型の数式なので、参照範囲を動的に設定します。A2 から、テキスト文字列がある列 A の最後のセルまでが設定されます。

Aggregate は、INDEX の条件に一致する最大の行番号 (最後の行) を返します。

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

答え2

でこれを行うことができますLOOKUP。

LOOKUPlookup_valueが配列内のどの数値よりも大きい 場合、関数は配列内の最後の数値を返します。この1/(1/(...))構造は をエラーに変換する0ため、最後の「数値」は最後の数値と同じ位置にある値、CSTまたはVAT数式に応じて異なります。

Last CST:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="CST")*$C$2:$C$999)))
Last VAT:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="VAT")*$C$2:$C$999)))

上記の数式では、請求書番号が常に数値であると想定していますが、文字列である場合は、数式を修正する必要があります。

Last CST:  =LOOKUP(2,1/(($B$2:$B$999="CST")*ROW($B$2:$B$999)),$C$2:$C$999)
Last VAT:  =LOOKUP(2,1/(($B$2:$B$999="VAT")*ROW($B$2:$B$999)),$C$2:$C$999)

答え3

垂直ルックアップには VLOOKUP を使用します。2 番目の引数はマトリックスです。行数に制限がない場合は、行番号を指定せず、列の文字のみを指定します。3 番目の引数は、使用する列を指定します。2 は 2 番目、つまりマトリックスの C を意味します。

デフォルトでは、関数は最初の検索で停止せず、さらに出現箇所を検索し、最終的には、希望どおりに一番下のエントリ (通常は最新のエントリ) を検索します。

したがって使用

=VLOOKUP("CST", B:C, 2)

そして

=VLOOKUP("VAT", B:C, 2)

水平検索用の HLOOKUP もあることに注意してください。

答え4

その日、私はいわばテーブルをひっくり返して、VLOOKUP()可能な場合は を使用し、そうINDEX/MATCHでない場合は を使用しました。重要な部分はテーブルをひっくり返すことであり、以下はその方法を示しています。それを、検索したいものを検索するためのテーブルとして使用し、最初の出現が見つかったら、元の (「実際の」) テーブルにある「実際の」最後の出現を見つけます。

したがって、値のテーブルは A1:B22 です。

=INDEX(A1:B22,  ROWS(A1:A22)+1-ROW(INDIRECT("1:"&ROWS(A1:A22))),  {1,2})

基本的に、行数 (22) を見つけ、1 (23) を加算し、その値 (23) から 1 から行数 (22) までの一連の値を引いた値の配列を作成し、返される行の配列を生成します。23-1=22、23-2=21、23-3=20 など、つまり 22、21、20 と 1 まで続きます。したがって、INDEX()行は逆の順序で返されます。

XLOOKUP()現在（2022年）では、 を使って最後から最初に向かって検索するだけです。確かに、上記を実行する場合は、 を使ってSEQUENCE()数列を生成することになります。

関連情報