こんにちは、旅田です。
昨年9月にアップしたGoogleフォームの記事ですが、おかげさまで非常に多くの方に読んでいただけているようです。
スプレッドシート大好き芸人（※）として、感無量でございます。
（※初出はこちら）
そんな折、記事についてこんなコメントを複数お問い合わせいただいています。
あの勤怠管理表、ぜひテンプレート配布してください！
「あの」とは、こちらの勤怠管理シートのことです。
こちらも私がGoogleスプレッドシートで独自に作ったものですが、どうやら一部の管理者の方々にご支持いただけたようで、大変光栄に思います。
ということで、今回そのご期待に応えるべく、こちらも無料でテンプレート配布します！
もちろん、配布するだけではありません。
今回の記事でも着想の部分や構築方法の部分をご紹介することで、前回同様、Googleスプレッドシートがいかに開発者視点でも優れたプラットフォームであるかもお伝えできたらと思っています。
- こんなアプリが欲しい
- 自分で気軽に開発したい
- いつでも無料でチューニングしたい
そんな方々のため、今回はレジットのスプレッドシート大好き芸人が人肌脱ぐような記事を目指します！
※ 今すぐテンプレートが欲しい方向け → ダウンロードURLはこちら！
※ 作り方から読みたい方向け → 『勤怠管理シート』制作 -入力編-
はじめに
開発秘話
この勤怠管理シートはいま私が在籍する部門などで運用されていますが、実はもっと以前からその原型は存在していました。
2018年当時の私の勤め先では某勤怠管理システムが全社導入されていたのですが、これが上長承認フローやそのための設定方法など煩雑なもので、より小規模なチームや文鎮型組織にとっては使いづらいものでした。
このシステムで自部門のメンバー管理を行うことに嫌気がさしたのが、当時の私の上長でした。
上長がふとこぼした「Excelなどで簡単な勤怠管理表を作れないかね？」というぼやきこそが、今回の勤怠管理シート開発のきっかけでした。
このエピソードは一見、職場にありがちな何気ない会話かもしれませんが、これこそが前回の記事でも述べた開発目的の部分に相当します。
改めて、あなたには、何か解決したいお悩みはありますか？
Googleスプレッドシートは、このような「解決したい」を叶えてくれる魔法のプラットフォームです！
数式などはちょっと頑張って覚える必要はありますが、学習コストに対するリターンは計り知れないので、ぜひご一緒にスキルを身に付けましょう。
Googleスプレッドシートとは？
前回もご紹介しましたが、改めてご紹介します。
Googleスプレッドシートとは、いわゆるExelに代表される表計算ソフトにあたるものですが、一番の特徴はクラウド上で管理されており、アクセスしたユーザーで同時多発的な編集が可能となります。
本当に何でも作れて、最近では自己分析のためのライフチャートが簡単に生成できるツールを開発しました。
これは我ながら発明だと思っています！
その他、例えばGoogleスプレッドシートで売上金額などの予実表を作ることで、チームのメンバー同士で数字を更新しながら議論したり、リアルタイムでの情報共有やプレゼン発表まで行えちゃうなど、とにかく活用方法が無限大のプラットフォームです。
制作にあたり
さて、次章から勤怠管理表もとい【勤怠管理シート】の作り方についてご紹介していきますが、非常に多機能なため、実際の作り方はかなり限定的なご紹介となる点、あらかじめご了承ください。
可能な限りご説明できるよう努めますが、最終的には実際のツールをダウンロードしていただき、ご自身で分析してみるのもよいかもしれません。
『勤怠管理シート』制作 -準備編-
さて、今回は前回の『時間管理アプリ』よりも少し複雑になるかもしれませんが、まずは準備編です。
勤怠管理シートとして必要な機能を整理してみましょう。
どうなって欲しいか？ の言語化
機能として「こうなって、こうなったら、こうなる」といった、動きを決定することを「要件定義」と言います。
かなり大雑把な説明ですが、つまりはどういう動きになるかという「要件」を「定義」する訳ですね。
まあ、最初はあまり難しく考えず「こういう機能が欲しい」といものをイメージし、それを言語化してみるのがよいでしょう。
- 名前と勤務時間を記入したら、その人の月内の勤務時間がわかる
- 出退勤の時間を記入したら、その日の勤務時間が集計される
- フラグを立てたら、その日だけリモートワークとしてカウントされる
などといった感じです。
“AしたらBになる”
どうなって欲しいか？ の言語化ができたら、もう一歩だけ踏み込んでみましょう。
要するにこの要件定義とは、前回も登場した「入力（インプット）」と「出力（アウトプット）」をひとつずつ洗い出す、ということです。
それでもまだ頭に「？」が浮かぶ方は、
“AしたらBになる”
という合言葉を思い出してください。
そして、このときの「A」と「B」に入る機能を、ひとつひとつ考えればOKということです。
A（＝入力部分）は何か？
今回の勤怠管理シートで言えば、次のような入力機能が欲しいところです。
- 年月日
- 氏名
- 出勤時間
- 退勤時間
- 休憩時間
- リモートワークの有無
B（＝出力部分）は何か？
出力機能ならこんな感じです。
- 曜日
- 当日（マーキング）
- 土日祝日（マーキング）
- 勤務時間（合計値）
- 残業時間（合計値）
- １週間の残業時間の平均値
出力は、まあ「こんなのが自動で表示されたら嬉しいよね〜」というものを考えてみたらよいです。
こちらは、手を動かしながら「あ、こんなこともできるじゃん！」と気付くこともあるので、いきなり正解をすべて出せる必要はないでしょう。
『勤怠管理シート』制作 -入力編-
必要な機能が整理できたところで、いよいよ実際に手を動かして作っていきましょう！
前回の記事に倣い「入力編」としましたが、入力とは名ばかりで、UI（見た目に関する部分）をまるっと作っていきます。
言い換えると「数式を扱わない部分」のすべてを作り込んでいきます。
UI制作
ここで言うUI（＝ユーザー・インターフェース）は、勤怠管理表としての見た目部分のことを指します。
つまり「横軸に日付があって〜」「縦軸に出勤時間・休憩時間・退勤時間を入れて〜」など、各メンバーや管理者が使いやすい表になるよう試行錯誤しながら見た目を作る作業です。
なので、この部分は答えがひとつという訳ではなく、皆が使いやすいデザインになるようコミュニケーションを取りながら完成させるのがおすすめです。
とはいえ、勤怠管理表なんかは昔からあるツールですし、テンプレートもたくさんあります。
正解はひとつではないものの、正解に近い “型” は世の中にたくさんあることは覚えておくべきでしょう。
表示形式の統一
Googleスプレッドシートには「表示形式」という機能があります。
例えば、同じ「19時30分」という時間でも、
- 19:30
- １９：３０
- 19時半
- １９時３０分
など、人によって書き方は様々あり得ますよね？
そうなると、この後「出力編」で数式を活用する際にエラーとなってしまう場合があるのです。
この画像の例の通り、半角英数字の「19:30」と記入している月曜日以外は、「#VALUE!」というエラーが出ちゃっています。
数式はプログラミング言語なので、世界共通の言語として「半角英数字」のみに従います。
そのため、時間の入力セルに関しては半角英数字、特に【00:00】の形式になるように「表示形式」から設定してみましょう。
一応、参考となる画面はこちらになりますが、より詳しい説明は割愛します。
祝日参照用シートの準備
勤怠は営業日に付けるものですよね？
弊社もカレンダー通り祝日がお休みなので、祝日は記入しないようなマーキングをしたいところです。
そのため、後々数式で自動的に祝日情報を取得できるよう「祝日の一覧表」を別シートに用意しておきます。
なお、祝日の一覧は「エクセル 祝日」などでGoogle検索すると、そのままコピペできる表が無数にヒットします。
ご自身で調べて表を作るのももちろんアリですが、権利的に問題ないものはどんどんパクって時間短縮しましょう！
リモートワークの報告
昨今の情勢もあり、会社によってはリモートワークの有無も記録しておく必要があるかと思います。
弊社でもリモートワークは自由に取得できるため、プルダウン形式によるリモート勤務のフラグ立てができるように改修しました。
プルダウンの設定は、シート上で右クリックを押したとき、メニューの一番下に表示される「データの入力規則」のうち、「リストを範囲で指定」か「リストを直接指定」で実現できます。
詳しい説明は思い切ってバッサリ省きますが、基本的には範囲での指定をオススメします。
ただ、今回のように選択肢が今後増えることのない場合に限り、直接指定でも良いかと思います。
- 「リストを範囲で指定」＝基本的にはこちらでOK
- 「リストを直接指定」＝リストの選択肢が不変であればこちらもOK
『勤怠管理シート』制作 -出力編-
見た目ができてきたので、あとはガシガシ数式を組んでいきます。
ただし、本章は普段、数式を扱っていない方にとってはだいぶハードな内容になるかと思います。
よって今回はとっかかりの部分であるカレンダー表示に関連する機能を中心にご説明することで、各機能を実装するうえで必要な考え方や、最低限必要となる関数をご紹介していきます。
月内残日数の自動化
大前提として、勤怠は月単位での管理が基本であるとします。
そうなると、Googleスプレッドシートの１シートを１ヶ月と定めると都合が良さそうです。
よって、理想としては「毎月、月初１日さえ入力すれば、残りの日数は自動的に表示される」と便利そうです。
そこで、下記のような数式を組んでいます。
=IF(H3=””,””,IF(DAY(H3+1)=1,””,H3+1))
こちらには２種類の関数【IF】と【DAY】を使っています。
DAY関数
DAY関数は、
| =DAY(日付) |
とすることで、その日付を表示してくれます。
「え？ そんなの関数じゃなくて直接日付書けばいいじゃん」と思われたかもしれませんが、例えばこれを
| =DAY([左隣のセル] + 1) |
とすることで、左隣にある日付の翌日を記録することができるので、この数式を右の方に連続させることで、【翌日→翌々日→…】と、月内の日数を自動的に取得することができます。
IF関数
IF関数は、
| =IF(計算式, 計算式が正しい場合の結果, 間違えていた場合の結果) |
ですから、今回は【左隣のセルが空欄だったら空欄に、そうじゃなければ翌日にする】という命令を出しています。
より詳細な説明は割愛しますが、数式の使い方、何となくわかってきましたか？
曜日の自動化
=text(L3,”ddd”)
曜日の表示方法も色々ありますが、私は制御しやすい【TEXT】関数が好きですね。
TEXT関数
TEXT関数は、
| =TEXT(表記を変えたいセル, 表記方法) |
と記述します。
今回は【ひとつ上のセルにある日付を「月火水木金土日」形式の表記】に変える命令を出しています。
この表記の種類がいくつもあるので、様々な指定を一括でできるのが便利です。
当日を目立たせる書式設定
お次は「条件付き書式設定」です。
条件付き書式設定を使いこなせるようになると、例えば「値が1以上のときだけセルを緑色にする」といったことが可能となります。条件に合わせて書式が変わる、ということです。
これの応用で「カスタム数式」を活用すると、「指定した数式を満たす場合のみに反映する書式」をも設定することができます。
今回は、
H3:AL4
という日付＋曜日の範囲に対して、
=H$3=today()
という数式を満たす場合にのみ、セルが紅色になるよう設定しました。
また、このとき「$」がアルファベットの右にあると、数式の適用範囲が「縦一列」になり、左にあると「横一行」になります。
TODAY関数
TODAY関数は、今日の日付を自動取得するものです。
なので、ここまでの条件をまとめると【C3セルが今日である場合、曜日の行までを紅色にする】といった条件付き書式設定になります。
今回は条件付き書式設定の中でも最難関の部分をいきなり紹介してしまいましたが、これをマスターすればかなり気の利いたツールが自作できるようになるでしょう。
土日の書式設定
前章同様、条件付き書式を活用することで、曜日が「土」の場合に縦一列をグレーアウトすることが可能になります。
同じく「日」の場合の条件付き書式も、別の条件として作ってみましょう。
祝日判定
「土日はいいけど、祝日は曜日情報だけではわからないよね？」とお気付きの方は鋭いです。
その通りで、今のままだと祝日をグレーアウトしたくとも、そのためのトリガーがない状態です。
そこで、前述の「祝日参照用」シートを活用し、【祝日なら「1」を、それ以外なら「0」を返す】数式を用意しましょう。
シートの5行目にあたりますが、便宜上これを「祝日判定機」と名付けています。
祝日判定機はただのトリガーなので、普段は非表示にしておけばよいでしょう。
関数としては前述の【IF】のほか、【COUNTIFS】があります。
COUNTIFS関数
こちらは数を数える【COUNT】関数に条件分岐の【IF】、それに複数形の【S】が付いているので、複数の条件に合致するものの数を数えてくれます。
| =COUNTIFS(調べたい範囲①, 探し物①, 調べたい範囲②, 探し物②, …) |
などと覚えると便利でしょう。
今回は「調べたい範囲」を祝日の一覧表とし、「探し物」を今日の日付としました。
よって、【祝日一覧の中に今日の日付があったら「1」を返し、それ以外は「0」を返す】という数式を組んでいます。
これで、「1」のときだけ縦一列をグレーアウトする条件付き書式を設定することで、土日だけでなく、祝日も自動的にグレーアウトすることができます。
まとめ
いかがだったでしょうか？
出力編では、様々な機能の自動化を実現するため、複数の関数や書式設定などを組み合わせてきました。もしかすると難度が高く感じた方もいるかもしれません。
しかし、大切なのは変わらず「実現したいこと」を念頭において作業にあたることです。
「こんなことを実現したいよね」から、「じゃあこんな機能が欲しいよね」というブレークダウンを経ることで、具体的に構築すべき要件が見えてくるでしょう。
『勤怠管理シート』ご紹介
前章を参考に勤怠管理シートを作られたあなた、お疲れさまでした！
まだ完成していない方もまだ着手していない方も、こちらからダウンロードができますので、ぜひ実際にお手に取ってみてください。
ダウンロードURLはこちら！
【サンプル】勤怠管理シート
https://docs.google.com/spreadsheets/d/1G2OnQ9kdTMD2U-cONZUiSSNmsT57JBw_HL7bE64BJss/edit?usp=sharing
ダウンロード方法
恒例のダウンロード方法です。
まず、ダウンロードURLからスプレッドシートを開き、「ファイル」→「コピーを作成」をクリックします。
この時点でGoogleのアカウントにログインしていないとコピーができないため、もしもアカウントをお持ちでない方はこちらからGoogleアカウントを作成してください。無料です。
コピーするファイルのファイル名と保存先フォルダを指定できるので、それぞれ任意の名前・フォルダを指定し、「OK」をクリックしてください。
これで、あなたがGoogleアカウントのドライブ内にコピーしたファイルとして追加することができました。
このコピーファイルはあなただけのものです。
以後、このコピーファイルを編集するようにしましょう！
使い方
使い方ですが、日々ご自身の勤怠を入力される「メンバー側」と、それを管理される「管理者側」とで、簡単にご紹介します。
基本的には赤枠で囲われた部分のみが入力箇所なので、「赤枠以外はノータッチで」を合言葉にしましょう。
メンバー側
メンバーのみなさんはH〜AL列にかけて赤枠で囲われた部分のみで完結するので、日々の運用は非常に簡単かと思います。
まずは基本の「出勤時間」「退勤時間」「業務外時間（休憩）」を【00:00】形式で入力すれば、それだけで勤怠管理表としては機能します。
加えて、リモートワークを実施した日に関しては、8行目にあるプルダウンから「R」を選択しましょう。
勤怠シート上の表記にて、改めてまとめると下記になります。
- 出勤
- 業務外(昼含む)
- 退勤
- (R=リモートワーク)
管理者側
管理者側は、その他の赤枠部分を月初に記入するのみです。
- C3セル ＝基本的な勤務時間を【半角数字】で記入
- H3セル ＝【月初１日】にあたる日付を入力
- EF列にある赤枠 ＝各メンバーの氏名を入力
こちらに加え、管理者は月が変わるタイミングでシートを複製するという重要タスクがあります。
手順は簡単で、シートのタブ部分（『勤怠yymm』の部分）で右クリックを押すと出てきるメニューのうち、「コピーを作成」をクリックすることで、シート丸ごと複製することができます。
あとはシート名を任意で変更することで、毎月の勤怠シートとして管理することができます。
注意事項
最後に注意事項ですが、スプレッドシート最大の天敵は【数式を壊す輩】です！笑
数式を壊すとは、セルに記述された数式を消したり、上書きしてしまったりすることです。
もちろん、誰しも悪気があって壊している訳ではないのですが、どうしてもスプレッドシートに不慣れな方はいらっしゃるでしょう。
そこで、改めて「赤枠以外はノータッチで」を合言葉に、各メンバーの方には周知した上で運用されることをオススメします。
おわりに
今回もかなり長くなってしまいました。かなり大変でした。。。
いつも通り、ちょっと偉そうな内容になってしまいましたが、もちろんご紹介したやり方が正解という訳ではありません。
ここで登場した数式や書式などには、まだまだ改善余地があるでしょうから、ぜひ、よりよいやり方があればお気軽にご連絡ください！
この記事を書いた人
旅田 康貴ディレクター / デザイナー / イラストレーター
デザインが一番苦手なデザイナーを目指しています。最近、名字が変わりました。
ネットショップ運営
ランキング
制作実績
