従業員増減対応で当番種類の増減対応な当番表

VBAでタイトルのような当番表を作りたいと考えています。
こちらにある質問やご回答を参考にいろいろ試しましたが、
なにぶんVBAはド素人のためお手上げ状態です。よろしくお願いします<(_ _;)>

カタチとしては以下のような感じには出来ていますが、
しかし土日の休日や、祝日リストから参照しての休日が表示されるように関数を使用してしまうと、例えば、希望休や有休などを表に記入すると関数が消えてしまい、表内に関数を入れられません。

ならば希望休(有給含む)は手入力で先に入力して、それを条件付き書式で識別できるように色付けて、そして土日と祝祭日も条件付き書式で背景色を付ければ、ほら、休みがどこか分かりやすい！
と思ったのですが。

色のついていないセル(休みではないセル)にだけ当番を割り当てるってできるの？
てか色ってどうやって認識させられるんだろ？ってところで困っています。

そこでなにか方法があれば助けていただきたいです。

■sheetは２つあって、「祝祭休日」「当番表」とあります。

①sheet「祝祭休日」について
自動計算(関数や計算)で算出された祝祭休日の年間の年月日をリストしています。
・A1～A54=祝祭休日の分類、B1=当番表!A1、C1="祝祭日"、D1="備考"
・B2～B37=各祝休日の年月日、B38～B45=会社指定休日(ここは毎年変るので手入力)、
　B46～B54=各イベント(クリスマスやバレンタインなど)の年月日、と分けてます。
・C2～C54=各祝祭休日の名称を記入してます。
・D2～D54=備考を書いています。
※ちなみに、こちらはこれで完成でいいかなぁと思っています。

②sheet「当番表」について
・A1=年(今なら"2022"を手入力)、A2=月(今なら"7"を手入力)、
・B1=DATE(A1,A2,1)、C1～AF1=左のセル+1
・B2～AF2=sheet祝祭休日から祝祭日の名称をXlookuoで引用
・B3～AF3=Weekday関数とaaaで曜日表示　※sheet祝祭休日から条件付き書式で色が付くようにしています
・A3="氏名"、A4～A14="各従業員の氏名を手入力"しています。　
　※今は(仮)として10名(A14)にしていますが、部署によっては多いところもあり、
　　また入社があれば増え、反対に減ることもあるため、表は26名対応(A29)にしてあります。
・B4:AF29=当番表　
　※ここでは、希望休を"休"、有休を"有"と手入力したいと考えていて、
　条件付き書式でそれぞれ色が付きます。また、sheet祝祭休日に応じて条件付き書式で
　「土、日、祝、指定休」の列に色が付くようになってます。
・AG3="合計"、AG4～AG29=各当番の合計
・AH3～AK3="各当番名"　※例)AH3=燃、AI=プラ、AJ=段、AK=消毒
　※部署によては当番が４～６項目ある場合もあって、当番の種類は固定されたものではありません。
・AH4～AH29=各従業員ごとに"燃"の合計、AI4～AI29は"プラ"の合計、
　AJ4～AJ29は"段"の合計、AK4～AK29は"消毒"の合計・・・と、各従業員の行ごとに各当番の合計が続き、
　つまり、この合計をAG4～AG29で各当番を合計しています。

■完成希望
各従業員の休み(希望休、有休)を土日祝の休みを考慮し、また、増減する人員に対応し、
ランダムに割り振られ、かつ月間で合計回数と各当番回数も平等に振り分けられるようにしたいと思っています。
※Aさんは毎月第一週の月曜日は固定というものではなく、
　毎月何の担当するかはバラバラ、されど月合計ではみんな平等というような。

また、ややこしいのですが、当番の種類が部署によって異なるということと、
当番によっては「週に何回やるのか」「何人でするのか」も違っていて、
そういった変更にも対応できる、今後も使用できるものを想定しています。

ちなみに今作っている表では、以下のようにしたいと考えています。
　
・燃＝週１回、４人
・プラ＝週１回、１人
・段＝週１回、１人
・消毒＝毎日、１人

何かいい方法がありますでしょうか？
お手数をおかけしますがお力貸していただけないでしょうか。
よろしくお願いいたします。

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

• １，に関して
  ケース①の「同じ日に４人を割り当て」です。
  ※その他「週何回、何人でするか」という設定が出来るならば、「何人でするか」の部分は全て同じ日に割り当てとなります。
  
  ２，に関して
  ②の「(イベント含む)」という部分のみ補足いたします。
  sheet祝祭休日にイベントの項目があり、sheet当番表に反映され、「今日は何の日か」というところでB2～AF2上でクリスマスやバレンタインデー、ホワイトデーと表示されます(もちろん他祝日も表示されます)。ですが、こういったクリスマスなどは祭日ではあっても国民休日には当たらないため仕事となります。そのため、②に関しては下記以外となります。
  
  ②シート：「祝祭休日」のB2～B54の日付（イベント除く）
  ※つまり「祝日(元旦～勤労感謝の日)、その他(お盆や大晦日)、会社指定休日」が仕事休み
  　　「イベント(バレンタインやひな祭り、クリスマス)」は仕事

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/07/19 19:30

