はじめに
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
とすると、
上記のようになります。
描画に失敗している箇所の原因と対策
上の画像を見ると末尾の一行がうまく反映されていないです。
表示形式を日付にすると原因は一目瞭然で、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"))
上のように、日付が変更した場合も問題なく対応が可能です。
終わりに
今回は1分毎のタイムチャートですが、何分でも考え方は同じとなります。
0時前後でうまくいかないという同じ悩みを持たれた方は、ぜひご参考ください。
