給料手取額からの源泉所得税の計算をExcelを使って逆算で･･･

Question

アルバイトの人に給与を支払う予定です。

手取額を丸い数字（１０，０００円など）にしたい場合、源泉所得税の金額を逆算（グロスアップ）したい場合、少額は簡単にできたのですがある程度の金額を超えた時、訳がわからなくなってしまいました。
やりたいことは、
１．アルバイトに渡す手取り金額を0.96937で割った数字(a)が85,384円未満であれば、割返した数字に3.063％乗じた金額が源泉所得税額。

２．(a)の数字が85,384円以上であれば、国税庁HPから出ている税額表（HP　https://www.nta.go.jp/publication/pamph/gensen/zeigakuhyo2017/01.htm）から数字を拾う。（今回は、他でお仕事されている方なので乙欄です）

３．２で求めた税額と手取額の合計額が税額表の税額と一致するまで反復計算

例　手取り18万円とすると、源泉所得税額の金額は20,900円となり、支給総額は200,900円。

↑これを簡単にExcelでできれば･･･と思っています。
毎回自力で計算していますが、間違えるのではないかと毎回ドキドキしています。

どなたかお力を貸して下さい。

よろしくお願いします。

tatsu99 · Accepted Answer

C1に計算月を指定するものとします。前回のマクロは全て破棄してください。以下のマクロを標準モジュールに登録してください。 --------------------------------------------------------- Option Explicit Const rate As Variant = 0.96937 '割率 Const Taxrate As Variant = 0.03063 '掛率 Const minVal As Variant = 85384 '総支給額の閾額 Const unit As Long = 100 '単位円 Public Sub 税額計算() Dim maxrow As Long Dim row As Long Dim getRow As Variant Dim tax As Variant Dim total As Variant Dim errmsg As String Dim result As Boolean Dim mm As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim col As Long Set sh1 = Worksheets("税額計算") Set sh2 = Worksheets("月額表") mm = sh1.Cells(1, "C").Value If mm < 1 Or mm > 12 Then MsgBox ("計算月エラー") Exit Sub End If col = (mm - 1) * 6 + 2 maxrow = sh1.Cells(Rows.Count, 1).End(xlUp).row ' A列最終行を求める For row = 5 To maxrow 'B列～E列をクリア sh1.Cells(row, col + 2).Value = "" sh1.Cells(row, col + 3).Value = "" sh1.Cells(row, col + 4).Value = "" sh1.Cells(row, col + 5).Value = "" result = GetTax(sh1.Cells(row, col + 1).Value, tax, total, getRow, errmsg, sh2) If result = True Then '正常終了なら、税額、総支給額、月額表の行番号を設定 sh1.Cells(row, col + 2).Value = tax sh1.Cells(row, col + 3).Value = total sh1.Cells(row, col + 4).Value = getRow Else '異常終了ならエラーメッセージを設定 sh1.Cells(row, col + 5).Value = errmsg End If Next MsgBox ("完了") End Sub '税額算出 Private Function GetTax(ByVal tedori As Variant, ByRef tax As Variant, ByRef total As Variant, ByRef getRow As Variant, ByRef errmsg As String, ByVal ws As Worksheet) As Boolean Dim val As Variant Dim row As Long Dim wtax As Long Dim wtotal As Long tax = Null total = Null getRow = Null errmsg = "" GetTax = False If tedori = "" Then Exit Function End If If IsNumeric(tedori) = False Or tedori < 1 Then errmsg = "入力エラー" Exit Function End If If tedori Mod unit <> 0 Then errmsg = "最小単位は" & unit & "円です" Exit Function End If val = tedori / rate val = Fix(val) '総支給額が85384円未満の場合 If val < minVal Then total = val tax = total - tedori GetTax = True Exit Function End If '総支給額が85384円以上のケース(月額表の10行～350行までトライする) For row = 10 To 350 If ws.Cells(row, "B").Value <> "" Then 'L列の税額＋手取り額を総支給額とする wtax = ws.Cells(row, "L").Value wtotal = tedori + wtax '該当条件にマッチした場合（B列の値≦総支給額＜C列の場合） If wtotal >= ws.Cells(row, "B").Value And wtotal < ws.Cells(row, "C").Value Then getRow = row tax = wtax total = wtotal GetTax = True Exit Function End If End If Next errmsg = "該当なし" End Function

