tatsu99様 VBA勤務管理表の業務割り振りプログラムについてご教示お願い致します

tatsu99様 昨日は「VBAシフト表における従業員の固定休のプログラムについて」の件ありがとうございました。
従業員の休みに対応したVBAプログラム、活用させていただいております。
昨日の今日で大変申し訳ないのですが、今回の質問は勤務管理表の休み以外のセルに、設定シートにある従業員の担当業務を勤務管理表シートに割り振りするプログラムについてご教示頂きたく質問致しました。

添付した画像を例に構築したいVBAプログラムをご説明させていただきます。
日にちは14日から一ヶ月とします。
上段の設定シートと下段の勤務管理表シートは同じExcelファイルになります。キャプチャする為に分割致しました。
設定シートの表は各従業員の担当業務になります。
それを勤務管理表の「休」以外のセルに優先1の業務をそれぞれの担当者に出力。
一日の業務には必ず「会議」、「事務」、「営業」を組み込みます。
14日を例としますと「営業」担当のCさんが休みの為、Eさんが「営業」担当になります。
17日は「会議」担当のBさんがお休みの為、Aさんが「会議」担当になります。
この様に「休」のセル以外に、担当常務を割り振るプログラムとその担当者が休みの場合に違う担当者が変わりを勤めるプログラムを作成したい内容になります。
また文字の色の条件を「サポート」は赤、「会議」は緑、「営業」は黄色、「事務」は水色と設定をしたいです。
勤務管理表の休みの関係で担当業務の「会議」、「営業」、「事務」を出力した際に重複があった場合はMsgBoxで「重複があります！」のメッセージを。また背景を赤で表示させたいです。

以上が私が構築したいプログラムの内容になります。

tatsu99様 よろしくお願い致します。

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

• tatsu99様、設定シートの各従業員担当業務の表の最新キャプチャ画像を添付致しましたのでこれを例にお礼からご説明致します。

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2017/05/15 12:05

No.8 回答者： tatsu99 回答日時： 2017/05/15 13:14

追加確認です。

「ヘルプ」に関しては、他の業務ができないので、「ヘルプ」を設けたとのことですので、
ヘルプが定義される場合は、優先１にヘルプを定義し、優先２から優先４は全て空白ということに
したいのですが、いかがでしょうか。
もし、その新人が、サポートとかの他の業務ができるようになれば、ヘルプを削除し、サポート等の業務を記述することになります。

この回答へのお礼

tatsu99様、要件の変更に関して誠に申し訳ございません。
この様なことを繰り返さない様に、以後、気をつけます。

１）提示例でＢさん１人体制のとき、割り当ては会議で間違いないですか。
　・はい、間違いありません。
２）Ｄさんが優先１＝事務、優先２＝会議で定義されているなら、
　　Ｄさん１人体制のとき、割り当ては、事務で間違いないですか。
　・はい、間違いありません。

一人体制、二人体制の時の業務の割り当てに「会議」、「事務」を優先の件ですが、
担当業務がない場合はそのまま入力なし、とする事が可能であれば以下の様にしたいです。
１）提示例でＣさん１人体制のとき、何の業務を割り当てるのか？
　　Cさん一人体制の場合は「会議」、「事務」の中の優先2の「会議」が出来るので「会議」。
２）提示例でＢさんとＣさんの２人体制のとき、ＢさんとＣさんは何の業務をわりあてるのか？
　　Bさんは「会議」、Cさんは「事務」が出来ないので何も入力無し。
３）提示例でＡさんとＣさんの２人体制のとき、ＡさんとＣさんは何の業務をわりあてるのか？
　　AさんはCさんには出来ない優先3にある「事務」が出来るので「事務」。Cさんは優先2の「会議」。
４）提示例でＥさんとＣさんの２人体制のとき、ＥさんとＣさんは何の業務をわりあてるのか？
　　Eさんはそのまま入力なしの、Cさんは「会議」。
上記の「会議」、「事務」に該当しない場合の処理をそのまま片方、無視するなどの処理は可能でしょうか？

はい、「ヘルプ」は優先1に定義し優先2から4は空白で問題ありません。

tatsu99様何か良い方法はありますでしょうか？

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

お礼日時：2017/05/15 13:52

No.7 回答者： tatsu99 回答日時： 2017/05/15 13:03

追加確認です。

１人体制で会議と事務は優先順位はないと考えてよいですか。
１）提示例でＢさん１人体制のとき、割り当ては会議で間違いないですか。
２）Ｄさんが優先１＝事務、優先２＝会議で定義されているなら、
　　Ｄさん１人体制のとき、割り当ては、事務で間違いないですか。

No.6 回答者： tatsu99 回答日時： 2017/05/15 12:35

要件が変わりすぎます。

・「ヘルプ」業務がいきなり出現している。
・１人体制では、会議か事務を割り当てるということだったが「ヘルプ」がある。
・各担当者は、サポート、営業、事務、会議のいずれもできるはずであるが、出来なくなっている。
等があります。
最初に要件を全て提示されることを望みます。今回は、やむなしとしますが、次回からの対応はなしと思ってください。

