目次
はじめに
本記事を見つけて頂き、ありがとうございますm(_ _)m
本記事は、 Power Automate(web) + Forms + Onedrive + Excel online で作る勤怠入力システム の続編になります。今回は最終出力先であるタイムシート、Excel onlineついてです。
タイムシート
必要な項目
まず、最終出力先であるタイムシートに載せたい項目を整理します。
- 日付・曜日
- 出勤時刻
- 退勤時刻
- 休憩時間
- 時間外
- 休暇
- 更新日時
※更新日時については各項目で説明します。
タイムシートの作成_sheet2
以上の項目からタイムシートを以下のように作成します。（企業ごとに必要な項目が異なるので一例になります）
テーブルの作成_sheet1
テーブルは
- Formsの入力内容の出力先
- タイムシートの取得元
を担っています。タイムシートの取得元として以下のテーブルを作成します。
※更新日時は必ず1列目に置くようにしましょう。
vlookupで取得を行う際のキーになります。
これで下ごしらえは完了です。次にタイムシートがテーブルの値を取得できるよう関数を入力していきましょう。
タイムシートの関数
日付・曜日
日付・曜日は図のように月、日付、曜日の3つに分けられています。
- 月（A1）
=YEAR(TODAY())
- 1日
=DATE($A$1,A$2,1)
- 2～31日
=IF(A3=" "," ",IF(DAY(A3+1)=1," ",A3+1))
- 曜日
=TEXT(A3,"aaa")
シートを複数作成したい場合は、西暦と月の変わり目に注意し、関数を作成ください。
出勤時刻
=IFERROR(CEILING(TIMEVALUE(TEXT(VLOOKUP($I3,Form1!$A:$G,4,FALSE),"0"":""00")),"00:30"),0)
流れとしてはVlookupで取得した数字を時刻に変換し、30分を境目に繰り上げ繰り下げを行っています。
関数が複数あるので解説します。
-
Vlookup
- I列の更新日時をキーとしテーブルから値を取得します。
-
TEXT
- テーブルに入力されている値を文字列「XX時XX分」に変換します。
-
TIMEVALUE
- TEXTで変換された文字列を時刻に変換します。
-
CEILING
- 30分を境に時刻の切り捨て、切り捨てを行います。
-
IFERROR
- テーブルに取得できる値がない場合空白にします。
※TIMEVALUEを使うのは、Fomrsに時刻・時間の入力機能がないためです。（Automate側で変換する方法もあると思いますが...）
- テーブルに取得できる値がない場合空白にします。
退勤時刻
=IFERROR(CEILING(TIMEVALUE(TEXT(VLOOKUP($I3,Form1!$A:$G,5,FALSE),"0"":""00")),"00:30"),0)
出金時刻と同じです。列が一つズレただけです。
勤怠区分
=IFERROR(VLOOKUP($I3,Form1!$A:$G,3,FALSE),0)
休暇なら反映されます。
休憩時間
=IFERROR(CEILING(TIMEVALUE(TEXT(VLOOKUP($I3,Form1!$A:$G,6,FALSE),"0"":""00")),"00:30"),0)
以下略。
時間外
=IF(OR(E3="有給休暇",D3-C3-F3<=TIME(8,,)),"",D3-C3-F3-TIME(8,,))
勤怠区分が「有給休暇」または「就業時間が8時間以下」なら空白と設定しています。
更新日時
=MAX(IF(Form1!$B:$B=A3,Form1!$A:$A,""))
各曜日に対し最も新しい入力日時 = 最大値を反映します。これによって入力の更新を可能とします。
備考
=IFERROR(VLOOKUP($I3,Form1!$A:$G,7,FALSE),0)
まとめ
今回はタイムシートついての内容でした。
使用した関数についてですが、「Excel onlineなら最新の関数が使えてもっと楽に値の取得が行えたのでは？」と思った方がいらっしゃると思いますが、これには事情があります。
- 社内で使われているOfficeのver.が統一されていない。
- お使いのOfficeで利用で可能な関数は必ず確認しましょう。
- 関数によって処理速度が異なり、場合によってはAutomateの作動時間が極端に遅くなり、フローが停止します。（filter関数重すぎ）
なんやかんやでvlookup様々です。
曜日によるセルや文字の色については条件付き書式でご自由に。