エクセルでランダムに隠しホールを設定する方法

質問

エクセルにてゴルフコンペのスコア管理を行おうと考えております。
コンペでは新ぺリア方式で順位をつけるつもりでいますが、
そのために18ホールの中からランダムで12の隠しホールを抽出しなければなりません。
(OUTからPar3 1ホール、Par5 1ホール、Par4 4ホール、INからも同じように
Par3 1ホール、Par5 1ホール、Par4 4ホール抽出する)

それらの隠しホールのスコアの合計の1.5倍から72を引いた値の80%がハンディキャップに
なります。
(ご存知でしたら、いらぬ説明をして申し訳ありません。)

これをふまえまして、まず9ホールの中からPar3、Par4、Par5のそれぞれのホールの抽出。
抽出したホールからランダムでPar3なら1つ選択、Par4なら4つ選択、Par5なら1つ選択し
個人の成績表から上記でランダムに選ばれたホールのスコアを合計し、上述した
新ぺリア方式のハンディキャップの算出計算をする。

といった、ことができればと考えています。

このような操作を行う場合、どのような関数で行うのが良いのでしょうか？

回答No.4

bunjii · ベストアンサー

>もしよろしければ作業用セルも開示していただけますと幸甚です。
作業用テーブルは対象ホールの抽選用です。

H列に各ホールにランダム数を設定しました。
H2=RANDBETWEEN(1,99)*100+A2
RANDBETWEEN関数で1～99までの乱数を発生させ100倍したものにホール番号を加算しました。
ホール番号を加算する理由は選択された乱数からホール番号を抽出できるようにするためです。

I～K列は抽選のセルを配置しました。
I2はパー3のホールから1ホール、J2～J5はパー4のホールから4ホール、K2はパー5のホールから1ホールを選択する式ですがI2のセルをコピーすることで完成します。
I2=MOD(SMALL(IF(($E$2:$E$10=COLUMNS($A$1:C$1)),$H$2:$H$10,""),ROWS($A$2:$A2)),100)
入れ子の内側にあるIF関数は配列の値を返す必要があるので式を入力してCtrl+Shift+Enterで確定します。
IF関数で対象ホールを絞り込みSMALL関数で抽出します。
その後、MOD関数でホールNoを切り出しています。

L列は当選したホールの行に◎印を付加してハンディキャップの計算に使います。
L2=IF(COUNTIF($I$2:$K$5,"="&A2)>0,"◎","")

上記はINの9ホールについての抽選ですがOUTの9ホールも同じ方法で選択できます。

お礼

非常に詳しい説明、ありがとうございました。
ご教授いただきましたもので、ほぼ理想通りのものを
作ることが出来ました。
bunjii様の回答をもちましてベストアンサーとさせて
いただきたく存じます。
誠にありがとうございました

回答No.3

bunjii

>このような操作を行う場合、どのような関数で行うのが良いのでしょうか？
作業用のテーブルを使わないと数式が複雑すぎて難しいようです。
試案ですが添付画像のようにすると分かり易いかと思います。

>それらの隠しホールのスコアの合計の1.5倍から72を引いた値の80%がハンディキャップになります。
隠しホールのみ1.5倍でその他のホールは加算しなくて良いでしょうか？
ハンディは端数を四捨五入しました。

=ROUND((SUM(IF((L2:L19="◎")*1,F2:F19*1.5,0))-E20)*0.8,0)
配列値を扱いますので式を入力後、Ctrl+Shift+Enterで確定します。

尚、B～D列は無くても計算に影響ありません。
H列は抽選用のランダム数で自動再計算のときは何処かのセルに入力等がある度に変更されます。
他の作業用セルについては必要があれば開示します。

お礼

回答ありがとうございます！！！
まさしくこのようなものを作りたいと思っていたものを
現実に提示してくださった感じがいたします。
もしよろしければ作業用セルも開示していただけますと
幸甚です。

回答No.2

kagakusuki

確認したい事があります。
　私は「新ぺリア方式」を行う場合には、outとinのそれぞれから６ホールずつ、Parの合計が48になるようにランダムに選ぶものであり、

>Par3 1ホール、Par5 1ホール、Par4 4ホール

等の様に、Parの数によって選ぶ数を決めたりはしないもの（例えばPar3、Par4、Par5が2ホールずつでも可）と思っていたのですが、違うのでしょうか？

お礼

回答ありがとうございます。

>確認したいことがあります。

わたしの理解では、そのように思っておりました。
しかしご指摘があって調べてみましたら、仰られた
とおりでございました。
ぺリア方式とごっちゃにして覚えていたようです。
お恥ずかしいです。
ご指摘ありがとうございました。

回答No.1

aokii

ランダムならRAND関数でしょうか。

補足

回答ありがとうございます。
隠しホールの選択にRAND関数を使うのだろおいうところまでは
なんとなくわかるのですが、
たてばA列に1～18のホール番号をふり
B列にそれぞれのParのあたい3or4or5が割り振られ
C列以降にそれぞの個人のスコアを記入してあるとします。
A列の1～9まででB列がPar3のものを抽出(Vlookup関数？)し
その抽出したものからランダムに1つ決定。(ここでRAND関数？)
そののち、選ばれたホールの各人のスコアをハンディキャップ
算出用に値として返す。(ここら辺がどの関数を使えばいいのか不明)
同じ作業を、1～9ホールのPar4、Par5、および10～18ホール
でも行う。
値とした返された12個の数値を合計し1.5倍し72を引く
そのあたいの80％をハンディキャップとして出す。
(12個の値さへ出せればこの計算は出来ると思います。)

例えば
A列 ホール数
B列 Par
C列 個人のスコア
とした場合

A B C
1 4 5
2 5 7
3 4 6
4 4 4
5 3 4
6 4 6
7 3 5
8 5 6
9 4 5
(簡便化のため9ホールまで)

上記のようなコースとスコアだった場合
5、7ホールがPar3なので、1～9のなかから5、7を
抽出しどちらかをランダムで選択、仮に5が選ばれたとしたら
5ホール目の個人のスコアを参照し、値4を選択
同じようにPar4のホールから無作為に4つ選択し
仮にそれが1、4、6、9ホールだった場合は値5、4、6、5を
選択。Par5のコースでも同じような作業を行い、
2ホールが選ばれた場合は値7となり、
ハンディキャップ={(4+5+4+6+5+7+(10～18ホールでの値の合計))×1.5-72}×80／100
という計算式の値を出すためにはそれぞれの関数をどのように
組み合わせればよいのかご教授いただきたく存じます。