Excelで価格表、成績表、測定結果などを扱っていると、入力した数値と完全に一致する値ではなく、最も近い値を見つけたい場面があります。
たとえば、売上金額に近いランクを返したい場合や、測定値に近い規格値を抽出したい場合、近似値検索の考え方を理解しておくと作業が大幅に効率化されます。
近似値は単に近い数値を探すだけではなく、検索方向、範囲の並び順、誤差の計算、関数の選び方を用途に応じて使い分けることが大切です。
この記事では、Excelで近似値を求める関数、最も近い値の抽出方法、検索時の注意点までを順番に解説していきます。
近似値を求める代表的な方法は、XLOOKUP関数、VLOOKUP関数、MATCH関数、INDEX関数、ABS関数の組み合わせです。
検索表から区分を返す場合と、数値同士の差を比較して最も近い値を抽出する場合では、適した数式が異なります。
エクセルで近似値を求める基本方法
| A | B | C |
|---|---|---|
| 1 | 購入金額 | 該当ランク |
| 2 | 3,280 | 近い基準を検索 |
それではまず、Excelで近似値を求める基本方法について解説していきます。
近似値検索では、検索する数値と候補となる数値の差を確認し、目的に合う候補を返します。
検索対象より小さい中で最大の値を探すのか、検索対象より大きい中で最小の値を探すのか、絶対的に最も近い値を探すのかによって結果は変わります。
たとえば購入金額が3,280円で、価格区分が3,000円、3,500円、4,000円と並んでいる場合、下限基準を調べるなら3,000円、単純な差の小ささを比べるなら3,500円が近似値になります。
近似値検索で確認する検索方向
近似値検索を始める前に、何を近い値として扱うのかを決めましょう。
料金表や評価表では、検索値以下で最も大きい値を返す下方向の検索がよく使われます。
一方で、指定した数量を満たせる最小の容量や、目標値以上で最も近い規格を探すときは上方向の検索が適しています。
測定値に最も近い規格値を探すようなケースでは、候補値との誤差を絶対値で比較する方法が必要です。
目的を決めずに関数だけを選ぶと、数式は正しくても業務上は誤った判定になるかもしれません。
検索用データを整える考え方
近似検索に使う表では、数値が文字列として保存されていないかを最初に確認します。
セルの左上に緑の三角形が表示されていたり、数値が左寄せになっていたりする場合は、文字列として扱われている可能性があります。
VLOOKUP関数やMATCH関数で近似一致を使う表は、検索列を昇順に並べることが基本です。
並び順が乱れていると、関数が途中の値を基準に判定するため、期待した近似値が返らないことがあります。
元データを変更できない場合は、別の場所に並べ替え済みの検索表を作る方法も有効です。
完全一致との使い分け
完全一致は、検索する数値と表内の数値が同じときだけ結果を返す検索方法です。
近似一致は、完全に同じ値がなくても、条件に合う近い値を返せる点が特徴です。
たとえば社員番号、商品コード、顧客IDの検索には完全一致が向いています。
一方で、点数から評価を決める表、金額から割引率を返す表、数量から送料を判定する表では近似一致が役立ちます。
識別番号の検索に近似一致を使うと別のデータを返すおそれがあるため、用途の区別が重要です。
【操作のポイント】近似値検索を行う前に、下限値を返すのか、上限値を返すのか、差が最小の値を返すのかを文章で整理してから数式を作成しましょう。
XLOOKUP関数による近似値検索
| A | B | C |
|---|---|---|
| 1 | 下限金額 | 割引率 |
| 2 | 0 | 0% |
| 3 | 3000 | 5% |
続いては、XLOOKUP関数による近似値検索を確認していきます。
XLOOKUP関数はMicrosoft 365やExcel 2021以降で利用できる検索関数で、検索範囲と戻り範囲を別々に指定できるため、柔軟な近似値検索が可能です。
検索値がF2セル、下限金額がA2からA5、割引率がB2からB5にある場合、検索値以下で最も大きい値に対応する割引率を返す数式は次のとおりです。
=XLOOKUP(F2,A2:A5,B2:B5,”該当なし”,-1)
一致モードを指定する数式
XLOOKUP関数では、第5引数の一致モードを指定することで近似値の探し方を調整できます。
=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード)
一致モードにマイナス1を指定すると、完全一致がないときに検索値より小さい次の値を返します。
下限金額から割引率を判定する表では、マイナス1の指定が特に使いやすい設定です。
反対に、検索値より大きい次の値を返したい場合は、一致モードに1を指定します。
検索値が3,280で、3,000と3,500が候補なら、マイナス1では3,000、1では3,500が基準になります。
検索値以下で最大の値を返す仕組み
購入金額に応じて送料や割引率を決める場合、一般的には検索値以下で最大の基準値を採用します。
たとえば3,000円以上で5パーセント、5,000円以上で10パーセントという表なら、3,280円は3,000円以上の条件に該当します。
この場合、戻り範囲に割引率を指定すると、該当する割合をそのまま取得できます。
XLOOKUP関数では検索列が左端でなくてもよいため、表のレイアウトを大きく変えずに使える点も利点です。
ただし、検索範囲と戻り範囲の行数は必ずそろえてください。
行数が異なると数式エラーにつながります。
上方向の近似値を返す方法
最小ロット、必要容量、配送サイズなどを調べる場合は、検索値以上で最も小さい値を返す上方向検索が便利です。
たとえば荷物の重量が3.2kgで、料金表が3kg、5kg、10kgの区分になっている場合、3.2kgは5kg区分に判定する必要があります。
その場合は、一致モードに1を指定します。
=XLOOKUP(F2,A2:A5,B2:B5,”該当なし”,1)
この数式では、F2より大きい次の値を検索し、その行の料金や区分を返します。
検索値が最大値を超えたときの結果も確認し、必要なら見つからない場合の文字列を設定しておきましょう。
【操作のポイント】XLOOKUP関数では、下限に近い値なら一致モードをマイナス1、上限に近い値なら1に指定し、検索表の意味に合わせて選びます。
VLOOKUP関数とMATCH関数による近似一致
| A | B | C |
|---|---|---|
| 1 | 得点下限 | 評価 |
| 2 | 0 | D |
| 3 | 60 | C |
続いては、VLOOKUP関数とMATCH関数による近似一致を確認していきます。
旧バージョンのExcelを含め、幅広い環境で使える近似値検索としてVLOOKUP関数とMATCH関数は現在も重要です。
VLOOKUP関数で評価を抽出する数式
得点がF2セルにあり、得点下限と評価がA2からB6に入力されているとします。
=VLOOKUP(F2,A2:B6,2,TRUE)
第4引数のTRUEは近似一致を意味します。
F2の得点が78点なら、0、60、70、80のように並んだ下限値の中から、78以下で最大の70を見つけ、その行の評価を返します。
VLOOKUP関数の近似一致では、検索列を小さい順に並べることが必須です。
数値が降順だったり途中で入れ替わっていたりすると、意図しない評価が表示されるため注意しましょう。
MATCH関数で近い位置を求める方法
MATCH関数は、指定した値が範囲内の何番目にあるかを返す関数です。
近似一致で使う場合は、第3引数に1を指定します。
=MATCH(F2,A2:A6,1)
この数式は、F2以下で最大となる数値が検索範囲内で何番目にあるかを返します。
得点が78でA2からA6に0、60、70、80、90が並ぶ場合、70が3番目なら結果は3になります。
MATCH関数の結果は位置番号なので、そのままでは評価名や金額は表示されません。
次の見出しで説明するINDEX関数と組み合わせることで、位置に対応するデータを抽出できます。
INDEX関数と組み合わせる抽出方法
INDEX関数は、指定した範囲から行番号や列番号に対応する値を返す関数です。
評価がB2からB6にあり、得点がF2にある場合は、次のように入力します。
=INDEX(B2:B6,MATCH(F2,A2:A6,1))
MATCH関数が得点に対応する行番号を求め、INDEX関数がその位置にある評価を返す仕組みです。
VLOOKUP関数では検索列より左にある値を返せませんが、INDEX関数とMATCH関数の組み合わせなら左右どちらの列からも抽出できます。
表の列順を自由に設計したいときは、INDEX関数とMATCH関数の組み合わせが便利です。
【操作のポイント】VLOOKUP関数とMATCH関数の近似一致では、検索対象の数値を必ず昇順に整え、境界値の判定結果を数件テストしましょう。
ABS関数とMIN関数による最も近い数値の抽出
| A | B | C |
|---|---|---|
| 1 | 規格値 | 測定値 |
| 2 | 9.5 | 9.8 |
| 3 | 10.0 | 近い規格を抽出 |
続いては、ABS関数とMIN関数による最も近い数値の抽出を確認していきます。
下限または上限ではなく、候補の中から検索値との誤差が最小になる数値を探す場合は、差の絶対値を利用します。
この方法は、測定値に近い規格、予算に近い商品価格、目標値に近い実績などを探すときに活用できます。
絶対値で誤差を計算する考え方
数値の差は、検索値より小さい候補ではマイナス、大きい候補ではプラスになります。
近さだけを比較したい場合、符号の違いは不要です。
ABS関数を使うと、マイナスの差もプラスの値に変換できます。
=ABS(A2-$F$2)
A2が候補の規格値、F2が検索値なら、この数式は両者の距離を返します。
検索値を固定するため、F2には絶対参照のドル記号を付けてコピーします。
数式を下方向へオートフィルすると、各候補値と検索値の誤差を一覧で確認できます。
MIN関数で最小誤差を求める方法
候補ごとの誤差がC2からC10に表示されている場合、最も小さい誤差はMIN関数で取得できます。
=MIN(C2:C10)
検索値が9.8で、候補が9.5と10.0なら、それぞれの誤差は0.3と0.2です。
そのため、MIN関数の結果は0.2となります。
最小誤差だけを表示するならこの数式で十分ですが、実際には対応する規格値や商品名も表示したいことが多いでしょう。
その場合は、MATCH関数で最小誤差の位置を求め、INDEX関数で元の候補値を取り出します。
最も近い値を返すINDEX関数の数式
規格値がA2からA10、誤差がC2からC10にある場合、最も近い規格値を返す数式は次のとおりです。
=INDEX(A2:A10,MATCH(MIN(C2:C10),C2:C10,0))
MATCH関数では完全一致を示す0を指定します。
これは、MIN関数で取得した最小誤差と同じ値が、誤差列のどこにあるかを正確に探すためです。
同じ最小誤差の候補が複数ある場合、この数式は上側にある最初の候補を返します。
たとえば9.8に対して9.5と10.1がともに0.3差なら、表の上にある値が選ばれます。
【操作のポイント】最も近い値を求めるときは、候補値との誤差をABS関数で可視化してから、MIN関数とINDEX関数で抽出すると数式の確認がしやすくなります。
近似値検索で発生しやすいエラーと対処法
| A | B | C |
|---|---|---|
| 1 | 検索値 | 結果 |
| 2 | 450 | エラー確認 |
続いては、近似値検索で発生しやすいエラーと対処法を確認していきます。
近似値検索では、数式の書式が正しくても、検索表の構造やデータ型によってエラーや誤判定が起こることがあります。
検索値が最小値より小さい場合
下方向の近似検索では、検索値が検索表の最小値より小さいと該当データが見つかりません。
たとえば下限値が1,000から始まる表に対して、検索値が450なら、450以下の候補は存在しないためです。
VLOOKUP関数ではこの状態でエラーになることがあり、XLOOKUP関数では第4引数に表示文を指定しておくと分かりやすくなります。
料金表などでは、最小値として0を登録しておくと、多くのケースでこの問題を避けられます。
値が未入力の場合も想定し、IF関数で空白判定を組み合わせる方法もあります。
数値と文字列が混在するケース
見た目は同じ500でも、数値の500と文字列の500はExcel内部では別の種類です。
CSVファイルを読み込んだ後や、他のシステムからコピーした後には、数値列に文字列が混在しやすくなります。
検索結果がおかしいと感じたら、セルの表示形式だけでなく、数式バーで内容を確認しましょう。
VALUE関数や区切り位置機能を使うと、文字列として保存された数値を数値に変換できます。
余分な空白が含まれる場合は、TRIM関数やSUBSTITUTE関数で整形することも必要です。
同じ距離の候補がある場合
最も近い値を抽出する数式では、検索値から同じ距離にある候補が複数存在することがあります。
たとえば検索値が100で、候補が95と105なら、どちらも差は5です。
INDEX関数とMATCH関数の基本形では、表の上側にある候補が返ります。
上側を優先するのか、大きい値を優先するのかを決めておくと、近似値の判定ルールが明確になります。
大きい値を優先したい場合は、候補表を降順にする、補助列に優先順位を付けるなどの工夫が考えられます。
【操作のポイント】検索結果に違和感があるときは、数式だけでなく、検索列の昇順、数値形式、最小値と最大値、同率候補の扱いを順番に確認しましょう。
近似値検索の活用場面
| A | B | C |
|---|---|---|
| 1 | 利用場面 | 近似値の基準 |
| 2 | 送料判定 | 上限側 |
| 3 | 評価判定 | 下限側 |
続いては、近似値検索の活用場面を確認していきます。
近似値検索は数式の知識としてだけでなく、日常的な集計や判定業務を自動化するための実務的な手段です。
料金表と割引率の自動判定
購入金額、利用時間、注文数量に応じて料金や割引率が変わる表では、下限値を使った近似検索が役立ちます。
検索表に金額の境界と適用率を登録しておけば、明細ごとに手入力で区分を選ぶ必要がありません。
条件が増えても、検索表の行を追加するだけで対応できるため、数式を何重にもするより管理しやすい方法です。
割引条件の変更にも対応しやすく、担当者が変わった後の引き継ぎにも向いています。
成績と評価ランクの分類
試験の点数、売上達成率、作業進捗率などからランクを返す処理にも近似一致を使えます。
0から59をD、60から69をC、70から79をBというように、各評価の下限値を検索表に入力します。
あとはVLOOKUP関数やXLOOKUP関数で得点を検索すれば、評価を自動表示できます。
境界の60点、70点、80点を実際に入力して確認すると、評価の設定ミスを防ぎやすくなります。
規格値と測定値の照合
製品寸法、温度、重量、電圧などの測定値に近い規格を選ぶ作業では、差の絶対値を利用する検索が適しています。
候補となる規格値と測定値の差を計算し、最小の誤差に対応する値を抽出します。
規格値との照合では、単に近い値を返すだけでなく、許容差の範囲内かどうかも別の列で判定すると実用性が高まります。
たとえば誤差が0.5以下なら合格、それを超えたら要確認と表示するルールを追加できます。
【操作のポイント】近似値検索は、検索表を別シートにまとめておくと、料金改定や評価基準の変更時にも修正箇所を見つけやすくなります。
まとめ エクセルで近似値を求める関数と抽出方法
Excelで近似値を求める方法は、求めたい結果の種類によって使い分けることが重要です。
検索値以下で最大の値を基準にする場合は、XLOOKUP関数の一致モードにマイナス1を指定する方法や、VLOOKUP関数のTRUE指定が便利です。
検索値以上で最も近い値を探す場合は、XLOOKUP関数の一致モードに1を指定します。
候補の中から差が最も小さい数値を抽出したい場合は、ABS関数で誤差を求め、MIN関数、MATCH関数、INDEX関数を組み合わせましょう。
近似値検索の精度を高めるポイントは、検索方向を決めること、検索表を正しい順序に整えること、数値と文字列の混在をなくすことです。
料金、評価、規格、在庫、送料など、近似値検索を活用できる場面は数多くあります。
まずは小さなサンプル表で境界値を試し、意図した結果になることを確認してから実際のデータへ適用していきましょう。
