今日を乗り切るExcel研究所

Excel に働かされていませんか

【Excel LAMBDA】乱数生成編【ユーザー定義関数集】

乱数生成編

LAMBDA 関数を使った数式によるユーザー定義関数(カスタム関数)として、実用的でコピペで使える実装例をまとめていきたいと思います。

使い方は以下の記事を参照してください。

www.shegolab.jp

本記事ではダミーデータの生成に使えそうな、乱数生成関数を主に集めました。

【使用上の注意】

  • 本記事に掲載している数式は LAMBDA 関数をサポートしてる Excel(現状、 Microsoft 365 版(サブスク版)Excel のみ)で使用可能です。
  • 本ブログに記載のLAMBDA 数式は全て単独で利用できます。必要な関数のみを「名前の定義」で「参照範囲」にコピー&ペーストしてください。
  • 不具合、使い勝手の改善、欲しい機能等ご意見ご希望がありましたら、本記事のコメントか Twitter にてお知らせください。

正規分布乱数

【名前の定義】

  • 名前

    NORMRAND

  • コメント

    正規分布の乱数列を生成します。

  • 参照範囲
=LAMBDA(個数, 平均, 標準偏差,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        μ, IF(ISOMITTED(平均), 0.0, 平均),
        σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差),
        NORM.INV(RANDARRAY(n, 1), μ, σ)
    )
)

【構文】

NORMRAND(個数, 平均, 標準偏差)

【引数】

引数 内容
個数 乱数列の行数を指定する正の整数、省略時は 1
平均 正規分布の平均値、省略時は 0
標準偏差 正規分布の標準偏差、省略時は 1.0

【戻り値】

指定の分布係数をもつ正規分布の乱数列を返します。

【説明】

NORMRAND 関数は指定の分布係数を持つ正規分布乱数を任意の個数生成し、縦方向の配列として返します。

対数正規分布乱数

【名前の定義】

  • 名前

    LOGNRAND

  • コメント

    対数正規分布の乱数列を生成します。

  • 参照範囲
=LAMBDA(個数, 平均, 標準偏差,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        μ, IF(ISOMITTED(平均), 0.0, 平均),
        σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差),
        LOGNORM.INV(RANDARRAY(n, 1), μ, σ)
    )
)

【構文】

LOGNRAND(個数, 平均, 標準偏差)

【引数】

引数 内容
個数 乱数列の行数を指定する正の整数、省略時は 1
平均 対数正規分布の平均値、省略時は 0.0
標準偏差 正規分布の標準偏差、省略時は 1.0

【戻り値】

指定の分布係数をもつ対数正規分布の乱数列。

【説明】

LOGNRAND 関数は、指定の分布係数を持つ対数正規分布乱数を任意の個数生成し、縦方向の配列として返します。

 



 

指数分布乱数

【名前の定義】

  • 名前

    EXPRAND

  • コメント

    指数分布の乱数列を生成します。

  • 参照範囲
=LAMBDA(個数, 平均,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        λ, IF(ISOMITTED(平均), 1.0, 平均),
        -LN(RANDARRAY(n, 1))/λ
    )
)

【構文】

EXPRAND(個数, 平均)

【引数】

引数 内容
個数 乱数列の行数を指定する正の整数、省略時は 1
平均 対数正規分布の平均値、省略時は 0.0

【戻り値】

指定の分布係数をもつ対数正規分布の乱数列。

【説明】

EXPRAND 関数は、指定の平均値を持つ指数分布乱数を任意の個数生成し、縦方向の配列として返します。

ポアソン分布乱数

  • 名前

    POISSONRAND

  • コメント

    ポアソン分布の乱数を生成します。

  • 参照範囲
=LAMBDA(個数, 平均,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        λ, IF(ISOMITTED(平均), 1, 平均),
        dist, UNIQUE(POISSON.DIST(SEQUENCE(100,,0), λ, TRUE)),
        occs, SEQUENCE(ROWS(dist)),
        cdf, LAMBDA(rnd, LET(
            lts, MAP(dist, occs,
                LAMBDA(p, o, IF(rnd > p, o, 0))),
            MAX(lts)
        )),
        MAP(RANDARRAY(n, 1), cdf)
    )
)

