乱数生成編
LAMBDA 関数を使った数式によるユーザー定義関数(カスタム関数)として、実用的でコピペで使えるサンプルコードをまとめていきたいと思います。
使い方は以下の記事を参照してください。
本記事ではダミーデータの生成に使えそうな、乱数生成関数を主に集めました。
【使用上の注意】
- 本記事に掲載している数式は LAMBDA 関数をサポートしてる Excel(現状、 Microsoft 365 版(サブスク版)Excel のみ)で使用可能です。
- 本ブログに記載のLAMBDA 数式は全て単独で利用できます。必要な関数のみを「名前の定義」で「参照範囲」にコピー&ペーストしてください。
- 関数名となる「名前」は一例を示していますが、任意の名前をつけて問題ありません。
- 不具合、使い勝手の改善、欲しい機能等ご意見ご希望がありましたら、本記事のコメントか Twitter にてお知らせください。
正規分布乱数
【名前の定義】
- 名前
NORMRAND
- コメント
正規分布の乱数列を生成します。
- 参照範囲
=LAMBDA(個数, [平均], [標準偏差], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), μ, IF(ISOMITTED(平均), 0.0, 平均), σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差), NORM.INV(RANDARRAY(n, 1), μ, σ) ) )
【構文】
NORMRAND(個数, 平均, 標準偏差)
【引数】
引数 | 内容 |
---|---|
個数 | 乱数列の行数を指定する正の整数 |
平均 | 正規分布の平均値、省略時は 0 |
標準偏差 | 正規分布の標準偏差、省略時は 1.0 |
【戻り値】
指定の分布係数をもつ正規分布の乱数列を返します。
【説明】
NORMRAND 関数は指定の分布係数を持つ正規分布乱数を任意の個数生成し、縦方向の配列として返します。
対数正規分布乱数
【名前の定義】
- 名前
LOGNRAND
- コメント
対数正規分布の乱数列を生成します。
- 参照範囲
=LAMBDA(個数, [平均], [標準偏差], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), μ, IF(ISOMITTED(平均), 0.0, 平均), σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差), LOGNORM.INV(RANDARRAY(n, 1), μ, σ) ) )
【構文】
LOGNRAND(個数, 平均, 標準偏差)
【引数】
引数 | 内容 |
---|---|
個数 | 乱数列の行数を指定する正の整数 |
平均 | 対数正規分布の平均値、省略時は 0.0 |
標準偏差 | 正規分布の標準偏差、省略時は 1.0 |
【戻り値】
指定の分布係数をもつ対数正規分布の乱数列。
【説明】
LOGNRAND 関数は、指定の分布係数を持つ対数正規分布乱数を任意の個数生成し、縦方向の配列として返します。
指数分布乱数
【名前の定義】
- 名前
EXPRAND
- コメント
指数分布の乱数列を生成します。
- 参照範囲
=LAMBDA(個数, [平均], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), λ, IF(ISOMITTED(平均), 1.0, 平均), -LN(RANDARRAY(n, 1))/λ ) )
【構文】
EXPRAND(個数, 平均)
【引数】
引数 | 内容 |
---|---|
個数 | 乱数列の行数を指定する正の整数 |
平均 | 対数正規分布の平均値、省略時は 0.0 |
【戻り値】
指定の分布係数をもつ対数正規分布の乱数列。
【説明】
EXPRAND 関数は、指定の平均値を持つ指数分布乱数を任意の個数生成し、縦方向の配列として返します。
ポアソン分布乱数
- 名前
POISSONRAND
- コメント
ポアソン分布の乱数を生成します。
- 参照範囲
=LAMBDA(個数, [平均], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), λ, 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(個数, 平均)
【引数】
引数 | 内容 |
---|---|
個数 | 乱数列の個数を指定する正の整数 |
平均 | 平均生起回数 |
【戻り値】
指定の分布係数をもつポアソン分布の乱数列。
【説明】
POISSONRAND 関数は、指定の平均値を持つポアソン分布乱数を任意の個数生成し、縦方向の配列として返します。
時系列乱数
- 名前
TIMERAND
- コメント
一定の確率で起こる時系列データを生成します
- 参照範囲
=LAMBDA(個数, [平均間隔], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), θ, IF(ISOMITTED(平均間隔), "1:00", 平均間隔), SCAN(0, RANDARRAY(n, 1, 0, 1), LAMBDA(a, x, a - θ * LN(x))) ) )
【構文】
TIMERAND(個数, 平均間隔)
【引数】
引数 | 内容 |
---|---|
個数 | 乱数列の個数を指定する正の整数 |
平均間隔 | 時刻間隔の平均時間、省略時は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 // 変更履歴 // [2022-06-18] 公開 // [2022-06-18] 「個数」を省略不可に、省略可引数をオプショナルに修正 /** * 正規乱数 */ NORMRAND =LAMBDA(個数, [平均], [標準偏差], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), μ, IF(ISOMITTED(平均), 0.0, 平均), σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差), NORM.INV(RANDARRAY(n, 1), μ, σ) ) ) ; /** * 対数正規乱数 */ LOGNRAND =LAMBDA(個数, [平均], [標準偏差], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), μ, IF(ISOMITTED(平均), 0.0, 平均), σ, IF(ISOMITTED(標準偏差), 1.0, 標準偏差), LOGNORM.INV(RANDARRAY(n, 1), μ, σ) ) ) ; /** * 指数乱数 */ EXPRAND =LAMBDA(個数, [平均], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), λ, IF(ISOMITTED(平均), 1.0, 平均), -LN(RANDARRAY(n, 1))/λ ) ) ; /** * ポアソン分布乱数 */ POISSONRAND =LAMBDA(個数, [平均], LET( n, IF(ISOMITTED(個数), #VALUE!, 個数), λ, 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(個数), #VALUE!, 個数), θ, IF(ISOMITTED(平均間隔), "1:00", 平均間隔), SCAN(0, RANDARRAY(n, 1, 0, 1), LAMBDA(a, x, a - θ * LN(x))) ) ) ; // EOF
参考資料
変更履歴
- [2022-06-18] 公開
- [2022-06-18] 「個数」を省略不可に、省略可引数をオプショナルに修正