Excelでユーザー定義関数を用いて離散確率分布からランダムに値を取得する方法

データ分析やシミュレーションをExcelで行う際、特定の確率分布に基づいてランダムな値を生成したいというニーズはよくあります。今回は、特定の範囲と出現比率に基づいてランダムな値を選択するユーザー定義関数を作成し、その利用方法を紹介します。

(サンプル表)

出現アイテム	出現比
茨城県	10
栃木県	8
群馬県	8
埼玉県	15
千葉県	15
東京都	40
神奈川県	15

ユーザー定義関数以外での離散確率分布での値生成
1. 「分析ツール」を使って離散分布乱数を生成する
2. RAND関数、MATCH関数、INDEX関数を組み合わせて離散分布乱数を生成する
ユーザー定義関数が実現すること
1. 参考：一様分布型のランダム値選択をなすユーザー定義関数
離散確率分布型のサンプリングをする関数コードの登録方法
関数の使用方法
使用上の注意点

ユーザー定義関数以外での離散確率分布での値生成

ユーザー定義関数を使わなくても離散確率分布で値が生成できます。やや操作が煩雑になったりしますが、先に解説ページをご紹介します。

「分析ツール」を使って離散分布乱数を生成する

離散分布（分析ツール：乱数発生）

分析ツールアドインにある「乱数発生」「離散分布」についての実習解説です。「乱数発生」の「分布」で「離散」を選択した場合の実習例を以下に示します。この例では、特定の数値とそれらの発生確率を指定した離散分布に従う乱数を100個生成します。まず、...

RAND関数、MATCH関数、INDEX関数を組み合わせて離散分布乱数を生成する

Excel関数を使った離散分布に基づく乱数の生成

Excel関数を使った離散分布に基づく乱数の生成1. はじめに離散分布とは、個別の値を取る確率変数の分布を指します。例えば、サイコロを振ったときの目や、コインの表裏などがこれに当たります。この記事では、Microsoft Excelを使用し...

ユーザー定義関数が実現すること

以下で紹介する関数(RandomWithRatio)は、指定したセル範囲から、指定した比率に基づいて、ランダムに1つの値を選んで表示するものです。各要素が選ばれる確率は、指定した比率に依存します。これによって、離散確率分布に基づいて値を生成することができます。

通常のRANDBETWEEN関数などは一様分布に基づいて値を生成しますが、この関数は指定した比率に従って値を生成するため、非一様な分布からのサンプリングを行うことが可能です。

参考：一様分布型のランダム値選択をなすユーザー定義関数

以下のページでは一様分布型で値をランダムに抜き出すユーザー定義関数を紹介しています。

範囲内からひとつだけの値をランダムに取り出すユーザー定義関数

Excelで複数範囲から一様分布型でランダムな値を取得：ユーザー定義関数今回は、ExcelのVBAを使用して、複数のセル範囲からランダムに値を選択するユーザー定義関数を作成する方法をご紹介します。素早く、そして効率的にランダムな選択を行いた...

離散確率分布型のサンプリングをする関数コードの登録方法

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