マクロ（Excel VBA）を使って源泉徴収税額表から「賞与に対する源泉徴収税額」を計算する 〜その2〜

Tweet

国税庁の HP の「平成29年分　源泉徴収税額表」のページから「 賞与に対する源泉徴収税額の算出率の表」の Excel ファイルをダウンロードすることができます。

このダウンロードした Excelファイルを改良して、マクロ（Excel VBA）を使った「賞与に対する源泉徴収税額」の計算方法をこの記事の中で説明しました。

My タックスノロジー

 

1 Pocket

マクロ（Excel VBA）を使って源泉徴収税額表から「賞与に対する源泉徴収税額」を計...

https://my-tax-nology.com/the-way-of-calculating-withholding-tax-on-bonus-by-excel-vba-part-1

法人や個人事業主などの源泉徴収義務者は、従業員への給料の支払いの際に所得税を源泉徴収して、翌月10日までに税務署（国）に納付しなければなりません。なお、従業員の数が常時10人未満の場合は「納期特例」の届出をすることにより、7月10日と1月20日の年に2回、それぞれ半年分の源泉所得税をまとめて納付するようにすることができます。 今年ももうすぐ、7月10日（月）ですね。さて、月々の給料から差し引かれる源泉徴収税額は、「給与所得の源泉徴収税額表（月額表）」から金額を計算することができます。以前に、Excel で Vlookup...

さて、上記記事では VBA のコードを全て示しましたが、「コード内のどこでどういう処理をしているか」ということについての説明まではできなかったので、今日はそのあたりについて書きたいと思います。

先回の記事と重複する部分もありますが、「賞与に対する源泉徴収税額の計算方法」から説明していきます。

なお、源泉徴収税額表の見方については Youtube に動画もアップしておりますので、よろしければどうぞ！

チャンネル登録は、こちらからお願い致します。

1、賞与に対する源泉徴収税額の計算方法

「賞与に対する源泉徴収」については国税庁の HP で確認することができます。

通常の場合の計算方法は次のように示されています↓
————————————-
（1）前月の給与から社会保険料等を差し引きます。
（2）上記（1）の金額と扶養親族等の数を「賞与に対する源泉徴収税額の算出率の表」に当てはめて税率（賞与の金額に乗ずべき率）を求めます。
（3）（賞与から社会保険料等を差し引いた金額） × 上記（2）の税率

この（3）の金額が、賞与から源泉徴収する税額になります。
————————————-

上記の流れを示しますと、まず「賞与の金額に乗ずべき率」を「賞与に対する源泉徴収税額の算出率の表」から次のX、Yを基に引っ張り出します（扶養控除等申告書の提出がある従業員の場合、甲欄を用います ）。

X、扶養親族等の数
Y、前月の社会保険料控除後の給与等の金額

賞与に対する源泉徴収税額 ＝ 社会保険料控除後の賞与の金額 × 賞与の金額に乗ずべき率

2、シート設計とExcel VBA によるプログラミング

シート設計

先回の記事と重複する部分もありますが、シート設計についてざっと説明したいと思います（詳細は前回の記事を確認下さい）。

「 賞与に対する源泉徴収税額の算出率の表」の Excel ファイルをダウンロードした際に入っている「賞与シート」に加え、「操作画面シート」を追加しました。

「賞与に対する源泉徴収税額」を計算する上で必要な入力値として、以下の5つがあります。

a､前月の給与等の総支給額（円）
b､前月の社会保険料（円）
c､賞与の総支給額（円）
d､賞与から控除される社会保険料（円）
e､扶養親族等の数（人）

上記のa、b、eの入力値を基に「賞与に対する源泉徴収税額の算出率の表」から

f､賞与の金額に乗ずべき率

を引っ張り出した後、c、d．f を基に「源泉徴収税額（円）」を計算します。

「実行」ボタンを設定して､上記①～⑤の5つの入力値を入力した後にそれをクリックすればマクロが実行するシート設計としています。

VBA によるプログラミング

これも先回の記事に載せていますが、源泉徴収税額（円）を計算するコードの全てを以下に記述します。

3、コードの説明

さて、上記に示したコードを流れに沿って説明してきたいと思います。

マクロ名

