エクセルでの勤務表作成、インターバルの設定について

エクセルを使ってランダムに勤務表を作りたいのですが、以下の条件を満たす勤務表の作成は可能でしょうか。
①1〜20日の枠を10人で分担する(1人2日ずつシフトに入る)
②直前の勤務から最低3日空ける
③勤務に入れない日程があれば反映する(例:Aさんは3日と5日、Bさんは7日がシフトに入れない)

Rand、Rank等を使ったのですが、インターバルと入れない日程の双方を反映する方法が分からずに詰まってしまいました。
教えて頂ければ幸いです。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2022/06/04 12:32

こんにちは

20日を10人×2回ということなので、丁度ぴったりなのですが、ランダムに埋めてゆくと他の条件から上手くいかず再試行が必要な場合もありますけれど、以下ではいかがでしょうか？
（上手くいくようにしてしまうことも可能とは思いますが、そうすると段々ランダムではなくなってきてしまいますので、この方式にしてあります）

条件がそれなりに複雑なのと、「出勤不可日」を関数式に組み込んでしまうと、後で習性できなくなるでしょうから、不可日は別入力するようにしてあります。
また、一気に計算式だけで求めると、相当面倒なことになるので作業テーブルを用いています。


添付図では、6～15行目が担当者一覧と、不可日の入力欄で、C6:V15は作業用のテーブルとなっています。
作業用テーブルがうるさければ、この部分だけ別シートにしても良いですし、文字色を背景色と同じにしておけば、見た目は表示されなくなるようにもできます。

1行目C1:V1は日付のつもりですが、1～20の数字（＝日付型ではありません）を単純に入力してあります。
担当者の不可日の入力は、単純にカンマ区切りで入力します。無駄なスペース等を入れると判断されませんのでご注意。
添付図では「3,5」、「7」のように入力してあります。

また、必ず計算がうまくいく保証がないので、リトライが必要かどうかの判断用に、A2セルに式を入れてあります。（リトライが必要な場合は、「要再試行」の表示になります）


前置きはこのくらいにして・・
添付図では、
・C1:V1に1～20の数値（日付ではありません）
・担当一覧（A6:A15)に担当者の名前
・不可日（B6:B15)に勤務不可の日（カンマ区切り）
をあらかじめ入力しておきます。

・作業テーブルのC6セルに
=IFERROR((FIND(","&C$1&",",","&$B6&",")=0)*1,1)*(COUNTIF(OFFSET($A$2,,MAX(COLUMN()-4,1),1,MIN(COLUMN()-2,3)),$A6)=0)*(COUNTIF($B$2:B$2,$A6)<2)
の式を入力し、C6:V15までフィルコピーします。
（ご提示の条件を式にしたもので、勤務可能ならば「1」が表示されます）
（結果的に、後ろの方になると「1」の数は減ってゆくことになります）

・C2セルに
=INDEX($A:$A,AGGREGATE(15,6,ROW(C6:C15)/(C6:C15=1),RANDBETWEEN(1,COUNTIF(C6:C15,1))))
を入力して、C2:V2にフィルコピー。
（計算としては、作業テーブルの「1」の人からランダムに選出しています）

・チェックのため、A2セルに（必要なければ、これは省略できます）
=IF(SUMPRODUCT(ISERROR(C2:V2)*1)>0,"要再試行","OK")
を入力します。

※ 以上ですが、randbetween関数を用いているため、シート上の空きセルなどに何か入力する毎に、表示内容が（再計算されて）変わりますのでご注意ください。
※ 上記を逆用して、A2セルの表示が「要再計算」の場合は、シート上の空きセルに何かを入力したり消したりして、再計算を試みてください。
（何度かトライすれば、上手く計算できる結果が得られるはずです）

この回答へのお礼

ご回答ありがとうございます。うまくいきました！
要再試行の数式までつけて頂き、ありがとうございます。
大変助かりました。
お忙しい中、本当にありがとうございました。

お礼日時：2022/06/04 19:43

No.2 回答者： aouze_ya 回答日時： 2022/06/03 21:22

済みません。

チェック不十分で投稿しました。
30行目の式で、35行目の候補から選択する式になっていて、結果が狂っていました。
30行目の式は、36行目の候補から選択するのが正しいです。

No.1 回答者： aouze_ya 回答日時： 2022/06/03 20:51

回答がすぐにつかないのは、EXCELの関数だけでやろうということに、無理があるとか、面倒なことになるとかではないかと想います。

添付画像でも一応できます。　かなり、面倒です。
なお、総員10名で、勤務は3日の空きをおく、1日に2名は詰める、希望休日は叶えるという条件だと、一発の乱数ではうまく当番を指定できず、数度乱数を発生させなおす（空いているどこかのセルに何か入力させるだけです）必要があるでしょう。
最終的に乱数を使いますが、その前段階で、当日に勤務可能な候補メンバーリストを作成し、その候補から乱数で一人を選ぶという方式がいいのではないかと思いました。