VBA 繰り返される定常業務の工数を見える化するアイデア

サービス業において、業務工数を見える化するというのはとても難しい。
実績ベースで工数入力をしている職場は多いと思うけど、実際のところその方法は担当者の匙加減で何とでもなってしまう。

工数分析をしようと思ったら、工数は理論値で出して、実績との乖離をヒアリングによって突き止めるのが良いと思う。
それで今回は、以下のようなタスクごとの工数値を管理する表を考えてみた。

これをVBAで集計処理することで、担当者ごとの日々の工数をグラフ化する。
すると、年間を通してスタッフごとの繁忙期・繁忙曜日などが見えてくる。

※スタッフの工数にあまり偏りが無いのは、業務を調整したあとでスクリーンショットを取った為。

ベースになるExcel表の解説

コードを紹介する前に、Excel表から解説する。

まず冒頭の表は1業務を1行で表している。
ヘッダーの上に薄い文字でManとかSelectとかCalcと書かれているのは、Manが手入力項目、Selectが選択項目、Calcが自動計算項目であることを表す。

一番目の項目群(Basic Information)について

単にタスクの情報を書いているだけなので、工数管理に直接影響はない。

二番目の項目群(Workload)について

Workload per Cycleは、その業務1回ごとの標準的な工数を分単位で記入する。
ここで、何をもって1回とするかも重要で、明細数によって工数が左右される場合は、1明細を1回とすれば良い。
たとえばID管理業務などで、1依頼につき、複数名が含まれる場合、1名登録に何分かかるかを書く。

次にMonthly Occasionは、その業務が月に何回発生するかを記入する。
Dailyなら20、Weeklyなら4、Monthlyなら1、Quarterlyなら=1/4、Annuallyなら=1/12と入力する。

リクエストベースの業務は見積もりが難しいので、昨年実績があれば12カ月で等分すればよい。
これでMonthly Workload(月間工数)とAnnually Workload(年間工数)が自動計算される。

(ここでAnnually(年次の意味)をAnnualy(エルがたりない)と間違えて書いてたことが発覚したけどスクリーンショット取ってしまったので、まぁいいや。。)

三番～六番目の項目群(Ratio of xxxx)について

これは、業務工数の偏りを比率で表すものである。1を100%として実数で記入する。
たとえば年間を通じてコンスタントに行う業務であれば、Ratio of each monthesは各月 = 1/12になる。(つまり0.08)
年次で9月だけ行うような業務であればSepが1で他は0となる。

Ratio of each weeksは週ごとの偏りを表す。
ただしここでいう週はその月の何週目ではなくて、第3木曜日などと言ったときの3のことである。
たとえば2019年3月21日は、3月の第四週目に位置するが、第三木曜日と呼ばれるので、ここでいう3rd Weekに相当する。

ちなみにこの記事の前に公開した以下の記事は今回の記事の布石だった。
thom.hateblo.jp

Ratio of each day of the weekは曜日ごとの偏りを表す。

最後にRatio of each staffsはスタッフごとの業務分担率を表す。

いずれの項目群もトータル値は1に近似しなければならない。

コードの紹介

まずタスクの一覧シートはオブジェクト名をTaskSheetとしておく。
それから出力シートはオブジェクト名をOutputSheetとしておく。

※オブジェクト名についてはこちらを参照
thom.hateblo.jp

OutputSheetのコード

Option Explicit
Public Cursor As Long
Sub WriteLine(ParamArray arr())
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Me.Cells(Cursor, i + 1).Value = arr(i)
    Next
    Cursor = Cursor + 1
End Sub
Sub Init()
    Cursor = 2
End Sub

標準モジュールのコード

Option Explicit
Const HEADER = 5
Enum Col
    Code = 1
    Workload = 9
    January = 10
    FirstWeek = 22
    Sunday = 26
    Staffs = 33
