２０２１年度以降の４直３交代のシフト表を作成したいです。 ２０２０年のベースはこの写真のようになって

２０２１年度以降の４直３交代のシフト表を作成したいです。
２０２０年のベースはこの写真のようになっています。
これは今年の初めにExcelの数式をなどを全く理解していない時に手打ちで地道に作ったものです。
シフトのサイクルは
1111休2222休3333休休です。
２０２１年の1月１日のシフトは
A班　休（1の次の日です）
B班　１（１日目）
C班　３（３日目）
D班　２（４日目）
としてスタートしたいです。
このシフト表をベースにして「I1」を２０２１.２０２２などと変えると自動的に１月なら3行目の曜日、5~8行目がその年の曜日やシフトに合わせて自動変換、それを12月まで変換出来れば助かります。
ちなみにシフト表の日付は固定しておきたいです。（１月なら４行目、２月なら11行目）
このようにできる数式があれば教えていただけるとありがたいです。
説明不足なところがあればおっしゃってください。よろしくお願いしますm(_ _)m

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

• mike_gさん
  1/1は12/31の続きになります。
  2021年1月1日からのシフトです（１番左）
  A→ﾔ2222ﾔ3333ﾔﾔ1111…
  B→1111ﾔ2222ﾔ3333ﾔﾔ…
  C→33ﾔﾔ1111ﾔ2222ﾔ33…
  D→2ﾔ3333ﾔﾔ1111休22…
  ※2020年12月31日は１番右です。
  
  
  
  tatsumaru77さん
  2.出来れば祝日も色を付けたいです。自分で準備が出来るのか自身がありませんがもし複雑そうならカレンダーを見て自分で色付けしたいです。
  3.2020年はAグループに所属しましたが2021年からは全グループの人が均等に見れるようなシフト表にするつもりです。
  ４.シフト表は「ﾔ」の表記でお願いします。
  
  色々と手間をおかけしてすいません。よろしくお願いします。

    補足日時：2020/11/25 13:58

• tatsumaru77さん
  マクロ...。すいません初心者なのでよく分からないのですがファイル名の後に付いてるやつでしょうか？？
  ファイル名も本日は休日で分からないため明日に会社に行き調べてみます。
  
  祝日の件に関しては題名は祝日で構いません。
  よろしくお願いします(^.^)

    補足日時：2020/11/25 15:51

• 皆様、多数のご回答頂きありがとうございますm(_ _)m
  
  tatsumaru77さん
  遅くなり申し訳ありません。マクロ実行できるように設定を行いました。先にシフト表を設定してから色付けをやっていきたいと考えています。
  
  enunokokoroさん、goomaniaさん
  教えて頂いたわかる範囲で数式を入れてみました。
  気になったのが「B1」に日付が入っていることです。
  月の変換、曜日、日付の設定はお陰様で出来ました。
  「B5」にも数式を入れてオートフィルタで「AF8」までやると1月分は出来ました。
  2月分からはどうすればいいですか？
  あと気になったのが2月29日ですね。
  今年は２９日があったので「AD」列に２９日の枠を作っていました。「I1」を２０２１年に変えてみると「AD11」が１日になってしまいます。
  これはどうしょうもないでしょうか?
  何度もすいません。

    補足日時：2020/11/28 09:17

No.17 ベストアンサー 回答者： goomania 回答日時： 2020/11/28 19:51

No.16です。

前回の投稿にタイプミスがありました。
tatsumaru77さんのマクロについての説明部分で
「シフト表」の「I1」に対象西暦年を入力した状態使います。(誤)
「シフト表」の「I1」に対象西暦年を入力した状態で使います。(正)

なお、1月より後のシフト表作成の説明部分の
作成した1月分の「B5からAF8」までの全てのセルをコピーし、･･････、
は
私の回答またはenunokokoroさんの回答の数式でシフト表を作成した場合、作成した1月分の「B5からAF8」までの全てのセルをコピーし、
という意味です。
また、1月の曜日の条件付書式設定を他の月にコピーする説明の
あとは、この条件付書式設定をコピーし、各月の曜日表示のセルに･･････
は
あとは、この1月の曜日表示セルを選択した状態で(条件付書式設定を)コピーし、各月の曜日表示のセルに･･････
という意味です。

