VLOOKUP で最後の一致を返すにはどうすればよいでしょうか?

Question 1

配列数式を使用して、最後に一致するレコードからデータを取得できます。

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Ctrl+ Shift+を使用して数式を入力しますEnter。

これはのINDEX/構文と同様に機能しますが、の代わりに条件文が使用されます。MATCHVLOOKUPMAXMATCH

これは、テーブルが行 1 から始まることを前提としていることに注意してください。データが別の行から始まる場合は、ROW(...)一番上の行と 1 の差を減算して、その部分を調整する必要があります。

Answer

配列数式を使用して、最後に一致するレコードからデータを取得できます。

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Ctrl+ Shift+を使用して数式を入力しますEnter。

これはのINDEX/構文と同様に機能しますが、の代わりに条件文が使用されます。MATCHVLOOKUPMAXMATCH

これは、テーブルが行 1 から始まることを前提としていることに注意してください。データが別の行から始まる場合は、ROW(...)一番上の行と 1 の差を減算して、その部分を調整する必要があります。

Question 2

(ソートされたデータについては別の質問がないので、ここで回答します。)

データがだった並べ替える場合は、引数(またはデフォルトなので省略)VLOOKUPを使用できます。これは、Excel では正式には「近似一致の検索」と説明されています。range_lookupTRUE

つまり、ソートされたデータの場合:

最後のFALSE引数をに設定すると、初め価値、そして
最後のTRUE引数をに設定すると、最後価値。

これはほとんど文書化されておらず、あまり知られていないが、VisiCalc (1979) に遡り、今日では少なくとも Microsoft Excel、LibreOffice Calc、Google Sheets に当てはまる。これは結局のところ、LOOKUPVisiCalc のの最初の実装 (およびVLOOKUPとHLOOKUP) に起因しており、4 番目のパラメータはなかった。値は次のように求められる。二分探索包括的左境界と排他的右境界（一般的でエレガントな実装）を使用すると、この動作が発生します。

技術的には、これは候補区間から検索を開始することを意味します。ここでは配列の長さであり、ループ不変条件は[0, n)(左境界はターゲット <= であり、右境界は末尾から 1 つ後から始まり、ターゲット > です。検証するには、前のエンドポイントの値をチェックするか、後の結果をチェックします)、次にを二分して、この不変条件を維持する側を選択します。排他的に、1 つの項を持つ区間に達するまで、一方の側はになり、アルゴリズムはを返します。これは完全に一致する必要はありません (!)。下から最も近い一致です。重複した一致の場合、これはを返します。nA[imin] <= key && key < A[imax][k, k+1)k最後一致する必要があるため、次の値はより大きいキー（または配列の末尾）よりも大きい。重複する場合はいくつかの動作は合理的であり、実装も簡単です。

この動作は、この古い Microsoft Knowledge Base の記事 (強調追加) に明示的に記載されています: 「XL: 配列内の最初または最後の一致を返す方法」(Q214069):

LOOKUP()関数を使用すると、ソートされたデータの配列内の値を検索し、別の配列内のその位置に含まれる対応する値を返すことができます。検索値が配列内で繰り返される場合、最後に見つかった一致を返しますこの動作は、VLOOKUP()、HLOOKUP()、および LOOKUP() 関数に当てはまります。

いくつかのスプレッドシートの公式ドキュメントは次のとおりです。どちらにも「最後の一致」の動作は記載されていませんが、Google スプレッドシートのドキュメントには暗示されています。

マイクロソフトエクセル

真実テーブルの最初の列が数字順またはアルファベット順にソートされていると仮定し、最も近い値。
Google スプレッドシート:

is_sortedまたは省略された場合TRUE、最も近い一致（以下検索キーに一致する文字列が返される

Answer

(ソートされたデータについては別の質問がないので、ここで回答します。)

データがだった並べ替える場合は、引数(またはデフォルトなので省略)VLOOKUPを使用できます。これは、Excel では正式には「近似一致の検索」と説明されています。range_lookupTRUE

つまり、ソートされたデータの場合:

最後のFALSE引数をに設定すると、初め価値、そして
最後のTRUE引数をに設定すると、最後価値。

これはほとんど文書化されておらず、あまり知られていないが、VisiCalc (1979) に遡り、今日では少なくとも Microsoft Excel、LibreOffice Calc、Google Sheets に当てはまる。これは結局のところ、LOOKUPVisiCalc のの最初の実装 (およびVLOOKUPとHLOOKUP) に起因しており、4 番目のパラメータはなかった。値は次のように求められる。二分探索包括的左境界と排他的右境界（一般的でエレガントな実装）を使用すると、この動作が発生します。

技術的には、これは候補区間から検索を開始することを意味します。ここでは配列の長さであり、ループ不変条件は[0, n)(左境界はターゲット <= であり、右境界は末尾から 1 つ後から始まり、ターゲット > です。検証するには、前のエンドポイントの値をチェックするか、後の結果をチェックします)、次にを二分して、この不変条件を維持する側を選択します。排他的に、1 つの項を持つ区間に達するまで、一方の側はになり、アルゴリズムはを返します。これは完全に一致する必要はありません (!)。下から最も近い一致です。重複した一致の場合、これはを返します。nA[imin] <= key && key < A[imax][k, k+1)k最後一致する必要があるため、次の値はより大きいキー（または配列の末尾）よりも大きい。重複する場合はいくつかの動作は合理的であり、実装も簡単です。

この動作は、この古い Microsoft Knowledge Base の記事 (強調追加) に明示的に記載されています: 「XL: 配列内の最初または最後の一致を返す方法」(Q214069):

LOOKUP()関数を使用すると、ソートされたデータの配列内の値を検索し、別の配列内のその位置に含まれる対応する値を返すことができます。検索値が配列内で繰り返される場合、最後に見つかった一致を返しますこの動作は、VLOOKUP()、HLOOKUP()、および LOOKUP() 関数に当てはまります。

いくつかのスプレッドシートの公式ドキュメントは次のとおりです。どちらにも「最後の一致」の動作は記載されていませんが、Google スプレッドシートのドキュメントには暗示されています。

マイクロソフトエクセル

真実テーブルの最初の列が数字順またはアルファベット順にソートされていると仮定し、最も近い値。
Google スプレッドシート:

is_sortedまたは省略された場合TRUE、最も近い一致（以下検索キーに一致する文字列が返される

Question 3

検索配列内の値が連続している場合 (つまり、最新の日付などの最大値を探している場合)、INDIRECT 関数を使用する必要さえありません。次の簡単なコードを試してください。

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

もう一度、CTRL + SHIFT + ENTERを使用して数式を入力します。

Answer

検索配列内の値が連続している場合 (つまり、最新の日付などの最大値を探している場合)、INDIRECT 関数を使用する必要さえありません。次の簡単なコードを試してください。

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

もう一度、CTRL + SHIFT + ENTERを使用して数式を入力します。

Question 4

最も頻繁な値を試してみました。LibreOfficeで機能するかどうかはわかりませんが、Excelでは機能するようです。

=INDEX($B$2:$B$9,MATCH(MAX(--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2)),--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2),0))

列 A は受取人、列 B はカテゴリ、D2 はフィルタリングする受取人です。上記の関数で余分な改行が挿入される理由がわかりません。

最後のセルを見つける関数は次のようになります。

=INDIRECT("B" & MAX(--($A$2:$A$9=D2)*ROW($A$2:$A$9)))

Indirect を使用すると、返したい列を指定して行を直接見つけることができます (そのため、ヘッダー行の数を減算する必要はありません)。

これらの関数は両方とも、Ctrl+Shift+Enter キー

Answer