エクセルで勤務表に作成したい

図のようなAからJまでの10人のシフト表を作成しています。
現在、E2に数字を入れることで、カレンダーと同期した休み表記まではできました。
右の表はAからJまでが1から8番までのどのシフトができるのかを表したものです。
左の休みだけの表の空きに1から8番までのシフト番号を入れたいのですがうまくできません。
どのような関数を使って、E2に数字を入れることで休みとシフト番号を表記できるのでしょうか？

条件としては曜日で1から8番までが重複しないこと。
できれば連続した番号が続かないこと。
あとはなるべく10人が平均的に1から8番なるようにしたいです。
どうしても1ヶ月で偏りができてしまうなら、3,6,12ヶ月などの期間で平均的になるようにしたいです。

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

• 説明不足で申し訳ありません。
  新しい図で説明させていただきます。
  E2はセルです。
  表2はAからJの休日と1から8までのどれを担当できるか、
  表3は各曜日ごとの行われるシフト番号です。
  
  担当シフト番号は重複できないので、当日の出勤人数で割り振ります。
  均等というのは、自分が担当してるシフト番号を偏りなくするという意味です。
  例えば、Iさんは1から8番までのシフトをこなすことができます。
  1番だけ多くて2番が極端に少ないということは避けたいのです。
  Gさんは担当できるシフト番号が10人の中で1番少なく2.4.5.7番の4つしかできません。
  当然他の人より2.4.5.7番は多くなると思います。
  
  VBAでしか解決できないのなら諦めますが。
  全自動というのは難しいなら段階を踏んで関数で出来ないものでしょうか？

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2019/07/24 01:18

• 7/1-8までのシフトを表1に入れました。
  多少補足させていただきます。
  
  ①１～８が担当で、それぞれの日に全ての担当が出勤していること
  A～Jの公休日は固定ですので、月曜日なら公休日のA,F,Jの3人を除くB,C,D,E,G,H,Iの7人が出勤になります。
  
  ②表２はそれぞれの担当者が対応可能な担当分類の表である
  Aなら1,2,3,4,5,6　Gなら2,4,5,7
  
  ③一日の出勤者が８人に満たない場合には重複作業が伴う
  例えば日曜日なら出勤者はA,F,G,H,I,Jの6人で、作業シフト番号は、1,2,3,4,6,7の6箇所です。
  6人の出勤者に対して、6箇所のシフト番号をこなすということになります。
  ですので、1一人出勤者が例えば、1,2番をやるということはありません。
  必ず誰か一人はシフト番号を出来るような調整をして出勤しております。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2019/07/25 15:31

• 偏りについてですが、7/1-8までのシフトを見ると、
  Aさんの場合、担当できるシフトが1,2,3,4,5,6に対してこの週は1,2,3,4,6となっており
  シフト番号5を行っていません。
  ですので、翌週に5番を入れるなのど月次で見た場合に平均的にどのシフト番号をこなしているということです。
  
  やはりかなり難しそうなので、例えば予め去年1年間をシフト表を別シートに作成して、
  そこから何かしら関数を使って表1にように表示することは可能でしょうか？
  
  セルE2にその月の数字を入力することで、休みもシフト番号も表示されるような。
  
  色々分かりづらい説明しかできなくて申し訳ありません。

    補足日時：2019/07/25 15:33

• 無理難題に協力していただき恐縮しております。
  前回のサンプルでのＣさんが５日に出勤は入力ミスでした。
  申し訳ありませんでした。
  新しく6月のシフトを入力したものと、表2のAが間違っていたので修正しました。
  表4は2018年1月から12月までのA～Jまで10人のシフト1～8までの総数を表したものです。
  これを見ると、シフト2.4.7以外はほぼ偏りがないよう見思われます。
  rukaandkaitoさんが指摘されているC,Gが問題のようです。
  これは対応シフトが他より少ないために起こっているものだと思います。
  祝日は全く無視で大丈夫です。
  やはり制約が多すぎて平均化は難しそうですね。
  ここまで色々考えてくださって頂いて感謝しております。
  提案していただいた振り分けデータのパターン化でご指導お願いできないでしょうか？

  
  No.6の回答に寄せられた補足コメントです。 補足日時：2019/07/29 20:08

