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

質問

数日前にゴルフの新ぺリア方式のときの隠しホールをエクセルで
ランダムに設定する方法を質問させていただいたものです。

IF関数で対象ホールを絞り込みSMALL関数で抽出し、
その後、MOD関数でホールNoを切り出す方法をここで
教えていただいたのですが、どうしてもうまくいかず、
#VALUE! が帰ってきてしまいます。

簡便化するため以下のように検定してみました。
A列に1-9までの番号。
B列には2-5までの任意の数。
仮にB列の数字が"4"であるものの中から
どれか一つを選ぶという条件を付け、
D列のD2にその番号(1-9)を返す。

というサンプルにいたしました。

しかし、やはりD2の結果には#VALUE!が帰ってきます。
ただ、B2が"4"で有った場合のみ#VALUE!ではなく
数値が表示されますが、この場合も、B列に"4"が
割り当てられている番号だけでなく、A列の番号の
どの番号でも再計算のたびに表示されます。

A列 B列
1 3
2 2
3 5
4 3
5 4
6 3
7 5
8 2
9 4

C列には抽選のためのランダム数を割り当てるため
C2=RANDBETWEEN(1,99)*100+A2

D2には抽選の結果当選した番号を表示するため
D2=MOD(SMALL(IF(B2:B10=4,C2:C10,""),1),100)
と入力しました。

検証していただき、訂正の必要がある箇所がありましたら
ご指摘いただきたく存じます。

回答No.2

bunjii · ベストアンサー

>検証していただき、訂正の必要がある箇所がありましたらご指摘いただきたく存じます。
式はそれで良いのですが入力後にCtrl+Shift+Enterで確定してください。
前回の質問でも説明しましたが入れ子のIF関数は配列を返す必要がありますので普通にEnterで確定するとエラーになります。
抽選数が1つの場合はIF関数で返った数列から1番小さい値のみで良いことになりますので変数にする必要はありません。
2つ以上を選ぶときはSMALL関数で何番目を取り出すかを変数で指定すると同じ列の下の行へコピーすることで入力の手数を省けます。
但し、コピーする場合はセル位置を絶対アドレスで指定する必要があるヶ所に$マークを付けます。
提示の式は応用性に欠けるだけで単独セルに対する式としては正しいと言えます。
式の確定時にCtrl+Shift+Enterの打鍵を忘れないようにしてください。

お礼

前回に引き続き、今回の質問においてもご面倒をおかけし
誠に申し訳ございません。
#VALUE!のエラーが返ってくる理由は、1回目の質問で
Bunjiiさんがすでに記載してくださっておりました。
それを見逃し、首をひねっていた自分を非常に情けなく思います。
Bunjiiさん、MackyNo1さん、本当にご迷惑をおかけ
いたしました。

ご回答いただいた方すべてにベストアンサーとしたいのですが
今回の質問内容と前回の質問にあまりに重複が多く、
引き続きご回答いただいたということで、Bunjiiさんの
回答をベストアンサーとさせていただきたく存じます。

回答No.3

MackyNo1

参考までに。

提示された数式に対する回答はすでにNo1に示した通りですが、Ctrl+Shift+Enterで確定しなくても配列数式と認識させるには、IF関数を使わずに加減乗除した配列にして、それをINDEX関数を利用して通常の範囲とする方法があります。

たとえば例示されたデータなら以下のような数式にします。
（SMALL関数で一番小さい値はMIN関数と同じなので変更してみました）

=MOD(MIN(INDEX((B2:B10<>4)*10000+C2:C10,)),100)

数式の意味は、B列のデータが４以外なら10000を掛けて（4の場合は0になる）、この値とC列のデータを加えた値の中の最小値の下一桁の数字を取得しています。

例示の式はC列に補助列を設けていますが、A列とB列の値だけで該当のデータをランダムに抽出したいなら、以下のような関数にするほうが無駄がなく合理的です。

=LARGE(INDEX((B2:B10=4)*A2:A10,),RANDBETWEEN(1,COUNTIF(B2:B10,4)))

上記の数式の意味は、まずINDEX((B2:B10=4)*A2:A10,)でB列が４の場合はA列の値、それ以外の場合は０の配列(範囲）を取得しています。その配列の中から、大きい順に数えて、RANDBETWEEN関数で「1～B列が４のセルの個数」の値の中からランダムに求められた順位の数値を取得しています。

お礼

先ほどの回答に加え、非常に合理的な提案をしていただき
誠にありがとうございます。
今回は質問内容にある方法でかなりのデータを組み上げて
しまっていますので、ご提案頂きました方法は、今後の
参考にさせていただこうと考えております。
みなさまの理解の深さに驚愕するっともに、自分の理解の
浅さが恥ずかしくなります。
本当にご教授感謝いたします。

回答No.1

MackyNo1

D2=MOD(SMALL(IF(B2:B10=4,C2:C10,""),1),100)

上記の数式は配列数式ですので、入力後Ctrl+Shift+Enterのキー操作で確定する必要があります。
数式の意味は、B2:B10セルが４の場合は、対応する行のC列の値を返し、それ以外の場合は「””」の配列を返しますので、その中で最小値のC列の値の下一桁を取得する式になっています。

一方、配列数式にしない場合は、B列に４が入力されている場合のみ、C2:C10のセル範囲を返しますので、単純にC列の一番小さい数字の末尾が求められることになるわけです（４以外の場合はVALUEエラーになります）。

お礼

非常に詳しい説明ありがとうございます。
おかげでなぜエラー表示が返ってくるか理解できました。