社会人1年目、エクセル初心者です。 関数を使って添付画像のようなローテーション当番表を作成することは可能でしょうか？…

Question

社会人1年目、エクセル初心者です。
関数を使って添付画像のようなローテーション当番表を作成することは可能でしょうか？

《ゴール》A〜Eの5種類の仕事を、1〜20の20名のメンバーで割り振りたいです。
できる限り自動化し、簡単な作業で表が埋まるようにしたいです。

《問題》
各仕事によって、仕事が必要な曜日・メンバー・順番が違っていて、私にはお手上げ状態で困っています。
また、添付画像右側・順番欄の★マークのついた番号の人から順番が始まるようにしたいです。

《各仕事の条件》
【仕事A】曜日：月火水木金 メンバー：1〜17
順番：1→2→3→…→17

【仕事B】曜日：月火水木金 メンバー：1〜17
順番：10→11→…→17→1→2→3→…→9

【仕事C】曜日：月水金 メンバー：1〜9
順番：2→3→1→4→9→8→7→6→5

【仕事D】曜日：木 メンバー：10〜17
順番：10→11→12→…→17

【仕事E】曜日：月火水木金 メンバー：10〜20
順番：18→10→19→11→12→13→14→20→15→16→17

皆さまのお知恵を貸していただけますと嬉しいです。
よろしくお願いいたします！

Answer 1

作成してみました。
添付図で同じ日に同じ人が複数の仕事に割り当てられていますが、それはいいのですね。（8日の10番）

I2は月を示す数字だけを入力、表示形式を 0月

B4
=DATE(B2,I2,1)

B5
=IF(B4="","",IF(MONTH(B4+1)<>I$2,"",B4+1))
下へコピー

C4
=IF(B4="","",TEXT(B4,"ddd"))
下へコピー

P4からT4に、その月の先頭の人の番号を入力
P5からT21に★は入れないで番号だけにして下さい。

V列とW列に番号と名前の対応表を作成

どこかに土日以外の休日を入力して「休日」と名前定義

P23
=MATCH(P4,P5:P21,0)
右へT23までコピー

P24
=COUNTIF(P5:P21,"<>")
右へT24までコピー

D4
=IF(AND(WEEKDAY(B4,2)<=5,COUNTIF(休日,B4)=0),1,"")

E4
=IF(AND(WEEKDAY(B4,2)<=5,COUNTIF(休日,B4)=0),1,"")

F4
=IF(AND(MOD(WEEKDAY(B4),2)=0,COUNTIF(休日,B4)=0),1,"")

G4
=IF(AND(WEEKDAY(B4)=5,COUNTIF(休日,B4)=0),1,"")

H4
=IF(AND(WEEKDAY(B4,2)<=5,COUNTIF(休日,B4)=0),1,"")

D5
=IF(B5="","",IF(AND(WEEKDAY(B5,2)<=5,COUNTIF(休日,B5)=0),MAX(D$4:D4)+1,""))

E5
=IF(B5="","",IF(AND(WEEKDAY(B5,2)<=5,COUNTIF(休日,B5)=0),MAX(E$4:E4)+1,""))

F5
=IF(B5="","",IF(AND(MOD(WEEKDAY(B5),2)=0,COUNTIF(休日,B5)=0),MAX(F$4:F4)+1,""))

G5
=IF(B5="","",IF(AND(WEEKDAY(B5)=5,COUNTIF(休日,B4)=0),MAX(G$4:G4)+1,""))

H5
=IF(B5="","",IF(AND(WEEKDAY(B5,2)<=5,COUNTIF(休日,B5)=0),MAX(H$4:H4)+1,""))

D5からH5を下へコピー

J4
=IF(D4="","",INDEX($W$3:$W$22,MATCH(INDEX(P$5:P$21,IF(MOD(D4+P$23-1,P$24)=0,P$24,MOD(D4+P$23-1,P$24)),0),$V$3:$V$22,0)))
これを右と下へコピー

D列からH列は作業列で見た目邪魔なので非表示にするといいです。

Answer 2

先の投稿時に仕事C、Dの稼働曜日の点を失念していた為、一旦削除させて頂きました。
失礼しました。

添付画像の様に表を作ったとしてです。

A1 セルに起算日を入力

B6 セルの数式：
=AND(WEEKDAY($A6,11)<6,COUNTIF(祝日,$A6)=0)*1

C6 セルの数式：
=NETWORKDAYS.INTL($A$1,$A6,1,祝日)

D6 セルの数式：
=AND(OR(WEEKDAY($A6,11)=1,WEEKDAY($A6,11)=3,WEEKDAY($A6,11)=5),COUNTIF(祝日,$A6)=0)*1

E6 セルの数式：
=NETWORKDAYS.INTL($A$1,$A6,"0101011",祝日)

F6 セルの数式：
=AND(WEEKDAY($A6,11)=4,COUNTIF(祝日,$A6)=0)*1

G6 セルの数式：
=NETWORKDAYS.INTL($A$1,$A6,"1110111",祝日)

H6 セルの数式：
=IFERROR(INDEX($O$6:$O$25,MATCH((MOD($C6-1,H$5-H$4+1)+1)*$B6,OFFSET($O$6,0,MATCH(H$3,$P$5:$T$5,0),ROWS($O$6:$O$25),1),0)),"")
I6、L6 セルへコピペ

J6 セルの数式：
=IFERROR(INDEX($O$6:$O$25,MATCH((MOD($E6-1,J$5-J$4+1)+1)*$D6,OFFSET($O$6,0,MATCH(J$3,$P$5:$T$5,0),ROWS($O$6:$O$25),1),0)),"")

K6 セルの数式：
=IFERROR(INDEX($O$6:$O$25,MATCH((MOD($G6-1,K$5-K$4+1)+1)*$F6,OFFSET($O$6,0,MATCH(K$3,$P$5:$T$5,0),ROWS($O$6:$O$25),1),0)),"")

B6 ～ L6 を下へフィルコピー
B ～ G列を非表示
4 ～ 5行も出したくない場合は非表示にして下さい。

Answer 3

関数を使う目的は何で、どこまで手作業を入れることができるのでしょうか。

作り込めば関数でいけるでしょうけど、
1年分くらいなら手打ちの方が早いと思います。
また、数字の順を昇順にした方が、作り込みは少なくて済むでしょう