まず、マクロ名は「Gensen_Syouyo」としました。

標準モジュールの中の「Sub Gensen_Syouyo()」 と「End Sub」の間にコードを書き込みます。

————————————————
Sub Gensen_Syouyo()

    コード

End Sub
————————————————

＊なお、Sub ～ End Sub までの命令の集まりを Subプロシージャと呼びます。

変数の定義

まず、冒頭では変数を定義してあります（型は指定していない）。各々の変数の右横に説明書きをしてあるので内容を確認下さい。

まず入力値である以下の5つに相当する変数が必要です。

・Zen_Kyuyo    ‘前月の給与等の総支給額（円）’
・Zen_Syaho    ‘前月の社会保険料（円）’
・Syoyo    ‘賞与の総支給額（円）’
・Syoyo_Syaho    ‘賞与から控除される社会保険料（円）’
・Fuyou    ‘扶養親族等の数（人）’

出力値である以下の2つの変数も必要です。

・Syoyo_Ritsu    ‘賞与の金額に乗ずべき率’
・Gensen    ‘源泉徴収税額（円）

また、

・Zen_Kyuyo_1 は「前月の社会保険料控除後の給与等の金額」
・Syoyo_1 は「社会保険料控除後の賞与の金額」
・Kyuyo_Min は「前月の社会保険料控除後の給与等の金額」の範囲の下側の値
・Kyuyo_Max は「前月の社会保険料控除後の給与等の金額」の範囲の上側の値

を意味します。

入力値の読込み

次に、入力値を読み込む部分について説明します。

まず、「操作画面シート」をアクティブ（そのシートを選択して､シート上で作業をする状態）にしています。
（後にセルを指定する際に、シート名から記述しなくてよくするために）

Worksheets(“シート名”).Activate

のように記述します。シート名を “” で囲うことを忘れないようにしましょう。

「操作画面」という名前のシートをアクティブにする際にはこのように↓書きます。

Worksheets(“操作画面”).Activate

次に「操作画面シート」で入力した以下の入力値を読み込んでいます。

a､前月の給与等の総支給額（円）
b､前月の社会保険料（円）
c､賞与の総支給額（円）
d､賞与から控除される社会保険料（円）
e､扶養親族等の数（人）

入力値が入ったセルの指定は、Cells(行番号,列番号)のように記述します。

セル「D6」であれば行番号は6､列番号は4となり､Cells(6, 4)のように記述します。
＊なお、Cells(6, “D”)と書いても同じ意味になります。

それぞれの入力値が入っているセルの値を変数に読み込んでいきます。

————————————————
Zen_Kyuyo = Cells(6, 4)    ‘セル「D6」’
Zen_Syaho = Cells(7, 4)    ‘セル「D7」’
Syoyo = Cells(8, 4)        ‘セル「D8」’
Syoyo_Syaho = Cells(9, 4)    ‘セル「D9」’
Fuyou = Cells(10, 4)        ‘セル「D10」’
————————————————

また、5つの入力値を読み込んだ後「Zen_Kyuyo_1（前月の社会保険料控除後の給与等の金額）」と「Syoyo_1（社会保険料控除後の賞与の金額）」を計算しています。

————————————————
Zen_Kyuyo_1 = Zen_Kyuyo – Zen_Syaho    
Syoyo_1 = Syoyo – Syoyo_Syaho    
————————————————

扶養親族等の数の選択

「扶養親族等の数」によって、「前月の社会保険料控除後の給与等の金額」の範囲が異なり「賞与の金額に乗ずべき率」も変わってきます。

「扶養親族等の数」の場合分けには、Select Case ステートメントを用います。

Select Case ステートメントは、次のように記述します。

————————————————
Select Case 変数
    Case ○○
      処理1
    Case □□
      処理2
    Case △△
      処理3
    ・
    ・
    ・
    Case Else
      処理n
End Select
————————————————

変数の値が

・○○の場合　処理1 を行う
・□□の場合　処理2 を行う
・△△の場合　処理3 を行う
    ・
    ・
    ・
・上記以外の場合　 処理n を行う

といことを意味しています。

Case ○○　という記述の ○○の部分に条件を書きます。

