エクセルで旗当番表を作っています あとひといきなんですができません

エクセルで当番表を作っています。過去のQ&Aを参考にさせていただき作ったのですが、もともとエクセルに詳しいわけではないので、細かいところまでできず、詰まってしまいました。
すいませんがご教示ください。よろしくお願いします。

・夏休み、春休みに当番を入らないようにはできたのですが、祝日（定義済み）も当番を入れたくないのですが、関数をどうすればよいかがわかりません。
A4のセルに=IF(OR(A4="",WEEKDAY(A4,2)>=6,COUNTIF(Sheet3!$E:$I,A4)>0),"",INDEX(Sheet3!L$2:L$100,MOD(COUNTIF(B$3:B3,"?*")+AC$4-2,COUNTA(Sheet3!L$2:L$100))+1))

・土日も当番が入らないようにしてありますが、年に数回土曜授業がありその時は当番が入るようにしたい。

・当番の月末の番号の取得の関数が、月末が土日、祝日に当たるとN/Aとなって翌日に反映されないので直したい。（関数の最終日を金曜に変えないと翌月の番号が取得できない）
AC5のセルに=MATCH(LOOKUP("ー",B4:B33),Sheet3!$L$2:$L$100,0)+1

・２月２９日はカレンダーは設定済みだけど、月末の番号取得の関数を入れるとN/Aがでるので直したい。

・当番のカレンダーへの反映は当番が何人の場合でも対応できるようにしたつもりだが、大丈夫かしりたい。

土曜授業の振り替えで月曜日お休みの場合は祝日の所の済んだ日付を変えて対応しようと思います。

画像が１枚しか載せれませんでしたが、シート３のk2からＫ21のセルに１から２０までの番号、Ｌ列に当番名があります。
同じシート３のA列B列に祝日が載っており定義してあります。
シート３のE2　F2　G2　H２　I2　に　それぞれ春休み、夏休み、秋休み、冬休み、翌年の春休みの開始日が、E3　F3　G3　H3　I3　に　終了日が入力してあります

すいませんがよろしくおねがいします。

質問者からの補足コメント

• 見てくださり、ありがとうございます。
  全体を掲載したいという思いが強すぎました。もうしわけありません。
  こんな感じでいかがでしょうか。
  よろしくお願いします。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2019/08/23 09:12

• ご指摘いただきありがとうございます。質問内容もしっかりしておらず申し訳ありません。
  補足にて図の拡大したものを添付させていただきました。
  
  おっしゃる通りで、A4のセルには=IF(MONTH(DATE($A$1,$A$3,ROW()-3))<>$A$3,"",DATE($A$1,$A$3,ROW()-3))
  B4のセルには=IF(OR(A4="",WEEKDAY(A4,2)>=6,COUNTIF(Sheet3!$E:$I,A4)>0),"",INDEX(Sheet3!L$2:L$100,MOD(COUNTIF(B$3:B3,"?*")+AC$4-2,COUNTA(Sheet3!L$2:L$100))+1))
  を入力してあります。
  
  申し訳ありませんでした。

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/08/23 12:06

• 説明がしっかりしておらず申し訳ありません。補足にて拡大図を添付させていただきました。
  学校の旗当番表を作成しようと考えております。
  
  当番は日替わりで、土日祝日は学校がお休みのため無し、春休み夏休み等も当番なし、ただ土曜授業のある日は当番あり。土曜授業のため月曜お休みの時は月曜は当番なし。
  前期、後期でシートが分かれていて、４月から１０月を前期　１０月から３月を後期、１０月はかぶってます。
  列に日付と当番が１か月づつ並んでいます。
  Ａ４に=IF(MONTH(DATE($A$1,$A$3,ROW()-3))<>$A$3,"",DATE($A$1,$A$3,ROW()-3))
  Ａ４に条件付き書式にて=WEEKDAY($A4)=7　薄いグレー
  　　　　　　　　　　　=WEEKDAY($A4)=1　濃いグレー
  　　　　　　　　　　　=COUNTIF(祝日,$A4) =1　濃いグレー

  No.4の回答に寄せられた補足コメントです。 補足日時：2019/08/23 13:25

• Ｂ４に=IF(OR(A4="",WEEKDAY(A4,2)>=6,COUNTIF(Sheet3!$E:$I,A4)>0),"",INDEX(Sheet3!L$2:L$100,MOD(COUNTIF(B$3:B3,"?*")+AC$4-2,COUNTA(Sheet3!L$2:L$100))+1))
  Ｂ４に条件付き書式にて=WEEKDAY($A4)=7　薄いグレー
  　　　　　　　　　　　=WEEKDAY($A4)=1　濃いグレー
  　　　　　　　　　　　=COUNTIF(祝日,$A4) =1　濃いグレー
  　　　　　　　　　　　=IF(AND(Sheet3!$E$2<=A4,A4<=Sheet3!$E$3),TRUE,FALSE)　濃いグレー
  
  ＡＣ５に=MATCH(LOOKUP("ー",B4:B33),Sheet3!$L$2:$L$100,0)+1
  
  シート３のＡ列に祝日の日付Ｂ列に祝日の名

    補足日時：2019/08/23 13:27

