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 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 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: Chuyển đến tab TẬP TIN. 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 “Excel Add-Ins” và nhấp vào “Go”.

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 nó. 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 được 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à chỉ định.

  • 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, 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 tiêu chí giải quyết vấn đề, ô là gì 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

Để 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", số tiền này phải 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ờ.

Step 1: Start the VBA subprocedure.

Code:

Sub Solver_Example() End Sub

Step 2: First we need to set the Objective cell reference by using the SolverOk function.

Step 3: First argument of this function is “SetCell”, in this example we need to change the value of Profit cell i.e. B8 cell.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8") End Sub

Step 4: Now we need to set this cell value to 10000, so for MaxMinVal use 3 as the argument value.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3 End Sub

Step 5: The next argument ValueOf value should be 10000.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000 End Sub

The next argument is ByChange i.e. by changing which cells this equation needs to be solved. In this case by changing Units to Sell (B1) and Price Per Unit (B2) cell needs to be changed.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") End Sub

Note: remaining arguments are not required here.

Step 6: Once the objective cell is set, now we need to construct other criteria’s. For this open “SolverAdd” function.

Step 7: First Cell Ref we need to change is Price Per Unit cell i.e. B2 cell.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2") End Sub

Step 8: This cell needs to be>= 7, so the Relation argument will be 3.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3 End Sub

Step 9: This cell value should be>=7 i.e. Formula Text = 7.

Code:

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

Step 10: Similarly the same cell needs to be less than 15, so for this relation is <= i.e. 1 as the argument value.

Code:

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

Step 11: First cell i.e. Units to Sell must be an Integer value for this also set up the criteria as below.

Code:

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

Step 12: In one final step, we need to add the SolverSolve function.

Code:

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 Sub

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.

thú vị bài viết...