Excel当番を条件付きで乱数で設定する方法

質問

とあるパトロールに担当する人の割り振りをします。

メンバーから乱数で当番を割り振りをしたいのですが、複数条件がある場合どのような数式を入れるのでしょうか。

条件は以下の通りです。
(1) 一度巡回したら３日空けて割り振りをする(連続または３日以内にパトロールに割り振るのを避けたい)
(2) 同じ部署同士でのパトロール・リーダー同士でのパトロールを避けたい
(3) 乱数で割り振りをした後にリーダーがパトロール１になるようにしたい(リーダーでない人が一番上になるのを避けたい)
(4) 先月パトロールした回数が３回の人は２回になるように割り振りたい。

乱数で当番を割り振る方法はインターネットの文献で拝見したのですが、条件付きで割り振る場合、どうすれば良いか悩んでいます。
担当者の個々人のスケジュールを一人ずつ確認し、割り振りをしていましたが時間をかなり要してしまい、効率化を図りたいと思い質問させて頂きました。

詳しい方いましたらご教授ください。
よろしくお願いいたします。

回答No.6

SI299792 · ベストアンサー

F4: =INDEX(Sheet1!$B:$B,MATCH(MIN(IF((Sheet1!$A$3:$A$21<>INDEX(Sheet1!$A:$A,MATCH(F3,Sheet1!$B:$B,0)))*(Sheet1!$C$3:$C$21=""),Sheet1!F$3:F$21)),Sheet1!F:F,0))
Ctrl＋Shift ＋Enter で配列数式にします。数式に{}が付きます。

https://support.microsoft.com/ja-jp/office/%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-e43e12e0-afc6-4a12-bc7f-48361075954d

お礼

巡回当番の割り振りに時間を要していた為、非常に助かりました。 本当にありがとうございます。

Powered by GRATICA

回答No.5

SI299792

返事が遅れてすみません。
質問には、土日祝日を除くと書いてなかったので。
NETWORKDAYS を使うか、IFで土日祝を空白にすればできると思います。（既にできている様なので省略します）

Sheet1
E3：当番数が公平かどうか確認する為で、当番作成に使ていません。削除してもいいです。
F3：これの応用です。
https://excel-spreadsheet-ninjya.com/random-pickup-without-duplication-exclusion/
（抽出するのは２つなので、もっといい方法はないかとは思うのですが、これ以上の方法は思いつかなかった）
但し、単なる乱数だとばらつきができます。
そこで、今まで何回当番をやったかを足します。すると、当番回数の少ない人が小さい数字になるので、当番回数の少ない人が優先されます。
また、(1) の条件を満たす為、当番の前３日の間に当番をしたらFALSE になるようにしました。これにより、１度当番行うと、次の３日は当番が当たらないようになります。

Sheet2
F3: Sheet1の最小値を求めて、INDEX とMATCH で一致する人を抽出します。
https://www.pc-koubou.jp/magazine/40845
F4: F3の条件に加え、更に上と同じグループではいけない、リーダーではいけないという条件を追加します。
INDEX とMATCH で上の人がどのグループかを調べて除外。さらに役職が空白という条件を付けて、その中から最小値を取得しています。

関数の動作を知るには、分解するのが一番いいです。（私はこのように別々に関数を作ってから、最後に組み立てます。）
Sheet1
F24 =COUNTIF(Sheet2!C$3:E4,$B3)
F25 =$D3+RAND()+COUNTIF(Sheet2!$E$3:E$4,$B3)
F26 =IF(F24=0,F25)

Sheet2
F6: =MIN(Sheet1!F$3:F$21)
F7: =MATCH(F6,Sheet1!F:F,0)
F8: =INDEX(Sheet1!$B:$B,F7)
F10 =MATCH(F3,Sheet1!$B:$B,0)
F11 =INDEX(Sheet1!$A:$A,F10)
F12 =MINIFS(Sheet1!F$3:F$21,Sheet1!$A$3:$A$21,"<>"&F11,Sheet1!$C$3:$C$21,"")
F13 =MATCH(F12,Sheet1!F:F,0)
F14 =INDEX(Sheet1!$B:$B,F13)

補足

関数の意味合いの補足ありがとうございます。

一つだけ問題なのですが。
教えて頂いた数式を職場で入力したところMINIFSが対応していないことが判明しました。(Office2016)
MINIFSの式が含まれている関数だけ利用できないです。
VBAでMINIFSの代用する方法はネットで参照し試してみたのですが特定の箇所だけ利用できないです。

MINIFSの代用する方法など分かりますでしょうか。
何度も補足頂き申し訳ないです。

回答No.4

SI299792

すみません。後が抜けてましたね。

(1) 一度巡回したら３日空けて割り振りをする
ができなくなります。
４日以降なら可能ですが、１～３日は不可能。
例えば、2/29、3/1 連続で当たる可能性が出ます。
前の当番をコピペすれば問題ありません。

補足

補足回答頂きありがとうございます。
ご教示いただいた関数を当てはめたところ、見事に条件通りの割り振りができました。
質問では一か月そのままの日付でしたので、巡回回数が３回以上の巡回者が出ていましたが、土日祝日を除いた日数で設定したところ、うまく割り振りができました。

