ひるタブ、第13回ガントチャートで勤怠管理の見える化
(今回はあさタブ2３回と同じ内容です）
若干のバグを発見したので改版してます。
前回、グラフ軸の書式をきれいにして、次回はグラフ自体をと予定していたのですが、勤務先の職場の勤務形態が変更になりまして、ざっくりいうとコアタイム3時間のフレックス勤務になりました。しかも在宅勤務がベースです。以前にもまして自己管理力が問われるようになりました。自由度が上がったと張り切って、働きすぎで、100時間は論外としてこれまでの40時間越えはまずいですし、家事に追われてアンダーになるとそれはそれで、上司をドキドキさせます。月末に向けて安定した着地が必要です。なので、勤務管理自体は各社各様のツールがあるかと思いますが、日付、出社時刻、勤務時間の3つを使ってガントチャートと従来定時と呼んでいた7.5時間勤務のベースとの日ごとの差分を表示させてみたいと思います。
では、最初のハードルが、勤怠表・勤務表という表を、CSVやエクセルにはぎとる作業です。勤務先の勤怠のWeb画面は、ダウンロードという項目がないので、Ｗｅｂの画面を月初の日から、月末までをドラッグしてエクセルに張り付けるという原始的な方法を採用しました。
勤務表のイメージはこんな感じ
出社時刻と退社時刻があればいいじゃないかと思われますが、実はフレックスは中に勤務しない時間帯を設けやすく、保育園の送迎や在宅ならではの家事に使用した時間を抜いて申告して、それをシステムが休憩時間とセットで算出しているので、今回は労働時間も所与のものを使っています。そういうことがなければ、単純にdiffを取っていただいて構いません。
データを取り込みます。
データインタプリタは使いません、また、日付に近いもの、数値のものは自動で変換されている場合は最初に合わせてください。
・今回は、日は日付型、出社と退社と実労働時間は文字型です。
今回、準備が少し長くなります。
①出社時刻を日付関数にする。
②労働時間を日付関数にして、さらに24時間での小数点表示に変換する。
今回は、3時間未満（基本あってはいけないので有給を使う）の日、標準労働時間内の日、標準時間を超える日の3種類で色分けをします。
そのため、
③7.5時間を超える分について、7.5時間分と超過分について、労働時間を分計します。
④超過分を折れ線で表すために、分計した超過分を時分で計算します。
⑤その他 24時間での小数点表示と日付型の間で型変換等をします。
と細かい準備が必要です。
先に完成イメージを見せるのはちょっと勿体ないんですが、今回は淡々と作る数式を何に使うかのイメージがないとつらいので説明を入れました。
では、①出社時刻を日付関数にする。からやりましょう。
これは、データソースの所で日付型にしても構わないのですが、そうすると1900年というありがたい年数がついてきて、厄介なので、今回は
出社時刻：DATEPARSE('hh:mm',[出社])
というフィールドを定義します。
②労働時間を日付関数にして、さらに24時間での小数点表示に変換する。
労働時間：dateparse('hh:mm',[実労働時間])
というフィールドを定義します。さらに、Tableauで日付に関するガントチャートを作る場合は、その労働時間を24時間が1になるような小数点で表します。整数で作ると60進法の管理をしないといけないのでこの仕様に合わせます。9時は0.375 12時は0.5 といった数値にします。
これは一発でできる関数がなく、DATEPART関数で時間と分を別に取り出して、それを60分で割って、24時間で割ります。これがちょっとわかりにくいですね。
労働時間(分/24H)：(DATEPART('hour',[労働時間])+DATEPART('minute',[労働時間])/60)/24
これが、今回のキモとなる数式です。
③7.5時間を超える分について、7.5時間分と超過分について、労働時間を分計します。
ここは無理しなくてもいいのですが、Tableauらしく凝ってみました。
まずは7.5時間越えをした時刻の算出です。
7.5超開始時刻：
IF [労働時間(分/24H)] >= 7.5/24 THEN dateadd('minute',30,dateadd('hour',7,[出社時刻]))
ELSE
DATEPARSE('hh:mm',[退社])
END
7.5に満たない場合は退社時刻を入れ労働時間のラベル表示を可能にしたい
ここは、ややこしいのですが、時間と分は同時に足せないので、こうしてまず7時間をたして、次に30分を足しています。満たないものは退社時刻をそのまま入れてます。
7.5時間を超えない分の算出は、IF文をつかって、超えていれば一律7.5/24にします。性能を考えれば0.3125を置いても構いません。パラメータにするともっとシートの柔軟性も上がるかもしれません。
7.5迄の労働時間：
IF [労働時間(分/24H)] >= 7.5/24 THEN 7.5/24
ELSE [労働時間(分/24H)]
END
続いて、いわば残業分の算出です。こちらも、書いてみると単純な関数です。NULLにしないで明示に0を設定しています。
7.5超労働時間：
IF [労働時間(分/24H)] >= 7.5/24 THEN [労働時間(分/24H)]-7.5/24
ELSE 0
END
④超過分を折れ線で表すために、分計した超過分を時分で計算します。
これはもう少しエレガントな方法があればなんですが、集計の奥の手を使います。
標準積算時間、つまり定時で働いたら何時間とうものです。休暇の対応が難しいので、今回はすいません、システム依存にさせてもらってます。使っているシステムが、一日休み（休暇、土日）の場合、出社時刻が"XX:XX"になるのに依存して、判定しています。
ここから先の折れ線グラフようの数値はガントチャートとことなり、1時間を１とした時単位の数値です。30分は0.5になります。
SABERのみなさまにはおなじみですが、Previous_Valueという変わった関数を使っています。前の値ということで、除外日でなければ、前日の値に7.5を足すというものです。
標準積算時間：
IF min([出社])="XX:XX" THEN PREVIOUS_VALUE(0)
ELSE PREVIOUS_VALUE(0)+7.5
END
次に労働時間の積み上げです。こちらは、加算条件がないので、すでに計算されている日々の労働時間をRUNNING_SUMで累積をだしています。
労働時間実績:RUNNING_SUM(sum([労働時間(分/24H)]))*24
で、この差分を取ると、今標準労働に対して、残業が発生しているのかどうかがわかります。
労働時間差分：[労働時間実績]-[標準積算時間]
で、この値は24時間の小数なので、⑤その他 24時間での小数点表示と日付型の間で型変換等をします。で、差分を時刻を表す文字列にしています。ここで日付型が王道ですが、またありもしない年や月が小姑のように現れるといけないので、文字列のままにしています。
労働時間差分の時分：
str( int([労働時間差分]))+":"+str(int(([労働時間差分]-int([労働時間差分]))*60))
整数部分はintで取り出してそのまま時とするのは簡単なのですが、小数部分を取り出すというのは一度に出せる関数がないので、差分から、時間の分を引いて、60倍にしたものを文字列化しています。
このくらいになってくるとノンプログラミングといいつつ、普通のプログラムの課題に近いですね。
⑤最後にもう一つ、勤務時間で色分けをするための関数を定義します。今回は
3時間未満、7.5時間未満、7.5時間超を識別します。
労働時間ランク：
IF [労働時間(分/24H)]<3/24 THEN "アンダーコアタイム"
ELSEIF [労働時間(分/24H)]<7.5/24 THEN "7.5時間未満"
ELSEIF [労働時間(分/24H)]<12/24 THEN "定型なら要協議"
ELSE "帰りなさい"
END
ここまでで大体材料が揃いましたので、組み立てていきます。
まずは、ガントチャートを作ります。
今回はサンプルストアでなく、勤務時間を記録したものの複写です。
ガントチャートのシートは「勤怠シート」にします。
行に、日の年月日、曜日、出社、退社を入れます。
列に、出社時刻とをいれ、大きさに7.5時間までの労働時間を入れます。
正確な日付表示にします。
ツールヒントに7.5超開始時刻を入れます。
出社時刻にNULLが出たら排除します。働いていない日は除外しましょう。休暇や週休日を意識したければ、しなくても構いません。
労働時間ランクを入れます。
続いて、列に7.5超開始時刻を入れます。
不連続はメジャーの前に来るので、このままではちゃんと表示できないので、正確な日付表示にします。
こちらは、大きさに7.5超労働時間を入れます。マークを間違えないようにしましょう。ラベルに実労働時間を入れます。
2つを二重軸にします。
まず、出社時刻のマークに労働時間ランクを入れます。マークを間違えないようにしましょう。
まだきれいにかさならないので、軸をクリックして軸の同期をします。
定型でいうところの残業時間が灰色になっています。
きヘッダの表示がうまくカスタマイズできませんでした。片方の軸だけ非表示にできず、[1900/1/1]というのを非表示にできませんでした。
続いて、２つめのシート 「標準時間との乖離」にします。
こちらは、列に日を
行に労働時間差分を入れます。
ラベルに労働時間差分の時分を入れます。
軸の値とラベルが同じオブジェクトで表せるのが理想ですが、時刻のような60進表示、ラジアンと角度表示、XY座標と角度座標のように描画に向いているのと人間の理解に向いているのはある場合、こうして２つ置くことになります。
こちらも、日でNULLの日を削除します。しかし、表のように明示的に出ません。なので、「日」をフィルタにいれます。
日付の場合は、連続風のフィルタですが、
正しくは、特別を選択してNULL値を除くが良いようですが、このままOKでもNULLの日は排除されるようです。
最後にダッシュボードを作りシート名を「勤務状況」にして
２つのシートを並べます。
今回はドリルダウンやハイライト等これ以上のアクションはないですが、労働の見えるかができて面白いと思っていただければうれしいです。
えらく長いブログになってしまいましたが、時刻と時間帯を扱うのがポイントです。４つのタイプがあります。
・文字列のMM:HH 元データ、表示用データ
・日付型、年はデフォルトで入ります。 ガントチャートの起点
・24時間を１とした場合の浮動小数点表示 ガントチャートの大きさ
・1時間を１とした場合の浮動小数点表示 時間のランク分け等
ガントチャート自体は日付を扱うこと以外は割と簡単です。
あとは、日付をクリックすると、元のシステムへやスケジューラへのリンクやを埋め込むとかすると面白いかと思います。
この記事が気に入ったらサポートをしてみませんか？