年度更新をエクセルで自動計算(賃金&記入)するツール

シェアする

2年程前(!)になりますが、労働保険の年度更新をエクセルで自動計算する独自の方法を紹介しました。
その際に公開予定としていたエクセルの数式やサンプルを、ようやくまとめる記事となります。

P5011303

水俣・湯の児温泉入口より

手計算からエクセルで自動化するために

タイミングとしては全然合っていません(今年は既に終了です)が、労働保険の年度更新で使えるエクセルシートについてのご紹介です。1年間の賃金集計を手書きでしている場合は、是非ご参考にしていただければと……

2016年のこちらの記事で、年度更新のエクセルを使った計算ツールを取りあげました。当時から今でも提供されている厚生労働省のツールと、私が独自に作ったものを画像付きで紹介しています。

前回、次のようなエクセルの一覧表入力で賃金を集計するものを紹介しています。基本的に手書きの集計を代替し、縦(行)に年度内に賃金を支払った労働者を入れ、横(列)に月ごとの賃金額(総支給額)を手作業で入力していくものです。

年更賃金入力

表内で黄色のセルには下のデータ(数=人数)を counta 関数で自動計算する式が入っており、赤色のセルには月ごとの全員の賃金額を合計する式を sum 関数で入れています。
少し変わっているのは、合計欄を下にした一般的な形でなはく、一番上に設けたところでしょう。

これは、見やすさとエクセルの行(人数)を増やす際に備えての苦肉の策です。この1枚のシートに全ての区分(一般労働者や臨時労働者、また雇用保険用の各区分)の賃金集計表を横に並べて作り、次の表で参照するようにしています。




複雑な参照をエクセルで一発計算するツールの作り方

さて、上記の賃金集計表では主に小さな会社等で使うことを想定して同じエクセル内の別タブで月ごとの合計額を参照し、次のような表を自動作成します。つまり、労働局から送付されてくる申告書一式にある「確定保険料・一般拠出金算定基礎賃金集計表」を一発で計算するものになりますね…。

内訳表数式1

この表でデータ参照に使っている数式は、上の図のD6セルで表示されているものです。下の各月も、それぞれ別シートの合計額を参照するために「=」を入れた状態でセルを手で指定する原始的方法?で入力しています。ようするに、エクセルの数式コピーが使えないのが難点なのです。

今回ブログで記事化するにあたり、この部分を改善できないか検討しました。関数を含めて調べましたが、縦横の置換がネックになりなかなかいい方法がありません・・・
結局、縦横を揃えてよりシンプルな各人別の賃金集計表を作り直したものがこちらです。(横の列、各区分とも人数を10人以下にして作っています。)

kanitinginsyukeihyo

この表の右端の合計(賃金額と人数)を賃金内訳表の列に参照して、コピーも可能になりました。関数を使うなどもう少し工夫したかったのですが、このシンプルな方法が一番早かったのです。

自分で作って効率化できるエクセルのすごさ

もともとこの年度更新エクセルシートを作り始めたのは十数年前のこと・・。それ以来、小さな改良を施しながら現在まで使い続けているものです…。そのため、我ながら素人くさい作り方や計算式を使っているところはあります・・。

今回、上記のような個人別賃金集計表を改良してみたものの、根本のところは一緒です。むしろ、人数が増えると横に拡げるしか無くて不恰好でさえあります・・・
しかし、最終的に申告書が作成できれば良いと割り切れば、十分に使えるものです。

少しまとめると、①個人別年間賃金集計、②算定基礎賃金集計、③申告書記入シミュレーション、の3つのエクセルの表で年度更新の計算を効率化するツールです。それぞれ計算結果を参照しながら自動計算するので、入力等以外のほとんどの複雑な計算が省略できるのです。

sinkokusyoyou

最後の③のシミュレーション表がこちらで、送付されてくる申告書の項目に転記するだけになります。
今回、このエクセルファイルをダウンロードできるようにしました。

Excelファイルダウンロード(70KB)

小規模な事業所の申告やエクセル活用の参考としてご活用ください。(※自己での責任と利用範囲とさせていただきます。一部の式は入っていません。)

P5011310.JPG

雨の「湯の児海水浴場」にて

 〔あとがき〕
鹿児島地方は梅雨入りですが、とりあえず月初は晴れ間が続いていますね・・。
さて、何と言っても、今回の年度更新の計算は参照が複雑で手間がかかります(*マニュアルはありますが…)。私のような?計算が苦手なタイプには、エクセルは本当に有用なツールだと感謝するほどです…。

近頃の Good & New
・とある講座参加(5/27)
・PRyC WP(プラグイン)

シェアする