VBA Solver | Ví dụ từng bước để sử dụng Solver trong Excel VBA
Bộ giải VBA trong Excel
Làm thế nào để bạn giải quyết các vấn đề phức tạp? Nếu bạn không chắc chắn làm thế nào để giải quyết những vấn đề này thì không có gì phải lo lắng, chúng tôi có trình giải quyết trong excel của chúng tôi. Trong bài viết trước đây của chúng tôi “Excel Solver”, chúng tôi đã học cách giải các phương trình trong excel. Nếu bạn không biết "SOLVER" cũng có sẵn với VBA. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách sử dụng “Solver” trong VBA.
Bật trình giải quyết trong trang tính
Bộ giải là một công cụ ẩn có sẵn trong tab dữ liệu trong excel (nếu đã được bật).
Để sử dụng SOLVER trong excel trước tiên chúng ta cần bật tùy chọn này. Làm theo các bước dưới đây.
Bước 1: Đi tới tab TỆP. Trong tab TẬP TIN, chọn “Tùy chọn”.
Bước 2: Trong cửa sổ Tùy chọn Excel, chọn “Phần bổ trợ”.
Bước 3: Ở dưới cùng, chọn “Phần bổ trợ Excel” và nhấp vào “Bắt đầu”.
Bước 4: Bây giờ đánh dấu vào ô “Solver Add-in” và nhấp vào Ok.
Bây giờ bạn phải thấy “Bộ giải” trong tab dữ liệu.
Bật trình giải quyết trong VBA
Trong VBA cũng vậy, Solver là một công cụ bên ngoài, chúng ta cần kích hoạt nó để sử dụng. Làm theo các bước dưới đây để kích hoạt nó.
Bước 1: Vào Tools >>> Reference trong Visual Basic Editor Window.
Bước 2: Từ danh sách tài liệu tham khảo, chọn “Solver” và nhấp vào Ok để sử dụng.
Bây giờ chúng ta cũng có thể sử dụng Solver trong VBA.
Các chức năng của bộ giải trong VBA
Để viết mã VBA, chúng ta cần sử dụng ba “Hàm Solver” trong VBA và các hàm đó là “SolverOk, SolverAdd và SolverSolve”.
SolverOk
SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)
SetCell: Đây sẽ là tham chiếu ô cần thay đổi tức là ô Lợi nhuận.
MaxMinVal: Đây là một tham số tùy chọn, bên dưới là các số và thông số.
- 1 = Tối đa hóa
- 2 = Giảm thiểu
- 3 = Khớp một giá trị cụ thể
ValueOf: Tham số này cần cung cấp nếu đối số MaxMinVal là 3.
ByChange: Bằng cách thay đổi ô nào mà phương trình này cần được giải.
SolverAdd
Bây giờ chúng ta hãy xem các thông số của SolverAdd
CellRef: Để thiết lập các tiêu chí để giải quyết vấn đề, ô cần được thay đổi.
Mối quan hệ: Trong trường hợp này, nếu các giá trị logic được thỏa mãn thì chúng ta có thể sử dụng các số dưới đây.
- 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.
Ví dụ về Solver trong Excel VBA
Bạn có thể tải xuống Mẫu VBA Solver Excel này tại đây - Mẫu VBA Solver ExcelĐể có một ví dụ, hãy xem kịch bản dưới đây.
Sử dụng bảng này, chúng tôi cần xác định số tiền "Lợi nhuận" cần tối thiểu là 10000. Để đạt được con số này, chúng tôi có một số điều kiện nhất định.
- Đơn vị để Bán phải là một giá trị số nguyên.
- Giá / Đơn vị phải từ 7 đến 15.
Dựa vào các điều kiện này ta cần xác định cần bán bao nhiêu đơn vị với giá nào để thu được giá trị lợi nhuận là 10000.
Ok, chúng ta hãy giải phương trình này ngay bây giờ.
Bước 1: Khởi động quy trình con VBA.
Mã:
Sub Solver_Example () End Sub
Bước 2: Đầu tiên chúng ta cần thiết lập tham chiếu ô Mục tiêu bằng cách sử dụng hàm SolverOk .
Bước 3: Đối số đầu tiên của hàm này là “SetCell”, trong ví dụ này chúng ta cần thay đổi giá trị của ô Lợi nhuận tức là ô B8.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8") End Sub
Bước 4: Bây giờ chúng ta cần đặt giá trị ô này thành 10000, vì vậy đối với MaxMinVal, hãy sử dụng 3 làm giá trị đối số.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3 End Sub
Bước 5: Giá trị ValueOf đối số tiếp theo phải là 10000.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000 End Sub
Đối số tiếp theo là ByChange, tức là bằng cách thay đổi ô nào mà phương trình này cần được giải. Trong trường hợp này bằng cách thay đổi Đơn vị thành Bán (B1) và ô Giá mỗi Đơn vị (B2) cần được thay đổi.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") End Sub
Lưu ý: các đối số còn lại không bắt buộc ở đây.
Bước 6: Khi ô mục tiêu được thiết lập, bây giờ chúng ta cần xây dựng các tiêu chí khác. Đối với chức năng “SolverAdd” đang mở này.
Bước 7: Ô Ref đầu tiên chúng ta cần đổi là ô Price Per Unit tức là ô B2.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2") End Sub
Bước 8: Ô này cần phải> = 7, vì vậy đối số Relation sẽ là 3.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relation: = 3 End Sub
Bước 9: Giá trị ô này phải> = 7 tức là Văn bản công thức = 7 .
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relation: = 3, FormulaText: = 7 End Sub
Bước 10: Tương tự, cùng một ô cần nhỏ hơn 15, vì vậy đối với quan hệ này là <= tức là 1 làm giá trị đối số.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relation: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relation: = 1, FormulaText: = 15 End Sub
Bước 11: Ô đầu tiên tức là Đơn vị cần bán phải là giá trị Số nguyên vì vậy cũng thiết lập các tiêu chí như bên dưới.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relation: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relation: = 1, FormulaText: = 15 SolverAdd CellRef: = Range ("B1"), Relation: = 4, FormulaText: = "Integer" End Sub
Bước 12: Một bước cuối cùng chúng ta cần thêm chức năng SolverSolve.
Mã:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relation: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relation: = 1, FormulaText: = 15 SolverAdd CellRef: = Range ("B1"), Relation: = 4, FormulaText: = "Integer" SolverSolve End Phụ
Ok, chạy mã bằng cách nhấn phím F5 để nhận kết quả.
Khi bạn chạy mã, bạn sẽ thấy cửa sổ sau.
Nhấn Ok và bạn sẽ nhận được kết quả trong một trang tính excel.
Vì vậy, để thu được lợi nhuận 10000, chúng ta cần bán 5000 đơn vị với giá 7 chiếc trong đó giá vốn là 5 chiếc.
Những điều cần ghi nhớ
- Để làm việc với Solver trong excel & VBA, trước tiên, hãy bật nó cho trang tính, sau đó bật cho tham chiếu VBA.
- Khi nó được bật trên cả bảng tính và VBA thì chỉ chúng ta mới có thể truy cập tất cả các chức năng của Bộ giải.
