Excel de ガントチャート その２

　こんにちは！Excelイケオジです！トレビアーン〜。海風がどうも来ないと思っていたら、海がありませんでした。

準備

　では、気を取り直して、ガントチャートのガント感をガガガガンと追加して参ります。まずはリレーションらしきものを作っていきます。G行とH行の２行を追加して、5行目に『先行タスク名』と書き、タスクBの7行目(G7)に『タスクA』と書きます。タスクBに先行するタスクは”タスクA"ということにします。H行はのちほど使用していきます。

タスクAとタスクBの関係性をそれっぽくするために、タスクBの開始と進捗を変更します。タスクBの開始を『2023/5/23』で、進捗を『0%』にします。これはタスクAが終わっていないのでタスクBが開始できないことを意味しています。

タスクの遅延を知らせる表示

　まずタスクが遅れていることを知らせる機能をつけていきます。タスク名のタスクAがあるB6をクリックし、「条件付き書式」->「新しいルール…」を選択します。

ルールのスタイルは「クラシック」で「数式を使用して、書式設定するセルを決定」を選択し、数式には『=AND(ROUNDDOWN(($E6-$D6)*$F6+$D6,0)<$E$3,$B6<>"")』と書きます。ANDの一つ目は現在の進捗状況が現在の日付よりも小さい場合(遅れている場合)で、２つ目はタスク名の欄が空欄でない場合を示しています。

これをタスク名の列全体に適用するために、「適用先」を『$B$6:$B$14』のように広げます。これでタスクAとタスクBが赤くなっていると思います。

タスクの遅延の原因が先行タスクの場合

　H行に先行タスクAの進捗を書き込むようにしていきます。H7のセルを選択して『=VLOOKUP(G7,$B$6:$F$22,5)』という数式を書きます。"左側にあるセルの文字をB行から探して、F行の値を返す"という意味になります。

H7にタスクAの進捗が正しく表示されたことを確認して、このH7の数式をH6やH7以降にコピーしていきます。

ここから、タスクBが遅れているのはタスクAのせいだということを明確にします。先行タスク名のタスクAが書かれているセルの上のG6を選択して、「条件付き書式」->「新しいルール…」を選択します。

ルールは今までのように、スタイルは「クラシック」、「数式を使用して、書式を設定するセルを決定」にし、数式には『=AND($G6<>"",$F6=0,$H6<>1,$D6<$E$3)』を入力します。これは”先行タスク名が入っていて、現在のプロジェクトの開始が現在の日付よりも前で、進捗が0%で、先行タスクの進捗が100%に達してしない”場合を示し、先行タスク名を赤でハイライトします。

これで先行のタスク名がハイライトされるようになりました。

リレーションを示す線を追加する！

　ここまではセルの操作のみで簡単だったと思います。もっと高度なことをしたい！という声を受け(妄想)、リレーションを示す線をまずはシンプルに書き込むVBAスクリプトを作っていきます。

まずVBAスクリプトを使うために「開発」タブを表示させる必要がありますので、設定から「リボンとツールバー」のウインドウを開いて、「開発」にチェックを入れます。

さっそく「開発タブ」から「ボタン」クリックして、ボタンを作っていきます。

「ボタン」をクリックした後にG2あたりをクリックすると、マクロ登録のウインドウが表示されます。ボタンに登録するマクロ名は『Relation_Click』としておきます。そして「新規作成」をおします。

するとVBAのウインドウが立ち上がってきます。

