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

Question

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

tatsu99 · Accepted Answer

下記URLに記述しました。
http://climbi.com/b/10180/0
標準モジュールに登録してください。他のマクロとはべつのモジュールに登録してください。

設定シート記入時の条件です。
１）優先1で業務の重複不可
２）優先1＝ヘルプの場合、優先2～優先4は空白扱いとする。（設定しても無視する）
３）優先1＝サポートの場合、優先2～優先4迄、全て設定してあることが必須。
４）優先1＝会議、又は事務又は営業の場合（以降専従要員とする）
　　優先2～優先4は設定してもしなくても良い。
　　（優先Nが空白の場合、以降の優先の指定は無視する）

業務を割り付けるときの手順です。
空白の欄が割りつけ可能なセルになります。このセルに「休」などの文字が設定されている場合は、業務を割りつけません。
「休」以外の文字も割り付け不能なセルとして扱います。
１）1人出勤の場合、
　会議、事務の何れかが割り付け可能なら、優先順位に従い、どちらかを割り付ける。
　上記以外の場合は、空白を割り付ける。
２）2人出勤の場合
　①会議及び事務の割り付けが可能なら、以下の方法に従う。
　　一方がどちらかの業務しか割り当てられない場合は、その業務を割り当て、他方に別の業務を割り当てる。
　　両方がどちらの業務も割り当てられるなら、高い方の会議の優先順位を持つものに会議を割り当て、他方に事務を割り当てる。
　　会議の優先が両方同じなら、高い方の事務の優先順位を持つものに事務を割り当て、他方に会議を割り当てる。
　②上記①ができない場合は、以下の処理をする。
　　一方が会議も事務も割り付け不能なら空白とし、残りの他方に１人出勤のケースを適用する。
　（他方は会議、事務、空白の何れかになる）
　　上記以外は、以下の処理をする。（両方会議のみ割り当て可能、又は両方事務のみ割り当て可能）
　　割り当て可能な業務について高い方の優先順位を持つ方にその業務を割り当て、他方は空白とする。
３）3人出勤の場合
　①１人がヘルプの場合は、その人に空白を割り当てる。残りの２名については2人出勤のケースを適用する。
　　上記で終了
　②専従要員3人の場合、優先1の業務を3人に割り当てる。
　　上記で終了
　③専従要員2人、サポート要員1の場合
　　専従要員2人に優先1の業務を割り当て、残りの業務をサポート要員に割り当てる。
　　上記で終了。
４）4人出勤の場合
　①1人がヘルプの場合は、その人にヘルプを割り当てる。残りの３人は、3人出勤のケースを適用する。
　　上記で終了
　②上記以外（専従員3人、サポート要員1人の場合）
　　専従要員3人の場合、優先1の業務を3人に割り当てる。サポート要員にはサポートを割り当てる。
　　上記で終了
５）5人出勤の場合
　①専従要員3人に優先1の業務を割り当てる。サポート要員にはサポートを割り当てる。ヘルプ要員にはヘルプを割り当てる。

不具合があれば、連絡ください。できるだけ対応します。但し、仕様の変更及び追加についてはご遠慮ください。
今回は対応しましたが、今後は私宛に質問＆依頼をされましても回答できる保証はありませんのでご了承ください。

tatsu99 · Answer

5人体制にすることにより、かなり、簡単になりましたが、それでもまだ複雑な部分があります。
サポート担当者には、優先２～優先４に全て設定を行う予定でしょうか。（従って、サポート担当者はどの業務も割り当て可能）
もしそうであれば、更に、割り当て方法が簡単になり、更に納期が短縮できますがいかがでしょうか。
優先２～優先４に、会議、事務、営業を設定するときの、優先順位は特に問いません。

tatsu99 · Answer

>ではtatsu99様、優先1で業務の重複なし「サポート」、「ヘルプ」も重複なしの5人体制でのプログラム作成をお願い致します。
>人件費の問題もありますので5人体制での勤務管理表に致します。

承知いたしました。
念のため、確認しますが、優先１で「ヘルプ」となった人は、他の業務は担当できません。
優先２～優先４は、必ず空白となります。
従って、ヘルプ以外の業務割り当てきません。割り当て結果は、「ヘルプ」か空白の何れかになります。
宜しいでしょうか。

No12で確認の回答をいただいてないものがありますので、再確認します。

あなたが提示された出勤者３名の場合の要件です。
>②ここで重ね条件があり出勤者3名の日の条件は「会議」、「営業」、「事務」を優先番号高い者から割り振る。それ以外は空欄処理。
>例：No5の添付画像を例にします。Aさん、Bさん、Cさんが出勤の場合はBさん優先1の「会議」Cさん優先1の「営業」Aさんは残りの優先3の「事務」を割り当てる。
>例：Aさん、Cさん、Dさんが出勤の場合はCさん優先1の「営業」Dさん優先1の「事務」Aさんは残りの「会議」を割り当て。
>例：Bさん、Dさん、Eさんの出勤の場合はBさん優先1の「会議」Dさん優先1の「事務」Eさんは営業がないので空欄処理。

