面倒な所得拡大促進税制をエクセルで簡単に集計する
所得拡大促進税制は、会社にとってはうれしい反面、税理士事務所にとっては面倒だし、追加料金も請求しにくいしで、非常に頭が痛い税制です。
でも、元データをきちんと入手できて、エクセルで処理する「アイデア」さえあれば、10分程度の短時間で集計ができるようになります。
※別ページにて、ダウンロード可能なエクセルシートを公開しました。
詳細は、【手間を極限まで減らす】所得拡大促進税制計算用エクセルシートをご覧ください。
この記事の目次
所得拡大促進税制の元データを集計
私が所得拡大税制に必要なデータの集計をするときは、ピボットテーブルを使っています。
まずは、給与明細と賞与明細から必要な情報を切り出して、加工。
その後、ピボットテーブルで集計することで、簡単に申告書に入力するデータが作れます。
ピボットテーブルで使う「元データ」を準備する
まず、給与明細など給与の支給額がわかるデータを入手します。
そして、「集計用の元データ」を作ります。
必要な情報は、
- 社員名(あるいは社員コード)など、同一人物であることを特定できる情報
- 年度
- 支給月
- 支払金額
- 集計対象給与かどうか?(例えば、雇用保険加入・役員関係以外の人などで集計対象外の場合は「0」、それ以外の場合は「1」を入れる)
の5つです。
給与明細には、膨大な情報が入っていると思いますが、必要なのは上記5つだけなので、必要ない項目は削除しても構いません。
元データを準備するときのポイント
元データを準備するときのポイントがいくつかあります。
a)給与・賞与の別
集計自体では、給与・賞与の区別をつける必要はありません。
ですから、集計だけのことを考えれば、給与・賞与の区分は必要ありません。
ただし、後で見返すときのために、先ほどの4つの項目に加え「給与・賞与の別」の列をつけても構いません。
b)支給月の入力
給与・賞与の両方とも支給月を入れます。
支給月は「YYYYMM」の形で入力するのがおすすめです。
たとえば、大元の給与明細をエクセル化したデータで、支給日がシリアル値の形でA1セルに入っているのであれば、
または
=year(A1)&right("0" & month(A1) )
といった式を入れれば「YYYYMM」の形に変換できます。
ピボットテーブルで集計する
このデータをピボットテーブルで集計します。
(ピボットテーブルの操作方法や考え方そのものは、初心者向けピボットテーブル解説をご覧ください)
集計時は、
- 値:支払額
- 列:従業員名
- 行:年度と支給月
- フィルタ:対象
で集計をかけます。
ピボットテーブル集計が損益計算書と一致しているか確認をする
集計手法が正しくても、元データが誤っていたら意味がありません。
そこで、この段階で作業の手を止めて、ピボットテーブルで集計したデータと損益計算書とが合っているかチェックをします。
もし何らかの原因で、
- ピボットテーブルの各年度別の給与合計
- 損益計算書の給与
この2つが一致していない場合には、元データを見直しましょう。
一致しない要因として、よくあるのが「未払給与」です。
未払給与が含まれていないなどあれば、
元の給与データに織り込んでしまいましょう。
雇用保険未加入・役員などへの支給額を除外する
損益計算書との整合性が取れましたので、
次に、下記のような人の給与を抜きます。
- 雇用保険未加入
- 役員・役員の家族
先ほどのピボットテーブルを作ったシートを丸ごとコピーして、「対象」欄が「1」のものだけを表示させます。
これで、雇用保険未加入者、役員・役員の家族などの分の給料を除外できます。
年度別の支給月数・支給金額を出す
ピボットテーブルの中で、
年度別の給与支給合計は17行目、31行目に出ていますが、
年度別の給与支給月数が出ていないので、計算します。
計算自体は単純で、count関数を使って計算をします。
支給金額自体は、すでにピボットテーブルで計算されていますが、
それを計算式で転記します。
継続雇用者の判定
継続雇用者の判定は、
前年度、今年度の両方で給与の支給実績があるかどうかで判定します。
今回は、
「C33セルとC34セルのどちらかが0」であれば
「(C33セル)×(C34セル)=0」になる、
ということを使って、計算式を入れています。
意味としては、or関数を使って次のような計算式を入れるのと一緒です。
or関数を使うほうがわかりやすいようであれば、もちろん、こちらの計算式でも問題ありません。
継続雇用者の給与支払月数・給与支給額を計算する
33行目~34行目の給与支払月数
36行目~37行目の給与支払月数について、
sumif関数を使って、継続雇用者分のみを集計します。
これで、必要な情報の集計ができました。
あとは、
集計したデータを申告ソフトに入れれば業務完了です。