Hướng dẫn random number generator excel binomial - nhị thức tạo số ngẫu nhiên excel

I wanted to simulated number of vegetarian student in a particular population. A particular research paper has given an idea about a specific distribution. I like to test the idea using true random numbers. Paper suggest that 6% of a student of a given population is considered as vegetarian.

So I wanted to generate 40 random numbers with probability 6% which follows binomial distribution in Excel. I can generate numbers using a normal distribution. But I can't figure out how to generate them to follow binomial distribution, in this case I think binomial is more appropriate.

Thanks

asked May 9, 2018 at 3:00

1

Use the following function, remember Bernoulli is a special case of binomial distribution with 1 trial

=binom.inv(1, p, rand()) will generate 1 or 0 with chance of 1 being p.

Stefan Zobel

2,9077 gold badges26 silver badges34 bronze badges

answered Jan 22, 2020 at 22:58

If Excel doesn't have a random number generator for the binomial distribution (I didn't look), it's easy to make a simple one. Remember that a sample from a binomial distribution with parameters n and p is just the sum of n variables which are Bernoulli variables (i.e. simple coin tosses) with probability p. So you just generate n coin tosses, i.e. 1 with probability p and 0 with probability 1 - p, and add them up to get one sample from binomial(n, p).

There are more efficient ways to do it, but if you just need to get something working, this method has the advantage of simplicity.

answered May 9, 2018 at 3:22

Robert DodierRobert Dodier

16.1k2 gold badges29 silver badges46 bronze badges

Bạn là sinh viên? Bạn có biết rằng Amazon đang cung cấp 6 tháng Amazon Prime - vận chuyển hai ngày miễn phí, phim miễn phí và các lợi ích khác - cho sinh viên? Click vào đây để tìm hiểu thêm
Click here to learn more

Các số ngẫu nhiên được rút ra từ một phân phối xác suất cụ thể (nghĩa là các biến thể ngẫu nhiên) thường xuyên cần thiết trong nhiều trường, đặc biệt là cho các mô phỏng. Có nhiều thuật toán để tạo các biến thể ngẫu nhiên, nhưng tôi sẽ tập trung vào một kỹ thuật đơn giản có thể được sử dụng với một số hàm Excel tích hợp. Kỹ thuật này được gọi là phương pháp biến đổi nghịch đảo. Đối với mục đích của chúng tôi, chúng tôi muốn tránh sử dụng bất kỳ chương trình VBA nào hoặc bất kỳ thuật toán nhiều bước nào có thể là thách thức để thực hiện trong bảng tính, vì vậy chúng tôi sẽ chỉ sử dụng các chức năng tích hợp. Ưu điểm, ngoài sự đơn giản, là bạn có thể tạo ra nhiều biến thể ngẫu nhiên như bạn có thể cần bằng cách chỉ sao chép một công thức.

Hàm phân phối tích lũy (CDF) là gì?

Hàm phân phối tích lũy (CDF) là một hàm cho chúng ta biết xác suất rằng một số ngẫu nhiên được rút ra từ phân phối xác suất sẽ nhỏ hơn hoặc bằng một số giá trị. Chính thức hơn, CDF là tích phân của hàm mật độ xác suất (PDF) từ vô cực âm đến vô cực dương.

Ví dụ, hãy nghĩ về phân phối bình thường tiêu chuẩn, có giá trị trung bình là 0 và độ lệch chuẩn là 1. Bởi vì phân phối này đối xứng xung quanh giá trị trung bình, nên rõ ràng là xác suất vẽ một số ngẫu nhiên từ phân phối này ít hơn hơn 0 sẽ là 50%. Biểu đồ sau đây của CDF bình thường tiêu chuẩn cho thấy kết quả này:

Bạn có thể thấy trong biểu đồ ở trên mà bạn có thể chọn một số trên trục x và sau đó đọc xác suất vẽ giá trị nhỏ hơn hoặc bằng giá trị đó từ trục y. Đường màu đỏ tối cho thấy xác suất vẽ giá trị nhỏ hơn hoặc bằng 0. Tôi cũng đã thêm một đường màu đỏ nhẹ hơn so với xác suất vẽ giá trị nhỏ hơn hoặc bằng 1 (khoảng 84%).

Tất nhiên, CDF của phân phối xác suất khác nhau sẽ cho kết quả khác nhau. Excel có các chức năng tích hợp để hỗ trợ các phân phối xác suất sau:

Có một vài người khác, nhưng tôi bỏ qua chúng vì chúng không có chức năng nghịch đảo tương ứng.

Phương pháp biến đổi nghịch đảo

Mục tiêu của bài viết này là chứng minh cách sử dụng một số hàm tích hợp để tạo số ngẫu nhiên (biến thể) từ các phân phối xác suất nhất định. Phương pháp biến đổi nghịch đảo là một cách để làm điều này bằng cách đảo ngược những gì chúng ta đã thấy trong biểu đồ trên. Lưu ý rằng có sự tương ứng một-một giữa các giá trị trên trục y và các giá trị trên trục x. Hơn nữa, lưu ý rằng các giá trị trên trục y là xác suất, do đó tất cả đều nằm trong khoảng từ 0 đến 1. Do đó, nếu chúng ta có thể chọn ngẫu nhiên xác suất (tức là, giá trị từ 0 đến 1) thì chúng ta có thể tìm thấy giá trị X được liên kết . Giá trị X này sau đó sẽ là một biến thể ngẫu nhiên được rút ra từ phân phối xác suất đã chọn.

