計算結果を条件付き（範囲指定）したいのですが

質問

ｅｘｃｅｌ、初心者です。
乱数で設定した２つのセルのかけ算の答えで、範囲指定（１～１００）し、範囲外の時はその範囲に収まるように再計算をしたいのです。
どうしたらいいですか？

回答No.7

keikan · ベストアンサー

keikan Wrote
>この方式を用いるとエクセルシートには１組のパターンしか出せなくなります。それはシートの再計算による物ですので複数必要であればやり方そのものを換えるか、得られた結果を値とし記録するかになります。

と書いたように、エクセルの再計算はシート単位で行われます。したがって、３０問作られると３０問すべてが１度にすべてが条件に見合うように作成される確立は相当な物でしょう＾＾；；・・・２９問目までうまくいっても３０問目で条件に合わずに再計算されれば１問目からになります。

処理をマクロ内で行い結果をすべてセルに書き出すマクロです。マクロbunnsuuを実行してください。

Public Sub bunnsuu()
Dim i As Integer
Dim bunsi1 As Integer
Dim bunnbo1 As Integer
Dim bunsi2 As Integer
Dim bunnbo2 As Integer
Dim insi As Variant

'RANDBETWEENシート関数がマクロ内で使えないため、代わりに配列変数に初期値を定義。このリストを変更すれば導かれる値が変わります。ただし、要素の数が変更になる場合は乱数発生時の値を変更してください。
insi = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 14, 15, 16, 18, 20, 21, 24, 25, 28, 30, 32, 36)

Range("$a$1") = "分子１"
Range("$b$1") = "分母１"
Range("$c$1") = "分子２"
Range("$d$1") = "分母２"

Range("$e$1") = "約分分子１"
Range("$f$1") = "約分分母１"
Range("$g$1") = "約分分子２"
Range("$h$1") = "約分分母２"

Range("$i$1") = "分子"
Range("$j$1") = "分母"
Range("$k$1") = "約分分子"
Range("$l$1") = "約分分母"

For i = 1 To 30 '問題３０個作成させるためのループ

Do
'乱数再計算ループ
bunnsi1 = insi(Int(Rnd() * (23))) '発生した乱数により配列に定義された値の取得。要素数が２３個の場合（０から２２を取得）
bunnbo1 = insi(Int(Rnd() * (23)))

bunnsi2 = insi(Int(Rnd() * (23)))
bunnbo2 = insi(Int(Rnd() * (23)))

Loop While (bunnsi1 * bunnsi2 > 100 Or bunnbo1 * bunnbo2 > 100) 'かけ算の結果が３桁以上になる間はDo-Loop間を繰り返す。

'条件適合値のセルへの代入
Range("$a$" & i + 1) = bunnsi1
Range("$b$" & i + 1) = bunnbo1
Range("$c$" & i + 1) = bunnsi2
Range("$d$" & i + 1) = bunnbo2

'各分子分母の約分
Range("$e$" & i + 1) = "=$A$" & i + 1 & "/GCD(A" & i + 1 & ",B" & i + 1 & ")"
Range("$f$" & i + 1) = "=$B$" & i + 1 & "/GCD(A" & i + 1 & ",B" & i + 1 & ")"
Range("$g$" & i + 1) = "=$C$" & i + 1 & "/GCD(C" & i + 1 & ",D" & i + 1 & ")"
Range("$h$" & i + 1) = "=$D$" & i + 1 & "/GCD(C" & i + 1 & ",D" & i + 1 & ")"

'分数のかけ算
Range("$i$" & i + 1) = bunnsi1 * bunnsi2
Range("$j$" & i + 1) = bunnbo1 * bunnbo2

'かけ算後の約分
Range("$k$" & i + 1) = "=$i$" & i + 1 & "/GCD(i" & i + 1 & ",j" & i + 1 & ")"
Range("$l$" & i + 1) = "=$j$" & i + 1 & "/GCD(i" & i + 1 & ",j" & i + 1 & ")"

Next i

End Sub

補足

すぐに回答して頂き、ありがとうございます。
難しそうですが、チャレンジしてみます。

回答No.6

imogasi

