今回の記事から新たな連載シリーズを開始します。
「税務に役立つExcel仕事術」と題し、会計事務所での業務に必須又は役立つExcelの機能を紹介していきたいと思います。
第一回のテーマは“VLOOKUP関数”です。
VLOOKUP関数とは
VLOOKUP関数は税務に携わる人であれば一度は使ったことがあるのではないでしょうか。
Microsoft Office Supportによれば、VLOOKUP関数はつぎのような場面で活用できます。
表や範囲から行ごとに数値や文字列などを検索するには、検索/行列関数の 1 つ、VLOOKUP を使用します。たとえば、部品番号によって自動車部品の価格を検索できます。
その最も簡単な形式で、VLOOKUP 関数は次のようになります。
= VLOOKUP (検索する値, 値を検索する範囲, 戻り値を含む範囲の列の番号, 完全一致か近似一致か – 0/FALSE か 1/TRUE で指定)。
引用：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円 |
まず、このテーブルをエクセルで再現します。
次にテーブルの下に、それぞれの項目について次のような表を作成します。
次に、計算基礎となる所得の金額を表の該当箇所に入力します。今回は7,000,000円を例とします。
なお、入力値を変更するセルはハイライトしておくと他のパターンを検証するときや他のメンバーが使用するときに、そのセルを変化させればいいことが明らかになるので、該当セルはハイライトする習慣をつけましょう。
次がVLOOKUP関数の出番です。税率欄（C13）につぎのような数式を入力します。
=VLOOKUP(C12,$B4$:$D$10,3,1)
かっこ内の意味は（検索値、範囲、列番号、検索方法）です。
すなわち、所得金額が7,000,000円の場合の税率を探し出してくれています。
なお、列番号の3はテーブルの左から3列目、検索方法の1(FALSE)は近似値の検索を意味します。検索方法を0(TRUE)とすると完全一致を意味し、検索値に該当する場合のみ検索結果を表示します。
今回のようにテーブル内の近似値を検索する必要がある場合は、1(FALSE)を使用しましょう。
7,000,000円は6,950,000円〜9,000,000円のレンジ内にありますので、これに対応する税率である23%を正しく表示してくれていることが分かります。
同じ要領で控除額を計算すると、7,000,000円に対応する控除額である636,000円が表示されました。
最後に所得税額の欄に次のような数式を入力し、所得税額の計算を行います。
これで計算過程は以上です。所得金額7,000,000円に対応する所得税額974,000円が計算されました。
まとめ
今回はVLOOKUP関数の使い方について解説しました。
なお、同じような関数として”HLOOKUP”という関数がありますが、VLOOKUP関数が縦(vertical)の関数であるのに対し、HLOOKUP関数は横(Horizontal)の関数です。縦横の違いはありますが、考え方は同じです。
上記の例は最もシンプルなケースですが、これを応用して、例えば給与の金額から所得税を求める計算や、さらに複雑な条件を加味した税額計算などあらゆるパターンに応用できます。
VLOOKUP計算は最も基本的な関数の一つであり、またとても便利な関数ですので是非マスターしましょう。
コメントを残す