[[20201117181651]]　『それぞれの希望に基づいたグループ分けを行いたい』（カイト）　ページの最後に飛ぶ

『それぞれの希望に基づいたグループ分けを行いたい』（カイト）

40人のデータから、各グループにA,B,C,D,Eの振り分けをさせた
ランダムなグループを8つ作成する方法を模索しております。

元のデータは、1列目に名前、2.3列目にそれぞれの第一,第二希望が記載されております。

１名前　第一　　　第二
２ああ　　B　　　　 C
３いい　　D　　　　 A

これらのデータを別のシートに、第一希望をそれぞれ優先で、
A,B,C,D,Eが必ずバラバラになり、かつ同じ人が複数のチームに含まれないように分ける方法はどのような関数を用いればよろしいでしょうか？

また、40人のデータがまだこちらの手元にはないので、第二希望までを考慮した
場合ではいずれかのアルファベットの振り分けが足りない可能性もあります。

そうなってしまった場合の対処もよろしければ是非ご協力をお願いしたいです。
どなたかご助力の方是非よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

追記
元のデータの情報に変更点がありましたので追記させていただきます。
元のデータは第一から第三希望のアルファベット分け、それに追加で1～５の個人評価点も追加されることとなりました。
１名前　第一　　　第二　　　第三　　　点数
２ああ　　B　　　　 C　　　　 A　　　　 4
３いい　　D　　　　 A D 2

評価点の平均値を優先としかつ、点数が低い人の希望を優先とした各希望に基づいたグループ分けを行いたいと考えております。
非常に煩雑な条件ですが、よろしければご助力の方お願いします
（カイト） 2020/11/17(火) 18:49

 過去ログのなかにある
 https://www.excel.studio-kazu.jp/kw/20200418102717.html
 が参考になるでしょう。

 いわゆる「クラス編成問題」として知られる問題ですが、
 線形計画法を適用できることが知られています。

 【使用変数】
 メンバ i が グループ j に割り当てたとき x(i,j)=1 とします。

 【目的変数】
 第1希望がかなったときの満足度を200
 第2希望がかなったときの満足度を100
 第3希望がかなったときの満足度を 50
 それ以外                      -1000
 といった満足度（上記は仮数値）の評価値を設定しておいて、
 その満足度を最大化させればよいことになります。

 【制約条件】
   x(i,j)は0または1
   全jにわたる合計 Σx(i,j) = 1  （各i について）
   全iにわたる合計 Σx(i,j) = 8  （各j について）
 という制約のもとで、全員の満足度の合計を最大にするように
 x(i,j)を決める問題となります。

 また、点数の低いひとには、満足度に加算をすればいいでしょう。（高い点数の人は減算？も）

 ＞評価点の平均値を優先とし
 というのは、
 　　各グループの平均評価点はできるだけ近いものにしたい
 ということでしょうから。
 x(i,j)を使って、各グループの平均評価点を求め、
 それの上限下限を指定して、制約条件に追加すればよいと思います。

 ソルバーは変動させる変数が200個ですので、ちょうどギリギリセーフのようです。
 トライしてみて下さい。

（γ） 2020/11/18(水) 01:07

文章がおかしかった。
ソルバーは変動させる変数の個数の上限が200個だそうです。（確認はしていませんが。）

なお、ソルバーは戦略が３つありますが、
シンプレックス LP　を選択するとよいです。
割と綺麗な結果がでました。

