数値の相性をExcel関数で算出したい

質問

単純に言うとミニロトの過去の実績を調査中です。
そこで、１が出た時に他の数字（4個)のどれが出ているか、各数字の相性率を算出したいと思い、
色々な関数を試しましたが、なかなかうまく行きません。

例
1 　4 　17 　 20 　30
4 　5 　12 　18 　25
1 　4 　22 　27　 31
5 　9 　20　　25　 21
1 　 4 　 17 　25 　29
１が出た時に4の相性は100%
１が出た時に17の相性は66%
１が出た時に20,30,22,27,31,25,29の相性は33%
その他の数字は０%
(％にこだわりません。　相性が判れば結構です）

Excelシートには、A1:AXに各回の最少数字,各回の次に小さい数字B1:BX,
順にC1,D1,E1,ボーナス数字D1(minilot),F1～AK1に1～31の数字が並び
F2～AK2以降サイクルを入力したデータがあります。

良い方法・良い関数をご存じの方がおられましたら教えて下さい。
よろしくお願いします。

回答No.2

KURUMITO

ご質問のデータがＡ１セルからＥ５セルにあるとします。
初めに表を選択してから昇順での並べ替えを行います。
これでＡ列には1が並んだあとに4、ついで5が並ぶことになります。
そこで1行目に挿入を行って1行目を空の行とします。
したがってご質問のデータはＡ2セルからＥ6セルに表示されることになります。
そこでＧ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ISERROR(IF(MOD(ROW(A1),2)=1,SMALL($A:$A,COUNTIF($A:$A,"<="&MAX($G$1:$G1))+1),$G1)),"",IF(MOD(ROW(A1),2)=1,SMALL($A:$A,COUNTIF($A:$A,"<="&MAX($G$1:$G1))+1),$G1))

これでＡ列の数値が2行ずつに表示されます。
Ｈ2セルには次の式を入力し右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($G2="","",IF(MOD(ROW(A1),2)=0,IF(H1="","",COUNTIF(INDEX($A:$E,MATCH($G2,$A:$A,0),2):INDEX($A:$E,(IF(COUNTIF($A:$A,$G2+1)>0,MATCH($G2+1,$A:$A,0)-1,MATCH($G2+1,$A:$A,1))),5),H1)/COUNTIF($A:$A,$G2)),IF(INDEX($A:$A,MATCH($G2,$A:$A,0)+INT((COLUMN(A1)-1)/4))<>$G2,"",INDEX($A:$E,MATCH($G2,$A:$A,0)+INT((COLUMN(A1)-1)/4),IF(MOD(COLUMN(A1),4)=0,5,MOD(COLUMN(A1),4)+1)))))

これでＡ列での数値ごとに横方向にデータが並ぶとともに偶数の行には上の行での値の出現率(相性度）を表示します。
偶数の行についてはそれらを範囲として選んだのちに％表示のアイコンをクリックすれば100％、33％などの表示に変わります。
これらのデータをさらに並び替えることでご質問に答えることになりますが、ここまでで十分その目的は果たしているものとしています。ご参考になれば幸いです。

回答No.1

Wendy02

こんばんは。

私も、別なものを作ったことがありますが、当面のレイアウトは、無視させてください。

とりあえず、例にしているものだけにします。

A～E　に並んでいるとします。

以下のような数式にすれば、ご希望のパーセンテージが出来ます。
ただ、これを組み合わせるとなると、かなり難しいような気がします。1に対して、本来は、30個あるわけですね。4に対して、同じく30個となるのでしょうか？

F1:下に9行目までオートフィル・コピー

=SUMPRODUCT(COUNTIF(OFFSET($A$1,ROW($A$1:$A$5)-1,,,5),1)*COUNTIF(OFFSET($A$1,ROW($A$1:$A$5)-1,,,5),H1))

G1:下に9行目までオートフィル・コピー
=F1/$F$1

H1～H9
4
17
20
30
22
27
31
25
29

-------------------------------------------
ところで、この統計方式について、できれば、もう少し仕組みを教えてください。新しい方法でしょうか。いくつか統計を取る方法があるそうですが、私の場合は、出てくる数のグループ分けという方法しか知りません。

補足

お教え頂き有難う御座います。
＞もう少し仕組みを教えてください。新しい方法でしょうか。
とありますが、そんなに凝った仕組みではありません。
又、新しい方法がないか質問したので、あんまり気にしないで下さい。

実際には今度の土日にやってみたいと思います。