段階表で近似一致を使う

段階表は、連続した値を複数の範囲に分類する表です。税率区分、重量別の送料、数量割引、得点別のレターグレードなどがその例です。

考えられるすべての値について行を用意するのではなく、各範囲の開始しきい値だけを登録します。たとえば、87という得点に完全一致する項目がなくても、80から始まる範囲に該当します。

このような場合に近似一致が力を発揮します。完全一致を要求するのではなく、該当する範囲を検索できます。

これまでは、MATCH(value, range, 0)を使って完全一致を検索してきました。第3引数の0は、「この値に正確に一致するものを検索し、見つからなければ#N/Aを返す」という意味です。

段階表では、代わりに一致の種類として1を使います。検索値以下で最大の値を検索します。これは、範囲を検索するときに必要な動作そのものです。

重要なルールが1つあります。一致の種類が1の場合、しきい値の一覧は昇順に並べる必要があります。

成績表を考えてみましょう。E列には、0、60、70、80、90という下限しきい値を昇順で入力します。F列には、F、D、C、B、Aというラベルを入力します。

得点が0～59点ならF、60～69点ならDというように判定します。保存するのは各範囲の開始値だけで、すべての得点を登録する必要はありません。

ここでは、G1の得点に対応するレターグレードを返すことが目標です。

近似一致のMATCHを使って、得点がどの範囲に入るかを検索します。得点が87の場合、MATCH(G1, E2:E6, 1)は87以下で最大のしきい値を検索します。

しきい値は0、60、70、80、90です。87を超えない最大の値は80で、位置は4です。そのためMATCHは4を返します。

87自体が一覧に含まれていなくても、この位置によって正しい範囲を特定できます。

次に、その位置をラベル列F2:F6のINDEXに渡します。

INDEX(F2:F6, MATCH(G1, E2:E6, 1))は位置4を受け取り、4番目のラベルである「B」を返します。

したがって、得点87は正しくグレードBに対応します。G1を95に変更するとMATCHは5を返して「A」になり、55に変更するとMATCHは1を返して「F」になります。

近似一致のMATCH（種類1）では、検索範囲を昇順に並べる必要があります。データが小さい順に並んでいることを前提とし、検索値を超えた時点で検索を停止します。

しきい値の順序が正しくないと、MATCHが早い段階で停止して誤った位置を返す可能性があります。エラーは表示されないため、誤りに気付きにくい点にも注意が必要です。段階表を使う前に、しきい値列を必ず小さい順に並べてください。

XLOOKUPでも近似一致を使えます。第5引数である一致モードには、段階表に最適な「完全一致、または次に小さい項目」を表す-1を指定できます。

これにより、G1以下で最大のしきい値を検索し、対応するラベルを返せます。INDEXは必要ないため、範囲検索ではINDEX-MATCHより読みやすいことがよくあります。

次は、数量割引の例です。E列に注文数量のしきい値として0、10、50、100を入力し、F列に割引率として0%、5%、10%、15%を入力します。

• 注文数が7の場合：7以下で最大のしきい値は0、位置は1なので、0%を返します。
• 注文数が60の場合：60以下で最大のしきい値は50、位置は3なので、10%を返します。
• 注文数が200の場合：200以下で最大のしきい値は100、位置は4なので、15%を返します。

数量がいくつであっても、1つの数式で対応できます。

値がすべてのしきい値より小さい場合はどうなるでしょうか。近似一致のMATCHでは、その値以下に該当する値がないため、#N/Aを返します。

これを避けるには、最初のしきい値で最小値（通常は0）をカバーするか、数式をIFERRORで囲み、入力が範囲外の場合に分かりやすいメッセージを表示してください。

段階表では、次の点に注意してください。

• しきい値が未ソート：誤った結果がエラーなしで返される、最も多い原因です。
• 一致の種類に0を使う：完全一致を強制するため、範囲の途中の値では#N/Aが返されます。
• 開始値ではなく終了値を保存する：種類1のMATCHでは、各範囲の上限ではなく下限が必要です。
• しきい値がテキストになっている：テキストとして保存された数値は比較を正しく行えません。数値として保持してください。

近似一致を2方向の検索方法と組み合わせることもできます。たとえば、重量の範囲（行）とゾーンの範囲（列）の両方で送料が決まる表を考えてみましょう。

種類1の近似一致MATCHを1つ使って重量の行を検索し、もう1つを使ってゾーンの列を検索します。そして、両方の位置をINDEXに渡します。どちらの軸もしきい値を並べたものなので、各MATCHは正しい範囲を特定できます。

この方法により、INDEX-MATCH-MATCHと段階表の仕組みを組み合わせて、複雑な料金表を扱えます。

段階表の近似検索について、理解度を確認しましょう。

段階表や範囲を検索する場合は、次の点を覚えておいてください。

• 各範囲の下限しきい値を保存し、昇順に並べます。
• MATCH(value, thresholds, 1)を使って範囲の位置を検索します（入力値以下で最大の値）。
• INDEX(labels, ...)でラベルを返すか、XLOOKUP(..., -1)を使って同じ結果を得ます。

0のしきい値で最小値をカバーするか、IFERRORで範囲外の入力に対処してください。また、しきい値を未ソートのままにしないでください。