勤怠時間が1日に複数列ある #CSV を使って #GoogleSheets で日単位・月単位の合計時間を計算する例 ( 勤怠管理 ) (稼働時間の計算) ( #エクセル の参考にもどうぞ )

# まとめ

列を二個使って、次のように行ごとに計算する。

列番号はあくまで例。

H列

```
=INDIRECT(CONCATENATE("C",ROW())) - INDIRECT(CONCATENATE("B",ROW())) + INDIRECT(CONCATENATE("E",ROW())) - INDIRECT(CONCATENATE("D",ROW())) + INDIRECT(CONCATENATE("G",ROW())) - INDIRECT(CONCATENATE("F",ROW()))
```

I列

```
=SUM(H2:INDIRECT(CONCATENATE("H",ROW())))
```

# CSV

- 次のようなCSVがあるとする。
- 1日あたりの開始時間と終了時間のペアが複数個あり、それぞれに列が分かれている。

```
月日,開始時間(1),終了時間(1),開始時間(2),終了時間(2),開始時間(3),終了時間(3)
2019/08/01,6:45,15:45,18:20,20:20,,
2019/08/02,6:55,15:25,17:05,19:20,,
2019/08/03,,,,,,
2019/08/04,,,,,,
2019/08/05,6:45,10:45,12:00,14:45,16:35,18:40
2019/08/06,6:45,10:45,13:00,19:55,,
2019/08/07,6:40,10:45,11:40,14:00,16:45,19:40
2019/08/08,7:15,17:20,,,,
2019/08/09,10:15,13:00,14:00,21:20,,
2019/08/10,,,,,,
2019/08/11,,,,,,
2019/08/12,,,,,,
2019/08/13,6:45,12:10,14:40,17:00,,
2019/08/14,10:00,11:10,13:00,16:50,,
2019/08/15,7:20,11:55,12:15,15:00,16:35,17:20
2019/08/16,6:55,12:35,14:10,18:35,,
2019/08/17,,,,,,
2019/08/18,,,,,,
2019/08/19,6:50,12:15,15:10,19:25,,
2019/08/20,10:05,14:20,17:00,19:00,,
2019/08/21,18:35,20:25,,,,
2019/08/22,8:55,11:55,14:05,14:50,,
2019/08/23,7:10,9:40,11:30,17:35,,
2019/08/24,6:45,9:15,10:55,13:40,,
2019/08/25,7:10,10:15,11:50,16:10,18:50,20:20
2019/08/26,6:45,9:50,10:50,13:15,,
2019/08/27,6:50,12:40,14:15,19:35,,
2019/08/28,6:50,10:25,11:45,15:00,16:25,19:05
2019/08/29,6:45,11:00,12:35,16:20,17:50,19:30
2019/08/30,6:50,10:05,11:30,14:50,16:40,19:40
2019/08/31,6:45,10:20,11:55,15:30,17:55,18:50
```

# Google Sheets

これを Google Sheets に取り込む

ファイル > 開く > アップロード

![image](https://user-images.githubusercontent.com/13635059/64083188-213c6b00-cd56-11e9-80db-0cbbbb847dbb.png)

# Google Sheets にインポートされる

![image](https://user-images.githubusercontent.com/13635059/64083189-226d9800-cd56-11e9-9446-2fa1a2a9d798.png)

# 表示形式

余っている列 H を選んで、表示形式を変更しておく。

表示形式 > 数字 > 経過時間

![image](https://user-images.githubusercontent.com/13635059/64083237-3bc31400-cd57-11e9-8225-a1565bf07398.png)

カスタム数値形式を入力しても可能。エクセルの場合も、こちらでいけるかも。

`[h]:mm; @` 

![image](https://user-images.githubusercontent.com/13635059/64083562-78ddd500-cd5c-11e9-9970-61a9c7a28cce.png)


# 1日の稼働時間を合計する

- 開始時間1 から 終了時間 1 を引いて、時間帯1の稼働時間を得る
- 開始時間2 から 終了時間2 を引いて、時間帯2の稼働時間を得る
- 開始時間3 から 終了時間3 を引いて、時間帯3の稼働時間を得る
- 全ての時間帯の稼働時間を合計する

ということをしたい。

まずは1行分だけ計算してみる。

```
=(C2-B2)+(E2-D2)+(G2-F2)
```

![image](https://user-images.githubusercontent.com/13635059/64083250-8d6b9e80-cd57-11e9-855d-cac7615d3c87.png)

![image](https://user-images.githubusercontent.com/13635059/64083256-ad02c700-cd57-11e9-8ae2-f943f25a7a2d.png)

# 全ての行で自動計算する

1行ずつ入力するのは大変なので、現在行に対して、自動計算するようにする。

```
=INDIRECT(CONCATENATE("C",ROW())) - INDIRECT(CONCATENATE("B",ROW())) + INDIRECT(CONCATENATE("E",ROW())) - INDIRECT(CONCATENATE("D",ROW())) + INDIRECT(CONCATENATE("G",ROW())) - INDIRECT(CONCATENATE("F",ROW()))
```

![image](https://user-images.githubusercontent.com/13635059/64083193-534dcd00-cd56-11e9-981c-78a3619100e4.png)

これを全ての行にコピー＆ペーストする。

![image](https://user-images.githubusercontent.com/13635059/64083303-ce17e780-cd58-11e9-9083-6207aa1777c5.png)

# 稼働時間合計

稼働時間の列 H を元に、合計稼働時間を出したい。

新しい列 I を使う。こちらも表示形式を `表示形式 > 数字 > 経過時間` に変更しておく。

これで24時間以上の合算が可能になる。

```
=SUM(H2:H2)
=SUM(H2:H3)
=SUM(H2:H4)
```

のようなことがしたいので、こちらも全ての行で現在行が自動設定されるように、次の式を入力して、全ての行にコピー＆ペーストする。

```
=SUM(H2:INDIRECT(CONCATENATE("H",ROW())))
```
![image](https://user-images.githubusercontent.com/13635059/64083349-a4ab8b80-cd59-11e9-814c-16f5a113372f.png)

# 月末の稼働時間合計

これで月末には稼働時間の合計が算出される。

![image](https://user-images.githubusercontent.com/13635059/64083352-b12fe400-cd59-11e9-9ac4-a30f391676c7.png)


# ダウンロードする場合

![image](https://user-images.githubusercontent.com/13635059/64083646-ca3a9400-cd5d-11e9-8c87-3dc1c76c84b4.png)

# エクセル 形式で表示可能？

Macの Preview / Numbers で見たところ、ちゃんと表示されなかった。
 
![image](https://user-images.githubusercontent.com/13635059/64083642-be4ed200-cd5d-11e9-8732-debfc001590b.png)

![image](https://user-images.githubusercontent.com/13635059/64083643-bee76880-cd5d-11e9-9623-1fc198a3d734.png)

# CSV形式でダウンロードして Mac の Numbers で表示した例

CSV形式でダウンロードしておくのが良いかもしれない。

![image](https://user-images.githubusercontent.com/13635059/64083661-08d04e80-cd5e-11e9-8e5c-98724ec31689.png)


# Original by Github issue

https://github.com/YumaInaura/YumaInaura/issues/2381








<!-- Update From Qiita API -->

# チャットメンバー募集


何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

https://line.me/ti/g2/eEPltQ6Tzh3pYAZV8JXKZqc7PJ6L0rpm573dcQ





# Twitter


https://twitter.com/YumaInaura


<!-- Update From Qiita API -->