２つの組合せ

質問

それぞれの生徒がそれぞれ好きな本を好きなだけリストしています。
生徒ID　好きな本
１　　　　A
１　　　　B
１　　　　C
２　　　　B
２　　　　C
３　　　　A
３　　　　B
３　　　　C
３　　　　D
４　　　　B
４　　　　D
・・・
私はこのクラスで本を２冊セットにして売ろうと思っています。どの２冊の組合せが一番売れるでしょうか。という問題です。実際は生徒数が1000人くらいいて、エクセルで表が作れないかと思っています。
よろしくお願いします。

回答No.3

y_akkie

５００種類あるそれぞれの本をB1,B2,...,B100とおきます。
また、B1,B2が好きな生徒の人数をn(B1∩B2)とあらわす事にします。
すると、n(B1∩B2),n(B1∩B3),.....,n(B499∩B500)の中から最大のものを選ぶとするばあい、#2さんがおっしゃるように500C2=12万回計算しなければなりません。プログラムを用いる場合は何とか実現できそうですが、Excelなどの手計算で行う場合はかなり膨大な作業量になる事は言うまでもありません。というよりも想像がつかないくらいの作業量です。
だが、よく考えてみると、５００冊の中から好きな本を選ぶ事はないかと思います。その中で人気があるものの個数は限られる事も予想されます。
また、全通りの計算は常識的に考えた場合は、意味のない計算も多く含まれることも考えられます。例えば、n(Bi)=1明らかに個数の少ないものは、
どんな組み合わせにおいてもn(Bi∩Bj)≦1になる事は言うまでもありません。まず、適当なn(Bi∩Bj)=Mに対して、n(Bk)≦Mになるものは全て候補から除外します。それを繰り返す事で対象を絞る事ができるかもしれません。
効率良く絞込み作業を行う場合は、上位１・２の人気のある本C1,C2の選んで、n(C1∩C2)=Mとし、n(Bk)≦Mとなるものは全て選択肢から外します。
次に、絞り込みによって残ったの本のうちから、n(Ci∩Cj) > n(C1∩C2)=MになるCi,Cjを探し、n(Ci∩Cj)未満になるn(Ck)は全て選択肢から外します。（だが、n(C1∩C2)が最大の場合は言うまでもなく、見つかりません）
この作業を繰り返していけば最終的には人気のある２つの本の組みあわせが見つかるのではないでしょうか。もし上手く絞りきれないのであれば、#2さんが提唱しているやり方を採用するか、そもそも最初から#2さんのやり方を採用した方が良いようにも思えます。また、#2さんの手法と対象の絞り込みを上手く繰り返していけば効率が良い事も期待されます。

お礼

ありがとうございました。
エクセルですべての可能性を網羅するのは大変だということがわかりました。参考にさせていただきます。

回答No.2

hrm_mmm

500冊中２冊の組み合わせだけでも、500*499/2 ≒12万以上なので、
全組調べるのも、結局は徒労ですし、
単純にある１冊を選択した人数上位１０冊で、組み合わせて選択している人数を調べる
とすれば、10*9/2=45通りなら、エクセルの数シートにおさまるかな。

作業１　クロス集計表を作る
１列目に生徒の名前（２行目から1001行目まで）
１行目に本の名前（B列から入力、しかしexcelの制限で１シート256 列までなので、２シートに分けての入力が必要ですね）
各生徒の選択した本の列のセルに1、他は未入力でok（入力作業を減らしましょう）

作業２　単純集計して上位１０冊をチェック
1002行目で単純集計　B列に　=SUM(B2:B1001) と入れる
あとは、このセルをコピーして、1002行目の必要範囲を選択してペーストすれば、各列用の集計が出る

作業３　ある２冊をともに選んでいる人数を調べる
まず対象の本の列名をとりあえず、シート１のB列とシート２のC列とします。
３枚目のシートにA列には生徒名をコピーしておき、B列２行目に
=IF((Sheet1!B2=1) * (Sheet2!C2=1),1,0)
この式をコピーして、同じ列の３行目から1001行目にペースト
1002行目には、作業１のSUM関数式をペースト
これで、シート１のB列とシート２のC列両方を選んでいる人数が解る
隣の列に同様に、別の本を選択した場合の式を入力
この作業を45列作れば、もっとも売れそうな組がだいたい解るのでは？

最初の入力作業がすでに膨大で大変かとは思いますけど。頑張って下さい。

お礼

ありがとうございました。
アプローチとしては、候補をある程度絞りこんでから計算するのがよいということですね。助かりました。

回答No.1

sanori

それだけだと、条件が不足していて解けないと思います。

条件の例

ア：　各々の生徒は、２冊セットのうち、気に入った本が１冊入っていれば買う。

のか、

イ：　２冊セットの２冊とも気に入った本でなければ買わない。

のか、
それとも、

ウ：　上記２つのケースの中間なのか？　（１冊しか気に入らなくても買う既定の確率を与えてやらないといけない）

アであれば、あまりにも話は簡単で、

Ａという本が何冊売れるか？　＝　表の中のＡの個数

人気（表の中の個数）の１位、２位の組み合わせが最も売れる。

現実の世界は、アでもイでもなくて、ウだと思います・・・

補足

説明不足ですみません。イのケースです。実際には、本の種類も500冊くらいあります。