Hướng dẫn generate data normal distribution excel - tạo dữ liệu excel phân phối chuẩn

Dự báo cho bất kỳ doanh nghiệp nhỏ liên quan đến phỏng đoán. Bạn biết doanh nghiệp của bạn và hiệu suất trong quá khứ của nó, nhưng bạn có thể không thoải mái dự đoán tương lai. Sử dụng Excel là một cách tuyệt vời để thực hiện phân tích what-if và các công thức tạo ra các giá trị ngẫu nhiên có ý nghĩa cho việc trợ giúp dự báo của bạn trình bày một loạt các kết quả để phân tích.

Chức năng rand ()

1. Excel sử dụng hàm rand () để trả về một số phân phối ngẫu nhiên và đồng đều trong khoảng từ 0 đến 1, mỗi khi tính toán được tính toán. Ví dụ, nếu bạn đang phân tích mức độ biến động trong bán hàng của bạn sẽ ảnh hưởng đến doanh nghiệp của bạn như thế nào, con số này ít sử dụng, vì tất cả các kết quả ngẫu nhiên đều tuân theo phân phối thống nhất, trong khi kết quả bán hàng thường tuân theo phân phối bình thường.

Phân phối bình thường

1. Phân phối bình thường, hoặc Gaussian, mô tả một mẫu trong đó các giá trị trong phạm vi được xác định của bạn xảy ra thường xuyên hơn xung quanh một điểm trung tâm hoặc trung bình. Sử dụng ví dụ dự đoán bán hàng, bạn quan sát thấy doanh số cực cao và thấp xảy ra không thường xuyên, nghĩa là chúng không được phân phối đồng đều. Ví dụ, nếu doanh số trung bình hàng tuần của bạn là 2000 đô la, hai tuần liền kề cho thấy doanh thu là 1.900 đô la và 2.100 đô la có khả năng hơn so với tuần là 0 và 4.000 đô la.

Hàm Norminv

1. Hàm Norminv trong Excel trả về một số thường được phân phối xung quanh một giá trị trung bình, bị thay đổi bởi độ lệch chuẩn và theo hệ số xác suất. Trong Excel, cú pháp cho hàm này là Norminv (xác suất, giá trị trung bình, độ lệch chuẩn). Sử dụng ví dụ bán hàng, với trung bình doanh số hàng tuần là 2.000 đô la dao động khoảng 500 đô la trở xuống, bạn biết độ lệch trung bình và tiêu chuẩn. Bạn có thể sử dụng hàm rand () để thiết lập xác suất và tạo một biến ngẫu nhiên với phân phối bình thường.

Cú pháp công thức

1. Sử dụng công thức "= Norminv (rand (), b2, c2)", trong đó hàm rand () tạo xác suất của bạn, B2 cung cấp giá trị trung bình và C2 của bạn về độ lệch chuẩn của bạn. Bạn có thể thay đổi B2 và C2 để tham chiếu các ô khác nhau hoặc nhập các giá trị vào chính công thức. Sự kết hợp của hai hàm đảm bảo tạo ra một biến ngẫu nhiên với phân phối bình thường.

Như một ví dụ f (0) = 50%, vì vậy, có 50% cơ hội một giá trị ngẫu nhiên từ phân phối bình thường của chúng tôi sẽ dưới 0.

Từ biểu đồ này, chúng ta cũng có thể bắt đầu với xác suất trên trục y và nhận được giá trị từ phân phối bình thường của chúng ta trên trục x. Đây là gọi nghịch đảo của một hàm.Normal distribution (also known as a Gaussian distribution) is any distribution with the following probability density function.

Nếu chúng ta bắt đầu ở mức 0,8 trên trục y và theo dõi theo chiều ngang cho đến khi chúng ta nhấn biểu đồ, sau đó di chuyển theo chiều dọc, chúng ta sẽ đến 0,788 trên trục x. Điều này có nghĩa là 0,788 là nghịch đảo của 0,8.

Sử dụng hàm nghịch đảo là cách chúng tôi sẽ có được tập hợp các giá trị ngẫu nhiên được phân phối bình thường. Chúng tôi sẽ sử dụng hàm rand () để tạo giá trị ngẫu nhiên từ 0 đến 1 trên trục y của chúng tôi và sau đó nhận được nghịch đảo của nó với hàm định mức.inv sẽ dẫn đến giá trị bình thường ngẫu nhiên của chúng tôi trên trục x.

Nếu chúng ta thực hiện tính toán này 1.000 lần, chúng ta có thể vẽ đồ thị bằng biểu đồ biểu đồ và chúng ta bắt đầu thấy đường cong hình chuông xuất hiện.(μ-3σ,μ+3σ).

Với 10.000 giá trị, phân phối trở nên rõ ràng hơn.