• シート３のＤ３に開始日　Ｄ４に終了日　と記載
  シート３のＥ１，Ｆ１，Ｇ１，Ｈ１，Ｉ１，にそれぞれ順に春休み、夏休み、秋休み、冬休み、翌年春休みと記載
  シート３のＥ２，Ｆ２，Ｇ２，Ｈ２，Ｉ２，に2020/4/1のように開始日を入力
  シート３のＥ３，Ｆ３，Ｇ３，Ｈ３，Ｉ３，に終了日を入力
  シート３のＫ２からＫ２１まで順に1から２０まで番号がふってあります
  シート３のＬ１に当番表
  シート３のＬ２からＬ２１まで当番名がはいっています
  シート３のＥ６に＝Ｅ＄２　を入力しＩ６までオートフィルコピー
  シート３のＥ７に=IF(OR(E$2="",E$3="",E6=""),"",IF(E6+1>E$3,"",E6+1))　を入力しＩ７までオートフィルでコピーし、Ｅ７からＩ７までを50列ぐらいまで下へオートフィルコピーしてあります
  
  このように作成しました。

    補足日時：2019/08/23 13:29

No.6 回答者： fujillin 回答日時： 2019/08/23 15:58

No4です

補足を読みましたが、内容はカレンダー作成方法の補足で、当番表に関してはほとんど情報がないみたい・・・
ついでながら、補足の休みの色判定（グレー色）には、ご説明文にある『土曜授業のある日は当番あり』が含まれていないような気がしますけれど、カレンダーの作成法がご質問の内容だったのでしょうか？
もしそうであれば、以下は当番表に関しての回答なので、まったくの的外れとなりますので、無視してくださるようお願いします。


内容がわからないままですが、勝手に解釈した内容で、ひとまずミニチュア的なものをを作成してみました。
内容的には、No4で示した考え方をそのままをエクセルで具体化しただけのものです。
（ビジュアルにした方が伝わりやすいかと思いましたので……実際の内容は同じですが）

添付図は9月の1ヶ月分のみですが、基準日を仮に「2019/4/1」として計算しています。
祝日等は計算に必要となる4～9月のみ、土日授業日は適当に設定、当番者リストも適当に作成してあります。

A列の日付はシリアル値で入力してあり、これと上記の3種類のリスト（添付図D～F列に仮設定）を参照する形で、B列に計算式が設定してあります。
具体的には、B2セルに
=IF(OR(NETWORKDAYS(DATE(2019,4,1),A2,D$2:D$14)-NETWORKDAYS(DATE(2019,4,1),A2-1,D$2:D$14),COUNTIF(E$2:E$10,A2)),INDEX(F$2:F$10,MOD(NETWORKDAYS(DATE(2019,4,1),A2,D$2:D$14)+COUNTIF(E$2:E$10,"<=" & A2),COUNTA(F$2:F$10))+1),"")
の式を入れ、下方にフィルコピーしています。
（A列が空白の場合の処理も必要かもしれませんが、簡略化のために省略）

※ 9/2の当番が、たまたまBBBさんから始まっていますが、仮の条件に基づいて4/1から算出するとその順になるという意味です。
※ 実際に作成なさろうとしているものとは少し違うかも知れませんが、試しに、お手元にまったく同じもの作成してみて、日付や当番者数等を変えてみると、どのように結果が変わるかテストができるものと思います。
（見た目が日付のデータは、全てシリアル値になっています）
※ もしも、内容的にご質問の意図と合っているなら、セル範囲等を実際の内容に合わせて修正してゆけば宜しいかと思います。

No.5 回答者： enunokokoro 回答日時： 2019/08/23 15:53

No.2の回答者です。

年間シートは回答に添付した画像のようなものです。
次の役員さんに説明できそうな形式をとってみました。

主な関数はこちら↓になります。

E列の土日に判定は
　=IF(F2="△","",IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"〇",""))
で求められます。
F列の土曜授業がある場合は△になるようにして、それを考慮した
判定になります。
G列の祝日は
　=IF(COUNTIF(祝日,A2)=1,"〇","")
などで対応します。
H列の長期休みは
　=AND(A2>=Sheet3!$E$2,A2<=Sheet3!$E$3)
で春休みの期間なのか判定できますので、それぞれの休みを別々に
列を用意して〇を出したり、休みの組み合わせで取得します。

J列は休みでの〇のある日の場合のみ連番になる設定です。
　J2のみ　=IF(COUNTIF(E2:H2,"〇")=0,1,"")
　J3以降　=IF(COUNTIF(E3:H3,"〇")=0,COUNT(J$2:J2)+1,"")
これで、当番のある日が連番で表示されます。
https://oshiete.goo.ne.jp/qa/1147059.html

