Excelで指定した条件で時間を自動に振り分ける方法がありましたら是非教えて下さい！

Excelに詳しい方でもし解決する方法がありましたら教えていただけますでしょうか。

①sheet1に以下の社員別の予定表があります。
　　　　１　　　　　２　　　　　３　　　　　４　　　　　５　　　　　６　 ・・・　
Ａ　H28年4月　 シフト表　
Ｂ　　氏　名　　振分業務①　振分業務②　 　１日　　　　 ２日　 　　 ３日 ・・・ 31日
Ｃ　小林　太郎　　製　造　　　販　売　　 8:00 8:00 8:00 ・・・ 0:00
Ｄ　渡辺　太郎　　販　売　　　配　達 8:00 6:00 8:00　・・・ 8:00
Ｅ　鈴木　太郎　　製　造　　　販　売 0:00 8:00 8:00　・・・ 8:00
Ｆ　山田　太郎　　販　売　　　配　達 6:00 8:00 0:00　・・・ 8:00
Ｇ　佐藤　太郎　　製　造　　　販　売 4:00 0:00 4:00　・・・ 4:00
・
・
・

②sheet2は社員別に振分業務ごとにsheet1に入力した時間が自動で振分けられます。
　　　　１　　　　　２　　　　　３　　　　　４　　　　　５　 ・・・　　　　　　　　　　
Ａ　H28年4月　 業務時間振分表　
Ｂ　　　　　　　　氏　名　　 　１日　　　　 ２日　 　　 ３日 ・・・ 31日
Ｃ　　製　造　　小林　太郎　　 8:00 8:00 8:00 ・・・ 0：00
Ｄ　　製　造　　鈴木　太郎　　 0:00 2:00 2:00 ・・・ 8：00
Ｅ　　製　造　　佐藤　太郎　　 2:00 0:00 0:00 ・・・ 2：00
Ｆ　　小　計 10:00 10:00 10:00 10:00
Ｇ　　販　売　　小林　太郎　　 0:00 0:00 0:00 ・・・ 0：00
Ｈ　　販　売　　渡辺　太郎　　 8:00 6:00 5:00 ・・・ 8：00
Ｉ　　販　売　 鈴木　太郎　　 0:00 6:00 6:00 ・・・ 0：00　
Ｊ　　販　売 山田　太郎　　 5:00 3:00 0:00 ・・・ 5：00
Ｋ　　販　売　　佐藤　太郎　　 2:00 0:00 4:00 ・・・ 2：00
Ｌ　　小　計 15:00 15:00 15:00
Ｍ　　配　達　　渡辺　太郎　　 0:00 0:00 3:00 ・・・ 0：00
Ｎ　　配　達　　山田　太郎　　 1:00 5:00 0:00 ・・・ 3：00
Ｏ　　小　計 1:00 5:00 3:00 ・・・ 3：00

【振分け条件】
・製造の合計上限額は「10：00」で超えた分は販売もしくは配達に振分けられます。
　　販売の合計上限額は「15：00」で超えた分は製造もしくは配達に振分けられます。
　・sheet１の氏名の上から順番に振分けます。

このような条件でできる方法がありましら何卒ご教授下さい。よろしくお願いいたします。

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

• 

  回答ほんとに有難うございます。それでは補足させていただきます。
  質問１：回答→そのとおりです。こちらの記載誤りです。
  質問２：回答→そのとおりです。こちらの記載誤りです。
  質問３：回答→Ｈ28年4月の例の場合はsheet2は30日が良いです。
  質問４：回答→[$-411]ge"年"m"月"として考えていただけますでしょうか。
  質問５：回答→ユーザー定義の[h]:mmとなります。
  ※不勉強で至らないことばかりで大変申し訳ございません。上記内容で申し訳ありませんがご考慮いただけますでしょうか。よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/11/03 20:07

• 

  ご質問有難うございます。以下のとおり回答させていただきます。
  質問６：回答→21名です。
  質問７：振分業務①が配達になるケースもございます。
  　　　　※ご理解していただいているとおり振分業務①が優先して振り分ける業務となります。
  
  分かりづらい点が多々あり申し訳ございません。これに懲りずご不明な点がありましたらいつでも
  ご連絡いただけますでしょうか。
  何卒宜しくお願いいたします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/11/03 20:13

No.5 ベストアンサー 回答者： tatsu99 回答日時： 2016/11/04 01:11

No4です。

マクロは標準モジュールへ登録してください。

この回答へのお礼

ご連絡有難うございました。
マクロはまだ未知の世界で、頂戴しましたURLを参考にして勉強させてもらっています。
理解するにはまだもう少し時間がかかりそうですがこの機会にしっかり勉強したい思います。

お礼日時：2016/11/06 17:04

No.4 回答者： tatsu99 回答日時： 2016/11/04 00:01

Ｎｏ２です。

マクロの行数が多いので、下記ＵＲＬへ書きました。
http://climbi.com/b/8349/0

以下の３つのマクロが使用可能です。
簡易版時間振分
　簡易方式で時間を振り分けます。振分結果はsheet2に格納されます。（sheet2がないとエラーになります）
高度版時間振分
　高度な方式で時間を振り分けます。振分結果はsheet3に格納されます。（sheet3がないとエラーになります）　簡易方式との違いは、後述します。
振分時間比較
　簡易版と高度版の違いを比較します。
　sheet2とsheet3の違いをチェックします。差異がある個所は、背景色黄色で表示します。

