エクセルで少し複雑な当番表を作成したい。

担当するのは1日一人、人数は6名です（A～Fとします)。
月曜～土曜と祝祭日とでローテーションが変化します。

月～土
AABCDEF,ABBCDEF…ABCDEFF,ABCDEF　→　最初に戻る。

祝祭日
EACBEDF

今までは手入力ですべて行ってきましたが、時間がかかることと誤りが多いことで大変不便に感じております。
例えば、1月1日がローテーションのどの場所かを指定すると自動で1年分が入力されるような表は作成可能でしょうか？
完璧な全自動でなくても、１から手入力するより少しでも簡素になれば良いと考えています。

当方、関数やVBAに関する知識はほとんどありませんが、社内に教本等もありますので、ヒントをいただければ悩みながらでもコツコツ作っていきたいです。

皆様のお力を拝借したいです。
よろしくお願いします。

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

• ご回答ありがとうございます。
  説明不足でしたので補足させていただきます。
  
  同じ社員が2日連続で当番になる理由は、6人で6日間をローテーションさせることで、例えばAが毎週月曜日に固定されてしまうといったことを避けるためです。
  
  ですので、月~土がAABCDEFの場合は、Fが次の週の月曜日に当番になる形になります。
  すなわち、次の月∼土はFABBCDという形にしたいのです。

  No.4の回答に寄せられた補足コメントです。 補足日時：2021/01/05 14:12

• ご回答ありがとうございます。
  
  本件とはあまり関係がありませんが、少しだけ補足させていただきます。
  当番というのは日勤のシフトのことではありません。
  わが社は終業後から次の日の始業までの時間の電話対応を6人が交代で行っています。
  
  なので何時から何時といったことは考えず、なるべく均等に不公平無く分けたいという考えから上記のローテーションになりました。
  
  よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/01/05 14:19

No.10 ベストアンサー 回答者： tatsumaru77 回答日時： 2021/01/08 10:05

No9です。

今後のことを考えて、多少修正しました。
ローテーションの組み合わせ変える場合
ro_tbl = Array("AABCDEF", "ABBCDEF", "ABCCDEF", "ABCDDEF", "ABCDEEF", "ABCDEFF", "ABCDEF")
hl_tbl = Array("EACBEDF")
の内容を変えれば、良いようにしました。
今後、人数が増減し、Gさんが追加になり
"AABCDEFG"のようになっても、上記の部分を変えれば、それに対応します。もちろん、人が減って、"AABCDE"になっても対応します。

ro_tblが月～土のローテーション
hl_tblが日曜祝祭日のローテーションです。

ソースはこちらです。
https://ideone.com/aqZbQX

この回答へのお礼

ありがとうございました。
結果的にすべてをお任せする形になり、申し訳ありませんでした。
後学のために仕組みを理解しつつ、状況に応じて中身を変更しながら使用させていただきたいと思います。
本当に助けられました。

お礼日時：2021/01/08 16:33

No.9 回答者： tatsumaru77 回答日時： 2021/01/07 16:35

シート名：当番表

https://gyazo.com/fd388ccc8bd4219e8fedec690a6479f5 参照
シート名：祝日　添付図参照
上記の２つのシートを準備してください。
シート名：祝日には、処理年の対象となる祝日を添付図の例のように記入してください。
（１行目は見出しです。データは２行目からです。行の抜けが無いようにしてください）

シート名：当番表　についてです。（入力個所は黄色のセルです）
D1に処理対象の西暦年を入力します。
K1で月～土の処理パターンを選択します。（リスト形式なのでそこから選択します）
M1で月～土の処理パターンの何番目から開始するかを入力します。
M2で祝祭日の処理パターンの何番目から開始するかを入力します。

緑色のセルはガイドになります。あなたが作成します。
４行目以降は全て、マクロが自動で作成します。
以下のマクロを標準モジュールに登録してください。
------------------------------------------------
文字数オーバーでこちらに投稿できませんでした。
下記URLにアップしています。
https://ideone.com/hQtjdz

No.8 回答者： tatsumaru77 回答日時： 2021/01/07 15:46

補足要求です。

１．日曜日は当番なしで間違いないでしょうか。

２．月～土のローテーションですが、
「AABCDEF,ABBCDEF…ABCDEFF,ABCDEF」の最後のABCDEFの気になります。
①AABCDEF
②ABBCDEF
③ABCCDEF
④ABCDDEF
⑤ABCDEEF
⑥ABCDEFF
⑦ABCDEF
のように⑦のパターンもあり、①～⑦を繰り返すということで、間違いないでしょうか。

３．祝日があった場合の当番の確認です。
AABCDEFのパターンなら
月：A
火：A
水：B
木：C
金：D
土：E
ですが、もし、水曜日が祝日なら、
祝日パターン：EACBEDFの１番目から割り当てることになっていれば、
水曜日はEが割り当てられ、Bさんは木曜日にずれることになりますか。
つまり、
月：A
火：A
水：E(祝日)
木：B
金：C
土：D
であってますか。

