前回は、「支払い給与総額」を入力すると、
「計算用セル」、そして「階差」、「同一階差の最小値」に該当の数値が自動で入力されるように
関数などの数式を設定しました。
今回は、入力された「支払い給与総額」が
「1,619,000円未満」の時と「6,599,999円を超える」時に
エラーが出力されてしまう場合の対処と、
最初の「年調給与額」の算出までを見ていきたいと思います。
目次
支払い給与総額の入力に条件を付ける
入力された「支払い給与総額」による
「階差」と「同一階差の最小値」を3つの中から探し出すように設定してきたわけですが、
もし、「支払い給与総額」が「1,619,000円未満」、もしくは「6,599,999円を超える」場合は、
「支払い給与総額」がそのまま「年調給与額」となり、「同一階差の最小値」を求めて計算をする必要はありません。
上のように、「支払い給与総額」に「1,618,999」と入力すると、
VLOOKUP関数やMATCH関数、INDEX関数が入力されたセルにエラーが表示されます。
これは、「I7」に設定している「VLOOKUP関数」において、
「F1:G4」の範囲で近似値が探せないためにエラーとなるのが原因となります。
他にも、「支払い給与総額」に何も入力されていないような場合にも
同様にエラーが表示されます。
そのため、エラーとならないように設定した関数にもう1手間加えていきます。
IF関数とOR関数を使って入力条件を設定する
「支払い給与総額」に設定したい
「1,619,000円未満」、もしくは「6,599,999円を超える」場合の処理は、以下のように
IF関数とOR関数を使います。
IF(OR(C7<1619000,C7>6599999),””,VLOOKUP(C7,F1:G4,2))
IF関数で、OR関数に指定した条件を満たせば、セルに何も表示させないようにします。
つまり、「1,619,000円未満」、もしくは「6,599,999円を超える」場合は、I7のセルは空になる、ということです。
それ以外の場合に、VLOOKUP関数が実行されるようになります。
上の画像のように、「支払い給与総額」に何も入力されていない場合でも、
OR関数で設定した「1,619,000円未満」、もしくは「6,599,999円を超える」条件を満たさないため、
「I7」のセルは何も表示されなくなった、というわけです。
同様に、「I8」セルも以下のようにIF関数を付け加えます。
IF(I7=””,””,MATCH(I7,G1:G4))
先程IF関数とOR関数を付け加えた「I7」のセルが空の場合、
この「I8」のセルがエラーとなるわけですから、
「I7」のセルが空であれば、「I8」も空にするようにIF関数で設定しています。
「階差」と「同一階差の最小値」もそれぞれ、IF関数を使ってエラーを表示させないようにします。
IF(I8=””,””,INDEX(F1:I4,I8,4))
「I8」が空かどうかを判断して、
空でなければINDEX関数が実行されるようにしています。
「支払い給与総額」に「6,660,000」と入力してみると、
「1,619,000円未満」、もしくは「6,599,999円を超える」条件に該当するので、
これまで設定してきたセルがすべて空となり、何も表示されないことが確認できましたね。
支払い給与総額から同一階差の最小値を引く
次に、「支払い給与総額」(①)から「同一階差の最小値」(②)を引き算します。
セルは「C10」(④)に表示します。
ここは、単純に数式として
「C7-C8」と入力すれば良さそうですが・・・
「#VALUE」というエラーが表示されてしまいました。
これは、「C8」が「””(空文字)」という文字列となっているために、
計算ができずにエラー表示されたものになります。
「C8」が「””(空文字)」だったら、この「C10」も「””(空文字)」にしてしまいましょう。
上の図のように、IF関数を使って条件分岐します。
MATCH関数やINDEX関数で条件分岐させた方法と同じですね。
「C8」が「””(空文字)」でなければ、単純に「C7-C8」という引き算をします。
試しに、「支払い給与総額」に「1,000,000」と入力してみました。
「1,619,000円未満」なので、この金額がそのまま「年調給与額」になります。
つまり、何も計算させないので、これまで関数を設定してきたすべてのセルが「””(空文字)」となっています。
「支払い給与総額」に「2,139,500」と入力すると、
各セルには計算された結果が上のように表示されます。
割り算の商を求める
さて、次の計算過程として
先程求めた「支払い給与総額 – 同一階差の最小値」(④)から
「階差」(③)を割った「余り」を求める必要があります。
割り算の「商」は計算過程としては必要ないのですが、
せっかくなので、まずは「④ ÷ ③」の商を求める関数を設定してみたいと思います。
割り算の商は、「QUOTIENT関数」を使って求めることができます。
「C11」のセルに、QUOTIENT関数を記述し、引数に「C10」と「C9」を設定します。
上の図で言えば、「515,500 ÷ 4,000」の商である「128」を求める、と言う意味になります。
これまでと同様に、「””(空文字)」に対応するように
IF関数で条件分岐をさせておきましょう。
割り算の余りを求める
本来、計算に必要な式はこちらの「割り算の余り」の方です。
「C12」のセルに「④ ÷ ③」で計算される「余り」(⑥)を表示してみます。
「割り算の余り」を求める場合は、「MOD関数」を使います。
「C12」のセルに、MOD関数を記述し、引数に「C10」と「C9」を設定します。
上の図で言えば、「515,500 ÷ 4,000」の余りである「3,500」を求める、と言う意味になります。
また、割り算の商を求めた時と同様に、「””(空文字)」に対応するように
IF関数で条件分岐をさせておきましょう。
年調給与額を算出する
さぁ、やっと「年調給与額の算出」までやってきました。
「年調給与額」は、「支払い給与総額」(①)から直前で求めた(⑥)を引くことで値を求めることができます。
ただし、一つ注意があります。
それは、「支払い給与総額」が
「1,619,000円未満」、もしくは「6,599,999円を超える」場合は、
「支払い給与総額」がそのまま「年調給与額」になる、と言う点です。
それでは、順番に見ていきましょう。
「年調給与額」を求めるセルは、「C13」になります。
このセルに、最終的に「C7 – C12」という引き算の答えが求められれば完成となります。
しかし、これまでと同様に、単純に「C7 – C12」という
引き算の式を入力しただけでは、C12が「””(空文字)」の場合は、
計算ができない、と言うエラーが表示されます。
したがって、上のようにIF関数で条件分岐をすれば、
「””(空文字)」の場合の対処はできます。
問題は、その次ですが、
もし、「支払い給与総額」である「C7」のセルに
「1,619,000円未満」、もしくは「6,599,999円を超える」金額が入力された場合は、
その値をそのまま「C13」のセルに表示したいのです。
現在、「C13」のセルに入力された
「IF(C8=””,””,C7-C12)」
という関数や式では、その対応ができません。
上の図のように、もし「支払い給与総額」に「1,000,000」と入力された場合は、
「I7」や「I8」の計算用セルが「””(空文字)」になり、
「C8」も「””(空文字)」になり、最終的に「年調給与額」である「C13」までもが
「””(空文字)」として終わってしまうのです。
そのため、関数は以下のように入力します。
IF(OR(C7<1619000,C7>6599999),C7,C7-C12)
OR関数で「1,619,000円未満」、もしくは「6,599,999円を超える」金額
を設定し、IF関数でOR関数の条件に当てはまれば、そのまま「C7」を「C13」に表示させるようにします。
「C7」に何も入力されていない場合は、「0」として扱われますので
「C13」には、上の図のように「年調給与額」が「0」と表示されています。
「支払い給与総額」に、「2,139,500」を入力すると
関数によって自動で計算され、最終的に
「年調給与額」は「2,136,000」となることが分かりました。
また、「1,619,000円未満」となる「1,500,000」を入力した場合は、
そのまま「C13」にも「1,500,000」が表示されるようになりましたね。
入力規則
今回の年末調整のお話は、関数をメインに扱っているので
簡単に触れるだけにしますが、もし支払い給与総額の「C7」に文字列が入力されると
以下のようになってしまいます。
OR関数の条件に当てはまってしまい、そのまま
「C7」に入力された「aaa」が「C13」にも表示されてしまうのです。
それを回避するために、「C7」にエクセルではおなじみの
「入力規則」で整数だけ入力ができるように縛りをかけてしまう方法もあります。
もちろん、人に使わせるわけではなく”自分だけ”がこの計算シートを使うのであれば、
こういった入力の制限をかけることは不要かもしれませんけどね。
次回は、最終の「年調年税額」の算出まで行けるところまで行きます！