この回答へのお礼

度々ご回答頂きありがとうございました(^o^)
もしよろしければこれに基づいた質問を投稿しますので拝見して頂けたら助かります。

お礼日時：2020/11/29 11:48

No.16 回答者： goomania 回答日時： 2020/11/28 19:13

>「B5」にも数式を入れてオートフィルタで「AF8」までやると1月分は出来ました。

>2月分からはどうすればいいですか？

作成した1月分の「B5からAF8」までの全てのセルをコピーし、「B12」のセルを選択して貼り付けしてください。
以下「B19」「B26」「B33」･･･と順次、月毎に貼り付けします。これで、各月のシフト表が作成できるはずです。

>今年は２９日があったので「AD」列に２９日の枠を作っていました。「I1」を２０２１年に変えてみると「AD11」が１日になってしまいます。
>これはどうしょうもないでしょうか?

ということなので、この課題を解決します。

AD11には「日付」を表示できるシリアル値が数式で入っているものとします。
まず、AD10からAD15までを選択した状態で、「条件付書式」を設定します。
条件付書式>新しいルール>数式を使用して、書式設定するセルを決定>次の数式を満たす場合に値を書式設定の数式欄に
=MONTH($AD$11)<>$A$10
という数式を記述します。
次に書式ボタンを押し、「フォント」タブを選択し、添付画像①のように、文字の｢色」を「白色」にします。
さらに、「罫線」タブを選択し、添付画像②のように左罫線のみ罫線表示にし、あとは「罫線なし」にします。
設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
これで、条件付書式により2/29がある年とない年の両方に対応できる書式になったはずです。
添付画像③は2020年のシフト表で条件付書式設定をした後、I1に「2021」と入力したものです。2/29が表示されていないことが確認できます。
ただし、これはあくまでも文字を白色にし外見上見えなくしているだけなので、「値コピーして他のシートに貼る」などといった操作をした場合は、ご質問者の現在の状況と同様に「AD11」が１日になってしまいます。

さて、ここまで来たら、土日祝日の曜日に色付けするところまで説明します。
まず、祝日の一覧表をtatsumaru77さんがNo.15の回答で示されたように「祝日」という名前の別シートに作成します。1月の曜日を表示しているセルの全てを選択し、上記の「2/29」の場合と同様に、条件付書式を設定します。
次の数式を満たす場合に値を書式設定の数式欄に
=WEEKDAY(B3)=1
という数式を記述し、書式の「フォント」タブから、文字色を「赤色」にします。設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
次に、同じ手順で条件付書式設定の数式欄に
=WEEKDAY(B3)=7
という数式を記述し、書式の「フォント」タブから、文字色を「青色」にします。設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
さらに、同じ手順で条件付書式設定の数式欄に
=COUNTIF(祝日!$A:$A,B3)>0
という数式を記述し、書式の「フォント」タブから、文字色を「赤色」にします。設定が完了したら条件付書式のダイアログボックスが全て閉じるまでOKボタンを押します。
これで、曜日に色をつける条件付書式設定の1月分が完了しました。
あとは、この条件付書式設定をコピーし、各月の曜日表示のセルに「貼り付けのオプション」から「書式設定」を選択して、貼り付けすれば完了です。

また、tatsumaru77さんのマクロは、色付けのみを行うものではなく、シフト表を作成するものです。予め「シフト表」の各セルに書式設定しておくと使い易いと思います。「シフト表」の「I1」に対象西暦年を入力した状態使います。このマクロを実行できるスキルがあれば、上記のような煩雑な設定は不要になります。

No.15 回答者： tatsumaru77 回答日時： 2020/11/28 09:53

マクロが実行できる環境になったということなので、マクロを提供します。

実行時の注意点です。
1.シート名：シフト表　を作成しておいてください。そこにカレンダーが作成されます。
もし、既にシフト表があるとそれは、上書きされますので、バックアップをとるか、新規で「シフト表」のシートを作成しておいてください。

