前回こんな記事を書きました。
前回の記事では、タイムスタンプから勤務時間を計算する方法について紹介しましたが、計算の方法しか紹介していませんでした。
実際に使っていくうえでは、
- 日付ごとに計算する
- 人ごとに計算をする
- わかりやすくシートにまとめる（できれば自動的に）
といったことが必要です。
そこでこの記事では、前回の記事の続きとして、日付・人ごとに自動集計する方法について、紹介したいと思います。
タイムスタンプから勤務時間を日ごと・個人ごとに自動集計するときの考え方
まず、勤務時間を日ごとに自動集計するための手順について、全体の流れを説明します。
グーグルフォームから回答された内容はスプシ上では以下のように、「タイムスタンプ」、「登録者」、「出退勤」という情報が得られているとします。
また、前回の記事で紹介したように、各行の時刻をtext関数で出力しているとします。
これらの情報を使って、勤務時間を日ごと・個人ごとに自動集計するためには以下の流れで準備をしていきます。
- 日付・人・出退勤カテゴリを用いたキー変数の作成（回答が入っているシート上で）
- 作成したキーから個人ごとに、各日付の出勤時刻・退勤時刻を抽出（別シートにまとめる）
- 抽出された出退勤時刻を使って勤務時間を計算（前回の記事で実施した計算方法）
以下で順番に説明します。
日付・人・出退勤カテゴリを用いたキー変数の作成
Googleフォームの回答が入るシート（デフォルトであれば、フォームの回答1となっているもの）にキー変数を作っていきたいと思います。
ここで作るキー変数を使うことで、後程作成する各日付の出退勤時刻を記載する表をまとめることができます。
図に示したように、フォームの回答が入ったシートの一番左側A列にKeyという変数名を作りました。
このkeyの一番初めのセル（A2）に対して、以下のように関数を記入します。
=text(B2,”YYYY/MM/DD”)&C2&D2
最初のtext(B2,”YYYY/MM/DD”)により、タイムスタンプの年/月/日が取り出せ、次の＆C2＆D2は、それぞれ、登録者情報、出退勤情報を、左側の文字列に結合させるものです。
この関数を入れることによって、上記のデータであれば、「2023/03/01Aさん出勤」というキーが出来上がったと思います。A2以降も同様にすれば、下の図のようになると思います。
このKeyを使って後々データを取り出していきます。
作成したキーから、個人ごとに各日付の出勤時刻・退勤時刻を抽出
データを見ればわかるように、出勤行、退勤行が個人ごとに１行ずつ作られています。
前の見出しで作ったkeyを利用して、出退勤時刻を取り出したいと思います。
また、フォームの回答上に抽出用の場所を取ると、やや見づらくなるため、別シートに抽出したいと思います。
以下のような出勤・退勤表を作りたいと思います。
まずは、A5からE5の文字情報（日付・出勤・退勤・勤務時間（秒）・勤務時間（分）を記入します。
続いて、A6以降（A7、A8、A9…）には日付を入れます。今回はデータもあまりないので、2023年３月の１か月間を書きました。
また、B1には、名前を出す欄を作っていますが、プルダウンを使って名前を選択する形にしています。
あとは各日付で出退勤時刻の抽出となります。
各日付で出退勤時刻の抽出をする場合、以下のように、vlookupを使うことで簡単に抽出できます。
=vlookup(text($A6,”YYYY/MM/DD”)&B$1&”出勤”,’フォームの回答 1′!$A:$E,5,FALSE)
vlookupはExcelの場合と同様、第１引数に「検索する値」、第２引数に「検索範囲」、第３引数に「取り出す値が入った列番号」を示します。
上記の場合は、
vlookup(text($A6,”YYYY/MM/DD”)&B$1&”出勤” が、検索する値であり、前の見出しで作成したKey（例えば、2023/03/01/Aさん出勤）と同じものを作成しています。
次の‘フォームの回答 1’!$A:$Eは、検索範囲を示すものになります。
シートをまたぐ場合はこのように、シングルクオーテーションで範囲をまとめます。
このとき、注意点として、vlookupでは、第２引数である「検索範囲」の一番左端の値を第１引数と比較するため、一番左側の列がkeyでないといけません。
最後の第３引数「取り出す値が入った列番号」には、取り出したい値が入ったセルの位置について、左から数えて何番目にあるかを示します。
今回は、フォームの回答のシートの5列目「時刻」の列の値が欲しいので5と記入します。
同様に、「出勤記録シート」の退勤列（C列）についても同じように、先ほどのvlookup関数内の”出勤”を”退勤”にすることで、今度は退勤時刻を取り出すことができます。
それぞれ書くことができたら、B１にある名前を「Aさん」、「Ｂさん」など変えてみましょう。
今回の入力データであれば、Ａさんは3月1日と2日それぞれの出退勤時刻が記録され、Ｂさんは３月2については出力されない、という状態になっていればＯＫです。
ちなみに、vlookup関数の最後の、FALSEを指定しないと、完全一致していない場合でも値を返してしまうことがあるので、必ずFALSEを明示しておきましょう。
抽出された出退勤時刻を使って勤務時間を計算
ここまで来たら、あとは、前回の記事と同様に、出退勤時刻を利用して、計算すればOKです。
勤務時間（時）は、hour(出勤ー退勤）、勤務時間（分）は、minute(出勤ー退勤）です。
Aさんの状態では、以下のようになるはずです。
おわりに
複数回にわたり、Googleフォームを使った出退勤管理のフォームの作り方を紹介しました。
この記事では、人・日時ごとに自動集計する方法について紹介しました。
今回の記事では、最低限の計算のまとめ方を紹介していましたが、カスタマイズして、バージョンアップをさせることも可能です。
例えば、if文を使って、「もし、休憩有りにチェックを入れた場合は、勤務時間から-1時間する」というような条件式を入れることで反映できそうです。
また、出勤記録がされていない場所はエラー文が表示されて、少し見栄えが悪いかもしれません。
そのような場合は、iferror()を使って、エラーがない時だけ計算をする、という形にすれば見た目もすっきりします（ただし、出勤ボタンを忘れて退勤ボタンだけをおしたときには、勤務時間が18時間（0-18:00）になったりすることもあるので注意が必要です）。
これらの内容が少しでも役に立てば幸いです。
