スケジュール管理表を作成 ⑥ セルのダブルクリックで終了時刻を15分単位で入力

Excel VBA

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

その案件が終わって、終了時刻を入力するとき。手入力するならば都度、
時計に目を向けねばならない。また、タイプミスが起きる恐れもある。
そこで、ダブルクリックで終了時刻を入力することにした。
f:id:Infoment:20210117113431p:plain

例えば、11:07分にその案件を終えたとき。人毎に様々な考え方があって、
私は１分単位で管理する必要は無いと思っている。大まかに仕事の予定と
実績（予実）を管理するならば、15分単位ぐらいで良い。従って

  1. 11:07 ⇒ 11:00
  2. 11:08 ⇒ 11:15

のように七捨八入することにした。Excelでは、MRound関数を用いる。
support.microsoft.com

これを、シートのダブルクリックイベントに設定してみよう。
↓ シートモジュール。

Private Property Get Tb() As ListObject
     Set Tb = Me.ListObjects(1)
End Property

Private Function wf() As WorksheetFunction
    Set wf = WorksheetFunction
End Function

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

        If Intersect(Target, Tb.ListColumns("終了時刻").DataBodyRange) Is Nothing Then
            Exit Sub
        ElseIf Target.Offset(-1) = vbNullString Then
            MsgBox "一つ前の項目が未だ終わっていません。"
            Cancel = True
            Exit Sub
        End If
        
        Target = wf.MRound(Time, "00:15")
        Cancel = True
End Sub

では、実際にやってみよう。
f:id:Infoment:20210117114439g:plain
想定通り、15分単位で入力することができた。

ところで、以下の三つは今までユーザー定義関数で算出していた。

  1. 終了予定時刻
  2. 残り時間
  3. 実際作業時間

引数もなく、単にセルに「=tm終了予定時刻()」のように入力してあって、
考えてみると値がセットしてあるのと何ら変わりがない。むしろ、再計算
のタイミングに気を遣う必要があるなどで、デメリットの方が多いような
気がしてきた。

そこで思い切って、UpdateTableに全て盛り込むことにした。
↓ 標準モジュール。

Private Function wf() As WorksheetFunction
    Set wf = WorksheetFunction
End Function

Public Sub UpdateTable()

    ' 無限ループ回避のため、シートのチェンジイベントを一時停止。
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    On Error GoTo er:

    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
    
    ' 罫線クリア。
        Tb.DataBodyRange.Borders.LineStyle = xlNone

    Dim arr As Variant
        arr = Tb.DataBodyRange
        
    Dim i As Long
        For i = 1 To UBound(arr)
            ' 終了予定時刻。
            If arr(i, en実施内容) = vbNullString Then
                arr(i, en終了予定時刻) = vbNullString
            ElseIf i = 1 Then
                arr(i, en終了予定時刻) = CDate(arr(i, en作業予定時間)) + tm始業時刻
            ElseIf arr(i - 1, en終了時刻) = vbNullString Then
                arr(i, en終了予定時刻) = CDate(arr(i, en作業予定時間)) + CDate(arr(i - 1, en終了予定時刻))
            Else
                arr(i, en終了予定時刻) = CDate(arr(i, en作業予定時間)) + CDate(arr(i - 1, en終了時刻))
            End If
            
            ' 残り時間。
            If arr(i, en終了予定時刻) = vbNullString Or _
               arr(i, 列名.en終了時刻) <> vbNullString Then
                arr(i, en残り時間) = vbNullString
            ElseIf CDate(arr(i, en終了予定時刻)) >= Time Then
                arr(i, en残り時間) = Format(CDate(arr(i, en終了予定時刻)) - Time, "hh:mm")
            Else
                arr(i, en残り時間) = Format(Time - CDate(arr(i, en終了予定時刻)), "-hh:mm")
            End If
            
            ' 実際作業時間。
            If arr(i, en終了時刻) = vbNullString Then
                arr(i, en実際作業時間) = vbNullString
            ElseIf i = 1 Then
                arr(i, en実際作業時間) = CDate(arr(i, en終了時刻)) - tm始業時刻
            Else
                arr(i, en実際作業時間) = CDate(arr(i, en終了時刻)) - CDate(arr(i - 1, en終了時刻))
            End If
        Next i
    

    ' 更新後の値セット。
        Tb.DataBodyRange = arr

    ' 数式の再セット。
        Tb.ListColumns(enNo).DataBodyRange = "=ROW()-ROW(" & Tb.Range(1).Address & ")"
        Tb.ListColumns(en●).DataBodyRange = "=IF([@作業予定時間]="""","""",""●"")"
        Tb.ListColumns(en予実比率).DataBodyRange = "=IF([@実際作業時間]="""","""",[@実際作業時間]/[@作業予定時間])"
    
    Dim ListRow As Excel.ListRow
        For Each ListRow In Tb.ListRows
            With ListRow
            ' ●のサイズ修正。
                .Range(en●).Font.Size = wf.MRound(6 * (Hour(.Range(en作業予定時間)) + _
                                                        Minute(.Range(en作業予定時間)) / 60) + 1, 2)
            ' 残り時間の文字色設定。
                Select Case Left(.Range(en残り時間), 1)
                    Case "-"
                        .Range(en残り時間).Font.Color = 192
                    Case Else
                        .Range(en残り時間).Font.Color = vbBlack
                End Select
            End With
        Next
        
    ' 罫線の再描画。
        For Each ListRow In Tb.ListRows
            If ListRow.Range(en終了予定時刻) >= tm終業予定 Then
                ListRow.Range.Borders.Item(xlEdgeBottom).Weight = xlThin
                Exit For
            End If
        Next
    
