業務課題をHackせよ! プログラミングで仕事自動化物語(12) Excelマクロで公平な当番表を作ろう

この物語は、入社して一年目の新人社員、田中君が業務を自動化していく様を記したものです。物語はフィクション（時々ノンフィクション）ですが、自動化の手法は実際にパソコンやスマートフォンで実践できるものですので、物語と自動化作業の両方を楽しめます。

【物語】

今回の物語は社長の朝礼から始まる。この朝礼で社長はいつも以上に拳に力を入れ熱く語ったのだった。

「来月から始まるキャンペーンは我が社の社運をかけたプロジェクトになります！そこで、全社員がキャンペーンに参加し、我が社のために貢献してもらえたらと思います。」

そして、僕（田中）の所属している部署からも一日三人が特別キャンペーンに協力するため、現地に派遣されることが求められた。

これをきっかけとして、我が部署の定例会議で大きな言い争いが起きたのだった。

「ただでさえ、日々の業務に忙殺されているのに、三人ずつ別の仕事に駆り出されるなんて！社長に我が部署だけは除外してもらうように進言しましょう。」

「そうですよ、我々は日々身を粉にして会社に貢献しています。ここは、上層部からの無理難題を跳ね返しましょう。」

しかし、部長の太田先輩は強く押し切った。

「みんなが忙しいのは分かっています。しかし、これはみんなのスキル向上の良い機会でもあり、また我が部署の存在感を公に示す千載一遇のチャンスでもあります。社運をかけたキャンペーンに参加して、この部署の存在感を高めましょう！」

そして、太田先輩の鶴の一声で、毎日三人ずつ部署から派遣されることになった。

会議が終わってすぐ、太田先輩が僕に近づいて来て言った。

「会議で決まったように、これからしばらく毎日三人ずつキャンペーンに協力することになるから、部署の当番表を自動生成するツールを作ってね。」

先輩の迫力に「は、はい」とだけ答えた僕だが、よくよく考えると、みんなが嫌々参加するキャンペーン協力の表を僕が作成することになるなんて責任重大だ。下手なことをすると、みんなから恨まれる可能性がある。

ことの重大さに既に気付いて真っ青になっている僕に太田先輩は重ねて言うのだ。

「田中くん、責任重大よ。必ず世界一公平な当番表にするのよ！二週間ずつ自動で当番表を作成する仕組みにしておいてね。」

すると、先輩の後ろにいた佐藤先輩が口を出した。

「田中、俺が忙しいのは知っているよな。絶対に俺を少なめにしつつ、公平にするんだぞ。」

僕は慌てて答える。「いやいや、公平なんだからえこひいきはできませんよ！」

そして、佐藤先輩は「冗談だよ」と笑って僕に一枚の紙を差し出した。佐藤先輩が僕に渡してきたのは隣の部と我が部の合同の名簿だった。

「あれっ、これってどういうことですか？」

僕が聞き返すと太田先輩と佐藤先輩が顔を見合わせてニカっと笑って得意げに答えた。

「実は、業務的にこの部署から三人を出すのは無理だから、既に太田先輩が社長に掛け合って、隣の部署と合同にして、その中から三人出すことで良いということにしてもらっていたんだよ。」

なんと太田先輩は策士なんだろうか。先にハードルが高い状態でみんなを嫌々納得させておいて、実際に行動に移す時には、それほどみんなの負担にならないようにする配慮するという姿勢を見せるとは・・・。これにより部署のみんなの太田先輩に対する好感度がまた上がるのだった。

とは言え、人数が増えた分、僕が作る当番表の重要度が増してしまった。頑張って作らないと。しかし、公平な当番表ってどうやって作ったら良いのだろうか。

僕が頭を抱えようとしたとき、今回のキャンペーンのためのグループLINEにみんなから、どうしてもキャンペーンに参加できない日程が送られてきた。毎日現場に出るため、社員それぞれ個別に休みを取ることになっている。そうなのだ。公平な表を作るだけでなく、みんなの休みの日程を考慮に入れなくてはならないのだ。

僕はとりあえず、もらった名簿に休みの日程を書き入れたExcelの当番表のひな形を作ってみた。

休みの日程を書き込んだ当番表のひな形

しかし、ここまで作ったところで僕は完全に行き詰まり、いつも通り僕は太田先輩に泣きついた。

「太田先輩、休みを考慮に入れつつ公平な当番表なんてどうやって作るんですか？」

太田先輩はすぐさまキリッと答えた。

「田中くん、キーワードは『ランダム』よ。サイコロを振るように当番表を作れば公平に見えるから。」

そして、サイコロを振って選んだ人が休みであれば、もう一度サイコロを振って別の人を選べば良いとのことだ。それから、Excel VBAでサイコロを振るのと同じ要領で使えるRnd関数について教えて貰った。

これを使えば何とかできるだろうか。僕は真っ青になってExcelを起動したのだった。

【プログラムを作ろう】

もう一度、今回作るプログラムについてまとめてみましょう。

