0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Googleデータポータル(DataStudio)で時間を集計する方法

Posted at

はじめに

Googleスプレッドシートで以下のように2:11のような形式で時間を記録していて、それをGoogleデータポータルで集計したいときに一工夫必要だったので残しておきます。

スクリーンショット 2021-09-14 23.04.05.png

ここでは労働時間を集計したいとします。
これをそのままデータポータルに追加すると、労働時間はテキストと認識されてしまい、うまく集計することができません(下図左:時間の集計ではなく件数として集計されてしまっている)。
タイプを数値に設定してもうまくいきません(下図右:null)。

スクリーンショット 2021-09-14 23.30.50.png

方法1:スプレッドシート側で数値に変換

スプレッドシート側に変更を加える方法です。
例えば、以下のようにします。

=ARRAYFORMULA(IF(F2:F1000,VALUE(F2:F1000)*24,""))

スクリーンショット 2021-09-14 23.51.38.png

ポイントはVALUE(F2:F1000)*24で、例えば2:112.18(≒ 2 + 11/60)のように時間単位の数値に変換できます。

あとはこれをデータポータルに再接続すれば、数値フィールドとして認識され、時間を集計することができます。

スクリーンショット 2021-09-18 12.03.04.png

方法2:データポータルの計算フィールドを利用

方法1では、データソースであるスプレッドシートに手を加える必要があります。
データソースはそのままに、データポータル側で対処することもできます。

労働時間2:11のような時間の文字列が入ったフィールドだとします。
このとき、カスタムの計算フィールドで計算式を以下のようにします。

HOUR(PARSE_DATETIME('%H:%M', 労働時間)) 
+ MINUTE(PARSE_DATETIME('%H:%M', 労働時間)) / 60

スクリーンショット 2021-09-14 23.56.07.png

中身としては、PARSE_DATETIME('%H:%M', 労働時間)2:11のような時間の文字列をDATETIMEにします。
そのDATETIMEからHOURとMINUTEで時間の部分(=2)と分の部分(=11)を取り出します。
分は時間に変換するために60で割ります(11/60≒0.18)。
時間の部分の数値(2)と分を時間に変換した数値(0.18)を足して、2:11を時間に変換した数値(2.18)が得られます(方法1で説明した数値と合っていますね)。

これでデータソースに手を加えずに時間を集計することができました。

スクリーンショット 2021-09-18 11.03.43.png

※形式の異なるデータが混じっていると下のようなエラーが出るかもしれません。

スクリーンショット 2021-09-15 0.11.18.png

余談ですが計算フィールドにはグラフ固有のものとデータソースのものがあり、それぞれ異なる利点があります。
詳しくは下記を参照のこと。

おわりに

そもそもデータポータルを使わずスプレッドシートで十分ならば方法1だけ知っていれば大丈夫です。
とはいえデータポータルも何かと便利な場面があるので、この記事が誰かのお役に立てば幸いです。

0
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?