第3週 課題：業務効率化ツール作成｜給与計算
AIを使って業務効率化するプロジェクト始動！
このkeitaroさん率いる「けいたろ軍団」のプロジェクトでは
毎週 課題提出＆フィードバックを繰り返し「2ヶ月後にAIを使って業務効率化できるようになること」を目指しています。
【挑戦】
給与計算を自動化する
▼前回の投稿【課題の学び】
今回の課題は、給与計算です！
MOSほぼ全問正解合格（多分一問だけ間違えた…）の元Excelマスター、現スプシマスターの私としては、データベースさえ整っていれば集計はお任せあれ！
というわけで、今回はAIよりも関数やスプシのテクニックの内容になります！
課題の目的
給与計算、皆さんの職場ではどうやっていますか？
大きな会社は当然給与システムで自動計算ですよね。
私はかつて大企業の賃金雇用担当で、数千人という人数の給与計算をなんと一人でやっていました。人事給与システムを導入していたので、勤怠データと時給データがあれば自動的に明細発行まで進めるので、件数多くても一人で対応できたわけですが…
小規模事業で従業員が数人、給与システムを入れるほどではないっていうケースもかなり多いと思います。
複雑化する労働法や税制改正などを考えると、税理士や社労士などに外注することも必要になってくるとは思いますが、時短パートさん数名で税金も社会保険の手続きも発生しない場合は自分で計算できたらいいですよね！
今回の目的は「勤怠データと時給から明細の発行まで」自社でできるようにする！です⭐️
給与計算に必要な項目
1.勤怠情報
給与明細には、まず従業員の勤怠情報を記載します。これには以下の項目が含まれます。
出勤日数: 該当月の出勤日数を記載します。
欠勤日数: 欠勤した日数を記載します。
労働時間: 総労働時間を記載します。残業時間も含める場合は、別途記載します。
2.支給額
支給額には、基本給や各種手当が含まれます。具体的には以下のように計算します。
基本給: 契約に基づく基本給を記載します。（今回は時給 × 勤怠）
残業手当: 残業時間に応じた手当を計算し、記載します。通常、残業手当は基本給の1.25倍（法定時間外労働の場合）で計算します。
3.控除額
控除額には、以下の項目が含まれます。これらは法律で定められたもので、必ず記載する必要があります。
住民税: 前年の所得に基づいて計算されます。具体的な金額は、住民税課税決定通知書を参照して記載します。
所得税: 所得税は、給与から天引きされる税金で、月々の給与に基づいて計算されます。税額表を使用して、該当する税率を適用します。
厚生年金保険料: 従業員の標準報酬月額に基づいて計算されます。厚生年金保険料率を適用し、従業員と会社で半分ずつ負担します。
健康保険料: 健康保険も同様に、標準報酬月額に基づいて計算され、保険料率を適用します。
給与計算準備
前回「勤怠データ」「ユーザーマスター」「集計」シートを作成したスプシに「賃金台帳」と「明細」シートを追加します。
賃金台帳シート
A列 年月
B列 Email
C列 社員ID
D列 社員名
E列 時給
F列 残業時給
G列 出勤日数
H列 勤務時間
I列 残業時間
J列 一日あたり交通費
K列 住民税
L列 所得税
M列 厚生年金
N列 健康保険
O列 雇用保険
明細シート
明細はここの「タテ型（上下分割）」からレイアウトお借りしました！
賃金台帳に１年分の給与をまとめて記載し、
明細シートで指定した月の明細を呼び出す形にします。
賃金台帳を完成させる
▼手動管理
A列 年月 ＝ 202401〜202412
B列 Email ＝ 勤怠データ取得の際に使っているGoogleアカウント
C列 社員ID ＝ あれば使用（キーにしているので、ない場合はGoogleアカウントにしておく）
D列 社員名 ＝ ユーザーマスターからXlookupでもコピペでもOK
E列 時給
J列 一日あたり交通費
K列 住民税
L列 所得税
M列 厚生年金
N列 健康保険
O列 雇用保険
ここは今回は手動で1月から12月分を入力します。（全部コピペでOK）
交通費
交通費の課税・非課税はこちらで確認してください。
今回はすべて非課税という想定で作成します。
公共交通機関を利用する場合の通勤手当は、1ヵ月あたり15万円が非課税。
マイカーや自転車通勤への通勤手当の場合距離に応じます。
税金・社会保険料
ここは標準報酬月額や前年の源泉の金額によって決まってきますので決まった内容を手入力します。今回はダミー値を入れています。
※「給与計算に必要な項目」を参考にしてください
▼計算式
F列 残業時給 ＝ 時給 × 1.25 [=ArrayFormula(E2:E*1.25)]
G列 出勤日数 ＝ ユーザーごとの該当月の出勤日数をカウント [=COUNTIFS(集計!B:B,C2,集計!D:D,A2)]
H列 勤務時間 ＝ ユーザーごとの該当月の勤務時間を集計 [=SUMIFS(集計!J:J,集計!B:B,C2,集計!D:D,A2)]
I列 残業時間 ＝ ユーザーごとの該当月の残業時間を集計 [=SUMIFS(集計!K:K,集計!B:B,C2,集計!D:D,A2)]
これで賃金台帳が完成します！
📝ArrayFormula
ArrayFormula関数は、Googleスプレッドシートにおいて非常に便利な機能で、複数のセルに対して一括で計算を行うことができる関数です。この関数を使うことで、手作業で数式を入力する手間を省き、スプレッドシートの処理速度を向上させることができます。以下に、ArrayFormula関数の基本的な使い方やその利点について説明します。
ArrayFormula関数の基本
1.構文
ArrayFormula関数の基本的な構文は以下の通りです。
=ARRAYFORMULA(数式)
この数式の部分には、通常の数式を記述しますが、範囲を指定することで、複数のセルに対して一度に計算を適用することができます。
2.使い方の例
例えばE列に時給があり、残業時給は通常、残業手当は基本給の1.25倍なので時給×1.25をします。通常は各セルに関数を入力する必要があります。しかし、ArrayFormulaを使うと、以下のように一つのセルに数式を入力するだけで済みます。
=ArrayFormula(E2:E*1.25)
この例では、E2からE列の最終行までの範囲を指定し、各セルの値に基づいて1.25倍の値を返してくれます。
3.利点
効率化: 数式を一つだけ入力することで、指定した範囲全体に結果を反映させることができるため、手作業で数式をコピーする必要がありません。
軽量化: 多くのセルに数式を入力すると、スプレッドシートが重くなることがありますが、ArrayFormulaを使うことで計算負荷を軽減できます。
自動更新: データが追加された場合でも、ArrayFormulaを使っている範囲内であれば、自動的に計算結果が更新されます。
4.注意点
範囲の一致: ArrayFormulaを使用する際は、数式内で指定する範囲が一致している必要があります。例えば、A列とB列の値を足す場合は、両方の列で同じ行数を指定する必要があります。
空白セル: ArrayFormulaを適用するセルには、他のデータを入力しないように注意が必要です。入力があるとエラーが発生します。
📝COUNTIFS
COUNTIFS関数は、指定した条件を満たすセルの数をカウントするための関数です。複数の条件を指定でき、特定のデータの件数を把握するのに役立ちます。
1.構文
=COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2], ...)
条件範囲1: 最初の条件を適用するセル範囲。
条件1: 条件範囲1に対する条件。
条件範囲2, 条件2: 追加の条件範囲と条件（オプション）。
2.使用例
今回は、ユーザーごとの該当月の出勤日数をカウントしたいので
=COUNTIFS(集計!B:B,C2,集計!D:D,A2)
この例では、B列が「社員ID」で、D列が「該当年月」の勤怠データの数をカウントします。
📝SUMIFS
SUMIFS関数は、指定した条件を満たすセルの合計を計算するための関数です。複数の条件を指定できるため、特定のデータを詳細に集計することが可能です。
1.構文
=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
合計範囲: 合計したい数値が含まれるセル範囲。
条件範囲1: 最初の条件を適用するセル範囲。
条件1: 条件範囲1に対する条件。
条件範囲2, 条件2: 追加の条件範囲と条件（オプション）。
2.使用例
例えば、特定の部署の売上合計を求める場合、次のように記述します。
=SUMIFS(集計!J:J,集計!B:B,C2,集計!D:D,A2)
この例では、B列が「社員ID」で、D列が「該当年月」の勤怠データの勤務時間／残業時間（J列／K列）の合計を計算します。
給与明細の形にする
テンプレートを利用して給与明細シートを
作成します。
テンプレートはレイアウトデザインのみなので…毎回賃金台帳から転記…？
絶対ダメです！
転記はミスの元！！
該当月の明細が表示できるようにカスタマイズしていきましょう！
月を選択するだけで、該当月の明細を表示させる
スプレッドシートは計算するだけではなく、条件にあったセルの値を抽出することもできます。
今回は一人一人の今月の給与明細を作りたいので、キーになるのが
該当年月 ＝ I3
社員名 ＝ I4
上記の画像の I4セルで社員名を選択、 I3で月を選択します。どちらもプルダウンを設定しておくと楽ちんです☆
挿入＞プルダウン
これをキーに設定して呼び出します。
つまり社員「TEST1」の2024年12月のを抽出させるという式を書けばいいわけです。
抽出する関数といえば、VLOOKUPやXLOOKUPですが、今回はキーが二つあるのでFILTER関数を使います。
📝FILTER
FILTER関数は、GoogleスプレッドシートやExcelで特定の条件に基づいてデータを抽出するための非常に便利な関数です。この関数を使用することで、必要なデータを迅速に取得し、分析やレポート作成を効率化できます。以下に、FILTER関数の基本的な使い方や特徴をまとめます。
FILTER関数の基本
1.構文
FILTER関数の基本的な構文は以下の通りです。
=FILTER(配列, 条件1, [条件2, ...], [空の場合])
配列: フィルタをかけるデータ範囲を指定します。
条件1: 抽出するための最初の条件を指定します。
条件2: 追加の条件（オプション）を指定できます。
空の場合: 条件に合うデータがない場合に表示する値を指定します（省略可能）。
2.使用例
今回は賃金台帳から社員名 A001の2024年12月のを抽出したいです。
I4セルで社員名「TEST1」を選択、 I3で年月を選択しているので
=FILTER(賃金台帳!$H:$H,$I4=賃金台帳!$D:$D,$I3=賃金台帳!$A:$A)この数式を実行すると、結果として以下のようなデータが表示されます。
勤務時間＝26.0
必要な値は全て賃金台帳シートに揃っていますので、、この関数を利用して各項目を呼び出せばOKです！
あとはこちらを計算すればOK
総支給金額 ＝ 時給×勤務時間＋残業時給×残業時間＋交通費
総控除額 ＝ 税金・社会保険料など控除額の合計
差引支払額 ＝ 総支給金額 - 総控除額
これで勤怠データから給与明細ができました！
ただ、こちらもまだまだ改善点がありそうなので
フィードバックをもらったらまた完成版として紹介しますね✨
これを一人一人にメールする…までできたらいいな。。。とか。
もし実務に参考にするなら次回のフィードバック反映版で試してみてください⭐️