Excel 数式を入れているため、当番が繰り返し循環しない

当番表を作っていますが
数式を入れているためか上表のようにしかならず
当番表に入力している数式を削除すれば循環しますが毎年使う人間が違うので
誰がやってもわかるように数式を削除することなく
下表のようにしたいです。
ご指導宜しくお願いいたします。

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

• どうしても画像が添付できないので・・・申し訳ありません
  
  担当一覧は
  選択した２つのチームを1つにするため数式が入っております。
  毎月変動があるのでチームメンバー表で増減し、それが担当一覧で反映されるように数式を入れています。
  その数式を当番だと認識して、空欄を循環させてしまいます。
  その数式を消さずに、作っていただいた表のように仕上げたいと思っております。
  
  説明が下手でスミマセンが宜しくお願いいたします<(_ _)>

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/08/10 20:51

• 画像添付出来ませんでした・・・。
  お指摘ありがとうございます(*- -)(*_ _)ペコリ

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/08/10 20:52

• 

  ご回答いただきありがとうございます<(_ _)>
  できました！！すごいです！！ありがとうございます。
  
  あと1点
  毎月、メンバー表の順番は動かない（増減は別）ものとし
  途中のものを指定してスタートさせる表を作るにはどうしたらよいでしょうか？
  
  表1)))決まったメンバー表
  パンダ
  犬
  猫
  うさぎ
  
  ⇩
  今月スタート犬とセルに入力したら
  
  表2)))
  犬
  猫
  うさぎ
  パンダ
  
  のようにしたいです。
  度々スミマセンが宜しくお願いいたします<(_ _)>

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

No.6 回答者： goomania 回答日時： 2023/08/15 13:42

No.5です。

>NETWORKDAYS関数を使うと、数式を簡潔にできます。
といっておきながら、NETWORKDAYS関数の使い方が中途半端でした。
D2セルに記述する数式を、

=IF(NETWORKDAYS(B2,B2),INDEX(J$3:J$11,MOD(NETWORKDAYS($B$2,B2)-2+開始値,9-COUNTBLANK($J$3:$J$11))+1),"")

とすれば、更に簡潔な数式になります。

No.5 回答者： goomania 回答日時： 2023/08/15 11:42

fujillinさんの回答でほぼ解決しているのですが、NETWORKDAYS関数を使うと、数式を簡潔にできます。

>毎月、メンバー表の順番は動かない（増減は別）ものとし
>途中のものを指定してスタートさせる表を作るには
>どうしたらよいでしょうか？

とのご要望ですが、ご質問者の掲出された表をよく見ると、E1セルに開始値が入力されているようです。

>今月スタート犬とセルに入力したら

という説明なので、、E1セルに「開始値」という名前を付けます。
J3～J11まで、9種類の担当の入力が可能となっており、I3～I11にそれに対応する番号が付与されているものとします。また、J1セルには

=VLOOKUP(開始値,$I$3:$J$11,2,0)

という数式をいれてあるものとします。
この状態で、E1セルに、例えば「3」と入力すると、J1セルに「パンダ」と表示され、これが、スタート位置を示すことになります。

前提条件は、fujillinさんがお示しになったものとほぼ同じです。

・B列は日付（＝シリアル値）で、土日以外の日に当番を設定する
・B列は空白または日付のみ
・担当者一覧はJ3:J11に存在する(とりあえず最大9個まで)
・担当者一覧は上詰めで入力されており、途中に空白はない
・循環のスタートを指定できるように、E1セルで指定し、J1セルに表示する
この前提で、D2セルに、

=IF((B2="")+(WEEKDAY(B2,2)>5),"",INDEX(J$3:J$11,MOD(NETWORKDAYS($B$2,B2)-2+開始値,9-COUNTBLANK($J$3:$J$11))+1))

という数式を記述し、下方向へコピーしています。

※上記数式中の「9-COUNTBLANK(～)」の9は担当者一覧の最大数を意味します。

これで、E1セルの開始値に好みの数値を入力することで、スタートを指定できるようになります。

No.4 回答者： fujillin 回答日時： 2023/08/10 23:36

No3です。

＞途中のものを指定してスタートさせる
＞表を作るにはどうしたらよいでしょうか？
No2ではそのようなことも想定した式（=H2セルの機能です）にしていましたが、関数で一覧を作成しているのなら、それも考慮された結果が表示されているであろうと解釈して、No3では不要として省きました。

全体の計算方法はNo2で述べたように、該当する日数を計算して、それを担当者数で除して序数を求めているだけです。
一定の数をずらすだけで良いのなら、上記の序数に「一定の数」を加えるなり減ずるなりすれば、循環のスタートをずらすことが可能になります。