不明点は以下の通りです。
１人体制、２人体制で、会議、事務を優先とあるが、そのことに関してです。
１）提示例でＣさん１人体制のとき、何の業務を割り当てるのか？
２）提示例でＢさんとＣさんの２人体制のとき、ＢさんとＣさんは何の業務をわりあてるのか？
３）提示例でＡさんとＣさんの２人体制のとき、ＡさんとＣさんは何の業務をわりあてるのか？
４）提示例でＥさんとＣさんの２人体制のとき、ＥさんとＣさんは何の業務をわりあてるのか？

No.5 回答者： tatsu99 回答日時： 2017/05/15 09:42

確認の追加です。

確認５
専従要員の割り当ては、その専従要員の優先１の業務が必ず割り当てられる。他の業務をすることは一切ない。
但し、１人体制で、営業の専従要員がついたときは、会議か事務の何れかを割り当てる。
従って、専従要員間で業務の持ち回りのようなことはしない。
持ち回りをする場合は、次の月に、設定シートの優先１の業務を変えることにより行う。

確認６
サポート要員を会議、事務、営業の何れかに割り当てる場合は、設定シートの従業員名が上位（行番号の小さいほう）に
あるものから、割り当てを行う。提示された例では、Ａさん、Ｅさん、Ｆさんがサポート要員なので
Ａ→Ｅ→Ｆの順に割り当てる。
従ってＦさんが新人なら、「サポート」を行う確率が最も高くなる。
もし、新人を優先的に会議、事務、営業の何れかに割り当てたいなら、Ａさんの位置に新人を持ってくることが必要となる。
尚、サポート要員の割り当て順序は、設定シートの並びで決まり、勤務管理表シートの並びとは関係しない。
（設定シートの要員の並びと勤務管理表シートの並びは一致しなくても良い）

この回答へのお礼

各従業員の担当業務は設定シートの様に担当業務が出来る人、出来ない人がおります。
補足添付画像を例にBさんは「会議」、「事務」は出来るが「営業が」出来ない。
Cさんは「営業」、「会議」は出来るが「事務」が出来ない。
この設定シートを条件に勤務管理表に出力をしたいです。
例えば月曜日は全員出勤なので優先1からそのまま割り当て。
火曜日はBさんがお休みの為「会議」担当にAさん、CさんがおりますがCさんが優先1の「営業」の為Aさん。
水曜日は「営業」担当のCさんがお休みの為優先2からDさんが担当でAさんが「事務」。
木曜日はBさん、Cさんがお休みの為Aさんが「会議」、Dさんが「事務」。
金曜日はFさんが一人なのでそのままヘルプを表示。
マクロが空欄をエラーと認識してしまう為Fさんの業務を「ヘルプ」としました。

「最初に出現した業務を割り当てる。（優先１→優先４の順に検索）」
そうです！
各担当従業員の優先順位が高い業務から配置をするという事になります。
よろしくお願い致します。

お礼日時：2017/05/15 12:20

No.4 回答者： tatsu99 回答日時： 2017/05/15 08:00

すみません。

確認４
⑥６人体制の場合
　３人体制と同様に決定し、残りの３人にサポートを割り当てる。

上記がもれてました。追加します。

No.3 回答者： tatsu99 回答日時： 2017/05/15 07:52

すみません。

確認４
④４人体制の場合
　３人体制と同様に決定し、残りの１人にサポートを割り当てる。

上記の④が２つありますが、１つは余分ですので、無視してください。

No.2 回答者： tatsu99 回答日時： 2017/05/15 07:48

確認１

>・一人、二人態勢の場合は「会議」、「営業」、「事務」の業務の中の「会議」と「事務」だけは優先順位が高い担当者で入力をしたいです。

では、以下の仕様でよろしいでしょうか。
１人体制の場合、会議、又は、事務の何れかを割り当てる。（どちらが割り当てられるかは事前には判らない）
２人体制の場合、会議、事務を割り当てる。

確認２
>※ただ従業員の研修期間で業務を担当出来ない方がいるのですが、設定表の担当業務に空欄がある箇所がある場合その担当者はそのまま空欄を出力させたいです。
>例としBさんの優先4の担当の「サポート」が空欄ならそのまま空欄など。

これは、要件として不適切です。
Ｂさんが、「会議」、「営業」、「事務」、空白と定義されても、
何日が研修日かの情報がないため、空白を何日の箇所に設定するのかが判りません。
研修期間で、当該業務を担当できない場合は、「研修」の文字を予め、いれておくことはできませんでしょうか。
例えば、Ｂさんが研修日が５月１４，１５，１６日なら、１５，１６，１７日へ「研修」の文字を設定しておきます。

