如何用Excel公式生成符合指定参数的偏态正态分布随机数？

Got it, let's break this down step by step—since you want pure Excel formulas, no VBA, and easy parameter tweaks, this approach will work perfectly for generating 1000 groups of 130 random numbers that match your target skewed normal distribution.

Step 1: Set Up Your Parameter Dashboard #

First, create a dedicated area to store your adjustable parameters (so you can swap out mean, standard deviation, and skewness anytime without rewriting formulas). Let's use cells A1-A4:

• A1: Mean (enter -3.99 for your initial case)
• A2: Standard Deviation (enter 3.17)
• A3: Skewness (enter -0.71)
• A4: Skew Adjustment Coefficient (δ) – use this formula to calculate it automatically:

  =SIGN(A3)*SQRT( (ABS(A3)/2)^(2/3) / (1 + (ABS(A3)/2)^(2/3)) )
  

  What this does: This coefficient translates your target skewness into a value we can use to adjust the shape of the normal distribution. The SIGN(A3) ensures the skew direction matches your target (negative for left-skewed, positive for right-skewed).

Step 2: Generate the Skewed Random Numbers #

Now, you'll use a combination of Excel's built-in random and normal distribution functions to create your data.

For any cell where you want a skewed random number, use this formula:

=$A$1 + $A$2*( $A$4*NORM.S.INV(RAND()) + SQRT(1-$A$4^2)*NORM.S.INV(RAND()) )

Let's break down what each part does:

• RAND(): Generates a uniform random number between 0 and 1.
• NORM.S.INV(RAND()): Converts that uniform number into a standard normal random number (mean = 0, standard deviation = 1). We use two independent instances of this to create two separate normal variables.
• The weighted combination $A$4*Z1 + SQRT(1-$A$4^2)*Z2: Adjusts the standard normal variables to introduce the desired skewness while keeping the overall variance controlled.
• $A$1 + $A$2*(...): Scales and shifts the adjusted distribution to match your target mean and standard deviation.

Step 3: Expand to 1000 Groups of 130 Numbers #

To create your full dataset:

1. Pick a starting cell (e.g., B1). Paste the formula above into B1.
2. Drag the formula horizontally to fill 130 columns (this makes your first group of 130 numbers).
3. Select the entire first row of 130 numbers, then drag vertically to fill 1000 rows (this creates all 1000 groups).

Step 4: Verify Your Results (Optional but Recommended) #

To make sure your generated data matches your target stats, use these Excel functions on a sample of your data:

• Mean: =AVERAGE(range)
• Standard Deviation (population): =STDEV.P(range)
• Skewness: =SKEW(range)
  You'll see small deviations from your target values (since it's random data), but they should be close enough for most use cases.

Pro Tip: Fix Your Data #

Since RAND() recalculates every time you change the sheet, press F9 to refresh the random numbers. If you want to lock in a set of numbers, select all your data, copy it, then right-click > Paste Special > Values.

内容的提问来源于stack exchange，提问作者thekingzapper