ExcelのVBAでシフト表を作っていますが、詰んでいます。

条件がこんがらがってしまったので、再投稿です。

自動割り振り機能のあるシフト表を作成しています。
（添付画像はB組のデータです。これを元に作成します）

上司の要求は｢急遽休みになった人員を補填出来る機能付きで｣とのことで…

例として；10日の日勤予定者3名がコロナで出勤不可となった場合、(10日日勤3名)の補充要員として（夜勤）（5連勤になる人）を除いた3人をランダムに選んで呼び出す機能。です。


【絶対条件】
・休みは各人10日前後（10日±4日までは可）
・各所最低必要人数を割らない。多すぎない事。
　最低必要人数＜最高限度人数→（A；夜勤日勤／各2人＜4人　B；日勤9人＜12人／夜勤6人＜8人）
・新人にはフラグを付けられる機能追加。フラグがある人員のみで固めない。
・基本4勤3休ベース。
・日勤は日勤、夜勤は夜勤で固定。
・補充人員の呼び出し回数、個人の1ヶ月の出勤日数が極力均等になること。

【基本情報】
A；日勤4人　夜勤4人　　　　　計8人
B；日勤14人　夜勤10人　　　　計24人
の人間がいます。今回、サンプルのシュミレーションデータとして使うだけなので
個人の予定や都合等は考慮せず、条件を抑えた上で、均等さのみを重視で自動割り振りしたいです。

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー



完全文系で、Excel経験は中学で時間割を作った以来です…。

シャッフルシフトのコードをコピーしてきて、ランダムに組むところまでは頑張れたのですが、
条件指定やフラグを付けた人員を固めないように自動割り振りするコードなどは、検索しても見つからなかったり、貼り付けても機能しなかったりで行き詰まっています。

自動割り振り機能を生かすまでに1週間かかり…、今回機能追加の締め切りが水曜で、正直もう間に合わなそうです。

どうかお助け下さい。

No.6 ベストアンサー 回答者： goomania 回答日時： 2021/05/25 09:58

No.1さんの回答のとおり、勤務のシフト表作成はかなり高度なスキルを要求される作業です。

従って、これを自動化しようとすると、自ずとアルゴリズムの作成やプログラミングの場面でもハイレベルなものを必要とすることになります。
>完全文系で、Excel経験は中学で時間割を作った以来です･･･
というレベルで立ち向かうのは至難の業です。
最近ではシフト表の作成にはAIが活用されるようになってきました。シフト勤務の最適化は無数の組合せの中からAIに試行錯誤させて最も好ましいものを選択するという流れなわけです。
AIにやらせる仕事をVBAでプログラミングするのは困難ですので、一部自動化してあとは人間が調整するという方法をとるケースも多いと思います。
No.1さんのいう
>自動で割り振る機能を付けると･･･絶対に他のすべてのメンバーの勤務に影
>響（変更）が出る･･･それを修正するのは無理･･･
ということになり、「AIが試行錯誤を繰り返す」でない限り、結局人による調整が何度も発生し、「自動化の意味がない」みたいなことになってしまいます。
No.1さんも
>その手のアルゴリズムを組むのは難しい。
とおっしゃっていますが、この手のアルゴリズムは特許すら取れるレベルのものです。
https://www.secomtrust.net/service/hrtech/shift. …
つまり、お金をかけず、「水曜日まで」という短時間でシフト表の自動作成に挑戦するのは無理があるということになります。
(1)自作をあきらめて、有料で提供されているサービスの活用や外部業者による開発を検討する
(2)少し時間をかけて、「自動化もどき」の「シフト表作成支援」プログラムの自作に挑戦する
という選択肢になるのではないかと思います。
(1)であれば以下のURL
https://www.otasukeman.jp/column/shift-automatic …
(2)であれば以下のURL
http://mt-soft.sakura.ne.jp/web_dl/common/shift_ …
がそれぞれ参考になると思います。

この回答へのお礼

皆さん回答を下さりましたが、URL等細かく出して下さったのでベストアンサーを選ばせて頂きます。
昨日提出が遅い、要求した機能がない等散々上司に怒られたのですが、
もう無理なもんは無理なんだなと開き直っております。
外注するにしろ全部上司にお任せしてしまおうと思います。

お礼日時：2021/05/27 12:29

No.5 回答者： banzaiA 回答日時： 2021/05/24 18:10

すでに回答されていますね。

皆さんのおっしゃることはもっともなことです。
＞資料の見本1個作ってくれ。という話なので
ということであれば、手作業で１個作り上げればいいでしょう。

以後稼働できるシステムを作ってくれとのことでしたら、
外注ですね。
あなたがシステムとして作り上げれば、あとあとのメンテナンスも
あなた任せになるのは当然のことでしょう。

この回答へのお礼

とりあえず手作業Excelのファイルを水曜に出したら、作業が遅い、要求した機能がないなどと怒られましたが
皆さんの回答を見る限り、こんなの事務通訳のド素人が出来るものでは無さそうなので、もう諦めています。
上司は基本的にExcelもワードも「パソコン」の方なので、よく分かっていないのかと。私も人のことは言えませんが…。
文系かつ年齢が高い方の多い職場ではこんなものなのかなと。

お礼日時：2021/05/27 12:26

No.4 回答者： 銀鱗 回答日時： 2021/05/24 16:47

＞マクロ使わずに似たようなものを作るアイデアがあれば教えてほしいです。

