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 件のコメント
>> アッカリ〜ン@_@….,….,…😅 さん
これって、AIを導入してからの話ですよね？メンテナンスの事を考慮して随所にメモがありますね（笑）
思ったよりはシンプルですね。
しかし、実際には更に複雑な条件があってね（笑）
検討はしてみましたが・・・
ググッてたら、複数な勤務表だと
遺伝的アルゴリズムで修正していくのが早い・・・的な内容がけっこう出てきて
月の初日に限り=SWITCH($BC8,"日","日","管","管","/1","/1","1/1","1/1","2/2","2/2","3/3","3/3","4/4","4/4","5/5","5/5","11/11","11/11","1/","1/","2/","2/","3/","3/","4/","4/","5/","5/","11/","11/","/2","/2","/3","/3","/4","/4","/5","/5","/11","/11","△","△",IF(D$6="","",IF(COUNTIF($AO8:$AS8,D$6)>0,"▲",IF(50-COUNTIF($B$36:$B$52,999)<$A$8,"",INDEX(A$35:A$52,MATCH(SMALL(B$35:B$52,A8),B$35:B$52,0),)))))
2日目以降は、前日の勤務を加味する必要があります
夜勤明けの人は勤務無しになります。ただし形態によっては明けも勤務になります
2日目以降のセルには、その”夜勤明けは勤務なし”をネストしてます
=SWITCH($BD8,"日","日","管","管","/1","/1","1/1","1/1","2/2","2/2","3/3","3/3","4/4","4/4","5/5","5/5","11/11","11/11","1/","1/","2/","2/","3/","3/","4/","4/","5/","5/","11/","11/","/2","/2","/3","/3","/4","/4","/5","/5","/11","/11","△","△",SWITCH(D8,"管","×","/1","×","2/2","×","3/3","×","4/4","×","5/5","×","11/11","×","/2","×","/3","×","/4","×","/5","×","/11","×",IF(E$6="","",IF(COUNTIF($AO8:$AS8,E$6)>0,"▲",IF(50-COUNTIF($D$36:$D$52,999)<$A$8,"",INDEX(C$35:C52,MATCH(SMALL(D$35:D$52,A8),D$35:D$52,0)))))))
作成途中のです
すぐ下のは、漏れをチェックするため勤務形態のカウント一覧
更に下のが乱数表
2個目のSWITCHは夜勤明けの勤務回避のため
次のIFは休日申請日の反映のため
最後に乱数抽出のネスト
です
もはや意味不明の数式（笑）
休日申請を入力した表と、固定勤務を反映させる表が別にあります
最初のSWITCHはその固定勤務を反映させるやつ。もう少しシンプルに作りたいのですが
反映するだけならD8=AQ8とかで良いんですが、固定勤務が無い場合に他をネストしていく方法がSWITCH関数しか思いつかない。
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
これは、VBAのソースコードをAIに書いてもらったものなので、これをExcelのVisualBasicの編集画面に張り付ければ、実行できます。ただ、初期設定がソースコードに直接書いてあったり、結果がテキストとして、デバッグ出力に出てくるだけです。
要は、アルゴリズムの確認用で、これで問題がなければ、データをセルから持ってきたり、セルに書き込んだり、シート状のボタンから起動したりと、作りこむ形になります。
上記の、AIが書いてくれたソース、数行直してそれっぽく動くと思ったら、シビアな条件のデータを入力すると、バグっていることが分かったので、修正が必要です。
行番号列番号が無いので、上図は想像で再現してみました。●この式には除く処理は入ってないですよね？除く処理とは
｢AさんとBさんは『へ』と『と』の勤務はやらない｣
の処理のことです。
●固定勤務は、人ごとに決まっているのではなく、日別で変わるのですか？例えば９月１日はAさんは"1/1"固定、9月2日はAさんは固定なし、みたいな感じですか？
固定勤務の反映の時、月初と2日目でBC列とBD列と分けてあるのはなぜ？
●B$35:B$52や、D$35:D$52は、下部の乱数ですよね？乱数に999が入るのはどういう時ですか？これは何の判定でしょうか。
50-COUNTIF($D$36:$D$52,999)<$A$8
他も色々ありますが、少しずつ質問。
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
＞固定勤務が無い場合に他をネストしていく方法がSWITCH関数しか思いつかない。固定勤務の仕組みがよくわからないのですが
=IF(LEN($BD8>0,$BD8, )
みたいなのはダメなんですか？固定勤務が無い時のルールはどうなっているんでしょうか。
ないですね。処理が考えつかないので保留段階
〉〉固定勤務は、人ごとに決まっているのではなく、日別で変わるのですか？例えば９月１日はAさんは"1/1"固定、9月2日はAさんは固定なし、みたいな感じですか？
固定勤務の反映の時、月初と2日目でBC列とBD列と分けてあるのはなぜ？
日こどに決まってますが、その分を別表に入力してないので反映してないだけです
〉〉固定勤務の反映の時、月初と2日目でBC列とBD列と分けてあるのはなぜ？
分けてるのは『前日の条件のありなし』です。
夜勤があるのですよ。でも月初日の勤務に限り前月の勤務を反映できない（できなくはないが複雑になる）ので。
初日に限り手入力になると思います。なので初日だけは関数はなくても良いです。
〉〉B$35:B$52や、D$35:D$52は、下部の乱数ですよね？乱数に999が入るのはどういう時ですか？これは何の判定でしょうか。
50-COUNTIF($D$36:$D$52,999)<$A$8
ここは知恵袋の回答まんまなので自分でも理解できてないです。
ただ、乱数の生成方法を変えました。
『勤務なし』の人がXになりますが、Xの人は複数人いるため複数必要
なので、RANRAND()*（20－1）+1にして複数のXにそれぞれ乱数を作成するようにしました。でないとXは全て999になるので
>> さと さん
固定勤務がない時の優先順位固定勤務あり
↓
ないなら休日？
↓
ないなら前日が夜勤なら×
↓
前日が夜勤でない、もしくわ日勤だけの人
を対象にしてランダム抽出
ただし、前日が夜勤の人でも翌日また夜勤がたまにあります。
頻度が少ないので時間調整も兼ねてその部分は手入れする予定
を除いた人を対象にして振り分ける
てのが複雑です
しかも、特定勤務をやる人とやらない人がいる
現状だと、ランダム抽出の関数は必ず乱数表の一番上の『項目』を含める必要があるため、やらない特定勤務を考慮して順番を組んでも全パターンをカバーするのが不可能なんですよね
なんで、やはり残りの『できる勤務』の中から抽出する形になりますかね
その場合の関数がよくわからない
INDEX+RANDBETWEENを入れて絞ってみたんですがエラー出るんですよね。まー単に数式間違ってるだけの可能性もかなりありますが
>> ヨッシーセブン@北京、今は大阪 さん
〉〉漏れなしにするだけなら、出てきた数値で振られていない勤務形態に振ればよいのでは？それは具体的にはどーゆー関数になりますかね？
INDEX+RANDBETWEENで勤務を指定してその中から割り振るようにネストしてみたんですがエラーでました。
多分、形式のミスか、またはカバーしてない部分がありその部分の処理を指定してないか、のどちらかだと思いますが
悲しいお知らせです。完璧と思っていた、選択肢が少ないものをその都度、評価し、優先的に割り当てていけば、（少なくとも１つでも割り当てができる場合は）必ず正しい割り当てに辿り着けるというアルゴリズムは、ダメでした。
上は、正しく割り当てることができた場合。
下は、割り当てが出来なかった場合です。
結果の全体像を見るために、同じアルゴリズム（と思われる）VBAのプログラムを1000回ループさせ、出現回数を見てみました。
左が出力結果、右の数字はその出現回数、
■が割り当てできなかったところです。
あういお■:216
あえいうお:74
あえいおう:58
あえうお■:139
いうあお■:257
いえあうお:56
いえあおう:78
いえうあお:58
いえうおあ:56
ワークシートをポチポチやっていても、だいたいこれと同じような結果なので、間違いはないと思います。
数独でいうところの、一度、どこかを確定させてみて、できるかどうかを確かめる、みたいな難しいパターンなんですかね？
というわけで、このアルゴリズムは使えないことが分かりました。
現場からは以上です。(￣^￣)ゞ
（ちなみに、出現回数は、0スタートだったので、各項目+1してください）
"A", Array("あ", "い", "う")
"B", Array("あ", "い", "う", "え")
"C", Array("あ", "う")
"D", Array("あ", "え")
"E", Array("い", "お")
あいうえお:253
いあうえお:121
いうあえお:263
いえうあお:115
ういあえお:243
---
"A", Array("あ", "い", "う", "え", "お")
"B", Array("あ", "い", "う")
"C", Array("い", "う")
"D", Array("あ", "い", "う", "え")
"E", Array("あ")
おいうえあ:493
おういえあ:505
>> アッカリ〜ン@_@….,….,…😅 さん
●2回目選択時に写真の様に「え」と「い」が1人ずつなので●それを検出して
●無条件で
・2回目でBさんには「え」を
・3回目でCさんには「い」を
割り当てるロジックの追加すれば正確に割当て出来るハズ(エラーにならない？)。
>> アッカリ〜ン@_@….,….,…😅 さん
前にも書いたけど、｢絶対｣は無いのでベストを尽くす方法しか無いと思っています。実現不可能なパターンの場合は、実現可能になるようなOKのデータに運用で変えて貰うしか無いと思います。
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
固定勤務は日毎に変わるのですね？見せて貰った式では、月初と月初以外の2種類でしたが、月中も毎日変わるのでしょうか？
以下の3つはそれぞれ別の話ですね？
それとも、同じ話が混じっていますか？
●固定勤務(日別、人別に決まっている)
●夜勤明けの勤務条件
●AさんとBさんは『へ』と『と』の勤務はやらない(人別)
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
知恵袋から取ってきた下の判定は不要です。50-COUNTIF($D$36:$D$52,999)<$A$8
これは重複のあるデータから、重複なしの50個のデータを抜き出す為の判定です。
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
>RANRAND()*（20－1）+1RANRANDはRANDの間違いですか？
（20－1）+1は乱数表の式ですか？
乱数表の式はどうなっていますか？
＞Xは全て999になるので
これは知恵袋から式を取ってきていますか？知恵袋は重複を無くす式になっているから、999になります。
xが重複するなら、999に変換する式を除く必要があります。
具体的には｢=RAND()｣だけでOKで、｢countif｣などを除く必要があります。
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
＞INDEX+RANDBETWEENで勤務を指定してその中から割り振るようにネストしてみたんですがエラーでました。具体的な式を書いて下さい。どう直したらいいか説明できると思うので。
>> さと さん
式を忘れました。もう一度書いてみます
とりあえず、
・式の最初は『固定勤務の反映』
A～E の人だけやる勤務、FGHの人だけやる勤務があって、A～Eの固定勤務は5日に一度のルーティンなんです。その勤務以外にも勤務は割り振るのですが、決まってる勤務のみ反映させてる訳です。
そして、問題は次の式なんですが
・夜勤明けは勤務しない（例外はあるが）という条件で、それをXにしてます。
これをネストしないとランダム抽出だと夜勤の連勤が多数出ますから
この２つが確定で、それ以外の人にランダム抽出するように組み込む必要がある。
この辺りを省いていたのは、質問してるとこをクリアできれば、他は何とかなると思ってるからです。
>> さと さん
RANDの間違いです。=RAND() だと同じ項目は同じ乱数になるので
=RAND()*(20－1)+1
としました。
これなら×が何個あっても違う乱数になるので
>> アッカリ〜ン@_@….,….,…😅 さん
趣味に付き合って頂き恐縮です（笑）とりあえず、想像以上に難い事に挑戦してるて事はよくわかりました
AIをインストールしてみようとは思ってますが、多分、私にはまだ使いこなせないでしょう😭
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
再度確認です。ランダム割り当ての時に以下の4つの条件があるという事ですね？【1】固定勤務
＞A～E の人だけやる勤務、FGHの人だけやる勤務
これは、ランダム割り当てから除外するという意味であって、固定とは限らず日々変動するのですね。
別表で管理されて、月初は$BC8、月初以外は$BD8の値を反映させるのですね。
【2】夜勤明け
＞夜勤明けは勤務しない
これが以下のようなルールで、ランダム割り当てから除外するのですね。
前日→当日
"管"→"×"
"/1"→"×"
"2/2"→"×"
"3/3"→"×"
"4/4"→"×"
(まだ続く)
【3】休み
休日申請が別であって、夜勤明けXとは別に休日▲を割り振るのですね。それがセルAO〜ASに入っていて、ランダム割り当てから除外ですね。
【4】人別にやらない勤務形態がある
＞AさんとBさんは『へ』と『と』の勤務はやらない
＞GさんとHさんは『い』と『ろ』の勤務はやらない
これが｢人別｣に設定されているのですね？
(上のコメントの画像には未実装)
【5】ランダム割り当て
＞それ以外の人にランダム抽出するように組み込む
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
＞=RAND() だと同じ項目は同じ乱数になるのでえっ同じ乱数にはならないと思いますが…
仮に同じに見えたとしたら、セルの幅が狭くて四捨五入されているだけとか？
＞*(20－1)+1
って何の数字なのでしょうか？何か私の知らない何かの技がある？？
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
AIを入れる必要はないですよ。下のURLで質問をしたら良いだけです。
https://www.bing.com/chat?setlang=ja-jp
例えば
｢excel vbaで、勤務携帯一覧から、ランダムに勤務表を作りたい｣
これらは誰かが固定勤務で割り当て済みだった場合、ランダム割り当ての人には割り当てないように除く必要があるのですね？
■固定勤務
/1,/1
1/1,1/1
2/2,2/2
3/3,3/3
4/4,4/4
5/5,5/5
11/11,11/11
1/,1/
2/,2/
3/,3/
4/,4/
5/,5/
11/,11/
/2,/2
/3,/3
/4,/4
/5,/5
/11,/11
>> blue777 さん
元々このデータは、AとBの選択肢が2個ずつで、これを先に選ばせるようになっていて、AとBで、「あ」と「う」を選んでしまうと、C～Eで選択肢が一気に少なくなって、選ぶものがなくなってしまう、という感じで、面倒そうなのを考えて作ってみたデータです。担当者A～Eで、業務あ～お、の選択肢の初期状態がどういうときに、こういうことが起きるか、難しそうなデータを募集中です！
今のところ、一番選択肢の少ないものから決めていくというのは、そのままですが、そこで、決めようとした担当者の選択可能業務が、現時点でそれ以外の担当者が誰も持っていなかった場合、乱数ではなく、その業務を選択する、というように変えようと思っています。
これで上記の３つのNGは回避できますが、これをまたすり抜けるデータが存在するのでしょうか？
>> さと さん
基本形で、決められたアルゴリズムで解けるのか、解けないのかが分かっていないと、ここに色々な条件を加えて、割り当て不可でバグった時に、追加した条件の部分が悪くてバグっているのか、元々のアルゴリズムで起きているかが分からないので、はっきりさせておきたいというのはありますが、そもそも、パズルを解いているような感覚で楽しんでいるのと、VBAやExcel関数の勉強をしているだけですので、あまり期待しないでください。(^_^;)
まぁ、今のアルゴリズムでも、1000回くらい回せば、だいたい分かるので、割り当てできたものの中から、適当に選ぶ、でもいいかもしれません。PCで、1000回回しても1秒とかでした。
また、選択肢の少ないものから決めていく、というのを止めて、決めていく担当者の順番もランダム、業務もランダムというアルゴリズムで、1000回、回したときに、どんな結果になるんだろうというのを試してみたい気はします。
割り当て不可のパターンは、たくさん出てくると思いますが、逆に、今のアルゴリズムでは出て来なかったパターンとかも出てきたりして‥？
>> アッカリ〜ン@_@….,….,…😅 さん
＞決めようとした担当者の選択可能業務が、現時点でそれ以外の担当者が誰も持っていなかった場合、乱数ではなく、その業務を選択する、というように変えようと思っています。すごい、そこまでする！？
他の人の選択肢まで考慮しながら選定するのですね。
ちょっとお遊びで作って見ました。ここに見えている範囲以外にシートはありません。
式が入っているのは、K、L列だけです。（L列は、割り当て結果が選択可能の中にあるものかをチェックするためのもの）
ヒント：循環参照、反復計算
>> アッカリ〜ン@_@….,….,…😅 さん
EXCELのオプション＞数式 設定で、反復計算機能のチェックボックスがありますね。これをＡ１セルでＯＮにして、Ｋ１セルの回数反復計算させてるんでしょうか？？（全然わからないけど）
チェッさんの画像を元に作りました。Googleドライブにアップしているので、絶対ダウンロードして見て下さい（チェッさんへ）
https://docs.google.com/spreadsheets/d/1-LvCTQ0OV-3E6437oEmFjBd_i_v6_hmq/edit?usp=drive_link&ouid=103999302028150044143&rtpof=true&sd=true
計算方法は、アッカリーンさんの３番目？のやり方かな。
１．選択肢の少ない人から順にランダム割り振り
２．割り振りするごとに、選択肢が変動するので、その都度一番少ない人へ割り振る
※最後に出た、他の人の選択肢を見ながら選択する、という機能は付けていません。
-----
チェッさんの既存の表のレイアウトのまま（画像見て想像で）、
上の方の勤務表は、チェッさんの式のまま、固定勤務、休日、夜勤明け、だけ反映し、ランダム割り当て対象は空白になっています。
下の方の勤務表は、ランダム割り振り後になっています。
ランダム割り振り自体は、別シートで計算しています。
次のコメントにランダム割り振りのシートの画像を載せます。
上図が、ランダム割り当て専用のシートです。１日分の割り当てするのに、３７列使っています。
１日１人割り当てするのに２列ずつです。
できない勤務形態の表が、G:H列です。
以下の登録をしています。
「2、AさんとBさんは『へ』と『と』の勤務はやらない」
「3、GさんとHさんは『い』と『ろ』の勤務はやらない」
計算方法は、
【１】１人割り当てごとに、その人の除外勤務形態と、既に他の人へ割り当て済み勤務形態を、VSTACK関数で上下にくっつけて、一つの配列にしています。除外配列です。
【２】除外配列の１行ずつを、勤務形態表からFILTER関数で排除しています。
【３】排除後の、勤務形態選択肢から、RANDBETWEEN関数でランダム選択しています。
【４】１人選択した後に、再度全員の選択肢の数をカウントし、一番選択肢の数が少ない人に、次割り当て操作をします【１】番に戻る。
この繰り返しを、人数×日数分行っています。
この計算結果を、元の勤務表シートが参照しています。
文字数エラーで、式が貼れない…２コメントに分けます。
M2=IF(AND(L2<>0,L2=SMALL(L$2:L$20,COUNTIF(L$2:L$20,0)+1),COUNTIF(L$2:L2,L2)=1),
LET(勤務表,$B$2:$B$19,
除外,UNIQUE(VSTACK(FILTER(K$2:K$20,LEN(K$2:K$20)>0,""),FILTER($H$2:$H$200,$G$2:$G$200=$J2,""))),
選択肢,FILTER(勤務表,
(勤務表<>IFERROR(INDEX(除外,1,0),""))
*(勤務表<>IFERROR(INDEX(除外,2,0),""))
*(勤務表<>IFERROR(INDEX(除外,3,0),""))
*(勤務表<>IFERROR(INDEX(除外,4,0),""))
*(勤務表<>IFERROR(INDEX(除外,5,0),""))
*(勤務表<>IFERROR(INDEX(除外,6,0),""))
*(勤務表<>IFERROR(INDEX(除外,7,0),""))
*(勤務表<>IFERROR(INDEX(除外,8,0),""))
*(勤務表<>IFERROR(INDEX(除外,9,0),""))
*(勤務表<>IFERROR(INDEX(除外,10,0),""))
*(勤務表<>IFERROR(INDEX(除外,11,0),""))
*(勤務表<>IFERROR(INDEX(除外,12,0),""))
*(勤務表<>IFERROR(INDEX(除外,13,0),""))
*(勤務表<>IFERROR(INDEX(除外,14,0),""))
*(勤務表<>IFERROR(INDEX(除外,15,0),""))
*(勤務表<>IFERROR(INDEX(除外,16,0),""))
*(勤務表<>IFERROR(INDEX(除外,17,0),""))
（次のコメントへ続く）
*(勤務表<>IFERROR(INDEX(除外,18,0),""))
*(勤務表<>IFERROR(INDEX(除外,19,0),""))
*(勤務表<>IFERROR(INDEX(除外,20,0),""))
*(勤務表<>IFERROR(INDEX(除外,21,0),"")),""),
INDEX(選択肢,RANDBETWEEN(1,ROWS(選択肢)),1)),IF(L2=999,K2,""))
●最初のIF文は、一番選択肢の数が少ない人の内上の方の人を対象とする判定。
●LET関数で勤務形態表と、除外表を定義する。
●除外表は「できない勤務形態表」G:H列から該当の人の行を選択
＋既に他の人が「選択済みの勤務形態」左隣の列 を除外表に合体。UNIQUE関数で重複を排除。
●勤務形態表から、FILTER関数で除外表の１行ずつを除いて行き、選択肢とする
●除外済みの選択肢から乱数でランダム選択する
そのままコピペで使えるように、チェッさんの表と行・列番号を合わせています！
再計算はF9で出来るので、自動計算OFFっておくのが良さそうですね。
>> さと さん
そうです！割り当てされたものが、選択可能のどれかと一致しない、または、割り当てされたもの５つの中に２つ以上ある（重複している）場合、再度、乱数で新しい値を出すようにしてます。
K1は、K1+1になっていて、再計算された回数が分かりますが、これがないと再計算が行われないようです。元々、数値演算で収束するまで再計算する、ということで、数値セルの変化率を見ているんでしょうか？そんな設定がありますよね。
なんか、CとEしか、「う」にならず、強制的にDに「う」を入れても割り当てが完了しないので、なにか内部的な計算順序でうまくいかないのかなぁと思ったら、Eの選択可能に「お」が抜けてました。これを入れると、Dにも「う」が割り当てられました。(^_^;)
>> さと さん
LET関数なんていう便利そうな関数があるんだ、と思ったら、FILTERやUNIQUE関数と同様、Office365でないと使えないんですね。うちは、Excel2019です。(>_<)
チェッさんの2021はOffice365なのだろうか。違ったら使って貰えないですね。
別の関数で代用しないと。
COUNTIF(K4:K8,K4:K8&"")
検索条件に範囲を持ってくることで、その１つ１つを検索できるようです。&"" が付いているのは、セルの内容を文字列として扱うため。結果は、配列になる。セルに貼ると1つ目しか表示されない。
(K4:K8<>"---")
領域に対して、"---"でないセルがTRUEになる配列が結果として返る。セルに貼ると1つ目しか表示されない。
この２つを掛け合わせると、FLASEが0なので、"---"が入っているセルのCOUNTIFの結果だけが0になる、配列になる。
=LARGE(INDEX((K4:K8<>"---")*COUNTIF(K4:K8,K4:K8&""),0),1)
これで、"---"のセルを除く、重複している数の最大値、が1つのセルの式で求められるようになりました。これが1以下なら重複したものがないということですね。
まぁ、セルを1列使えば、できますけどね。
配列は、セルに貼ると先頭しか表示されないので、確かめたい時は、
=TEXTJOIN(",",FALSE,INDEX((K4:K8<>"---"),0))
としないといけないようです。
INDEX(xxx,0)がいらないときもあるみたいですが、内部処理の違いとのことで詳しいことはよく分かりませんでした。
>> さと さん
ごめんなさい、バグっていたので修正しました。修正箇所は、自動割当てシートの方の、選択肢の数が一番少ない人から順に割り振る条件です。全員が割り振り済みになると２回割り振ってしまう不具合発生してしました。既に割り振り済みの人は対象外にする条件を追加しました。
図の上表と下表が微妙に違うなあと思ったら、バグでした。Googleドライブの方も直したものを再アップしました。URLは同じです。
https://docs.google.com/spreadsheets/d/1-LvCTQ0OV-3E6437oEmFjBd_i_v6_hmq/edit?usp=drive_link&ouid=103999302028150044143&rtpof=true&sd=true
こりゃOffice365契約しないと？(^_^;)
>> アッカリ〜ン@_@….,….,…😅 さん
私も今回アッカリーンさんのBINGの回答を聞くまで知りませんでした。が、数年前バージョン2021になってからか、スピルで戻ってくる(表が表示される)ことに驚いて、今は慣れました。
最初の1行だけなのは、2019だからかも？違ったらごめんなさい。
>> アッカリ〜ン@_@….,….,…😅 さん
＞選択肢の少ないものから決めていく、というのを止めて、決めていく担当者の順番もランダム、業務もランダムというアルゴリズムで、1000回、回したときに、どんな結果になるんだろうというのを試してこれをやってみました。
割り当てできないパターンが増えてました。さすがに、これは使えないですね。選択肢が多くて、適当に選んでも割り当てできてしまう場合はいいですが。
>> アッカリ〜ン@_@….,….,…😅 さん
1人それぞれ、1個ずつ別々のできない業務があった場合は、こんな感じ。ランダムだと、こんなにひどいんですね。A. 決定する従業員をランダムで決め、タスクは選択肢内からランダムで決める
→割り当て不可が多すぎてダメ
B. 決定する従業員を、選択可能なタスクが少ない従業員（同数は最初に見つかったもの）を決め、タスクは選択肢内からランダムで決める
→当初のアルゴリズム。まぁまぁよい。
C. 決定する従業員を、選択可能なタスクが少ない従業員（同数は最初に見つかったもの）を決め、その従業員の選択可能タスクの中で、他に誰も持っていないタスクがあれば、それに決める
→★これをやってみました
さらに、
D. 決定する従業員を、選択可能なタスクが少ない従業員から決めるが、タスク数が同数であった場合は、他に誰も持っていないタスクを持っている従業員を選択し、その従業員の選択可能タスクの中で、他に誰も持っていないタスクに決める。
E. さらに選択肢が同数で、例えば、
A が あ:2 い:1
B が う:1 え:1
C が あ:2 お:2
...
全体数1のタスクの数が違ったら、どうするかですね。
AかBか、どちらか優位かはよく分かりません。
Aなら、「あ」を持っている人があと1人いるので、こっちを選んだ方がいいように見えますが、Bは、いつ選んでも、片方が消えてしまうので、どちらでも結局同じとか。。
どちらかというと、その前に選んだことによって、全体数1のタスクが２つも現れないように、事前選ぶ、みたいなことやらないと、意味がないのかも。
そもそも、全体数1を2つ持つ状況になるデータの組み合わせはまだ作れていません。作れるのかな？
>> アッカリ〜ン@_@….,….,…😅 さん
C. は、こんな感じになって、割り当てできないパターンは出ませんでした。なので、この先を進めても、そのアルゴリズムが検証できないので、検証するためのデータを作らないといけないけど、もう、いいか。(^_^;)
>> アッカリ〜ン@_@….,….,…😅 さん
制限が多過ぎますねもっと少なければ、カンペキにこだわらず何回か抽出を繰り返して条件を満たすものを選べば良いんですが
この条件の多さだと『たまたま全てを満たす』物を期待するには無理がある。
それこそblue777さんの全通り抽出からの絞りというアプローチの方がよほど現実的です
〉〉アッカリ～ンさん
〉〉さとさん
時間を割いて頂きありがとうございました
コメントが多くなり過ぎて、収拾つかなくなってきたので締め切ります
疑問点は直接メッセージを送らせて頂きます
あと、VBAに挑戦してみようかなと思ってます。ありがとうございましと