VBA で毎月の源泉徴収税を返すユーザー関数を作る(電算機計算バージョン)

Excel で給与情報から源泉徴収される所得税を求めるユーザー関数を作ってみようと思いました。

(最後の方にテーブルを使わないバージョンがあります(完全プロシージャー版)。とりあえず使うだけならそちらが良いでしょう。)


所得税の計算

所得税の計算方法は国税庁のサイトにあります。最新版は「所得税 表」などのキーワードで見つけることができるでしょう。

令和4年分 源泉徴収税額表

基本的には上のページにある「給与所得の源泉徴収税額表」を元に求めることになるでしょう。月払いの場合には次の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月以降分

動作を検証する場合、次のサイトが便利でしょう。「電算機計算の特例」は「使用する」にします。

給与所得の源泉徴収税額 令和2,3,4年(月額)


ユーザー関数化

上の計算ができたら、あとは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を使って作りましたが、布団に入った後で、細かい処理は不要なので、テーブルを構造化参照するだけでも作れることに気が付きました。


'源泉徴収所得税計算(構造化参照Ver.)
Public Function uGetTax2( _
    ByVal uKozyogo As Double, uFuyo As Long) As Variant
    
    Dim uWS As Worksheet
    
    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
    
    Set uWS = ThisWorkbook.Worksheets("所得税")
    
    With Application.WorksheetFunction
        u1a = .XLookup(uKozyogo, _
            uWS.Range("別表第一[以上]"), _
            uWS.Range(" 別表第一[a]"), 0, -1)
        u1b = .XLookup(uKozyogo, _
            uWS.Range("別表第一[以上]"), _
            uWS.Range(" 別表第一[b]"), 0, -1)
        
        uKyuyoKozyo = .RoundUp(uKozyogo * u1a + u1b, 0)
        
        uFuyoKozyo = uWS.Range("別表第二[扶養控除]") * uFuyo
        
        uKisoKozyo = .XLookup(uKozyogo, _
            uWS.Range("別表第三[以上]"), _
            uWS.Range("別表第三[基礎控除]"), 0, -1)
        
        uB = uKozyogo - (uKyuyoKozyo + uFuyoKozyo + uKisoKozyo)
        
        u4a = .XLookup(uB, _
            uWS.Range("別表第四[以上]"), _
            uWS.Range("別表第四[a]"), _
            0, -1)
            
        u4b = .XLookup(uB, _
            uWS.Range("別表第四[以上]"), _
            uWS.Range("別表第四[b]"), _
            0, -1)
        
        uZeigaku = .Round(uB * u4a - u4b, -1)
    End With
    
    uGetTax2 = uZeigaku
End Function

こちらの方がよりシンプルになりました。


完全プロシージャー版

テーブルを使わない、完全プロシージャー版も作ってみました。

Public Function uGetTax3(ByVal uA As Double, uSupported As Long) As Double
    Dim uKyuyo As Double    '給与控除
    Dim uFuyo As Double     '扶養控除
    Dim uKiso As Double     '基礎控除
    Dim uB As Double        '課税給与所得
    Dim uTax As Double  '所得税

    Select Case uA
        Case Is < 0
            uKyuyo = 0
        Case Is < 135417
            uKyuyo = 45834
        Case Is < 150000
            uKyuyo = uA * 0.4 - 8333
        Case Is < 300000
            uKyuyo = uA * 0.3 + 6667
        Case Is < 550000
            uKyuyo = uA * 0.2 + 36667
        Case Is < 708331
            uKyuyo = uA * 0.1 + 91667
        Case Else
            uKyuyo = 162500
    End Select
    uKyuyo = WorksheetFunction.RoundUp(uKyuyo, 0)
    
    uFuyo = 31667 * uSupported
    
    Select Case uA
        Case Is < 0
            uKiso = 0
        Case Is < 2162500
            uKiso = 40000
        Case Is < 2204167
            uKiso = 26667
        Case Is < 2245834
            uKiso = 13334
        Case Else
            uKiso = 0
    End Select
    
    uB = uA - (uKyuyo + uFuyo + uKiso)
    
    Select Case uB
        Case Is < 0
            uTax = 0
        Case Is < 162501
            uTax = uB * 0.05105
        Case Is < 275001
            uTax = uB * 0.1021 - 8296
        Case Is < 579167
            uTax = uB * 0.2042 - 36374
        Case Is < 750001
            uTax = uB * 0.23483 - 54113
        Case Is < 1500001
            uTax = uB * 0.33693 - 130688
        Case Is < 3333334
            uTax = uB * 0.4084 - 237893
        Case Else
            uTax = uB * 0.45945 - 408061
    End Select
    uTax = WorksheetFunction.Round(uTax, -1)
    
    uGetTax3 = uTax
End Function

やっていることは基本的に同じです。使い方も同じです。

プロシージャー内にしきい値を直接書き込んでいます。メンテナンス性を考えるといかがなものかとは思いますが、テーブルを使う方が入力ミスや指定間違いでバグが出やすいかもしれません。また、法改正時には今の表とは大きく変わっている可能性もあるので、これでも良いのかもしれません。

ざっくりテストした感じでは問題なさそうでした。


コメント

アクセス数の多い投稿

セキュリティ対策ソフトのノートンが詐欺ソフトまがいになってしまってショック

突然滅茶苦茶遅くなったPCがWindows Updateのキャッシュクリアで復活

Teamsで日本語入力すると左上に変換ウィンドウが出る

Amazon Prime Videoで4K UHD映画を検索する方法

NEC Aterm WX5400HP をセットアップ

Excel 2019 クエリが原因で日本語入力の一文字目が勝手に確定する

Microsoft Flight Simulator (2020)のPS4コントローラー設定

次期主力Wi-Fiルーター NEC Aterm WX5400HP

iPhone 12 Proと iPhone 8 Plusのサイズを比較

Excel VBAからODBCを使ってデータを簡単に取得する