経営者の方や経理部に所属している方にとって、新年を迎えた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」と入力してみてください。
「階差」と「同一階差の最小値」もきちんと表から正しいデータを拾ってくれましたね。
関数を入れ子にしてもいい
今回の計算では、「I7」と「I8」に計算用セルを用意しましたが、もちろん、「C8」に以下のように関数をすべて並べても問題ありません。
説明のために、関数を各セルに分けましたが、どちらの方法でも結果は同じになります。
エラーにも対応できるようにする
今回作成した関数だと、「支払い給与総額」が「1,619,000未満」もしくは「6,599,999を超える」場合は、関数でエラーが表示されます。
次回、「年調給与額」の残りの計算を行いつつ、このエラーにも対処できるように関数を作り直していくことにします。