Index関数で指定したデータの降順検索の方法(新しい情報の取得)重複、複数検索対応

未分類

今回はIndexとMax関数で降順検索をする方法です。
Index関数でデータを検索と言ったらMatch関数ですが、今回は使用しません。
その代わり、Max関数とRow関数を使用します。

厳密にいうと「降順検索」ではないのですが、検索値が重複している情報があった場合、最も下にある情報を抽出する方法です。

今回は2つパターンを用意しました。

  • 通常の降順検索パターン
  • 複数条件の降順検索パターン
結果画像

両方とも解説していきますので、ご覧ください。

通常の降順検索パターン

通常の降順検索パターン画像

オーストラリア人はこの表の中では5人います。その中で最後に登録している人はソフィアになります。

式はこちらになります。

=IFNA(INDEX(A1:E16,IF(MAX((D1:D16=”オーストラリア”)
ROW(B1:B16))=0,NA(),MAX((D1:D16=”オーストラリア”)ROW(D1:D16))),2),”該当しない”)

配列になりますので、Ctrl+Shift+Enterで確定するのをお忘れずに

式の解説

重要なポイントは以下の3つです。

  • 検索値に該当する箇所を配列にし、特定する
  • Row関数で行数を特定
  • Max関数で最大値を抽出(これによって降順が実現)

ポイント1:検索値に該当する箇所を配列にし、特定する

Max((D1:D16=”オーストラリア”)この部分は配列になっています。
配列にすることで、出身国(D列)の1~16行目までをそれぞれ、「オーストラリア」かどうかを判別しています。下図の枠線内がTrue/Falseで表記されており、Trueの箇所が「オーストラリア」です。

検索値に該当する箇所を配列にし、特定する画像

ポイント2:Row関数で行数を特定

行数を特定する必要がありますので、True/Falseになっている部分を数値化させます。
そこでRow関数を使用します。こちらも同じように配列にします。
(Trueには行数が入り、Falseは0になります。)

Row関数で行数を特定画像

ポイント3:Max関数で最大値を抽出(これによって降順が実現)

Max関数で最大値を算出します。
この表の場合、15が最大値になります。

Max関数で最大値を抽出(これによって降順が実現)画像

これにより、15行目の2列目のソフィアが抽出されるという仕組みです。

行番号取得イメージ

IFNAとIF関数は抽出したいデータがない時の、エラー対策です。

複数条件の降順検索パターン

複数条件の降順検索パターン画像

オーストラリア人かつ車が趣味で1番最後に登録した人は1人しかいません。(複数用意しておけばよかったです)
式の仕組みとしては、一緒ですので複数条件の方法をご説明いたします。

式はこのような式になります。

=IFNA(INDEX(A1:E16,IF(MAX((D1:D16=”オーストラリア”)(E1:E16=”車”)ROW(B1:B16))=0,NA(),MAX((D1:D16=”オーストラリア”)(E1:E16=”車”)ROW(B1:B16))),2),”該当しない”)

配列になりますので、Ctrl+Shift+Enterで確定するのをお忘れずに

式の解説

複数条件にする際のポイントは2つです。

  • 条件分、Max関数内に記載する
  • And関数は使用できないので「*」でAnd関数と同じ事をする

ポイント1:条件分、Max関数内に記載する

趣味である車の条件を図のように追加します。

条件分、Max関数内に記載する画像

ポイント2:And関数は使用できないので「*」でAnd関数と同じ事をする

And関数は配列の時には使用することができないため、「*」(アスタリスク)を使用します。
(条件1:出身国)と(条件2:趣味)をアスタリスクでつなげます。

And関数は使用できないので「*」でAnd関数と同じ事をする画像

そのあとは、通常の降順パターンと同じです。

動画

動画も取りましたので、合わせてご参考ください。

まとめ

いかがでしたでしょうか。Index関数は単体ではほとんど使わないのですが、関数を組み合わせて使う事で爆発的な力を発揮します。Vlookup関数ではできなかったことが、色々とできますのでExcelの幅が本当に広がりますよ。

コメント