マクロでは、空白の箇所のみを選んで、（つまり、「休」と「研修」）を除いて、その箇所へ業務を割り当てます。
運用上は、研修でも出張でもなんでもかまいません。そのセルが空白でなければ、割り当て不能とマクロは認識します。
従って該当日の空白の数が人数の体制になります。

確認３
担当者（Ａさん～Ｆさん）の設定シートの業務の割り当ては、以下の仕様で行いますが宜しいでしょうか。
①優先１の場合、営業＝１人、会議＝１人、事務＝１人、サポート＝３人が割り当てられていること。
（以降、営業、会議、事務に割り当てられた要因を専従要員、サポートに割り当てられた要因をサポート要員と呼ぶ。
あなたの提示例では、Ｂさん，Ｃさん，Ｄさんが専従要員であり、以外の人がサポート要員となる。）
②各担当者は、優先１～優先４の割り当てで、営業、事務、会議、サポートが必ず１つずつ割り当てられていること。
例　Ａさんに　サポート、営業、事務、事務
　　Ｂさんに　会議、営業、事務、空白
のような割り当てはエラーとなる。

確認４
割り当ては、要員を機会均等に割り当てるのではなく、最も優先順位の高い順に割り当てるようにしますが宜しいでしょうか。
①１人体制の場合、会議と事務の何れかを割り当て
　会議又は事務の専従要員なら、その業務を割り当てる。
　上記以外の要員なら、会議、事務が最初に出現した業務を割り当てる。（優先１→優先４の順に検索）
②２人体制の場合、会議と事務を割り当て
　２人とも会議又は事務の専従要員なら、その業務を割り当てる。
　１人が会議又は事務の専従要員なら、その業務を割り当てる。残りは、残りの業務を割り当てる。
　２人とも会議又は事務の専従要員でないなら、会議、事務が最初に出現した業務を割り当てる。（優先１→優先４の順に検索）
③３人体制の場合、会議、事務、営業を割り当て
　３人とも専従要員ならその業務を割り当てる。
　２人が専従要員ならその業務を割り当て、あとの１人は残りの業務を割り当てる。
　１人が専従要員ならその業務を割り当て、あとの２人は残りの業務が最初に出現した業務を割り当てる。（優先１→優先４の順に検索）
④４人体制の場合
　３人体制と同様に決定し、残りの１人にサポートを割り当てる。
④４人体制の場合
　３人体制と同様に決定し、残りの１人にサポートを割り当てる。
⑤５人体制の場合
　３人体制と同様に決定し、残りの２人にサポートを割り当てる。

No.1 回答者： tatsu99 回答日時： 2017/05/14 11:20

前回、

https://oshiete.goo.ne.jp/qa/9751595.html
で、回答したソースに１点、誤りがありました。
wk = dicT(key)
For i = 1 To 31
col = 2 + i　・・・・①
If sh2.Cells(2, col).Value = "" Then Exit For

①の箇所ですが、
C列から判定を開始するので、B列に休みが設定されることはありません。
col = 1 + i
が正しいです。お詫びして、訂正します。

ここからが本題ですが、
１）人数は５人限定になりますが宜しいでしょうか。
２）休みの設定は、別マクロで実施の前提なると、ありうる人数の体制は
１人・・・エラー
２人・・・エラー
３人
４人
５人
になります。
１人、２人の場合は、「会議」、「営業」、「事務」が確保できないので、エラーにしますが宜しいですか。
３）「会議」、「営業」、「事務」で重複の場合、赤表示とのことですが、
そもそも、これはあり得ないのではないでしょうか。
これが、発生する可能性があるのは、４人、５人の場合です。
（３人は会議、営業、事務を１人ずつ行う為、重複しない）
Aさん～Eさんは、優先順位はありますが、「会議」、「営業」、「事務」、「サポート」の何れかを選択することができます。
そうすると、「会議」、「営業」、「事務」の割り当て後、残りの人を強制的にサポートにすれば良いはずです。
あなたが、想定されている、重複ケースはどのような場合でしょうか。
（Aさん～Eさんの誰かが「サポート」の割り当てを持たないなら話は別ですが）

この回答へのお礼

tatsu99様お世話になっております。訂正の件ありがとうございます。

本題の件についてご説明させていただきます。
・人数の限定は新人さんが一人増える予定なので「Fさん」を追加した6人限定でお願い致します。
・一人、二人態勢の場合は「会議」、「営業」、「事務」の業務の中の「会議」と「事務」だけは優先順位が高い担当者で入力をしたいです。
・重複の場合の赤表示はtatsu99様が仰る通り4、5人の場合になりますが、3業務を割り当て後、残りの担当者を強制的に「サポート」にする方法でお願い致します！
※ただ従業員の研修期間で業務を担当出来ない方がいるのですが、設定表の担当業務に空欄がある箇所がある場合その担当者はそのまま空欄を出力させたいです。
例としBさんの優先4の担当の「サポート」が空欄ならそのまま空欄など。

以上になります。
　　　　　　　　　　　　　　　　　　　　
よろしくお願い致します。

お礼日時：2017/05/14 14:11