エクセル関数応用
数値範囲で表検索するVLOOKUP近似一致

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2021-02-02

数値範囲で表検索するVLOOKUP近似一致


数値範囲で指定されている表を検索する場合は、VLOOKUP関数の近似一致を使います。


○以上~△未満、
○超~△以下、
このような数値の範囲で示されている表を検索する場合は、
VLOOKUPの近似一致を使う事で検索できます。

具体的に良くあるものとして、所得税の税率があります。

表をエクセルで扱えるように調整する

課税される所得金額 税率 控除額
195万円以下 5% 0円
195万円を超え 330万円以下 10% 97,500円
330万円を超え 695万円以下 20% 427,500円
695万円を超え 900万円以下 23% 636,000円
900万円を超え 1,800万円以下 33% 1,536,000円
1,800万円を超え 4,000万円以下 40% 2,796,000円
4,000万円超 45% 4,796,000円

所得税の累進課税の税率と控除額です。
見たことのある人も多いと思います。

所得金額が500万円なら、
「330万円を超え 695万円以下」なので、税率20%、控除額427,500円となります。

エクセルのシートで、所得金額を入力したら税率と控除額が自動的に表示されるようにVLOOKUP関数を入れたい場合を考えます。

しかし、上の表ではエクセルの関数が使えません。
エクセルの関数で使うには、「万円」といった表記は正しい数値として認識されません。
「超え 以下」は、別々の数値なので、別々のセルにする必要があります。

エクセル VLOOKUP 近似一致

このように、数値として正しく入れ直し、「超え 以下」を2列に分けています。
B列はVLOOKUP関数では使用しませんが、このようにした方が見た感じでわかり易いだろうという事で入れています。
金額なので整数という事で、「超え」の部分は+1することで対応しています。

これが出来れば、後はVLOOKUP関数で自動的に税率と控除額を取得することが出来ます。

VLOOKUP関数の近似一致

エクセル VLOOKUP 近似一致

F1セルに所得金額を入れたら、
G1セルに税率、H1セルに控除額が表示されるようにしています。

G1セルの数式
=VLOOKUP(F2,A2:D8,3,TRUE)

H1の数式
=VLOOKUP(F2,A2:D8,4,TRUE)

VLOOKUPの書式
=VLOOKUP(検索値,範囲,列番号,検索方法)

第4引数の検索方法でTRUE(1でも良い)にするのが近似一致になります。

エクセル VLOOKUP 近似一致
エクセル VLOOKUP 近似一致

近似一致を使う場合は、検索する一番左の列は、昇順に並べられている必要があります。
昇順に並んでいないと、正しく検索されません。

VLOOKUP近似一致がどのように値を探しているか

検索値と完全に一致する値、または、その近似値が返されます。
完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。
少し詳しく説明すると、
範囲の先頭列(一番左の列)を上から下に向かって順に検索していきます。

・検索値と一致している
・検索値を超える値が出現
・表の最下端に達した時

いずれかの状態になった時点の行が採用されます。
検索値を3,000,000円としてみた時、
4行目の3,000,001の値が、「検索値を超える値」となるので、この行になります。
また、40,000,001以上の値を指定した時は、最終行の8行目が採用されることになります。

VLOOKUP近似一致の表を作成する場合の注意点

VLOOKUP関数の性質をしっかりと把握して作成します。

・検索値と一致している
・検索値を超える値が出現
・表の最下端に達した時

特に注意点としては、一致している数値があると、その行になってしまうので、
○以上~△未満
この場合は、
以上なので○と同値を、範囲の先頭列(一番左の列)に設定します。

0以上~100未満
100以上~200未満
200以上~300未満
300以上
この場合は、

エクセル VLOOKUP 近似一致

以上の数値をそのまま指定すれば良いです。

○超~△以下
この場合は、
超なので○よりわずかに大きい数値(整数なら+1、小数なら+0.001等)を、範囲の先頭列(一番左の列)に設定します。
前述の所得税の表がこれに該当します。

VLOOKUP関数に関する参考ページ

VLOOKUP関数の基本的な使い方については、
VLOOKUP関数(範囲の左端列で値検索し対応セル値)
・VLOOKUP関数の書式 ・VLOOKUP関数:近似一致(検索方法=TRUE)の使用例 ・VLOOKUP関数:完全一致(検索方法=FALSE)の使用例
こちらを参考にしてください。

以下のページも参考にしてください。
VLOOKUPを他の関数でやる方法
・VLOOKUP関数の問題点 ・解決方法 ・解決方法の具体例 ・解決方法ごとの長所短所
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
・解説に使うシート ・VLOOKUP関数 ・INDEX関数とMATCH関数の組み合わせ ・OFFSET関数とMATCH関数の組み合わせ ・VLOOKUPを他の関数でやる方法 ・徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)の最後に
【奥義】大量データでの高速VLOOKUP
・高速VLOOKUPに使用するサンプルデータ ・高速VLOOKUPの数式 ・高速VLOOKUPの数式解説 ・高速VLOOKUPの補足
ワイルドカードが使える関数
・ワイルドカードについて ・データベース関数を除くExcel2003までの関数 ・データベース関数を除くExcel2007以降 ・2020年3月現在のOffice365 ・ワイルドカードの使用例



同じテーマ「エクセル関数応用」の記事

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
エクセルの日付と時刻のまとめ
連続数値部分を取り出し記号で連結
指数近似/対数近似/累乗近似(掲載順位とCTR)
ジャンプ・セル選択の応用(空欄を埋める、1行置きに挿入)


新着記事NEW ・・・新着記事一覧を見る

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.エクセルVBAでのシート指定方法|VBA技術解説
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