tatsu99 · Answer

状況わかりました。
その場合、何月の計算を行うかを指定させた方が良いかと思います。
添付図のC1セル（黄色部分）に計算を行う月（1～１２）を指定させ、その月の該当箇所の計算を行うのはいかがでしょうか。
（例では１月の月の箇所の計算を行うことになります）
もし、C１で困る場合は、他のセルをご提示ください。

又、年間を通した場合、人の増減もありますので、その人が計算対象の月に、いないことも考えられます。
その場合、手取り額が空白になりますが、今までは、エラーメッセージに「入力エラー」を表示していました。
このケースの場合、手取り額は意図的に空白にしたと考えられるので、この場合は、エラーメッセージを空白にしたほうが良いかと
思いますがいかがでしょうか。添付図の緑の部分（名前６の人の１月分の手取り）を参照ください。

tatsu99 · Answer

>我が儘かもしれないのですが1年間を通じて1つのシートで計算したいので（年末の源泉徴収票作成用に）、横にどんどん追加していこうと考えたのですが、1つのシートに登録できるマクロは１つだけになるのでしょうか？
>（自分で、試行錯誤してモジュールを追加してみましたが、最初のところだけが自動計算され、他の所は何も動かなかったので・・・）
>イメージは、A列に氏名B列に手取り、C列に税額、D列に総支給額、E列に該当行、F列にエラーメッセージ、G列手取り、・・・と続いていくイメージです。
>不可能であれば、1月ずつシートを作成し、最後に串刺し計算したいと思います。（グロスアップ計算が自動でできたことだけでも奇跡のようなものなので）

すみません。よくわかりませんので、私が提示したような画像を提示していただけませんでしょうか？
windowsのアクセサリのSnipping Toolを使うと簡単に画像が切り取れます。
それをアップしてください。（私が提示した画像もSnipping Toolを使っています）

tatsu99 · Answer

