VBAで順位の期待値を出したい。

質問

VBAを使おうと思いましたがさっぱりわからず困っています。やりたい事はA1からP1の16セルに順位の期待値を出す事です。方法はこうです。まず1から16までをA1からP1までに重複しないようにあてはめ、全組み合わせの平均を出します。そうすると(8、5)、(8、5)、・・・となるはずです。次に大小の条件を付けます。例えばAがBに勝ったとしてA1<B1という条件を付けます。これだけで組み合わせが半減し、平均がA1から(5、67)、(11、33)、(8、5)、(8、5)、・・・となるはずです。さらにどんどん大小の条件を付け、組み合わせを絞り込みます。それで平均を出します。大小条件は簡単に入力出来た方がいいです。これらを可能にするにはどうしたら良いでしょうか？

回答No.5

Wendy02

>その試合の組み合わせと最終的な順位を付けるのに使わせていただきます。
以前も、ここのカテゴリで、何度か、順位のつけ方について、話が出てきています。

>全組み合わせ全部ではなく順位の期待値だけ出ればこの問題は解決されます。
「期待値」という言葉を使うと、意味が違ってしまうように思いますので、あえて、こちらでは使いませんが、全部の順位を決める方法ということですね。カルタというのも、一対一の対戦で、もちろん、得点もあるわけですね。トーナメントにするので、1位2位が決まるというわけですね。

バレーや野球などに使われるルールがあります。それぞれの得点のつけ方は微妙に違います。協会独自の順位のつけ方もあるようです。昔は、もう少しわかりやすいサイトがあったような気がしますが、今、インターネットでは分かりやすい内容が出てきません。それでも、例えば、トーナメント方式-スイス式トーナメントの方式（参考URL)は、Excelで組めるはずです。ただし、VBAはほとんど必要はありません。

参考URL：

http://ja.wikipedia.org/wiki/%E3%83%88%E3%83%BC%E3%83%8A%E3%83%A1%E3%83%B3%E3%83%88%E6%96%B9%E5%BC%8F

お礼

情報ありがとうございます。カルタ大会は団体戦で一対一ですが試合の組み方は僕が求めたい数値の1位と2位、3位と4位、5位と6位、・・・と組んで試合をする予定です。同じ場合は試合前にしたくじ引きで順番を決める予定です。試合をしないチームは16チームの場合出ません。おそらく4試合しかしませんが僕の求めたい数値で1と16のチームが出れば今度は2位と3位、4位と5位、・・・とひとつずつずらしてやるのが理想だと思っています。これで上手く行くかどうかは確かめる予定です。僕の求めたい数値は1位から16位まで実力差が均等である事を前提とさせていただいてます。勝ち負けの結果のみ使います。イメージとしては1gずつ違うおもり16個を8つの天秤に2つずつ計16個全部乗せ、4回でおおよその順番を決めるような感じです。期待値になると思ってましたがわかりづらい表現だったのかもしれません。質問の中にいい順位の決め方があるか検索しておきます。スイスのやつは僕も検索して見つけた事があります。でも僕のの正確さと全順位にバラツキが出る魅力には勝てなかったようです。

補足

わかりづらいという意見があったためなるべくわかるような表現で新たに質問を加えました。「VBAを使って全おもりの重さの期待値を出したい。」で検索して下さい。

回答No.4

Wendy02

まず、VBAは別のアプリケーションで、、ExcelやWordなどとはまったく違った機能と使い方だと思ってください。

掲示板では、あくまでも、ご質問者さんの指示に基本的には従っていくことが多いですが、情報が足りません。解決に必要な、疑問の思う部分、出してみました。

・基本的なことですが、これは、何をしていらっしゃるのですか？（賭け事でしたら、「想像に任せます」でもよいですが、）ただ、何か考え方そのものの、肝心な部分が書いていないままに、それがこちらも分かっていることが前提で話が進められているようです。

