Googleスプレッドシートによる勤怠管理シートをCOUNTIFS関数と書式設定で仕上げる

★気に入ったらシェアをお願いします!

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

Googleスプレッドシートを使って勤怠管理集計システムを作成していくシリーズです。

前回はこちらの記事。

Googleスプレッドシートで時間内・時間外(残業)・休日・深夜の労働時間を計算する
Googleスプレッドシートを使って勤怠管理集計システムを作成しています。今回は最大の難関ともいえる各労働時間つまり時間内労働、時間外労働(残業)、休日労働、深夜労働の時間を計算していきます。

日別の時間内労働時間、時間外労働時間(残業時間)、休日労働時間、深夜労働時間の計算をすることができました。

もうここまで来ればあと一歩です。

今回はCOUNTIFS関数などを駆使して出勤日数や欠勤日数など日数の合計および時間外労働時間や深夜労働時間などの労働時間の合計を求めます。

勤怠管理シートとしては完成となります。

今回のお題:日数と労働時間の合計を求める

前回の記事で作ったのはコチラのシートです。

スプレッドシートの勤怠管理シート

G列からU列までに各日の出勤区分、打刻の時刻、就業時刻、就業時間が算出されるように作られています。

給与計算をする場合は月単位で行う場合が多いと思うので、このシートも月ごとに計算をしていきたいと思います。

出勤・欠勤・有休・代休の日数合計

B6~B9セル、つまり出勤・欠勤・有休・代休のそれぞれの合計日数を求めます。

ここはF列「区分」が「出勤」「欠勤」「有休」「代休」となっている日数をそれぞれカウントすれば良いわけですから、COUNTIFS関数を使えばOKですね。

それぞれ以下の式になります。

=COUNTIFS(F:F,”出勤”)
=COUNTIFS(F:F,”欠勤”)
=COUNTIFS(F:F,”有休”)
=COUNTIFS(F:F,”代休”)

簡単ですね。

総就業時間の合計

セルB10は総就業時間ですが、これは簡単です。

Q列を合計すればOKですね。

=SUM(Q3:Q33)

スプレッドシートで時間の表記がおかしい

…おや?なんか合計値がおかしい気がしますね…。

半月分くらいのデータが入っていますが、合計時間が23時間30分ということはありませんよね。

これは表示形式が「時」「分」になっているのが原因です。24時間を超えて切り上げた分が表示されていないのです。

そこでメニューから「表示形式」→「数字」→「表示形式の詳細設定」→「その他の日付や時刻の形式」とたどって「カスタムの日付と時刻の形式」ウィンドウを開き、「[[時(1)]]:[[分(01)]]」となっているのを「[[経過時数(1)]]:[[分(01)]]」と変更をしましょう。

削除は Delete キーで追加は入力欄の右側にある▼ボタンから選択すればOKです。

スプレッドシートのカスタムの日付と時刻の形式

これで「適用」をすれば、欲しかった表示になります。これはセルB11~B14も同様ですので合わせて表示形式を変更しておきましょう。

労働時間の合計

次にセルB11~B14、の時間内労働時間、時間外労働時間、休日労働時間、深夜労働時間の合計を求めていきます。

これらもそれぞれR列~U列で算出されている時間の合計ですから簡単です。

=SUM(R3:R33)
=SUM(S3:S33)
=SUM(T3:T33)
=SUM(U3:U33)

このようにシートに計算結果が全て算出されました。

スプレッドシートの勤怠シートに月次の合計を算出

書式を設定して見やすくする

これでシートの計算式、値については完成なのですが、少し見づらいので書式設定をして見やすくしていきましょう。

まずセルの塗りつぶしの色ですが

  • 見出し:濃い水色
  • 日、曜日:薄い水色
  • 計算式のセル:薄いグレー

とします。
計算式のセルをグレーにするのは、視覚的に「値のベタ打ちじゃないよ」とわかりやすくするためです。

次に罫線でA,B列のブロックと、D~U列のブロックの外側に枠線をつけます。

見出し、曜日、区分のセルの文字位置を中央配置にします。またG~U列の1行目の見出しはセルの結合をします。

またD,E列の書式設定は条件つき書式で土日の場合は別はピンク色にしましょう。それについてはこちらの記事が参考になります。

スプレッドシートでスケジュール管理表の土日に条件付き書式で色を付けて見やすくする方法
エクセルでスケジュール管理されている方を見かけます。ただ、土日の欄を青や赤で毎月塗り直すのが手間ではありませんか?日付と曜日も毎月面倒ですね。 今回はその手順を、スプレッドシートで自動化してみたいと思います。難しいコードなどは使いません。いくつかの関数と条件付き書式を使った簡単な方法でご案内いたします。

さて、以上の書式設定でこのようになりました。
スプレッドシートの勤怠シートに書式設定
だいぶそれっぽくなりましたね。

まとめ

以上でGoogleスプレッドシートによる勤怠管理シートが完成しました。

今回使った関数や書式設定などのテクニックは今まで紹介してきたものばかりですので、復習としてご活用いただけたのではないかと思います。

さて、次回ですがこの元となる「データ」シートへの記録、つまり打刻をチャットワークで実施できるようにGoogle Apps Scriptでプログラムを組んでいきたいと思います。

Google Apps Scriptでチャットワークでのチャット打刻システムを作る
Googleスプレッドシートを使って勤怠管理集計システムを作成しています。今回はGoogle Apps Scriptでチャットワークからの出勤・退勤の打刻情報を受け取るスクリプトを作っていきます。

どうぞお楽しみに!

連載目次:Googleスプレッドシートで勤怠管理集計システムを作る

勤怠管理業務はたくさんのスタッフとのやり取りもありますし、集計もなかなか複雑です。本シリーズでは複数人や離れた場所での共同作業も得意なスプレッドシートを用いてクラウド上で勤怠管理システムを作る方法をお伝えしていきます。
  1. Googleスプレッドシートでクラウド上の勤怠管理集計システムを作る
  2. 【スプレッドシート初心者向け】定番関数と表示形式で手入力しないで日付と曜日を出力
  3. GoogleスプレッドシートのVLOOKUP関数で複数条件による検索を行う方法
  4. 【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法
  5. GoogleスプレッドシートでIF文を超絶駆使して通常・残業・深夜の就業時間を求める
  6. Googleスプレッドシートで時間内・時間外(残業)・休日・深夜の労働時間を計算する
  7. Googleスプレッドシートによる勤怠管理シートをCOUNTIFS関数と書式設定で仕上げる