Mô phỏng là một kỹ thuật phân tích mạnh mẽ giúp bạn dự đoán kết quả, hiểu được sự không chắc chắn và đưa ra quyết định dựa trên dữ liệu. Excel là một công cụ tuyệt vời để mô phỏng các sự kiện ngẫu nhiên, cho phép bạn khám phá xác suất và lập mô hình kết quả cho các tình huống khác nhau.
Trong hướng dẫn này, chúng tôi sẽ trình bày cách sử dụng Excel để mô phỏng các sự kiện ngẫu nhiên trong thế giới thực, bao gồm các tập dữ liệu thực tế về việc tung đồng xu, tung xúc xắc và dự báo thời tiết.
1. Mô phỏng việc lật đồng xu đơn giản
Mô phỏng việc tung đồng xu 100 lần.
Các bước:
- Mở sổ làm việc Excel mới.
- Trong ô A1, nhập Số dùng thử.
- Trong ô B1, nhập Kết quả lật xu.
Điền số dùng thử:
- Trong ô A2, nhập 1.
- Trong ô A3, nhập 2.
- Chọn ô A2:A3 và kéo núm điều khiển điền xuống A101 để đánh số các lần thử từ 1 đến 100.
- Nếu bạn đang sử dụng Excel 2021 trở lên. Bạn có thể sử dụng hàm SEQUENCE.
- Chọn ô A2 và chèn công thức sau.
Công thức này tự động tạo ra 100 số đường.
Mô phỏng việc lật đồng xu:
- Trong ô B2, nhập công thức sau:
=IF(RAND()<0.5, "Heads", "Tails")
Công thức này trả về Heads nếu giá trị ngẫu nhiên nhỏ hơn 0,5, còn lại trả về TAils.
- Kéo núm điều khiển điền từ B2 đến B101.
Phân tích kết quả:
Đếm mặt ngửa/sấp từ kết quả mô phỏng.
Đếm đầu:
- Chọn ô E2 và chèn công thức sau.
=COUNTIF(B2:B101, "Heads")
Đếm đuôi:
- Chọn ô E3 và chèn công thức sau.
=COUNTIF(B2:B101, "Tails")
Lưu ý: Công thức tạo ra kết quả mới mỗi khi sổ làm việc tính toán lại.
2. Mô phỏng cuộn xúc xắc
Mô phỏng việc tung xúc xắc sáu mặt 100 lần.
Các bước:
- Trong ô A1, nhập Số cuộn.
- Trong ô B1, nhập Kết quả Xúc xắc.
Điền số cuộn:
- Chọn ô A2 và chèn công thức sau.
Mô phỏng việc đổ xúc xắc:
- Chọn ô B2 và chèn công thức sau
Công thức này tạo số ngẫu nhiên từ 1 đến 6.
- Kéo công thức từ B2 xuống B101.
Phân tích kết quả:
Tính tần suất cho mỗi số xúc xắc (1 đến 6):
- Số 1:
- Số 2:
- Số 3:
- Số 4:
- Số 5:
- Số 6:
Lưu ý: Công thức tạo ra kết quả mới mỗi khi sổ làm việc tính toán lại.
3. Mô phỏng sự đến của khách hàng (Kịch bản kinh doanh thực tế)
Bạn có thể mô phỏng lượng khách hàng đến cửa hàng hàng giờ, giả sử tỷ lệ khách hàng đến cửa hàng trung bình là 20 khách hàng mỗi giờ.
Các bước
- Trong ô A1, nhập Giờ.
- Trong ô B1, nhập Khách hàng đang đến.
Thời gian lấp đầy:
- Trong ô A2, chèn 9:00 sáng.
- Kéo xuống từng giờ cho đến 4 giờ chiều.
Mô phỏng lượt đến:
- Trong ô B2, chèn công thức sau.
=ROUND(NORM.INV(RAND(), 20, 4), 0)
Công thức này mô phỏng một số ngẫu nhiên thực tế (ví dụ:lượng khách đến) tập trung vào khoảng 20 với độ lệch chuẩn là 4. Kết quả được làm tròn đến số nguyên gần nhất.
- Kéo công thức từ B2 xuống B9.
Tính số khách hàng trung bình:
- Chọn ô D2 và chèn công thức sau.
Điều này tạo ra số lượng khách đến ngẫu nhiên thực tế dựa trên mức trung bình nhất định.
Lưu ý: Công thức tạo ra kết quả mới mỗi khi sổ làm việc tính toán lại.
4. Mô phỏng điều kiện thời tiết ngẫu nhiên
Bạn có thể mô phỏng thời tiết hàng ngày trong một tháng dựa trên xác suất lịch sử.
Đặt xác suất:
- Nắng:60%
- Có mây:25%
- Mưa:15%
Các bước:
- Trong ô A1, nhập Ngày.
- Trong ô B1, nhập Điều kiện thời tiết.
Số ngày lấp đầy:
- Chọn ô A2 và chèn công thức sau.
Mô phỏng thời tiết:
- Trong ô B2, chèn công thức sau.
=IF(RAND()<=0.6,"Sunny",IF(RAND()<=0.25/(0.25+0.15),"Cloudy","Rainy"))
Công thức này mô phỏng thời tiết ngẫu nhiên bằng cách gán “Nắng”, “Mây” hoặc “Mưa” dựa trên xác suất được xác định trước, nhưng nó sử dụng hai lệnh gọi RAND(), có thể làm sai lệch một chút phân bố dự kiến. Cách tiếp cận tốt hơn là sử dụng một RAND() với phạm vi tích lũy.
- Kéo công thức xuống B32.
Phân tích kết quả:
Đếm số lần xuất hiện thời tiết:
- Nắng:
- Có mây:
=COUNTIF(B2:B32,"Cloudy")
- Mưa:
Phân tích tần suất :
Tính tần suất bằng cách chia số lượng cho tổng số lần thử (31):
- Nắng:
- Có mây:
- Mưa:
Lưu ý: Công thức tạo ra kết quả mới mỗi khi sổ làm việc tính toán lại.
5. Kịch bản nâng cao:Mô phỏng Monte Carlo để dự báo doanh số
Ước tính doanh thu hàng tháng có tính đến sự không chắc chắn. ở đâu:
- Doanh số dự kiến:2000 chiếc
- Độ lệch chuẩn:300 đơn vị
Các bước:
- Trong ô A1, nhập Mô phỏng #.
- Trong ô B1, nhập Bán hàng mô phỏng.
Điền số mô phỏng:
- Chọn ô A2 và chèn công thức sau.
Mô phỏng bán hàng:
- Trong ô B2, chèn công thức sau.
=NORM.INV(RAND(), 2000, 300)
Công thức này tạo ra một giá trị ngẫu nhiên từ phân phối chuẩn với giá trị trung bình là 2000 và độ lệch chuẩn là 300, rất hữu ích cho việc mô phỏng doanh số bán hàng hàng tháng.
- Kéo công thức này xuống B1001.
Phân tích kết quả:
- Tính doanh số mô phỏng trung bình.
- Tính xác suất vượt mục tiêu bán hàng (ví dụ:2200 đơn vị):
=COUNTIF(B2:B1001,">2200")/1000
Lưu ý: Công thức tạo ra kết quả mới mỗi khi sổ làm việc tính toán lại.
Mẹo bổ sung:
- Khóa kết quả: Nếu bạn muốn giữ kết quả ngẫu nhiên ở trạng thái tĩnh, hãy chọn phạm vi, sao chép và dán làm giá trị.
- Biểu đồ và hình ảnh hóa: Sử dụng biểu đồ của Excel để dễ dàng trực quan hóa kết quả mô phỏng.
- Sử dụng công cụ Phân tích dữ liệu (Analysis ToolPak): Để có bộ dữ liệu lớn hơn và phân tích thống kê chi tiết.
- Ngăn chặn: Cố định các hàng trên cùng trong khi tạo các sự kiện ngẫu nhiên lớn, giúp cuộn và hiểu sự kiện một cách dễ dàng.
- Nhấn F9 để làm mới kết quả ngẫu nhiên theo cách thủ công.
Kết luận
Bằng cách làm theo các bước chi tiết này cùng với bộ dữ liệu thực tế, bạn có thể tự tin mô phỏng và phân tích các sự kiện ngẫu nhiên bằng Excel. Các hàm số ngẫu nhiên mạnh mẽ nhưng dễ tiếp cận của Excel như RAND(), RANDBETWEEN() và các hàm thống kê (NORM.INV, POISSON.INV) cho phép bạn mô phỏng các sự kiện trong thế giới thực một cách hiệu quả. Cho dù phục vụ mục đích giáo dục, dự báo hay quản lý rủi ro, Excel đều cung cấp các công cụ thiết thực để trực quan hóa sự không chắc chắn và đưa ra quyết định sáng suốt.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!