先日、年間の４直3交代のシフト表の作成をお願いしたのですが今回は年間のシフトを元に週間の予定表に自動

先日、年間の４直3交代のシフト表の作成をお願いしたのですが今回は年間のシフトを元に週間の予定表に自動入力出来ないかよろしくお願いしますm(_ _)m
※この写真のソフトは正規のEXCELではありません。再現のために作ったものです。
元々の仕様が「B4」に日付を入力すると「B3」「C3」「C4」「D4」「E4」「F4」「G4」「H4」が自動入力されるようになっています。
本題はこれではないのですがこれももっと楽に入力出来る方法があれば教えて下さい。
本題ですがABCDのグループがあり各グループ５人所属しています。
「A6」より下は氏名のみが入ります。
現在この週間勤務表は各マスにドロップダウンリストを作成してあって（1.2.3.定休.有給）のリストから選択して作成しているため時間が掛かります。
先日教えて頂いた年間のリストを元に例えば「B4」を入力すればABCDグループ5人×20人分のシフトが自動的に入るようにする方法はありますか？
ただし、有給を取ったりする場合もあるので後でドロップダウンリストから「G7」のように有給を選択したりもしたいです。
※基本は自動入力で後で編集も出来れば助かります。

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

• mike_gさん
  回答して頂きありがとうございます。
  見事に出来ました(^O^)
  ２点ほど質問追加させてください。
  Ａ．Ｂグループの式は分かったのですがＣ．Ｄグループの式も教えていただけますか?
  Ｃ=「B16」Ｄ=「B21」の予定です。
  
  あとドロップダウンリストで有給とかにしてしまうと式が消えてしまいますね(´・ω・｀)
  可能であれば例えば１２月の第4週の1/2の「G7」をドロップダウンリストで有給に変更した場合、式が消えずに？次の週に変更しても変更した「G7」は元の式通りの表示になることは出来ますか?

    補足日時：2020/11/30 07:21

• mike_gさん
  ご指摘ありがとうございます。
  データーを送っていませんでしたね。
  失礼しました。
  tatsumaru77さん
  ご回答頂きありがとうございます。
  じっくり拝見させていただきます。

  
    補足日時：2020/11/30 19:09

No.1 回答者： mike_g 回答日時： 2020/11/29 16:57

添附圖が參考になれば仕合せかと。

