ブログ

配偶者控除のExcel計算はINDEX関数とMATCH関数

税制改正後により配偶者控除、配偶者特別控除の計算が複雑化しました。
INDEX関数とMATCH関数を組み合わせれば
Excel計算が可能です。

 

2018年より、配偶者控除の計算が複雑化

2017年度の税制改正により、
2018年から、所得税の計算上、
・配偶者控除
・配偶者特別控除
の計算方法が変わりました。

これがなかなかに厄介です。

たとえば、国税庁ホームページにおける、
配偶者特別控除の表はこちら。

https://www.nta.go.jp/taxes/shiraberu/taxanswer/shotoku/1195.htm

目が痛くなります。。

しかも、配偶者控除の方も、
老人対象控除配偶者の場合の控除額が一律10万円ではなく、
本人の所得金額に応じて、段階的に下がることになりました。

https://www.nta.go.jp/taxes/shiraberu/taxanswer/shotoku/1191.htm

 

以前はこんなに複雑ではありませんでした。

複雑になった原因は、控除額決定の要素として、
①配偶者の所得金額
②本人の所得金額
の2点が絡み合うようになったからです。
(従前は、①のみ)

 

Excelで控除額を計算する方法

このような場合、
控除額の対応表をつくり、
INDEX関数とMATCH関数を組み合わせれば、
うまく控除額が取り出せます。

①控除額の対応表をつくる

こんな感じです。

①配偶者の所得金額
②本人の所得金額
の2つの条件によって控除額が変わる

という表です。

たとえば、
①配偶者の所得金額:860,000円
②本人の所得金額:9,600,000円
であれば、控除額は120,000円になります。

配偶者控除と配偶者特別控除は別々に計算せず、
ひとつの表に集約します。
その方がスッキリ。

一方で、老人対象控除配偶者については、
老人加算として、一般の控除額からは分離しました。
(元ネタは国税庁のこちら。)

 

②一般控除額の関数

次に、配偶者控除と配偶者特別控除の
(老人加算の無い)一般の控除額を計算する関数を組みます。

=INDEX(E7:H17,MATCH(E27,C7:C17,1),MATCH(E26,E5:H5,1))
という関数を組んでいます。

本人所得と配偶者所得は別途入力して出すので、所与。
そこにINDEX関数とMATCH関数を絡ませて、控除額を取り出します。

説明のために加工したのがこちら。

INDEX関数

INDEX関数は、=INDEX(範囲,縦位置,横位置)という構造を取ります。

「指定の範囲の中で、
縦位置について、上から●番目、
横位置について、左から●番目
のセルを取り出す」

という意味です。

範囲はそのまま指定すればいいとして、
縦位置、横位置の「●番目」を指定するために、MATCH関数を組合せます。

 

MATCH関数

MATCH関数は、=MATCH(検索値,範囲,一致or近似)です。

「検索値は、指定の範囲の中で、●番目」

というもの。

一致or近似は、0か1か-1のいずれかですが、
ここでは1を指定します。

(検索値に一致するものが無い場合は)
「検索値より小さい中で、最も近いものを検索値とみなす」

が1の意味です。

この例でいえば、検索値である860,000は、
指定した範囲の中にはありません。

そこで、「検索値より小さい中で、最も近いもの」として、
850,001に白羽の矢が立って、
代役として指定されることになるのです。
この場合、MATCH関数で返されるのは「3」(番目)になります。

このようにして、
INDEX関数の縦位置、横位置にそれぞれに
MATCH関数を入れ込むことによって、
適切な控除額が取り出されることになります。

 

③老人加算の控除額の関数

老人加算についても、一般の場合とほぼ同様です。

説明加工バージョンはこちら。

=IF(E28=”○”,
INDEX(E21:H22,MATCH(E27,C21:C22,1),MATCH(E26,E19:H19,1))
,0)

という関数を組んでみました。

老人加算は、配偶者が、
その年12月31日現在の年齢が70歳以上である場合にのみ該当しますので、
その判定を予めしてもらわないといけません。

その入力方法はいろいろありますが、
今回のケースでは、「○」「×」を選択するセルをつくっておいて、
IF関数をつかって、
「○」だった場合にのみ控除額を取り出す
ということをしています。

この辺のやり方はいろいろあるかと。

 


編集後記

昨日はお客様の会計入力効率化の支援。
お客様ごとに確立されたやり方があるので、
そちらを尊重しつつ、
かつ失敗リスクの少なく、
圧倒的に効率的な方法を提案する!
効率化の道、けっこう険しい!!

昨日の一日一新

Amazonビジネスアカウント

PAGE TOP