Excelで当番表の作成(休み考慮、完全ランダム)

Excelにてタイトルのような当番表を作成したいです。
・18人いる職場で、窓口当番を以下の通りとしたいです。
午前2人
昼　2人
午後2人
※1日の中で1人が2回以上当たらないように。
例)午前当番当たっているのに昼当番も当たる

・事前に1ヶ月のどこが都合が悪いか確認しており、その日 は当番を当たらないようにしたい。

また当番は完全ランダムになるようにしたいです。

誰かご教示ください

No.3 ベストアンサー 回答者： fujillin 回答日時： 2021/08/16 12:28

こんにちは

前回の回答で、均質化する方法をご提示しましたが、それを理解したうえで敢えて
＞(休み考慮あり、ランダム)　→　(休み考慮、完全ランダム)
＞また当番は完全ランダムになるようにしたいです。
と「完全」を強調していることから、『ご提示の条件以外は一切手を加えず、ランダムであること』が重要であるものと解釈しました。

添付図では10～27行に、A列に18人の名前（あああ等で仮置き）とB列以降に都合の悪い日を「×」印で入力してあります。
上記に基づいて、3～8行にランダムに選定された当番者名が表示されます。

図では、仕組みとしてB3セルに
=INDEX($A:$A,AGGREGATE(15,6,ROW($A$10:$A$27)/(B$10:B$27="")/(COUNTIF(B$2:B2,$A$10:$A$27)=0),RANDBETWEEN(1,COUNTBLANK(B$10:B$27)-ROW(A1)+1)))
の関数式を入力し、B3:B8にコピー、更に、B3:B8を右方にフィルコピーしてあります。

※ 欠点としては、（No2様も指摘なさっていますが）ランダム関数を使用している関係から、ブック内でセルの値を変更すると再計算が発生して、全体がランダムに変わってしまいます。
（ブックを閉じて、新たに開いても再計算が発生します）
※ 上記を避けて値を固定したければ、3:8行を選択してコピー、「値をペースト」で固定値化することで値は変わらなくなります。

No.2 回答者： トモクンアヤチャン 回答日時： 2021/08/16 11:02

申し訳ありません。

ややいい加減ですが、参考にしてください。
回答ではなくアドバイスになります。
C2=XLOOKUP(SMALL(C$9:C$26,$B2),C$9:C$26,$B$9:$B$26,"err",0,1)以下c3,c4,c5,c6,c7も同様
C9:T26まで、=RAND()
A9:A27は各人の1月の当番回数をカウント「=COUNTIF($C$2:$AG$7,B9)」しています。
ランダムなので回数も大小あります。長期間継続すれば平均に収束します。
問題点
①=RAND()は、セルをいじるたびに変わるので、当番表も変わる。
②現時点では、休暇に対応していない。
マクロ（VBA）で組めば、①②の問題は解消しやすいでしょう。
C++やjavaでも簡単に組めそうです。

No.1 回答者： 銀鱗 回答日時： 2021/08/15 19:55

申し訳ありません。

回答ではなくアドバイスになります。

・・・本題・・・

業者に作成を委託することを薦めます。
業務として考えていらっしゃるのであれば、有料で作成委託を受けてくれるサイトの利用を薦めます。


・・・余談・・・

自分も日勤と夜勤の勤務表を自動化できないか考えた時期がありました。
結果として、条件が多すぎて自動化は無理と判断しました。
突然の ”勤務日の変更” が生じた時の影響がとんでもなく大きくなるのです。
例：
　平日日中は1人
　平日夜間は2人
　休日日中は2人
　休日夜間は2人
　夜勤明けの日に次の夜勤入りはさせない
　同じペアでの連続勤務は避ける
　労働時間の偏りは最小限にする
たったこれだけの条件でも突然の勤務日の変更が生じた場合、
自動化をしていると取り返しがつかないくらい全体のスケジュールが変わる。
それを補償するのは並大抵の事ではない。

ちなみに質問にあるだけの条件のモノなら、数時間もあれば自分は作れるんじゃないかな。まあ、実際に作ってみないと分からないけどね。
当然ながら、他に突然の ”勤務日の変更” など、条件が追加されない事が前提です。

そんなわけでポンと出来上がったものをお渡しできるような簡単なものではありません。
そして作ってお渡しできたとしても、不具合があっても修正はできません。
どのくらいの時間をロスし、どれだけの影響がありますか。
また、業務時間を使って自身で作る場合、作成に当てた時間は本来の仕事ができないのです。いくらの損失になりますか。
そんなですので、お金を払って委託することを薦めているのです。

委託のポイントは、
　・メンテナンス。
　・作成時間にかかる労働時間の損失。
です。
上司に話を付けるのであれば、金銭の話は重要になりますよ。

・・・自力で作るためのアドバイス・・・

こういうものは「アルゴリズム」を考える必要があります。
要は「考え方」です。
現在、質問者さんはどのようにして割り当てていますか？
いままでは質問者さんの仕事ではなく他の人が担当していたのであれば、前任者にその「考え方」を教えてもらってください。
それが無いと絶対に作れません。

そこで質問者さんは、件の勤務表を作るうえで何が分かりませんか？
自力で作成する場合は、そういった点を考えて一つずつ問題を解決していきましょう。