B3: 2020 (西暦年) ←書式: G/標準"年"
C3: 12 (月) ←←←←書式: G/標準"月"
D3: 4 (第4) ←←←←書式: "第"G/標準
E3: 月 (曜日) ←←←書式: @"曜日"
B4: =DATE(B3,C3,D3*7-WEEKDAY(DATE(B3,C3,1-FIND(E3,"月火水木金土日")),3))
￣￣書式: m/d(aaa)
同上書式を設定し、且つ、式 =B4+1 を入力したセル C4 を右方に(NB列迄)ズズーッとオートフィル
下記の各セルに夫々右に示した式を入力した後で下方に４行及び右方に(NB列迄)ズズーッとオートフィル
B6: =CHOOSE(MOD(B$4-1,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")
B11: =CHOOSE(MOD(B$4-21,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")

【云はずもがな】2020年12月の第４月曜は28日のカレンダー開始日に該當

No.2 回答者： mike_g 回答日時： 2020/11/29 17:03

[No.1]の補足、

セル B11 の式は(其の儘でもＯＫなるも)下記の方がスマートですね(*^_^*)
=CHOOSE(MOD(B$4-5,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")

No.3 回答者： tatsumaru77 回答日時： 2020/11/30 08:54

下記URLに記載しました。

https://ideone.com/ZTDV83

仕様上の注意
１．このマクロは前回のマクロとは別の標準モジュールに登録してください。
（前回のがModule1なら今回のはModule2にしてください）

２．B4～H4の書式はm/d/aaaであることが前提です。これで、１１／１５／金のような表示になります。

３．マクロはA列については、更新しません。
　　B3,C3,B4～H4,B6～H25を更新します。

４．このシートのシート名は”週刊シフト表”にしてください。

No.4 回答者： tatsumaru77 回答日時： 2020/11/30 09:35

NO3です。

週刊シフト表 は間違です。
週間シフト表に訂正します。

No.5 回答者： tatsumaru77 回答日時： 2020/11/30 15:13

No3です。

追伸
あなたが、入力するのはB4の日付のみです。（念のため）

No.6 回答者： mike_g 回答日時： 2020/11/30 15:23

》 ２点ほど質問追加させてください

》 Ｃ=「B16」Ｄ=「B21」の予定です。
私、キャッチボールやラリーを何囘も繰り返すのは嫌いです
單に B16、B21 だけでなく、範圍 B16:G21 のデータも全て教えて下さい。
それとも、貴方から教えられなくても觧る方法があるので、黙って居られたのですか？

No.7 回答者： mike_g 回答日時： 2020/11/30 17:01

[No.6]用の添付図です。

♪黄色いサクランボ♪(ウッフン)の部分です。

No.8 回答者： tatsumaru77 回答日時： 2020/11/30 18:52

>あとドロップダウンリストで有給とかにしてしまうと式が消えてしまいますね(´・ω・｀)

>可能であれば例えば１２月の第4週の1/2の「G7」をドロップダウンリストで有給に変更した場合、式が消えずに？次の週に変更しても変更した「G7」は元の式通りの表示になることは出来ますか?

mike_gからの回答がないようなので、僭越ながら私が回答したします。
>>mike_gさんへ
もし、私の回答が誤っているようでしたら、その旨、ご指摘ください。

関数式が設定されたセルへ手入力なりドロップダウンリストで値を設定すると、そのセルの関数式は消滅します。
従って、次の週に元通りにするためには、関数式を再設定する必要があります。
従って、「関数式を再設定するのがいやなら、ドロップダウンリストで値を設定してはいけない」ということになります。

それ故、今回のようなケースではマクロでシフト表を作成し、それをドロップダウンリストで更新するのが、もっとも簡単な解決方法です。

但し、マクロを使わなくても、あなたの要求は実現可能です。
あなたの要件をまとめると
１．関数式でシフト表を作成したい。しかし、１度設定した関数式を、次の週に、再設定することは避けたい。
２．作成したシフト表の値だけでなく、”有給”等の値も、必要があれば設定できるようにしたい。
ということになります。

以下のような方法になります。
シフト表の右側に同じサイズの表を作りその右側の表をドロップダウンリストで設定可能にします。
シフト表自体（左側）はドロップダウンリストによる更新は禁止になります。
添付図参照。
右側の表のどこかに”有給”を設定すると、それが、自動的に左側の表に反映されるようにします。
添付図の黄色いセルです。

左側に設定する式は、以下のような手順の式です。
１．右側の表の該当するセルが空白でないならその内容を表示。
２．空白なら、今まで通りのシフト表の値を表示。

なお、右側のセルは有給以外の任意の値が設定可能です。(1,2,3,定休も設定可能）
尚、添付図が、小さくて見づらい場合は、下記URLの図を参照ください。
https://gyazo.com/efed64c5e37a7ff152de245b14355122

この方法で良ければ、その関数式を提供可能です。

この回答へのお礼

色々と詳しく説明頂き感謝しております。
ありがとうございました。

お礼日時：2020/12/03 21:42

No.9 回答者： mike_g 回答日時： 2020/11/30 21:42

2020/11/30 19:09附の[補足コメント]へのコメント、

添附圖參照(Excel 2019)

範圍 B6:B25 には次式を入力するが、係数pm1 は氏名に依って、シートShiftCycleに示す正数pm1(又は負数m2でもＯＫ)に變更の事。
=CHOOSE(MOD(B$4+pm1,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")

A6:A10→氏名A1: 15
A11:A15→氏名B1: 11
A16:A20→氏名C1:　7
A21:A25→氏名D1:　3

因みに、式 =YEAR(F4) を入力したセル F3 を右方にズズーッとオートフィルして居る(オアソビ?)。

この回答へのお礼

初期に回答頂き感謝しております。
お陰様でスムーズに作成していくことができましたm(__)m

お礼日時：2020/12/03 21:41

No.10 回答者： goomania 回答日時： 2020/11/30 22:57

既に、他の回答者さんにより殆ど解決済みの中、横から割り込むようで、心苦しいのですが、先日回答させていただいた、年間のシフト勤務表はすでに作成されている状況だと認識していますので、一年間の予定はあるものの、有休等もあるので、週間のシフト表を実勤務の一定の期間前に確定させたいというニーズと理解しました。

だとすると、敢えてこの週間シフト表に「開始日」を入力させる意味はあるのか疑問になりました。
そこで、週間シフト表は今日から4週間後の月曜日からのものを作成するという前提にして作成したみました。
したがって、B4に「=TODAY()-WEEKDAY(TODAY(),3)+28」(最後の+28が4週間後を示していますのでこの数値で何週間後を適宜調整可能です)という数式を入れておき、必要ならここに直接日付を入力するものとします。

さらに、「有休」「欠勤」などを指定するための入力規則の設定についてシフト勤務を表示するは全てのセルに添付画像②③の設定を行うものとします。
これにより、添付画像①のように、数式と入力規則を共存させることができます。
添付画像①では「有休」「欠勤」をリスト化していますが、会社によっては「公休」「休務」「産休」「育休」なども必要かも知れません。

なお、既にNo,9さんが示されたように、班によって異なる数値の調整が必要となり、No.9さんは別表で対応されていますが、数式でも対応可能です。
>Ｃ=「B16」Ｄ=「B21」の予定です。
ということであれば、添付画像①でB15に記述した数式

=CHOOSE(MOD(B$4-1-INT((ROW()-6)/5)*4,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")

を表示していますが、この数式はすべてのシフト勤務表示セルにコピーできます。