休みが連続し過ぎるのは好ましくないので、抑制を掛けます。
方式:
割り当てられたシフト勤務(変化するセル)において、連続する休みの個数を調べます。
連続数の求め方
計算用としては、休みは「0」で表しますので、この「0」の連続状態を調べます。
ただし、セルに分割されたままの状態で調べるのは面倒です。
そこで、連結された文字列の中から、00、000、0000のような連続する「0」の個数を調べます。
■文字列の中から、特定文字列の個数を調べる
このような計算は、VBAでユーザー定義関数を作った方がすっきりしますが、ここでは数式で計算。
元の文字列中に、xxx が含まれている場合、元の文字列の中から xxx を削除(SUBSTITUTE関数)してみます。
もし、xxx が1個の場合は、削除後の文字列の長さは3文字少なくなります。
もし、xxx が10個有った場合は、30文字少なくなります。
すなわち、調べる文字列を空白に置換してみて、元の文字列との長さの差を調べる文字列の長さで割った値が、調べる文字列の個数となります。
文字列の個数=(元の文字列の長さ-調べる文字列を全て空白にした後の文字列の長さ)/調べる文字列の長さ
下図の右側、「連続休み数」でK53~K57の文字列に含まれる、「00」「000」「0000」の個数を算出しています。
(「00」「000」「0000」は、それぞれ、2連休、3連休、4連休を表す)
この算出方法についての詳しい説明は、 無料エクセル教材-文章中の特定文字の個数をカウント を参照
最小化セル = 勤務の分散 + 連続休み数個数値
を最小化すれば、勤務シフト表が得られることになります。
重み
2連休、3連休、4連休 について重要性が異なりますので(一般的には連続数が多いものほど抑制したい?)、
ここでは、それぞれの重み 0.2 , 0.5 ,2 を掛けて、なるべく長い連続の休みを発生を抑えています。
この重み値は、自由に設定可
補足:SUMPRODUCT関数は、配列の要素間の積を計算し、その和を返します。
ここでは、それぞれの休み個数と重みを掛けたものの総和を算出
単純割り振り
連続休み個数抑制 - 4連休が消え、3連休が1個になった!
2週間シフト表で見ると、もう少し、よくわかる
考慮無し - 3連休と4連休が発生
連続休み個数抑制 - 休みは2連休以内に収まった
次項は、連続勤務数の抑制。
ほとんど、考え方は連続休み数の抑制と同じになります。