循環する順そのものも変えたいのであれば、担当者一覧の横に適当な順番を指定する欄を設け、そこの指定を変えることで可能になると思います。
元の式では、INDEXで序数番目の担当者を参照していますけれど、これを序数に該当する番号の担当者を返すように、VLOOKUP（←完全一致検索）などに変えれば可能と思います。

No.3 回答者： fujillin 回答日時： 2023/08/10 21:57

No2です

＞チームメンバー表で増減し、それが担当一覧で反映
＞されるように数式を入れています。
一覧表に数式が入力されているってことですね。
その場合は、No2の式でも上手くいきません。
（空白か、直接入力を想定していましたので・・）

一覧表が数式の場合は、No2のレイアウトで、D2セルに
=IF((B2="")+(WEEKDAY(B2,2)>5),"",INDEX(G$2:G$15,MOD(SUMPRODUCT((WEEKDAY(B$2:B2,2)<6)*1)-1,SUMPRODUCT((G$2:G$15<>"")*1))+1))
として、下方にフィルコピーでできると思います。

数式を簡略化するために、No2の条件の一部を省いています。
・B列は必ず日付が入力されていて、空き（＝空白セル）は無いものとする。
　（ただし、フィルコピー時の最下部の空白セルには対応しています）
・担当者の順番調整用のH2セルの機能は無いものとする。
　（＝最初の担当は必ずG2セルからスタートする）
・担当者一覧は数式でも良いが、空欄部には空白文字列（＝""）が返されており、
　かつ、担当者名は上詰めで表示されているものとする。

No.2 回答者： fujillin 回答日時： 2023/08/10 12:56

こんにちは

＞当番表に入力している数式を削除すれば循環しますが～～
意味がイマイチよくわかりません。
関数で循環するようにしたいのではないかと推測しますが、削除しちゃったら何も表示されないのでは？（手入力すれば循環できるという意味なのかなぁ？）

「循環」の意味もいろいろ考えられますが、単純に順番になれば良いのであれば、比較的簡単に実現可能と思います。
添付の図が読めないのでよくわかりませんけれど、B列に日付が入力されていて、土日を除いて順に割り振れば良いということでしょうか？
もしそうであるなら、対象行までのB列の「日付かつ土日以外」の数を数えて、右側のリスト（？）からその順に合致するメンバーを選択すれば良さそうに思えますが・・？


よくわからないので、勝手に以下のような条件を設定しました。
（考え方は上記の通りなので、条件が異なる場合は同様に考えれば可能と思います）
・B列は日付（＝シリアル値）で、土日以外の日に当番を設定する
・B列は空白または日付のみ（空白行があるのかは不明ですが・・）
・担当者一覧がどこかに存在する（添付図ではG2:G15の範囲に設定）
・担当者一覧は上詰めで入力されている
　（詰めなくても可能ですが、式が長くなるので）
・循環のスタートを指定できるように、H2セルにスタートする
　担当者の番号を入力できる（空欄でも可）
　（図では、H2が1なのでG2のパンダがスタートになりますが、
　　仮に2であれば犬からスタートするという意味です）

上記の条件を仮定した場合、添付図でD2セルに
=IF((B2="")+(WEEKDAY(B2,2)>5),"",INDEX(G$2:G$15,MOD(SUMPRODUCT((B$2:B2<>"")*(WEEKDAY(B$2:B2,2)<6))+H$2-(H$2>0)-1,COUNTIF(G$2:G$15,"<>"))+1))
を入力して下方にフィルコピーをすれば図のようになります。

※ 条件がもう少し簡単にできるなら、式はもっと簡単になります。
　逆にもっと複雑であれば、長いものになります。
※ 以下の場合はエラー値になります。
・B列に日付（＝数値）か空白以外の値が入力されている場合
・H2セルに数値、空白以外の値が入力されている場合
・担当者一覧に担当者が一人も入力されていない場合

No.1 回答者： zongai 回答日時： 2023/08/09 21:41

画像は必要最小限の部分だけにトリミングして貼らないと、

500ピクセルに縮小され、潰れちゃってもう何がなんだか分からなくなります。

長い数式を画像に含めたかったのかもしれませんが、
そこまで長い数式ならば、
質問投稿の中にコピペした方が良いでしょう。

回答者が数式を編集するにしても、
画像を見ながら入力し直せと…？
ってことになっちゃうので。

補足投稿で画像と数式を上げてみましょ。