まず、名簿にある13人の中から毎日3人をキャンペーンを手伝う当番に決めます。二週間分の表を作りますが、しばらく継続するプロジェクトのため、自動で作表するようにプログラムを作ります。ただし、公平に当番を決めなくてはなりません。加えて、いつも同じメンバーが参加するのではなくランダムに三人を選ぶようにします。さらに、各メンバーの休みの日程を避けて当番を決める必要があります。

先ほど田中君が作ったExcelの表では、公平感が見えるように、表の右端に何度当番が割り当たったかが表示されるようになっています。それで、当番を割り当てる日に「1」を書き込むようにします。そうすると割当たった総日数を自動計算できます。

上記、田中君が作った表サンプルをこちらにアップロードしています。ファイル「当番表.xlsx」にVBAのプログラムを書き込んで当番表を完成させましょう。

それでは、Excelを起動し、[Alt]+[F11]キーを押すとVBAを記述するためのVBエディタが起動します。ここでVBAを記述します。

最初に、何度か試行錯誤できるよう、休み以外の当番日程をクリアするプログラムを作りましょう。プロジェクトエクスプローラーでSheet1をダブルクリックして、そこに以下のプログラムを書き込みます。

Sub 休み以外クリア()
    Dim r As Range
    Set r = Range("B3:M15")
    For y = r.Row To r.Row + r.Rows.Count - 1
        For x = r.Column To r.Column + r.Columns.Count - 1
            v = Sheet1.Cells(y, x)
            If v <> "休" Then Sheet1.Cells(y, x) = ""
        Next
    Next
End Sub

そして、続けて本題となる当番表の作成を行うプロシージャ（手続き）を作りましょう。同じくSheet1のコードとしてプログラムを記述しましょう。

Sub 当番表作成()
    ' 定数変数の宣言
    Const 人数 = 13
    Const 日数 = 12
    Const 一日人数 = 3
    Dim 回数(1 To 人数)
    Randomize
    ' 最大回数を計算 --- (*1)
    最大回数 = WorksheetFunction.RoundUp(日数 * 一日人数 / 人数, 0)
    ' 回数を初期化
    For i = 1 To 人数
        回数(i) = 0
    Next
    Call 休み以外クリア ' --- (*2)
    ' 二週間分の予定を作る --- (*3)
    For i = 1 To 日数
        For j = 1 To 一日人数
            Result = False
            For k = 1 To 100
                ' ランダムに一人を選ぶ --- (*4)
                r = Int(Rnd * 人数) + 1
                ' すでに選択済みか休みなら選び直し --- (*5)
                v = Sheet1.Cells(2 + r, 1 + i)
                If v <> "" Then GoTo Continue
                ' 最大回数を超えていたら選び直し
                If 最大回数 < (回数(r) + 1) Then GoTo Continue
                ' 表に当番であることを書き込む
                Sheet1.Cells(2 + r, 1 + i) = 1
                回数(r) = 回数(r) + 1
                Result = True
                Exit For
Continue:
            Next
            If Result = False Then
                MsgBox "作表失敗しました。やり直してください"
                Exit Sub
            End If
        Next
    Next
End Sub

プログラムを記入したら、SubからEnd Subの間にカーソルを動かして、[F5]キーを押します。あるいは、メニューバーにある実行ボタンを押しましょう。するとプログラムが実行されて表が作成されます。

すると、次のような表が作成されることでしょう。

公平な当番表が作成されたところ

プログラムを確認してみましょう。最初に公平感を出すため、(＊1)の部分で一人当たりの最大回数を求めます。ここでは、2週間で一日3人ずつ入る当番を13人でこなすため、一人当たり最大3回の当番になります。この回数を超えて当番に入ることがないように配慮します。(＊2)の部分では、最初に作った処理「休み以外クリア」を呼び出して「休」と書かれている以外のセルを空にします。

そして、(＊3)以降の部分で実際に当番を決定します。そこで鍵となるのが(＊4)のRnd関数です。これを使うと0以上1未満のランダムな実数を返します。そこで、「Int(Rnd ＊人数) + 1」のように書くと1から人数までの乱数を返します。乱数を初期化するために冒頭でRandomizeと記述している点にも注目しましょう。また、(＊5)の部分では「休」と書かれていたり、既に当番に選ばれていれば選択をやり直すようにしています。

ただし、完全にランダムに作成している表のため、合計の部分を確認すると当番が1回だけになってしまう可能性があります。その場合、改めてVBAのプログラムを実行して表を作り直します。

今回、プログラムを短くするため、作り直し処理を入れませんでした。改造のヒントとして、当番の配分に偏りが出てしまったときに再度表を作り直す処理を入れると良いでしょう。

以上、今回は公平な当番表を作るプログラムを作ってみました。現実の現場ではさらに様々な条件を考慮した当番表やシフト表が必要になることでしょう。しかし、今回のプログラムを応用しつつ、一つずつ条件を作り込んで行くなら条件にあった表を作ることができるでしょう。参考にしてみてください。