最近、給与明細の分析に余念のないわたくしですが、先日、ついに給与計算用のexcelシートを作成しました。1ヶ月の退勤時刻を入力すると、社内ルールに従って残業代や保険料が計算され、その月の給与額(手取り)が計算されるというものです。まぁそんなことやって何になるんだといわれるとアレなんですけど、なんでしょう、好奇心ですよね。給与や税金のシステムに対する。これまで7年以上ものあいだ何も考えずに給与明細を受け取っていたんですけど、それがある日ふっと疑問に感じられて。給与明細にブラックボックスがあって(本当はそんなことはなかった)、妙に気になると。そういうことです。

そんなわけで、給与計算用のexcelシートを作ってみたのは良いのですけど、唯一上手く作れなかったのが、源泉徴収のところ。源泉徴収はあくまで概算なんだから何か公式でも作っておいてくれればいいのに、そうじゃないんですよね。課税対象額の「以上」と「未満」が決まっていて、それに合わせた額が設定されています。知りたかったら表から検索しろと。えええ。面倒くさい...

まぁそういうわけでそこだけ手動入力してたんですが、関数VLOOKUPを使うとその検索が簡単にできるらしいです。なんとまぁ。



1. 源泉徴収用のシートを用意する

使い回しを考えて、別シートとして源泉徴収用のシートを用意します。

シート「源泉徴収」:

ABCDE
1以上未満甲0甲1
22300002330005770419029700
32330002360005870429030700
42360002390005980440031700
52390002420006080450032700
62420002450006190461033700
72450002480006290471034700
82480002510006400482035700
92510002540006500492036700
102540002570006610503037700
112570002600006710513038600

「甲0」「甲1」はメインの給与振込先。数字は扶養家族です。乙はそれ以外。給与の振込先が1社で独身の場合には「甲0」を使います。データは国税庁のサイトでPDFまたはExcel形式で公開されているのでそこから持ってくればOK。上のサンプルはそこからの抜粋です。

平成22年4月以降分 源泉徴収税額表|パンフレット・手引き|国税庁



2. 関数を記述する

給与計算用のシートがこうなっていたとしましょう。

ABC
1課税対象額所得税給与
2238,255

A2が社会保険料等を天引きし、手当を加えた最終額です。
給与の受け取りが1社のみで扶養家族無しだったとし、B2に所得税額(源泉徴収)を、C2に給与額(手取り)を表示させたいとすると、それぞれのセルは以下の通りとなります。

B2:

=VLOOKUP(A2,'源泉徴収'!A2:E11,3,TRUE)

C2:

=A2-B2


結果はこんな感じ。

ABC
1課税対象額所得税給与
2238,2555,980232,275


おおー。
(自分で感動した)


所得税が変わる度にエクセルファイルを差し替える必要はあるけど、まぁだいたいこれで何とかなりそうです。
素晴らしい。


参考

excel で 賞与 源泉徴収税額 の関数を教えてください。 | OKWave




ちなみに...VLOOKUPってなんだ?

VLOOKUP関数の説明としてはこんな感じ。

VLOOKUP関数は、指定した表の「左端の列」を縦方向(行単位)に検索します。
 

あ、そうなのか。
ということは、源泉徴収シートは「以上」だけあれば良くて「未満」は別にあってもなくても良いんですね。

さらに関数の最後の「TRUE」の部分は検索の型。使い分けは以下の通り。

FALSE:

FALSEを指定すると、データが見つからなかった場合、「#N/A」エラーが表示されます。

つまり、完全一致の検索。

TRUE:

TRUEを指定、または、この引数の指定を省略すると、データが見つからなかった場合、そのデータを超えない最大値を検索します。ただし、表の左端列で昇順に並べ替えておく必要があります。

なるほどねー。

VLOOKUPは使いようによって色々と使い道があるようなので、覚えておきたいですね。便利。


参考

VLOOKUP関数の使い方[エクセル(Excel)の使い方]All About
VLOOKUP関数の定番組み合わせテクニック![エクセル(Excel)の使い方]All About