Excel で指定された平均値と標準偏差に基づく乱数を生成する

指定された平均値と標準偏差を持つ乱数セットを生成することは、統計シミュレーション、アルゴリズムのテスト、あるいは金融・工学・教育分野におけるプロセスモデリングなど、さまざまな場面で一般的な要件です。しかし、Excel にはこうした平均値と標準偏差の両方に合わせて即座に乱数リストを生成する直接的な組み込み関数が用意されていません。特定の統計的特性に合致したランダムなテストデータを頻繁に作成する必要がある場合、その方法を知っておくことで、ワークフローの効率とデータ品質を大幅に高めることができます。

本チュートリアルでは、指定した平均値と標準偏差に基づいて乱数を生成する実用的な方法をご紹介します。具体的なステップバイステップの手順に加え、数式で使用するパラメーターの詳細な説明、エラーを防ぎトラブルシューティングをスムーズに行うための専門家向けヒントも提供します。さらに、このプロセスの自動化や大規模データセットの効率的な生成が必要なユーザーのために、VBA マクロによるソリューションもご用意しています。

指定された平均値と標準偏差に基づく乱数を生成する

VBA コード - 指定された平均値と標準偏差を持つ乱数を生成する

---

指定された平均値と標準偏差に基づく乱数を生成する

Excel では、標準関数を組み合わせることで、目的の平均値と標準偏差に適合する乱数セットを簡単に作成できます。小規模から中規模のデータセットやアドホックな用途に最適な以下の手順をご活用ください：

1．まず、目標となる平均値と標準偏差をそれぞれ別の空のセルに入力します。わかりやすく整理するため、ここではセル B1 に目標の平均値を、セル B2 に目標の標準偏差を入力すると仮定します。スクリーンショットをご参照ください：

2．初期のランダムデータを作成するには、セル B3 に次の数式を入力します：

=NORMINV(RAND(),$B$1,$B$2)

数式を入力後、フィルハンドルを下にドラッグして、乱数データセットに必要な行数だけセルを埋めてください。各セルは、指定された平均値と標準偏差に基づいて値を生成します。

ヒント：数式 =NORMINV(RAND(),$B$1,$B$2)について：

• RAND()は、ワークシートが再計算されるたびに0 から1 の間で新しい乱数を生成します。
• $B$1は、指定された平均値を参照します。
• $B$2は、目標となる標準偏差を参照します。

最新バージョンのExcel（2010 以降）では、=NORM.INV(RAND(),$B$1,$B$2)ご検討ください。機能は同一ですが、関数名が更新されています。

3．生成された数値が意図した平均値と標準偏差に統計的に近いかどうかを検証するには、生成されたサンプルの実際の平均値を次の数式で計算します。セル D1 に、サンプル平均を求める次の数式を入力してください：

=AVERAGE(B3:B16)

セル D2 で、次の数式を使用してサンプルの標準偏差を計算します：

=STDEV.P(B3:B16)

ヒント：

• B3:B16 はあくまで例として示した範囲です。ステップ2 で生成した乱数の個数に応じて、適宜調整してください。
• 大規模な乱数サンプルほど、大数の法則によって実際の平均値と標準偏差が指定値に近づいていきます。

4．さらにシリーズを調整して、目的の平均値と標準偏差に正確に一致させるには、初期の乱数値を正規化します。セル D3 に次の数式を入力してください：

=$B$1+(B3-$D$1)*$B$2/$D$2

フィルハンドルを、乱数の個数に応じた行数だけ下にドラッグしてください。この数式により、初期値が正規化され、B1 および B2 の平均値と標準偏差に正確にスケーリングされます。

ヒント：

• B1は必要な平均値です。
• B2には必要な標準偏差が入力されます。
• B3は元の乱数値です。
• D1は、これらの元の乱数値の平均値です。
• D2は、これらの元の乱数値の標準偏差です。

品質保証および記録目的で、最終的な値のセットが要件を満たしていることを確認するために、その平均値と標準偏差を再計算できます。

5．セル D17 で、次の数式を使用して最終的な乱数セットの平均値を計算します：

=AVERAGE(D3:D16)

次にセル D18 で、次の数式を使用して標準偏差を計算します：

=STDEV.P(D3:D16)

ヒント：D3:D16 は、生成された乱数の最終的な範囲を示します。

トラブルシューティング：

• #VALUE!エラーが表示された場合は、参照されているすべてのセル範囲を再度確認し、数式が空白セルや無効なセルを参照していないことをご確認ください。
• 数式が再計算のたびに変化し続ける場合は、最終的に得られた乱数を選び、コピーしてから特殊貼り付け > 値を使ってこれ以上の更新を防いでください。
• Excel の乱数生成機能は再計算に依存するため、一貫性が重要であれば、静的な結果を保存する必要があります。

---

VBA コード - 指定された平均値と標準偏差を持つ乱数を生成する

指定された平均値と標準偏差に一致する大量の乱数データを迅速に生成する必要がある場合（特に繰り返し実行、自動化、または大容量処理が求められるケース）には、VBA マクロが時間節約につながる最適なソリューションです。一度実行するだけで、ワークブック内に完全なデータセットを直接作成でき、手作業による繰り返し作業を削減し、数式のコピーに起因するエラーを最小限に抑えることができます。

このアプローチは次のような場合に適しています：

• シミュレーション、ストレステスト、または教育デモンストレーション用に乱数データセットを自動生成する場合。
• 手作業を最小限に抑えつつ、出力形式を標準化したい状況です。
• Excel の VBA エディターを利用できるユーザー。

数式による方法と比べて、VBA では動的な調整やより複雑なワークフローとの統合が可能ですが、マクロを有効にし、「マクロ対応」の.xlsm 形式で明示的に保存する必要がある点にご注意ください。

1．Excel のリボンで開発者ツールをクリックします（表示されていない場合は、)ファイル > オプション > リボンのカスタマイズから有効にしてください）。次に、Visual Basicを選びます。Visual Basic for Applications ウィンドウで、挿入 > モジュールをクリックし、空のモジュールウィンドウに次のコードをコピーしてください：

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2．実行ボタンをクリックするか（または)F5 キーを押す）と、マクロが起動します。まず、乱数を出力する範囲を選択するよう促すダイアログボックスが表示されます（たとえば、100 個の値を生成する場合は A1:A100 を選択してください）。次に、目的の平均値と標準偏差の入力を求められます。マクロは、指定された条件に一致する乱数で選択範囲を自動的に埋めます。

ヒントとトラブルシューティング：

• VBA では、Excel のNormInv 関数を使って正規分布に従う数値を生成できます。ただし、お使いのExcel バージョンでこの関数がサポートされているか、必ずご確認ください。古いバージョンのExcel では、関数名がNORMINVとなっている場合があります。
• 乱数のシードはRandomizeで設定されるため、実行するたびに異なる結果が得られます。
• 再現可能な結果が必要な場合は、Randomize 行をコメントアウトするか、削除してください。
• マクロは選択したリスト配置エリア内の既存データを上書きしますので、必要に応じて空の領域を選択してください。
• 不適切な値（たとえば負の値や標準偏差がゼロ）を入力すると、マクロは処理を中止し、警告メッセージを表示します。

---

関連記事：

• Excel で重複しない乱数を生成する
• Excel で正または負の乱数を生成する
• Excel で乱数の変化を停止する
• Excel で「はい」または「いいえ」をランダムに生成する