er:
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Public Property Get tm始業時刻() As Date
    If ActiveSheet.Range("始業時刻") = vbNullString Then
        tm始業時刻 = TimeValue("8:00")
    Else
        tm始業時刻 = Range("始業時刻")
    End If
End Property

Public Property Get tm終業予定() As Date
    If ActiveSheet.Range("終業予定") = vbNullString Then
        tm終業予定 = TimeValue("17:00")
    Else
        tm終業予定 = Range("終業予定")
    End If
End Property

実は先程のＧＩＦアニメは、上記置き換え後のものだ。今のところ、

  1. 作業予定時間
  2. 終了時刻

のどちらかが変わった時点で更新することとしている。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ListRow As Excel.ListRow
        Select Case Target.Column - Tb.Range(1).Column + 1
            Case 列名.en作業予定時間, 列名.en終了時刻
                Call UpdateTable

        End Select
End Sub

今回は、ここまで。
次回は、案件追加用のフォーム作成に挑戦です。

参考まで。

スケジュール管理表を作成 ⑤ その日に出来る範囲を把握してみる

Excel VBA

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

これから少し、シートの塗りつぶしなどによる見える化も行っていく予定。
そのために、見た目は極力質素にしておきたい。

ということで、見た目を少し変えてみた。
f:id:Infoment:20210116222349p:plain

ところで、このスケジュールには問題がある。日本全体の問題と言っても
良いかもしれない。それは「終わり時間を気にしていない」という問題だ。
一日は、有限だ。終わり時間を決めて、その日に出来る範囲を線引きして
おきたい。

Public Sub UpdateTable()
    
    ' スケジュール表。
    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
    
    ' ループ用。
    Dim ListRow As Excel.ListRow
        
        ' 更新前の罫線を削除。
        Tb.DataBodyRange.Borders.LineStyle = xlNone
        
        ' 終業時刻を超える案件に罫線を引く。
        On Error Resume Next
        For Each ListRow In Tb.ListRows
            If ListRow.Range(列名.en終了予定時刻) >= Range("終業予定") Then
                ListRow.Range.Borders.Item(xlEdgeBottom).Weight = xlThin
                Exit For
            End If
        Next
        On Error GoTo 0
        
End Sub

この更新は、作業予定時間または終了時刻が更新されたことを切っ掛けに
実行するとしよう。
↓ シートモジュール。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ListRow As Excel.ListRow
        Select Case Target.Column - Tb.Range(1).Column + 1
            Case 列名.en作業予定時間
                For Each ListRow In Tb.ListRows
                    With ListRow
                        
                        .Range(列名.en●).Font.Size = wf.MRound(6 * (Hour(.Range(列名.en作業予定時間)) + _
                                                                     Minute(.Range(列名.en作業予定時間)) / 60) + 1, 2)
                    End With
                Next
        
            Case 列名.en作業予定時間, 列名.en終了時刻
                Call UpdateTable
        
        End Select