＃５です。
＃５で「言いかえればvはuで割ってあまりが０でない」と書いたのは、間違いでした。「最大公約数が２以上でない」に訂正します。
下記はVBAでやって見た一例です。エクセルのGCD関数がVBAで使えず苦労しました。
ABCD列にOKのものを上げました。EF列は参考までに最大公約数を上げてます。u,vとx,yのGCDを出してます。すべて１です。
HIJK列に不向きとして捨てたデータを出してます。L列はエラーの種類。
１０００回やって、約１５０がOK、８５０が不向きになりました。掛けて１００以上のケースが出ないのが心配ですが取りあえず上げます。ご参考にしてください。
Sub test01()
k = 1: l = 1
s = Array("", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 14, 15, _
16, 18, 20, 21, 24, 25, 28, 30, 32, 36)
For I = 1 To 1000
u = s(Int(22 * Rnd + 1))
v = s(Int(22 * Rnd + 1))
x = s(Int(22 * Rnd + 1))
y = s(Int(22 * Rnd + 1))
'----
If v = 1 Or y = 1 Then
Cells(l, "L") = "分母１エラー"
GoTo p03
End If
'----
If (u / v) * (x / y) > 100 Then
Cells(l, "L") = "書けて１００以上"
GoTo p03
End If
'----
p02:
Cells(k, "A") = u
Cells(k, "B") = v
Cells(k, "C") = x
Cells(k, "D") = y
Cells(k, "E").Formula = "=gcd(A" & k & ",B" & k & ")"
Cells(k, "F").Formula = "=gcd(C" & k & ",D" & k & ")"
If Cells(k, "E") <> 1 Then
Cells(l, "L") = "ｕ、ｖ既約"
Range(Cells(k, "A"), Cells(k, "F")).ClearContents
GoTo p03
End If
If Cells(k, "F") <> 1 Then
Cells(l, "L") = "ｘ、ｙ既約"
Range(Cells(k, "A"), Cells(k, "F")).ClearContents
GoTo p03
End If
k = k + 1
GoTo p01
p03:
Cells(l, "H") = u
Cells(l, "I") = v
Cells(l, "J") = x
Cells(l, "K") = y
l = l + 1
p01:
Next I
End Sub
その後A-Fでソートして、A+B列が同じもので、C列が同じかどうかチェックして、同一データがないかチェックしてください。
＞「マクロ」がやったことがないので、難しそうですが・・
と言う質問者の方には無駄かも知れませんが、参考になればと思います。

回答No.5

imogasi

＃２です。
●当初質問と、補足と全く違うじゃないですか。自分の頭の中で一杯の部分だけでなく、全体を見とおして、広い立場、他者の目の立場からも考えないと。
●まず哲学なき、問題にとりかかりやプログラム作成は駄目です。
●まず、処理する方針を考えないと。むしろある程度考えたら、自分の考えに拘らず、他にも良い考えはないか、質問するべきです。本件は、組み合わせ論的なところがあり、カテゴリはコンピュタよりむしろ数学の方がふさわしいのではといつも思ってます。エクセルでの処理は、苦手な分野だと個人的に思います。
内容が算数の問題作成だからではないですよ。本件２３の数を４つ選ぶと（他の条件を考えず）２３＾４の組み合わせ順列が有ります。
かくいう本稿も良く判った方から見れば、幼稚で誤りがある惧れは有ります。
●エクセルでは結果により処理を変える繰り返しが不得手です。
出来てもIF文などで、数分岐が精々です。
したがってこの問題は、プログラムに処理させる必要が有ると思います。その中でもとっつき易いのがエクセルVBAです。エクセルが入っておれば、直ぐに取りかかれるから。
●さて使う数は、(u/v)*(x/y)型のU,v,x,yを決める問題として、u,v,x,yの数は１・・３６の内２３個を指定された。
これはベターだと思います。これでu,v,x,yの数は正整数で
有ることが担保されるので有り難い。
しかし素数の多くをなぜ捨てたか疑問？教育的観点から素数があるのも大切では？。
●条件
（１）u,v,x,yの数はこの何番目の数か、を乱数指定して選ぶ
（２）V,yは１は捨てる。３／１のようなのは、みっともない。
（３）u、vは既約とする。言いかえればvはuで割ってあまりが０でない。２／４などはみっともない。
（４）同じくx、yは既約とする。言いかえればyはxで割ってあまりが０でない。
（５）(u/v)*(x/y)の値が１００以上であれば捨てる。
回答では、G.C.Dが出てきているが、（３）（４）でクリアできる。
（５）最終的に同じ組み合わせが出来るかもしれないが、
最後に省く。これは関数で出来る。
（６）問題総数はどうするの？千も万もの組み合わせを作っても仕方がないでしょう。
それで最終に組み合わせの中から、乱数で望みの数（問題の数）だけ選ぶのはどうでしょう。
以上（１）－（５）の条件の数をA-D列にセットするプログラムを出来れば考えてみます。

