VBA で毎月の源泉徴収税を返すユーザー関数を作る（電算機計算バージョン）
Excel で給与情報から源泉徴収される所得税を求めるユーザー関数を作ってみようと思いました。
（最後の方にテーブルを使わないバージョンがあります（完全プロシージャー版）。とりあえず使うだけならそちらが良いでしょう。）
所得税の計算
所得税の計算方法は国税庁のサイトにあります。最新版は「所得税 表」などのキーワードで見つけることができるでしょう。
基本的には上のページにある「給与所得の源泉徴収税額表」を元に求めることになるでしょう。月払いの場合には次のExcel表を参照するようです。
給与所得の源泉徴収税額表（月額表） Excel
電子計算機、要はパソコン等を使って計算する場合には、次のPDFにある計算方法も認められているようです。
電子計算機等を使用して源泉徴収税額を計算する方法を定める財務省告示 PDF
税額表と、電算機計算では同じ金額を元にして税額を求めても結果が異なる場合があります。これは年末調整で差額を調整することになるようです。
（令和5年分も令和4年と同額です）
電算機計算
Excelを使って計算するのですから、電算機計算から作ってみます。
最初にExcelのワークシートに上のPDFにある表を下のように作ります。表は必ずテーブルとして作ってください。データを入力したら、選択して、「挿入」メニューから「テーブル」を選ぶだけです。
 |
| クリックすると拡大 |
テーブルを選択すると「テーブルデザイン」という緑の文字のメニューが上に表示されるので、選択し、「テーブル名：」で「別表第一」のように名前を変更します。
各セルには、次のように名前を付けてあります。参照範囲というのが名前を付けるセルのアドレスです。$B$3のように絶対指定で書かれていますが、B3 のように読み替えてOKです。
 |
