【VBA】暇だったのでランダムでシフトを自動生成する仕組みを作ってみた(解説編)

前回VBAを使ってカウンターを作りましたが、それが2017年6月の話。

今回はシフトを自動生成する仕組みを作ってみました。

何やってんだって話ですよね（笑）

カウンターの記事はこちら

www.htmllifehack.xyz

シフト自動生成のコード編はこちら

www.htmllifehack.xyz

遊びで作りはじめて徐々に改善していった結果、別に使ってもいいんじゃないかなってレベルになりました。

VBAのことなんてほぼ知らない状態でカウンターを作り、結構勉強になったんですけど

今回もまた知らないことが沢山出てきたので本当勉強になります。

遊びでもやってみるもんですね。

作ったもの
仕組み
1週間分のシフトを乱数を使って生成する
生成したシフトを繰り返し貼り付け1ヶ月分のシフトを作る
5連勤以上しないようにチェックする
- チェックするための作戦
6連勤していないかチェックするコードの解説
まとめ

作ったもの

f:id:htmllifehack:20171212211849g:plain

動画では処理が完了するまで7分ほどかかっていますが、通常は1分前後で完了します。

仮想でlinuxを動かして、タブを10個くらい開いた状態で動画撮ってるとやっぱり負荷がかかりますね。

遊びで作ったものなのであまり期待しないでくださいいい！！！

仕組み

f:id:htmllifehack:20171212202035j:plain

仕組みは簡単、０と１をランダムで表示し０だったら休み、１だったら出勤って感じにしました。

斜線パターンで塗りつぶされたセルは０が、青色で塗りつぶされたセルには１が入力されています。

表示形式を変更し値を表示させないようにしています。

1日に必要な人数と個人の出勤希望数をあらかじめ入力しておき、乱数で発生した値の合計が一致するまでループさせています。

（日本語って難しいよな、文系出身なのに全然説明できてないや。）

あとは1か月分のシフト表にコピーするためFor文を使って5回繰り返し処理をしています。

わかる人はコード見たほうが早いか。

1週間分のシフトを乱数を使って生成する

Excelの場合RANDBETWEEN関数を使うことで乱数を発生させますがVBAではRndを使います。

Rndは小数点で乱数を発生させるためIntを使って四捨五入し整数で表示させます。

1~10の値をランダムで表示させる場合は、こんなコードになります。

range("a1").value = Int((10-1+1)*Rnd+1)

int((最大値-最小値+1)*Rnd+最小値)って具合になってます。

単純に０と１を発生させたいような場合はRndで乱数を発生させた後Excelで小数点以下を表示させないようにするだけでもOKです。

f:id:htmllifehack:20171206231722j:plain

Do  ''''''''''''''''''''''''''''''''''月曜日''''''''''''''''''''''''''''''''''''
    Do '秋元真夏
        For Row = 6 To 12
            Cells(21, Row) = Int((1 - 0 + 1) * Rnd + 0)
        Next Row
        
        If Range("m21") = Range("b21") Then Exit Do
    Loop
        
    Do '生田絵梨花
        For Row = 6 To 12
            Cells(22, Row) = Int((1 - 0 + 1) * Rnd + 0)
        Next Row
        
        If Range("m22") = Range("b22") Then Exit Do
    Loop
    
    If Range("f28") = Range("f19") Then Exit Do
   
Loop

全部載せると長くなっちゃうので一部載せています。

①For文を使ってF21からL21(秋元真夏の列)を指定し、Rndを使って０～１の乱数を発生させます。

発生した乱数の合計をM21に表示させ、出勤希望数(B21)と同じ値になるまで繰り返し処理を行います。

秋元真夏の出勤数が希望数と一致したら生田絵梨花に移動し同じように処理を行うようになっています。

②最後の井上小百合まで終わったら次は曜日ごとの人数を見ていきます。

F19~L19にある数値は曜日ごとに必要な人数です。

F28~L28にある数値は乱数で出勤した人数の合計です。

28の値が19の値と一致するまで①の作業を繰り返します。

日曜日まで終わってやっと1週間分のシフトが完成です。

生成したシフトを繰り返し貼り付け1ヶ月分のシフトを作る

シフトは1週間分しか生成できないのでFor文で5回繰り返すことで1ヶ月(35日)分を作ります。

Sub gen() '生成したシフトを1カ月のシフト表に張り付けるプロシージャ
    
    Call clear  'clearのプロシージャを呼び出し生成数を0に、シフト表の書式をクリアにする
            
        For i = 1 To 5                                              '35日分のシフトを生成するため5回繰り返す
            Call shuffle                                            'shuffleを呼び出し1週間分のシフトを生成
            If Range("c30") = 0 Then                                '生成回数が0だったとき
                Range("f21:l27").Copy                               '生成されたシフトをコピーし
                Range("f8").PasteSpecial                            '1か月分のシフト表に張り付ける
                Range("c30") = Range("c30") + 1                     '生成回数を+1する
            Else                                                    '生成回数が0でない場合
                Range("f21:l27").Copy
                Range("f8").Offset(0, Range("c30") * 7).PasteSpecial '生成回数×7の数だけ右に移動し張り付ける
                Call check                                           '6連勤がないかcheckプロシージャを呼び出し確認する
                Range("c30") = Range("c30") + 1
            End If
                
        Next

End Sub

f:id:htmllifehack:20171211223809j:plain
for i = 1 to 5 は下記の作業を5回繰り返しますよって意味です。

1週目を貼り付けたあと右に７つ移動して２週目を貼り付ける、３週目４週目も同じ。

なので今何週目を作っているのかをカウントするためにC30に生成回数という項目を作りました。

生成回数が０回だった場合1週間分のシフトを１週目に貼り付け、生成回数を＋１しています。

もし生成回数が０以外だった場合、生成回数に７をかけた数だけ右に移動し貼り付けを行うようにしました。

これを繰り返すことにより1ヶ月分のシフトを作ることができます。

5連勤以上しないようにチェックする

ランダムで出勤日を決めているのでとんでもない連勤が発生する場合があります。

例えば秋元真夏は１週目の火水木金土日出勤したとして、２週目もランダムで5日決めて1を表示するため、

月火水木金が出勤になる可能性があります。

最大10連勤という大型連勤です。

そうならないように6連勤になった時点で再度シフトを生成するためのコードを書き足します。

チェックするための作戦

f:id:htmllifehack:20171212201104j:plain

出勤日は１、休日は０になっているので直近6日間の合計を求めて、その合計が６になったら再生成することで解決です。

秋元真夏はF32に、F8~K8までの合計を表示します。

G32にはF8~K8までの合計を表示、とういうようにAI8~AM8まで求めます。

同様に生田、生駒〜井上まで人数分行います。

Sub check() '6連勤していないかチェックするためのプロシージャ
    
        For Column = 6 To 35
            For Row = 32 To 38
            Cells(Row, Column).Interior.ColorIndex = 2
                If Cells(Row, Column) = 6 Then                              'もしF32～AI38の中に6がある場合
                    Cells(Row, Column).Interior.Color = RGB(255, 100, 100)  '背景を赤く塗りつぶし
                    Call shuffle                                            'シフトを生成し
                    Range("f21:l27").Copy                                   '生成したシフトをコピーし
                    Range("f8").Offset(0, Range("c30") * 7).PasteSpecial    '1か月分のシフト表に張り付け
                    Call check                                              '再度6連勤がないかチェックする
                Else
                    Cells(Row, Column).Interior.ColorIndex = 2              '6がない場合背景を塗りつぶし無しにする
                End If
            
            Next Row
        Next Column
        
End Sub