メンバー88人の年間スケジュールを自動で作成したい

質問

私は、600戸2000人規模の大規模マンションの自治会役員をしています。
自治会活動として「夜間防犯パトロール」担当をしており、メンバー88人の年間スケジュールをExcelで作成しています。
スケジュール作成に当たっての条件は以下のとおりです。

○メンバー88人の内訳は、
　有志グループ　16人
　自治会役員グループ　15人
　フロアー長グループ　57人
○毎週、月・水・土に参加する。年間約130日。
　一回に有志1名、自治会役員1名、フロアー長2名、計4名が参加。
○有志グループと自治会役員グループは毎月1回の参加。
○フロアー長グループは年4回の参加。
○メンバー全員から、希望曜日と個別条件(毎月第一月曜、月・水でも祝日は×、8月は外して等)
をアンケートで聞く。
○希望曜日と個別条件をクリアするようスケジュールを組む。
○年間スケジュールは、「有志グループ」「自治会役員グループ」「フロアー長グループ」の3つを作る。

現在は、私がExcelの並べ替え機能とCOUNTIFを使って作っていますが、結構時間がかかります。
「メンバー氏名」と「希望曜日」と「個別条件」を入力すれば、自動的に年間スケジュールが作成できたらよいと思っています。
質問したいことは、2点です。

(1)パソコン入力は出来る程度の人でも扱えるようにしたいが、Excelでもっと自動化することはできないか？
(2)Excelで無理なら、専門のSEにプログラム(システム)作成をお願いしたいが、その場合費用はどれ位かかるのか？

以上、ご回答いただければ幸いです.

回答No.5

nishi6 · ベストアンサー

質問の回答として、「乱数を使ったりして、できますよ」では無責任かと思い、実際例を作ってみました。乱数など使わず、手作業を意識しています。いろいろ面倒なので、マクロ（VBA）を使っていますが、手作業で十分できる操作だと思います。

１．年の入力と開始月のセルを設定
２．年間のスケジュール表を作成
　　　指定した年・月から各月、月と「月・水・土」の日を出力表示
　　　曜日を表示
　　　各日が祝日かどうかを計算し、祝日なら「祝」を表示
　　　　振替休日や第2、第3月曜、春分・秋分の日は計算可能です
　（マクロで実行しましたが、手作業でも十分可能でしょう）
３．アンケートの要望の整理
　　　要望をシート外で管理すると面倒なので表に取り込みました
　　Ａ．希望曜日
　　　　入力が無ければ月水土OK
　　　　「○」が入力されていれば、希望曜日とし、なければ参加不可
　　Ｂ．内・指定曜日
　　　　例えば、「234」とあれば、第2、第3、第4が可能という意味

　　Ｃ．除外曜日
　　　　「▲」がある曜日は割り当てません
　　Ｄ．平日祝日
　　　　「×」があれば平日祝日には割り当てません
　　Ｅ．除外月
　　　　指定の月には割り当てません。年３回にしました
　（マクロでスケジュールに参加できない日には「×」を付けましたが参照
　　しながら手作業でもできる範囲でしょう）

４．その他の要望を加味した年間のスケジュール表の「×」を完成させます
５．後は、スケジュール表に機械的に参加の「●」を記入していきます。
　　右下に進んでいき、「×」でないセルに「●」を記入します。「×」ならば
　　さらに下を探します。「×」の人はなるべく早く「●」にします
　　どうやっても「月1回」に収まらない時は「ごめんなさい」でしょうか。
　　多分翌月は「0回」になるでしょう
６．予定変更が発生した場合、添付図のように「都合のいい人と交換する」
　　ことにしておけば不公平は無いでしょう　
７．新参加の人も考慮して、３グループに空き行を作っておけばいいでしょう
　　また、途中月で諸事情で参加できなくなった人の対応も、
　　機械的に配置してあれば、修正は簡単でしょう。ずらすだけ
　　一応、個人別の「●」の年間計は集計しました。

全体的にみると、人数がある程度多いので、
　・「スケジュール表に参加できない日をセットする」のが重要
　・セットできれば人の配置は機械的に行う
と考えれば、自作できれば、高額の費用や管理費を心配する必要もないでしょう。

また、「Excelの並べ替え機能とCOUNTIFを使い」とありますが、このような機能は使いませんでした。機能を頻繁に使うと、Excelの操作に慣れていることが必要になるので、引継ぎとかが心配になります。
このような案件への対応は、「今日入ってきたパートさんや契約社員さん」が苦痛なく作業できるような手順にしていました。そうしておけば、引継ぎも不要になるわけです。

要は、（手作業であっても）スケジュール表に各人のだめな日を記入することでしょう。

お礼

nishi6さん
ごていねいに、再度ご提案いただき、たいへんありがとうございます。
こういう作り方もあったのか！と眼を開かれる思いです。

私は、メンバーを月水土にグループ分けをし、カレンダー横に曜日グループごとに貼り付け、並べ替え機能でカレンダーの日付順に並べ直す。個別条件を参照しながら入れ替え・調整をする。
という単純な方法でやっています。
次年度に向け、ご提案の方法で作成してみたいと思います。
ほんとうにありがとうございました。

回答No.4

nishi6

ナーススケジューリング問題の類かなと思いましたが、条件がたくさん書かれているように見えて、３グループ（有志グループ、自治会役員グループ、フロアー長グループ）間の関係は無いようです。「あの人とは組みたくない」、「男性とは組みたくない」、「若い人がいい」などの条件がないわけですね。「並べ替え機能とCOUNTIFを使って作っています」と書かれているので、個人間の関係を気にする必要が無いはずです。