End Sub

結果、本日の終業予定を17:00とした場合、案件Ｈ以降は明日以降に回さざるを
得ないことが視覚的にわかる。
※その結果を受けて、リスケジューリングするのか、或いは優先順位を変えるか
　を考えることになる。
f:id:Infoment:20210116223701p:plain

ところで、案件によっては昼休みをまたぐものも出てくるだろう。そこで、
昼休みボタンを設けることにした。
f:id:Infoment:20210116224130p:plain

これを押すことで、現在進行中の案件を一旦中断し、昼休みののちに再開する
ようなスケジュールの引き直しが出来る。またそれに、終業予定時刻が追従し
変更される。

↓ シートモジュール。

Private Sub cb昼休み_Click()
    Call SetLunchBreak
End Sub

↓ 標準モジュール。

Public Sub SetLunchBreak()

    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
        Tb.DataBodyRange.Interior.Color = xlNone

    ' 直近の終了時刻セル。
    Dim LatestRange As Range
    Set LatestRange = Tb.ListRows(Tb.ListRows.Count).Range(列名.en終了時刻).End(xlUp)
    
        If LatestRange >= TimeValue("13:00") Then
            MsgBox "既に昼休み以降の作業を実施中です。"
            Exit Sub
        Else
            LatestRange.Offset(1) = "12:00"
        End If
    
    ' 直近の終了時刻セルの、テーブル内の行番号。
    Dim RowIndex As Long
        RowIndex = LatestRange.Row - Tb.Range(1).Row
    Dim ListRow As Excel.ListRow
    Set ListRow = Tb.ListRows.Add(RowIndex + 2)
        With ListRow
            .Range(列名.en実施内容) = "昼休み"
            .Range(列名.en作業予定時間) = "1:00"
            .Range(列名.en終了時刻) = "13:00"
            .Range.Interior.Color = RGB(240, 240, 240)
        End With
    
    Set ListRow = Tb.ListRows.Add(RowIndex + 3)
        ListRow.Range(列名.en実施内容) = Tb.ListRows(RowIndex + 1).Range(列名.en実施内容)
        ListRow.Range(列名.en作業予定時間) = Tb.ListRows(RowIndex + 1).Range(列名.en作業予定時間)
        ListRow.Range(列名.en備考) = Tb.ListRows(RowIndex + 1).Range(列名.en備考)

End Sub

結果は、以下のとおり。
f:id:Infoment:20210116224719p:plain

本来1時間30分を見込んだＥ案件は30分で一旦終了し、昼休みを挟んで
再スタートとなっている。昼前の30分を引いて、昼からは残りの1時間
とするかどうか迷ったが、ややこくなるので引かないままとした。

今日はここまで。
次回こそは、前回予告した終了時刻の記入方法簡素化などに挑戦です。

参考まで。

スケジュール管理表を作成 ④ 見込み時間の長短を視覚でとらえる

Excel VBA

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

先日から作成しているスケジュール表は、今のところ見た目は文字と数字の
羅列であり、ガントチャートのような視覚に訴える効果は無い。
f:id:Infoment:20210114225609p:plain

実は昨日突如登場した４列目の「●」は、その解消を意図して用意したもの。
掛かる時間の長短に併せて●の大小を追従させ、直感的にその作業に掛かる
時間を把握しようと試みてみた。

例えば、↓こんな感じだ。
f:id:Infoment:20210114225916p:plain

作業予定時間に比例して丸が大きくなっていることが、お判りいただけた
だろうか。ちなみにこの●は、単純な式で表示させている。
f:id:Infoment:20210114230039p:plain

なお、フォントサイズの変更は、WorkSheetChangeイベントで行うものとする。
※ ↓ は全て、スケジュール表のシートモジュールに記載。
※ 「列名.en●」は、昨日紹介したEnumから使用。