　今回、期待値というのは、名目上だと思います。元の金額や数量が必要です。もしかしたら、ただ、数学的な問題で、16項目ですと、VBAでも、かなり難しいような気がします。理由は、最後に書きます。

・以下を読んでいたので、この延長だと思い、ここの数字は、得点だと勘違いしました。

「題名：条件を付けて組み合わせを絞って平均を出す関数は？」
それは、不等号が、「>」「<」と両方を言っていたからです。順位でしたら、「<」だけのはずです。

・以下は、どんな計算式なのですか？
　平均がA1から(5,67)、(11.33)、(8.5)、(8.5)、・・・となるはずです。 (読点では意味が通りませんので、書き換えました。）

もう少し、小さなサンプルを出して、そこで、計算式を見せて、こう求めるのか、話を進めていただくか、他のサイトで、具体的な例を示して、どういう計算式を求めるのか書いていただかないと、「平均値」というものは、こちらの知っているものとは、まったく違いますから分かりません。

・これは何ですか？
　》Aから123、132、213、231、312、321が全組み合わせです。
　Aからという、123とかは、順位のことで、それは「順列」ですか？
　123は、A-1位、B-2位、C-3位ということではないでしょうか？
　しかし、それでは話がつながりません。123とは一体なのでしょう。

・以下の仕組が分かりません。それは、上記が分からないからですが。

》A<Bとすると123、132、231に絞られ、これが半減するの意味です。
》C<Aとすると231のみになり

なぜ、「A<B」とか「C<A」が関係してくるのでしょうか？
A<Bにして、C<Aにするということは、どういう意味なのでしょうか？
何を持って減らしているのか分かりません。

ここから、どう、平均値に結びつくのか、どのような計算式するのか分かりません。前回の質問である程度理解したつもりでいたのですが、まったく違いました。

ここから、6分の1に絞り込む計算ロジックがみえてきません。数式はどういうものですか？

何かのロジックはあるような気がしますが、何一つ、ご説明が理解できません。私などに説明するのは、時間の無駄かもしれません。私に説明しても無駄だと思うなら、そのままにしてくださって結構です。

最後に、16項目の順列なら、20,922,789,888,000通り存在します。
仮に、そこから、6分の1に絞ったところで、3,487,131,648,000通りになります。もちろん、Excel自体では不可能です。

私自身も直感的というか経験的に、もしも、順列の中での平均値なら、それは一回だけのもので、VBAの必要性はありません。しかし、数万回の場合はソルバーを使い、何百万回の計算になる場合は、VBAを使うというのが通例です。数億回の場合は、一端、C++で作ってあげるとかしないと、うまくいかなことが多いです。

また、ここは仕事や学習に関係しているものは回答が付きやすいのですが、賭け事や利殖などに関係に関するものは、今まで、私は、つとめて、それ専門に回答したものの、迷宮入りが多いです。もしかしたら、競馬ですか？競馬には確かに「期待値」は存在しますが、それは、元金総額があるはずです。確率などの数学は、私の課題のひとつですが、今の私には荷が重いです。

お礼

質問ありがとうございます。カルタ大会があるのですがその試合の組み合わせと最終的な順位を付けるのに使わせていただきます。不等号ですが順位が上であるほど何位という数字が小さくなるためややこしくなったのだと思います。計算式ですが単純に17×1/3、17×2/3、17×1/2、・・・です。条件が少なければ大体こうなるなでわかります。123などは1位2位3位をABCに順位の上下の条件が成り立つように当てはめたものです。A<B、C<Aは試合の結果です。小さい方が勝ちです。上位ほど何位という数字が小さくなりますから。1/6に絞り込むというのは全組み合わせから3つの上下の関係が成り立っていない5/6を計算外にまわすためです。全組み合わせ全部ではなく順位の期待値だけ出ればこの問題は解決されます。果たしてどうやって出るかは僕にもわかりません。興味を持ってもらえただけ嬉しいです。

回答No.3

Wendy02

#1の回答者です。

最初に
>VBAについてまだまだ未熟者であるためコピーするだけですむような形で文章を入力してもらえるとありがたいです。

VBAの質問を出した以上は、ご自身の責任の中で、他のWebサイトなり書籍などで調べてください。分からない所は聞けばある程度は教えますが、最初から、手取り足取りというようなことはしていません。
まったく知らない人でも、VBAの登録などの基礎は、2・3日もあれば覚えられます。一週間もあれば、記録マクロ以上のことが可能になります。最低限の努力はしてください。1ヶ月で、相当のことが出来るようになる人もいます。

さて、
>全組み合わせからAがBに勝てばA1<B1が、CがAに勝てばC1<A1が成り立つ組み合わせだけに絞り込み
(これだけで全組み合わせを1/6に絞り込めると思います。)、

1/6　?　どういう意味でしょうか?　頭の中で考えていらっしゃることが、うまく伝わっていないようです。6分の1ということは、各々の対戦が6個あり、平均値が6個あることに対するもののはずです。

こんな話を繰り返しても目処が立ちそうにないので、#1の表でシミュレートしてみたらどうでしょう。

マクロ以前の問題がクリアされていません。もちろん、これらの対戦が、連続的に発生したものとしないと意味がありません。また、勝ち抜き戦いではないということになります。

あくまでも、勝ちに対して、点を数えるものとします。ただし、数の多いほうを勝ちとします。
「<」、「>」 に関しては、オプションは設けるものの、本来、勝ちを拾うなら、あくまでも、片方だけのはずです。

例：
　A　B　C
　1　2　2
　1　3　5
　2　1　3
　3　2　1

=========
AxB で、Aの勝ちは、
2, 3 =AVERAGE(2,3) 答え.. 2.5
AxC で、Aの勝ちは、
3 = AVEARGE(3) 答え.. 3
平均値　2.75
---
BxC でBの勝ち
2 =AVERAGE(2) 答え.. 2
BxA でBの勝ち
2,3 =AVERAGE(2,3) 答え..2.5
平均値　2.25
---
CxA でC の勝ち
2,5,3 =AVERAGE(2,5,3) 答え.. 3.3333
CxB でC の勝ち
5,3 =AVERAGE(5,3) 答え.. 4
平均値　3.6666

そうすると、
A..2.75
B..2.25
C..3.6666

という結果になります。

こういう一覧を作るマクロなら#1のマクロを直せば可能だということです。

お礼

ありがとうございます。VBAは身につけたいと思っています。ちょっと覚える前はマニアの領域みたいなイメージがありましたが何とかなるかもしれません。さて、1/6についてですがABCの3チームに1から3を当てはめるとAから123、132、213、231、312、321が全組み合わせです。A<Bとすると123、132、231に絞られ、これが半減するの意味です。さらにC<Aとすると231のみになり1/6に組み合わせが絞り込まれた事になります。4チームの場合同じ条件をつけると全組み合わせを24のうち2314、2413、3412、3421の4通りになり、同じく1/6になっています。

回答No.2

layy

---------
AvsB AvsC AvsD AvsE AvsF........ AvsP
BvsA BvsC BvsD BvsE BvsF
CvsA CvsB CvsD CvsE CvsF
DvsA DvsB DvsC .............
EvsA EvsB EvsC EvsD ........
............................
PvsA PvsB

別途、
ＶＢＡでということで回答しましたが、
ＶＢＡでやると組み合わせのパターンは出せます。

３チームなら６パターンというのも出せるし、
何個のうち何個選んだときの確立は関数電卓でも使えます。

この組み合わせ結果からどう使うか、でしょう。

回答No.1

Wendy02

マクロを作るのは大した問題ではありません。しかし、現実に、16列から2列を選び出すと、その組み合わせは120個になります。平均値としては、240個になります。しかし、それはただの数値の羅列にしか過ぎません。

>さらにどんどん大小の条件を付け、組み合わせを絞り込みます。それで平均を出します。
とは、どういうことか、そこから、どう「期待値」を求めるのか分かりません。

>題名：条件を付けて組み合わせを絞って平均を出す関数は？
の回答のマクロを出しておきます。

結果：
　A　B　C
　1　2　2
　1　3　5
　2　1　3
　3　2　1

　AB　AC　BC
　2.5　3　2
　1.5　1　1

'注意：AからZまでで、それ以上は現在のコードのスタイルでは不可能です。

''//標準モジュール
Private Const N As String = "<" '大小の設定
Private Const COMBI As String = "AB,AC,BC" '列の組み合わせ

Sub TestCompare1()
　Dim rng As Range
　Dim dSum1 As Double
　Dim dSum2 As Double
　Dim ar As Variant, ArRet As Variant
　Dim i As Long, j As Long, t As Long
　Dim a As Variant, b As Variant
　Dim Lst As Long
　ar = Split(COMBI, ",")
　ReDim ArRet(1, UBound(ar))
　Set rng = Range("A1", Range("A1").End(xlDown).Offset(, UBound(ar)))
　If rng.Rows.Count > 2000 Then MsgBox "セル範囲か不適切のようです。", 48: Exit Sub
　Lst = rng.Rows.Count
　For j = LBound(ar) To UBound(ar)
　　For i = 1 To Lst
　　　a = Cells(i, Mid(ar(j), 1, 1)).Value: b = Cells(i, Mid(ar(j), 2, 1)).Value
　　　If N = "<" Then
　　　　If Val(a) < Val(b) Then
　　　　　dSum1 = dSum1 + a
　　　　　dSum2 = dSum2 + b
　　　　　t = t + 1
　　　　End If
　　　Else
　　　　If Val(a) > Val(b) Then
　　　　　dSum1 = dSum1 + a
　　　　　dSum2 = dSum2 + b
　　　　　t = t + 1
　　　　End If
　　　End If
　　　If dSum1 <> 0 Then
　　　　ArRet(0, j) = dSum1 / t
　　　Else
　　　　ArRet(0, j) = 0
　　　End If
　　　If dSum2 <> 0 Then
　　　　ArRet(1, j) = dSum2 / t
　　　Else
　　　　ArRet(0, j) = 0
　　　End If
　　Next
　　dSum1 = 0: dSum2 = 0: t = 0
　Next
　Cells(i + 1, 1).Resize(1, UBound(ArRet, 2) + 1).Value = ar
　Cells(i + 2, 1).Resize(2, UBound(ArRet, 2) + 1).Value = ArRet
　Beep
End Sub

AからP の組み合わせ(120個）

"AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,
BN,BO,BP,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,
DO,DP,EF,EG,EH,EI,EJ,EK,EL,EM,EN,EO,EP,FG,FH,FI,FJ,FK,FL,FM,FN,FO,FP,GH,GI,GJ,
GK,GL,GM,GN,GO,GP,HI,HJ,HK,HL,HM,HN,HO,HP,IJ,IK,IL,IM,IN,IO,IP,JK,JL,JM,JN,JO,
JP,KL,KM,KN,KO,KP,LM,LN,LO,LP,MN,MO,MP,NO,NP,OP"
使用する時は、繋げてください。

お礼

ありがとうございます。「さらにどんどん大小の条件を付け、組み合わせを絞り込みます。それで平均を出します。」とは全組み合わせからAがBに勝てばA1<B1が、CがAに勝てばC1<A1が成り立つ組み合わせだけに絞り込み(これだけで全組み合わせを1/6に絞り込めると思います。)、全組み合わせのAならAだけの、BならBだけの平均を出すという意味です。その平均が期待値だと思います。あと僕がVBAについてまだまだ未熟者であるためコピーするだけですむような形で文章を入力してもらえるとありがたいです。条件の付け方はAND A1<B1とかである事は何となくわかったのですが。