Zenn
🖥

勤怠時間が1日に複数列ある #CSV を使って #GoogleSheets で日単位・月単位の合計時間を計算する例 ( 勤怠管理 ) (稼働

2019/09/02に公開
Excel
CSV
Google Sheets
tech

まとめ

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

列番号はあくまで例。

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

Google Sheets にインポートされる

image

表示形式

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

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

image

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

[h]:mm; @

image

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

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

ということをしたい。

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

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

image

image

全ての行で自動計算する

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

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

image

稼働時間合計

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

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

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

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

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

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

image

月末の稼働時間合計

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

image

ダウンロードする場合

image

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

Macの Preview / Numbers で見たところ、ちゃんと表示されなかった。

image

image

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

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

image

Original by Github issue

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

チャットメンバー募集

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

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

Twitter

https://twitter.com/YumaInaura

公開日時

2019-09-02

Discussion