「Case 1」と書けば、変数がその1と一致することを条件としており
「Case Is >=7」と書けば、変数が7以上の場合を条件とします。

扶養親族等の数が
・0の場合
・1の場合
・
・
・6の場合
・7以上の場合
に場合分けをして、それぞれの条件ごとに処理を書いてあります。

例えば、扶養親族等の数（Fuyou）が0の場合（Case 0）であれば、以下の4つの処理を行っています。

————————————————
Min_Retsu = 4
Max_Retsu = 5
Kyuyo_Min = 68000
Kyuyo_Max = 3548000
————————————————

Min_Retsu は「前月の社会保険料控除後の給与等の金額」の範囲の下限が書かれた列番号
Max_Retsu は「前月の社会保険料控除後の給与等の金額」の範囲の上限が書かれた列番号

を表しています。

扶養親族等の数ごとに、以下の赤字で書かれた列番号が Min_Retsu と Max_Retsu になるように設定しています。

Kyuyo_Min は扶養親族等の数に応じて決まる「前月の社会保険料控除後の給与等の金額」が範囲の下側の値（Kyuyo_Min 以上）
Kyuyo_Max は扶養親族等の数に応じて決まる 「前月の社会保険料控除後の給与等の金額」が範囲の上側の値（Kyuyo_Max 未満）

です。

「前月の社会保険料控除後の給与等の金額」の範囲

扶養親族等の数に応じて Min_Retsu、Max_Retsu、Kyuyo_Min、Kyuyo_Max の値を決定後は、「前月の社会保険料控除後の給与等の金額」の範囲に応じて、「賞与の金額に乗ずべき率」を抽出します。

「前月の社会保険料控除後の給与等の金額」が
範囲の下側の値を下回る場合、上側の値以上の場合

通常の範囲にある場合

通常の範囲にある場合は、行番号が10行目～34行目に「前月の社会保険料控除後の給与等の金額」の範囲が書かれているので、行番号を変数 iにより10から34まで1つずつ増加させて範囲が当てはまったら、同じ行のB列（2列目）から「賞与の金額に乗ずべき率」を拾い出します。

———————————————–
‘「前月の社会保険料控除後の給与等の金額」が通常の範囲にある場合’
    For i = 10 To 34

        If Zen_Kyuyo_1 >= Cells(i, Min_Retsu) * 1000 And Zen_Kyuyo_1 < Cells(i, Max_Retsu) * 1000 Then

            Syoyo_Ritsu = Cells(i, 2) / 100
            Exit For
        Else
        End If

    Next i
———————————————–
Zen_Kyuyo_1 が Cells(i, Min_Retsu) * 1000 以上、Cells(i, Max_Retsu) * 1000 未満の範囲にある場合
Syoyo_Ritsu を Cells(i, 2) / 100 としています。

なお、範囲は千円単位で書かれているため、 Cells(i, Min_Retsu) * 1000 のように 1000を掛けて円単位に換算しています。

Syoyo_Ritsu は％を割合に換算するため、Cells(i, 2) / 100 のように 100で割っています。

出力値の計算と書出し

最後に出力地の計算と書出しになります。

源泉所得税額を計算して、「操作画面シート」に書き出します。

Gensen = Int(Syoyo_1 * Syoyo_Ritsu) 　の計算式によって、Syoyo_1（社会保険料控除後の賞与の金額）に先ほど拾い出した Syoyo_Ritsu を掛けています。

また Int 関数を用いて Int(Syoyo_1 * Syoyo_Ritsu)　と記述することによって、Syoyo_1 * Syoyo_Ritsu　の計算結果の小数点以下を切り捨てることができます。

4、まとめ

以上、マクロ（Excel VBA）を使って源泉徴収税額表から「賞与に対する源泉徴収税額」を計算する方法について、コードの詳細を説明しました。

VBA　を使わなくても、Excel 関数で計算する方法もあるかもしれませんが、いろいろ考えるとVBAを使う方法が一番すっきりすると感じました。

先回の記事から順を追って内容を確認頂くと分かりやすいと思います。

なお、源泉徴収税額表の見方については Youtube に動画もアップしておりますので、よろしければどうぞ！

チャンネル登録は、こちらからお願い致します。