上記の要件にあてはめると、
例として、Ｃさん、Ｄさん、Ｆさん出勤の場合は、
Ｃさん優先１の「営業」、Ｄさん優先１の「事務」、Ｆさんは「会議」の割り当て不能なので空白。・・・・①
となります。
それとも、このケースは、実質的には、２人出勤のケースになるので、「会議」と「事務」を割り当てることが優先されるので、
Ｃさん優先２の「会議」、Ｄさん優先１の「事務」、Ｆさんは「営業」の割り当て不能なので空白。・・・・②

私が確認したかったのは、①なのか②なのかということです。
あなたが提示された出勤者２名の場合の要件に従うと、「会議」と「事務」が必須で以外は空白です。
Ｃさん、Ｄさんの2人出勤の場合は、
Ｃさん優先２の会議、Ｄさん優先１の「事務」・・・③
となります。

Ｃさん、Ｄさんが２名出勤の状態（③の割り当て）で、役に立たないＦさんが出勤すると、①の割り当てと
なってしまうのはいかがなものかと。

tatsu99 · Answer

ちなみに、優先１で業務の重複なし（サポート、ヘルプも重複なし）なら、５人体制となります。
この条件で良いなら、格段に業務の割り付け方法が簡単になります。
現在、マクロで作成で悩んでいる原因は、優先１にサポートが2名（将来的には３名）になる為です。

tatsu99 · Answer

>設定表の従業員の優先1においては重複がおきない様に設定致します。
これが、適用されるのは、会議、事務、営業の３つのみではないでしょうか。
サポート、ヘルプも重複させないなら、６人体制はあり得ません。

No13の例はサポートが２人の場合です。

tatsu99 · Answer

現在、わかっている範囲で不明なのは、３人体制の以下のケースです。
>ここはNo5の補足の添付画像から
>Aさん 優先1=サポート、優先2=会議、優先3=事務、優先4=営業
>Bさん 優先1=会議、優先2=事務
>Cさん 優先1=営業、優先2=会議
になります。 
>Aさん、Bさん、Cさんの場合は３名出勤の条件の3業務を割り振るので、Bさん「事務」、Cさん「会議」、Aさん「営業」にそれぞれ割り振るというものになります。
>Aさん、Bさんの2名の場合は2名出勤の条件の2業務を割り当てるので、Bさん優先2「会議」、Aさん優先3「事務」になります。

私が確認したいのは、添付図の状態ではなくて、設定シート上で
Ａさん　優先１＝サポート、優先２＝営業、優先３＝事務、優先４＝会議
Ｂさん　優先１＝サポート、優先２＝営業、優先３＝事務、優先４＝会議
Ｃさん　優先１＝ヘルプ
と定義されていた場合で、
Aさん、Bさん、Cさんの3人体制の場合、どうなるのかということです。
（設定シートに上記のような設定はあり得るはずです）

添付図にできるだけ沿った例ならば、（Ａさんは優先順位は変更、Ｅさんは業務が追加されています）
Ａさん　優先１＝サポート、優先２＝営業、優先３＝事務、優先４＝会議
Ｅさん　優先１＝サポート、優先２＝営業、優先３＝事務、優先４＝会議
Ｆさん　優先１＝ヘルプ
が定義されていて
Aさん、Ｅさん、Ｆさんので3人体制のケースです。
（このような設定は実際には行わないかもしれませんが、あり得るはずですのマクロ作成時は考慮しておく必要があります）

tatsu99 · Answer

ほぼ要件は確定したかと思いますので、検討に入りますが、難航しそうです。
現在、わかっている範囲で難航しそうなのは、「最適の業務を優先順位に従い割り付ける方法」です。
これは、割り付け可能なパターンが複数あるとき、どのパターンを採用するかを決める方法です。
例えば、提示例での２人体制でＡさん、Ｂさんの場合、
①Ａさん＝事務、Ｂさん＝会議
②Ａさん＝会議、Ｂさん＝事務
の２つが考えられますが、望まれているのは①と考えられる為です。（Ｂさんの優先１が会議の為）
①②のいずれでもよいなら、多少は容易になりますが、①を期待する場合は、十分検討する必要があります。

又、提示例では、Ｅさんはサポートのみ、Ｆさんはヘルプのみですが、将来的には、Ａさんのように全ての業務が可能になる
ことを想定してマクロを作成する必要があります。そのような時、「最適の業務を優先順位に従い割り付ける方法」
をきちんと確定しておかないと、「割り付けは間違ってはいないが、期待する割り付けになっていなかった」となる可能性があります。
よって、この割り付けの方法について、十分検討したいので、最大１週間ほど、時間をいただけませんでしょうか。
1週間後に完全に期待する割り付け結果を出力するマクロを提示できる保証はありませんが、検討します。
もし、多少の妥協（スペックダウン）が必要になる場合とか不明点がでた場合、その都度、ご連絡します。

