勤務時間の実績表をExcelでフローティング横棒グラフ化する

受講生様からの要望で、勤怠の実績表を分かりやすくグラフにするにはどうしたらいいか、やってみました。

いろんな勤務形態があると思うのですが、ここでは

• 9時始業、18時退勤
• 15分以内の打刻誤差は所定外労働とみなさない
• 22:00を超えたら1分でも深夜労働

という規定でグラフ化してみます。(※労基法上は1分でも所定時間を超えたら所定外労働です)

勤怠実績をグラフ化するときの考え方

上のようなグラフを「フローティング横棒グラフ」と言うのですが、Excel2003以前にはあったこの項目がExcel2007以降廃止され、直接は描けなくなっています。

そこで、「積み上げ横棒グラフ」の機能を使ってこのグラフをどう描くか、考えてみます。

積み上げ横棒グラフは、各データが積みあがっていきますので、各系列の長さはこのような引き算できちんと「長さ」として求めないと、生の時刻データをそのまま放り込んでも変なグラフになります。

そして、1つ目の系列の塗りつぶしを「なし」にすることで、フローティング横棒のように見せることができます。

作業セルを使って、いったんグラフ用の表の表に変換する

そこで、生の時刻データを次のように作業セルを使って、グラフ用の表に変換します。

数式は次のとおり入っています。

• E1 =A4
• F1 =IF(B4=””,0,B4)
• G1 =IF(C4=””,0,IF(C4<=$G$1,C4-B4,$G$2-B4))
• H1 =IF(C4=””,0,IF(C4<=$G$1,0,IF(C4<=$I$1,C4-$G$2,$I$1-$G$2)))
• I1 =IF(C4=””,0,IF(C4>$I$1,C4-$I$1,0))

退勤時刻が所定内、所定外、深夜、のどれに該当するかをIF関数で仕分けして、各系列がそれにふさわしい「棒の長さ」を表現するように計算しています。

「フローティング横棒」を積み上げ横棒グラフで描く

この右側の作業セルを積み上げ横棒グラフにすると、次のようなグラフになります。

日付そのものがデータとして扱われているため、グラフがおかしなことになっています。日付を系列から削除します。なお、状況により、この前に行列を入れ替えなければいけない場合もあります。

• オレンジの系列を選択し、「塗りつぶしなし」を設定
• 各系列の「塗りつぶし」を好みの色に設定

縦軸のラベルを、正しく日付のところから拾う設定をしないと、日付が出ません。データの選択ダイアログから行います。

こうすると日付が出ますが、シリアル値になってしまった場合は、縦軸の表示形式を “m”月”d”日などに変更します。

さらに

• グラフの上下を反転
• グラフタイトルは適宜変更　またはセル参照を設定

とすると、だいたい望みに近いものになります。

値軸の目盛り設定は時刻シリアル値を指定する

最後に、一見しておかしいのは値軸(横軸)です。中途半端な時刻になっています。

最大値・最小値・単位を、時刻シリアル値で設定してやる必要があります。

0.333333 -> time(8,0,0)　 0.041667 -> time(1,0,0) です。どこか適当なセルにtime関数を書くと確認できますので、それを入力します。

最大値は、time(24,0,0) としたいところですが、0になってしまいますので、24時間=1日を表す 1 にします。 1.001としているのは、 1にしてしまうと 24時の目盛りが出なくなるので、わずかに大きい値を入れました。

あとは系列の間隔をちょっと狭くして見栄えをよくしたものが、最初に載せたこちらの画像です。

やり方次第でいろいろなグラフが描ける

いかがでしたでしょうか。やり方次第でいろいろなグラフが描けるものですね。

手短な記事でしたが、ぜひ皆様もおためしください!