【エクセル】組み合わせの集計方法

質問

すみません、教えてください。。

注文番号、商品区分、（商品名）　というレコードがあったとして、
同一の注文番号における商品区分の組み合わせの集計値を
出したいです。エクセルを使って簡単に出せる方法はないでしょうか？

※つまり、、
スーパーでお客さんが何を一緒に買っているかの傾向を知りたい
ということになります。。

・肉類を買っている人は野菜も一緒に買っている
・酒類を買っている人はお菓子も一緒に買っている
・惣菜を買っている人は冷凍食品も一緒に買っている

説明がいたらなくてすみませんが教えていただけると幸いです。
手動で表作ったらこんな感じなるのかな、というイメージ図を添付します。

回答No.2

kagakusuki

今仮に、注文番号はアルファベット等の文字が含まれてはいない、純粋に数値として扱う事の出来る番号であるものとし、
基データの表の中の「注文番号」と入力されているセルがSheet1のA1セルであるものとし、
添付画像中の右側に写っている表の中で「A」と入力されている2つのセルが、それぞれSheet2のA2セルとB1セルであるものとします。
　又、Sheet3のA列とB列を作業列として使用するものとします。

　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$A$1:$A1,Sheet1!$A1)=1,Sheet1!$A1,"")

　次に、Sheet3のB1セルに次の数式を入力して下さい。

=Sheet1!$A1&Sheet1!$B

　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=SUMPRODUCT((COUNTIF(Sheet3!$B:$B,Sheet3!$A$2:INDEX(Sheet3!$A:$A,MATCH(99^9,Sheet3!$A:$A))&B$1)>0)*(COUNTIF(Sheet3!$B:$B,Sheet3!$A$2:INDEX(Sheet3!$A:$A,MATCH(99^9,Sheet3!$A:$A))&$A2)>0))

　次に、Sheet2のB2セルをコピーして、Sheet2のB2～F6の範囲に貼り付けて下さい。

　以上で完成です。
　尚、Sheet2のB2セルでは縦方向の項目が商品Aで、横方向の項目も商品Aとなっていますが、この様に縦方向も横方向も同じ項目となっているセルに表示される値は、その商品を購入した注文番号の総数（人数？）になっています。
　それから、上記の数式では、基データの行数が多いと、計算負荷が大きくなり、処理に時間が掛かる様になります。
　ですから、もし、元データの行数が非常に多い場合には、Sheet2のB2に入力する数式を次の様に変更しますと、計算の負荷を数分の1に軽減する事が出来ます。

=SUMPRODUCT((COUNTIF(Sheet3!$B:$B,SMALL(Sheet3!$A:$A,ROW(INDIRECT("Z1:Z"&COUNT(Sheet3!$A:$A))))&$A2)>0)*(COUNTIF(Sheet3!$B:$B,SMALL(Sheet3!$A:$A,ROW(INDIRECT("Z1:Z"&COUNT(Sheet3!$A:$A))))&B$1)>0))

　尚、この数式中の

ROW(INDIRECT("Z1:Z"&COUNT(Sheet3!$A:$A)))

という部分は、注文番号の種類が例えば1～4までの4種類の数が存在している場合には、1～4までの数を自動的に生成するために、SUMPRODUCT関数とROW関数を組み合わせて使用しているだけで、行数を表す数が必要なだけであり、Z列である事には特に意味はありません。（ですから、「Z1:Z」の部分を「A1:A」や「IV1:IV」等に変更しても構いません）

回答No.1

mi7443

まず、イメージ図での疑問
１.注文番号4で商品分類Ａ、Ａ、Ｂとあり右側のマトリクスのＡＡ＝１となっていますが、同一番号で同一商品があり得るとしたら、もし番号４の商品分類がＡ、Ａ、Ａだった場合マトリクスＡＡ＝３となるのでしょうか？
２．マトリクスＢＥ＝２となっていますが、左の表からはＢＥ＝１としか読めないように思いますが・・。

上記１．は無視する（一注文番号で同一商品は無いとする）　２．はＢＥ＝１が正しいとして・・・

まず注文番号毎に右側のマトリクスの表を作る。（これはそんなに難しくないですよね）
しかる後に同一フォームの「合計マトリクス」に各欄の合計を求めれば出来ますね。
注文番号毎にシートを分け、最後のシートを合計シートとする事も考えられると思います。
しかし、商品分類がいくつ位になるか？注文番号はどのくらいになるのかで、実際的ではないように思いますし、お役には立てないかもしれないなと思いながら、なにかヒントのでもなればと思い記してみました。