Nó thường hữu ích để tạo một mô hình bằng cách sử dụng mô phỏng. Thông thường, điều này có hình thức tạo ra một loạt các quan sát ngẫu nhiên (thường dựa trên phân phối thống kê cụ thể) và sau đó nghiên cứu các quan sát thu được bằng cách sử dụng các kỹ thuật được mô tả trong phần còn lại của trang web này. Cách tiếp cận này thường được gọi là mô phỏng Monte CarloChức năng trang tínhHàm Excel. Excel cung cấp các hàm sau để tạo số ngẫu nhiên Show
RAND() – tạo một số ngẫu nhiên trong khoảng từ 0 đến 1; . e. một số ngẫu nhiên x sao cho 0 ≤ x < 1 RANDBETWEEN(a, b) – tạo ra một số nguyên ngẫu nhiên giữa a và b (bao gồm) Lưu ý rằng các hàm này không ổn định, theo nghĩa là mỗi khi có thay đổi đối với trang tính, giá trị của chúng được tính toán lại và một số ngẫu nhiên khác được tạo. Nếu bạn không muốn điều này xảy ra, hãy nhập RAND() trên thanh công thức và nhấn phím chức năng F9. Điều này sẽ thay thế công thức RAND() bằng giá trị được tạo. Ngoài ra, bạn có thể sao chép số ngẫu nhiên (hoặc một dãy số ngẫu nhiên) bằng Ctrl-C rồi dán lại vào cùng một vị trí bằng Trang chủ > Bảng tạm. Dán và sau đó chọn tùy chọn Dán giá trị RANDBETWEEN chỉ tạo ra các giá trị số nguyên. Nếu bạn muốn một số ngẫu nhiên có thể là bất kỳ số thập phân nào giữa a và b, hãy sử dụng công thức sau để thay thế = a + (b − a) * RAND() Hàm Excel 365. Excel 365 cung cấp chức năng mảng động sau với hiệu ứng lan tỏa (xem Công thức mảng động) RANDARRAY(mũi tên, ncols, a, b). điền vào một phạm vi nrows × ncols bắt đầu trong ô hiện tại với các số ngẫu nhiên trong khoảng từ a đến b RANDARRAY(mũi tên, ncols, a, b, TRUE). lấp đầy một phạm vi nrows × ncols bắt đầu trong ô hiện tại với các số nguyên ngẫu nhiên trong khoảng từ a đến b Nếu bỏ qua nrows, ncols và b mặc định là 1 và a mặc định là 0 E. g. để tạo 10 số ngẫu nhiên trong khoảng từ 0 đến 1 bằng Excel 365, bạn nhập công thức =RANDARRAY(10) vào ô A1 rồi nhấn Enter Nếu bạn không sử dụng Excel 365, thay vào đó, bạn có thể nhập công thức =RAND() vào ô A1, đánh dấu phạm vi A1. A10 và nhấn Ctrl-D Nhiều chức năng trang tính hơnChức năng thống kê thực. Gói tài nguyên thống kê thực cung cấp chức năng RANDOM tạo ra một số ngẫu nhiên không biến động RANDOM(a, b, FALSE, seed) = số ngẫu nhiên giữa a và b; . e là phiên bản cố định của a + (b − a) * RAND() RANDOM(a, b, TRUE, seed) = số nguyên ngẫu nhiên giữa a và b, đã bao gồm; . e. một phiên bản không bay hơi của RANDBETWEEN(a, b) Nếu a bị bỏ qua thì giá trị mặc định là 0, nếu b được bỏ qua thì giá trị mặc định là 1 và nếu đối số thứ ba bị bỏ qua thì giá trị mặc định là FALSE Nếu hạt giống ≤ 0 hoặc bị bỏ qua thì không có hạt giống nào được sử dụng, trong khi nếu đó là giá trị dương thì giá trị này được sử dụng làm hạt giống. Một hạt giống có thể được sử dụng để tạo một chuỗi các giá trị giả ngẫu nhiên có thể lặp lại Gói tài nguyên thống kê thực cũng cung cấp chức năng mảng sau RANX(mũi tên, hạt giống, ncols). trả về một mảng nrows × ncols gồm các số ngẫu nhiên không thay đổi trong khoảng từ 0 đến 1 trong đó hạt giống dành cho RANDOM; Công cụ phân tích dữ liệuCông cụ phân tích dữ liệu Excel. Ngoài các hàm RAND và RANDBETWEEN, Excel còn cung cấp công cụ phân tích dữ liệu Tạo số ngẫu nhiên để tạo các số ngẫu nhiên ở dạng bảng tuân theo một trong số các phân phối. Bạn có thể chỉ định các giá trị sau bằng công cụ này Số biến = số mẫu. Đây là số cột trong bảng kết quả do Excel tạo ra Số lượng số ngẫu nhiên = kích thước của mỗi mẫu. Đây là số hàng trong bảng đầu ra được tạo bởi Excel phân phối mong muốn. chỉ định một trong các bản phân phối sau
Hạt giống ngẫu nhiên = một giá trị tùy chọn được sử dụng để tạo số ngẫu nhiên đầu tiên. Bạn có thể sử dụng lại giá trị này sau để đảm bảo rằng các số ngẫu nhiên giống nhau được tạo ra. Nếu trường này để trống, thì một số ngẫu nhiên mới sẽ được tạo mỗi lần ví dụví dụ 1. Mô phỏng Định lý giới hạn trung tâm bằng cách tạo 100 mẫu có kích thước 50 từ một tổng thể có phân bố đồng đều trong khoảng [50, 150]. Do đó, mỗi phần tử dữ liệu trong mỗi mẫu là một giá trị được chọn ngẫu nhiên, có khả năng như nhau trong khoảng từ 50 đến 150 Chọn Dữ liệu > Phân tích. Phân tích dữ liệu và chọn công cụ phân tích dữ liệu Tạo số ngẫu nhiên. Điền vào hộp thoại hiện ra như hình 1
Hình 1 – Hộp thoại Trình tạo số ngẫu nhiên Đầu ra là một mảng Excel có 50 hàng và 100 cột. Tiếp theo, chúng tôi tính giá trị trung bình của từng cột bằng cách sử dụng hàm AVERAGE. Kết quả là một hàng có 100 mục chứa giá trị trung bình của từng mẫu trong số 100 mẫu. Điều này được thể hiện trong Hình 2 (được định dạng lại thành mảng 10 × 10 để vừa với màn hình hơn)
Hình 2 – Giá trị trung bình của 100 mẫu ngẫu nhiên Sử dụng công cụ phân tích dữ liệu Biểu đồ của Excel, giờ đây chúng ta tạo một biểu đồ của 100 phương tiện mẫu, như thể hiện ở phía bên phải của Hình 3
Hình 3 – Kiểm định Định lý giới hạn trung tâm Sử dụng AVERAGE và STDEV. S, chúng tôi tính giá trị trung bình và độ lệch chuẩn của 100 giá trị trung bình mẫu từ Hình 2. Giá trị trung bình của mẫu là 100. 0566 (ô B7 của Hình 9. 8. 3) và độ lệch chuẩn là 4. 318735 (ô B8). Như bạn có thể thấy, biểu đồ là một đường cong hình chuông hơi không hoàn hảo của phân phối chuẩn Vì mẫu được lấy từ phân phối đồng đều trong phạm vi [50, 150], như có thể thấy từ Phân phối đồng nhất, nên giá trị trung bình của tổng thể là = 100 (ô B11). Ngoài ra, độ lệch chuẩn là = 28. 86751 (ô B12). Dựa trên Định lý giới hạn trung tâm, chúng tôi hy vọng rằng giá trị trung bình của phương tiện mẫu sẽ là giá trị trung bình của dân số, điều này dường như đúng kể từ năm 100. 0566 khá gần với 100. Chúng tôi cũng hy vọng rằng độ lệch chuẩn của mẫu có nghĩa là
(ô B16) gần hợp lý với giá trị quan sát được là 4. 318735 Mẫu ngẫu nhiên từ một phân phốiChúng tôi cũng có thể tạo thủ công một mẫu ngẫu nhiên tuân theo bất kỳ bản phân phối nào được Excel (hoặc Gói tài nguyên thống kê thực) hỗ trợ mà không cần sử dụng công cụ phân tích dữ liệu. e. g. để tạo mẫu gồm 25 phần tử tuân theo phân phối chuẩn với giá trị trung bình là 60 và độ lệch chuẩn là 20, chúng ta chỉ cần sử dụng công thức =NORM. INV(RAND(),60,20) 25 lần Ở cột C (i. e. cột giá trị x) trong Hình 4, chúng ta đã làm được điều đó. e. g. ô C4 chứa công thức = BÌNH THƯỜNG. INV(B4,$G$3,$G$4) trong đó ô B4 (và tất cả các ô khác trong cột B) chứa công thức =RAND(). Cột D chứa các giá trị mật độ xác suất (i. e. gắn nhãn giá trị y) cho mỗi giá trị x. e. g. ô D4 chứa công thức = BÌNH THƯỜNG. DIST(C4,$G$3,$G$4,SAI) Cuối cùng, chúng tôi tạo một biểu đồ phân tán của các giá trị x so với. các giá trị y bằng cách đánh dấu phạm vi C4. D28 và chọn Chèn > Biểu đồ. Phân tán như được mô tả trong Biểu đồ Excel. Biểu đồ phân tán trong Hình 4 có dạng đường cong hình chuông đặc trưng của phân bố chuẩn
Hình 4 – Tạo mẫu từ phân phối chuẩn Vẽ một mẫu ngẫu nhiên từ một phân phốiTheo cách tương tự, chúng tôi có thể tạo các mẫu ngẫu nhiên cho bất kỳ bản phân phối nào được hỗ trợ bởi Excel (hoặc Gói tài nguyên thống kê thực). e. g. để tạo một phần tử từ phân phối Poisson với giá trị trung bình bằng 7, chúng tôi sử dụng công thức =POISSON_INV(RAND(),7) trong đó POISSON_INV là hàm Thống kê thực được mô tả trong Phân phối Poisson Chúng ta cũng có thể sử dụng các hàm Thống kê thực RANDOM hoặc RANDX thay cho RAND. Điều này đặc biệt hữu ích nếu bạn muốn một số ngẫu nhiên không thay đổi hoặc khi bạn muốn sử dụng một hạt giống Trong môi trường Excel 365, RANDX có thể được sử dụng làm hàm mảng động. e. g. để có được ước tính về giá trị trung bình của phân phối beta với α = 4 và β = 6, bạn có thể thực hiện mô phỏng Monte Carlo với kích thước 10.000 thông qua công thức trang tính =AVERAGE(BETA. INV(RANDX(10000),4,6)) để có được kết quả như 3. 99901, gần với giá trị lý thuyết của α/( α+β) = 4/(4+6) =. 4. Bạn cũng có thể sử dụng một hạt giống như trong công thức = AVERAGE(BETA. INV(RANDX(10000,123),4,6)) để có được. 39985 Xem Mô phỏng phân phối liên quan đến mô phỏng phân phối không xác định Số ngẫu nhiên có trọng sốKhi sử dụng công thức số ngẫu nhiên trong Excel =RANDBETWEEN(1, 4), xác suất xảy ra bất kỳ một trong các giá trị 1, 2, 3 hoặc 4 là 25%. Bây giờ chúng tôi mô tả một cách thay đổi xác suất xảy ra bất kỳ giá trị cụ thể nào Chức năng thống kê thực. Gói tài nguyên thống kê thực cung cấp chức năng sau WRAND(R1) = một số nguyên ngẫu nhiên trong khoảng từ 1 đến n trong đó R1 là phạm vi trọng số của n × 1 cột ví dụ 2. Tạo 20 số ngẫu nhiên từ tập hợp {1, 2, 3, 4} bằng cách sử dụng các trọng số trong phạm vi H4. H7 của Hình 5 Do đó, xác suất tạo ra số 1 là 50/(50+10+20+20) = 50%, xác suất tạo ra số 2 là 10/(50+10+20+20) = 10%, v.v. Kết quả được thể hiện trong cột J của Hình 5
Hình 5 – Tạo số ngẫu nhiên có trọng số Ở đây, mỗi ô trong phạm vi J4. J23 chứa công thức =WRAND($H$4. $H$7). Phạm vi L3. M7 chứa một bảng số lần mỗi giá trị 1, 2, 3 và 4 xuất hiện trong phạm vi J4. J23. e. g. ô L4 chứa công thức =COUNTIF(J$4. J$23,K4). Chúng tôi quan sát thấy rằng tỷ lệ phần trăm tần số trong phạm vi M4. M7 tương tự (nhưng không đồng nhất) với các xác suất xuất phát từ các trọng số Hàm Excel nào có thể được sử dụng để hỗ trợ mô phỏng cho phân tích rủi ro?@RISK (phát âm là “có nguy cơ”) là công cụ bổ trợ cho Microsoft Excel giúp bạn đưa ra quyết định tốt hơn thông qua phân tích và lập mô hình rủi ro. Nó thực hiện điều này bằng cách sử dụng một kỹ thuật được gọi là mô phỏng Monte Carlo.
Bảng tính mô phỏng là gì?Mô phỏng bảng tính đơn giản liên quan đến việc sử dụng bảng tính để biểu diễn mô hình, lấy mẫu, thực hiện tính toán mô hình và báo cáo kết quả.
Trò chơi có thể được tạo bằng Excel không?Vâng, chơi trò chơi Excel không phải là một điều mới, tuy nhiên, chúng ta đều biết rằng Excel là một công cụ phân tích dữ liệu tuyệt vời. Bạn có thể chơi các trò chơi Excel dựa trên flash hoặc dựa trên macro . Đó là lý do tại sao đôi khi bạn có thể cần kích hoạt macro để vui vẻ với excel. |
