面倒な所得拡大促進税制をエクセルで簡単に集計する

所得拡大促進税制は、会社にとってはうれしい反面、税理士事務所にとっては面倒だし、追加料金も請求しにくいしで、非常に頭が痛い税制です。

でも、元データをきちんと入手できて、エクセルで処理する「アイデア」さえあれば、10分程度の短時間で集計ができるようになります。

※別ページにて、ダウンロード可能なエクセルシートを公開しました。
詳細は、【手間を極限まで減らす】所得拡大促進税制計算用エクセルシートをご覧ください。

所得拡大促進税制の元データを集計

私が所得拡大税制に必要なデータの集計をするときは、ピボットテーブルを使っています。

まずは、給与明細と賞与明細から必要な情報を切り出して、加工。
その後、ピボットテーブルで集計することで、簡単に申告書に入力するデータが作れます。

ピボットテーブルで使う「元データ」を準備する

まず、給与明細など給与の支給額がわかるデータを入手します。
そして、「集計用の元データ」を作ります。

集計用の元データ
syotokukakudai_1_1

必要な情報は、

  • 社員名(あるいは社員コード)など、同一人物であることを特定できる情報
  • 年度
  • 支給月
  • 支払金額
  • 集計対象給与かどうか?(例えば、雇用保険加入・役員関係以外の人などで集計対象外の場合は「0」、それ以外の場合は「1」を入れる)

の5つです。

給与明細には、膨大な情報が入っていると思いますが、必要なのは上記5つだけなので、必要ない項目は削除しても構いません。

元データを準備するときのポイント

元データを準備するときのポイントがいくつかあります。

a)給与・賞与の別

集計自体では、給与・賞与の区別をつける必要はありません。
ですから、集計だけのことを考えれば、給与・賞与の区分は必要ありません。

ただし、後で見返すときのために、先ほどの4つの項目に加え「給与・賞与の別」の列をつけても構いません。

syotokukakudai_1_2

b)支給月の入力

給与・賞与の両方とも支給月を入れます

支給月は「YYYYMM」の形で入力するのがおすすめです。

たとえば、大元の給与明細をエクセル化したデータで、支給日がシリアル値の形でA1セルに入っているのであれば、

=text( A1 , "YYYYMM")
または
=year(A1)&right("0" & month(A1) )

といった式を入れれば「YYYYMM」の形に変換できます。

ピボットテーブルで集計する

このデータをピボットテーブルで集計します。
(ピボットテーブルの操作方法や考え方そのものは、初心者向けピボットテーブル解説をご覧ください)

集計時は、

  • 値:支払額
  • 列:従業員名
  • 行:年度と支給月
  • フィルタ:対象

で集計をかけます。

syotokukakudai_1_3

ピボットテーブル集計が損益計算書と一致しているか確認をする

集計手法が正しくても、元データが誤っていたら意味がありません。

そこで、この段階で作業の手を止めて、ピボットテーブルで集計したデータと損益計算書とが合っているかチェックをします。

syotokukakudai_1_4

もし何らかの原因で、

  • ピボットテーブルの各年度別の給与合計
  • 損益計算書の給与

この2つが一致していない場合には、元データを見直しましょう。

一致しない要因として、よくあるのが「未払給与」です。
未払給与が含まれていないなどあれば、
元の給与データに織り込んでしまいましょう。

雇用保険未加入・役員などへの支給額を除外する

損益計算書との整合性が取れましたので、

次に、下記のような人の給与を抜きます。

  • 雇用保険未加入
  • 役員・役員の家族

先ほどのピボットテーブルを作ったシートを丸ごとコピーして、「対象」欄が「1」のものだけを表示させます。

syotokukakudai_1_5

これで、雇用保険未加入者、役員・役員の家族などの分の給料を除外できます。

年度別の支給月数・支給金額を出す

ピボットテーブルの中で、
年度別の給与支給合計は17行目、31行目に出ていますが、
年度別の給与支給月数が出ていないので、計算します。

計算自体は単純で、count関数を使って計算をします。

syotokukakudai_1_6

支給金額自体は、すでにピボットテーブルで計算されていますが、
それを計算式で転記します。

syotokukakudai_1_7

継続雇用者の判定

継続雇用者の判定は、
前年度、今年度の両方で給与の支給実績があるかどうかで判定します。

syotokukakudai_1_8

今回は、
「C33セルとC34セルのどちらかが0」であれば
「(C33セル)×(C34セル)=0」になる、
ということを使って、計算式を入れています。

意味としては、or関数を使って次のような計算式を入れるのと一緒です。

=if(or(C33=0,C34=0), 0, 1)

or関数を使うほうがわかりやすいようであれば、もちろん、こちらの計算式でも問題ありません。

継続雇用者の給与支払月数・給与支給額を計算する

33行目~34行目の給与支払月数
36行目~37行目の給与支払月数について、
sumif関数を使って、継続雇用者分のみを集計します。

syotokukakudai_1_9

これで、必要な情報の集計ができました。

あとは、
集計したデータを申告ソフトに入れれば業務完了です。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。