この記事は会計処理や税務処理を行う方向けに、EXCELのVLOOKUP関数の近似値検索について説明しています。
VLOOKUP関数の復習
会計処理や税務処理でデータをまとめるとき、EXCELを利用されていると思います。この過程の中で、ある条件に合致した値を取得するのにVLOOKUP関数を使用されているのではないでしょうか。まずは、VLOOKUP関数について復習してみたいと思います。
①検索値
条件となる値や、その値が格納されたセルを指定します。
②範囲
①の検索値が格納された列を1列目として、①の検索値に合致した値が含まれている列までの範囲を指定します。
③列番号
②の範囲のうち、①の検索値に合致した値が含まれているのが何列目であるのかを指定します。
④検索の型
TRUEもしくはFALSEのいずれかを指定します。
TRUEを指定すると、検索値と完全に一致しなくても、近似値があればその値を取得します。これに対して、FALSEを指定すると検索値と完全に一致しなければ、値を取得することはできません。
ここで、注目したいのは検索の型です。普段、VLOOKUP関数を使用するときにはFALSEに設定しているのではないでしょうか。近似値に合致した値を取得しても、結局あやふやな結果が得られても困るわけですから。
しかし、ここをTRUEに指定して、VLOOKUP関数を使用する(近似値検索、あいまい検索などともいわれています。)ことで非常に便利になるものがあります。
近似値検索をどんなときに利用するか
では、どんなときに利用すると便利になるのでしょうか。
上記の図は所得税の速算表です。特に、税務処理をする場合は、このように、いくら~いくらまでは何％とか、いくら~いくらまでは金額がいくらだとか、段階的に税率や金額が指定されていることが多々あります。
VLOOKUP関数で近似値検索を利用すると簡単にほしい値を取得することができます。例として、ここでは上記の所得税の速算表にVLOOKUP関数の近似値検索を利用してみたいと思います。
まずは準備です。VLOOKUP関数で近似値検索できるように、少しデータを変更します。
大きく変更されている点は、課税される所得金額の欄です。いくら~いくらまでとなっている部分を別々の列に入力しています。
2列目は直接必要とはなりませんが、資料を確認したときにわかりやすいので、そのままにしています。
そのほか、表の中の文字は削除しています。
準備ができたら、VLOOKUP関数を使ってみたいと思います。
上の図のような配置になっていたとします。G1のセルに課税される所得金額を入力すると、税率と控除額が取得できるようになっています。
①検索値
検索する値は、課税される所得金額ですので、G1を指定します。
②範囲
G1に指定した値を検索する必要がありますので、G1の値を検索する列が1列目に、取得したい値が入っている列を最後の列(ここでは、税率と控除額を求めるので、控除額を最後の列とします。税率のみであれば、税率を最後の列とします。)までを指定します。A3:D9を範囲指定します。
③列番号
・税率を求める場合：範囲指定した表の3列目になるので3
・控除額を求める場合：範囲指定した表の4列目になるので4
④検索の型
もちろん、TRUEですね。
上記をまとめると、次のような式がG3,G4に入っています。
(G3)税率 ：=VLOOKUP(G1,A3:D9,3,TRUE)
(G4)控除額：=VLOOKUP(G1,A3:D9,4,TRUE)
うまくできましたか？
近似値検索の概要
上の図の例では8,000,000を検索しました。検索している列は範囲指定した1列目となるので、6,950,001の6行目、もしくは、9,000,001の7行目になることはわかると思います。
近似値検索ではその数字の範囲内の最大値となるため、8,000,000より小さい、表に記載された最大値、つまり6,950,001の値を取得することになります。
段階的に率や金額が指定されている場面には出くわすことが多いと思いますので、ぜひとも覚えて使ってください。