Phương pháp biến đổi nghịch đảo hoạt động như sau:

1. Tạo một biến thể ngẫu nhiên phân phối đồng đều (gọi nó là u) trong phạm vi 0 đến 1.
2. Cắm U vào hàm phân phối tích lũy nghịch đảo (CDF nghịch đảo, còn được gọi là hàm lượng tử của phân phối) của phân phối xác suất thích hợp (ví dụ: phân phối bình thường, phân phối gamma, v.v.). Giá trị kết quả (gọi nó là x) là một biến ngẫu nhiên được rút ra từ phân phối xác suất đã chọn.

Đối với bước 1, chúng ta có thể sử dụng chức năng RAND. Điều này tạo ra một biến thể ngẫu nhiên phân phối đồng đều giữa 0 và 1, đó chính xác là những gì chúng ta cần. Lưu ý rằng chức năng này không biến động, do đó, số ngẫu nhiên mà nó tạo sẽ thay đổi mỗi khi bảng tính được tính toán lại.

Bước 2 thường khó khăn hơn vì nhiều hàm phân phối tích lũy không có nghịch đảo dạng đóng (ví dụ: phân phối bình thường). Tuy nhiên, Excel có các chức năng tích hợp cho nghịch đảo của một số phân phối xác suất mà chúng ta có thể sử dụng.

Có một vài người khác, nhưng tôi bỏ qua chúng vì chúng không có chức năng nghịch đảo tương ứng.

Phương pháp biến đổi nghịch đảo

Mục tiêu của bài viết này là chứng minh cách sử dụng một số hàm tích hợp để tạo số ngẫu nhiên (biến thể) từ các phân phối xác suất nhất định. Phương pháp biến đổi nghịch đảo là một cách để làm điều này bằng cách đảo ngược những gì chúng ta đã thấy trong biểu đồ trên. Lưu ý rằng có sự tương ứng một-một giữa các giá trị trên trục y và các giá trị trên trục x. Hơn nữa, lưu ý rằng các giá trị trên trục y là xác suất, do đó tất cả đều nằm trong khoảng từ 0 đến 1. Do đó, nếu chúng ta có thể chọn ngẫu nhiên xác suất (tức là, giá trị từ 0 đến 1) thì chúng ta có thể tìm thấy giá trị X được liên kết . Giá trị X này sau đó sẽ là một biến thể ngẫu nhiên được rút ra từ phân phối xác suất đã chọn.

Phương pháp biến đổi nghịch đảo hoạt động như sau:

Tạo một biến thể ngẫu nhiên phân phối đồng đều (gọi nó là u) trong phạm vi 0 đến 1.

=Norm.S.Inv(Rand())

Cắm U vào hàm phân phối tích lũy nghịch đảo (CDF nghịch đảo, còn được gọi là hàm lượng tử của phân phối) của phân phối xác suất thích hợp (ví dụ: phân phối bình thường, phân phối gamma, v.v.). Giá trị kết quả (gọi nó là x) là một biến ngẫu nhiên được rút ra từ phân phối xác suất đã chọn.

Đối với bước 1, chúng ta có thể sử dụng chức năng RAND. Điều này tạo ra một biến thể ngẫu nhiên phân phối đồng đều giữa 0 và 1, đó chính xác là những gì chúng ta cần. Lưu ý rằng chức năng này không biến động, do đó, số ngẫu nhiên mà nó tạo sẽ thay đổi mỗi khi bảng tính được tính toán lại.

Bước 2 thường khó khăn hơn vì nhiều hàm phân phối tích lũy không có nghịch đảo dạng đóng (ví dụ: phân phối bình thường). Tuy nhiên, Excel có các chức năng tích hợp cho nghịch đảo của một số phân phối xác suất mà chúng ta có thể sử dụng.

Các chức năng CDF nghịch đảo tích hợp của Excel

Phân bổ

Hàm Excel

Tiêu chuẩn bình thường

Norm.s.inv (xác suất)

Norm.inv (xác suất, trung bình, standard_dev)

Beta.inv (Xác suất, Alpha, Beta, [A],)

Binom.inv (thử nghiệm, xác suất_s, alpha)

Chisq.inv (Xác suất, DEG_FREEDOM)

F.inv (xác suất, deg_freedom1, deg_freedom2)

= Chisq.inv (rand (), 2)

Phân phối F

Chọn một biến thể ngẫu nhiên từ phân phối F với 3 và 7 độ tự do.

= F.inv (rand (), 3, 7)

Phân phối gamma

Vẽ một biến thể ngẫu nhiên từ phân phối gamma với tham số hình dạng 2 và tham số tỷ lệ là 1.

= Gamma.inv (rand (), 2, 1)

Sự phân phối lognatral

Vẽ một biến thể ngẫu nhiên từ phân phối lognatural với giá trị trung bình là 0,10 và độ lệch chuẩn là 0,20:

Lognorm.inv (rand (), 0,10, 0,20)

Phân phối sinh viên T

Tạo một biến thể ngẫu nhiên từ phân phối sinh viên T với 3 độ tự do.

= T.inv (rand (), 3)

Tôi hy vọng rằng bạn đã tìm thấy hướng dẫn này là hữu ích. Vui lòng tải xuống bảng tính ví dụ có chứa tất cả các ví dụ này. Nếu bạn có bất kỳ câu hỏi, xin vui lòng liên hệ với tôi.

Làm thế nào để bạn tạo ra một số ngẫu nhiên nhị thức?

Làm cách nào để phân phối ngẫu nhiên các số ngẫu nhiên trong Excel?