現在、わかっている範囲で不明なのは、３人体制の以下のケースです。
Ａさん　優先１＝サポート、優先２＝営業、優先３＝事務、優先４＝会議
Ｂさん　優先１＝サポート、優先２＝営業、優先３＝事務、優先４＝会議
Ｃさん　優先１＝ヘルプ
上記の３人体制の場合、
Ｃさん＝空白は、決定できますが、
Ａさん、Ｂさんは何を割り当てるのでしょうか？
①Ａさん＝営業、Ｂさん＝事務
②Ａさん＝事務、Ｂさん＝会議
①、②のどちらでしょうか。
要は3人体制で1人が空白の場合は、残りの2人を2人体制の場合のルールを適用するか否かということです。（①なら適用しない、②なら適用する）
（２人体制を適用する場合は、残りの2人は営業を割り当ててはいけない）

又、３人体制で２人が空白の場合は、残りの1人を１人体制のルールを適用して良いでしょうか。

tatsu99 · Answer

>4名以上出勤の場合は「会議」、「事務」と「会議」、「事務」、「営業」が割り当てられている場合に限りEさんの「サポート」Fさんの「ヘルプ」が出力される処理。それ以外は空欄処理。

これは、以下の意味と理解しました。
①「会議」と「事務」が割り当て済みなら、「サポート」、「ヘルプ」を出力する。
②「会議」と「事務」と「営業」が割り当て済みなら、「サポート」、「ヘルプ」を出力する。

そうすると、
>Bさん、Ｄさん、Ｅさん、Ｆさんの場合
>Bさん優先1の「会議」Dさん優先1の「事務」Eさん「空欄」Fさん「空欄」処理です。
とありますが、①のケースに該当するので
Bさん優先1の「会議」Dさん優先1の「事務」Eさん「サポート」Fさん「ヘルプ」処理ではないでしょうか？

それとも①は誤りで、②のケースのみが「サポート」「ヘルプ」出力の条件でしょうか。

もし、①も「サポート」「ヘルプ」出力の条件であるなら、３人体制の場合の条件と矛盾するので、
その場合も問題があります。

tatsu99 · Answer

>空白処理の定義ですが、私が考えたものになりますが設定シート表のG2セルに「条件」とタイトルをつけ、G3セルに「会議」、
>G4セルに「事務」と入力しこのセルに担当者の優先順位から該当しないものは空白処理という事は可能でしょうか？

「会議」と「事務」を外出しにする理由は何でしょうか？
そうするとマクロとしては、Ｇ３セルに「ヘルプ」、Ｇ４セルに「サポート」が定義された場合、どうなるのか、
Ｇ３セルのみ定義され、Ｇ４セルが空白ならどうなるのか等のケースも考慮する必要があります。
このようなケースを含めてまで、要件定義をする必要があるのでしょうか。
又、「会議」と「事務」を外出しにすると、３人体制では、「会議」と「事務」と「営業」が必須であるという前提で、
「営業」のみ内部で抱え込みになってしまいます。
又、「ヘルプ」についても優先１にみ定義可能というような、特別な意味を持たせており、この情報も内部で抱え込みです。
その為、一方で内部抱え込みの情報をもち、他方で一部を外出しにすると、整合性が保障されなくなります。
これについては、外出しでなく「会議」と「事務」固定にしていただけませんでしょうか。

現在まで、私が記憶している業務固有の制約については、以下の点ですが、齟齬があれば指摘ください。
①「ヘルプ」は、優先１にのみ定義可能。そして、その場合、優先２～４に他の業務を定義した場合はエラーとする。
②「会議」と「事務」と「営業」は、優先１に１つだけ定義可能。また、各々１つが定義されていない場合は、エラーとする。
　例　Ａさん＝「会議」、Ｂさん＝「事務」、Ｃさん＝「サポート」、Ｄさん＝「サポート」、Ｅさん＝「サポート」は、エラー。（営業がない為）
　例　Ａさん＝「会議」、Ｂさん＝「事務」、Ｃさん＝「営業」、Ｄさん＝「営業」、Ｅさん＝「サポート」は、エラー。（営業２つの為）
③「ヘルプ」と「サポート」は優先１に複数回定義可能。０回でもＯＫとする。（以下の意味です）
　優先１の例
　Ａ＝会議、Ｂ＝事務、Ｃ＝営業のとき、Ｄ＝サポート、Ｅ＝サポート、Ｆ＝サポートはＯＫ。
　Ａ＝会議、Ｂ＝事務、Ｃ＝営業のとき、Ｄ＝ヘルプ、Ｅ＝ヘルプ、Ｆ＝ヘルプはＯＫ。
④１人体制では、担当者を「会議」又は「事務」に割り当て不能の場合、空白とする。
⑤２人体制では、担当者を「会議」及び「事務」に割り当て不能の場合、空白とする。（２つとも揃わないと空白）
⑥３人体制では、担当者を「会議」及び「事務」及び「営業」に割り当て不能の場合、空白とする。（３つが揃わないと空白）
　それとも、３人体制でも、「会議」及び「事務」に割り当てができれば、残りは「サポート」又は「ヘルプ」でも良いのでしょうか。
　そうなると、最初に提示された『一日の業務には必ず「会議」、「事務」、「営業」を組み込みます。』の話は、亡くなったのでしょうか。