• ３，に関して
  現時点で設定してるのは４項目(燃、プラ、段、消毒)ですが、部署によっては６項目ある部署もあるため、増えて6項目になっても対応できるものを望んでいます。
  （例）現時点で「AH3～AK3」に「燃、プラ、段、消毒」と入っていますが、「AL＝トイレ」や「AM＝休憩室」と項目が追加されたり、あるいは減っても、週何回で何人でするかという設定をすると反映される、というようなものです。
  
  ４，に関して
  マクロではなく関数式で算出するように関数式を入れてあります。
  
  ５，に関して
  sheet当番表のA1に入れている「年(2022年)」をsheet祝祭休日のB1に引用しており、そのB1を基としてB2～B54に、それぞれの祝祭日に合った計算で日にち(シリアル値)が算出されるようになっていて、そのシリアル値をユーザー定義から「m/d」としています。
  
  ６，に関して
  そのご理解であっております。

    補足日時：2022/07/19 19:34

• sheet当番表

  
    補足日時：2022/07/20 06:23

• sheet祝祭休日

  
    補足日時：2022/07/20 06:24

• 旧当番表

  
    補足日時：2022/07/20 06:25

• 以下、お役に立てば...
  
  マクロは組んでいませんが、必要ヵ所に関数式が入っていると思います。条件付書式や関数式、その他ご確認いただき活用いただければと思います。
  
  『～Excel19Ver』
  https://ac-data.info/d/FntBHuurcDY
  sheet当番表のB2～AF2の参照時、Xlookupに対応していないためINDEX、MATCHで参照してます。
  
  『Excel21～365Ver』
  https://ac-data.info/d/5AyNzBsij2o
  こちらはXlookupで参照してます。どちらかお使いください。
  
  ※sheet当番表のAG1に歴日数「=TEXT(DAY(EOMONTH(B1,0)),0)」、A30に記入されている従業員数「=SUBTOTAL(103,A4:A29)」がありますが、何かお役に立てれば…と置いてますが、必要なければ消して構いません。

    補足日時：2022/07/21 00:52

No.21 ベストアンサー 回答者： tatsumaru77 回答日時： 2022/07/26 11:06

下記にアップしました。

https://firestorage.jp/download/6a63b7fd253b0451 …

https://firestorage.jp/download/fbf79c1f4fbef7c9 …

ブック内のマクロ（Module1）をあなたの環境に上書きしてください。
エラーメッセージ一覧.docxを作成しました。
表作成、作業人数設定時に、エラーが表示された場合に活用して下さい。


マクロ変更箇所
１．シート名を「祝祭休日」に変更しました。
２．コメントを追記しました。
３．当番表割当実行時、割当調整時の処理を変えました。
DOWN調整時、作業の移動先の従業員が存在しない場合、エラー表示後、当番表割当を打ち切っていましたが、
最後まで割当調整処理を行いようにしました。
UP調整時、作業の移動元の従業員が存在しない場合、エラー表示後、当番表割当を打ち切っていましたが、
最後まで割当調整処理を行いようにしました。
割当調整処理については、エラーメッセージ一覧.docxを参照ください。

この回答へのお礼

ありがとうございます！
大変理想通りの表になりました！
今いろいろな状況をそうていしていますが、特に問題もなさそうです(^^)
今月中には何かあればご連絡いたします！

本当にありがとうございました♪

お礼日時：2022/07/27 07:43

No.20 回答者： tatsumaru77 回答日時： 2022/07/24 10:58

>「1週間程度をめどに」というのは、修正にはご対応いただけないということですが、エラーなどは対応いただけるとの理解でよろしいでしょうか？

はい。エラー（不具合）については、できるだけ対応いたします。
機能の追加／修正は考えていませんが、とりあえず話だけは伺いますので、
あれば、その旨連絡ください。

この回答へのお礼

承知しました！
今想定されるいろいろなパターンを試していますが今のところ問題なさそうです！また何かあればご連絡いたします。

お礼日時：2022/07/24 11:01

No.19 回答者： tatsumaru77 回答日時： 2022/07/24 09:36

No18の誤記訂正です。

３．割当履歴シート
誤：表作成を行った時の、合計値（AG列の値に相当）を割り当てシートに書き込みます。
正：表作成を行った時の、合計値（当番表AG列の値に相当）を割当履歴シートに書き込みます。