2.シート名：祝日　来年以降の祝日を登録しておいてください。添付画像を参照ください。２行目以降に、祝日を登録してください。1行目は見出しです。

3.シフト表の罫線、及びセル結合はあなたが行ってください。
マクロは罫線、及びセル結合は行いません。

4.I1に指定可能な年は2020～2099までです。
（今年もできるのでマクロで作成しものと、あなたが手入力したものを比較してください）
以下のマクロを標準モジュールに登録してください。
---------------------------------------------------------
https://ideone.com/9LD0lF

ここに投稿するとエラーになるので上記URLに投稿しました。
上記URLのマクロをコピペしてください。

この回答へのお礼

度々ご回答頂きありがとうございました(^o^)
もしよろしければこれに基づいた質問を投稿しますので拝見して頂けたら助かります。

お礼日時：2020/11/29 11:48

No.14 回答者： goomania 回答日時： 2020/11/28 09:34

No.10です。

とりあえず、ご質問者の疑問にお答えし、2/29問題は後ほど回答させいてただきます。

>気になったのが「B1」に日付が入っていることです。

B1に日付が入っている添付画像①は、あくまでも数式を検証するためのテスト画像であって、
「B1に日付を入れて表をつくりましょう」という意味ではありません。

ご質問者が数式を応用する場合の参考としていただくためのものです。
このため、現在の作表には必要ありません。
よろしく御願いします。

No.13 回答者： mike_g 回答日時： 2020/11/26 14:08

貴方が示した添附圖が示す形式のカレンダーを私の貧弱な知識で完成するには困難かも(さう思ふ最大の理由は、各月に依ってカレンダーの横幅がマチマチ)。

代わりに此処の添附圖に示した物なら、と云ふ事でアップロードして措きます。
取り敢えず祝日も含めて、2023年迄對應させた物です。
(左端圖に措いて、３行目を下方にズズーッと1463行目迄オートフィル)

セル A2、B2 の年、月の數値を變更すればカレンダーの(３行目の)起点が該當年月の朔日から始まります。貴方が補足で應へて呉れた
》 1/1は12/31の続きになります。
》 2021年1月1日からのシフトです
の通りに成って居るのが判讀出來ますか？

土曜、日曜の行は夫々青、赤色の塗りつぶし、祝日は橙色ですが同色の日曜は翌日以降に振替休日(此れも祝日扱い)が在る事を示しています。

御望みのモノじゃ無いので、此れ以上の解説は遠慮させて下さい。m(_._)m
失禮しました。

この回答へのお礼

ご回答頂きありがとうございました(^o^)

お礼日時：2020/11/29 11:46

No.12 回答者： enunokokoro 回答日時： 2020/11/25 19:26

No.11の添付画像の一部に誤りがありました。

2022年ようにしたつもりでスクリーンショットしたので、
2021年のままになっていたのに気が付きませんでした。

2022年用にしたものを添付しておきます。

この回答へのお礼

度々ご回答頂きありがとうございました(^o^)
もしよろしければこれに基づいた質問を投稿しますので拝見して頂けたら助かります。

お礼日時：2020/11/29 11:49

No.11 回答者： enunokokoro 回答日時： 2020/11/25 18:46

No.1の回答者です。

皆さんの色々な案が出てきていて、私も参考になります。
私の案については補足がないので、あくまで参考としてくださいね。
とりあえず、No.9の回答者さんが使用する関数以外では同じもので
具体的に説明しているので、この回答は補足程度の追加回答です。

日付部分について。
No.9の回答者と基本的に同じです。
　=DATE($I$1,$A3,COLUMN()-1)
として、日付を各月分用意しています。
曜日または日付どちらかで使用すれば済みますね。
曜日のほうで設定したら、表示形式で対応してください。
日付は
　＝B3　
とすれば、各月を用意するときにオートフィルで対応できます。