時間がとれれば、もう少し詳しく書く予定です。
（γ） 2020/11/18(水) 15:09

 質問者からコメントがまったくないので、もう興味を失っているんでしょうか。
 何か一言くらいあってもよいと思うが。
 まあ、折角トライしたので、閲覧者への参考となるよう、メモしておきます。

 まず、ソルバーを使うには、
 ・目標とする変数と、
 ・それに影響を及ぼす変数、
 ・両者の関係
 を、シート上で明確に定義する必要があります。

 そのためには、
 ・各人の希望に沿えているかどうかの「満足度」を定量的に決める必要があります。
   （もちろん勘というやつです）
 そのうえで、制約条件がありますから、全員の第一希望が叶うわけでもないので、
 ・「各人の満足度の合計」を目標変数とし、
 ・その目標変数を最大化する組み合わせをソルバーに調べさせることになります。

 まず、ワークシートの設計です。
 <<Sheet1>>
       A     B     C     D     E     F     G     H     I     J     K     L     M     N      O    P     Q
 1行                                                                   200   100    50  -1000    5    30
 2                                                                                                     
 3                                                                       満足度                              
 4     氏名  第一  第二  第三 点数   G1    G2    G3    G4    G5          1     2     3     4     5    結果満足度
             希望  希望  希望
 5     P1    1     2     3     10    1     0     0     0     0     1   200   100    50 -1000 -1000    200
 6     P2    2     5     1     4     0     0     0     0     1     1    80   230  -970  -970   130    130
 7     P3    4     3     5     3     0     0     0     1     0     1  -970  -970   130   230    80    230

                   ---------------------- (中略）---------------------------

 42    P38   1     2     4     7     1     0     0     0     0     1   200   100 -1000    50 -1000    200
 43    P39   1     4     3     10    0     0     0     1     0     1   200 -1000    50   100 -1000    100
 44    P40   2     5     4     6     0     1     0     0     0     1  -1000  200 -1000    50   100    200
 45                                  8     8     8     8     8                                       7800
 46
 47                            平均点 5.5  5.125 5.625 5.5  5.375

     ■前提
     A5:A44  氏名
     B5:D44  各人の第一希望から第三希望
     E5:E44  各人の点数

     ■グループの割当 
     F5:J44 ・・・ 各人のグループへの割当（ソルバーが試行に使用）
                   例：　P1さんにG1を割り当てると、
                         F5セルには 1を設定し、他の G5:J5は0 とするものとします。
     K5:K44 ・・・ 計算式を埋め込む（ K5 = SUM(F5:J5) 以下同じ ）。
                   各人ごとに、どれか一つのGを割当ることを確認するための項目です。

     ■平均点の制約のための計算                                                              
     F47:J47・・・  F47: =SUMPRODUCT($E$5:$E$44,F5:F44)/8    横にコピー。
                   各グループに割り当てられた人たちの点数の平均点です。

     ■満足度計算                                                                
     L1:Q1  ・・・ 満足度計算のための係数群。
     L5:P44 ・・・ 割当に伴う満足度。
                   （ここでは、例として、
                     第一希望なら200、第二希望なら100、第三希望なら50。その他は-1000。
                     点数が5点以下なら一律30を加算することとしました。）

                   L5セル  =IF(L$4=$B5,$L$1,IF(L$4=$C5,$M$1,IF(L$4=$D5,$N$1,$O$1)))+IF($E5<=$P$1,$Q$1) 
                   その他のセルにはこれをコピーペイストします。

     Q5:Q44 ・・・ 実際の割当結果による満足度。
                  Q5セル   =SUMPRODUCT(F5:J5,L5:P5)
                  縦にコピーペイスト
     Q45    ・・・全員の満足度の合計値（=SUM(Q5:Q44)）

 ■<<ソルバーの設定>>
 まず、ソルバーのアドインを使えるようにします。（ネットで調べてください。）
 基本的な使い方も調べてください。

 (1)目的セルの設定   $Q$45             (全員の満足度の合計値） 
                     「最大値」を選択
 (2)変数セル         $F$5:$J$44       （各人のグループ割当）
 (3)制約条件の対象
        $F$5:$J$44    bin    と設定   （0か1の値に限定する意味です）
        $K$5:$K44    =   1   と設定     各人にはどこかのグループを割当る
        $F45:$J$45   =   8   と設定     グループは8人
        $F47:$J$47   ≧  5.1 と設定    (全員の平均値が5.43だったので目の子で下限を設定）
        $F47:$J$47   ≦  5.7 と設定    (同上）

 (4)制約のない変数を非負数とする　にチェック
 (5)解決方法の選択
        「シンプレックスLP」を選択

 「解決」ボタンをクリック。
   　→計算開始。
   　→条件を満たすものが表示されるはずです。

  もし、上手くいかない場合、
  満足度を決めるためのパラーメータ（L1:Q1セル）を色々変えて見て効果を検証してみてください。

 ■ちなみに、テストに使ったサンプルは以下でした。（ランダムに発生させました）
   （シートにそのままコピーペイストしてください）

 氏名	第一希望	第二希望	第三希望	点数
 P1	1	2	3	10
 P2	2	5	1	4
 P3	4	3	5	3
 P4	1	4	2	7
 P5	1	4	2	7
 P6	3	5	2	1
 P7	2	4	5	1
 P8	4	5	2	2
 P9	3	1	2	6
 P10	2	5	1	5
 P11	5	1	2	2
 P12	4	3	2	3
 P13	2	3	1	4
 P14	3	4	2	9
 P15	5	2	4	8
 P16	4	5	2	7
 P17	3	5	1	3
 P18	2	4	1	1
 P19	3	4	2	4
 P20	1	3	5	5
 P21	4	1	3	8
 P22	2	1	5	10
 P23	4	1	2	3
 P24	2	1	3	2
 P25	1	5	3	10
 P26	3	2	4	7
 P27	4	5	1	6
 P28	2	1	3	4
 P29	1	4	5	4
 P30	2	3	4	6
 P31	4	3	5	8
 P32	2	1	4	9
 P33	3	4	5	1
 P34	3	5	2	7
 P35	1	4	3	2
 P36	3	2	5	5
 P37	3	1	4	10
 P38	1	2	4	7
 P39	1	4	3	10
 P40	2	5	4	6

（γ） 2020/11/20(金) 00:08

ほんの通りすがりですが、ソルバー初心者の自分にとって非常に勉強になりました！
満足度計算、汎用性があって使えそうな匂いがプンプンします・・・

いつか、したり顔でお披露目しようと思います。ありがとうございます！
（ヒックル） 2020/11/20(金) 09:42

早速にコメント頂き、ありがとうございました。
お役に立てれば幸いです。

（γ） 2020/11/20(金) 15:45

コメント返信：

[ 一覧(最新更新順) ]

[[20201117181651]] 『それぞれの希望に基づいたグループ分けを行いたい』（カイト） ページの最後に飛ぶ

[[20201117181651]]　『それぞれの希望に基づいたグループ分けを行いたい』（カイト）　ページの最後に飛ぶ