Private Property Get Tb() As ListObject
     Set Tb = Me.ListObjects(1)
End Property

Private Function wf() As WorksheetFunction
    Set wf = WorksheetFunction
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ListRow As Excel.ListRow
        Select Case Target.Column - Tb.Range(1).Column + 1
            Case 列名.en作業予定時間
                For Each ListRow In Tb.ListRows
                    With ListRow
                        .Range(列名.en●).Font.Size = wf.MRound(6 * (Hour(.Range(列名.en作業予定時間)) + _
                                                                     Minute(.Range(列名.en作業予定時間)) / 60) + 1, 2)
                    End With
                Next
        End Select
End Sub

大した仕掛ではないし、これで完璧とも思わないが、それなりに役に立ちそうだ。
f:id:Infoment:20210114230755g:plain

今日は、ここまで。次回は、終了時刻の記入方法の簡易化などに挑戦です。

参考まで。

スケジュール管理表を作成 ③ 列名をEnumで管理

Excel VBA ユーザー定義関数

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

昨日作成したユーザー定義関数で、一つ大きな抜けがあった。
関数自体に抜けがあったわけではなく、関数に使用したEnumを掲載して
いなかったのだ。

例えば

Enum 列名
　実施内容=2
End Enum

としておくと、以下の二つは同じ列を指しているが、後者の方が圧倒的に
読み易い（※個人の感想です）。

　Columns(2).Select

　Columns(列名.実施内容).Select

テーブルに於いては、ラベル名称を変えられてもエラーにならない。ただし、
列順の入れ替えには追従しないので、Enum側の編集が必要となる。逆に

　Dim Tb As ListObject
　Set Tb = ActiveSheet.ListObjects(1)
　    Tb.ListColumns("実施内容").DataBodyRange.Select

のようにラベル名称で選択することも可能で、この場合は列の入れ替えに
コードが左右されない利点がある。ただし、ラベル名称を変更されると
途端にエラーになるのが難点だ。

どちらを選ぶも一長一短があって、今回はEnumを選択した。

Enum 列名
    enNo = 1
    en実施内容
    en作業予定時間
    en●
    en実際作業時間
    en終了予定時刻
    en残り時間
    en終了時刻
    en予実比率
    en備考
    [_eLast]
End Enum

なお今回はD列として、前回に無かった列「●」を追加しており、Enumにも
それが反映されている。これについては、次回紹介する。

ということで、現時点で最新のレイアウトがこちらになる。
f:id:Infoment:20210113222046p:plain

先日述べたように、開始時刻を外出しにしてみた。会社によっては９時開始
だったり、午後から出社する日もあると考え、柔軟に対応できるようにして
みた。

終業予定については、定時に帰ることもあれば、残業することもある。毎日
決まった時間にはならないだろうから、終業「予定」とした。

なお、具体的な時間が入っているセルは、各々「開始時刻」「終業予定」と
名前をつけてある。
f:id:Infoment:20210113222345p:plain

それでは、今回新たに作成した「実際作業時間」を求めるためのユーザー定義
関数と併せて、今まで作成した関数を紹介する。
※開始時刻の外出し対応済み。

' 実際作業時間
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終了時刻) - Range("始業時刻")
                
            Else
                tm実際作業時間 = .Range(列名.en終了時刻) _
                                 - Tb.ListRows(RowIndex - 1).Range(列名.en終了時刻)
            End If
        End With
        
End Function

' 終了予定時刻
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作業予定時間) + Range("始業時刻")
                
            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

' 残り時間。
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

予実比率は、予定と実際の比率を計算している。例えば１時間を見込んだ作業が
30分で終われば「50％」、２時間で終われば「200％」と表示することで、進捗を
はかる目安となる。また自分が行った見積もりの精度を振り返るための材料にも
なる。さほど複雑な計算ではないため、こちらはセルに直接計算式を入力した。
f:id:Infoment:20210113223036p:plain

実際に案件を入力してみると、このような感じになる。
f:id:Infoment:20210113223352p:plain

必要な情報が大分揃ってきたが、機能としてはまだまだ足りない。

ということで次回は、今回追加した「●」の説明などを行います。

参考まで。

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

