はじめに
Excelでデータを扱う際、「○以上△未満」のような範囲条件でデータを検索したい場面はよくあります。例えば、得点に応じて評価を割り当てたり、売上額に応じてランクを分けたりする場合です。従来のVLOOKUP関数でも近似一致検索で同様の処理が可能でしたが、XLOOKUP関数では「一致モード」引数を活用することで、より直感的かつ柔軟に範囲検索を行うことができます。この記事では、XLOOKUP関数を使った範囲検索に焦点を当て、その利点や具体的な使用方法を解説します。
XLOOKUP関数とは？
XLOOKUP関数は、指定された範囲または配列で値を検索し、最初に見つかった一致に対応する項目を返す関数です。
基本的な構文（復習）
Excel
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
各引数の意味は以下の通りです。
- 検索値（必須）: 検索する値を指定します。
- 検索範囲（必須）: 検索を行う範囲を指定します。
- 戻り範囲（必須）: 結果として返す値が含まれる範囲を指定します。
- 見つからない場合（省略可能）: 検索値が見つからない場合に返す値を指定します。
- 一致モード（省略可能）:
- 0：完全一致（既定値）。
- -1：完全一致。見つからない場合は、次に小さい項目を返します。（今回のテーマ）
- 1：完全一致。見つからない場合は、次に大きい項目を返します。
- 2：ワイルドカード文字（
*、?、~）を使用した部分一致。
- 検索モード（省略可能）:
- 1：先頭から末尾へ検索（既定値）。
- -1：末尾から先頭へ検索。
- 2：昇順で並べ替えられた範囲を使用したバイナリ検索。
- -2：降順で並べ替えられた範囲を使用したバイナリ検索。
＜Excelサンプルデータダウンロード＞
Excel XLOOKUP関数：「○以上△未満」の範囲検索をスマートに！VLOOKUPの弱点を克服
XLOOKUP関数での「○以上△未満」検索
XLOOKUP関数で「○以上△未満」の範囲検索を行うには、「一致モード」引数に-1を指定します。この時、検索範囲は昇順に並んでいる必要があります。画像例を基に解説します。
画像例の解説
画像では、得点に応じて評価を割り当てる表（D3:F6）を使って、得点に対応する評価を検索しています。セルB3に入力されている数式は以下の通りです。
Excel
=XLOOKUP(B2,D3:D6,F3:F6,"---",-1)
この数式を分解して解説します。
B2: 検索値（得点）が入力されています。例では「72」です。D3:D6: 検索範囲（得点の下限値が並んだ列）を指定しています。昇順に並んでいることが重要です。F3:F6: 戻り範囲（評価が並んだ列）を指定しています。"---": 見つからない場合の値を指定しています。-1: 一致モードに-1を指定しています。これが「○以上△未満」検索を行うための重要な設定です。
数式の動作
- 検索値
B2（例：「72」）が、検索範囲D3:D6内で検索されます。 - 「一致モード」が
-1なので、検索値以下の最大の値（70）が検索されます。 - 70に対応する評価「B」が返されます。
つまり、この数式では、
- 0点以上60点未満：F
- 60点以上70点未満：C
- 70点以上80点未満：B
- 80点以上：A
という評価が割り当てられます。
Excelのサンプルデータ【ダウンロード】
以下は、上記画像のExcelデータですので、ダウンロードして練習などに使用してください。
Excel-g720-1.xlsx （ダウンロード）
「一致モード」1の場合（「○超△以下」）
「一致モード」に1を指定すると、「○超△以下」の範囲検索を行うことができます。この場合は、検索範囲は降順に並んでいる必要があります。
XLOOKUPとVLOOKUPの比較（近似一致検索）
XLOOKUPとVLOOKUPの近似一致検索を比較すると、以下のようになります。
| 特徴 | XLOOKUP | VLOOKUP |
|---|---|---|
| 検索範囲の並び順 | 昇順（一致モード-1）、降順（一致モード1） | 昇順のみ |
| 検索値が見つからない場合 | 次に小さい/大きい値を返すかどうかを選択可能 | 常に次に小さい値を返す |
| 柔軟性 | 高い | やや低い |
特に、検索範囲の並び順が昇順のみではない点と、検索値が見つからない場合の挙動を選択できる点は、XLOOKUPの大きな利点です。
まとめ
XLOOKUP関数を使うことで、「○以上△未満」のような範囲検索を簡単に行うことができます。「一致モード」引数を適切に設定することで、VLOOKUPよりも柔軟かつ明確に範囲検索を行うことが可能です。
この解説で、XLOOKUP関数を使った範囲検索についてより深く理解できたかと思います。この情報を活用し、Excelでのデータ分析をより効率的に行いましょう。
コメント