D列は、J列の連番からK2にある当番の人数をもとにしてMOD関数で
余りを出して、当番の番号を出しています。
　=IFERROR(IF(MOD(J2,$K$2),MOD(J2,$K$2),$K$2),"")
空白の場合のエラーなども考慮してあります。

C列はVLOOKUP関数などで番号に応じた氏名を取得します。
https://kokodane.com/kan53.htm

このような年間シートを用意すれば、当番の休みが正しいのか判断
しやすいですし、〇の部分も直接入力することも可能になります。

前期シートなどは、年間シートから月ごとに列単位で取得するだけ
なので、作業的には簡単になると思います。
https://oshiete.goo.ne.jp/qa/8328679.html

No.4 回答者： fujillin 回答日時： 2019/08/23 09:38

こんにちは

説明がよくわからないのと、添付図も判別できないのですが、
「当番表を作成したいが、稼働日に対して順に当番者を割り振ってゆきたい」
という主旨かと勝手に解釈しました。

セルの位置関係等は不明なので、考え方のみです。
エクセルのカレンダー機能（祝日を含む）はご存知のことと思いますが、基準日を決めて（例えば2019/4/1）その日からの稼働日日数を数えられればほとんどできたも同然でしょう。

上記の日数は祝日等を除いて並べれば（当然ながら）1,2,3…と順になります。
これを当番者に割り振るには、当番者の総数nの除余を利用すれば良いことになります。
実際には、1始まりにするためにちょっとした工夫が必要ですが、基準日からの日数を「d」、当番者の総数を「n」とするなら、
　MOD(d-1,n)+1
を計算することで、「1～n」の数が順に繰り返されるようになります。
この数を元に当番者を割り振れば良いことになります。

さて、基準日からの日数を求めるには、普通であれば
　NETWORKDAYS(基準日,対象日,祭日リスト)
で求めることが可能です。

ご質問の場合は、これに加えて
＞年に数回土曜授業がありその時は当番が入るようにしたい。
という特殊事情があるので、その分だけ補正をしてあげる必要があります。
どこかに土日授業日のリストがあるものとして、対象日がこの日を超えていれば日数を+1すれば良いですから、上で求めた日数に
　+COUNTIF(土日授業日リスト,">=" & 対象日)
とすれば補正ができることになり、この日数を基に算出すればよさそうに思います。


※ご質問の 解釈が違っていたなら、スルーしてください。

No.3 回答者： mike_g 回答日時： 2019/08/23 09:37

》 A4のセルに=IF(OR(A4="",WEEKDAY(A4,2)>=6,…

判読困難な添付図では、例えば式 =DATE($A$1,A$2,ROW(A2)) が既に A4 に入力されているのでは？上の記述も奇妙かと。A4 の式中に A4 が登場するなんて、循環参照エラーが出ませんか？！
例え、「エクセルに詳しいわけではな」くても、チコちゃんに叱られないような記述をお願いします。

No.2 回答者： enunokokoro 回答日時： 2019/08/23 09:07

Excelに詳しくないなら、表を単純化することをお勧めします。

前期シートと後期シートは、それぞれ印刷用シートですよね。
これとは別に、年間シートとして日付の列と当番の列を月別単位で
分割しないで同じ列ごとに配置します。
これなら月末などを考慮しないで、順番に割り当てできます。

土日祝日の列を用意して、該当日には〇を付けるようにします。
夏休みなどの長期休暇の列を用意し、該当期間に〇を付けるように
します。休みの列それぞれは単純なものなので〇を付けるのは簡単
になります。

当番列には、休みの列のどちらかに〇があれば当番にしないように
設定します。（OR関数などで指定できます）
この設定までできれば、前期シートなどの印刷シートに該当日時に
応じて、年間シートから参照するようにすれば、関数の組み合わせ
も少なく簡単に済みます。
前期シートなどのAC列も必要ありません。

このように印刷用を別にすることで、管理も簡単にできます。
年間シートも印刷するわけではないので、作業列を増やしても問題
ありません。条件が増えたとしても対応ができるものなので、検討
してみてください。

この回答へのお礼

回答くださりありがとうございます。
そうです、前期　後期シートは印刷するシートなんです。
印刷シートを別に作成する案は思いつきませんでした。
大変お恥ずかしいのですが、自分にはこのOR関数などを組み立てる力が足りません。
次の未来の役員さん（パソコンが苦手な方）にも作成できる表をできれば目指しております。
参考にさせていただきます。

お礼日時：2019/08/23 09:54

No.1 回答者： mike_g 回答日時： 2019/08/22 22:14

》 画像が１枚しか載せれませんでしたが

字が小さくてハズキルーペを掛けても読めなーいッ！

この回答へのお礼

見ていただきありがとうございます。
拡大したものを添付させていただきました
すいません。

お礼日時：2019/08/23 11:53