Excel VBA 確率
乱数を使う(円周率 Monte Carlo simulation)
解説:
Rnd 関数は、 0 以上、1 未満の範囲の値で、等しい確率の、単精度浮動小数点数型 (Single) の乱数を返します。一様乱数と言います。
構文
Rnd[(number)]
引数 number は省略可能です。引数 number には、単精度浮動小数点数型 (Single) の数値または任意の有効な数式を指定します。
number の値 | 戻り値 |
< 0 | 常に、引数 number のシード値によって決まる同じ数値を返します。 |
> 0 | 乱数系列の次の乱数を返します。 |
= 0 | 直前に生成した乱数を返します。 |
省略したとき | 乱数系列の次の乱数を返します。 |
Randomize ステートメントは、乱数ジェネレータを初期化 (乱数系列を再設定) する数値演算ステートメントです。
Randomize ステートメントを使用しない場合、引数を指定しないで Rnd 関数を呼び出すと、最初に Rnd 関数を呼び出したときのシード値と同じ値が使用されます。それ以降は、直前に生成された数がシード値として使用されます。
Randomize ステートメントを使用すると、システム タイマーから取得した値を新しいシード値として乱数を発生します。
HasTitle プロパティは、グラフのタイトル表示を制御します。True の場合、軸やグラフのタイトルを表示します。ブール型 (Boolean) の値を使用します。
指定範囲の整数乱数を発生する関数
サイコロなどを模倣するときなどに使える関数です。
下のマクロをダウンロードできます。→RandomValueCheckVBA01.xls
試行回数 が 10000 回だと均一な分布になりますが、100 回程度だと正常範囲でしょうが偏りが見えるものですね。
正規乱数・二項乱数・指数乱数・ポアソン乱数を発生
上で一様乱数の関数を使ったついでに、他の乱数の発生方法を紹介します。
統計分析では、その目的とする確率分布に従う乱数を使ってシミュレーションします。
ここでは、代表的な4つの乱数(正規乱数・二項乱数・指数乱数・ポアソン乱数)を発生させて、ヒストグラムで、その分布の形を確認します。
また、Excelの関数で計算した累積確率と、結果を対比させます。
以下のVBAコードは、縄田和満さんの「Excel VBAによる統計統計データ解析入門」のコードを使わせていただきました。プログラムの乱数発生部分の解説は、この書籍をお買い求めになるか、図書館などで閲覧して下さい。
このマクロでは、ヒストグラムを表示するために、Excelの「分析ツール-VBA」を使っています。「分析ツール-VBA」は、Excelで統計や技術計算に基づく分析を実行するための、アドイン機能です。
[ツール] メニューに [分析ツール-VBA] が表示されない場合は、[ツール] メニューの[アドイン]で表示されるリストボックスの [分析ツール-VBA] にチェックを入れて、プログラムをインストールして下さい。
Vista の場合は、「ファイル」→「Excelのオプション」で表示される、下の画面で、「設定」ボタンを押します。
すると、リストボックスが表示されます。
このマクロでは、Excelのバージョンを取得して、[分析ツール-VBA] の拡張子を、自動で変更するようにしています。
Excel2003 以前は、ATPVBAEN.XLA!Histogram だったものが、
Excel2007〜は、ATPVBAEN.XLAM!Histogram と、4桁拡張子に変更しているからです。
Microsoft 社は、なぜこんなところを変更する! 強い怒り!!
下のマクロをダウンロードできます。→RandomNumbersVBA4.xls
(この「乱数発生」は、[分析ツール-VBA] を使っているためか、ブラウザの画面で開いた状態では実行できません。いったんダウンロードして、Excel で開いて、実行して下さい。
使い方は、いずれかの「乱数」のボタンを押すだけです。何度でも、押せます。)
マクロの利用方法の例:
@さいころを10回振って、1の出る回数は?
→ 二項乱数で、確率を1/6、回数を10として実行します。
A一日平均5通のメイルが来る人が、全くメイルを受け取らない日は、月の内何日?
メイルを10通以上受け取る日は何日?
→ ポアソン乱数で、平均到着率を5で実行します。
★ コラム ★
車で高速道路を走っていて、インターチェンジでの車の出入りが無いのに、上り坂の途中や、トンネルの入り口手前で、渋滞して、止まりそうになることがありますね。
そのくせ、しばらく行くと、狐につままれたように、渋滞が解消して、車間距離を空けて、高速で走れるようになります。
これは、待ち行列ではなく、単位時間あたりの処理能力の問題です。
時速を秒速に直して比較してみましょう。
時速(100km)=秒速(28m)
時速( 4km)=秒速( 1m)
時速100kmで走っているときは、1秒間に28m走れますが、歩いているくらいに遅くなると、1秒間に1mしか走れません。
車の長さを 5m とすると、スピードが遅くなると、秒速 1m x 5 = 5m で、車が数珠つなぎ状態になってしまう交通量でも、時速100kmだと 秒速 28m x 5 = 135m で、充分な車間距離をおいて走れるのです。
この事実から言えることは、高速道路の途中で渋滞しているときには、渋滞の後ろで
「この先渋滞中!速度落とせ!」
という掲示を出すのは逆効果で、渋滞の列がどんどん長くなってしまう可能性があります。
むしろ、渋滞の先頭部分に、
「後続車か渋滞しています。もっとスピードを上げて走りましょう!」
という警告を表示すべきなのです。
(^^)/~~~
|
統計データ解析の詳しい方法については、下記のサイトを、ご欄下さい。
(^^ゞ
「sda-trial.xls」のシート「スロットマシン」の「乱数の簡単な応用例」は、やり過ぎないように!
ポアソン乱数発生2モジュールは、このサイトのコードを使わせていただきました。
また、「待ち行列のシミュレーション」のコードをベースに、アニメーション風にアレンジしたものを、RandomNumbersVBA4.xls のシート「待ち行列」に、おまけに付けました。
(「待ち行列」は、ブラウザで開いた状態でも、simulate ボタンを押すと、動きます。)
追記: Excel の「コントロールツールボックス」の「コマンドボタン」にマクロを登録したとき、マウスポインタをボタンに重ねても、ポインタの形は矢印のままで、手(指)の形にはなりません。
ここでは、下のサイトを参考にさせていただき、MouseMoveイベントで、手の形になるようにしました。
(更に調べたところ、「フォーム」の「ボタン」を使うと、既定で手の形になることが分りました。)
関連ですが、音楽の世界では、ピンクノイズと呼ばれる周波数分布の「乱数(ノイズ)」が使われます。
ノイズには、あらゆる周波数成分を一様に含むホワイトノイズ(白色雑音 white noise 1Hzごとの音のエネルギーが等しい)と、周波数に反比例して、高い周波数の音ほど弱くなるピンクノイズ(pink noise または1/f雑音 1オクターブごとのエネルギーが等しい)が有ります。
光に例えると、すべての波長の光を含む光が白色光で、周波数が低い(波長が長い)成分は、赤い光なので、低い周波数のレベルが大きければ、白色より赤みがかっているということでピンクと表現したものです。
ホワイトノイズの例は、テレビの放送終了後の、画面が「砂嵐」状態のときの音や、FM放送の局間ノイズで、「シャー…」と聞こえるものです。機械的に発生させやすいノイズです。
人間の耳は、周波数に対して(音量に対しても)対数的に感じるようにできています。音程のオクターブの違いは、人間の耳には等間隔に聞こえますが、周波数でオクターブの違いは、周波数の比率が2倍になっています。(100Hz→200Hz→400Hz→800Hz・・)
ピンクノイズは、この人間の耳の特性を基準に考えられたもので、聴感上はホワイトノイズよりも低音域が勝っているので、「ザー…」と聞こえます。音響製品の周波数特性の評価には、このピンクノイズが使われます。ホワイトノイズを、-3dB/oct の低域通過フィルタ(Low Pass Filter)に通して作り出します。(ピンクノイズ・ジェネレータ)
解説:
Function ステートメントは、Function プロシージャの名前、引数、および本体部分を構成するコードを宣言します。
構文
[Public | Private | Friend] [Static]
Function name [(
arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function
name 必ず指定します。定義する Function プロシージャの名前を指定します。変数の標準的な名前付け規則に従って指定します。
引数
arglist は、次の形式で指定します。
[Optional] [
ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]
引数の値渡し
ByVal と、参照渡し
ByRef の違い
http://shuhho.hatenablog.com/entry/excelvba-37
http://officetanaka.net/excel/vba/tips/tips94.htm
http://msdn2.microsoft.com/ja-jp/library/eek064h4(VS.80).aspx
ByRef :参照渡し
すべての引数は、特に指定しない限り、参照渡しでFunction プロシージャ、Sub プロシージャに引き渡されます。
デフォルトの渡し方です。
参照渡しを使うと、呼び出し元のコードにある、基のプログラミング要素を、プロシージャから直接参照できるようにします。
Sub で引数を渡すときは、戻り値がほしいためなので、参照渡し(無指定のまま)を使う場合が多いでしょう。
メイン処理から、Call した Sub のプロシージャに渡した変数を、Sub で加工してもらって、メインに戻してもらう場合が、参照渡しです。
参照渡しは、引数のデータ型に関係なく、すべての引数が渡されるときの所要時間およびプロシージャ内のメモリ領域 (4 バイト) が同じであるため、効率的です。
ByVal :値渡し
値渡し (値による引き渡し) を使用すると、元の変数(基になるプログラミング要素)の値を、プロシージャ内のローカル変数にコピーします。
プロシージャ コードから呼び出し元のコードにある、基の要素にアクセスすることはありません。
したがって、呼び出されたプロシージャ内で渡された変数の値を変更しても、呼び出し側のプロシージャが参照する元の変数の値は変更されません。
値で渡される引数は、引数のデータ型に応じて、プロシージャ内で 2 〜 16 バイトのメモリ領域を使用します。
データ型が大きいほど、値渡しに要する時間が少し長くなります。
引数を値渡しで渡すには、プロシージャ定義内で対応するパラメータに ByVal キーワードを指定します。
Function のデフォルトは、「参照渡し」です。
文字列型変数は、データ量が多くなるので、
Function 内で戻り値を変更しないことが明らかな文字列型変数は場合に限っては、デフォルトの「参照渡し」のままで良いです。このほうが、メモリも少なく、処理速度も速くなります。
しかし、引数を Function 内で変更する場合は、
ByVal を明示的に指定して、メイン処理と分離する必要があります。
ByVal (値渡し) することで、メイン処理の変数の値が、Function 内で変えられても、メイン処理に影響を与えることはなくなります。
逆に言えば、デフォルトの「参照渡し」を使う場合は、
Function 内で引数の値を変えない工夫 が必要です。
Application プロパティは、オブジェクトを返すプロパティです。
対象となるオブジェクトが指定されない場合は、Excel アプリーション (Application オブジェクト) を返します。
対象となるオブジェクトが指定された場合は、指定されたオブジェクトを作成した Application オブジェクトを返します。OLE オートメーションを使っていて、オブジェクトのアプリケーションにアクセスするときなどに、このプロパティを使います。値の取得のみ可能です。
NormSInv は、Excel の関数で、標準正規分布の累積分布関数の逆関数の値を返します。
この分布は、平均が 0 で標準偏差が 1 である正規分布に対応します。
NormSInv(確率)
確率 正規分布における確率を指定します。
注意:
確率に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
確率 < 0、または確率 > 1 である場合、エラー値 #NUM! が返されます。
確率の値が指定されると、NORMSDIST(z) = 確率となるような値 z がシークされます。したがって、NORMSINV 関数の精度は NORMSDIST 関数の精度に依存します。NORMSINV 関数では、反復計算の手法が利用されます。100 回反復計算を繰り返しても計算結果が収束しない場合、エラー値 #N/A が返されます。
NormDist は、指定した平均と標準偏差に対する正規分布関数の値を返します。
この関数は、仮説検定を始めとする統計学の幅広い分野に応用できます。
NormDist(x,平均,標準偏差,関数形式)
x 関数に代入する値を指定します。
平均 対象となる分布の算術平均 (相加平均) を指定します。
標準偏差 対象となる分布の標準偏差を指定します。
関数形式 計算に使用する指数関数の形式を論理値で指定します。関数形式に TRUE を指定すると累積分布関数の値が計算され、FALSE を指定すると確率密度関数の値が計算されます。
注意:
平均、標準偏差に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
標準偏差 <= 0 である場合、エラー値 #NUM! が返されます。
平均 = 0 、標準偏差 = 1 かつ関数形式 = TRUE である場合、標準正規分布関数 (NORMSDIST 関数) の値が計算されます。
NormSDist は、標準正規分布の累積分布関数の値を返します。
この分布は、平均が 0 (ゼロ) で標準偏差が 1 である正規分布に対応します。正規分布表の代わりにこの関数を使用することができます。
BinomDist は、二項分布の確率関数の値を返します。
この関数は、一定回数の試行を伴う問題の中で、試行の結果が成功または失敗のいずれかである場合、試行が独立したものである場合、かつ実験を通して成功の確率が一定である場合に使用します。
たとえば、次に生まれる 3 人の赤ちゃんの中で 2 人が男の子である確率を計算することができます。
BinomDist(成功数,試行回数,成功率,関数形式)
成功数 試行回数に含まれる成功の回数を指定します。
試行回数 独立試行の回数を指定します。
成功率 1 回の試行が成功する確率を指定します。
関数形式 関数の形式を、論理値で指定します。関数形式に TRUE を指定した場合、BINOMDIST 関数の戻り値は累積分布関数となり、0 〜 成功数回の範囲で成功が得られる確率が計算されます。FALSE の場合は、確率密度関数となり、正確に成功数回の成功が得られる確率が計算されます。
注意:
成功数、試行回数に整数以外の値を指定すると、小数点以下が切り捨てられます。
成功数、試行回数、成功率に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
成功数 < 0 または成功数 > 試行回数の場合、エラー値 #NUM! が返されます
成功率 < 0 または成功率 > 1 の場合、エラー値 #NUM! が返されます。
RoundUp 関数は、数値を指定された桁数に切り上げるExcelの関数です。
WorksheetFunction プロパティを前に付ける必要が有ります。
RoundUp(数値,桁数)
数値 切り上げの対象となる実数値を指定します。
桁数 数値を切り捨てた結果の桁数を指定します。
ROUNDUP 関数は、ROUND 関数(四捨五入)に似た働きをしますが、常に数値の切り上げを行う点が異なります。
桁数に正の数を指定すると、数値は小数点の右 (小数点以下) の指定した桁に切り上げられます。
桁数に 0 を指定すると、数値は最も近い整数に切り上げられます。
桁数に負の数を指定すると、数値は小数点の左 (整数部分) の指定した桁 (1 の位を 0 とする) に切り上げられます。
Round 関数は、指定された小数点位置で四捨五入した数値を返します。
Round(expression [,numdecimalplaces])
expression 必ず指定します。四捨五入を行う数式を指定します。
numdecimalplaces 省略可能です。四捨五入を行う小数点以下の桁数を表す数値を指定します。省略すると、Round 関数は整数値を返します。
ExcelのRound 関数は、この桁数に負の数を指定すると、数値は小数点の左側 (整数部分) の指定した桁 (1 の位を 0 とする) に四捨五入されます。しかし、VBAの関数では、負数は使えません。
例: Round(2.149, 2) とすると、2.149 を小数点第2位に四捨五入します (2.15)
Int(number) 関数と Fix(number) 関数は、どちらも引数 number の小数部分を取り除いた整数部分を返します。
引数
number に負の値を指定した場合には、
Int 関数が引数
number を超えない最大の負の整数を返すのに対して、
Fix 関数は引数
number 以上の最小の負の整数を返します。
たとえば、引数として -8.4 を指定すると、Int 関数は -9、Fix 関数は -8 をそれぞれ返します。
Fix(
number) は、次の数式と等価です。
Sgn(
number) * Int(
Abs(
number))
ExponDist 関数は、指数分布関数を返します。
この関数は、銀行の ATM で現金を引き出すのにかかる時間など、イベントの間隔をモデル化する場合に使用します。
たとえば、ある処理が 1 分以内に終了する確率を算出することができます。
ExponDist(x,λ,関数形式)
x 関数に代入する値を指定します。
λ パラメータの値を指定します。
関数形式 計算に使用する指数関数の形式を論理値で指定します。関数形式が TRUE の場合、戻り値は累積分布関数となり、FALSE の場合は、確率密度関数が返されます。
注意:
x またはλに数値以外の値を指定すると、エラー値 #VALUE! が返されます。
x <0 (負の数) を指定すると、エラー値 #NUM! が返されます。
λ <=0 である場合、エラー値 #NUM! が返されます。
Poisson 関数は、ポアソン確率の値を返します。
通常、ポアソン分布は一定の時間内に起きる事象の数を予測するために利用されます。
たとえば、高速道路の料金所を 1 分間に通過する自動車の台数を予測することができます。
Poisson(イベント数,平均,関数形式)
イベント数 生じる事象の数を指定します。
平均 一定の時間内に起きる事象の平均値を指定します。
関数形式 確率分布を計算する関数形式を、論理値で指定します。関数形式に TRUE を指定した場合、生起するランダムな事象の数が 0 からイベント数の範囲であるような累積ポアソン確率が計算されます。FALSE の場合は、生起する事象の数が正確にイベント数となるようなポアソン確率が計算されます。
注意:
イベント数に整数以外の値を指定すると、小数点以下が切り捨てられます。
イベント数、平均に数値以外の値を指定すると、エラー値 #VALUE! が返されます。
x < 0 の場合、エラー値 #NUM! が返されます。
平均 <= 0 の場合、エラー値 #NUM! が返されます。
IIf 関数は、式の評価結果によって、2 つの引数のうち 1 つを返します。
IIf(
expr, truepart, falsepart)
IIf 関数の構文は、次の名前付き引数から構成されます。
指定項目 | 内容 |
expr | 必ず指定します。評価対象の式を指定します。 |
truepart | 必ず指定します。名前付き引数 expr が真 (True) の場合に返す値または式を指定します。 |
falsepart | 必ず指定します。名前付き引数 expr が偽 (False) の場合に返す値または式を指定します。 |
注意:
IIf 関数では、名前付き引数 truepart または名前付き引数 falsepart のいずれか一方だけが返されますが、評価は両方の引数に対して行われます。
このため、IIf 関数を使うと、予期しない結果が起きることがあります。たとえば、名前付き引数 falsepart を評価した結果 0 による除算エラーが発生する場合は、名前付き引数 expr が真 (True) であってもエラーが発生します。
名前付き引数とは、オブジェクト ライブラリにあらかじめ定義された名前を持つ引数です。
名前付き引数を使用すると、構文どおりに指定された順序で各引数に値を指定しなくても、任意の順序で値を設定することができます。
たとえば、3 つの引数を取得できる次のようなメソッドがあるとします。
DoSomeThing namedarg1, namedarg2, namedarg3
名前付き引数を使うと、次のように引数を指定することができます。
DoSomeThing namedarg3 := 4, namedarg2 := 5, namedarg1 := 20
名前付き引数は、構文の標準的な配置順序で指定する必要はありません。
数の暗黙知能力向上プリント
テレビ・ドラマ「ドラゴン桜」で紹介されていた計算ドリルを生成するマクロを作ってみました。
登場する数値の範囲を変えて、難易度を調整できます。
・算数 数学、勉強ができるかどうかは小2算数で決まる
・数の暗黙知を身につけるためにはスパルタが効果的
・数の暗黙知を身につける方法@「柳式 数の暗黙知能力向上プリント」
下のマクロをダウンロードできます。→ MathWorkbookVBA04.xls