エクセルのIFS関数が長くなりすぎ問題 ～XLOOKUP関数で評価ランクをスッキリ整理

XLOOKUP関数は評価ランクの判定にも使える

　売上金額や点数に応じて、○万円以上なら「A」や○点以上なら「S」といった判定を行うことがありますよね。営業成績に応じてランクを付けたり、購入金額によって送料を変更したりするケースも同様です。

　このような処理では、IF関数やIFS関数を使うことが一般的です。特にIFS関数を使えば、複数の条件を上から順に判定できるため、段階的な評価を分かりやすく記述できます。

　「D2>=200000」のような条件式も何を判定しているのかが明確です。ただし、判定基準が増えると条件式も追加する必要があり、数式全体としての可読性は下がります。

IFS関数を使って、売上金額に応じて評価ランクを判定した例

　こうした評価は、実はXLOOKUP関数を使って処理することも可能です。対応する値を検索するイメージの強いXLOOKUP関数ですが、まさにその“検索”を使ったテクニックです。1つの数式内に条件と結果を列挙すれば、IFS関数よりも簡潔に記述できますよ。

「{ }」で対応表を直接書ける

　今回の数式では、以下のように複数の値を「{ }」で囲んで記述します。これは「配列定数」と呼ばれる記法です。難しく感じるかもしれませんが、ワークシート上の表を数式中に埋め込む際に利用できます。

上記の配列定数をワークシートに入力したイメージ

　セル範囲に入力した一覧を参照するのではなく、数式中に一覧そのものを書き込んでいると考えるとわかりやすいでしょう。文字列も同じように記述できます。

上記の配列定数をワークシートに入力したイメージ

　数式中に“小さな表”を記述できる点が、この書き方の特徴です。一時的な集計であれば、シート上にマスタ表を作らずに済むため、数式をコンパクトにまとめられます。

　ただし、配列定数は数式の中に直接書き込むため、基準値を頻繁に変更する用途には向きません。後から変更する可能性が高い場合はマスタ表を作成して参照するほうがいいでしょう。今回は、数式を簡潔にするために、こちらの記法を採用します。

配列を利用して評価ランクを判定する

　これらの配列を利用すると、XLOOKUP関数で評価ランクを判定できます。数式は以下のようになります。

　D列（D2:D21）の売上金額を「基準値」の配列（{0,50000,100000,200000}）から検索し、対応する値を配列（{"D","C","B","A"}）から返します。

　5つめの引数［一致モード］に指定している「-1」は“次に小さい値を探す”という指定です。今回のような段階評価では、セルの値に完全一致する値を検索しても意味はありません。どの基準値の範囲に収まるかを判定するため、「近似一致」を利用します。

　例えば、セルD2の「32,000」は、0以上50,000未満ですから、「32,000」の次に小さい値は「0」となり、対応する評価は最下位ランクの「D」となります。

　つまりこの数式は、{0,50000,100000,200000}という基準値を検索して、該当するランクを返しているわけです。

　今回のような「○円以上」の判定では“次に小さい値”を使う必要があるため、基準値は小さい順に並べ、引数［一致モード］には「-1」を指定します。「1」を指定してしまうと、“次に大きい値を探す”を指定したことになり、32,000が50,000として誤って判定されてしまいます。

上記の数式を使って、売上金額を判定した結果。1つめの引数［検索値］に「D2:D21」と指定しているため、結果はスピルで表示される

　IFS関数で条件を並べるだけでなく、XLOOKUP関数で“対応表を検索する”という考え方を覚えておくと、評価ランクや段階判定をよりスッキリ整理できるようになります。