会社が、年末調整で各従業員の年調年税額を求める過程をエクセルの関数で行った場合どういう流れになるか・・・
前回、前々回と2回に渡り「年調給与額」までを算出してきました。
シリーズでお送りしている第3回目。
今回は求めた「年調給与額」を元に、「給与所得控除」の計算を行ってみたいと思います。
給与所得控除後の計算式を作る
算出された「年調給与額」から給与所得控除を行うのですが、この計算式は決まったものを与えられます。
とりあえず、エクセルのシート上にこの計算式の一覧を作成してみましょう。
作成する表は赤く囲った範囲になります。
表の位置は自由で構わないのですが、自分で表の位置を変えた場合は、関数に指定するセル番地も適宜変えるようにしてください。
上の図で言えば、F列とG列は文字や数値をそのまま入力します。
H列は、表の項目の文字列(給与所得控除後の給与等の金額を求める計算式)はそのまま入力しますが、数値は計算式が入っています。
説明上、I列に青文字で「H列に入力された計算式」を記載していますのでこの通りに入力してみてください。
I列は説明用なので、H列に数式が入力できれば、I列を入力する必要はありません。
H列に入力する計算式で出てくる「C17」というセル番地には、上の図のように、前回算出した「年調給与額」の「C13」セルをコピーした値が入っています。
年調給与額による給与所得控除後の給与を算出する
先程作成した一覧表から、「年調給与額」(C17)が、「区分始まり」以上「区分終わり」以下に該当する「給与所得控除後の給与等の金額を求める計算式」を探し出します。
このシリーズでは、支払い給与総額が「2,139,500円」だったとして計算を始めました。
そこから前回まで、「年調給与額」は「2,136,000円」と算出されました。
つまり、この「2,136,000円」が該当する「区分始まり」以上「区分終わり」以下の行に当てはまる「給与所得控除後の給与等の金額を求める計算式」を求めることになるわけです。
上の図のように、「2,136,000円」は「1,800,000円」以上「3,599,999円」以下に該当し、結果として「C18」セルに「1,315,200円」を表示させたいことになります。
そこで、今回は「配列数式」を使って計算してみたいと思います。
配列数式
「配列数式」とは、複数のセル範囲を対象に1つの数式でまとめて結果を求める場合に使います。
「配列数式」が使えるようになると、エクセルでの作業も格段に楽になるかもしれません。
まずは、もし「配列数式」を使わなかったら、関数はどのようになるかを見てみましょう。
Excel2016より前のバージョンの場合
方法はいくつかありますが、例えば作業用セルで「年調給与額」が「区分の始まり」と「区分の終わり」の間にあるかどうかを判断し、作業用セルにとりあえず「〇」か「×」かを表示するように関数を作ってみます。
表の手前の「E列」に作業用セルを確保します。
年調給与額(C17)が「区分始まり」以上「区分終わり」以下であれば「〇」そうでなければ「×」を表示するようにIF関数とAND関数を組み合わせます。
「E27」までフィルハンドルで関数をコピーするため、「年調給与額」である「C17」は絶対参照にします。
結果を表示する「C18」には、「Vlookup関数」を使います。
E17：H27の表の範囲から先頭列の「〇」を探し出し、その並びの4列目(H列)の値を表示します。
結果として「1,315,200」が表示されます。
Excel2016もしくはそれ以降のバージョンの場合
最近のエクセルの場合、新しく登場した「IFS」関数を使うことができます。
これは、「IF関数」が発展したバージョンで、「IFS関数」の引数に、「条件が当てはまる場合」の「処理」をずらずらと指定することができる関数となります。
アドレスバーに入力した「IFS関数」を見やすいように、書き出してみました。
「IF関数」を使ったときのような関数が入れ子になる見づらい形にならず、後で見返してみても、すっきりとした形で書けていることが分かると思います。
それでも、11通りを条件検索するので、関数自体は長くなってしまいますね。
配列数式を使う場合
それでは、配列数式を使うとどのようになるかを見てみましょう。
まず配列数式を使う場合に、指定する引数の列数・行数はすべて同じであることが条件となります。
上の式で言えば、行数が11行(17:27)、列数が1列(F,G,H)となります。
「(C17>=F17:F27)*(C17<=G17:G27)」の式の途中に「*」がありますが、これは、「AND」を意味します。
実は、配列数式内では「AND関数」や「OR関数」を使うことができません。
論理積である「AND」を配列数式内で使う場合は、上のように条件分を「*」で繋いで使うと覚えてください。
一方、論理和である「OR関数」を配列数式内で使う場合は、条件分を「＋」で繋ぎます。
つまり、IF関数内の数式の意味は次の図のようになります。
分解してみると、「Excel2016より前のバージョンの場合」で見たように作業用セルに入力した関数の形と似ていますよね。
配列数式を使うと、数式がシンプルになるのです。
そして、条件に該当したH17からH27までの数値を足し算して結果を表示します。
足し算していますが、該当する行は1行しかありませんから必ずその行のH列の値が結果として表示される、というわけですね。
作業用セルを確保したり、関数式が長くなったりせず、コンパクトな数式で一気に計算できる、これが、配列数式を使う醍醐味となります。
なお、配列数式を作成するときには、上の例で言えば、
=SUM(IF((C17>=F17:F27)*(C17<=G17:G27),H17:H27))
と入力した後に、キーボードで「Ctrlキー+Shiftキー」を押しながら、「Enterキー」を押すと、「配列数式」として認識されるようになります。
配列数式の場合は、全体が｛｝で囲まれることになります。
給与等の金額が決定
実際の「支払い給与額」が「2,139,500円」だったのが、「年調給与額」を経て「給与所得控除」を計算すると、現時点で、この従業員は税務上「1,315,200円」という給与をもらった、ということになるわけです。
動作確認は次回まとめ
試しに、「支払い給与額」に違う金額を入れてみます。
「支払い給与額」に「1,500,000円」と入力したら、これまで構築してきたセルで自動で計算され、給与所得控除後の給与等の金額が「850,000円」になると、瞬時に計算ができました。
さて、次回でようやく最終回になると思います。
次回は、所得控除を行って、最終的に収める税金となる「年調年税額」を算出します。
お楽しみに！