簡易版と高度版の違いを説明する前に、以下の用語を定義します。
Ａグループ：振分業務①②が製造と販売の担当者のグループ（配達を含まないグループ）
Ａグループの担当者の作業時間が２５Ｈを超えている場合はエラーになります。
（製造と販売にどのように振り分けても２５Ｈを超えてしまう為）
Ｂグループ：上記以外の担当者のグループ（配達を含むグループ）

簡易版による振分
１．グループＡについて、上の担当者から順に下の担当者へ割り当てを行う。
２．グループＡの1人の担当者については、まず、振分業務①に割り当てる。もし、割当先の業務（製造又は販売）の上限を超える場合は、割り当て可能なぶんを振分業務①に割り当て、残りを振分業務②に割り当てる。
３．グループＢについて、上の担当者から順に下の担当者へ割り当てを行う。
４．グループＢの1人の担当者の割り当て方法は、グループＡと同様。

高度版による振分
簡易版で割り当てを行うと、以下のようなケースの場合、配達への割り当て時間が多くなる。
ケース例１
　グループＡを割り当てた結果、製造が１０Ｈ、販売が１１Ｈとなったとする。
その後、グループＢの担当者が振分業務①＝製造、振分業務②＝配達で６Ｈを割り当てるとき、
製造に割り当てる時間がないので、製造＝０Ｈ、配達＝６Ｈとなる。
これで、運用上、問題がなければ構わない。しかしながら、配達に割り当てる時間を極力少なくしたい
ということであれば、以下の調整が可能である。
販売側が４Ｈの余裕があるので、グループＡに割り当てた時間を調整して、
製造＝６Ｈ、販売＝１５Ｈとなるように、グループＡの割り当てを調整を試みる。
（この調整は、グループＡの担当者について、下の担当者から上の担当者の順に行う）
この調整の結果、製造＝６Ｈ、販売＝１５Ｈとなれば、（ならない場合もあるが）
グループＢのその担当者を製造＝４Ｈ、配達＝２Ｈと割り当てることができる。
ケース例２
　ケース１と同様であるが、総販売の時間が満杯で、総製造の時間に余裕があるケース。
　販売の時間を製造に割り当て、グループＢで販売の時間を確保できるようにする。

従って、高度版の振分は以下のようになる。
１．グループＡについて、割り当てる。（簡易版と同じ）
２．上記の後で、ケース１、２対応の調整作業を行う。
３．グループＢについて、割り当てる。（簡易版と同じ）
要は、配達の時間を極力少なくするようにしています。
（但し、振分業務①が配達の場合はそのまま全時間を配達へ割り当てます）

どちらが、運用上都合が良いかは、両方を試してみて、しっくりくるほうを採用してください。
どちらが良いか、補足していただければ幸いです。
違いの確認は、振分時間比較のマクロを使用してください。
又、ほかに要望があれば、その旨、補足ください。（必ずしも要望に添える保証はありませんが）

No.3 回答者： tatsu99 回答日時： 2016/11/03 13:11

No2です。

追加質問です。
質問６：社員別の予定表の担当者の方は何名ほどでしょうか。
質問７：振分業務①が配達になるケースもありますか。
（例　振分業務①＝配達、振分業務②＝製造のようなケースです）
振分業務①が優先して振り分ける業務で、その業務が時間が満杯のとき、振分業務②に振り分けると
理解しています。

No.2 回答者： tatsu99 回答日時： 2016/11/03 11:32

補足要求です。

質問１：横に 1 2 3 ・・と並んでいる数字は、A列 B列 C列・・と解釈して良いでしょうか。
質問２：縦に A B C ・・と並んでいる文字は、1行 2行 3行・・と解釈して良いでしょうか。
質問３：H28年4月の例ですが、この場合、日付が31日までありますが、sheet2に作成するのは4月なので30日のほうが良いと思いますがいかがでしょうか。
質問４：A1に日付が記入されていますが、セルの書式設定でユーザー定義の[$-411]ge"年"m"月"を使用と理解して
良いでしょうか。又、そうでないなら、そのように書式を設定することは可能ですか。
（質問の意図はA1のセルから正しい年月を取得したい為です。書式が標準でただの文字列として"H28年4月"があるのはまずい為です。）
質問５：時間が表示してあるセル（例8:00）の書式設定は、ユーザー定義のh:mmでしょうか。
又、その書式をユーザー定義の[h]:mmにすることはできますか。
[h]:mmにすると34:00等の表示が可能になります。
小計の時間が24時間を超えたとき、例えば28時間の場合、28:00と表示できますが、
h:mmでの表示の場合は4:00と表示されてしまいます。
（質問の意図は、小計が24時間を超えた場合でも、それを正しく表示したいということです。）

No.1 回答者： mike_g 回答日時： 2016/10/29 20:50

上端行の列記号が 右横へ１、2、3、…

左端列の行番号が縦下方向に A、B、C、…
となっているのは何処の国の表計算ソフトですか？