Solucionador VBA | Exemple pas a pas per utilitzar Solver a Excel VBA
Solucionador VBA d'Excel
Com es resolen problemes complicats? Si no esteu segur de com solucionar aquests problemes, res de què preocupar-vos, tenim solucions en el nostre excel. Al nostre article anterior “Excel Solver” hem après a resoldre equacions en Excel. Si no n’esteu al corrent, “SOLVER” també està disponible amb VBA. En aquest article, us explicarem com utilitzar "Solver" a VBA.
Activa Solver al full de treball
Un solucionador és una eina oculta disponible a la pestanya de dades en excel (si ja està activat).
Per utilitzar SOLVER a Excel primer hem d’habilitar aquesta opció. Seguiu els passos següents.
Pas 1: Aneu a la pestanya FITXER. A la pestanya FITXER, trieu "Opcions".
Pas 2: A la finestra Opcions d'Excel, trieu "Complements".
Pas 3: A la part inferior, seleccioneu "Complements d'Excel" i feu clic a "Vés".
Pas 4: Ara marqueu la casella "Complement de resolució" i feu clic a D'acord.
Ara heu de veure "Solucionador" a la pestanya de dades.
Activeu Solver a VBA
També a VBA, Solver és una eina externa, hem de permetre-li que l’utilitzi. Seguiu els passos següents per activar-lo.
Pas 1: Aneu a Eines >>> Referència a la finestra de Visual Basic Editor.
Pas 2: A la llista de referències, trieu "Solucionador" i feu clic a Ok per utilitzar-lo.
Ara també podem utilitzar Solver a VBA.
Funcions de resolució a VBA
Per escriure un codi VBA hem d’utilitzar tres “Funcions de resolució” a VBA i aquestes funcions són “SolverOk, SolverAdd i SolverSolve”.
SolverOk
SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)
SetCell: Aquesta serà la referència de cel·la que cal canviar, és a dir, la cel·la de benefici.
MaxMinVal: Aquest és un paràmetre opcional, a continuació hi ha números i especificadors.
- 1 = Maximitzar
- 2 = Minimitzar
- 3 = Coincideix amb un valor específic
El valor de: Aquest paràmetre s'ha de proporcionar si el fitxer MaxMinVal l'argument és 3.
Per canvi: En canviar quines cel·les cal resoldre aquesta equació.
SolverAdd
Ara vegem els paràmetres de SolverAdd
CellRef: Per definir els criteris per resoldre el problema, cal canviar la cel·la.
Relació: En això, si es compleixen els valors lògics, podem utilitzar els números següents.
- 1 és inferior a (<=)
- 2 és igual a (=)
- 3 és més gran que (> =)
- 4 ha de tenir valors finals que siguin enters.
- 5 ha de tenir valors entre 0 o 1.
- 6 ha de tenir valors finals que siguin diferents i enters.
Exemple de Solver a Excel VBA
Podeu descarregar aquesta plantilla Excel de VBA Solver aquí: plantilla Excel de VBA SolverPer obtenir un exemple, consulteu l'escenari següent.
Mitjançant aquesta taula hem d’identificar l’import “Benefici” que ha de ser un mínim de 10000. Per arribar a aquest número tenim certes condicions.
- Unitats per vendre ha de ser un valor enter.
- Preu / Unitat ha de ser d'entre 7 i 15.
Basant-nos en aquestes condicions, hem d’identificar quantes unitats venem a quin preu per obtenir el valor de benefici de 10000.
D’acord, resolem aquesta equació ara.
Pas 1: Inicieu el subprocés VBA.
Codi:
Sub Solucionari_Exemple () Final Sub
Pas 2: Primer hem d’establir la referència de la cel·la Objectiu mitjançant el fitxer SolverOk funció.
Pas 3: El primer argument d'aquesta funció és "SetCell", en aquest exemple hem de canviar el valor de la cel·la Profit, és a dir, de la cel·la B8.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8") End Sub
Pas 4: Ara hem d’establir aquest valor de cel·la a 10000, així que per a MaxMinVal utilitzeu 3 com a valor de l’argument.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3 End Sub
Pas 5: El següent argument El valor de el valor ha de ser 10000.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000 End Sub
El següent argument és ByChange, és a dir, canviant quines cel·les cal resoldre aquesta equació. En aquest cas, canviant les cel·les Unitats per vendre (B1) i Preu per unitat (B2) cal canviar.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") End Sub
Nota: els arguments restants no són necessaris aquí.
Pas 6: Un cop establerta la cel·la objectiu, ara hem de construir altres criteris. Per a aquesta funció oberta "SolverAdd".
Pas 7: Primer Cèl·lula Ref hem de canviar és el preu per unitat de cel·la, és a dir, cel·la B2.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2") End Sub
Pas 8: Aquesta cel·la ha de ser> = 7, de manera que Relació l'argument serà 3.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relació: = 3 Final Sub
Pas 9: Aquest valor de cel·la ha de ser> = 7, és a dir, Text de fórmula = 7.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relació: = 3, Text de fórmula: = 7 Final Sub
Pas 10: De la mateixa manera, la mateixa cel·la ha de ser inferior a 15, de manera que per a això relació és <= és a dir, 1 com a valor de l'argument.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relació: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relació: = 1, FormulaText: = 15 End Sub
Pas 11: La primera cel·la, és a dir, que les unitats per vendre ha de ser un valor sencer, també ha de configurar els criteris que es detallen a continuació.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relació: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relació: = 1, FormulaText: = 15 SolverAdd CellRef: = Range ("B1"), Relació: = 4, FormulaText: = "Enter" End Sub
Pas 12: Un darrer pas cal afegir la funció SolverSolve.
Codi:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Relació: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relació: = 1, FormulaText: = 15 SolverAdd CellRef: = Range ("B1"), Relació: = 4, FormulaText: = "Enter" SolverSolve End Sub
D'acord, executeu el codi prement la tecla F5 per obtenir el resultat.
Quan executeu el codi, veureu la finestra següent.
Premeu Ok i obtindreu el resultat en un full Excel.
Per tant, per obtenir un benefici de 10000, hem de vendre 5.000 unitats a 7 per preu, on el preu de cost és de 5.
Coses que cal recordar
- Per treballar amb Solver a Excel i VBA, primer, activeu-lo per al full de càlcul i, a continuació, activeu-lo per a la referència VBA.
- Una vegada que estigui habilitat als dos fulls de treball i VBA, només podrem accedir a totes les funcions de Solver.