=(1/SQRT(2*PI()*StdDev^2))*EXP(-1*(X-Mean)^2/(2*StdDev^2))(1/SQRT(2*PI()*StdDev^2))*EXP(-1*(X-Mean)^2/(2*StdDev^2))

• Trong thực tế vì luật có số lượng lớn, càng nhiều giá trị bình thường được tạo ngẫu nhiên này, biểu đồ của chúng ta sẽ xuất hiện gần như hình chuông. – This is any real number.
• Phương thức Muller Box Muller để tạo các giá trị bình thường ngẫu nhiên – This is the mean of the normally distributed random variable.
• Phương pháp M-Muller phụ thuộc vào Định lý rằng nếu U1 và U2 là các biến ngẫu nhiên độc lập được phân phối đồng đều trong khoảng (0, 1) thì Z1 và Z2 sẽ là các biến ngẫu nhiên độc lập với phân phối chuẩn tiêu chuẩn (trung bình = 0 và độ lệch chuẩn = 1). – This is the standard deviation of the normally distributed random variable.

Chúng tôi sẽ dễ dàng có thể tạo các công thức này trong excel với u1 = rand () và u2 = rand (). Đối với mục đích của chúng tôi mặc dù, chúng tôi sẽ chỉ cần tính toán Z1.

Chúng ta có thể vẽ đồ thị hàm mật độ xác suất bình thường trong Excel bằng cách thiết lập một bảng với hai cột giá trị.

• X-Đây là một loạt các số thực sẽ xuất hiện trên trục x mà chúng tôi sẽ đánh giá hàm mật độ bình thường của chúng tôi trên. – This is a series of real numbers that will appear on our X-axis which we will evaluate our normal density function on.
• F (x) - Đây là kết quả của hàm mật độ bình thường của chúng tôi được đánh giá tại X. – This is the result of our normal density function evaluated at X.

Nếu chúng tôi chọn bảng của mình thì hãy chuyển đến tab Chèn và chọn biểu đồ dòng từ phần Biểu đồ. Chúng ta có thể thấy kết quả là một đường cong hình chuông đẹp giữa giá trị trung bình.Insert tab and select a Line Chart from the Charts section. We can see the result is a nice bell shaped curve centered around the mean value.

Tạo một tập hợp các số ngẫu nhiên được phân phối bình thường trong Excel

