Excelにて年間の当番表作成したい

Excelにて年間の当番表を作成したいと思っています。
６人で月～土の６日を回し、
祝日は６人のうちから一人、当番対応をする表を作りたいと思っています。

当番表を作るうえでの決まり事は
・Cさんは火曜日固定の為、Cさんを除いた５人で火曜日以外の当番を回す
・Aさんからスタートし、A → B → D → E → Fで１周した場合、次はBさんからスタート、
　その翌週はDさんからスタートのような順番で回したい
　A →...F → B → D → E → F → A → D → E → F → A → B → E →...のような感じ
・祝日はCさんも入れた６人で順番に対応する
になります。

平日、Cさんは火曜日固定で、残りの５人で火曜以外のローテーションを組む関数、
祝日はCさんも含めた６人でローテーションを組む関数　を教えて頂きたいです。

このようなイメージで　と画像を添付しますが、
ローテーション間違ってるかもです…

わかる方いたらよろしくお願いいたします。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2021/11/19 20:16

こんばんは

ルールが複雑なのは仕方ないとしても、A~Fのうちのど真ん中のCだけ特別扱いする仕様では、計算式が面倒で複雑になるばかりですね。

ご提示の図で、W列とX列が表示できれば、あとはご自身でできるものと解釈しました。
・祝日を決定する方法が示されていませんので、どこかに祝日リストがあるものと仮定しました。
・祝日の判定も式に含めると更に長くなるので、祝日判定はV列で行うものと仮定しています。
・祝日リストには、名前の定義で「祝日リスト」の名前を付けてあるものとします。
・また、日付は全てシリアル値であるものと仮定しています。

＞・祝日はCさんも入れた６人で順番に対応する
とありますが、祝日が日曜の場合はどうなるのか不明ですけれど、祝日当番には当番が表示されるものと解釈しました。

・ご提示の、T5：T10に名前のリストがあって、表示にはそれを参照するものとしています。

◇添付の図では、V２セルに、
　=IF(COUNTIF(祝日リスト,U2),"祝",U2)
の式を入れ、書式を「aaa」として曜日表示にして、フィルコピーしてあります。
（この結果、祝日だけ「祝」表示になる点が、ご提示の図とは異なっています）

更に、W2、X2セルに以下の式を入れて、下方にフィルコピーしてあります。
◇W2セルの式
=IFS((V2="祝")+(WEEKDAY(U2)=1),"",WEEKDAY(U2)=3,T$7,1,INDEX(T$5:T$10,MOD(INT((SUMPRODUCT((V$2:V2<>"祝")*(WEEKDAY(U$2:U2)<>1)*(WEEKDAY(U$2:U2)<>3))-1)/5)+SUMPRODUCT((V$2:V2<>"祝")*(WEEKDAY(U$2:U2)<>1)*(WEEKDAY(U$2:U2)<>3))-1,5)+1+(MOD(INT((SUMPRODUCT((V$2:V2<>"祝")*(WEEKDAY(U$2:U2)<>1)*(WEEKDAY(U$2:U2)<>3))-1)/5)+SUMPRODUCT((V$2:V2<>"祝")*(WEEKDAY(U$2:U2)<>1)*(WEEKDAY(U$2:U2)<>3))-1,5)+1>2)))

◇X2セルの式
=IF(V2="祝",INDEX(T$5:T$10,MOD(COUNTIF(V$2:V2,"祝")-1,6)+1),"")


※ 仮定だらけの回答ですが、ご参考までに。
※ 仮定と異なる点に関しては、適宜修正願います。

この回答へのお礼

やってみたらやりたかったようにきれいに出来ました！
ありがとうございます。

お礼日時：2021/11/27 10:12

No.4 回答者： goomania 回答日時： 2021/11/20 23:55

No.3さんによって既に完成形が示されているので蛇足になりますが、作業列使用に問題がないないら、作業列に平日の当番順一覧を作成すると、数式がより単純になります。

添付画像をご覧ください。

No.3さんも指摘されておられますが、ご質問者が示された図で、W列とX列が表示できれば、あとはご自身でできるものと想像します。
前提条件は以下のとおりです。

(1)日付はシリアル値である
(2)祝日一覧をZ列に作成し、その範囲に「祝日」という名前をつけている
(3)祝日の当番順はT5～T10にある
(4)日曜日の祝日もあるが、法律上「振替休日」があるので、実務上は「振替休日」を「祝日」として取扱うものとする
(5)曜日の表示は、V2セルに
=IF(COUNTIF(祝日,U2),"祝",U2)
という数式を記述。書式を「aaa」として曜日表示にし、下方向へコピーしている
(6)平日の火曜日を除く当番順はA～、B～、C～･･･と開始位置がずれるので、それを一覧にしてしまう方法をとる。AA２～AA6セルに「A、B、D、E、F」(5文字)を１文字ずつ入力し、AA7セルに
=INDEX(AA$2:AA$6,MOD(ROW(A1)+INT((ROW(A1)-1)/5),5)+1)
という数式を記述し、AA26まで下方向へコピーしている(5文字で開始位置が５回変わるので５×5=25セル分必要)

この状態で、W2セルに
=IFS((V2="祝"),"",(WEEKDAY(U2)=3),T$7,(WEEKDAY(U2)>1),INDEX(AA$2:AA$26,MOD(SUMPRODUCT((W$1:W1<>"")*(W$1:W1<>T$7))-1,25)+1),TRUE,"")
という数式を記述し、下方向へコピー
X2セルに
=IF(V2="祝",INDEX(T$5:T$10,MOD(SUMPRODUCT((X$1:X1<>"")*1)-1,6)+1),"")
という数式を記述し、下方向へコピー
これで、W列、X列が表示されます。

No.2 回答者： めぐみん_ 回答日時： 2021/11/19 16:53

月・水が祝日だとしたらＣさんが二日連続になってもＯＫ？

それと画像ですけど１５日はＦさんで翌週頭が先週頭のＡさんになるのはどういう理由で？
と言う前に１２日のＢさんが１０日に続いてやるのもどうなのかと思えますし。
Ｂ→Ｃ→Ｄ→Ｅ→Ｆ→Ａ（翌週）
Ｄ→Ｃ→・・・
ならまだわかるのですが。

No.1 回答者： mike_g 回答日時： 2021/11/19 15:34

》 ローテーション間違ってるかもです…

素朴な疑問ですが、なぜチェックして「ン！正しいッ」と判断したものを載せないのですか？

この回答へのお礼

何回も確認はしたんですがなーーぜか違和感がぬぐい切れず...

お礼日時：2021/11/19 15:43