この記事では「エクセルにて指定の値（基準値）に近い値（大きい方や小さい方など）を返す」について解説していきます。
方法としては1つではありませんが、
といいです。
サンプルデータを用意してありますので、それに基づき解説しましょう。
エクセルにて指定の値（基準値）に近い値を返す方法【概要】
基準値の近似値を出力する方法をチェックしていきます。
下記サンプルでは10個のアイテムと各アイテムにおける寸法(縦・ヨコ)が記録されたリストが用意されています。
この中から次のルールに従い、最適なアイテムを抽出する想定となります。
ただし、
・縦は許容値を「下回って」はならない
・ヨコは許容値を「上回って」はならない
とする。
エクセルにて指定の値に近い値を返す方法【検索値以上の最小値】
上サンプルを用いて、まずは検索値以上の最小値を求める場合についてお話しします。
セルF4に「=XLOOKUP(F1,B2:B11,A2:A11, -1,1)」と入力します。
使用している関数の意味を解説していきます。
XLOOKUP関数は、指定した範囲のなかから条件を満たすセルの個数を求める関数であり、
と入力します。
各引数には
・検索範囲(B2:B11): 判定対象のデータ範囲
・戻り範囲(A2:A11): 関数の出力値とするデータ範囲
・[見つからない場合](-1): 該当するデータが存在しない場合に出力する値
・[一致モード](1):一致とする条件を設定
・[検索モード]():検索方向に関する条件を設定 *本件では不使用
をそれぞれ指定します。
この状態でENTERを押すと、許容下限値(縦)が何も設定されていないため[見つからない場合]に設定した「-1」が出力されますが、関数の動作としてはこれで正しいと言えます。
ここまでできたら、許容下限値(縦)に値を設定します。
リストに存在する範囲の値であればいくつでも構いませんが、ここでは「20」を設定してみます。
すると、「20以上の最小値」に該当するのは26であり、抽出されるアイテムは「Hoge10」となります。
エクセルにて基準値に近い値（近似値）を返す方法【検索値以下の最大値】
今度は検索値（基準値）以下の最大値を求める方法を見ていきます。
こちらのケースでも同様にXLOOKUP関数で対応可能です。
セルF5に「=XLOOKUP(F2,C2:C11,A2:A11, -1,-1)」と入力します。
「検索値以上の最小値」との違いは[一致モード]=-1であること、検索範囲が「C2:C11」であることの2つのみで、それ以外はすべて同じです。
[一致モード]=-1の場合は「完全一致または次に小さい値」、=1であれば「完全一致または次に大きい値」という意味になります。
ENTERを押して、数式を確定させ、許容上限値(ヨコ)に値を設定すると該当するアイテムが判明します。
まとめ エクセルにて指定の値に近い値を返す方法
この記事では「エクセルにて指定の値に近い値を返す方法」について解説しました。
XLOOKUP関数は2021で新たに登場した関数でありますが、そのパフォーマンスは相当なものです。
この関数が登場する以前であれば、INDEX、MATCH関数を組み合わせた数式が必要となりますが、非常に複雑でかつデータの並び方(昇順/降順)にも制約がつくなどの弱点があります。
それに比べて、XLOOKUP関数を使用した場合は数式が非常にシンプルでかつデータの並びは適当でも構いません。
今まで苦労して作っていた数式がシンプルになる、非常に大きな利益ではないでしょうか。
コメント