経営者の方や経理部に所属している方にとって、
新年を迎えた1月の1ヶ月は、年明けの慶びだけでなく、
ある意味憂鬱さを感じる季節にもなるのではないでしょうか。

そう、それは従業員の方々に支払ってきた「給与」から
税務署に収めた所得税額を最終的に清算する
「年末調整」
の時期がやってくるからです。

パソコンで給与計算ソフト等を使っている会社さんであれば、
過程の計算は自動で行ってくれるし、
源泉徴収票の作成は、何の問題もなく終えられると思います。

しかし、従業員が少数で、自力で計算している会社さんであれば
やはり「エクセル」の力が必要になるでしょう。

ここでは、そんな経営者の方々のために、
「年調給与額」の計算から、「年調所得税額」の算出まで
エクセルで行っていった場合に、
どのような「関数」や「計算式」を使っていくのかを見ていきたいと思います。

年末調整は、あまり関係ないなぁ～・・・
と言う方も、エクセルの関数を使う場面の参考に是非ご覧になってください。

年調給与額の算出

年調給与額の求め方は、毎年税務署から送られてくる
「年末調整のしかた」
に載っています。

従業員に支払った「支払い給与」の区分の内、
1,619,000円以上6,599,999円以下は、「年調給与額」を計算することになります。

とりあえずエクセル上では、
支払い給与総額を入力すれば、年調給与額を自動計算するように設定してみたいと思います。

年調給与額の算出表を作る

「年末調整のしかた」に載っている
「年調給与額の算出表」
をエクセル上に作成してみます。

表をそのままエクセルに作成しただけです。
作成する位置はどこでも構いません。

支払い給与総額の入力枠や計算枠を作る

次に、「支払い給与総額」の入力枠と
「年調給与額」を自動計算するための数式を入力するための枠を用意します。

実際に入力するのは、①の「支払い給与総額」だけです。
後の引き算や割り算は、「こういう形で計算する」と決まっているものなので、
”なぜ、そうやって計算するのか”
という計算の根拠は全く考えなくて結構です。

さて、それでは「支払い給与総額」が「2,139,500円」だったとして話を進めていきましょう。

先程の「年調給与額の算出表」を確認してみてください。

「区分1」以上、「区分2」以下で、
「2,139,500円」が当てはまるのは以下の赤く囲んだ部分になります。

この行の「階差」と「同一階差の最小値」の数値を使用して計算していきます。

ちなみに、もし「支払い給与総額」が「1,619,500円」だったら、
「階差」は「1,000円」、「同一階差の最小値」は「1,619,000円」になります。

該当する階差と同一階差の最小値を求める

入力された「支払い給与総額」に該当する「階差」と「同一階差の最小値」を
「年調給与額の算出表」から自動で抜き出してみたいと思います。

「支払い給与総額」の区分は「年調給与額の算出表」の通り、全部で3つ(3行)あります。
入力された「支払い給与総額」が、3つの内どの行に当てはまるのかを探る必要があります。

そのため、任意のセルに「計算用セル」として、以下の関数を設定します。

「Vlookup関数」を使って、
入力された「支払い給与総額」が「区分1」と「区分2」の間に該当する行はどこか、
を探っています。

VLOOKUP(C7,F1:G4,2)

まず、設定した「Vlookup関数」の引数は全部で3つしかありませんが、
実は、「Vlookup関数」には全部で4つの引数が存在しているのです。

しかし、4番目の引数を省略すると、
完全一致ではなく、「近似値」を求めることができるようになります。

つまり、今回のように「範囲検索」をしたい場合には
4番目の引数を省略して使う、ということになります。

最初の引数の「C7」は、入力された「支払い給与総額」の値で、
この値の近似値を、2番目の引数の「F1:G4」の中から探し出します。
そして、3番目の引数に指定した「2」で、2列目の値(G列の値)を求めます。

今回の計算では、「F1:G4」の範囲から「2,139,500」の近似値を探し出し、
3行目(表タイトルを含めるので実際は4行目)の「1,624,000」を近似値として見つけました。
見つかった近似値と同じ行にある2列目(G列)の「6,599,999」という値を求めたわけです。

次に、「6,599,999」がG列の1列目(タイトル)から4行目までの内何行目に該当するのかを
「Match関数」を使って調べます。

先程の計算用セルのすぐ下に、この「Match関数」で行数を求めてみましょう。

「Match関数」で、「6,599,999」が「G1:G4」の範囲で何番目のデータと一致するかを調べます。

「6,599,999」は4行目にあるので、結果としてI8のセルには「4」と表示されます。

「階差」と「同一階差の最小値」は3列目と4列目と決まっています。

と、ここまでをまとめてみると
支払い給与総額が「2,139,500」であれば、
求める「階差」は、表の3列目(H列)と4行目の交差する「4,000」となり、
求める「同一階差の最小値」は、表の4列目(I列)と4行目の交差する「1,624,000」となることが分かります。

それでは、実際にC8に表示される「同一階差の最小値」と
C9に表示される「階差」を関数を使って設定してみましょう。

ここで使う関数は、「Index関数」です。

1番目の引数に「F1:I4」を指定し、表の範囲を設定します。
次に、2番目の引数に「Match関数」で求めた「I8」の行数を指定します。
最後に、列数である4を3番目の引数に指定します。

結果として、「C8」の「同一階差の最小値」は、
表の範囲から4行目と4列目が交差する「1,624,000」が表示されます。

同じく「C9」の「階差」にも、「Index関数」を指定します。
列数である3番目の引数に「3」を指定し、それ以外の引数は「同一階差の最小値」を表示した「C8」と同じです。

無事、「C9」には「4,000」と表示されました。

それでは、試しに「C7」の「支払い給与総額」に
「1,619,500」と入力してみてください。

「階差」と「同一階差の最小値」もきちんと表から正しいデータを拾ってくれましたね。

エラーにも対応できるようにする

今回作成した関数だと、「支払い給与総額」が「1,619,000未満」
もしくは「6,599,999を超える」場合は、関数でエラーが表示されます。

次回、「年調給与額」の残りの計算を行いつつ、
このエラーにも対処できるように関数を作り直していくことにします。