photosdematures.com

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 Solver

Per 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.