End Enum
Function WeekNumberByDayOfTheWeek(d As Date) As Long
    Dim ret As Long
    Dim target As Long: target = Weekday(d)
    Dim i As Date
    For i = DateSerial(Year(d), Month(d), 1) To d
        If Weekday(i) = target Then ret = ret + 1
    Next
    If ret > 4 Then ret = 4 '5週目以降は4週目と見做す。
    WeekNumberByDayOfTheWeek = ret
End Function

Sub AggregateStaffWorkloadsForEachDay()
    OutputSheet.Init
    Dim d As Date
    For d = #1/1/2019# To #12/31/2019#
        Dim i
        For i = HEADER + 1 To TaskSheet.Cells(Rows.Count, Col.Code).End(xlUp).Row
            Dim AnnualWorkload: AnnualWorkload = TaskSheet.Cells(i, Col.Workload).Value
            Dim RatioOfMonth: RatioOfMonth = TaskSheet.Cells(i, Col.January + Month(d) - 1).Value
            Dim RatioOfWeek: RatioOfWeek = TaskSheet.Cells(i, Col.FirstWeek + WeekNumberByDayOfTheWeek(d) - 1).Value
            Dim RatioOfWeekday: RatioOfWeekday = TaskSheet.Cells(i, Col.Sunday + Weekday(d) - 1).Value
            Dim staffWorkloads(1 To 3)
            Dim j
            For j = 1 To 3
                staffWorkloads(j) = staffWorkloads(j) + _
                    AnnualWorkload * RatioOfMonth * RatioOfWeek * RatioOfWeekday _
                    * TaskSheet.Cells(i, Col.Staffs + j - 1).Value
            Next
        Next
        If Weekday(d) <> 1 And Weekday(d) <> 7 Then
            OutputSheet.WriteLine d, staffWorkloads(1), staffWorkloads(2), staffWorkloads(3)
        End If
        Dim k As Long
        For k = 1 To 3
            staffWorkloads(k) = 0
        Next
    Next
    MsgBox "Completed"
End Sub

あとはAggregateStaffWorkloadsForEachDayを実行するとOutputSheetに出力されるので、グラフ化するだけ。

手抜き免責事項

スタッフを3名決め打ちでハードコードしている。

汎用化を考えてるけど、まずは動くところまでこぎつけたかったので雑に作った。
(それくらい直してから記事にすればと言われそうだけど、とりあえず動いた興奮が冷めないうちに記事化してしまわないと、執筆が面倒くさくなる。)

OutputSheetのヘッダーは手入力しないと入らない。

表の構成を記事で説明してない件について

説明が難しいのでGithubでブックごと公開してしまった方が速いんだろうけど、やり方を忘れて調べるのが面倒。。
(とりいそぎ、ヘッダのセル位置だけ公開しておくので再現したい方は頑張ってください。)

A5    Code
B5    Category
C5    Task Name
D5    Description
E5    Cycle
F5    Workload per Cycle (Unit: Minutes)
G5    Monthly Occasion (Unit: Times)
H5    Monthly Workload (Unit: Hours)
I5    Annualy Workload (Unit: Hours)
J5    Jan
K5    Feb
L5    Mar
M5    Apr
N5    May
O5    Jun
P5    Jul
Q5    Aug
R5    Sep
S5    Oct
T5    Nov
U5    Dec
V5    1st Week
W5    2nd Week
X5    3rd Week
Y5    4th/5th Week
Z5    Sun
AA5    Mon
AB5    Tue
AC5    Wed
AD5    Thu
AE5    Fri
AF5    Sat
AG5    Staff A
AH5    Staff B
AI5    Staff C

おわりに

私がブログ記事のタイトルに「アイデア」と付けるときは大抵、未完成品である。
VBA使用者ならアイデアを取得して応用できるだろうと思って、熱意があるうちにサクッと公開してしまう方針なのでどうかご理解いただきたい。
(キチンと完成にこぎつけてから公開しようと思ってると、そのうち熱が冷めてしまってお蔵入りになるので。)