【構文】

POISSONRAND(個数, 平均)

【引数】

引数 内容
個数 乱数列の個数を指定する正の整数、省略時は1
平均 平均生起回数

【戻り値】

指定の分布係数をもつポアソン分布の乱数列。

【説明】

POISSONRAND 関数は、指定の平均値を持つポアソン分布乱数を任意の個数生成し、縦方向の配列として返します。

時系列乱数

  • 名前

    TIMERAND

  • コメント

    一定の確率で起こる時系列データを生成します

  • 参照範囲
=LAMBDA(個数, 平均間隔,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        θ, IF(ISOMITTED(平均時間), "1:00", 平均時間),
        SCAN(0, RANDARRAY(n, 1, 0, 1), LAMBDA(a, x, a - θ * LN(x)))
    )
)

【構文】

TIMERAND(個数, 平均間隔)

【引数】

引数 内容
個数 乱数列の個数を指定する正の整数、省略時は1
平均間隔 時刻間隔の平均時間、省略時は1時間("1:00:00")

【戻り値】

指定の平均間隔時間で発生するランダムな時刻列。

【説明】

TIMERAND 関数は、指定の平均間隔時間のランダムな時刻を任意の個数生成し、縦方向の配列として返します。

本来なら乱数時刻の「個数」ではなく、生成する「期間」を指定できるようにしたいところなのですが、乱数でサイズが決まるような配列は #SPILL! エラーになってしまうため個数指定としています。

ある期間内での時系列乱数が欲しい場合には、多めの個数を指定したうえで、オートフィルターで範囲を絞り込むか、値のみコピーするなどしてください。

 



AFE ソース

Excel に Advanced Formula Environment アドインを導入されている上級者のために、本記事掲載の全てのユーザー定義関数を一括登録できるソースを用意しました。

以下のソースを AFE の Editor にコピー&ペーストしてください。 関数名の衝突を避けるため、任意の名前で専用の Namespace を用意することをお勧めします。

// 今日を乗り切るExcel研究所 
// 【Excel LAMBDA】乱数生成編【ユーザー定義関数集】
// https://www.shegolab.jp/entry/excel-lambda-random-01


/**
 * 正規乱数
 */
NORMRAND
=LAMBDA(個数, 平均, 標準偏差,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        μ, IF(ISOMITTED(平均), 0.0, 平均),
        σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差),
        NORM.INV(RANDARRAY(n, 1), μ, σ)
    )
)
;


/**
 * 対数正規乱数
 */
LOGNRAND
=LAMBDA(個数, 平均, 標準偏差,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        μ, IF(ISOMITTED(平均), 0.0, 平均),
        σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差),
        LOGNORM.INV(RANDARRAY(n, 1), μ, σ)
    )
)
;


/**
 * 指数乱数
 */
EXPRAND
=LAMBDA(個数, 平均,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        λ, IF(ISOMITTED(平均), 1.0, 平均),
        -LN(RANDARRAY(n, 1))/λ
    )
)
;


/**
 * ポアソン分布乱数
 */
POISSONRAND
=LAMBDA(個数, 平均,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        λ, IF(ISOMITTED(平均), 1, 平均),
        dist, UNIQUE(POISSON.DIST(SEQUENCE(100,,0), λ, TRUE)),
        occs, SEQUENCE(ROWS(dist)),
        cdf, LAMBDA(rnd, LET(
            lts, MAP(dist, occs,
                LAMBDA(p, o, IF(rnd > p, o, 0))),
            MAX(lts)
        )),
        MAP(RANDARRAY(n, 1), cdf)
    )
)
;


/**
 * 時系列乱数
 */
TIMERAND
=LAMBDA(個数, 平均間隔,
    LET(
        n, IF(ISOMITTED(個数), 1, 個数),
        θ, IF(ISOMITTED(平均間隔), "1:00", 平均間隔),
        SCAN(0, RANDARRAY(n, 1, 0, 1), LAMBDA(a, x, a - θ * LN(x)))
    )
)
;


// EOF

参考資料

変更履歴

  • [2022-06-18] 公開