EXCELでBINOMDIST関数を使った式で質問があります。

質問

Sheet1のＡ列には1,2,3,4,5,6・・・・100とA1からA100まで数字が入っています。

SHEET2のＡ列は表示が％になるように設定された空欄です。
Ｂ列には数式が入っています。
B1には「=BINOMDIST(0,SHEET1!A1,A1,0)」
B2には「=BINOMDIST(0,SHEET1!A2,A2,0)」
B3には「=BINOMDIST(0,SHEET1!A3,A3,0)」
B4には「=BINOMDIST(0,SHEET1!A4,A4,0)」
・
・
・
・
B100には「=BINOMDIST(0,SHEET1!A100,A100,0)」
と入っています。

私の作業は空欄のＡ列にランダムに数字を入力して、Ｂ列の数字を0.100～0.098に入るようにすることです。

　A列　　　　　　Ｂ列
１　90.0%　 0.100
２　68.4%　 0.100
３　53.6%　 0.100
４　43.7%　 0.100
５　36.9%　 0.100
６　31.9%　 0.100

という感じです。けっこうな手間なので、Ｂ列が0.100～0.098になるような数字をＡ列に入れる関数や何か他に方法があったら、教えて頂きたいのですが、ありますでしょうか？よろしくお願いします。

回答No.5

noname#262398 · ベストアンサー

成功数=0以外の時は、関数・数式では無理だと思うので、
[ゴールシーク]を使いましょう。
http://office.microsoft.com/ja-jp/excel/HP052038941041.aspx?pid=CH010004551041

[数式入力セル] B1
[目標値]　0.1（～0.098）
[変化させるセル] A1

A1からA100まで一回ずつ同じ作業をしなければなりませんが。。。

お礼

この機能初めて知りました！とても便利ですね。ありがとうございました。

回答No.4

mt2008

「成功数＝0ですから…」のくだりを説明します。
成功数＝0→　全て失敗　です。
成功率X = 失敗率（1-X)　これがｎ回失敗する確率は　（1-X)＾ｎ　です。

この率が0.1の場合の成功率を求める式は↓になります。
（1-X)＾ｎ =　0.1
1-X = 10^(log(0.1)/n)
X = 1-10^(log(0.1)/n)

同様に、0.098以上の場合も計算します。
で、Excelでやっていることは、確率関数0.1の成功率　～確率関数0.098の成功率を以下の様な式で出しているだけです。

成功率=（確率関数0.1の成功率）+（確率関数0.098の成功率 -　確率関数0.1の成功率）*（0～1間の乱数）

成功数=0以外の時は、申し訳ないですが、私の手に余ります。

お礼

詳しく説明してくださってありがとうございます。とても参考になりました。

回答No.3

mt2008

得意分野ではないので参考程度にしてください。

成功数＝0ですから、BINOMDIST式は　(1-A1)^SHEET1!A1　同じになります。
確率関数　0.1の時　A列の値は　=1-10^(LOG(0.1)/SHEET1!A1)
0.098の時、=1-10^(LOG(0.098)/SHEET1!A1)　になります。

この間に収まる乱数を発生させればよい事になります。
Sheet2のA1に↓を入れてオートフィルでどうでしょう？

=1-10^(LOG(0.1)/SHEET1!A1)+(10^(LOG(0.1)/SHEET1!A1)-10^(LOG(0.098)/SHEET1!A1))*RAND()

補足

mt2008様へ

教えて下さっている数式の意味がわからないまま、SHEET2のA1に入れてみたところ、Ａ列には求めている数字にり、Ｂ列には0.100～0.98の数字が出ました。ありがとうございました。
この作業は更に成功数１のとき、成功数２のとき・・・と、成功数が１０のときまでやる必要があるので、A1に入れた数式の意味がわからないと応用ができません。すでに「成功数＝0ですから、BINOMDIST式は　(1-A1)^SHEET1!A1　同じ」という部分が理解できません。私のようなものが、この式を理解することはできますでしょうか？せっかく作ってくださった式なのに、成功数１から１０までバージョンをどうやっていいのやら、全くわかりません。お手数かと思いますが、説明していただけますでしょうか？

回答No.2

jellya_a

＃1です。
すみません分析ツールのアドインの設定を
行ってください。

http://www.qmss.jp/e-stat/excel/analysis.htm
http://www.relief.jp/itnote/archives/001184.php

お礼

jellya_a様へ

教えてくださってありがとうございます。でも、私の能力が足りないので、説明していただいた意味が理解できていません。
分析ツールのアドインの設定はできました。ツールをクリックすれば、分析ツールが出てきて、何か選べそうな感じになりましたが、何を選べばいいのか、わかりません。

また、この「=RANDBETWEEN(1,100)」数式は１～１００の中の数字をランダムに出すというものですよね？それをＡ１に入れたとして、Ｂ１が0.100になるというのは、どうすればいいのでしょうか？ごめんなさい。素人なので、理解する能力が足りません。

回答No.1

jellya_a

一例ですが
=RANDBETWEEN(1,100)はいかがでしょう？
1～100のランダムな数字を返します。