Excel の ”マクロ” は ”Visual Basic for Application”…VBAとも呼ぶ、プログラム言語なんです。
「アルゴリズム」が明確でなければ作成できないと思ってください。
No.1の回答でもお話しした通り、アルゴリズムが示されず、条件だけが並べられても、それではプログラムは作れないのです。
どうしても作成する必要があるのでしたら、シフト表作成のアルゴリズムを理解している人の協力を得てください。

・・・

本来の業務でないことに時間を取られるというのは
”損失” になりますので、無理なら無理と言わなければ損失額は時間が経つほど大きくなりますよ。
しかも自身の評価の下げ幅も大きくなる。
これは社会人としてとても大切なことですので、しっかり覚えましょう。

そんなわけで外注することを勧めます。
水曜日には間に合わないでしょうけどね。

この回答へのお礼

素人には無理。アルゴリズムだとか…もう日本語の意味すら怪しいレベルの代物です。文系通訳事務には無理な話でした。
上司が何故私にこの手の要求を振ってきたのかわからないのですが、
無茶振りも良いところだということが分かりました。
水曜前に中間報告で出来ているところまで出して全然要求したものと違うと怒られましたが、その後修正などは言われていないのでこのまま放置にします。

お礼日時：2021/05/27 12:05

No.3 回答者： amerikaumare 回答日時： 2021/05/24 11:45

>マクロ使わずに似たような物を作るアイデア

私がCOBOL（プログラム言語の一種）作ったのは、
会社の出勤日カレンダー、各個人の出勤可能日カレンダー、なんかをマスターデータとして毎日・毎時を割り当てていくのですが 大昔のことで細かいところはわすれちゃいました。

各個人の出勤可能日カレンダー　があるのに　登録忘れで　そのまま計算しちゃうのですよ。　シフト表を配布したあとで「私、この日、有休です」なんて言いやがるので「いい加減にしろ！　期日までにカレンダーの入力をやっておけと言っただろうが」と（普段はおとなしい）私が怒ってしまいました。　また、シフト表を見て「やっぱりこの日は休みます」なんていうバイトの子が居て　頭に来ました。

この回答へのお礼

結局のところ、実用するにはかなりの手間がかかるということですね…。
上司が何故私にこの手の要求を振ってきたのかわからないのですが、
無茶振りも良いところだということが分かりました。
水曜前に中間報告で出来ているところまで出して全然要求したものと違うと怒られましたが、これはもう私の領域ではないと開き直ることにします…

お礼日時：2021/05/27 12:03

No.2 回答者： amerikaumare 回答日時： 2021/05/24 08:50

No１さんの

＞マクロを組んでもらって、それを使って仕事をしたら、それに関係したもっと面倒な仕事を押し付けられて、
システム屋の世界ではよくあります。
「ちょっと教えて」と言ってきた話が結局、1300行のVBAになってその後のメンテナンスもすべてやらされました。

私は　シフト割り当て　をCOBOLで作りました。　が、結局、職長さんが手修正してましたね。

この回答へのお礼

システムの業界ではそうなんですね…。
私は文系事務員で、会社での役割は事務所の通訳なので
今回これが出来ても次回有るかといえば無さそうではあるんですが…。
資料の見本1個作ってくれ。という話なので
条件さえ抑えてそれっぽいものができれば…という感じです。
いっそマクロ使わずに似たような物を作るアイデアなどあれば知りたいです。

お礼日時：2021/05/24 10:09

No.1 回答者： 銀鱗 回答日時： 2021/05/23 01:29

早めに「無理」と伝えて、社内で協力を求めるようにしましょう。

ここは、お仕事で使うものを「無料」で作ってくれるサイトではありませんので、
具体的なアルゴリズムができていない人に対してのマクロの開示は無理と思ってください。

・・・
その手のアルゴリズムを組むのは難しい。
自分も10～12人を日勤、夜勤で割り振り、可能な限り同じメンバー同士で勤務しない、且つ、当人の希望を優先し、しかも勤務時間(給料)は均等になるシフト表を考えたことがありますが、
最終的には全部手作業での作成になりました。

自動で割り振る機能を付けると、
体調不良で交代要員を入れた場合の振り分けの変更をすると、
絶対に他のすべてのメンバーの勤務に影響（変更）が出るのです。
（もう、それを修正するのは無理……てね）

ハッキリ言って素人が手を出しちゃいけないレベルの話です。
そんなわけですので、素直に「無理」とギブアップ宣言することを勧めます。
朝にでも上司に電話しましょう。


・・・余談・・・

ここでマクロを組んでもらって、それを使って仕事をしたら、
それに関係したもっと面倒な仕事を押し付けられて、
またここに泣きついてきた人がいましたね。

前の質問と同じ人が答えてくれれば良いのでしょうが、
見ていないのか回答されませんでした。
「マクロを代わりに作れ。オレ様の成果として使ってやるwww」とやった報いと思ってます。

この回答へのお礼

やっぱり普通の人は無理ですよね？
上の人は会議で使う資料なので、サンプルさらっと作ってよ！って感じでしたが、調べれば調べるほどなんだか技術が必要な気が…。
私は事務職で、本来は通訳なのでパソコンはさっぱりです。
しかしながら事務所内で一番若いのと、今コロナで通訳が出来ることが減って、新卒なのもあって社内で暇そうなのでこの資料作りを任されてしまった感じです。
せっかく頂けた仕事なの頑張りたいのですが…。マクロ使わずに似たようなものを作るアイデアがあれば教えてほしいです。

お礼日時：2021/05/24 10:12