| クリックすると拡大 |
まずは、関数を使って、税額を計算してみます。
関数は確認用なので作らなくても問題はありません。関数だけ作りたい方は下の「ユーザー関数化」を参照してください。
社会保険を控除した後の給与
給与から社会保険等を控除した後の金額に対して税金を計算します。
今回は、そのセルを社保控除後(A)と呼んでいます。
扶養人数
配偶者や扶養人数に応じて、税金は軽くなります。
本来配偶者控除と扶養控除は異なるものですが、現在は同額なのでまとめて扶養人数としています。
給与所得控除額
社保控除後Aの金額を使って別表第一から給与所得控除の額を求めます。
別表第一のa, b, 給与所得控除の額、はそれぞれ次の式を割り当ててあります。
=XLOOKUP(社保控除後A, 別表第一[以上], 別表第一[a], 0, -1)
=XLOOKUP(社保控除後A, 別表第一[以上], 別表第一[b], 0, -1)
=ROUNDUP(社保控除後A * 第一a + 第一b, 0)
これで、給与所得控除の額を求めています。
最近のExcelで使えるようになった xLookUpは、離れた列から容易に対応する値を取り出すことができとても便利です。
上の最初の式では、社保控除後Aの値を使って、別表第一の「以上」の列を上から検索し、社保控除後Aの値以下のセルを見つけたら、それに対応する「a」の列の値を返しています。見つからない場合（社保控除後Aがマイナスの場合）には0を返します。
ここでは検索のために「以上」の列の最初の行に0を入力しています。
最後の RoundUp は、1円未満の端数を切り上げるためのものです。
扶養控除額
扶養控除の額は、単純に扶養控除額に人数を掛けるだけです。
= 別表第二[扶養控除] * 扶養人数
基礎控除額
基礎控除額は社保控除後(A)を使って別表第二から求めます。
=XLOOKUP(社保控除後A, 別表第三[以上], 別表第三[基礎控除], 0, -1)
課税給与所得(B)
社会保険を控除した後の給与から課税給与所得金額を計算します。
社保控除後(A)から、給与所得控除と扶養控除、そして基礎控除を引いているだけです。
=社保控除後A - (給与所得控除の額 + 扶養控除の額 + 基礎控除の額)
所得税額
課税給与所得(B)の金額を使って、別表第四の「以上」の列を検索し、(B)以下の金額のセルを見つけたら、対応する、a, b を使い、税額を計算しています。
a, b, 所得税額のセルの式は次の通りです。
=XLOOKUP(I21, 別表第四[以上], 別表第四[a], 0, -1)
=XLOOKUP(I21, 別表第四[以上], 別表第四[b], 0, -1)
=ROUND(課税給与所得B * 第四a - 第四b, -1)
これで源泉徴収する所得税額を求めることができるはずです。
最後の行は10円未満の端数を四捨五入しています。
解説と検証
次の弥生会計の解説が参考になるでしょう。
給与の所得税の求め方（電子計算機の場合）：令和4年1月以降分
動作を検証する場合、次のサイトが便利でしょう。「電算機計算の特例」は「使用する」にします。
ユーザー関数化
上の計算ができたら、あとはVBAに移植するだけです。
コード
Public Function uGetTax( ByVal uKozyogo As Double, uFuyo As Long) As Variant
Dim uList1 As ListObject
Dim uList2 As ListObject
Dim uList3 As ListObject
Dim uList4 As ListObject
Dim uKyuyoKozyo As Double
Dim uFuyoKozyo As Double
Dim uKisoKozyo As Double
Dim uZeigaku As Double
Dim u1a As Double
Dim u1b As Double
Dim u4a As Double
Dim u4b As Double
Dim uB As Double
With ThisWorkbook.Worksheets("所得税")
Set uList1 = .ListObjects("別表第一")
Set uList2 = .ListObjects("別表第二")
Set uList3 = .ListObjects("別表第三")
Set uList4 = .ListObjects("別表第四")
End With
With Application.WorksheetFunction
u1a = .XLookup(uKozyogo, _
uList1.ListColumns("以上").DataBodyRange, _
uList1.ListColumns("a").DataBodyRange, 0, -1)
u1b = .XLookup(uKozyogo, _
uList1.ListColumns("以上").DataBodyRange, _
uList1.ListColumns("b").DataBodyRange, 0, -1)
uKyuyoKozyo = .RoundUp(uKozyogo * u1a + u1b, 0)
uFuyoKozyo = uList2.ListColumns("扶養控除").DataBodyRange(1) * uFuyo
uKisoKozyo = .XLookup(uKozyogo, _
uList3.ListColumns("以上").DataBodyRange, _
uList3.ListColumns("基礎控除").DataBodyRange, 0, -1)
uB = uKozyogo - (uKyuyoKozyo + uFuyoKozyo + uKisoKozyo)
u4a = .XLookup(uB, _
uList4.ListColumns("以上").DataBodyRange, _
uList4.ListColumns("a").DataBodyRange, _
0, -1)
u4b = .XLookup(uB, _
uList4.ListColumns("以上").DataBodyRange, _
uList4.ListColumns("b").DataBodyRange, _
0, -1)
uZeigaku = .Round(uB * u4a - u4b, -1)
End With
uGetTax = uZeigaku
End Function
使用例
次のようにセルに指定すれば、税額を取得することができます。
=uGetTax(社保控除後A, 扶養人数)
実際には次のような表で使う事になるでしょう。
所得税列の式は次の通りです。
=uGetTax([@社保控除後], [@扶養人数])
解説
最初に、Excel上の各表を ListObjectとして取得しています。これでVBAでテーブルを扱えるようになります。
WorkSheetFunctionを使えば、Excelの関数が使えるので、xLookUpやRoundUpなどもそのまま使えます。
テーブルの指定がわかりにくいかもしれないので解説します。
uList1 などはテーブルオブジェクトを示します。
ListColumns プロパティを使うと指定した列を扱えるようになります。
.LisColumns("以上")なら「以上」の列です。それに対して、 .DataBodyRangeを指定しています。これは列のデータ部分のセルを返すプロパティです。
これにより uList1 の場合は、0から708,331 の行までを取得することができます。
uList2.ListColumns("扶養控除").DataBodyRange(1) は、扶養控除列の1行目の値を取得しています。
Excelのシートに作る表の名前や列名を同じにしておかないと動作しません。
正直、適当に作っただけでよく検証していません。計算サイトの結果とよく照合される事をお勧めいたします。
いずれ気が向いたら税額表ベースのFunctionも作ってみようと思います。ただし、税額表はExcelでの計算に不向きのため計算範囲が限定された不完全なものになります。
電算機計算の方があらゆる給与額に対応できるため良いと思います。
最初の表を作るところが面倒かもしれませんが。ちなみに、「以下」の列は参考用なので、なくてもかまいません。
むしろ、今後の法改正に備えて、配偶者控除の列を作った方が良いのかもしれません。
税制改正されたらまた考えます。
構造化参照バージョン
いつもの調子で ListObjectを使って作りましたが、布団に入った後で、細かい処理は不要なので、テーブルを構造化参照するだけでも作れることに気が付きました。
こちらの方がよりシンプルになりました。
完全プロシージャー版
やっていることは基本的に同じです。使い方も同じです。
プロシージャー内にしきい値を直接書き込んでいます。メンテナンス性を考えるといかがなものかとは思いますが、テーブルを使う方が入力ミスや指定間違いでバグが出やすいかもしれません。また、法改正時には今の表とは大きく変わっている可能性もあるので、これでも良いのかもしれません。
ざっくりテストした感じでは問題なさそうでした。
コメント
コメントを投稿