今回はIndexとMax関数で降順検索をする方法です。
Index関数でデータを検索と言ったらMatch関数ですが、今回は使用しません。
その代わり、Max関数とRow関数を使用します。
厳密にいうと「降順検索」ではないのですが、検索値が重複している情報があった場合、最も下にある情報を抽出する方法です。
今回は２つパターンを用意しました。
- 通常の降順検索パターン
- 複数条件の降順検索パターン
両方とも解説していきますので、ご覧ください。
通常の降順検索パターン
オーストラリア人はこの表の中では５人います。その中で最後に登録している人はソフィアになります。
式はこちらになります。
=IFNA(INDEX(A1:E16,IF(MAX((D1:D16=”オーストラリア”)
ROW(B1:B16))=0,NA(),MAX((D1:D16=”オーストラリア”)ROW(D1:D16))),2),”該当しない”)
配列になりますので、Ctrl＋Shift＋Enterで確定するのをお忘れずに
式の解説
重要なポイントは以下の３つです。
- 検索値に該当する箇所を配列にし、特定する
- Row関数で行数を特定
- Max関数で最大値を抽出（これによって降順が実現）
ポイント１：検索値に該当する箇所を配列にし、特定する
Max（(D1:D16=”オーストラリア”)この部分は配列になっています。
配列にすることで、出身国（D列）の１～16行目までをそれぞれ、「オーストラリア」かどうかを判別しています。下図の枠線内がTrue/Falseで表記されており、Trueの箇所が「オーストラリア」です。
ポイント２：Row関数で行数を特定
行数を特定する必要がありますので、True/Falseになっている部分を数値化させます。
そこでRow関数を使用します。こちらも同じように配列にします。
（Trueには行数が入り、Falseは０になります。）
ポイント３：Max関数で最大値を抽出（これによって降順が実現）
Max関数で最大値を算出します。
この表の場合、15が最大値になります。
これにより、１５行目の２列目のソフィアが抽出されるという仕組みです。
IFNAとIF関数は抽出したいデータがない時の、エラー対策です。
複数条件の降順検索パターン
オーストラリア人かつ車が趣味で１番最後に登録した人は１人しかいません。（複数用意しておけばよかったです）
式の仕組みとしては、一緒ですので複数条件の方法をご説明いたします。
式はこのような式になります。
=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で確定するのをお忘れずに
式の解説
複数条件にする際のポイントは２つです。
- 条件分、Max関数内に記載する
- And関数は使用できないので「＊」でAnd関数と同じ事をする
ポイント１：条件分、Max関数内に記載する
趣味である車の条件を図のように追加します。
ポイント２：And関数は使用できないので「＊」でAnd関数と同じ事をする
And関数は配列の時には使用することができないため、「＊」（アスタリスク）を使用します。
（条件１：出身国）と（条件２：趣味）をアスタリスクでつなげます。
そのあとは、通常の降順パターンと同じです。
動画
動画も取りましたので、合わせてご参考ください。
まとめ
いかがでしたでしょうか。Index関数は単体ではほとんど使わないのですが、関数を組み合わせて使う事で爆発的な力を発揮します。Vlookup関数ではできなかったことが、色々とできますのでExcelの幅が本当に広がりますよ。
コメント