Penyelesai VBA - Langkah demi Langkah Contoh untuk menggunakan Solver dalam Excel VBA

Isi kandungan

Penyelesai Excel VBA

Bagaimana anda menyelesaikan masalah yang rumit? Sekiranya anda tidak pasti bagaimana mengatasi masalah ini, maka tidak perlu risau kami mempunyai penyelesaian dalam prestasi kami. Dalam artikel sebelumnya "Excel Solver" kami telah belajar bagaimana menyelesaikan persamaan dalam excel. Sekiranya anda tidak sedar, "SOLVER" juga tersedia dengan VBA. Dalam artikel ini, kami akan membimbing anda bagaimana menggunakan "Solver" di VBA.

Dayakan Penyelesai dalam Lembaran Kerja

Penyelesai adalah alat tersembunyi yang tersedia di bawah tab data di excel (jika sudah diaktifkan).

Untuk menggunakan SOLVER dalam excel terlebih dahulu, kita perlu mengaktifkan pilihan ini. Ikuti langkah di bawah.

Langkah 1: Pergi ke tab FILE. Di bawah tab FILE pilih "Pilihan".

Langkah 2: Di tetingkap Pilihan Excel pilih "Tambah".

Langkah 3: Di bahagian bawah pilih "Excel Add-Ins" dan klik "Go".

Langkah 4: Sekarang tandakan kotak "Solver Add-in" dan klik, Ok.

Sekarang anda mesti melihat "Penyelesai" di bawah tab data.

Dayakan Penyelesai dalam VBA

Di VBA juga, Solver adalah alat luaran; kita perlu membolehkannya menggunakannya. Ikuti langkah di bawah untuk mengaktifkannya.

Langkah 1: Pergi ke Alat >>> Rujukan di Tetingkap Visual Basic Editor.

Langkah 2: Dari senarai rujukan, pilih "Penyelesai" dan klik Ok untuk menggunakannya.

Sekarang kita boleh menggunakan Solver di VBA juga.

Fungsi Penyelesai dalam VBA

Untuk menulis kod VBA kita perlu menggunakan tiga "Fungsi Solver" di VBA dan fungsi-fungsi tersebut adalah "SolverOk, SolverAdd, dan SolverSolve".

SolverOk

SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)

SetCell: Ini akan menjadi rujukan sel yang perlu diubah iaitu, Profit cell.

MaxMinVal: Ini adalah parameter pilihan, di bawah adalah nombor dan penentu.

  • 1 = Maksimumkan
  • 2 = Kurangkan
  • 3 = Padankan nilai tertentu

ValueOf: Parameter ini perlu dibekalkan sekiranya argumen MaxMinVal adalah 3.

ByChange: Dengan menukar sel mana, persamaan ini perlu diselesaikan.

PenyelesaianAdd

Sekarang mari kita lihat parameter SolverAdd

CellRef: Untuk menetapkan kriteria untuk menyelesaikan masalah, sel apa yang perlu diubah.

Perkaitan: Dalam hal ini, jika nilai logik dipenuhi maka kita boleh menggunakan nombor di bawah.

  • 1 kurang daripada (<=)
  • 2 sama dengan (=)
  • 3 lebih besar daripada (> =)
  • 4 mesti mempunyai nilai akhir yang berupa bilangan bulat.
  • 5 mesti mempunyai nilai antara 0 atau 1.
  • 6 mesti mempunyai nilai akhir yang semuanya berbeza dan integer.

Contoh Penyelesai dalam Excel VBA

Sebagai contoh lihat senario di bawah.

Dengan menggunakan jadual ini, kita perlu mengenal pasti jumlah "Keuntungan", yang minimum 10000. Untuk mencapai nombor ini, kita mempunyai syarat tertentu.

  • Unit untuk Dijual mestilah nilai integer.
  • Harga / Unit hendaklah antara 7 dan 15.

Berdasarkan syarat-syarat ini, kita perlu mengenal pasti berapa unit yang hendak dijual dengan harga berapa untuk mendapatkan nilai keuntungan 10000.

Ok, mari selesaikan persamaan ini sekarang.

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, jalankan kod dengan menekan kekunci F5 untuk mendapatkan hasilnya.

Apabila anda menjalankan kod, anda akan melihat tetingkap berikut.

Tekan Ok dan anda akan mendapat hasilnya dalam lembaran excel.

Oleh itu, untuk mendapatkan keuntungan 10000, kita perlu menjual 5000 unit pada harga 7 setiap harga di mana harga kos adalah 5.

Perkara yang Perlu Diingat

  • Untuk bekerjasama dengan Solver di excel & VBA, mula-mula, aktifkannya untuk lembaran kerja, kemudian aktifkan untuk rujukan VBA.
  • Setelah diaktifkan pada kedua-dua lembaran kerja dan VBA maka hanya kita yang dapat mengakses semua fungsi Penyelesai.

Artikel menarik...