#4 打刻データを出勤簿にする／ITド素人がGoogle無料アプリで業務管理システムを作る！

ちゃー

2024年11月11日 15:53

11/27 追記
ちょっと管理が楽になる関数を見つけたので記事を修正。

今回は、前回作ったタイムカードフォームの回答を出勤簿のテンプレートに反映させていきます。

【やったこと】

打刻データを使いやすいようにする加工用シート [data] を作成
[data] : 固有の打刻IDを作成する関数を追加
[data] : 打刻時間（HH:MM表記）を取り出す関数を追加
出勤簿に反映させる年月を設定するシート[date]を作成
[tpl] : [data]の打刻データを反映させる関数を各セルに追加
勤怠締日に合わせて日付を調整する

【どういう感じでやったか】

打刻データを使いやすいようにいじる加工シートを作成

蓄積された回答データそのままでは使いにくいので、加工するシートを作成します。
シート名は関数に書きやすいようシンプルに[data]。
[data]のA1セルに「フォームの回答 1」のデータを転記する関数を書きます。

=query('フォームの回答 1'!A:H)

@[data]：個々の打刻データに固有のIDをつける【追記あり】

いつ・誰の出勤、退勤かを区別させるために、各々に打刻データ固有のIDを割り振ります。
列を追加、ここではA列を固有IDにしました。

【11/27 追記】
旧Ver.は一番下まで関数をフィルして、データが1000行超えたら更に行を追加してフィルしなきゃならんかったんですが、ITド素人こと私、つい先日ARRAYFORMULA関数を見つけました。
一番上のセルにだけ関数を入れておけば、いくら途中で行を追加しようとも人的コピペミスが防げるってもんです。（過信はしてないけど）
色々制約はあるようなのですが、ここで作っているものに関しては十分に働いてくれるので助かりました。
もし自分が辞めた場合の引き継ぎが大変楽になりますわ。

=arrayformula(if(B2:B="","",C2:C&ROUNDDOWN(B2:B,0)&D2:D))

順番は　名前＋タイムスタンプのシリアル値＋出退勤

@[data]：打刻時間（HH:MM表記）を取り出す【追記あり】

早退・遅刻を選んで打刻した場合、打刻時間を出退勤時間として計算しなきゃいかんので、打刻時間を HH:MM 表記で取り出しておきます。
列を追加、ここではJ列を打刻時間にしました。

【11/27 追記】
ARRAYFORMULA関数追加。

=arrayformula(if(B2:B="","",text(B2:B,"hh:mm")))

参考URL>>

出勤簿に反映させる年月を設定するシートを作成

弊事業所、経理関係を母体会社と合わせるために勤怠締日が20日なんです。

でも参考URLや他のサイトではみんな１日が月始まり。
21日始まりの弊事業所は年を跨ぐ時にちょっと面倒なことになってしまいます。
なので、年月を設定するシートを別に作り、そっちを経由することで解決しました。
こっちも関数に書きやすいようシート名はシンプルに[date]です。

[data]シートと[date]シート、名前が似ちゃったけど関数に書くときに日本語だと入力切り替えが面倒なので英字にしてます。

@[tpl]：[data]の打刻データを反映させる関数を各セルに追加

ここから個人的に複雑になっていきました。

＊現在反映されているデータの年度と月

年度：[date]の表示欄から関数で転記します。
　月：[date]のデータ欄から関数で転記し、１を足します。

しかし、転記した月に１を足すだけでは21日始まりの弊害が出ちゃう。
（１月分のデータが欲しいとき、[date]で12月を指定すると出勤簿上で13月表記になってしまう）
それを解消するためにここでMOD関数を使います。

=MOD(date!$D$3,12)+1

＊日付

月の初日だけ、DATE関数を使います。翌日以降は=An+1でオートフィル。
セルの表示形式で日付のみにしています。

=DATE(date!$B$3,date!$D$3,21)

=A5+1

＊曜日

日付を転記、セルの表示形式で曜日のみに設定。

=A5

＊打刻（出勤）

【通常出勤】
VLOOKUP関数で取り出し値を指定する。
検索値：固有IDと同じ順で指定　名前＋日付＋出勤
範　囲：[data]のA列からJ列
指　数：[data]のE列の出勤時間データが欲しいので、5
　　　　（A列=1 , B列=2 , C列=3 …）

=VLOOKUP($K$1&$A5&E$4,data!$A:$J,5,FALSE)

【遅刻】
この場合、いつ出退勤したか時刻が定まっていないので、打刻時間を取り出し値に指定する。
検索値：固有IDと同じ順で指定　名前＋日付＋出勤
範　囲：[data]のA列からJ列
指　数：[data]のJ列の打刻データが欲しいので、10

=VLOOKUP($K$1&$A5&E$4,data!$A:$J,10,FALSE)

【休み】
欠席や土日の休みは空白でいいので特に何もしません。

出勤列のセルは必ず【通常出勤】【遅刻】【休み】のどれかなので、IF関数を使います。
【休み】の空白を返されたときに、#N/Aのエラーが見える状態で出ちゃうので、IFERROR関数を使って見えないようにしました。

=IFERROR(IF(VLOOKUP($K$1&$A5&E$4,data!$A:$J,5,FALSE)="遅刻",VLOOKUP($K$1&$A5&E$4,data!$A:$J,10,FALSE),VLOOKUP($K$1&$A5&E$4,data!$A:$J,5,FALSE)),"")

＊打刻（退勤）

出勤と同じように【通常退勤】【早退】【休み】の値を取り出します。
指数の列数は間違えないように注意。

=IFERROR(IF(VLOOKUP($K$1&$A5&F$4,data!$A:$J,6,FALSE)="早退",VLOOKUP($K$1&$A5&F$4,data!$A:$J,10,FALSE),VLOOKUP($K$1&$A5&F$4,data!$A:$J,6,FALSE)),"")