Có thể tạo ra một tập hợp các giá trị phân phối bình thường trong Excel không? Vâng, đúng vậy, nhưng chúng ta sẽ cần xem xét hàm phân phối tích lũy f (x) = p (x

Công thức liên quan đến tính toán nhưng rất may chức năng định mức Excel Excel sẽ thực hiện tính toán này cho chúng tôi.NORM.DIST function will do this calculation for us.

=NORM.DIST(X,Mean,StdDev,TRUE)NORM.DIST(X,Mean,StdDev,TRUE)

• X - Đây là bất kỳ số nào từ phân phối. – This is any number from the distribution.
• Trung bình - đây là giá trị trung bình của phân phối bình thường. – This is the mean of the normal distribution.
• STDDEV - Đây là độ lệch chuẩn của phân phối bình thường. – This is the standard deviation of the normal distributed.
• Đúng - đây là một đầu vào được xác định trước cho hàm định mức.Dist nói với Excel để tính toán phân phối tích lũy. – This is a predefined input for the NORM.DIST function which tells Excel to calculate the cumulative distribution.

Chúng ta cũng có thể vẽ đồ thị theo cách tương tự như hàm mật độ xác suất và tạo biểu đồ dòng từ phần biểu đồ của tab Chèn.Line Chart from the Charts section of the Insert tab.

Lưu ý rằng trục y của biểu đồ này đi từ 0 đến 1. Đây là giá trị xác suất và biểu thị xác suất của một giá trị ngẫu nhiên từ phân phối bình thường của chúng tôi nhỏ hơn hoặc bằng một giá trị nhất định.Y-axis of this chart goes from 0 to 1. This is a probability value and represents the probability of a random value from our normal distribution being less than or equal to a given value.

Như một ví dụ f (0) = 50%, vì vậy, có 50% cơ hội một giá trị ngẫu nhiên từ phân phối bình thường của chúng tôi sẽ dưới 0.

Từ biểu đồ này, chúng ta cũng có thể bắt đầu với xác suất trên trục y và nhận được giá trị từ phân phối bình thường của chúng ta trên trục x. Đây là gọi nghịch đảo của một hàm.Y-axis and get a value from our normal distribution on the X-axis. This is call the inverse of a function.

Nếu chúng ta bắt đầu ở mức 0,8 trên trục y và theo dõi theo chiều ngang cho đến khi chúng ta nhấn biểu đồ, sau đó di chuyển theo chiều dọc, chúng ta sẽ đến 0,788 trên trục x. Điều này có nghĩa là 0,788 là nghịch đảo của 0,8.0.8 on the Y-axis and follow out horizontally until we hit the graph, then move vertically down we will arrive at the 0.788 on the X-axis. This means 0.788 is the inverse of 0.8.

Sử dụng hàm nghịch đảo là cách chúng tôi sẽ có được tập hợp các giá trị ngẫu nhiên được phân phối bình thường. Chúng tôi sẽ sử dụng hàm rand () để tạo giá trị ngẫu nhiên từ 0 đến 1 trên trục y của chúng tôi và sau đó nhận được nghịch đảo của nó với hàm định mức.inv sẽ dẫn đến giá trị bình thường ngẫu nhiên của chúng tôi trên trục x.RAND() function to generate a random value between 0 and 1 on our Y-axis and then get the inverse of it with the NORM.INV function which will result in our random normal value on the X-axis.

=NORM.INV(RAND(),Mean,StdDev)NORM.INV(RAND(),Mean,StdDev)

• Trung bình - đây là giá trị trung bình của phân phối bình thường. – This is the mean of the normal distribution.
• STDDEV - Đây là độ lệch chuẩn của phân phối bình thường. – This is the standard deviation of the normal distributed.

Đúng - đây là một đầu vào được xác định trước cho hàm định mức.Dist nói với Excel để tính toán phân phối tích lũy.1,000 times we can graph it with a Histogram chart and we start to see a bell shape curve emerge.

Chúng ta cũng có thể vẽ đồ thị theo cách tương tự như hàm mật độ xác suất và tạo biểu đồ dòng từ phần biểu đồ của tab Chèn.10,000 values, the distribution becomes more clear.

Lưu ý rằng trục y của biểu đồ này đi từ 0 đến 1. Đây là giá trị xác suất và biểu thị xác suất của một giá trị ngẫu nhiên từ phân phối bình thường của chúng tôi nhỏ hơn hoặc bằng một giá trị nhất định.

Như một ví dụ f (0) = 50%, vì vậy, có 50% cơ hội một giá trị ngẫu nhiên từ phân phối bình thường của chúng tôi sẽ dưới 0.

Từ biểu đồ này, chúng ta cũng có thể bắt đầu với xác suất trên trục y và nhận được giá trị từ phân phối bình thường của chúng ta trên trục x. Đây là gọi nghịch đảo của một hàm.U1 and U2 are independent random variables uniformly distributed in the interval (0, 1) then Z1 and Z2 will be independent random variables with a standard normal distribution (mean = 0 and standard deviation = 1).

Nếu chúng ta bắt đầu ở mức 0,8 trên trục y và theo dõi theo chiều ngang cho đến khi chúng ta nhấn biểu đồ, sau đó di chuyển theo chiều dọc, chúng ta sẽ đến 0,788 trên trục x. Điều này có nghĩa là 0,788 là nghịch đảo của 0,8.U1=RAND() and U2=RAND(). For our purposes though, we will only need to calculate Z1.

Sử dụng hàm nghịch đảo là cách chúng tôi sẽ có được tập hợp các giá trị ngẫu nhiên được phân phối bình thường. Chúng tôi sẽ sử dụng hàm rand () để tạo giá trị ngẫu nhiên từ 0 đến 1 trên trục y của chúng tôi và sau đó nhận được nghịch đảo của nó với hàm định mức.inv sẽ dẫn đến giá trị bình thường ngẫu nhiên của chúng tôi trên trục x.Z1 will have a mean of 0 and standard deviation of 1, we can transform Z1 to a new random variable X=Z1*σ+μ to get a normal distribution with mean μ and standard deviation σ.

=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*StdDev+MeanSQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*StdDev+Mean

• Trung bình - đây là giá trị trung bình của phân phối bình thường. – This is the mean of the normal distribution.
• STDDEV - Đây là độ lệch chuẩn của phân phối bình thường. – This is the standard deviation of the normal distributed.

Đúng - đây là một đầu vào được xác định trước cho hàm định mức.Dist nói với Excel để tính toán phân phối tích lũy.Histogram for a large number of calculations and see a nice well defined bell shaped curve.

Chúng ta cũng có thể vẽ đồ thị theo cách tương tự như hàm mật độ xác suất và tạo biểu đồ dòng từ phần biểu đồ của tab Chèn.

Lưu ý rằng trục y của biểu đồ này đi từ 0 đến 1. Đây là giá trị xác suất và biểu thị xác suất của một giá trị ngẫu nhiên từ phân phối bình thường của chúng tôi nhỏ hơn hoặc bằng một giá trị nhất định.