マエムラボ Work & Nature Log

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

2年ほど前(!)、労働保険の年度更新をエクセルで自動計算する独自作成のフォーマット( Excel シート)を紹介しました。
その際にエクセルのサンプルファイルを公開予定としており、ようやくその約束を果たしつつの記事となります。

P5011303

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

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

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

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

年更賃金入力

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

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

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

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

内訳表数式1

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

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

kanitinginsyukeihyo

この表の右端の合計(賃金額と人数)を賃金内訳表の列に参照して、コピーも可能になりました。関数を使うなどもう少し工夫したかったのですが、このシンプルなやり方が一番手っ取り早かったので…。(追記:高年齢者免除分は廃止のため入力不用です。)

〔参考:高年齢者の保険料免除廃止に伴う変更点まとめ〕

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

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

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

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

sinkokusyoyou

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

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

¨¨

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

〔追記:R5年手動アップデートの概要〕

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

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

 

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


<仕事のご依頼等はこちらから>
 -提供サービス一覧・業務のご依頼
 -セミナー講師・テーマ等のご要望

ブログランキングに参加しています。

 にほんブログ村 士業ブログ 社会保険労務士(社労士)へ
 にほんブログ村

スポンサーリンク



SNSでフォローする