回答No.4

keikan

＃３です。
＞(2)関数ｇｃｄで約分する方法をとった。
＞　答えに、分子＝○×◇／gcd(○、□)gcd(△、□)
＞　　　　　分母＝△×□／gcd(○、□)gcd(△、□)
＞　と式を作りました。

この方式を用いるとエクセルシートには１組のパターンしか出せなくなります。それはシートの再計算による物ですので複数必要であればやり方そのものを換えるか、得られた結果を値とし記録するかになります。

ちなみに
通分ですが

○／△×◇／□に対して

○◇＿○◇/gcd(○◇、△□）
－－＝－－－－－－－－－－－
△□＿△□/gcd(○◇、△□）

（＿はテキスト整形用なので無視してください）

で、いいと思います。

本題ですが、再計算はマクロ無しでは難しいように思います。

Private Sub Worksheet_Calculate()

If Range("$N$7") > 100 Or Range("$N$8") > 100 Then
'MsgBox "over!!ReCalculate"
Range("$N$7").Calculate
End If

End Sub

ワークシートのWorksheet_Calculateイベントとして作成してください。

計算結果の分子="$N$7"、分母="$N$8"とした場合です。
適時変更してください。

上記イベント形式にするよりも
乱数発生、値の作成、検証、修正までをマクロで一括処理された方が汎用性があり使えるかもしれません。

お礼

ていねいにありがとうございます。
「マクロ」がやったことがないので、難しそうですが、チャレンジしてみます。
また教えてください。

補足

またまたすみません。
やっと先日教えて頂いたマクロ文
　Private Sub Worksheet_Calculate()
　If Range("$N$7") > 100 Or Range("$N$8") > 100 　Then
　'MsgBox "over!!ReCalculate"
　Range("$N$7").Calculate
　End If
　End Sub
を実行することができました。（文の意味はよくわかっていないのですけど）

ただマクロに関する知識がなく分数の問題を３０問作るため、このマクロを
　Private Sub Worksheet_Calculate()

　If Range("$N$7") > 100 Or Range("$N$8") > 100 Then
　'MsgBox "over!!ReCalculate"
　Range("$N$7").Calculate
　End If

　If Range("$N$10") > 100 Or Range("$N$11") > 　100 Then
　'MsgBox "over!!ReCalculate"
　Range("$N$11").Calculate
　End If

…

End Sub

と問題のセルごとに命令文を作りました。
これで自分の「３桁の答えが出る」は解決できたのですが、歳計を何度も繰り返し、１分くらい計算をやり直しています。
　いい方法はありますか？
　できましたら出いいので、教えてください。

回答No.3

keikan

こんな感じでどうでしょうか。
一つ目のセルの乱数を求め、その値をもとに２つ目の乱数の範囲を限定して発生させます。

A1セル＝INT(RAND()*(50-1)+1)
B1セル＝INT(RAND()*(INT(100/A1)-1)+1)

RAND（ｂ－ａ）＋ａでａからｂまでの乱数を発生させます。ＩＮＴは小数点以下の切り捨てを行っています。

Ａ１セルの５０は適当です。あまりこの数字をお聞くすると必然的にＢ１には１が多くなると思います。

かけ算の結果の１０００個サンプルの平均は５０弱でした。
分布的には平均値を中央に持つ、山形になっており
平均値あたりが最も多く１または１００に近づくほど頻度が少なくなっています。

出現頻度を一様にされるのであれば、式を工夫する必要があるかもしれません。
最初の５０の部分に別の乱数（１から１００）を埋め込むとそれらしくなるかもしれません。

=INT(RAND()*(INT(RAND()*(100-1)+1)-1)+1)

補足

すみません。説明不足で。
実は「分数のかけ算」の問題を作っています。
○／△×◇／□で、
(1)それぞれの分母分子を乱数で出すようにした。
　関数「choose」「randbetween」で、３６までの整数のう　ち比較的使われる２３個を乱数でえらぶようにしま
　した。
=CHOOSE(RANDBETWEEN(1,23),1,2,3,4,5,6,7,8,9,10,12,14,15,16,18,20,21,24,25,28,30,32,36)
(2)関数ｇｃｄで約分する方法をとった。
　答えに、分子＝○×◇／gcd(○、□)gcd(△、□)
　　　　　分母＝△×□／gcd(○、□)gcd(△、□)
　と式を作りました。
　この答えの分母分子が大きく(３桁）になるときがあ　るのです。
よって、３桁になった場合は、もう一度計算し、２桁になるような風にしたいと思っているのですが、できますか？
　　　すみません。お願いします。

回答No.2

imogasi

Ａ＊Ｂの結果が１以上かつ１００以下ということでしょうか。
Ａ、Ｂは正整数で良いのでしょうか。
問題は内容をしっかり書いて質問して欲しい。
上記なら
Ａ１に=INT(RAND()*10+1)
Ｂ１にも同じ式
Ｃ１に=A1*B1
Ａ１：Ｃ１を範囲指定し、Ｃ１で＋ハンドルをだし、下方に引っ張る。
Ａ，Ｂ列は１から１０までの数字が出るから結果は１－１００です。Ｆ９を押す都度変わるから、形式を選択して貼り付けで、自身のセルに値を複写し、式を消す方が良いかもしれない。

補足

すみません。説明不足で。
実は「分数のかけ算」の問題を作っています。
○／△×◇／□で、
(1)それぞれの分母分子を乱数で出すようにした。
　関数「choose」「randbetween」で、３６までの整数のう　ち比較的使われる２３個を乱数でえらぶようにしま
　した。
=CHOOSE(RANDBETWEEN(1,23),1,2,3,4,5,6,7,8,9,10,12,14,15,16,18,20,21,24,25,28,30,32,36)
(2)関数ｇｃｄで約分する方法をとった。
　答えに、分子＝○×◇／gcd(○、□)gcd(△、□)
　　　　　分母＝△×□／gcd(○、□)gcd(△、□)
　と式を作りました。
　この答えの分母分子が大きく(３桁）になるときがあ　るのです。
よって、３桁になった場合は、もう一度計算し、２桁になるような風にしたいと思っているのですが、できますか？
　　　すみません。お願いします。

回答No.1

dora1

私が答えられるかはわかりませんが、質問の文が私にはちょっと難しかったので補足お願いします。

・乱数と2つのセルとの関係？（2つのセルに、それぞれ乱数が入っている？それとも乱数でどことどこのセルを掛け算するのかを決める？）
・1～100範囲指定とは？（スプレッドシートの場所の範囲？数字の大きさの範囲？）
・範囲外の再計算とは？（その乱数を捨てて、もう1度別の乱数で新たに計算をスタートするということ？それとも出た結果を範囲内になるように変換するということ？）

補足

すみません。説明不足で。
実は「分数のかけ算」の問題を作っています。
○／△×◇／□で、
(1)それぞれの分母分子を乱数で出すようにした。
　関数「choose」「randbetween」で、３６までの整数のう　ち比較的使われる２３個を乱数でえらぶようにしま
　した。
=CHOOSE(RANDBETWEEN(1,23),1,2,3,4,5,6,7,8,9,10,12,14,15,16,18,20,21,24,25,28,30,32,36)
(2)関数ｇｃｄで約分する方法をとった。
　答えに、分子＝○×◇／gcd(○、□)gcd(△、□)
　　　　　分母＝△×□／gcd(○、□)gcd(△、□)
　と式を作りました。
　この答えの分母分子が大きく(３桁）になるときがあ　るのです。
よって、３桁になった場合は、もう一度計算し、２桁になるような風にしたいと思っているのですが、できますか？
　　　すみません。お願いします。