ガントチャート

May 14, 2024

excel Excel for Mac Mac ForMe FormeStudio QC KAIZEN

執筆:EugeneAmnis

ガントチャート

各工程のスケジュール調整に使われるガントチャート。Excelでもよく題材にしているサイトを見かけます。今回は最低限のVBAのコードで実装してみます。

作成方法

今回のガントチャートは条件付き書式と、ユーザー定義関数を含む2つのマクロで実装します。データの入力は開始日時と完了日時のみを考慮してセル1つを1日とした上で該当期間であれば色を変える形にします。尚、休日祝日は除くようにします。

タスク名や担当などの入力できるようにしますが、実際の動きには関与しません。なぜコーディングが必要なユーザー定義関数を使用するかといえば、Excelのシート関数のみで実装しようとすると結構複雑な関数群になってしまうからです。複雑な関数は破損しやすいことを踏まえ、今回はコード数も比較的少ないのでユーザー定義関数を利用します。

1.祝日データ取得と日付セル成形

祝日データ取得はこの記事から拝借します。まず、テーブルを挿入して開始時刻と完了時刻に日付のみ入力できるようにします。次に日付に対応するセルの幅を高さと同一にします。この操作の際は印刷モードで単位をｃｍするとやりやすいです。(通常モードではピクセルとインチなので設定しづらいです。)

2.条件付き書式を設定

条件付き書式を日付セル群に設定します。ここでは休祝日を−１、タスク内日を１、タスク外日を空欄としています。この数値に対応するように条件付き書式を設定します。(画像では下の部分)

3.ユーザー定義関数を作成

ここからVBAの出番です。日付の計算には日付範囲のヘッダーに日付を表示し、それを元にユーザー定義関数で休祝日、タスク内、タスク外を判定するします。ヘッダーの日付は以下の関数で表示します。

=日付起点セル + 1

この関数を入力したセルを表示形式を日付(曜日付きyyyy/m/d aaaと入力する)にして見やすくするために表示を斜めにします。そしてオートフィルで日付範囲全てに適用します。ガントチャートを新規作成する場合に開始時間を入力後、日付セルのヘッダーが対応する日付に書き換えられます。

次に本丸と言える休祝日、タスク内、タスク外を判定するユーザー定義関数をコーディングします。このユーザー定義関数は開始日、完了日、休祝日範囲、日付を引数に持ち、休日であれば-１を返し、タスク内であれば１を返し、タスク外では空を返す仕様になっています。この関数を日付セルに入力することでガントチャートのコア部分を実現しています。実際のコードは以下になります。

Function InSchedule(ByVal startDay As Date, ByVal finishDay As Date, ByVal currentDay As Date, Optional ByVal holidayList As Range) As Variant
Dim cd As Date

If startDay = 0 Or finishDay = 0 Then
    InSchedule = ""
Else
    cd = currentDay
        
    If cd >= startDay And cd <= finishDay Then
        If Weekday(cd) = 7 Or Weekday(cd) = 1 Then
            InSchedule = 0
        Else
            InSchedule = 1
        End If
    Else
        InSchedule = ""
    End If
    
    If Not holidayList Is Nothing Then
        For Each d In holidayList
            If cd = d.value And (cd >= startDay And cd <= finishDay) Then
                InSchedule = 0
                Exit For
            End If
        Next
    End If
End If

End Function

あとは条件付き書式を設定することで完成です。