＞＞Sheet1
E3: =D3+COUNTIF(Sheet2!$F$3:$AJ$4,B3)
F3: =IF(COUNTIF(Sheet2!C$3:E$4,$B3)=0,$D3+RAND()+COUNTIF(Sheet2!$E$3:E$4,$B3))
右へコピペ。纏めて下へコピペ。

Sheet2
D3～F4: 先月の当番を入力（これを入力しないと、
F3: =INDEX(Sheet1!$B:$B,MATCH(MIN(Sheet1!F$3:F$21),Sheet1!F:F,0))
F4: =INDEX(Sheet1!$B:$B,MATCH(MINIFS(Sheet1!F$3:F$21,Sheet1!$A$3:$A$21,"<>"&INDEX(Sheet1!$A:$A,MATCH(F3,Sheet1!$B:$B,0)),Sheet1!$C$3:$C$21,""),Sheet1!F:F,0))
纏めて右へコピペ。

差し支えなければ、Sheet1,2の関数の内容(意味合い等)とか教えていただけないでしょうか。

Sheet1の意味合いは何となく理解はできました。
(先月の27から29の巡回がなかったら、先月巡回回数＋乱数＋Sheet2の29日から巡回前日までの巡回カウント？？)

回答No.3

SI299792

Ｇ列のリーターは、不要になったので廃止します。

Sheet1
E3: =D3+COUNTIF(Sheet2!$F$3:$AJ$4,B3)
F3: =IF(COUNTIF(Sheet2!C$3:E$4,$B3)=0,$D3+RAND()+COUNTIF(Sheet2!$E$3:E$4,$B3))
右へコピペ。纏めて下へコピペ。

Sheet2
D3～F4: 先月の当番を入力（これを入力しないと、
F3: =INDEX(Sheet1!$B:$B,MATCH(MIN(Sheet1!F$3:F$21),Sheet1!F:F,0))
F4: =INDEX(Sheet1!$B:$B,MATCH(MINIFS(Sheet1!F$3:F$21,Sheet1!$A$3:$A$21,"<>"&INDEX(Sheet1!$A:$A,MATCH(F3,Sheet1!$B:$B,0)),Sheet1!$C$3:$C$21,""),Sheet1!F:F,0))
纏めて右へコピペ。

補足

補足回答頂きありがとうございます。

＞＞Sheet2
D3～F4: 先月の当番を入力（これを入力しないと、

これの意味合いが理解できていません。SheetではC3:E3が先月の範囲になっていますが、C3:E3に先月の当番の人を手入力すれば良いのでしょうか。

回答No.2

SI299792

パトロール１がリーダー、パトロール２が非リーダー。この条件だと、リーダーのパトロールが増えますが。
「一度巡回したら３日開けて割り振り」とは、例えば１日にに巡回したら、２～４日は当たらない、５日になったら可能という意味ですか❓
作業列を大量に使います。画像の様にして下さい。

Sheet1
E3: =D3+COUNTIF(Sheet2!$G$3:$AK$4,B3)
F3: =IF(C3=F$2,B3,F2)
G3: =IF(COUNTIF(Sheet2!D$3:F$4,$B3)=0,$D3+RAND()+COUNTIF(Sheet2!$F$3:F$4,$B3))
右へコピペ。
纏めて下へコピペ。
Ｅ列の合計回数は、先月回数＋今月回数です。
リーダーが６～７回、非リーダーが４～５回になっています。

Sheet2（Sheet1と位置を合わせる為、Ｇ列からにしました。
D3～F4: 先月の当番を入力（これを入力しないと、
G3: =INDEX(Sheet1!$B:$B,MATCH(MINIFS(Sheet1!G$3:G$21,Sheet1!$C$3:$C$21,"リーダー"),Sheet1!G:G,0))
G4: =INDEX(Sheet1!$B:$B,MATCH(MINIFS(Sheet1!G$3:G$21,Sheet1!$C$3:$C$21,"",Sheet1!$F$3:$F$21,"<>"&F3),Sheet1!G:G,0))
右へコピペ。

補足

回答ありがとうございます。
＞＞パトロール１がリーダー、パトロール２が非リーダー。この条件だと、リーダーのパトロールが増えますが。

パトロール１はリーダー、パトロール２は非リーダーですが、パトロール１・2共に非リーダーでも構いません。

＞＞「一度巡回したら３日開けて割り振り」とは、例えば１日にに巡回したら、２～４日は当たらない、５日になったら可能という意味ですか❓

先月3回巡回した人は2回まで。１日にに巡回したら、２～４日は当たらない、５日になったら可能です。

複雑な条件で分かりにくいと思いますが、ご教授ください。 上記の関数を入れて検証をしてみます。

回答No.1

f272

(4) 先月パトロールした回数が３回の人は２回になるように割り振りたい。
ということですが，先月パトロールした回数が2回の人は19人のうち６人です。残りの13人の今月パトロール回数を3回とすれば，今月パトロール回数の合計は6*2+13*3=51回です。31日分*1日当たり2回分=62回分を埋めることはできません。
残りの13人の今月パトロール回数を4回とかにしていいのですか？

補足

回答ありがとうございます。
先月のパトロール回数が3回の人は3回にならないように、調整したいです。
通り数が足りない場合はパトロール日数を減らす等しようと考えています。