ここからVBAを作っていきたいところですが、解説が大変なので、ソースをコピペする形にしたいと思います。コメントを沢山つけますので、各自で解読してください。
　物事は全部を同時に理解する必要はなく、層状に構成されている構造を上の層から順番に理解していくのが良いです。そこで重要なのはインターフェイスという概念です。いわゆるAPIというのはApplication Programing Interfaceですが、「使う側から見えるものだけを理解して使っていく」ということだと思っています。タクシーに乗るときは目的地を伝えたりお金を払えれば、車の運転の仕方を理解する必要がありません。また車を運転するときはエンジンのかけ方・アクセル・ブレーキを理解して交通ルールを覚えれば、車のエンジンの仕組み・交通ルールの歴史や意義を理解する必要はありません。ちなみに下層への理解を深める方法としては、タクシーに乗っている時に車の運転の仕方を観察したり、車を運転しながらエンジンの回転数などを観察していく、という手法になります。
　プログラムの構造としては、ボタンをクリックしたら呼び出される関数、セルからセルへラインを引く関数に分けています。ボタンをクリックしたら呼び出される関数は、(1)初めに現在描かれている線を消して、(2)タスクの中で先行タスクが記入されれているタスクを探して、(3)タスクの中から先行タスクを探して開始日と終了日を探して、(4)開始日と終了日のセルを特定して、セル同士を線で結ぶ関数を呼び出します。

'ボタンをクリックした呼び出される関数
Sub Relation_Click()

    '初めに線を消していく    
    wkCnt = ActiveSheet.Shapes.Count
    For i = wkCnt To 1 Step -1
        '線だったら        
        If ActiveSheet.Shapes(i).Type = 9 Then
            ActiveSheet.Shapes(i).Delete
        End If
    Next

    '表示の初めの日付
    StartDate_Sheet = Range("J3")

    'G6 から10行のタスクを一つずつ確認していく
    For Index = 0 To 10
        '先行タスクをG列から抽出
        Previous_TaskName = Range("G6").Offset(Index, 0)
        If IsEmpty(Previous_TaskName) Then  '先行タスク名が無かったら
            GoTo Continue '何もしないで次へ
        End If

        '先行タスク名を全体から検索
        Index_PreviousTask = -1
        For Index2 = 0 To 10
            TaskName = Range("B6").Offset(Index2, 0)
            If Previous_TaskName = TaskName Then '記入されていた先行タスク名と同じタスク名を見つけたら
                Index_PreviousTask = Index2  'Indexを保存しておく
                Exit For
            End If
        Next
    
        '先行タスク名が見つからなかったら次のタスクに移る
        If Index_PreviousTask = -1 Then
            GoTo Continue
        End If
            
        'それぞれの先行タスクの終了日と元タスクの開始日を特定する
        StopDate = Range("E6").Offset(Index_PreviousTask, 0)
        StartDate = Range("D6").Offset(Index, 0)
            
        '日付から線の始点と終点になるセルを特定
        Dim StopCell As Range
        Set StopCell = Range("J6").Offset(Index_PreviousTask, StopDate - StartDate_Sheet)
        Dim StartCell As Range
        Set StartCell = Range("J6").Offset(Index, StartDate - StartDate_Sheet)
            
        'セルからセルへ線を引く関数を呼び出す
        Call DrawLine(StopCell, 1, 0.5, StartCell, 0, 0.5)

        Continue:
    Next
End Sub

'セルからセルへラインを引く関数
Sub DrawLine(StartCell, StartPosX, StartPosY, StopCell, StopPosX, StopPosY)
    ' 始点の座標を計算
    Start_PosX = (StartCell.Offset(0, 1).Left - StartCell.Left) * StartPosX + StartCell.Left
    Start_PosY = (StartCell.Offset(1, 0).Top - StartCell.Top) * StartPosY + StartCell.Top

    '終点の座標を計算
    Stop_PosX = (StopCell.Offset(0, 1).Left - StopCell.Left) * StopPosX + StopCell.Left
    Stop_PosY = (StopCell.Offset(1, 0).Top - StopCell.Top) * StopPosY + StopCell.Top

    ' ラインを引く
    With ActiveSheet.Shapes.AddLine(Start_PosX, Start_PosY, Stop_PosX, Stop_PosY).Line
        .Weight = 3
        .ForeColor.RGB = RGB(0, 128, 255)
        .EndArrowheadStyle = msoArrowheadTriangle '終点を矢印
    End With
End Sub

さて、ボタンをおして線を引いてみると以下のように先行タスクの終了から次のタスクの開始まで矢印がひかれます。

とりあえずバーが表示されて、関係性が表示されて、なんとなくガントチャートっぽくなってきました。ここでできたExcelは下からダウンロードできるようにしておきます。必要な方はダウンロードしてください。次回もこれをさらに発展させていきます！