Excel VBA ユーザー定義関数

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

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

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

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

【終了予定時刻】の条件

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

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

' 終了予定時刻
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

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

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

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

次回は、

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

などの設定に挑戦です。

参考まで。

スケジュール管理表を作成 ① 案件の追加と差し込み

Excel

一年の計は元旦にあり。ということで、元旦を10日も過ぎてしまったが、
スケジュール管理表を作成してみよう。
f:id:Infoment:20210111141733p:plain

個人的に、予定管理において特に重要な項目は、以下の三つと考える。

  1. 時間管理
  2. 予実管理
  3. 優先度管理


そこで、管理項目を以下に書き出してみた。

  1. 実施内容
  2. 作業予定時間（実施内容に掛かる見込み時間）
  3. 実際作業時間（実施内容に掛かった実際時間）
  4. 終了予定時刻（予定時間から求めた終了時刻）
  5. 残り時間（現時刻から終了予定時刻迄の残り）
  6. 終了時刻（実施内容が実際に終了した時刻）
  7. 予実比率（予定時間と実際時間の比率）
  8. 備考

表にすると、こんな感じ。
f:id:Infoment:20210111142553p:plain

扱い易さのため、テーブルにしてみよう。
f:id:Infoment:20210111143045p:plain

まず、No.には通し番号が入る。行番号を取得するRow関数を設定しよう。
f:id:Infoment:20210111143255p:plain

以下の３つは直接入力なので後回しにして、終了予定時刻の計算を考える。
とここで、禁じ手に手を染める。テーブルは１行１レコードであって前後の
レコードと相関関係があってはいけないと思うのだが、都合上止むを得ず、
直前レコードの終了予定時刻を参照することになる。
f:id:Infoment:20210111224427p:plain

数式中の"8:00"は、朝８時からスケジュールが開始されることを想定している。
後ほど、管理しやすいよう外出しするかもしれない。

ここで、案件ＤとＥが追加されたとする。各々の作業予定時刻は30分。
案件Ｄはさほど急ぎではないが、案件Ｅは案件Ａの次ぐらいに重要だ。
すると、追加作業の様子はこのようになる。
f:id:Infoment:20210111225244g:plain

これにより、各案件の変更後の終了時刻（目安）が容易に変更可能となった。
各案件の作業予定時刻を前もって見積もる習慣が付く、という利点もある。


今回はここまで。
次回は、残り時間の算出などを行います。

参考まで。

表で累積を表示したい

Excel

職場で、「Excelの表で累積を表示したい」という相談を受けた。
さて、どうしたものか。
f:id:Infoment:20210107224453p:plain

今回は、以下二つの制約があった。

  1. 表は、２行がセル結合で１レコードになっている。
  2. 途中のレコードを削除する場合がある。

例えば、こんな感じだ。
f:id:Infoment:20210107224749p:plain

良し悪しはさておき、セルが結合されているため、テーブルとして
書式設定することはできない。マクロ使用はＮＧではないとのこと
だったが、であれば、なるべく避けた方が良かろう。

例えば、一つ上と左隣を足していく、という方法がある。
f:id:Infoment:20210107225049p:plain

１レコード目のさらに一つ上はラベル行であるため、足し算すると
エラーになる。従って、エラー回避としてIFERRORを使用している。

しかしこれだと、途中の行を削除した際、参照先が失われて空振り
が発生する。
f:id:Infoment:20210107225312p:plain

さて、どうしたものか。あーでもない、こーでもないと議論・検討を
重ねた結果、今回はこれに落ち着いた。

  1. Row関数－２で、自身の二行上の行番号を得る。
  2. Column関数で、自身の列番号を得る。
  3. １と２からAddress関数で、自身の二行上のアドレスを得る。
  4. ３とIndirect関数で、自身の二行上の値を得る。
  5. ４と左隣を足し算する。

f:id:Infoment:20210107230139p:plain

この場合、自分自身を基準にして加算対象セルを指定しているため、途中の
レコードを行ごと削除しても、先程のような空振りは発生しない。

他にSumif関数を使用する案などもあったが、取り敢えずこれに落ち着いた。
でもきっと、もっといい方法があるに違いない。
参考まで。