エクセルで不可日と祝祭日の考慮してランダムに毎日の当番表を作成したいと思っています。

エクセルで不可日と祝祭日が同じ人にならない考慮をしてランダムに毎日の当番表を月毎に作成したいと思っています。
月の予定表に不可日は×を表示、それを利用して当番可能な人の中から毎日一人を選出してなるべく公平になるような当番表を作りたいです。

添付画像の説明としては
行28~29は日付と曜日
B33~B41は稼働者
F33~AJ41は不可日
行31にF33~AJ41の不可日より×の付いていない稼働可能者の中から毎日一人の当番の表示
このような条件でどのような関数を入れたらよいでしょうか？

御教授どうぞよろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2023/07/13 15:37

こんにちは

ご質問のイメージはわかりますが、実現するにはいくつかの問題点があるようです。

例えば、列の左の方から順に可能な人からランダムに選定してゆく方法を考えたとします。
この方式の場合、表の右側に多く不可日を設定している人の方が選ばれにくくなり、結果として偏りが生じ、「公平に選定する」のとは異なってしまう可能性があります。
「公平に」を重要視するなら、「順に決めてゆく」方法ではなく「全体を見渡しながら決めてゆく」方法にせざるを得ませんが、これを関数で行うことは難しいと思われます。
（VBAで行えば、ロジックさえきちんと作成できれば可能と思います）

一方で、不可日を自由に記入できるとするなら、そもそもそこに不公平があるので、題意を満たせる保証はありません。
（極端な例として、全員が同じ日に不可であれば、該当者を選定できない）

また、エクセルの関数で実現しようとする場合には、「ランダム」とするには乱数の関数等を使用する必要がありますが、乱数の関数はシートが自動計算の場合に再計算が発生しますので、何かの操作をしたりブックを開いたりする毎に表示内容が変わってしまいます。
想像するところ、このことが利用上は不都合になるのではないかと思いますが・・・
（VBAを利用なさるのであれば、自動実行するような仕組みにしなければ、このようなことは起こりませんけれど）

＞祝祭日が同じ人にならない考慮をして
に関しては、どのような考慮をするのか記載がありません。
平日と祝祭日をまったく別にして、同じことを行えば可能ですが・・
例えば、Ａさんは平日6日、祝祭日0日当番を行っていて、Ｂさんは平日1日、祝祭日1日行っていた場合に、祝祭日の当番でＡさんかＢさんかの判断をどのようにするかということです。
平日と祝祭日を完全に分けて考えるなら、当然Ａさんが選定されます。
「いや、Ａさんは既に平日6日もやっているのだから・・」というような判断をするのなら、よくあるのは、祝祭日の当番は平日の1.5日分と見做すとか2日分と見做すなどの重み付けをして評価するような方法でしょうか。
この場合には、「平日と祝祭日を完全に分けて」考える方式とは異なり、「祝祭日に重み付けをして評価する」ような考え方になりますが。


・・・などなど、いろいろあると思いますけれど、以下はごく単純化した一例です。
（以下を、修正することである程度のことまでは可能と思いますので）
考え方としては、
1）左側の列から順に選定してゆく
2）平日、祝祭日の区別はしない
3）当番可能な人から、それまでの実績の少ない人を優先して選定する
4）実績回数が同じなら、名簿の早い順（=行番号の小さい順）に選定する
という方式のものです。

2）に関しては、実績のカウントに祝祭日の重みを加えればそれなりの評価は可能でしょう。
（ご提示の表で、日付や曜日の実際のデータ形式や祝日の判断をどうしているのかなどが不明なので、式にはできませんが）
4）については、実績が同じ人の人数をカウントして、その中からランダムに選択することは関数でも可能と思いますが、前述のような問題は残ります。

※ ご提示の添付図がはっきりとは読み取れないので、セル位置を以下の図のように仮定してあります。
（多分、合っているとは思いますが念のため）
添付図では、F31セルに以下の式を入力し、右方にフィルコピーしてあります。
=INDEX($A:$A,MOD(AGGREGATE(15,6,(COUNTIF($E31:E31,$A33:$A41)*100+ROW(A33:A41))/(F33:F41=""),1),100))

※ E31セルが「空きセル」であることを条件にしています。
（正確には、空きセルでなくても良いですが、A33:A41の名前と一致しない値であること）
※ いろいろと問題があり、ご参考までとしての式なので、エラー処理はしてありません。
（該当がない場合などにはエラーになります）