No.18 回答者： tatsumaru77 回答日時： 2022/07/24 09:30

下記にアップしました。

https://firestorage.jp/download/2fb3c5c49d372171 …

１．祝際休日シートの件
①当番表の2022の位置が変わったためエラーになっていましたので、とりあえずこちらで修正しておきました。
②シート名ですが「祝祭休日」でなく「祝際休日」としたのは、何か意図があるのでしょうか。（祭と際）
もし、「祝祭休日」にする場合は、その旨連絡ください。現行通りであれば、連絡不要です。

２．当番表シートの件
①AG列の合計が、消毒までしか対応していません。最大１０件まで定義しても対応できるようにしてください。

３．割当履歴シートを追加しました。
このシートは、表作成を行った時、なければ自動的に作られますので、予め作っておく必要はありません。
(このシートを削除したのち、表作成を行ってください。そうすると自動的に作られることが確認できます。
但し、最初は非表示の状態になっています)
表作成を行った時の、合計値（AG列の値に相当）を割り当てシートに書き込みます。
F列（1月）～Q列（12月）の位置に書き込みます。
この値を使用して、割り当ての調整を行います。
例えば、従業員1～従業員10の１０人構成で、６７作業を1月に実行したとき
従業員２，３，８の人が作業数６件で、ほかの人が作業数７件になったとします。
2月に割り当てを行うとき、1月の履歴を参照すると、従業員２，３，８の人は、作業数を多く割り当てる
必要があるので、従業員２，３，８が割り当て順序の1番目～３番目に来るように調整します。
（従業員２，３，８の中でどの順番にするかは乱数で決めます）
上記以外の従業員は、４番目～１０番目に来るように調整します。
（上記以外の従業員の中でどの順番にするかは乱数で決めます）

前回の実行履歴がない場合は、単純に乱数でシャッフルした順番で割り当てます。
これが、割当履歴シートの用途です。

以下のマクロが提供されています。
①割当履歴シート表示
作成時は、非表示になっています。これを表示に切り替えます。
②割当履歴シート表示
表示を非表示に切り替えます。
③割当履歴クリア
割当履歴をクリアします。

運用時は、通常上記のマクロを使用するのは、システム管理者だけなので、
公開不要のため、ボタンは設けませんでした。

４．作業項目の表示／非表示について
ボタンを設けましたが、暫定的なボタンです。見栄えの良いボタンに変えてください。

５．マクロの機能追加については、これで全て完了しました。
このブックのマクロで、機能確認をお願いいたします。
こちらでは、機能の修正は行いませんが、後々のことを考えメンテナンスがしやすいように、
マクロ内にコメントをもう少し追記しようと思っています。
２、３日以内に、再度マクロをアップする予定です。（コメントを追記したバージョン）
機能確認は、1週間程度をめどにおこなっていただきたいのですが、可能でしょうか。

この回答へのお礼

ありがとうございます！
大変助かりました！
今さっそく使ってみておりますが、大変感激しております！

以下、ご回答にお答えします。

１．「１の②」の件、「祝祭休日」で間違いありません。確認不足です。

