Googleフォーム&スプレッドシートで、ひとり出退勤表を作成してみる
ここは、小～中劇場規模の舞台公演（主に演劇）で制作やチケット管理業務をしてきた私が培ってきた事を綴るnoteです。
フリーランスで制作の仕事をしていると、自分の労働時間についてうっかり把握しないことがあります。
特に現場制作として稽古場や劇場で仕事をしていると、かなりの拘束時間となりますが、そういうものだと日々を過ごしてしまいます・・・。
仕事量を俯瞰してみるためにも、毎日何時間働いているか一度記録につけてみませんか。
今回はGoogleフォームとスプレッドシートを使用して、自分用の出退勤表を作ってみたいと思います！
※労働時間、出退勤など厳密には業務委託のフリーランスには使用しない言葉かもしれませんが、ここではざっくり働いている時間という意味で使っています。
Googleフォームでタイムカード作成
・フォームの作成
まずは必要項目を作成します。
一番シンプルなのは、出勤／退勤 のみ。
例えば稽古中だったら、稽古場ついたら【出勤】、帰る時に【退勤】にそれぞれチェックして送信すれば、タイムスタンプで打刻されるのでスプレッドシートで開いた時に出退勤の時間がわかります。
※フォームからスプレッドシートの作成方法は他の記事で紹介しているので省略します。
ただこれだと、フォーム送信忘れた時に困るのでもう少し質問を増やします。
●出退勤
それっぽく【休憩】も追加し、必須項目にしました。
●休憩時間
出退勤で【休憩】を選んで時間を入力しても、退勤時にまとめて休憩時間を入力してもOK。
●打刻し忘れた場合（ひにち）
忘れても大体当日に思い出すかなと「当日なら入力不要」と説明文をつけました。
●打刻し忘れた場合（時間）
これで後から入力したい時でも対応可能に。帰り道の電車内などでも操作できますね。
次にスプレッドシートを整えていきます。
追加した項目もスプレッドシートに反映されています。
ここから日にち毎の出退勤時刻を取り出します。
・date/time value関数
タイムスタンプには、年月日時分秒がすべて入っています。
datevalue関数を使うと年月日、timevalueは時分秒を抽出する事が出来ます。
F列の行タイトルを「日にち」として、下記数式を入力します。
A2はタイムスタンプのセルです。
=datevalue(A2)シリアル値(整数：ここでは45553)が表示されるので、表示形式を変更します。
[表示形式]→[数字]→[カスタム日時]を選択
好みの形式にします。ここでは〇月〇日(〇)にしてみました。必要ない項目を削除し、カッコを入力します。
適用されました。 今度は同じようにtimevalueを使います。
=timevalue(A2)
これでタイムスタンプの日にちと時間をそれぞれ抽出する事ができました。
・if関数で出退勤日時を抽出
しかしこれだと打刻し忘れた場合の出退勤時間が取得できていません。
日によって、[タイムスタンプ]の値が欲しい時と、[打刻し忘れた場合]の値が欲しい時とが混在します。
そんな時はif関数を使用します。
「もし、[打刻し忘れた場合](E3)が空白だった時に[タイムスタンプ（の時間）](A3)の値を表示し、そうでなかった場合は[打刻し忘れた場合]の値を表示」
という式を作ります。
ではない(ノットイコール)の記号は<>、空白は""です。
=IF(E3<>"",E3,timevalue(A3))timevalue関数と組み合わせて使用しています。
これで、E列(打刻し忘れた場合)に値が入っている行はE列が、空白の場合はA列(タイムスタンプ)が表示されました。
出退勤表作成
次に出退勤表一覧を作っていきます。
新しいシートを追加し（シート名：出退勤表）、日付を入力します。
日付の表示形式はお好みで。
・index&match関数で出退勤の時刻を抽出
タイトル行に【出勤】【退勤】【休憩】【稼働時間】の列を作成します。
出退勤の時間を取り出すためにはindex&match関数を使用します。
=INDEX('フォームの回答 1'!$G:$G,MATCH($A2&B$1,'フォームの回答 1'!$F:$F&'フォームの回答 1'!$B:$B,0))日付とタイトル（出勤or退勤）から、時刻を取り出しています。
index&match関数の説明は下記noteをご参考ください。
・sumif関数で休憩時間を抽出
休憩時間は、休憩時と退勤時など何回か入力する機会があるので合計できるようsumif関数を使います。
=sumif('フォームの回答 1'!F:F,A2,'フォームの回答 1'!C:C)
日にちごとの休憩時間が算出されまさした。
・ifna関数
これで出退勤、休憩時間を取り出す事ができましたが、打刻していない日は「＃N/A」とエラーとなってしまいます。
infa関数を使用し、値が「＃N/A」となった時に空白が表示されるようにします。
=ifna(値, [NAエラーの場合の値])
使い方はiferror関数と同じです。こちらは#n/aだけを対象とする関数です。iferrorを使用してもよいと思います。
出勤時刻を取り出したい時は、=ifna(出勤時刻, "") となるので出勤時刻のところに先ほどのindex&match関数を入れ込みます。
=ifna(INDEX('フォームの回答 1'!$G:$G,MATCH($A2&B$1,'フォームの回答 1'!$F:$F&'フォームの回答 1'!$B:$B,0)),"")エラーが消えました。
これですっきりしましたね。
労働時間を算出
・floor/ceiling関数
ここからやっと働いた時間を計算していきます。
退勤-出勤-休憩=稼働時間
となります。簡単ですね。ただこのまま計算すると・・・、
「おや？計算が合わない・・・？」となる場合があります。
これは、元のデータでは隠れていた「秒」が関係しています。
表示形式を変更して秒まで見えるようにするとわかります。
秒以下は切り捨てたい！ そんな時に使用するのがfloor関数です。
=floor(値,"0:01")
これで秒数以下を切り捨ててくれます。
G列に入っている数式をfloor関数で囲みます。
=floor(IF(E2<>"",E2,timevalue(A2)),"0:01")
秒数がすべて0になりました！ ちなみに切上げたい時はceiling関数を使用します。床(floor)と天井(ceiling)ですね。覚えやすい！
************
今回のひとり出退勤表にはあまり必要ないかもしれませんが、時給アルバイトの管理などに使用する場合は、キリの良い分数毎に切り上げ／切り捨てしたい場合もありますよね。そういう時もこの関数が使えます。
例えば、15分毎に切り上げる場合には、
=ceiling(値,"0:15")
切り上げられた時間が表示されました。
ちなみにここから時給計算したい場合は、時間×時給としても金額はでてきません。
時間に24をかける必要がありますのでご注意ください。（時間×24×時給）
本題からずれましたが、ちょっとした補足です。
************
・month関数
一日の稼働時間がわかりましたので、ここから月の時間を算出します。
month関数を使用して日付から月だけ取り出します。
=month(A2)
お分かりのとおり、月はmonth、日にちはdayで取り出せます。
・sum/countif(s)関数で月の稼働時間と休日を算出
上記図のI～K列に、稼働時間と休日の合計（月ごと）表を作成しました。
I列：月
J列：稼働時間合計
K列：休日の数
I列には月を入力。J列はsumifを使用してその月の稼働時間を算出。
=sumif(G:G,I2,E:E)K列はcountifsを使用してその月の稼働時間が0の日をカウントし、休日を算出します。
=countifs(G:G,I2,E:E,0)これで、ひと月の労働時間と休日が見えるようになりました！
あまり見たくない数字になる人も多いかと思いますが、記録をつけておくと事故やトラブルなど何かあった時の資料になる場合もありますので、よろしければご参考になさってください。
番外
もし、パソコンを立ち上げてから、電源落とすまでが労働時間！という方がいたらエクセルだけで作成してもよいかと思います。
その際は、
［Ctrl］＋［：］ コントロールボタンとコロンを同時に押すと現在時刻が入力されますので、このショートカット是非覚えておいてください！便利！
ちなみに日付は［Ctrl］＋［；］ コントロールボタンとセミコロンです！
こちらも便利！
この記事が気に入ったらサポートをしてみませんか？