Hướng dẫn này sẽ chỉ cho bạn cách sử dụng phần bổ trợ Solver trong VBA.
Solver là một phần bổ trợ được cung cấp với Excel và được sử dụng để thực hiện phân tích 'điều gì xảy ra' bằng cách cung cấp các câu trả lời thay thế cho một công thức trong một ô dựa trên các giá trị mà bạn có thể chuyển đến công thức từ các ô khác trong sổ làm việc của mình.
Bật phần bổ trợ Solver trong Excel
Chọn Tập tin trên ruy-băng Excel và sau đó đi xuống Tùy chọn.
Lựa chọn Thêm vào và nhấp vào Đi bên cạnh Bổ trợ Excel.
Hãy đảm bảo rằng Phần bổ trợ Solver tùy chọn được chọn.
Ngoài ra, hãy nhấp vào Phần bổ trợ Excel trên Nhà phát triển ruy-băng để nhận hộp thoại Phần bổ trợ.
Bật Trình bổ sung Bộ giải trong VBA
Khi bạn đã bật Phần bổ trợ Solver trong Excel, sau đó bạn cần thêm một tham chiếu đến nó trong Dự án VBA của bạn để sử dụng nó trong VBA.
Đảm bảo rằng bạn được nhấp vào Dự án VBA nơi bạn muốn sử dụng Bộ giải. Bấm vào Menu công cụ và sau đó Người giới thiệu.
Tham chiếu đến Phần bổ trợ Solver sẽ được thêm vào dự án của bạn.
Bây giờ bạn có thể sử dụng Phần bổ trợ Bộ giải trong mã VBA!
Sử dụng các chức năng của bộ giải trong VBA
Chúng ta cần sử dụng 3 hàm Solver VBA để sử dụng Solver trong VBA. đó là SolverOK, SolverAdd, và SolverSolve.
SolverOK
- SetCell - không bắt buộc - ô này cần tham chiếu đến ô cần thay đổi - ô này cần chứa công thức. Điều này tương ứng vớiĐặt ô mục tiêu hộp trongTham số bộ giải hộp thoại.
- MaxMinVal - không bắt buộc - Bạn có thể đặt giá trị này thành 1 (Tối đa hóa), 2 (Thu nhỏ) hoặc 3. Điều này tương ứng với Max, Min, vàGiá trị các tùy chọn trongTham số bộ giải hộp thoại.
- Giá trị của - không bắt buộc -Nếu MaxMinValue được đặt thành 3, thì bạn cần cung cấp đối số này.
- ByChange - không bắt buộc -Điều này cho người giải biết những ô nào nó có thể thay đổi để đạt được giá trị cần thiết. Điều này tương ứng vớiBằng cách thay đổi các ô biến hộp trongTham số bộ giải hộp thoại.
- Động cơ - không bắt buộc - điều này chỉ ra phương pháp giải quyết cần được sử dụng để đi đến một giải pháp. 1 cho phương pháp Simplex LP, 2 cho phương pháp GRG Phi tuyến, hoặc 3 cho phương pháp Tiến hóa. Điều này tương ứng vớiChọn một phương pháp giải quyết danh sách thả xuống trongTham số bộ giải hộp thoại
- EngineDesc - không bắt buộc -đây là một cách thay thế để chọn phương pháp giải - ở đây bạn sẽ nhập các chuỗi “Simplex LP”, “GRG Nonlinear” hoặc “Evolution”. Điều này cũng tương ứng vớiChọn một phương pháp giải quyết danh sách thả xuống trongTham số bộ giải hộp thoại
SolverAdd
- CellRef - yêu cầu - đây là một tham chiếu đến một ô hoặc một dải ô sẽ được thay đổi để giải quyết vấn đề.
- Quan hệ - yêu cầu - đây là một số nguyên phải từ 1 đến 6 và chỉ định quan hệ logic được phép.
- 1 nhỏ hơn (<=)
- 2 bằng (=)
- 3 lớn hơn (> =)
- 4 là phải có giá trị cuối cùng là số nguyên.
- 5 là phải có giá trị từ 0 hoặc 1.
- 6 là phải có giá trị cuối cùng là tất cả các số nguyên và khác nhau.
- FormulaText - không bắt buộc - Mặt phải của ràng buộc.
Tạo một ví dụ về bộ giải
Hãy xem xét bảng tính sau.
Trong trang tính ở trên, chúng ta cần hòa vốn trong Tháng số một bằng cách đặt ô B14 thành 0 bằng cách sửa đổi tiêu chí trong ô F1 thành F6.
| 123 | Sub TestSolverSolverOk SetCell: = "$ B $ 14", MaxMinVal: = 3, ValueOf: = 0, ByChange: = "$ F $ 2: $ F $ 6", Engine: = 1, EngineDesc: = "GRG Nonlinear"Kết thúc Sub |
Khi bạn đã thiết lập các tham số SolverOK, bạn cần thêm một số hạn chế về tiêu chí.
| 1234567 | Sub TestSolverSolverOk SetCell: = "$ B $ 14", MaxMinVal: = 3, ValueOf: = 0, ByChange: = "$ F $ 2: $ F $ 6", Engine: = 1, EngineDesc: = "GRG Nonlinear"'thêm tiêu chí - F3 không được nhỏ hơn 8SolverAdd CellRef: = "$ F $ 3", Relation: = 3, FormulaText: = "8"'thêm tiêu chí - F3 không được nhỏ hơn 5000SolverAdd CellRef: = "$ F $ 5", Relation: = 3, FormulaText: = "5000"Kết thúc Sub |
Khi bạn đã đặt SolverOK và SolverAdd (nếu cần), bạn có thể Giải quyết vấn đề.
| 1234567 | Sub TestSolverSolverOk SetCell: = "$ B $ 14", MaxMinVal: = 3, ValueOf: = 0, ByChange: = "$ F $ 2: $ F $ 6", Engine: = 1, EngineDesc: = "GRG Nonlinear"'thêm tiêu chí - F3 không được nhỏ hơn 8 SolverAdd CellRef: = "$ F $ 3", Relation: = 3, FormulaText: = "8"' thêm tiêu chí - F3 không được nhỏ hơn 5000SolverAdd CellRef: = "$ F $ 5", Relation: = 3, FormulaText: = "5000"'tìm một giải pháp bằng cách giải quyết vấn đềSolverSolveKết thúc Sub |
Sau khi bạn chạy mã, cửa sổ sau sẽ hiển thị trên màn hình của bạn. Chọn tùy chọn bạn yêu cầu (tức là Giữ giải pháp Bộ giải hoặc Khôi phục Giá trị Ban đầu), và bấm OK.
