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

アルバイトの人に給与を支払う予定です。

手取額を丸い数字（１０，０００円など）にしたい場合、源泉所得税の金額を逆算（グロスアップ）したい場合、少額は簡単にできたのですがある程度の金額を超えた時、訳がわからなくなってしまいました。
やりたいことは、
１．アルバイトに渡す手取り金額を0.96937で割った数字(a)が85,384円未満であれば、割返した数字に3.063％乗じた金額が源泉所得税額。

２．(a)の数字が85,384円以上であれば、国税庁HPから出ている税額表（HP　https://www.nta.go.jp/publication/pamph/gensen/z …）から数字を拾う。（今回は、他でお仕事されている方なので乙欄です）

３．２で求めた税額と手取額の合計額が税額表の税額と一致するまで反復計算

例　手取り18万円とすると、源泉所得税額の金額は20,900円となり、支給総額は200,900円。

↑これを簡単にExcelでできれば･･･と思っています。
毎回自力で計算していますが、間違えるのではないかと毎回ドキドキしています。

どなたかお力を貸して下さい。

よろしくお願いします。

質問者からの補足コメント

• mike_g 様
  
  すみません。日本語の文章がおかしかったです。申し訳ありません。
  具体的な数字で説明しますと・・・
  
  手取金額を0.96937で割った数字というのは、乙欄の最低税額（給与に3.063％を乗じた金額）があるので、総支給額を仮計算するのに使いました。
  
  例その１：手取り10,000円の時
  10,000÷0.96937＝10,315（85,384円未満なので、ここで計算終わり）
  　総支給額は10,315円、　税額は315円
  
  続きます･･･

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/06/23 16:00

• mike_g　様
  
  続きその１です。
  
  例その２：手取り180,000円の時
  180,000÷0.96937＝185,687円（1円未満切り捨て）
  185,687＞＝85,384なので、税額表より税額を探してくる。
  見当を付けるために、
  ①手取り金額を0.96937で割った185,687円の税額を税額表から探すと16,000円
  手取180,000＋税額16,000＝196,000円（仮の総支給額）
  仮の総支給額196,000円の税額を税額表で確認･･･19,500円
  196,000－19,500≠180,000なので税額16,000は、誤り。
  ②19,500が税額だったら？と仮定すると、仮の総支給額は手取180,000＋19,500で199,500円。
  199,500円の税額を税額表で確認･･･20,900円
  199,500－20,900≠180,000
  なので、税額19,500円も誤り。

    補足日時：2018/06/23 16:02

• mike_g　様
  
  続きその2です。
  
  ③税額20,900円ならば？と仮定すると、仮の総支給額は180,000＋20,900で200,900円
  200,900円の税額を税額表で確認･･･20,900円
  200,900－20,900＝180,000
  手取金額と一致したので、手取り180,000円の場合、総支給額200,900円　税額は20,900円
  
  とこのような計算をExcelでできれば・・・と考えています。
  
  わかりにくい質問ですみません。

    補足日時：2018/06/23 16:05

• tatsu99様
  
  お返事ありがとうございます。
  まさに私が望んでいること(表もバッチリ合っています)も、エクセルの表示も理想のものです。
  
  支給単位は100円単位で考えています。
  
  よろしくお願い致します。

  No.4の回答に寄せられた補足コメントです。 補足日時：2018/06/25 01:18

• tatsu99　様
  
  Shipping Tool　の存在を初めて知りました（本当にPCオンチなのがお恥ずかしい･･･）
  
  添付の図（あれからやっぱり支給日も欲しい！と欄を１つ追加してしまいました）のようにA列は人の名前、B列は支給日（この場合は1月）、C列に手取り金額、D列に税額、E列に総支給額･･･というように12ヶ月連続させます。
  
  こんな説明で大丈夫でしょうか・・・？

  
  No.6の回答に寄せられた補足コメントです。 補足日時：2018/06/25 15:21

• 

  tatsu99 様
  
  仰るとおり、毎月同じ人が働くわけではないので、手取り額を意図的に空白にしているのでエラーメッセージは空白だと嬉しいです。

  No.7の回答に寄せられた補足コメントです。 補足日時：2018/06/25 16:00

No.8 ベストアンサー 回答者： tatsu99 回答日時： 2018/06/25 16:24

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

この回答へのお礼

できました！！

素晴らしいです！感動しました。

ありがとうございました。

お礼日時：2018/06/25 16:37

No.7 回答者： tatsu99 回答日時： 2018/06/25 15:52

状況わかりました。

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

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

No.6 回答者： tatsu99 回答日時： 2018/06/25 14:42

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

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

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

No.5 回答者： tatsu99 回答日時： 2018/06/25 06:38

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　様

とっても詳しく教えて頂きありがとうございます！
できました！！

我が儘かもしれないのですが1年間を通じて1つのシートで計算したいので（年末の源泉徴収票作成用に）、横にどんどん追加していこうと考えたのですが、1つのシートに登録できるマクロは１つだけになるのでしょうか？
（自分で、試行錯誤してモジュールを追加してみましたが、最初のところだけが自動計算され、他の所は何も動かなかったので・・・）

イメージは、A列に氏名B列に手取り、C列に税額、D列に総支給額、E列に該当行、F列にエラーメッセージ、G列手取り、・・・と続いていくイメージです。
不可能であれば、1月ずつシートを作成し、最後に串刺し計算したいと思います。（グロスアップ計算が自動でできたことだけでも奇跡のようなものなので）

何度もすみませんが、もう一度お力を貸して頂けると大変嬉しいです。

お礼日時：2018/06/25 14:34

No.4 回答者： tatsu99 回答日時： 2018/06/24 19:12

No2です。

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

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

No.3 回答者： tatsu99 回答日時： 2018/06/24 18:06

No2です。

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

No.2 回答者： tatsu99 回答日時： 2018/06/24 17:44

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

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

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

No.1 回答者： mike_g 回答日時： 2018/06/23 15:32

確認させてください。

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