年調給与額の計算(平成28年版)-エクセルで年末調整その１

2016年7月26日

年末調整計算用ソフトを導入していない場合、エクセルで年末調整計算をできれば便利です。

そこで、何回かに分けて、エクセルで年末調整計算をする方法を紹介していこうと思います。

給与所得の計算方法

年末調整を行うためには、まず最初に「給与収入」から「給与所得」を計算しないといけません。
給与所得の計算をするには、いくつか計算方法があるのですが、今回はいわゆる「電算機計算の特例」を使った計算方法をご紹介します。

「電算機計算の特例」による計算式は、毎年、国税庁にて公表されています。
現時点では、まだ、平成28年版は公表されていませんが、平成27年版はこちらからご覧頂けます。

給与「収入」から給与「所得」を計算する

「給与収入」というのは「給料・賞与の額面金額」を１年間合計したものを指します。

一方で、年末調整を行う場合には「給与所得」という金額を使います。
（給与収入から「一定の経費相当額」を差し引いた金額が「給与所得」というイメージです）

そこで、最初に、給与収入から給与所得を計算しないといけません。

給与所得の計算方法

給与所得は、次の２ステップで計算をします。

1. 「給与等の収入金額」から「年調給与額」を計算する
2. 「年調給与額」から「給与所得（＝給与所得控除後の給与等の金額）」を計算する

今回は、上記のうち最初のステップの「給与等の収入金額」から「年調給与額」を計算する方法を紹介します。

給与等の収入金額から年調給与額を計算する

「給与等の収入金額」（＝先ほどの給与収入と同じ意味です）から、年調給与額を計算する方法ですが、下記の計算を行います。

※上記画像は平成27年版ですが、平成28年版も同一の内容になると思いますので、下記の表を前提に計算していきます。

基本的には、次のように、この表に書かれている内容をそのまま計算式にすれば、年調給与額の計算をすることができます。

例えば、B2セルに「給与等の収入金額」が入っている場合、下記の計算式が一番素直です。

※インストラクターのネタ帳で紹介されている計算式を改変させて頂きました。

計算式の意味ですが、

１行目：「if(B2<=1618999,B2,　... ）」

もし、B2セルに入っている値が1,618,999円以下であれば、B2セルの値をそのまま使います。
そうでない場合は、２行目に進みます。

２行目：「if(B2<=1619999,B2-mod(B2-1619000,1000), ... ）」

もし、B2セルに入っている値が（1,618,999円超　かつ）1,619,999円以下の場合。
「B2-mod(B2-1619000,1000)」の計算結果を使います。

この計算の意味ですが、
先ほどの表の「年調給与額の求め方」の計算を忠実に再現しています。

先ほどの表を見ると、1,618,999円超1,619,999円以下の場合
「階差」＝「1,000円」
「同一階差の最小値」＝「1,619,000円」
ですので、

①（「給与の総額」－「同一階差の最小値」）÷「階差」の余りを計算する
　→　（B2セル－1,619,000円）÷1,000円　の余りを計算する
　→　mod(B2-1619000,1000)

②「給与の総額」－「①の余り」
　→ B2 - mod(B2-1619000,1000)

ということで、先ほどの計算式が出てくるわけです。

なお、B2セルに入っている値が1,619,999円を超える場合には、３行目以降に進むことになります。

３行目、４行目、５行目

• ３行目、４行目→「階差」「同一階差の最小値」が変わるだけで２行目と全く同じ考え方
• ５行目→１行目と全く同じ考え方

で計算をしています。

内容的には全く同じですので、説明は省略します。

もっと簡単な計算方法はないか？

上で見た計算式は、国税庁が公表している計算式を忠実に再現しているのですが、その分、計算式は、かなり複雑になってしまいました。

この計算式を、もっと簡単にできないでしょうか？

実は、先ほどの計算の「意味」を考えると、計算式は、ぐっと簡略化できます。

（再掲）

この計算の「意味」をよく考えてみると、単に一定の単位で端数を切り捨てているだけ、ということがわかります。

具体的にいうと、

1,618,999円まで

端数処理なし

1,619,000円～1,619,999円

1,000円単位で端数を切り捨て

1,620,000円～1,623,999円

2,000円単位で端数を切り捨て

例：
1,622,999円　→　1,622,000円
1,621,000円　→　1,620,000円

1,640,000円～6,599,999円

4,000円単位で端数を切り捨て

例：
1,643,100円　→　1,640,000円
4,253,200円　→　4,252,000円

6,600,000円以上

端数処理なし

このような「XX円単位で端数を切り捨てる」関数として、floor関数というのがあります。

ですからfloor関数を使うと、先ほどの考え方をそのまま計算式化でき、計算式が（少しですが）簡単になります。

でも、まだ、if関数のネストが多くて面倒くさいですよね？
今度は、もう一工夫してif関数のネストを消してしまいましょう。

計算式を、さらに単純化する

いきなり、結論を書いてしまいますが、次のような表＋計算式を組むことで、目的の計算ができます。

考え方としては、「給与等の収入金額」に応じて端数処理する単位を変えれば良いということに着目しています。

まず、「給与等の収入金額」（D列）と端数処理単位（F列）の対照表を作ります。

そして、vlookup関数で「給与等の収入金額」に応じた端数処理単位を取得して、floor関数を使っています。

なお、端数処理をしないでもいい範囲（1,618,999円以下と、6,600,000円以上の部分）については、D列に「１」を入れておくことで、適切な値を返すようにしています。

このようにして作った計算式

をB4セルに入力すれば、目的の計算ができます。

最初の計算式と比べると、かなり簡単になったことがわかると思います。

計算式を作るときに考えるべきこと

計算方法の「意味」を考えて、適切なテクニックを使うことで、計算式を大幅に単純化することができます。

計算式が単純になれば、ミスも減りますしメンテナンスも楽になるので、良いことだらけです。

どうせ計算式を組むのであれば、できるだけ「単純な計算式」を組めるようにがんばってみてください。

なお、次回発行するメールセミナー（2016年7月末頃に発行予定です）では、今回の計算式を作るために使った「発想法」を解説する予定です。

汎用的に使える「発想法」を知っておくと、様々な計算式作成に応用できます。

下記登録フォームから登録できますので、興味がある方は、ぜひ、ご登録ください！