問題は、
＞メンバー全員から、希望曜日と個別条件
　(毎月第一月曜、月・水でも祝日は×、8月は外して等)　をアンケートで聞く。
をどのように数値化、計算ができるように条件・計量化するかだと思います。「等」という表現が外注するにしろExcelで作るにしろ問題個所でしょう。

ナーススケジューリングに近い条件で、100名近い人員の配置をExcelで何件も対応しましたが、基本的には、
１．制約条件の強さを見定める。
２．個人を割り当てる時の優先順位の評価関数を作る。
３．条件が強い制約から「乱数」を使い配置を決めていく。
４．割り当てが破綻したら、新たな「乱数」で３から再計算
５．「実現度」的な全体の評価をする関数で、設定した再計算回数で中断する。
６．ある程度の「実現度」になったら、配置を固定し、３から再計算する。
７．どうしてもだめなら、数人（少ない回数）に謝って対応してもらう。
のような手順で対応していました。100人弱で３ヶ月の割り当てに要する時間は２、３分でした。作るのは１週間くらいかかりました。（開発経費の計算は難しいです）作業はパートさんが行っていました。

この質問の場合、３グループ毎に乱数を使い簡単と思える制約条件を考慮して、各人の配置を行い、数値化が難しい条件を手作業で修正するくらいで対応できるでしょう。設定だけが違った、３つの同じプログラムになるでしょう。また、「急に都合が悪くなった」ということは必ず起きるので、対応方法（誰かと交代するとかのルール）を決めておくべきでしょう。さらに、どのくらい前にスケジュールを公表するかも大事です。「年間スケジュール」というのはかなり長い気がします。変更が頻繁に起こらなければいいんですが。

ご参考に。

お礼

nishi6 さん
ご丁寧なご回答ありがとうございます。

フロアー長で実質参加不可能な方がいるため、1回2人のペアを組むときに、二人とも不参加にならないよう配慮する必要はあります。

おっしゃる通り、個別条件をクリアすることが難しいです。この調整にとても時間がかかっています。
曜日と個別条件を「公約」として参加して頂いているため、厳守しなければなりません。
また、都合が悪く当番日を変更してほしいという要請も、月に4件ほどあります。
その度に、手作業で変更調整をしています。
それは、仕方がないのでOKなのです。

問題は、防犯担当の役員が輪番で毎年変わることにあります。
私が次年度も作成するなら、多少面倒でも問題はありません。
しかし、役員の中にはExcel不案内の方もおり、そういう方が担当になった場合なんとかできないかと思って、質問させて頂きました。

4人の方に貴重な回答をいただきましたが、やはり、Excel知らない人でも簡単に扱えるようにはできないようです。
ありがとうございました。

回答No.3

maiko04

マクロにせよ、プログラムにせよ、内容を理解し、メンテナンス（変更対応）
するためには「作って」「はい出来上がり」というわけには行かないのですよ。

普通は作成した会社に月いくらかのお金でメンテナンスを依頼していますよ。

したいことを明確にすれば何人かは作るでしょうが、
理解できなければ意味ないのですよ。

お礼

早速のご回答ありがとうございます。

まことにmaiko04 さんのおっしゃる通りだと思います。
メンテナンス（変更対応）が必要不可欠ですね。

当分、手作業でやるしかないかと思います。
ありがとうございました。

回答No.2

STICKY2006

シフトとか、スケジュールの自動作成ツールは探してみましたか？？

要望に完璧に一致！はせずとも、ちょっと調整すれば十分使えるだろう。
ぐらいなツールならば無料で落ちてんじゃねーかと思いますが。

お礼

早速のご回答ありがとうございます。

一応すこしネットで探してみました。
今のところ、そういう格好のスケジュール作成ツールは見つかりませんでした。
もっとよく調べてみようと思います。

回答No.1

kkkkkm

> Excelでもっと自動化
毎回の同じ作業が単純作業だけど時間だけがかかるというのでしたらマクロの記録で操作を一度記録しておけば次からはその作業を自動化できます。(ご自身でできます)

作成してもらうならこのようなところがあります。(Excelなどで作成してもらう方が安く上がると思います)
2019年最新版】Excel開発に強い システム開発会社9選
https://imitsu.jp/list/web-system/excel/

お礼

早速、ご回答いただきありがとうございます。

そうでしたね。「マクロの記録」という方法がありました。
考えてみます。

昨年までは19名程度の有志メンバーが内輪だけてやっていたので、問題はなかったのです。
本年、私の担当になってから役員やフロフー長を参加させることになり大所帯になりました。
役員やフロアー長に対しては「公約」としているので、個別条件は厳守です。
ですから、かなり面倒な条件をいくつもクリアしないといけません。

私が毎年作成すれば良いのですが、役員は輪番で毎年交替、Excelをよく知らない人もいます。
そこで、もっと簡単に自動化はできないかと思ったわけです。
担当が変わるということが問題なんです。不案内な人がなると、トラブルが起こった場合お手上げになります。

Excelの開発会社は知りませんでした。10万からできるのですね。
前に、ランサーズでフリーランスのSEに問い合わせたところ20万と言われました。それは自治会の予算では無理と断念しました。

ご親切な回答、感謝いたします。こんなに早く反応があるとは思いませんでした。