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

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

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

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

年度更新の賃金集計等を手計算からシンプルなエクセルシートで自動化する方法

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

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

年更賃金入力

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

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

 

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

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

内訳表数式1

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

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

kanitinginsyukeihyo

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

 

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

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

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

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

sinkokusyoyou

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

Excelファイルのダウンロード

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

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

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

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

是非フォローしてください

最新の情報をお伝えします

Y.Maemura

Yoshiaki Maemura 社会保険労務士 前村ひと・しごと研究所代表。
鹿児島県伊集院町出身。鹿児島⇔東京を経て鹿児島市郡山町在住。社会保険労務士。自称・自然体験愛好家。はじめの一歩をサポートする情報等を発信中。≫詳しくはこちら