EXCELの関数にお詳しい方に質問
( ˘･з･)チェッ@君の💜にRev.🎵
エクセルで自動で勤務表を作成できないか試作中なのですが
左の勤務形態を重複なく、かつ漏れなく各勤務者に割り振る必要があるとします。
それだけならできてるんですが、ここから更に複雑な条件があります
1、漏れなくかつ重複なく割り振る
2、AさんとBさんは『へ』と『と』の勤務はやらない
3、GさんとHさんは『い』と『ろ』の勤務はやらない
2と3を満たすだけなら、AさんBさん用と、GさんHさん用の乱数表をそれぞれ別に作成すれば良いだけなんですが、それだと1の『重複なく漏れなく』をクリアできないんですよね。
それをクリアするには同じ乱数表を使うのを余儀なくされます。
しかし、その場合、AさんBさんは『へ』と『と』の勤務を除外して、かつ残りの勤務からランダム抽出して、GさんとHさんにも同様の条件付けて、かつ全員の勤務で重複なし、かつ漏れなし
そんな条件の関数を作成する事可能ですか？
VBAとかはなしです。自分でそれが使えるなら最初からそれで考えてます。
もう10日間くらい悩んでます。
とりあえず知りたいのはどうゆう関数式になるか、ではなくエクセルの関数式のネストだけで可能かどうか、です。
できない事で悩むのは時間の無駄なので
Q＆A向きですが
とりあえず、エクセルの知識は大してないので、１の条件も知恵袋の回答見て真似して全く同じ関数を作成しただけです。でもそれはできました。
しかし、２と３がハードル高過ぎて
そもそもできますかね？
左の乱数表は例として出してるだけです。
実際はこれだけだと毎日同じ勤務になるので31日分の乱数表を作成してます。なので勤務はバラバラにはなります。
130 件のコメント
「漏れなくかつ重複なく割り振る」という意味がよくわからないので…
どういう結果を求めておられるのか。
>> さと さん
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12276390257この回答をまんまセル番だけ変えてみました。確かにできました。
ランダム抽出なら他関数で色々あると思うんですが『漏れなく』をクリアするには乱数を順位付けして上から割り振る、になるのかなと思います。
そうすれば確かに全ての勤務が誰かに割り振られますから
これを手入力で修正してたら『自動』が自動でなくるので無いよりマシくらいの物になっちゃいます😅
>> さと さん
『漏れなくかつ重複なく』てのは『い』～『と』までの勤務は、毎日必ず誰かが就く勤務で、しかし全て１人ずつ限定です。
勤務者に対して勤務形態が足らないでふよね？つまりそこが『休み』です。実際は他の形態がまだありますが省いてます
でも、ABGHの方が、特定の勤務をしないということは、その他の方はその特定勤務をする確率が高まりますよね。
それは問題にはならないのでしょうかる
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
リンクありがとうございます。やりたい事がわかりました、がどうやって実現するかは今から考えます。（できなかったらごめんなさい）
>> ヨッシーセブン@北京 さん
〉〉漏れなしにするだけなら、出てきた数値で振られていない勤務形態に振ればよいのでは？それだと重複が出ませんかね？
INDEX＋MATCHとか
INDEX＋RANDBETWEEN
で組んでみたんですが、上手くいかないんですよねー
〉〉ABGHの方が、特定の勤務をしないということは、その他の方はその特定勤務をする確率が高まりますよね。
それは問題にはならないのでしょうか
なりません。それを言ってたらそもそも勤務組めないので
あと、省いてますが
実際には部分的に固定勤務があって、その固定勤務の分は別に表を作成して、それを勤務表の方に反映するようネストで組み込んでます。
休み申請も別に組み込んでます。
>> さと さん
ありがとうございます(^_-)-☆因みにこれは、担当者が毎月悩んでるのを見て、私が趣味で挑戦してるだけです。なので期限はありません。
なので、私のモチベーションが低下したら終わりです（笑）
明日4時起きなので（笑）寝ます
個別のレスはできませんが
回答？解答？解凍？はお待ちしております
m(__)m
解き方としては、ヨッシーセブンさんの言われるように、
＞出てきた数値で振られていない勤務形態に振ればよい
のでしょうね。
そうすると、勤務者No.の上から順番に、「まだ振られていない勤務形態」の配列を作って、配列中で乱数が一番小さい勤務形態を選んだら良い気がします。
VSTACK関数でセルの配列を作るのを試しています。
ただし、上から選定をして行くと、勤務者No.の大きい人（下の行の人）が、選択肢の数が少ない場合、上の人に既に取られてしまって、選べなくなってしまうエラーが起こります。
ですので、勤務者名簿の順番を、選択肢が少ない人の順番に、あらかじめ並べ替えておく必要があります。
また、例えば全員が「はにほ」しか選べません、という条件だと４人目以降の人が選べなくなりエラーになります。
こういう事が絶対起こらない前提で、この名簿の並び替えを人間が事前にやってくれていると良いんですけどね。名簿の並べ替えまで関数でやるとなると、かなり大変。
VBAでやったほうが、変更とかしやすいのでいいと思いますが、式でやるならこんな感じかなぁ。簡単にするために、AとBだけで、ランダムに４つの文字を埋める場合、
・H3に選びたい文字を文字列にしていれる
・D3は、H3の文字列のうちの1文字をランダムに抽出
・I3は、H3の文字列から、D3の1文字を取り除いた文字列を入れる
・E3は、I3の文字列のうちの1文字をランダムに抽出
というのを、D3→E3→D4→D4 という順にやっていけば、被らないものを表に埋められます。これが基本形で、
＞2、AさんとBさんは『へ』と『と』の勤務はやらない
＞3、GさんとHさんは『い』と『ろ』の勤務はやらない
こういう条件があれば、Aさんの値を決めるときに使うI3などの文字列を一時的に、選択したくない文字列を除去した文字列を式の中で作成し、その中からランダムで1文字選び出せばいいと思います。
その後の、使った文字列を1文字減らす、という右側の式は同じです。
同じ人の前の文字と被らないようにするには、前の日の文字を除去しておけばよいと思います。
ただ、はっきり言って、式が相当複雑で、変更が入ると大変なので、VBAでやったほうがよい気がします。
式なので、セルに何か入力したり、シートを開く度に、全部変わりますし。
ちなみに、上記の式はこんな感じ。>> さと さん
〉〉ただし、上から選定をして行くと、勤務者No.の大きい人（下の行の人）が、選択肢の数が少ない場合、上の人に既に取られてしまって、選べなくなってしまうエラーが起こります。そう。
まさしく私もそこでつまづいてました。
で、おっしゃる通り並べ変えして、条件付きの人もクリアできると思われる表にしてみようと・・・したら今度は、おっしゃる通りのエラーが・・・
私は無意識のうちに『除外した人以外を対象にして抽選してくれる』と思ってましたが、そんな便利な事はさすがに無い
そこまでは気づいてたんですが、クリアできる式とか関数が思いつかない😰
>> アッカリ〜ン@_@….,….,…😅 さん
ええ私も、やればやる程『こりゃエクセルの関数だけじゃ無理じゃね？』と思えてきました
でも『可能』ではあるんですね。
関数式も教えて頂き感謝です。
ただね、おっしゃる通り、作る以上はメンテナンスも私がしなきゃいかんのですよね。でも、複雑にすればするほど、些細な変更ですぐエラー発生するの目に見えてます。
知恵袋の回答に『メンテナンスできないなら最初から辞めた方が良い』というのもありました。
バックボーンのない私に無謀ですかねー
現時点で、示して頂いた式の4倍くらいの長さになってるんですよ（笑）
結局、全てを加味してかつ、一つの式にまとめなきゃいかんエクセルだけでは、無理ありますかねー
VBAとかpythonでやるべきですよねー
🤣🤣
とソコソコ詳しい人が言ってましたが、自分でトライして意味がよくわかりましたよ。
chat GPTにVBAを作ってもらいましたが、勤務が重複したり、エクセルがずっと考えたりして、数回作り直してもダメでした。
勤務表を考えてる方、とても苦労されていると思います。
過去の勤務表の実績に連番を打って、ランダムで番号を選ぶのはダメですよね…
>> アッカリ〜ン@_@….,….,…😅 さん
おお！セルの配列じゃなくて文字列として結合したら、｢除く｣操作がしやすいですね。なるほど。これで私も考えてみます。
セルの配列からFILTER関数で除いていたら、関数が長くなって困っていたので。
●(8人くらいなら)ここは力技で
●【従業員＝8人】【勤務形態＝8勤務】
→「い～と」の「7勤務」＋「休み」も勤務と考え「8勤務」とする。
●8人を条件無しで均等に勤務を割り振ると→8!=40,320通り
●「い～と＋休」の順番を並べ替えた40,320通りのパターンをEXCELの1シートに書き出す。(1パターン＝1行)(EXCELの最大行数は1,048,576行)
→これが一番難しい？マクロなら簡単？
「順列の全パターンを列記するマクロ」
https://pasokondojo.com/blog/2017/08/16/
●40,320通りのパターンから条件に合うパターンと合わないパターンを判定する。
●条件に合うパターンだけ上にソートして
●そのパターン全てに1からナンバリング
●その日の勤務形態は
「ナンバリングの最大値×乱数」
でどのパターンかをランダムに決定する。
●以上で等確率で勤務表が完成する？
●従業員(例えば「Cさん」)だけで見たとき「い～と＋休」(8勤務)を均等に
・「い」を3~4回
・「ろ」を3~4回
・・・
・「と」を3~4回
・「休」を3~4回
と言う条件も有る？
これがあると複雑過ぎるけど、この条件が無いと同じ勤務が偏る可能性がある？
関数を作成できますか？とありますが
関数を作成するにはVBAが必要となります。
VBAは、そんなに敷居が高いものではないので
チャレンジしてみたらいいと思いますよ。
作ってみました。基本はアッカリ～ンさんのやり方です。
●名簿を、勤務形態の選択肢が少ない順に並べ替え（XY列）
●上から順に、割り当て済みの勤務形態は除いた中から（AM列）、乱数で割り当てる（Z列）
●割り当て時に、除外する勤務形態は除く（AN列）
●ただし休みの人は勤務形態を選択しない（Z列）
次のコメントで式を書きます
下の式を下の行や右の列にコピペで行けます。
■L3（選択肢の数）
=COUNTA(B$3:B$10)-COUNTA(F3:K3)
■M3（割り振り順＝選択肢が少ない順）
=RANK(L3,L$3:L$10,1)+COUNTIF(L$3:L3,L3)-1
■AC3（除外する勤務形態）
=T(INDEX(F:F,MATCH($X3,$D:$D,FALSE),1))
■AM3（勤務形態の選択肢）
=CONCAT($B$3:$B$10)
■AM4（勤務形態の選択済み除く）
=SUBSTITUTE(AM3,OFFSET($Y3,0,AM$1),"")
■AN3（名簿にある勤務形態除外分を除く）
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AM3,$AG3,""),$AH3,""),$AI3,""),$AJ3,""),$AK3,""),$AL3,"")
■X3（名簿並べ替え）
=INDEX(D:D,MATCH(ROW()-2,M:M,FALSE),1)
■Y3（名簿の名前を引用）
=VLOOKUP(X3,D:E,2,FALSE)
■Z3（選択肢から勤務形態をランダム選定 休日除く）
=IF(INDEX(Q:Q,MATCH($X3,$O:$O,FALSE),1)="休","",MID(OFFSET($AL3,0,Z$1*2),RANDBETWEEN(1,LEN(OFFSET($AL3,0,Z$1*2))),1))
ブラウザで見ると、Googleスプレッドシートのエラーが出るので、ダウンロードしてEXCELで開いて下さい。
https://docs.google.com/spreadsheets/d/1WmXJA97vbfjKcrXE1tk0m1dFj1-DoAFS/edit?usp=sharing&ouid=103999302028150044143&rtpof=true&sd=true
>> さと さん
おお、すごい。ここまで作るだけの根気はないです。(^_^;)除外後の値をセルにしておくと、式の中に２つ同じ記述が出て来ないので、少し楽になりますね。（乱数を発生させる数を決める部分と、実際に乱数で選び出す部分）
VBAで関数を作るなら、選ぶ元の文字列、除外する文字列を渡して、選んだ文字、残りの文字を返す関数を作り、日ごと、メンバー毎でループを回すのがいいのかなぁ。
Javascriptとかのほうが、楽しそうですけどね。
分からないことは、Bing AIに聞くと、細かく説明してくれるので本当に助かります。(^_^;)
>> アッカリ〜ン@_@….,….,…😅 さん
乱数初めて使いました😅こうやって使うんだー、勉強になりました。
>> さと さん
＞勤務形態の選択肢が少ない順に並べ替え確かに、これやらないと、ハマりますね。
別のパターンを考えてみると、
全員、選択肢が２つだとして、上の方で割り当てたことによって、下の方の選択肢が１つになった場合、それを上に持ってこないと、割り振れなくなってしまいますね。
例えば、あ～お が選択肢で、
A：あい
B：いう
C：うえ
D：えお
E：おあ
が選択可能の場合、たまたま、
A：あい
C：うえ
D：えお
E：おあ
B：いう
と並んでいたとすると、
A：あい→い 割り当て
C：うえ→う 割り当て
D：えお
E：おあ
B：いう
としたときに、Bに割り当てするものがなくなってしまうので、1文字割り当てる度に、選択肢の少ないものを先に割り当てるということをしないといけないですね。
具体的には、Aに割り当てた後に、Bに割り当てられるのは、1種類なので、優先的にBに割り当てをする。この場合、Bは、う しか選択できない状態となっていて、う が割り当てられる。
Office365で使えるSORT()関数とか使えないと難しいのかな？なんかそれ使っても、もっと大掛かりになるような気もするけど…
>> アッカリ〜ン@_@….,….,…😅 さん
VBAなら、割り当てるときに、選択肢が一番少ない人を先に割り当て、その割り当てによって、他の人の選択肢の数がどうなったかを全部計算し直し、その中でもっとも選択肢の少ない人を次に割り当てる、という感じになるでしょうか。>> アッカリ〜ン@_@….,….,…😅 さん
書いたのを見直していて気付いたのですが、A：あい
B：いう
C：うえ
D：えお
E：おあ
の場合、
1番目を選ぶか、
A：[あ]い
B：[い]う
C：[う]え
D：[え]お
E：[お]あ
2番目を選ぶか、
A：あ[い]
B：い[う]
C：う[え]
D：え[お]
E：お[あ]
の２パターンしかないんですね。(^_^;)
A：[あ]い
B：い[う]
C：うえ
D：えお
E：おあ
とすると、
その後、選ぶものがなくなって、取り合いになってしまいます。(^_^;)
>> アッカリ〜ン@_@….,….,…😅 さん
＞1文字割り当てる度に、選択肢の少ないものを先に割り当てるということをしないといけないおお！それは気付きませんでした。EXCEL関数だけでやると、物凄い沢山のセルを使いそうですね。
>> Herb tea さん
〉〉過去の勤務表の実績に連番を打って、ランダムで番号を選ぶのはダメですよねダメな事はないですけど、毎月イレギュラーな項目がありすぎて、相当な手直しが発生する事が予想され、多分、自動の意味が無くなると思います😅