スケジュール管理表を作成 ② ユーザー定義関数でIF文の多重入れ子解消

昨日から、スケジュール管理表をExcelで作成している。
infoment.hatenablog.com
今日は、昨日の続きから。
f:id:Infoment:20210112201850p:plain

昨日は、数式で以って終了予定時刻を算出した。
f:id:Infoment:20210112202005p:plain

実はこれに、もう少し条件の追加が必要だ。しかしこれ以上、IF文の多重
入れ子構造になると、ぱっと見で理解できなくなる。それは回避したい。

ということで、ユーザー定義関数を作成することにした。

【終了予定時刻】の条件

  1. 実施内容が未記載の場合、何も表示しない。
  2. 表の一行目である場合、始業時刻+作業予定時間とする。
  3. 表のn行目であって且つ
    ① n-1行目の案件が終わっていない場合、
      n行目の作業予定時間 + n-1行目の終了予定時刻とする。
    ② n-1行目の案件が終わっている場合、
      n行目の作業予定時間 + n-1行目の終了時刻とする。

ユーザー定義関数にすると、こんな感じだ。

' 終了予定時刻
Public Function tm終了予定時刻()

        Application.Volatile
    
    ' 数式がセットされたセル。
    Dim TargetRange As Range
    Set TargetRange = Application.ThisCell
    
    ' 数式がセットされたセルを含むテーブル。
    Static Tb As ListObject
        If Tb Is Nothing Then
            Set Tb = TargetRange.ListObject
        End If
    
    ' 数式がセットされたセルの、テーブルにおける行番号。
    Dim RowIndex As Long
        RowIndex = TargetRange.Row - Tb.Range(1).Row
    
        With Tb.ListRows(RowIndex)
            If .Range(列名.en実施内容) = vbNullString Then
                tm終了予定時刻 = vbNullString
                
            ElseIf RowIndex = 1 Then
                tm終了予定時刻 = .Range(列名.en作業予定時間) + TimeValue("8:00")
                
            ElseIf Tb.ListRows(RowIndex - 1).Range(列名.en終了時刻) = vbNullString Then
                tm終了予定時刻 = .Range(en作業予定時間) + _
                                 Tb.ListRows(RowIndex - 1).Range(en終了予定時刻)
                                 
            Else
                tm終了予定時刻 = .Range(en作業予定時間) + _
                                 Tb.ListRows(RowIndex - 1).Range(en終了時刻)
            End If
        End With
        
End Function

同様に、残り時間もユーザー定義関数で作成する。
なお、残り時間に関しては以下の仕様とする。

  1. 終了予定時刻前に終わった場合は、残り時間をhh:mmの書式とし、
    その文字色を黒とする。
  2. 終了予定時刻超で終わった場合は、残り時間を-hh:mmの書式とし、
    その文字色を赤とする。

注意点として、時間差を求めた結果がマイナスとなった場合、エラーになる。
従って、予め引かれる側が引く側より大きくなるよう、確認が必要だ。

' 残り時間。
Public Function tm残り時間() As Variant

        Application.Volatile
    
    ' 数式がセットされたセル。
    Dim TargetRange As Range
    Set TargetRange = Application.ThisCell

    ' 数式がセットされたセルを含むテーブル。
    Static Tb As ListObject
        If Tb Is Nothing Then
            Set Tb = TargetRange.ListObject
        End If
    
    ' 数式がセットされたセルの、テーブルにおける行番号。
    Dim RowIndex As Long
        RowIndex = TargetRange.Row - Tb.Range(1).Row
            
    ' 終了予定時刻と現在の時刻の大小関係から、数式を決定する。
    ' ※引いた結果がマイナスの場合、エラーになることへの処置。
        With Tb.ListRows(RowIndex)
            If .Range(列名.en終了予定時刻) = vbNullString Or _
               .Range(列名.en終了時刻) <> vbNullString Then
                tm残り時間 = vbNullString
            ElseIf .Range(列名.en終了予定時刻) >= Time Then
                tm残り時間 = Format(.Range(列名.en終了予定時刻) - Time, "hh:mm")
                TargetRange.Font.Color = vbBlack
            Else
                tm残り時間 = Format(Time - .Range(列名.en終了予定時刻), "-hh:mm")
                
                ' 負時間の色。赤より少し暗くしている(任意に変更可)。
                TargetRange.Font.Color = 192
            End If
        End With
        
End Function

例えば、↓ の最初の時点で案件Bの終了予定時刻は9:30であるが、
f:id:Infoment:20210112220910p:plain

案件Aが予定から30分遅れて9:30に終了した場合、案件Bの終了予定時刻は
自動的に10:00に繰り下げられている(残り時間も同様)。
f:id:Infoment:20210112221109p:plain

また例えば案件Dの優先度を案件Cより上げた場合も、案件D以降の終了予定
時刻は自動的に再設定されることになる。
f:id:Infoment:20210112221457g:plain

次回は、

  1. 実際作業時間
  2. 予実比率

などの設定に挑戦です。

参考まで。