Power Automate + Forms + Onedrive + Excel online で作る勤怠システム（Excel online 編）

# 目次
- [はじめに](#はじめに)
- [タイムシート](#タイムシート)
    - [必要な項目](#必要な項目)
    - [タイムシートの作成_sheet2](#タイムシートの作成_sheet2)
    - [テーブルの作成_sheet1](#テーブルの作成_sheet1)
- [まとめ](#まとめ)
- [参考文献](#参考文献)

# はじめに
　本記事を見つけて頂き、ありがとうございますm(_ _)m
　本記事は、 [Power Automate(web) + Forms + Onedrive + Excel online で作る勤怠入力システム](https://qiita.com/crispy_1741/items/58d9ffd9172cc48f8d59) の続編になります。今回は最終出力先であるタイムシート、Excel onlineついてです。
![qiita2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1167400/66bedc6a-c199-3674-bfcc-fb9274f0303c.png)

# タイムシート
#### 必要な項目
　まず、最終出力先であるタイムシートに載せたい項目を整理します。
- 日付・曜日
- 出勤時刻
- 退勤時刻
- 休憩時間
- 時間外
- 休暇
- 更新日時
※更新日時については各項目で説明します。

## タイムシートの作成_sheet2
　以上の項目からタイムシートを以下のように作成します。（企業ごとに必要な項目が異なるので一例になります）
![Qiita2-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1167400/971a6e0d-6f12-31a5-2e1f-7f28c2ec2304.png)
## テーブルの作成_sheet1
　テーブルは
- Formsの入力内容の出力先
- タイムシートの取得元
　を担っています。タイムシートの取得元として以下のテーブルを作成します。
![Qiita2-3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1167400/926794a4-59e3-ef28-5b6d-5aa5773384e8.png)
※更新日時は必ず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様々です。
　曜日によるセルや文字の色については条件付き書式でご自由に。

# リンク
- [準備・仕組み 編](https://qiita.com/crispy_1741/items/58d9ffd9172cc48f8d59)
- [Forms 編](https://qiita.com/crispy_1741/items/745f711bb3fa80a08df8)
- [Power Automate 編](https://qiita.com/crispy_1741/items/8b91c6fa2df111f1f4d9)


# 参考文献
- [Timevalue関数](https://www.tipsfound.com/excel/04timevalue)
- [Ceiling関数](https://office-hack.com/excel/ceiling/#:~:text=CEILING%E9%96%A2%E6%95%B0%EF%BC%88%E3%82%B7%E3%83%BC%E3%83%AA%E3%83%B3%E3%82%B0%E9%96%A2%E6%95%B0%EF%BC%89%E3%81%AF,%E3%81%AB%E4%BE%BF%E5%88%A9%E3%81%AA%E9%96%A2%E6%95%B0%E3%81%A7%E3%81%99%E3%80%82)
- [Maxifs関数とその代用](https://nyanto.jimdofree.com/%EF%BD%B4%EF%BD%B8%EF%BD%BE%EF%BE%99%E9%96%A2%E6%95%B0-%E8%AA%AC%E6%98%8E/maxifs%E9%96%A2%E6%95%B0%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E7%AF%84%E5%9B%B2%E3%81%AE%E6%9C%80%E5%A4%A7%E5%80%A4%E3%82%92%E4%B8%80%E3%81%A4%E5%8F%88%E3%81%AF%E8%A4%87%E6%95%B0%E6%9D%A1%E4%BB%B6%E3%81%A7%E6%8A%BD%E5%87%BA%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95%E3%81%A8%E4%BB%A3%E3%82%8F%E3%82%8A%E3%81%AE%E6%96%B9%E6%B3%95/)