Excelでユーザー定義関数を用いて離散確率分布からランダムに値を取得する方法
データ分析やシミュレーションをExcelで行う際、特定の確率分布に基づいてランダムな値を生成したいというニーズはよくあります。今回は、特定の範囲と出現比率に基づいてランダムな値を選択するユーザー定義関数を作成し、その利用方法を紹介します。
(サンプル表)
| 出現アイテム | 出現比 |
|---|---|
| 茨城県 | 10 |
| 栃木県 | 8 |
| 群馬県 | 8 |
| 埼玉県 | 15 |
| 千葉県 | 15 |
| 東京都 | 40 |
| 神奈川県 | 15 |
ユーザー定義関数以外での離散確率分布での値生成
ユーザー定義関数を使わなくても離散確率分布で値が生成できます。やや操作が煩雑になったりしますが、先に解説ページをご紹介します。
「分析ツール」を使って離散分布乱数を生成する
RAND関数、MATCH関数、INDEX関数を組み合わせて離散分布乱数を生成する
ユーザー定義関数が実現すること
以下で紹介する関数(RandomWithRatio)は、指定したセル範囲から、指定した比率に基づいて、ランダムに1つの値を選んで表示するものです。各要素が選ばれる確率は、指定した比率に依存します。これによって、離散確率分布に基づいて値を生成することができます。
通常のRANDBETWEEN関数などは一様分布に基づいて値を生成しますが、この関数は指定した比率に従って値を生成するため、非一様な分布からのサンプリングを行うことが可能です。
参考：一様分布型のランダム値選択をなすユーザー定義関数
以下のページでは一様分布型で値をランダムに抜き出すユーザー定義関数を紹介しています。
離散確率分布型のサンプリングをする関数コードの登録方法
Excelに新しい関数を登録するにはVBA（Visual Basic for Applications）を使用します。具体的な手順は以下の通りです。
- Excelを開き、「開発」タブをクリックします。
- 「Visual Basic」をクリックして、VBAのエディタを開きます。
- 「挿入」メニューから「標準モジュール」を選択し、新しいモジュールを追加します。
- 下記のコードをコピーして、VBAエディタにペーストします。
- VBAエディタを閉じます。
Function RandomWithRatio(rng As Range, Optional ratioRng As Range) As Variant
Dim cell As Range
Dim collection As New collection
Dim i As Integer, j As Integer, selected As Integer
' ratioRngが指定されているかどうか、またはセル数が同じかどうかをチェック
If ratioRng Is Nothing Then
' ratioRngが指定されていない場合、rng内のすべてのセルを等確率で選択
For Each cell In rng
collection.Add cell.Text
Next cell
ElseIf rng.Cells.Count = ratioRng.Cells.Count Then
' ratioRngが指定され、セル数が同じ場合
i = 1
For Each cell In rng
For j = 1 To ratioRng.Cells(i).Value
collection.Add cell.Text
Next j
i = i + 1
Next cell
Else
' ratioRngが指定されているが、セル数が異なる場合はエラーを返す
RandomWithRatio = CVErr(xlErrValue)
Exit Function
End If
' collectionからランダムに要素を選択
If collection.Count > 0 Then
Randomize
selected = Int((collection.Count * Rnd) + 1)
RandomWithRatio = collection(selected)
Else
RandomWithRatio = CVErr(xlErrValue)
End If
End Function関数の使用方法
事前に出現させたいアイテムと、その出現比を入力しておきます。行展開も列展開でも対応しています。
関数を使用するには、エクセルシートでセルを選択し、「関数の挿入」ボタンをクリックします。次に、「ユーザー定義関数」カテゴリを選択し、登録した関数「RandomWithRatio」を選んで引数を指定します。
引数「Rng」に出現アイテムの範囲を登録します。引数「RatioRng」に出現比を入力した範囲を登録します。
コピーして大量に生成するならばF4キーなどで各引数を「絶対参照」を設定しておきましょう。
出現比に基づきランダムに値が選択されました。
コピーすればその出現比に基づきランダムに値を取り出すことができます。
使用上の注意点
比率を指定するセル範囲は、値を取得するセル範囲と同じ数のセルから構成されている必要があります。また、比率は正の整数である必要があります。
この関数を利用して、シミュレーションやデータ分析において、特定の離散確率分布に基づいたランダムな値をエクセルで簡単に生成することができます。
コメント