⑦４人体制でも、担当者を「会議」又は「事務」「営業」に割り当て不能の場合、空白とする。
　例として、Ｂさん、Ｄさん、Ｅさん、Ｆさんの場合、
　Ｅさんはサポートのみなので空白、Ｆさんもヘルプのみなので空白となる。
　それとも、４人体制でも、「会議」及び「事務」に割り当てができれば、残りは「サポート」「ヘルプ」でも良いのでしょうか。
⑧５人体制も⑦と同様。
　（提示例でＡさんをサポートだけ定義し、Ａさん、Ｃさん、Ｄさん、Ｅさん、Ｆさん体制のケース）（会議、事務、営業の３業務は割り当て不能）

又、念の為、確認ですが、設定シートに定義可能な業務は、
「会議」、「事務」、「営業」、「サポート」、「ヘルプ」の５業務のみを想定しています。
任意の適当な文字、例えば「待機」の業務を設定シートに定義すれば、勤務管理表に「待機」が表示されるようなことは考えていません。
もし、このようなことを望まれるなら、今までの確認事項を全て破棄して、最初から要件定義に戻る必要があります。

tatsu99 · Answer

返信ありがとうございました。
返信で不明な点ついて以下に述べます。

>２）提示例でＢさんとＣさんの２人体制のとき、ＢさんとＣさんは何の業務をわりあてるのか？
>　　Bさんは「会議」、Cさんは「事務」が出来ないので何も入力無し。

念のため確認ですが、Ｂさんは「事務」、Ｃさんは「会議」にはしなくて良いのですね。

>４）提示例でＥさんとＣさんの２人体制のとき、ＥさんとＣさんは何の業務をわりあてるのか？
>　　Eさんはそのまま入力なしの、Cさんは「会議」。
	
Ｅさんはサポートではないのでしょうか？

>上記の「会議」、「事務」に該当しない場合の処理をそのまま片方、無視するなどの処理は可能でしょうか？
これは、空白出力（入力無し）と理解しましたが、その要件がきちんと定義できれば、可能です。

空白（入力無し）を出力する場合の要件ですが、判らなくなりました。
①Ｅさん１人体制は、サポートではなく、空白なのですか。
Ｆさん１人体制は、ヘルプになっていますが、それなら、Ｆさん１人も空白にならないと矛盾しませんか。
それともヘルプ業務だけの人は、一切他の条件を無視して、「ヘルプ」割り当てですか？

②２人体制で、ＥさんとＦさんの場合も、同様です。
　Ｅさん、「空白」で、Ｆさん「ヘルプ」でしょうか。

③３人体制で、Ｃさん、Ｅさん、Ｆさんの場合は、
Ｃさんは「会議」、Ｅさんは空白、Ｆさんは「ヘルプ」ですか。

あなたが考えている、空白出力（入力無し）の要件をきちんと提示していただけると非常にありがたいのですが。
その人が空白（入力無し）になる条件を全て網羅して、いただけませんでしょうか。

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

下記URLに記述しました。

5人体制にすることにより、かなり、簡単になりましたが、それでもまだ複雑な部分があります。

>ではtatsu99様、優先1で業務の重複なし「サポート」、「ヘルプ」も重複なしの5人体制でのプログラム作成をお願い致します。

ちなみに、優先１で業務の重複なし（サポート、ヘルプも重複なし）なら、５人体制となります。

>設定表の従業員の優先1においては重複がおきない様に設定致します。

現在、わかっている範囲で不明なのは、３人体制の以下のケースです。

ほぼ要件は確定したかと思いますので、検討に入りますが、難航しそうです。

>4名以上出勤の場合は「会議」、「事務」と「会議」、「事務」、「営業」が割り当てられている場合に限りEさんの「サポート」Fさんの「ヘルプ」が出力される処理。

>空白処理の定義ですが、私が考えたものになりますが設定シート表のG2セルに「条件」とタイトルをつけ、G3セルに「会議」、

返信ありがとうございました。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング