(O+P)ut

頭の整理のために、Slerで働く若手インフラエンジニアが、【 (O+P)ut = OutPut 】していく場です。

【日付跨ぎ対応版】Excelでタイムチャート自動描画

Excelにてタイムチャート、または、ガントチャートを描画する際には マクロ または 条件付書式 で行うのが一般的です。
細かいことをしようとするとマクロの方がよいですが、更新後すぐに描画する使いやすさから条件付書式を採用する方も多くいらっしゃるかと思います。

条件付書式を用いたタイムチャートでよく見るのが

=AND(作業開始時間<=タイムチャート上の時間+"0:0:01",作業終了時間>タイムチャートの時間+"0:0:01"))

という数式を、タイムチャートの描画エリア全体に適用するやり方です。

例えば、

23:58 23:59 0:00 0:01 0:02
1 23:58:00 23:59:00
2 23:59:00 0:01:00
1 0:01:00 0:02:00

上のような表を作成し、右側のエリアに描画したいとします。
左下の1,2,1は作業時間で、開始時間と作業時間を用いて終了時間は

=開始時間+TIME(0,作業時間,0)

で算出し、23:58...0:02 も 左のセル+TIME(0,1,0) で時刻を出しています。

上の表の左端のセルがA3だとすると

=AND($B4<=D$3+"0:0:01",$C4>D$3+"0:0:01")

という条件で
適用範囲を

=$D$4:$H$6

とすると、
f:id:mtiit:20171114152804p:plain
上記のようになります。

最後の一行がうまく反映されていないです。

表示形式を日付にすると一目瞭然で、0:01というのが24:01と認識されていないことが原因となります。

1 1900年1月0日 1900年1月0日
2 1900年1月0日 1900年1月1日
1 1900年1月0日 1900年1月0日

そこで、タイムチャートの開始時刻 23:58 よりも開始時刻が小さければ1日を足す処理をしてあげると、この問題は解決します。

例えば、

23:58

という開始時刻をD1におき、そことの比較で1日を足す(日付をYEAR MONTH DAYで取得し、時間を出す)、下記の条件式に変更すれば

=IF($D$1-$B4>0,AND(DATE(YEAR($B4),MONTH($B4),DAY($B4)+1)+$B4<=D$3+"0:0:01",DATE(YEAR($C4),MONTH($C4),DAY($C4)+1)+$C4>D$3+"0:0:01"),AND($B4<=D$3+"0:0:01",$C4>D$3+"0:0:01"))

f:id:mtiit:20171114154522p:plain
上のように、日付が変更した場合も問題なく対応が可能です。

今回は1分毎のタイムチャートですが、何分でも考え方は同じとなります。
0時前後でうまくいかないという同じ悩みを持たれた方は、ぜひご参考ください。