• rukaandkaito様　有難うございます。
  早速手順通りやっているのですがうまくいきません。
  いくつか質問させていただきます。
  パターン表はとりあえず6月分を作成しました。
  
  ①勤務表に関してですが、私の書式では『=WEEKDAY(D$6)』ですが、
  　頂いた条件式で(D$7)になっていますが、これはあえて変更しているのでしょうか？
  
  ②E5『=IF(WEEKDAY(E7)=1,D5+1,D5)』を入力するとエラー表示になります。
  
  とりあえず指示通りにすべて入力したのですが、反映されたのはD列だけです。
  
  お手数おかけして申し訳ありません。

  No.7の回答に寄せられた補足コメントです。 補足日時：2019/07/30 17:27

No.9 ベストアンサー 回答者： rukaandkaito 回答日時： 2019/07/30 17:36

再度失礼します

こちらで作成したファイルをＵＰしますのでご確認ください
https://13.gigafile.nu/0806-b92cb0ad42f7114cfa7a …
ダウンロード期限は１週間です

この回答へのお礼

何から何まで本当に有難うございました。
頂いたファイルを使わせていただきます。
本当に助かりました。

お礼日時：2019/07/30 17:43

No.8 回答者： rukaandkaito 回答日時： 2019/07/30 17:33

No.7です

①どちらでも問題ありませんよ
②もしかしてですがE7の関数は『=E6』ではなかったりしますか？
前回の説明でその部分が抜けていたのでもしかしてと思いました！
曜日欄は全て上位のセル（６行目）を参照しているだけになっています

No.7 回答者： rukaandkaito 回答日時： 2019/07/30 11:36

No.6です

お待たせしました！
では手順を説明します
Ⅰ．まずパターン表を作成します
　１．新規シートA列には任意で名前を付けて下さい(なんでも構いません)
　２．B列～H列がそれぞれ曜日に対応していますので横７(日～土)、縦１０(従業員数)です
　３．パターンはいくつあってもかまいませんが、今回はサンプルとして４週分用意しました
　４．サンプルはいただいた６月分のシフトをそのまま入れています
Ⅱ．勤務表を作成します
　１．いただいているフォーマットをそのまま使用しています
　２．表の条件付き書式
　　①D6～AG17『=WEEKDAY(D$7)=7』背景色水色
　　②D6～AG17『=WEEKDAY(D$7)=1』背景色薄赤
　　③D6～AG7 『=WEEKDAY(D$7)=7』前景色青色
　　④D6～AG7 『=WEEKDAY(D$7)=1』前景色赤色
　　⑤D8～AG17『="休"』前景色赤色
　　※条件停止は全て外す
　３．それぞれの関数を記載します
　　①D5は入力欄でパターンの開始番号を入力します
　　②E5『=IF(WEEKDAY(E7)=1,D5+1,D5)』
　　③F5以降はE5をフィルしてください
　　④D6『=DATEVALUE(C4&"/"&E4&"/"&1)』：表示形式『DD』
　　⑤D7『=D6』：表示形式『AAA』
　　⑥E6『＝D6＋１』以降右方向にフィル
　　⑦D8『=INDEX(パターン表!$B:$H,勤務表!D$5*10-10+勤務表!$A8,WEEKDAY(勤務表!D$7))』
以上となります。表内は全てD８をフィルすれば可能です
また５行目が目障りなので非表示や前景色白にて対応下さい

使い方：
D5にパターン開始番号を入れます。あとは自動的にシフトが設定されますが、日曜日の度にパターンが次へと変更されますので、次月開始曜日が日曜で無い場合には、先月の最終パターンを指定してください。
不明な点等あればまたご返信ください
画像が小さかったらゴメンナサイ(笑)

No.6 回答者： rukaandkaito 回答日時： 2019/07/29 09:22

No.5です