No2です。以下のマクロを標準モジュールに登録してください。必ず、”税額計算”と”月額表”のシートを作成してください。 ”税額計算”のA列の２行目以降に手取り額を記入してください。（1行目は見出しです） "月額表"はシートのコピーを行って作成してください。（コピペはしないほうが良いかと）データのレイアウトがずれているとマクロが誤動作します。 "月額表"の10行～350行に乙の税額が記載されているので、それを対象にしています。 ----------------------------------------------------------- Option Explicit Const rate As Variant = 0.96937 '割率 Const Taxrate As Variant = 0.03063 '掛率 Const minVal As Variant = 85384 '総支給額の閾額 Const unit As Long = 100 '単位円 Public Sub 税額計算() Dim maxrow As Long Dim row As Long Dim getRow As Variant Dim tax As Variant Dim total As Variant Dim errmsg As String Dim result As Boolean Dim sh1 As Worksheet Dim sh2 As Worksheet Set sh1 = Worksheets("税額計算") Set sh2 = Worksheets("月額表") maxrow = sh1.Cells(Rows.Count, 1).End(xlUp).row ' A列最終行を求める For row = 2 To maxrow 'B列～E列をクリア sh1.Cells(row, 2).Value = "" sh1.Cells(row, 3).Value = "" sh1.Cells(row, 4).Value = "" sh1.Cells(row, 5).Value = "" result = GetTax(sh1.Cells(row, 1).Value, tax, total, getRow, errmsg, sh2) If result = True Then '正常終了なら、税額、総支給額、月額表の行番号を設定 sh1.Cells(row, 2).Value = tax sh1.Cells(row, 3).Value = total sh1.Cells(row, 4).Value = getRow Else '異常終了ならエラーメッセージを設定 sh1.Cells(row, 5).Value = errmsg End If Next MsgBox ("完了") End Sub '税額算出 Private Function GetTax(ByVal tedori As Variant, ByRef tax As Variant, ByRef total As Variant, ByRef getRow As Variant, ByRef errmsg As String, ByVal ws As Worksheet) As Boolean Dim val As Variant Dim row As Long Dim wtax As Long Dim wtotal As Long tax = Null total = Null getRow = Null errmsg = "" GetTax = False If tedori = "" Or IsNumeric(tedori) = False Or tedori < 1 Then errmsg = "入力エラー" Exit Function End If If tedori Mod unit <> 0 Then errmsg = "最小単位は" & unit & "円です" Exit Function End If val = tedori / rate val = Fix(val) '総支給額が85384円未満の場合 If val < minVal Then total = val tax = total - tedori GetTax = True Exit Function End If '総支給額が85384円以上のケース(月額表の10行～350行までトライする) For row = 10 To 350 If ws.Cells(row, "B").Value <> "" Then 'L列の税額＋手取り額を総支給額とする wtax = ws.Cells(row, "L").Value wtotal = tedori + wtax '該当条件にマッチした場合（B列の値≦総支給額＜C列の場合） If wtotal >= ws.Cells(row, "B").Value And wtotal < ws.Cells(row, "C").Value Then getRow = row tax = wtax total = wtotal GetTax = True Exit Function End If End If Next errmsg = "該当なし" End Function

tatsu99 · Answer

No2です。
もし、マクロで実現する場合の前提です。
マクロ不可の場合は、No3、及び、この補足要求の回答は不要です。
手取額を丸い数字（１０，０００円など）にしたいということですが、
具体的には1万円単位でしょうか。それとも、千円単位、百円単位、十円単位でしょうか。
手取り額を入力時に、手取り額が指定された単位の整数倍になっていなければ、エラーにすることは可能です。
その場合、いくらの単位にするかをご提示ください。

尚、手取り額が1円単位でも、税額の算出は可能です。その場合は、総支給額が1円単位になります。
どちらを選択するかは質問者様のほうで決めてください。

tatsu99 · Answer

No2です。
月額表ですが添付図のようなフォーマットです。間違いないでしょうか？

tatsu99 · Answer

マクロなら実現可能です。
添付図の「税額計算」のA列（青色部分）に手取り額を入力します。（複数行入力可能）
マクロを実行すると、B～Eに結果が表示されます。
B列：税額
C列：総支給額
D列：月額表で採用した税額の該当行
E列：エラーがあった場合のエラーメッセージ
上記のようなマクロで良ければ提供可能です。

月額表は、国税庁の「給与所得の源泉徴収税額表（月額表）（1から7ページ）」のexlcel（01-07.xls）の月額表をコピーしたものです。
（この月額表であってますか？）

mike_g · Answer

確認させてください。
》 手取り金額を0.96937で割った数字(a)が85,384円未満であれば、
》 割返した数字に3.063％乗じた金額が源泉所得税額
２つの言葉「割った数字」と「割返した数字」との違いは？
上で仰っている「割返した数字」は具体的にどう計算された数字ですか？

給料 手取額からの源泉所得税の計算をExcelを使って逆算で･･･

C1に計算月を指定するものとします。

状況わかりました。

>我が儘かもしれないのですが1年間を通じて1つのシートで計算したいので（年末の源泉徴収票作成用に）、横にどんどん追加していこうと考えたのですが、1つのシートに登録できるマクロは１つだけになるのでしょうか？

No2です。

No2です。

No2です。

マクロなら実現可能です。

確認させてください。

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

給料手取額からの源泉所得税の計算をExcelを使って逆算で･･･