情報整理術の数々! PICK UP!

年末調整で求める年調年税額をエクセルの関数を交えて算出してみる!―その1―

経営者の方や経理部に所属している方にとって、
新年を迎えた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を超える」場合は、関数でエラーが表示されます。

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

関連記事

  1. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編⑤

  2. 【シリーズ:人より一歩先行くエクセル技を身につける】ピボットテーブル編[番外]

  3. 【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】条件分岐を楽にできるSWITCH関数と…

  4. エクセルに標準搭載された3Dマップを活用する![その4]―フィールドリストが重複したら

  5. 年末調整で求める年調年税額をエクセルの関数を交えて算出してみる!―その2―

  6. 【シリーズ:一昔前のExcel(エクセル)で停滞している方へ】進化し続けるエクセルのIF関数を紐解く…

  7. エクセルに標準搭載された3Dマップを活用する![その3]―ツアー動画をYouTubeにアップしてみる…

  8. 年末調整で求める年調年税額をエクセルの関数を交えて算出してみる!―その3―

  9. 年末調整で求める年調年税額をエクセルの関数を交えて算出してみる!―その4―

特集記事!

  1. iPadOS13で強化された「Slide Over」と「Split View」!…
  2. 【連載3】Lucidchartで作成した図面をGoogleの文書やシートに挿入す…
  3. 【連載2】Lucidchartで図面作成からプレゼンテーションまですべて完結!プ…
  4. 【連載1】Lucidchartの図をワードやエクセルに挿入する―マイクロソフトイ…
  5. 【連載序章】よい図面作成アプリが見つからない現場担当者必見!Lucidchart…
  6. 簡単設定と分かりやすい料金プランで国内・国外”どんなときも”Wi-Fiが繋がるな…
  7. マイクロソフト発StickyNotesで覚えておきたい設定・使い方8選
  8. 【OneNote for Windows10】StickyNotesが使用可能に…
  9. iPadで手書きのメモを取る!ApplePencilが使える無料のメモアプリ5選…
  10. 楽天銀行の法人ビジネス口座を持っているなら無料で使えるクラウドアプリを使ってみよ…

人気記事 PickUp!

ー文字だけで語る(あつもじ)ー

おすすめ記事

ピックアップ!

  1. 【OneNoteへGo】Office製品のOutlook連絡帳をOneNoteの…
  2. 【ちょっと休憩】検索したいわけじゃないけどつい弾きたくなる「弾いちゃお検索」とは…
  3. 【マイクロソフト To-Do】パソコンとスマホで”やること”をシンプルに管理する…
  4. 【Google ToDo リスト】Gmailからも起動OK!Googleの予定管…
  5. ワールドカップには嘘のような法則が存在する?!
  6. 【リメンバーザミルク】海外製アプリながら使いやすいインターフェースで人気のタスク…
  7. これは使ってみたい!3つのTo-doリストを比較してみた
  8. 絵心がないからデザインは苦手っ・・・そんなあなたは「Canva」があればもう大丈…
  9. 経県値―自分がどれだけの都道府県に足を踏み入れたのかが簡単に分かるスマホアプリ
  10. とうとう正式リリース!マイクロソフトの「Whiteboard」アプリの使い心地は…

↓OneDriveの便利な機能が満載!↓

icon-cogマイクロソフトのクラウドストレージである「OneDrive」の便利な使い方をご紹介しています。個人契約でしか利用できない機能もあります。

PAGE TOP