シフト表について。
B5セルには、数式としては長いですが
　=CHOOSE(HEX2DEC(RIGHT(DEC2HEX(INDIRECT("R[-"&MOD(ROW()+2,7)+1&"]C",FALSE)-(MOD(ROW()+2,7)*4+1)),1))+1,1,1,1,1,"ﾔ",2,2,2,2,"ﾔ",3,3,3,3,"ﾔ","ﾔ")
を入れています。
No.9の回答にある方法のほうが、コンパクトで良いですね。

日付の色などについて
条件付き書式で対応するだけなので難しくはないと思います。
http://www4.synapse.ne.jp/yone/excel2010/excel20 …

添付画像は、上記条件で作成した1月分だけ3年分並べたものです。
各月の分も正しく表示されますし、年指定を変えれば対応できます。

No.10 回答者： goomania 回答日時： 2020/11/25 16:36

No.9です。

前回の回答で漏らしたことを追記します。
前回回答の一覧用のシートでは、曜日の行および日付の行にはそれぞれ書式設定が必要です。
曜日の行にはシリアル値(日付)が入るものとして、｢ユーザー設定」で「aaa」を設定することで曜日を表示させます。
日付の行にもシリアル値(日付)が入るものとして、｢ユーザー設定」で「d"日"」を設定することで「○日」と表示させます。

No.9 回答者： goomania 回答日時： 2020/11/25 16:24

このご質問は、No.1さん、No.4さんのご指摘のとおり、16個のシフトパターンが繰返すというところに注目すれば、解決できると思います。

No.1さんは16進数とCHOOSE関数を組合せ、No.4さんは基本テーブルの作成で対応しておられますが、本回答ではMID関数、MOD関数、FIND関数を組合せてみました。
日付セルに対応したA、B、C、D各班のシフト状況を表示するには添付画像①を例にとれば、B2に
=MID("ヤ1111ヤ2222ヤ3333ヤ",MOD(B$1+FIND($A2,"DCBA")*4,16)+1,1)
という数式を記述すればよいということになります。
要は日付セル(シリアル値であることが前提)を参照して1～16の値を導き、この値と班の区別によって、その日のシフトを決定するというロジックを作ればよいわけです。
そこで、シリアル値を16で割った余りをMOD関数で求めて1を加えることで1～16までを繰返すパターンを発生させます。
この値がMID関数の｢文字列の中の位置」を指定してくれるように調整します。
A、B、C、D各班のシフト状況を示す、MID関数の｢文字列の中の位置」と上記の1～16の数字には「ずれ」がありますのでこの「ずれ」を修正する必要があります。
「FIND($A2,"DCBA")*4」を使ってこのずれを調整しています。
このため添付画像でいうA列の「班名」とFIND関数に記述する"DCBA"は半角・全角、大文字・小文字等の相違があったり、A列に「A班」などとアルファベット以外の文字をつけたりしないようにして、必要な場合は「書式設定」で対応してください。
添付画像①として一日分のシフト状況を表示するサンプルを作成したものを投稿しておきます。
日付欄に任意の年月日を入力してみて、ご質問者が既に作成した一覧表の内容と一致するか確認してみてください。

一覧表のフォーマットは色々考えられると思いますが、年度を指定すると日付と曜日が自動的に表示されるカレンターを作成し、そのカレーンダーの日付のシリアル値を参照する上記数式を記述した表を作成すればよいことになります。
2021年分の一覧表をご質問者の作成されたフォーマットに近いもので作成してみました(添付画像②)。
この場合のB5に記述する数式は、
=MID("ヤ1111ヤ2222ヤ3333ヤ",MOD(OFFSET(B5,(2-MOD(ROW()-2,7)),0)+FIND($A5,"DCBA")*4,16)+1,1)
とすればオートフィル等でコピーしても修正不要になります。
セルの色づけについては他の回答者さんにお任せしたいと思います。

No.8 回答者： tatsumaru77 回答日時： 2020/11/25 16:17

マクロを登録して実行するためには

あなたのPCのexcelをマクロが実行できるようにしておく必要があります。
こちらを一通り、読んで、実際に確認してみてください。
https://www.tipsfound.com/vba/01004