Excelで乙欄の源泉徴収税額を自動計算表を作成する

こんにちは。かわべです。

Excelで源泉徴収税額表の乙欄の源泉徴収税額を自動算出する表を作成してみました。

[aside type=”pink”]この記事は平成27年の税額表を使用して作成しています。
Excelで算出表を作成する場合は、各年分の税率をよく確認してから作成してください。[/aside]

源泉徴収税額表の乙欄の仕組み

源泉徴収税額表の乙欄は、給与等の金額（※）の違いにより、次の6つの段階にわかれています。
※　正確には「その月の社会保険料等控除後の給与等の金額」といいます。

以下の画像は、国税庁；平成27年分　源泉徴収税額表から引用したものです。

①　給与等の金額が88,000円未満の場合

給与等の金額に3.063%の率を乗じて計算します。

②　給与等の金額が88,000円以上、1,010,000円未満の場合

税額表に当てはめて源泉徴収税額を確認します。

例）給与等の金額が30万円の場合

下の画像のとおり52,900円となります。

（下の画像は一部の列を非表示にしています。）

③　給与等の金額が1,010,000円の場合

税額表に当てはめて源泉徴収税額を確認します。

④　給与等の金額が1,010,000円超1,250,000円未満の場合

一定金額（396,700円）に、給与等の金額のうち1,010,000円を超える金額に40.84%を乗じて算出した金額を加算した金額になります。

⑤　給与等の金額が1,250,000円の場合

税額表に当てはめて源泉徴収税額を確認します。

⑥　給与等の金額が1,250,000円を超える場合

一定金額（494,800円）に、給与等の金額のうち1,250,000円を超える金額に45.945%を乗じて算出した金額を加算した金額になります。

Excelで自動計算する表を作成

次のようにExcelで乙欄の源泉徴収税額を自動計算する表を作成しました。

（クリックすると別タブで開きます。）

上の画像の左下の「社会保険料等控除後の金額」に給与等の金額を入力すると、金額の多寡によって、右上の表のK行の該当列に表示されます。（上の画像の赤色の枠囲み部分）

上記の例では、80,000円を入力したので、K行の2列目（88,000円未満）に給与等の金額が表示されています。

そして、K列に「0」以外の金額が表示されると税額が算出される仕組みになっています。（下の画像の緑色の枠囲み部分）

例えば、80,000円の場合は、80,000×3.063％で2,450円（円未満端数切捨て）となります。（その結果は、上の画像の黄色の背景色のC18セルにも表示されます。）

300,000円を入力の場合は、次の画像のようになります。

ADDRESS関数とMATCH関数を使ってセル位置を表示

税額の算出方法について説明します。まず、国税庁の平成27年分　源泉徴収税額表Exdelの源泉徴収税額表をダウンロードしておきます。

次に上の画像のような表を作成して、L列にADDRESS関数とMATCH関数を使用して、源泉徴収税額表を参照してK列の給与等の金額が当てはまるセル位置を表示するようにしました。

上の画像のとおり、300,000円の場合は「L127」セルが源泉徴収税額記載のセル位置となります。（下の画像の赤色の枠囲みどおり）

※　源泉徴収税額表は次の画像のとおり「月額表」とうシート名称にしてあります。

L列には次のような式を入力しました。（下記のL列の3行目の式です。）

[aside type=”boader”]「=IF(K3=0,””,ADDRESS(MATCH(K3,月額表!B10:B410,1)+9,12,1,1,”月額表”))」[/aside]

式を簡単に解説すると次のとおりです。

[aside type=”boader”]・　K3セルが0の場合は計算しません。

・　それ以外の場合は、MATCH関数によりK3セルの数字と同じ数値か、それより小さい近似値を（源泉徴収）月額表シートのB列から探します。

・　ADDRESS関数によってMATCH関数によって見つかったセルを表示します。[/aside]

INDIRECT関数を使用して税額を表示

次にL列で求めたセル位置の数値を表示するためにN列には次のような式を入力しました。（下記は、N列の3行目のセルの式です。）

[aside type=”boader”]「=IF(K3=0,0,IF(C14=0,INDIRECT(L3,TRUE),IF(INDIRECT(L3,TRUE)<C14*T3,0,INDIRECT(L3,TRUE)-C14*T3)))」[/aside]

ちょっと難しいのですが、式について解説します。

INDIRECT関数によってL3セルに入力されているセル位置の数字を表示するような式になっているのですが、乙欄の場合でも「従たる給与についての扶養控除等申告書」を提出することにより、扶養控除の計算をする場合がありますので、それも加味した式になっています。

上の式の「C14*T3」は、下の税額表の「扶養控除等の人数×1,610円」を考慮した式です。

MATCH関数とADDRESS関数は次の記事を参考にしてください。

関連記事 MATCH関数、ADDRESS関数、INDIRECT関数を使って検索する

従たる給与についての扶養控除等申告書については次の記事を参考にしてください。

関連記事 2か所の勤務先から給与の支払いを受ける場合の従たる給与についての扶養控除等申告書

※　1,010,000円以上の金額の場合も、上記の式を応用して源泉徴収税額を算出するようにしました。（詳細は省略させていただきます。）

■□◆◇　編集後記　◇◆□■

今日は、午前中は以外と寒く、午後になってやっと暖かくなりました。4月に入り電車が混んでます。