４．上記３で間違いないとすると、同じ人が最大３日間続くことがありますが、
それでよろしいでしょうか。

５．「1月1日がローテーションのどの場所かを指定する」ということですが、
指定する項目は、以下でよろしいでしょうか。
１）月～土の場合、どのローテーションから開始するか。
（項番２の①～⑦のどれから開始するか）
２）上記で決まったローテーションの何番目に人から開始するか。
例として、②ABBCDEFから開始する場合、
１番目から開始ならA
２番目から開始ならB
３番目から開始ならB
４番目から開始ならC
・・・・
７番目から開始ならF
になります。
３）祝祭日（EACBEDF）の何番目から開始するか。
１番目から開始ならE
・・・・
７番目から開始ならF
になります。

６．年間の当番表のレイアウトは、下記URLのような内容でよろしいでしょうか。
https://gyazo.com/fd388ccc8bd4219e8fedec690a6479f5
このサイトでは画像が小さくなってしまい、見えにくくなってしまうので、他にアップしました。
上記で良ければマクロの提供は可能です。

この回答へのお礼

丁寧に書いていただいてありがとうございます。

1．日曜祝祭日の誤りでした。大変申し訳ありません。
2．①～⑦の繰り返しで間違いありません。私も気になっていますがそういう方針だそうです。
3．あっています。
4．3日連続することがあります。
5．そのように出来たら一番良いと考えています。
6．素晴らしいです。本当にありがたいです。

お礼日時：2021/01/07 16:09

No.7 回答者： aouze_ya 回答日時： 2021/01/07 00:07

｢ルールがきちんとできていれば｣

たぶん、ルールはそれほど明確にはなっていなくて、
だいたい、A⇒B⇒C⇒D⇒E⇒F⇒（Aに戻って）
何か都合がつかない、体調不調など場合は臨機応変に交替して、
でも、全員がほぼ均等に夜勤の電話当番を、
それでも、祝祭日、年末年始、お盆、日曜などは、都合が悪い人もいる、休日当番が多くなってもいいというEがいるけれど、一切休日当番はしない人が出るのはまずい。　全員均等ではなく、ちょっと偏った当番回数での設定にしたい。　休日当番表を先に作って、あとを、全員が順番に当番をする仕組みにしたい。　でも、全員で6名だと、月～土で6日だから、毎週の曜日当番が回ることになりかねない。それだと、不公平に感じる人もいるから、今週火曜が当番の人は、来週は火曜ではないように工夫したい、、、
　　というようなことではないかと思います。

しかし、(祝祭休日のローテーション)と(月～土のローテーション)を別個に検討すると、四連休があって、[EACB"EDFE"ACBEDF]の"EDFE"が四連休の填まって、四連休の前後の日に、(月～土のローテーション)でたまたまEが当番になると、5日間で3日がEの当番という計画表ができてしまいます。
実際には臨機応変に当番を入れ替えれば済むのでしょうが、ルールそのものを、別のものに変えた方が良さそうな気がします。

(祝祭休日のローテーション)を最初に決めて、(月～土のローテーション)は、その前の当番状況で、当番が短期間に続くことがなく、まあ、だいたい、全員がほぼ似た日数が当番で、曜日の偏りもあまり起きないようにという、状況次第の方式なら、それで構わない、というような、比較的、いい加減な方式でいいと言うことにされてはどうなのでしょう。　
1年間だと、メンバーにもいろいろ事情ができたり、人数も変わるでしょうから、(なるべく均等に不公平無く分けたいという考え)であっても、｢来週からは、AABBBCDDEEEFFの当番比率になるように、計画を修正｣というような対応もできるように考えてはどうでしょうか。

＞　悩みながらでもコツコツ作っていきたいです。

とは言っても、ルールが単純ではなくなれば、それほど簡単には作れません。
Excelを使う時、1年365日を、ひたすら1列の中で、上から下に並べ(A列)、B列に曜日や祝祭日を並べ、祝祭日や日曜に、C列に暫定で当番を割り当て、D列～I列に各人のその日付までの累計当番回数を入れる式をいれてはどうでしょう。この時点では平日の当番が未記入ですから、祝祭休日の当番数しか表示されませんが、平日の当番を入れていく都度、当番数が変わります。
また、J列～O列に、各人のその行から6行(何行でも良いけれど)上までの当番日数を計算する式を入れます。　この値を参照すれば、各人の当番期待比率(まあ、1/6づつでも、Cさんは少なく･Eさんは多いでも、期待する割)と比べて、次の当番候補を選ぶ式が出せます。順位が同じ人が3人いる場合は、この人という第2条件か第3条件も決めておけばいいです。
これらの式を、P列より右を使って出せるようにしておきます。
前週の同じ曜日に当番だった人は選ばないという条件は、各人の当番期待比率から人を選ぶ候補から、前週の同じ曜日に当番だった人は外すというルールを作ればなんとかなります。

