=randbetween() sau đó đóng băng kết quả, Excel 07

CHÀO,

Tôi đang thực hiện một mô phỏng Monte Carlo cho một lớp học và tôi phải sử dụng công thức =randbetween(). Tôi cần đóng băng kết quả sau khi các số được tạo ra, để tôi có thể viết kết luận và đảm bảo chúng có ý nghĩa khi Giáo sư chấm điểm và tôi trình bày trước lớp. Tôi không thể sử dụng chức năng sao chép/dán đặc biệt để chỉ sao chép và dán lại các giá trị, vì Giáo sư muốn xem cả công thức. Tôi đã thử thiết lập tùy chọn tính toán thành thủ công, nhưng mỗi khi tôi lưu hoặc đóng và mở lại tệp, nó vẫn tính toán lại. Có gợi ý nào về cách đóng băng kết quả nhưng vẫn giữ lại công thức không? Giáo sư nói rằng điều đó có thể thực hiện được, nhưng không thể chỉ cho tôi cách làm. Rất mong nhận được sự giúp đỡ!

Cảm ơn, Courtney

Trả lời:

Tôi tò mò về bài đăng của bạn. Tôi nghĩ rằng việc "Sao chép và dán đặc biệt dưới dạng giá trị/định dạng" vào một trang tính mới là đủ để đóng băng kết quả. Trang tính hiện tại (trang tính riêng biệt) với tất cả các công thức vẫn có sẵn để kiểm tra.

Trả lời:
Tôi đã viết:

Tôi định đề xuất sử dụng các hàm UDF VBA không thay đổi. Nhưng có vẻ như các công thức gọi chúng sẽ được tính toán lại khi tệp được lưu nếu chế độ tính toán tự động được chọn.

À ha! Tôi nhầm rồi! Tôi đã thực hiện thay đổi trong một mô-đun VBA khác ngay trước khi lưu, và dường như điều đó khiến tất cả các công thức có hàm do người dùng định nghĩa (UDF) phải được tính toán lại khi bảng tính được thay đổi hoặc lưu sau đó. (Điều này hợp lý.)

Vì vậy, bạn có thể cân nhắc sử dụng các hàm UDF VBA không thay đổi như myRAND và myRANDBETWEEN bên dưới.

Hãy gọi hàm myRAND và myRANDBETWEEN giống như cách bạn gọi hàm RAND và RANDBETWEEN trong Excel, cụ thể là RAND() và RANDBETWEEN(low,high). Do đó, bạn có thể sử dụng chức năng Tìm và Thay thế để thay đổi tất cả các công thức hiện có.

Nhóm hàm UDF đầu tiên bên dưới sử dụng VBA Evaluate để tận dụng các hàm RAND và RANDBETWEEN của Excel. Tôi làm vậy để tận dụng bất kỳ cải tiến nào trong việc tạo số ngẫu nhiên, đặc biệt là trong XL2010.

Tuy nhiên, hàm VBA Evaluate tương đối chậm. Nếu hiệu năng trở thành vấn đề, hãy cân nhắc sử dụng nhóm hàm UDF thứ hai bên dưới, sử dụng hàm VBA Rnd thay thế.

Lưu ý: Như đã đề cập ở trên, dường như các thay đổi đối với bất kỳ mô-đun VBA nào (trong dự án VBA?) đều khiến các biểu thức gọi hàm myRAND và myRANDBETWEEN được tính toán lại. Tôi nghi ngờ cách duy nhất để có quyền kiểm soát tuyệt đối (thủ công) thời điểm các biểu thức ngẫu nhiên được tính toán lại là sử dụng chức năng sao chép và dán giá trị đặc biệt như tôi đã đề xuất trước đó, hoặc sử dụng macro để tạo ra một tập hợp các hằng số ngẫu nhiên hoặc chính giá trị của các biểu thức ngẫu nhiên đó dưới dạng hằng số (tức là triển khai các "công thức" trong macro). Có lẽ cách thứ hai cũng là cách nhanh nhất.

-----

Hàm UDF sử dụng VBA Evaluate....

Hàm myRAND() là kiểu Double
myRAND = Evaluate("rand()")
Kết thúc hàm

Hàm myRANDBETWEEN(lo As Double, hi As Double) As Double
myRANDBETWEEN = Đánh giá("randbetween(" & lo & "," & hi & ")")
myRANDBETWEEN = Int(lo + (hi - lo + 1) * Đánh giá("Rand()"))
Kết thúc hàm

CHỈNH SỬA.... Hàm myRANDBETWEEN sử dụng Evaluate("rand()") nhanh hơn 85% so với sử dụng Evaluate("randbetween(...)") trên máy tính của tôi với Excel 2003. Cách triển khai này cũng cung cấp một số tính linh hoạt mà hàm RANDBETWEEN của Excel 2003 không có. Kết quả có thể khác nhau với Excel 2007 và Excel 2010, nơi hàm RANDBETWEEN được tích hợp sẵn trong Excel.

Hàm UDF sử dụng VBA Rnd....

Đầu tiên là tư nhân. Miễn là

Hàm myRAND() là kiểu Double
Nếu first = 0 thì ngẫu nhiên hóa: first = 1
myRAND = Rnd
Kết thúc hàm

Hàm myRANDBETWEEN(lo As Double, hi As Double) As Double
Nếu first = 0 thì ngẫu nhiên hóa: first = 1
myRANDBETWEEN = Int(lo + (hi - lo + 1) * Rnd)
Kết thúc hàm