Hợp lý hóa mô hình Bennu với RandArray - Mẹo Excel

Mục lục

Xem video

Tuần trước tại Ignite, nhóm Excel đã giới thiệu mảng động. Hôm nay, hãy xem xét kỹ hơn chức năng RANDARRAY.

Gần đây, trong mục nhập vào trò chơi Excel Hash, tôi đã tạo một mô hình để tính toán khả năng Trái đất sẽ có một điểm thu hút khách du lịch mới, miệng núi lửa Bennu vào năm 2196. Mô hình đó đã thực hiện ba mươi triệu phép tính và yêu cầu 200.001 công thức cùng với 100- bảng dữ liệu hàng. Dưới đây là các công thức được sử dụng trong 200.001 ô:

Mô hình này được tính trong 10-12 giây

Để đơn giản hóa mô hình, bạn sẽ sử dụng hàm RANDARRAY (100000) thay vì hàm RAND. Điều này sẽ làm cho công thức tính toán 100.000 lần.

Bạn bắt đầu bằng cách thay thế RAND () bằng RANDARRAY (100000) để tạo ra 100.000 câu trả lời:

RANDARRAY(100000)
Gửi RANDARRAY đến NORM.INV để tính toán 100.000 vị trí

NORM.INV(RANDARRAY(100000),$H$4,$H$5)
Gửi NORM.INV vào VLOOKUP để xác định xem Bennu có tác động đến Trái đất hay không:

VLOOKUP(NORM.INV(RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE)
Và cuối cùng tổng hợp 100K kết quả

=SUM(VLOOKUP(NORM.INV(_xlfn.RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE))

Mô hình cuối cùng để chạy 100.000 thử nghiệm được chứa trong một công thức duy nhất:

200.000 ô được thay thế bằng 1 công thức

Kích thước tệp thu nhỏ đáng kể: Từ 3.270.979 byte xuống còn 37.723 byte. Thời gian Recalc được cắt giảm một nửa. Xem thời gian Recalc trong video dưới đây.