２．「２の①」の件、当番表シートのAG列の合計も確認不足です(^^;
　ご指摘ありがとうございます。

３．「５」の件、機能確認は今日からさっそく使わせていただきます。
　なお「1週間程度をめどに」というのは、修正にはご対応いただけないということですが、エラーなどは対応いただけるとの理解でよろしいでしょうか？

お礼日時：2022/07/24 10:13

No.17 回答者： tatsumaru77 回答日時： 2022/07/23 20:51

レイアウト案を拝見しました。

ほぼ問題ないですが、下記の１点が非常に大きな負担になります。
作業日と人数の指定は、週１もしくは毎日の前提でしたが、
これが、最大週２もしくは毎日になっています。
週２の場合、祝日とかち合わなければ、問題ないですが、
どちらか、もしくは両方が祝日とかち合った場合に、どのように解決するかが、
基本的に処理内容をかなり見直さないといけなくなります。

週２というのは、本当に必要な要件でしょうか。
もう一度、再検討していただけませんでしょうか。

この回答へのお礼

＞もう一度、再検討していただけませんでしょうか。

週2にすることで処理内容をかなり見直さないといけないということであれば、そのまま「週１もしくは毎日(つまり今のままの作業曜日)」で問題ありません。
と言いますのも、No8のご回答の私のお礼コメントの中で、
＞表作成後も直接入力でき、その後クリア実行しても手入力した作業割当分も消えたことから、振分け範囲をB5:AF10とし、クリア範囲をB5:AF80と考えました。
と書きましたように、表作成後であっても、設定され認識される作業項目であれば、クリアボタンを押すことで消すことができたからです。

こちらで週２必要な場合は、必要ヵ所に直接入力いたします。
よろしくお願いいたします。

お礼日時：2022/07/23 21:08

No.16 回答者： tatsumaru77 回答日時： 2022/07/23 18:03

＞結果、DL時の設定値そのままの状態で、上記手順を行った場合、以下の通り振分けられていない日が見つかりました。

・3/22、23
・5/6
・7/19、20
・8/11
・9/20、21
・10/11、12


8/11は山の日のため、休みになります。
他の日はバグのため、出力されていませんでした。
ご指摘ありがとうございました。
下記に修正版をアップしました。ご確認をお願いします。
https://firestorage.jp/download/c602070782c1f07e …


現在、新しいレイアウト用に修正中ですが、割り振りの機能はアップしたものと変わっていませんので、引き続き確認作業をお願いいたします。

この回答へのお礼

ですね(´∀`;)
みおとしていました。引き続きいろん年月でテストしてみます。

お礼日時：2022/07/23 20:44

No.15 回答者： tatsumaru77 回答日時： 2022/07/23 08:36

シャッフルの件ですが

＞例えば、別のsheet(隠しsheetで見えなくしててもマクロ的にOKなら隠しsheetなど)に、シャッフルした表で割り振りを行い、VLOOKUP的な方法でsheet当番表の各従業員毎に引用する、というのはいかがでしょうか？

イメージ的には、そのようなことをするということで理解していただいて問題ありません。
（実際には、乱数を使用するので、隠しSheetもVLOOKUP関数も使用しません）

１．シャッフルをしても、当番表の従業員の表示順は一切変わらない。
２．シャッフルにより、毎月の従業員の割り振り順番がランダムに変化する。

上記の２点が保証されます。

この回答へのお礼

すごいです！私は独学でこれまで関数ばかりやってましたが、マクロに大変興味がわきました！勉強していきます！ありがとうございます。

お礼日時：2022/07/23 15:47

No.14 回答者： tatsumaru77 回答日時： 2022/07/23 08:25

>●「追伸」に関して

感が悪くて申し訳ないのですが、「１日に１つの作業に割り当て可能な最大の人数は、９人」という理由？根拠？言葉が分かりませんが、どのような理屈で「9人」となっているのでしょうか？ご説明いただけると幸いです。
また、ご回答いただけましたらすぐに返答いたします。

上記の件ですが、
https://gyazo.com/2140b1e0597e7c65ba048af81c92358e
のAH5～AK5（燃、プラ、段、消毒）の人数の記入可能な上限値を
決めてください。という意味です。
燃、プラ、段、消毒ともに上限値は同じ値にしたいと考えています。
（下限値は１）
１～Nまでが記入可能とするときの、Nの値を決めてください。

この回答へのお礼

No12のご回答も含めて、理解いたしました。
１～９で問題ありません。

お礼日時：2022/07/23 15:44

No.13 回答者： tatsumaru77 回答日時： 2022/07/23 00:11

＞作業回数の均等化のこともあって難しいかもしれませんが、従業員の並びは、役職や入社年月日順のため、シャッフルせずにできる方法があると大変助かります。

回答：
シャッフルは、内部メモリで行います。見た目は、そのまま表示されている順序の通りです。
従業員A,B,C,D,E,Fの順に並んでいるとき、
例として、乱数の値により
７月は、C,F,E,A,B,Dの順に割り当てる。
8月は、B,A,E,F,D,Cの順に割り当てる。
ということをおこないますが、表示上は常にA,B,C,D,E,Fの順に並んでいます。

シャッフルをせずに、常に固定の順番でおこなうと、
割当に偏りがでます。
例えば、従業員A,B,C,D,E,Fの順に割り当てると、
A,Bは常に多くなる可能性があります。
E,Fは常に少なくなる可能性があります。
このケースでは従業員６人なので、作業件数３２件を処理すると、
A,Bは６件、C,D,E､Fは５件割り当てられます。
（休み等で多少のぶれがある可能性はありますが、ほぼこのようになります）
これが、毎月繰り返されます。
これで良いのでしょうか。

この回答へのお礼

そんなことが出来るのですね！
承知しました！よろしくお願いいたします。

お礼日時：2022/07/23 15:45

No.12 回答者： tatsumaru77 回答日時： 2022/07/22 23:54

>●「追伸」に関して

感が悪くて申し訳ないのですが、「１日に１つの作業に割り当て可能な最大の人数は、９人」という理由？根拠？言葉が分かりませんが、どのような理屈で「9人」となっているのでしょうか？ご説明いただけると幸いです。
また、ご回答いただけましたらすぐに返答いたします。

現在、熱は1日４人を割り当てていますが、最大１日９人とします。という意味です。最大９９人でもOKですが、そもそも、１日に同じ作業をやるのは９人程度あれば十分かと考えました。