Excelによるサンプルの拡大について

Excelで正規分布に従う乱数を発生させたいと考えています。

例えば，生成したサンプルが、平均が100，標準偏差が10の正規分布に従うように大きさ1000のサンプルデータをExcelで生成したいと考えています。そこで、

数式　100+10*NORMINV(RAND(),0,1)　 として1000個のセルに乱数を発生させサンプルを得たところ、平均は100程度と期待した値になったのですが、分散が10.13，標準偏差が 3.18 程になりました。

なぜ、生成された1000個のデータの標準偏差は10に近い値をとらないのでしょうか？

No.3 ベストアンサー 回答者： qas2021 回答日時： 2023/08/22 20:48

1000個ならそれなりに10に近い標準偏差が得られるはずです。

数式に間違いがないか確認してください。
（9.5～10.5位の値が得られるはずです）

そんなことはないとは思いますが、例えば、その分散は
STDEV.S, STEDEV.PSTDEV, STDEVP, STDEVA, STDEVPA, DSTDEV, DSTDEVP
のどれかで求めたもので、標準偏差はそのルートをとったものではないでしょうね？

この回答へのお礼

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

>その【分散】はSTDEV.S, STEDEV.PSTDEV, STDEVP, STDEVA, STDEVPA, DSTDEV, DSTDEVPのどれかで求めたもので、標準偏差はそのルートをとったものではないでしょうね？

すみません！まさにその通りでした。分散の計算を誤ってSTEDEVで行っていました。感謝に尽きます。

お礼日時：2023/08/22 21:05

No.6 回答者： kamiyasiro 回答日時： 2023/08/23 15:55

#5です。

まだ閉じられていなくて良かったです。
お詫びです。

線形変換の場合は歪みません。100まわりにいるデータを、０を基準に拡大しても何ら問題ありません。

私の勘違いでした。

そんなこと言ったら、100＋10*(標準正規乱数)の変換が成立しませんよね。とんだ間違いをしていました。お恥ずかしい。

この回答へのお礼

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

線形変換では分布の正規性（正規分布かどうか）が変わらないこと、承知いたしました。

正規分布曲線の定義式f(x)=k*Exp(g(x)) 　←大雑把な表記ですみません
のｘに線形変換ax+bを代入しても、その関数が正規分布曲線の関数の形のままだからではないか、これが証明？　と考えています。もっと本質的な証明があるような気がして、何だか不安です。

お礼日時：2023/08/26 06:24

No.5 回答者： kamiyasiro 回答日時： 2023/08/23 11:48

#1です。

あと、データを歪ませるのは、100まわりにいるデータを、０を基準に拡大するせいです。

０まわりにいる標準正規分布のデータを、０を基準に拡大するときは歪みません。

ソルバーのせいではありません。あしからず。

この回答へのお礼

ありがとうございます！

お礼日時：2023/08/26 06:28

No.4 回答者： kamiyasiro 回答日時： 2023/08/23 11:43

#1です。

var.p()とstdev.p()の使い間違い、そういうミスだったんですね。

私はてっきり教育用とか事例用として、ピッタリ分散10とか標準偏差10という数値例を作りたいのか、と思っていました。

社内のQC検定対策講習とかで、そういう需要は結構あるんですよ。

この回答へのお礼

私の質問は、ピッタリを目指していたわけではないのですが、それでも、ソルバー機能という便利なツールがあること、ソルバー機能で簡単にピッタリのデータを作成できることを知ることができたことは大きいです！

お礼日時：2023/08/26 06:27

No.2 回答者： kamiyasiro 回答日時： 2023/08/22 18:38

#1です。

歪み、尖りを劣化させたくないときは、面倒ですが、標準正規乱数の段階でソルバーで調整すると良いです。

それから100＋10*(標準正規乱数)の計算すると良いです。

まあ、そんなに気になるレベルじゃないですけど・・・。

この回答へのお礼

（No1にまとめてお礼させていただきます！）

お礼日時：2023/08/22 23:39

No.1 回答者： kamiyasiro 回答日時： 2023/08/22 18:33

それは生成数が少ないからです。

F9ボタン（再計算）を押すたびに値が変わると思います。
10万個くらい生成しないと、毎回狙い通りの値が出ないと思います。

高々1000個で狙い通りの分散を持つデータを作りたい場合は、エクセルのソルバー機能を使います。

① まず、RAND()の数式の入っているセルの値を、別の場所に「値」でコピーします。それをB列とします。（RAND()の式を含んでいるとソルバーが動くたびに値が変わり、うまく収束しないため）
② 空いているセルに１を入力します。これをD1とします。
③ 空いている列にB列＊$D$1 の計算結果を入れます。これをC列とします。
④ 別の空いているセルにC列の分散var.p(C1:C1000)の計算結果を入れます。これをD2とします。
⑤ ソルバーを起動し、D2を目的セル、値を10に指定し、変更セルをD1としてソルバーの解決ボタンを押します。これで分散が10に調整されます。
⑥ 最後に平均（バイアス）を調整します。

これで小数点以下５桁までの精度が得られます。

この注意点は、尖りの値が若干劣化する点です。

この回答へのお礼

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

No3 qas2021様の回答で本ＱＡは私の初歩的な誤りによるものであることがわかりましたが、 kamiyasiro 様の回答にも感謝いたします。
教えていただいたことを実践してみました。

「ソルバー機能」に目からウロコです　（鱗□鱗）!!
こんな便利な機能があるとは、エクセルすごいです！
また、ソルバー機能はデータに歪みを与えてしまうなど、注意して使わなければならないことも肝に銘じようと思います。

たびたびお付き合いいただき、ありがとうございます。

お礼日時：2023/08/22 23:44