＊実働時間、休憩時間

実働時間は（出勤から退勤までの時間）-（休憩時間）なので、先に作った出勤簿テンプレではちょっと用が足りません。
なので列を追加して【滞在時間】という項目を作りました。（最終的に非表示にするのでどこでもいい。）
【滞在時間】【休憩時間】【実働時間】を表示するかどうかのトリガーは退勤打刻の有無に設定しました。

【滞在時間】
出勤〜退勤までの時間を算出。IF関数を使用。
論理値：""　値がない状態を正とする
TRUE値：""
FALSE値：（退勤時間）-（出勤時間）

=if(F5="","",(F5-E5))

【休憩時間】
滞在時間３時間未満の場合は休憩時間なしのルールなので、IF関数で算出。
論理値：""　値がない状態を正とする
TRUE値：""
FALSE値：もう一回IF関数。滞在時間が0.1243（2:59の時間数値）以上の場合は1:00、未満には空白を返すように設定

=IF(F5="","",(IF(D5>0.1243,"1:00","")))

【実働時間】
IF関数を使用。
論理値：""　値がない状態を正とする
TRUE値：""
FALSE値：（滞在時間）-（休憩時間）

=IF(F5="","",(D5-G5))

＊昼食購入

ここも退勤打刻の有無をトリガーにしました。

VLOOKUP関数で取り出す値を指定する。
検索値：固有IDと同じ順で指定　名前＋日付＋退勤
範　囲：[data]のA列からJ列
指　数：[data]のG列の昼食購入データが欲しいので、7

IF関数で先に取り出した値が「あり」だった場合、１を返す。
IFERROR関数で空欄だった場合は空欄を返すよう設定。

=IFERROR(IF(VLOOKUP($K$1&$A5&F$4,data!$A:$J,7,FALSE)="あり","1",""),"")

＊送迎（行き／帰り）

昼食購入と関数は同じ。VLOOKUP関数の指数とIF関数の論理値を変えただけ。

=IFERROR(IF(VLOOKUP($K$1&$A5&F$4,data!$A:$J,8,FALSE)="行き","1",""),"")

=IFERROR(IF(VLOOKUP($K$1&$A5&F$4,data!$A:$J,9,FALSE)="帰り","1",""),"")

ここまでやったら全ての関数・数式を一番下までコピー。
動かしたくない行や列には必ず $ をつけて固定するのを忘れないようにします。
滞在時間の列は見えてるとちょっと邪魔なので非表示にします。

＊実働合計

最後に実働時間の合計です。
ここは実働時間が返されてるセルの合計数で総出勤日数、実働時間の合計で総実働時間を出しました。
単位の「日」と「H m」はセルの書式設定で付けます。

=COUNT(C5:C35)

=sum(C5:C35)

勤怠締日（20日締め）に合わせて日付を調整する

月の最大日数は31日間です。でも30日間にもなるし、2月は28日間、閏年なんかは29日間になる。
月始めが1日だったら何も問題ないのですが、弊事業所は20日締め・21日始まりなので、ひと月31日間以外のときには締日以降のデータも加算されちゃう。

これに関しては解決策が全く見つからず、ものすごく悩みました。
関数なんか未だよくわかってない自分にとっての鬼門がついに来た。
ばちくそ面倒。

でも使えそうなのをめちゃくちゃ調べて実験してちゃんと解決しました。
えらい。
割と簡単な関数を追加するだけで実現したので、同じ悩みを持つ人の参考になるといいな。
関数を追加するのは、末から3日間。とその下の3セル。

＊日付を枠外に移動する

関数を入れるセルに該当する末3日間の日付を枠外に移しました。
文字色は白にして見えないようにします。

＊末3日間のセルにIFS関数を追加

結局のところ余分な日付を非表示にしたいので、余分な日付を順に潰して条件に合う日は表示すればいい。
なのでそれを叶えてくれそうなIFS関数を使いました。

ひと月28日間の場合、29日間の場合、30日間の場合、31日間の場合　の日付を条件にして、条件と合わなければ空欄、合致すれば枠外に逃した日付を返すようにしました。

ここに入る日付は18,19,20,21なので、18,19,20の場合に日付を返す

=IFS(A36=DATE($A$1,$E$1,21),"",A36=DATE($A$1,$E$1,20),A32+1,A36=DATE($A$1,$E$1,19),A32+1,A36=DATE($A$1,$E$1,18),A32+1)

=IFS(A37=DATE($A$1,$E$1,22),"",A37=DATE($A$1,$E$1,21),"",A37=DATE($A$1,$E$1,20),A33+1,A37=DATE($A$1,$E$1,19),A33+1)

=IFS(A38=DATE($A$1,$E$1,23),"",A38=DATE($A$1,$E$1,22),"",A38=DATE($A$1,$E$1,21),"",A38=DATE($A$1,$E$1,20),A34+1)

ここまで実装したら、誰か一人フォームで打刻して氏名欄にその人の名前を記入してみます。
正く値が取り出せていたら成功です。
もし、関数も名前も全部ちゃんとあってるのに値が返ってこない場合は、
関数の入ったセルをダブルクリックして編集状態にし、そのまま確定すれば反映されます。なんでかはわかんないけど。

また、[date]で年度と月を指定し、月末が正く取得できているか確認します。

こんな感じ。
次は今回作った出勤簿からデータを引用して工賃明細書をつくったはなしをさせてください。

サブアカウントのまっさらGoogleドライブに実際作った業務管理システムを順に再現させながら記事を書いているので更新は不定期になります。

よければまた見てくださると嬉しいです。

今日はこのへんで。ありがとうございました。