制約が多く難航しています。
サンプルでいただいている表１に関してＣさんが５日に出勤されているのは意図してでしょうか？
難航している理由をいくつか挙げてみます
Ｃさんが対応可能なシフトが『２４５６８』ですが『５』は火金のみのため必然的に火曜日のみとなります。のこり４つのシフトで月木土を行うにあたり、『８』は月～金のため土は『２４６』のいずれかとなり更にシフト重複をしない様に、また、シフトの平均化がされるようにという要望ですので、パターンが限られてきます。
Ｇさんに至っては更に対応シフトが減り『２４５７』となっており、また休日の関係上やはりこちらも『５』は金曜日のみとなります。１０人で『５』のシフトを均等化するとすると年間５２週で１０４回。つまりひと月辺り１回弱ということになりますので、４～５週に一度になるとＣ、Ｇのシフトはかなり制限されてしまいます。
上記を踏まえた上で表を優先して埋めていくと次に『８』が問題になります。ＢＦＨＩの４名で月～金の穴を埋めなければならないためシフトが偏ります。
また、オールラウンダーのＩさんに続きＨＪの３名が必然的に他者の穴埋めになってしまい、不規則なシフトしか組めません。
NO.5で最小公倍数という話をしていましたので計算しましたが、対応可能シフトの数が４、６～８となっていますので１６８となり、一番少ないＩさんで２１回、最も多いＧさんで４２回、週計算で約３４週～４２週・・・ほぼ一年分のシフトを作成してそれをループさせる必要がありそうです。現実的ではないですねぇ～
いっそ過去の振り分けデータがあるのであれば、それをパターン化して代入していけばいかがでしょうか？
休みのパターンが決まっているのであれば週単位で貼り付け可能だと思いますが、祝日の扱いはどうなっているのでしょうか？全て無視で良いのでしょうか？
ご検討ください

No.5 回答者： rukaandkaito 回答日時： 2019/07/25 16:48

No.3、４です

なるほど理解できました。
要は『ナンクロ』ですね(笑)

簡単に出来る方法を検討してみます！
休日のパターンが決まっていて、シフトのパターンも決まっているので、基本的には週単位の繰り返しになります。
何パターンで一周するかは試してみないと分かりませんが、それぞれの最小公倍数を求めればよいですね。
上記法則でいくと、特定の周期で同じ組み合わせが繰り返し行われることにはなりますが、可能と言えば可能ですね
ここまでのルールで問題なければパターン表を事前に作成して、週毎に埋めていくだけでいける気がします。
パターン作成試してみますので、もうしばらくお待ちください。

No.4 回答者： rukaandkaito 回答日時： 2019/07/25 11:04

No.3です

少し質問内容を読み違えていました。
担当ではなくシフトという事ですね(-_-;)
出来ればシフトが埋まっている状態の表も参考として拝見できれば検討しやすいかと考えます。

No.3 回答者： rukaandkaito 回答日時： 2019/07/25 10:53

①１～８が担当で、それぞれの日に全ての担当が出勤していること

②表２はそれぞれの担当者が対応可能な担当分類の表である
③一日の出勤者が８人に満たない場合には重複作業が伴う
まず③について重複可能な担当の組み合わせも必要かと考えます。
上記を踏まえて例えばＡさんの場合だと、水～日の出勤日に対して１～６の担当を振り分ける。それによる月次の担当がなるべく偏らないようにしたい。
という解釈で宜しいかと考えます。

関数のみでとなるとかなり複雑になりますので、ＶＢＡの案件かと思います。
今回の案件で問題となる部分は上記③についてと、③は①にも掛かってきますが、それぞれの日で担当全てを網羅し、且つ担当が偏らないようにしなければならないため、関数のみで行うためには複雑な論理の組み立てが必要になると思います。その場合にはやはり作業用のシートが必要になるでしょう。
不可能とはいいませんが、かなり高度な処理分割が必要で、加えて乱数などによる振り分けの際に担当重複等が発生した場合に再起処理が必要となるため、やはりＶＢＡに頼るべきではないでしょうか。

No.2 回答者： lv4u 回答日時： 2019/07/23 23:14

まあ、関数だけでは無理でしょう。

もうエクセルＶＢＡでプログラムで解決する課題だと思います。
プログラムを勉強しましょう。

No.1 回答者： housyasei-usagi 回答日時： 2019/07/23 23:07

御質問を理解できません。

E2ってなんじゃ？隠れているセル?
均等にするなら、同じパターンをシフトすればいいと思うが，条件不明なのでなんとにねえ・・・