スポンサーリンク/Sponsored Link

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

勤怠実績のグラフ化(フローティング横棒グラフ)
Excel(エクセル)
スポンサーリンク/Sponsored Link

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

10月勤怠実績グラフ

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

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

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

スポンサーリンク/Sponsored Link

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

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

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

勤務時間グラフの概念図
出勤時刻 a
退勤時刻 b
系列1 長さ a 塗りつぶし なし
系列2 長さ 18:00-a 塗りつぶし グレー
系列3 長さ 22:00-18:00 塗りつぶし 黄色
系列4 長さ b-22:00 塗りつぶし 赤
画像をタップすると拡大できます

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

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

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

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

日付 10月1日(木) 出勤 8:51:09 退勤 18:02:01
数式を使って変換
日付 10月1日(木) 空白列 8:51:09 所定内 9:10:52 所定外 0:00:00 深夜 0:00:00

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

  • 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関数で仕分けして、各系列がそれにふさわしい「棒の長さ」を表現するように計算しています。

スポンサーリンク/Sponsored Link

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

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

最初にあらわれるグラフ
作業セルから生成したばかりのグラフ

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

データの選択ダイアログ
日付を削除
日付を削除したグラフ
  • オレンジの系列を選択し、「塗りつぶしなし」を設定
  • 各系列の「塗りつぶし」を好みの色に設定

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

データの選択ダイアログ
横(項目)軸ラベルの 編集 をクリック
軸ラベルの範囲
日付が入っているセル範囲を選択してOK

こうすると日付が出ますが、シリアル値になってしまった場合は、縦軸の表示形式を “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時の目盛りが出なくなるので、わずかに大きい値を入れました。

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

10月の勤務実績
スポンサーリンク/Sponsored Link

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

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

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

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、オンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

キュリオステーション志木店運営をフォローする
スポンサーリンク/Sponsored Link

コメント

  1. サンタロウ より:

    早速のご教授ありがとうございます。

    はずかしながら「加算貼り付け」という方法をしりませんでした。

    今までしていたやり方はかなりな手間でした、、、、。

    いつもいつもありがとうございます。

  2. サンタロウ より:

    いつもお世話になっております。

    今回の記事は難しいです、、、、。

    ですが、頑張って拝読しております。

    質問があるのですが、お願い致します。

    例えば
    9:00~10:00
    10:00~11:00
    12:00~13:00
    13:00~14:00 とあった場合に
    かく時間の予定にずれがあった場合にですが
    9:10~10:10
    10:10~11:10
    12:10~13:10
    13:10~14:10
    このような場合に違うセルに[A1+time(0,10,0)]で時間の足し算等ができるのわかるのですが
    作業セルを作りそこで足し算をしてまた変更したい箇所にコピーとなると
    手間ですし、間違いがおこります。
    そこで
    ご教授頂きたいのは
    元の変更前の時間に上手く変更を加えて例えばオートフィルのような
    方法で変更する方法をお教え願いたいです。

    変な分かりにくい質問で申し訳ありません。

    宜しくお願い致します。

    • コメントありがとうございます。これはつまり、「元データ全体に一斉に10分を足す」と捉えてよろしいでしょうか。
      そうすると、例えば「加算貼り付け」という方法があります。

      足したい数値を、K2セルに作りました。ここには=TIME(0,10,0)と書いています。K2セルの数値の書式を、あらかじめ貼り付けたいB~C列の書式に合わせておきます。
      K2セルをコピーして、上のように足したい範囲を選択し、「形式を選択して貼り付け」→「加算」と選択します。
      すると、選択範囲に10分をまとめて足してくれます。元セルの値そのものが変更され、数式は貼りつきません。

タイトルとURLをコピーしました