先日、年間の４直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.12 ベストアンサー 回答者： goomania 回答日時： 2020/12/01 23:01

他の回答者さんが「数式を消さない方法」について回答されないので、若干荒技ですがその方法を投稿します。

まず、週間シフト表とは別のシートに「選択用リスト」などと適宜の名前をつけて、A列に選択する項目を入力します。
A列の書式は「文字列」を指定します。試しに選択項目として、「有休」「欠勤」をA1、A2に入力します。
最後にA3に、入力規則用に修正した以下の数式を入力します。
=CHOOSE(MOD($B$4-1-INT((ROW()-6)/5)*4+COLUMN()-2,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")
ここで重要なのは、A3セルの書式が文字列であることです。つまり、上記の数式を入力してもA3セルの内容は数式の結果ではなく、数式そのものが表示されている状態だということです。
次に、週間シフト表を作成するシートの各班のシフト表示用のセル全てに
=CHOOSE(MOD($B$4-1-INT((ROW()-6)/5)*4+COLUMN()-2,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")
という数式を記述します(全て同じ数式なので一つのセルに記述し、あとはコピーでよい)。
さらに、数式を記述した全セルに入力規則を設定します。
入力規則の「設定」タブの「入力値の種類」で「「リスト」を選択、「元の値」で「選択用リスト」シートのA1からA3を指定します。さらに「エラーメッセージ」タブで「停止」ではなく、「警告」に設定します。
これで、「有休」「欠勤」「数式」の三択が可能になりました。もともと数式が記述されていますので、有休等を選択しなければそのままでOKですが、一旦有休等を選択した場合にも「数式」を選択し直すことができます。
しかし、「有休」「欠勤」は単純にドロップダウンリストから選択入力すればよいのですが、「数式」の場合は選択しても数式の文字列が表示されるだけで、シフト勤務の内容が表示されません。
ここで、数式バーにカーソルを移動し、表示されている数式の末尾をクリックしてEnterを押してみて下さい。
警告メッセージが表示されると思いますが、これに「はい」を返すと、シフト勤務の内容が表示されます。
数式を文字列で入力するという荒技ではありますが、参考になれば幸いです。

この回答へのお礼

ご回答頂き感謝しております。
実際、まだ全てを実践したわけではありませんが参考にさせて頂きますm(__)m

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

No.11 回答者： goomania 回答日時： 2020/12/01 04:33

No.10です。

前回の回答で漏らしてしまった注意事項を追記します。

>これにより、添付画像①のように、数式と入力規則を共存させることができます。

と書きましたが、これはあくまでも「有給」等を選択入力する前の状態です。
一旦、選択入力で「有休」等を選択してしまうと、数式は消えてしまいます。
このため、週間シフト表はマスターのブックまたはシートを作成し、各週のシフト表作成作業は、マスターをコピーしたもので作業する必要があります。
また、誤って他のセルを選択して「有休」を入力してしまうと、そのセルの数式がきえてしまいます。
この場合はメニューの「編集」から「元に戻す」で、数式を復活させてください。
「数式が消えないように」というご質問者のニーズと異なるかもしれませんが、次回の週間シフト表の作成は、前回作成したものを修正するか、マスターコビーから作業開始するかの違いだけで、手間は変わらないと思います。

また、以下の数式
=CHOOSE(MOD(B$4-1-INT((ROW()-6)/5)*4,16)+1,1,1,1,1,"定休",2,2,2,2,"定休",3,3,3,3,"定休","定休")
の中の「INT((ROW()-6)/5)」の部分の6は氏名欄の開始行位置、5は一つの班の人数(全ての班が同じ人数が前提)ですので、例えば、A班に新人の見習いを配置し、コーチングのためA班の1人がサポートにつくといった場合、A班は一時的に6名となるため、この数式は使えません。

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,"定休","定休")

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

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.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.7 回答者： mike_g 回答日時： 2020/11/30 17:01

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

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

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

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

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

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

No3です。

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

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

NO3です。

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

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を更新します。

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