Ngày 17 tháng 2 năm 2022
Chỉnh sửa Công thức Excel trong WORD – Mẹo và Thủ thuật Bảng tính
Đọc thêm
Chỉnh sửa Công thức Excel trong WORD – Mẹo và Thủ thuật Bảng tính
Ngày 8 tháng 2 năm 2021
51. Oz du Soleil & Hội nghị thượng đỉnh Excel toàn cầu 2021
Đọc thêm
51. Oz du Soleil & Hội nghị thượng đỉnh Excel toàn cầu 2021
29 Tháng Một, 2021
Hội nghị thượng đỉnh Excel toàn cầu 2021
Đọc thêm
Hội nghị thượng đỉnh Excel toàn cầu 2021
22 Tháng Một, 2021
50. Randy Austin – Excel dành cho người làm nghề tự do
Đọc thêm
50. Randy Austin – Excel dành cho người làm nghề tự do
Ngày 8 tháng 1 năm 2021
49. Theresa Estrada – Trưởng nhóm Giám đốc Chương trình Chính của Microsoft
Đọc thêm
49. Theresa Estrada – Trưởng nhóm Giám đốc Chương trình Chính của Microsoft
26 Tháng Năm, 2020
Tất cả các tra cứu Excel được giải thích
Đọc thêm
Tất cả các tra cứu Excel được giải thích
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ính
Hàm Excel. Excel cung cấp các hàm sau để tạo số ngẫu nhiên
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ơn
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 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ệu
Cô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 thủ 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
- Thống nhất, chỉ định α [giới hạn dưới] và β [giới hạn trên]
- Bình thường, chỉ định µ [giá trị trung bình] và σ [độ lệch chuẩn]
- Bernoulli, xác định p [xác suất thành công];
- Nhị thức, chỉ định p [xác suất thành công] và n [số lần thử]
- Poisson, chỉ định λ [có nghĩa là]
- Có khuôn mẫu – chỉ định giới hạn trên và dưới, một bước, tốc độ lặp lại cho các giá trị và tốc độ lặp lại cho chuỗi
- Rời rạc – chỉ định một giá trị và phạm vi xác suất liên quan. Phạm vi phải chứa hai cột. cột bên trái chứa các giá trị và cột bên phải chứa các xác suất liên quan đến giá trị trong hàng đó. Tổng các xác suất phải bằng 1
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à
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ối
Chú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ối
Theo 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ố