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

２０２１年度以降の４直３交代のシフト表を作成したいです。
２０２０年のベースはこの写真のようになっています。
これは今年の初めに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.1 回答者： enunokokoro 回答日時： 2020/11/24 18:36

回答が付きにくいようですね。

説明するのが面倒な作りなので、それが原因かも。

関数のみで処理するとして、簡単なヒントだけの回答です。
シフト表の順番の処理の仕方には幾つかの方法がありますが、私が
作成する場合なら、CHOOSE関数を使いますね。
シフトの順番が16日間で繰り返されるので、16進数と10進数で処理
したシリアル値を使って、年間使用ができるものにします。

基本的にはシリアル値の日付から、
　=CHOOSE(16進数の値,1,1,1,1,"休",2,2,2,2,"休",3,3,3,3,"休","休")
これで順番に表示されるようになります。
この日付セルを参照したB5セルに
　=HEX2DEC(RIGHT(DEC2HEX(シリアル値のあるセル)))
とすることで、末尾の16進数を元に10進数の値が取得できます。
あとは、3行目または4行目のどちらかをシリアル値の日付にして、
そのセルを参照させることで対応します。
数式の例：
　=HEX2DEC(RIGHT(DEC2HEX(INDIRECT("R[-"&MOD(ROW()+2,7)+1&"]C",FALSE)-(MOD(ROW()+2,7)*4+1)),1))
これで取得した値をCHOOSE関数と組み合わせをすることで、希望
するシフト表になっていると思います。

添付画像は2020年になっていますが、2021年に切り替えれば、
> ２０２１年の1月１日のシフトは
> A班　休（1の次の日です）
> B班　１（１日目）
> C班　３（３日目）
> D班　２（４日目）
としてスタートします。

No.2 回答者： mike_g 回答日時： 2020/11/25 08:09

》 シフトのサイクルは 1111休2222休3333休休 です。

2020年1月度下記の樣に始まってますね？
A→222ﾔ3333ﾔﾔ1111…
B→11ﾔ2222ﾔ3333ﾔﾔ…
C→ﾔﾔ1111ﾔ2222ﾔ33…
D→3333ﾔﾔ1111休22…
１日は上記に示されたサイクルのどの位置から始めるのか、其の規則を教へて下さい。
2021年１月度は2020年12月31日の續きでなきゃアカンのですか？
其れとも新たに斯樣にすると云ふ規則でも？

No.3 回答者： tatsumaru77 回答日時： 2020/11/25 09:28

私が回答できる保証はありませんが、以下質問です。

１．曜日で土、日、祝日の場合、色を付けていますが、
添付図の①のように直接文字の色を変える方法と、
添付図の②のように背景色変える方法があります。
①の方法が希望でしょうか。

２．祭日に色を付ける場合、別途、祝日用のカレンダーが必要になりますが、それはあなたが準備する必要がありますが、よろしいでしょうか。
A列　　　　　B列
2020/11/3 　文化の日
2020/11/23　勤労感謝の日
のように記述したシートです。

３．Aグループが休みの日だけ、日付の背景に色がついていますが
Aグループだけ、特別扱いするのでしょうか。それとも、これは不要でしょうか。

４．シフトが休みの場合は、「休」ではなく「ヤ」を表示あってますか。

No.4 回答者： mike_g 回答日時： 2020/11/25 11:18

御參考迄に、

添附圖參照(Excel 2019)
Sheet1! B2: =OFFSET(Sheet2!$B2,,MOD(COLUMN(G2)-1,16))
Sheet1! B3: =OFFSET(Sheet2!$B2,,MOD(COLUMN(C2)-1,16))
Sheet1! B4: =OFFSET(Sheet2!$B2,,MOD(COLUMN(O3)-1,16))
Sheet1! B5: =OFFSET(Sheet2!$B2,,MOD(COLUMN(K3)-1,16))
【餘談】１サイクル16進でも32進でもヘッチャラだいッ！？

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

No3です。

１．曜日で土、日、祝日の場合、色を付けていますが、
添付図の①のように直接文字の色を変える方法と、
添付図の②のように背景色変える方法があります。
①の方法が希望でしょうか。

上記の質問は取り消します。曜日の背景は黄色なので、文字の色（土、日等）を変えるパターンになりますね。

No.6 回答者： tatsumaru77 回答日時： 2020/11/25 14:27

>2.出来れば祝日も色を付けたいです。

自分で準備が出来るのか自身がありませんがもし複雑そうならカレンダーを見て自分で色付けしたいです。
>3.2020年はAグループに所属しましたが2021年からは全グループの人が均等に見れるようなシフト表にするつもりです。
>４.シフト表は「ﾔ」の表記でお願いします。

了解しました。土日祝日に自動で色を付けることは可能です。
私の場合、マクロでの提供になりますが、よろしいでしょうか。
その為には、あなたのexcelの環境がマクロが実行可能な状態になってる必要があります。また、マクロのついたファイルを保存するとき、
ブック名の拡張子をxlsxでなくxlsmにする必要があります。
例　Book1.xlsxがBook1.xlsmになります。
それでもよろしければ、提供します。
又その場合、今、表示しているシフト表のシート名を教えてください。
更に、祝日カレンダーのシートを予め作っておく必要があります。
シート名は”祝日”でよろしいでしょうか。

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

>マクロ...。

すいません初心者なのでよく分からないのですがファイル名の後に付いてるやつでしょうか？？

ファイル名の後につくのは拡張子です。
マクロはファイルの中に格納されます。
拡張子は添付図のxlsx,xlsmの部分です。

あなたのPCでBoook1だけが見えていてBook1.xlsxと表示されていないなら拡張子が
表示されていません。

拡張子を表示する方法は下記URLを参照してください。

windows10の場合
https://pc-karuma.net/windows-10-show-explorer-f …

windows7の場合
https://pc-karuma.net/windows-7-show-file-name-e …

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

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

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

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.10 回答者： goomania 回答日時： 2020/11/25 16:36

No.9です。

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