もしも、VBAが使えるのであれば、別に実績表を作って、実績を計画に移し、そのとき、3週間先までの計画は以前のママ固定し、4週間よりも先の計画だけが式で再再計算されるというような対応もできそうです。

実際にやっていないので、この方式でできるか、保証はできませんが、単純な数式で計画表を作るのではなくて、積み上げで、次々と日毎の計画が、1年先、2年先まで作れそうな気がします。

この回答へのお礼

ご回答ありがとうございます。

最初の文については全くその通りです。
ルールについては色々工夫した上で3年前に今の形になりました。
これからも少しずつ改良していく予定です。

＞比較的、いい加減な方式でいいと言うことにされてはどうなのでしょう。

このローテーションは手入力で表を作成する際に考える手間を省くために作られたもので、そこまで厳格なものではありません。
実際、社員どうして相談をして順番を交代することも多いのです。

色々な発想を書いて下さってありがとうございます。
私には思いつかないことで、非常に勉強になります。

お礼日時：2021/01/07 16:42

No.6 回答者： fujillin 回答日時： 2021/01/05 15:42

こんにちは

No1様がおっしゃっているように、ルールがきちんとできていれば、それをコード化すれば自動化は可能です。


一方で、いろいろな方から回答よりも質問ばかりが出ているのは、質問者様の記したルールの説明が十分ではないからです。
とは言え、説明をしていないだけで、ルールはちゃんと定まっているものと想像しますので、（多分）自動化できるのではないかと推測します。

＞悩みながらでもコツコツ作っていきたいです。
とのことですので、仮に、ルールに不十分なところがあったとしても、都度きちんと整理してゆけば宜しいでしょう。


まずは、日付の計算に慣れる意味からも、
・「年を指定したら、1年分のカレンダーを作成する」
といったものでも作成してみてはいかがでしょうか？

それができたら、
・カレンダーに曜日や祝祭日も表示する。
・ルールに従って、n番目の当番記号(A～F)を返す関数を作成する。
・上記に祝祭日判定も含める。
　　・・・・・
この辺りまでできるようになれば、完成はかなり近いものと想像します。

※途中で行き詰まったら、また、ご質問なされば宜しいでしょう。
　その際には、できるだけ必要充分な情報で簡潔に質問なさるのがコツかと思います

この回答へのお礼

ご回答ありがとうございます。

説明不足でした。皆様が親身になって下さって本当に感謝しております。

・年を指定したら、1年分のカレンダーを作成する
・カレンダーに曜日や祝祭日も表示する
ここのあたりは出来そうですので、まずは挑戦します。

1年ありますので、また疑問が出た際にはこちらで相談をさせていただきたいと思います。

お礼日時：2021/01/07 16:20

No.5 回答者： enunokokoro 回答日時： 2021/01/05 14:53

平日のパターンは6週で繰り返すものであることは理解できますが、

祝祭日のパターンが入ると6週での繰り返しになるのでしょうか？

今年の場合だと、1月と2月にそれぞれ2回祝祭日があります。
曜日も違うので、祝祭日のパターンが入った場合には週3回勤務が
ある場合も起きうるということでしょうか？

それと、祝祭日のパターンにEACBEDFとEが2回ある理由は何？

日曜日は当番がないということで問題ないのですよね？

この回答へのお礼

ご回答ありがとうございます。

平日は7周で繰り返すようになっています。

日曜が抜けていました。日曜祝祭日がEACBEDFのローテーションです。申し訳ありません。

3日連続することもあります。

Eが2回ある理由はE本人の希望によります。
今後1回になることもありえます。

よろしくお願いいたします。

お礼日時：2021/01/07 16:42

No.4 回答者： tatsumaru77 回答日時： 2021/01/05 11:26

すみません。

ローテーションが、よくわかりません。
月~土がAABCDEFの場合
月A
火A
水B
木C
金D
土E
なのでFが余ってしまうように思えます。
具体的に今年の１月分の例を添付図の画像のようにしてアップしていただけませんでしょうか。黄色の部分があなたが記入すべき個所です。
画像の切り取りはアクセサリのsnipping toolを使うと簡単にできます。
この画像もsnipping toolを使っています。

No.3 回答者： goold-man 回答日時： 2021/01/05 09:39

当番表を自動作成する無料アプリもあるし、web上にあるシート（テンプレート当番表）を利用する手もあるよ。

No.2 回答者： gamedesign 回答日時： 2021/01/05 09:29

Aさんから見たら結局月曜日は何時から何時まで出ればいいのか

わかりにくいから

Aさん
月は●時〜●時まで
火は●時〜●時まで


Bさん
月は●時〜●時まで
火は●時〜●時まで


みたいに、人ごとにしてあげた方が
働く側としては助かるよ

No.1 回答者： lv4u 回答日時： 2021/01/05 09:29

＞＞例えば、1月1日がローテーションのどの場所かを指定すると自動で1年分が入力されるような表は作成可能でしょうか？

ヒント：
どういう手順、ルールで当番割り当てをするか？が明確になっていれば、それに従ってプログラムを作れば可能。

頑張ってください。