contingut
"Cerca una solució" és un complement d'Excel, mitjançant el qual és possible triar la millor solució als problemes en funció de les restriccions especificades. La funció permet programar empleats, distribuir costos o inversions. Saber com funciona aquesta funció us estalviarà temps i esforç.
Què és la recerca de solucions
En combinació amb altres opcions a Excel, hi ha una funció menys popular, però extremadament necessària, "Cercar una solució". Tot i que trobar-lo no és fàcil, conèixer-lo i utilitzar-lo ajuda a resoldre molts problemes. L'opció processa les dades i dóna la solució òptima entre les permeses. L'article descriu com funciona directament la cerca d'una solució.
Com activar la funció "Cerca una solució".
Malgrat l'eficàcia, l'opció en qüestió no es troba en un lloc destacat a la barra d'eines o al menú contextual. La majoria dels usuaris que treballen en Excel no són conscients de la seva presència. Per defecte, aquesta funció està desactivada, per mostrar-la, realitzeu les accions següents:
- Obriu el "Fitxer" fent clic al nom adequat.
- Feu clic a la secció "Configuració".
- A continuació, seleccioneu la subsecció "Complements". Tots els complements del programa es mostraran aquí, la inscripció "Gestió" apareixerà a continuació. A la part dreta hi haurà un menú emergent on hauríeu de seleccionar "Complements d'Excel". A continuació, feu clic a "Vés".
1 - Es mostrarà una finestra addicional "Complements" al monitor. Marqueu la casella al costat de la funció desitjada i feu clic a D'acord.
- La funció desitjada apareixerà a la cinta a la dreta de la secció "Dades".
Sobre els models
Aquesta informació serà molt útil per a aquells que acaben de familiaritzar-se amb el concepte de "model d'optimització". Abans d'utilitzar la "Cerca d'una solució", es recomana estudiar els materials sobre els mètodes de construcció de models:
- l'opció considerada permetrà identificar el millor mètode per a la destinació de fons per a inversions, càrrega del local, subministrament de béns o altres actuacions on calgui trobar la millor solució.
- El “mètode òptim” en una situació així significaria: augmentar els ingressos, reduir costos, millorar la qualitat, etc.
Tasques típiques d'optimització:
- Determinació d'un pla de producció, durant el qual el benefici de la venda de mercaderies alliberades serà màxim.
- Determinació de mapes de transport, durant els quals es minimitzen els costos de transport.
- Cercar la distribució de diverses màquines per a diferents tipus de treball, de manera que es redueixin els costos de producció.
- Determinació del temps més curt per a la realització de l'obra.
Important Per formalitzar la tasca, cal crear un model que reflecteixi els principals paràmetres de l'àrea temàtica. A Excel, un model és un conjunt de fórmules que utilitzen variables. L'opció considerada busca indicadors que la funció objectiu sigui major (menor) o igual al valor especificat.
Etapa preparatòria
Abans de col·locar una funció a la cinta, heu d'entendre com funciona l'opció. Per exemple, hi ha informació sobre la venda de béns indicada a la taula. La tasca és assignar un descompte per a cada article, que seria de 4.5 milions de rubles. El paràmetre es mostra dins d'una cel·la anomenada target. A partir d'això, es calculen altres paràmetres.
La nostra tasca serà calcular el descompte pel qual es multipliquen les sumes per a la venda de diversos productes. Aquests 2 elements estan connectats per una fórmula escrita com aquesta: =D13*$G$2. On a D13 s'escriu la quantitat total per a la implementació i $G$2 és l'adreça de l'element desitjat.
Ús de la funció i configuració
Quan la fórmula estigui a punt, heu d'utilitzar directament la funció:
- Heu de canviar a la secció "Dades" i fer clic a "Cerca una solució".
- S'obrirà les "Opcions", on es defineixen els paràmetres requerits. A la línia "Optimitza la funció objectiu:" has d'especificar la cel·la on es mostra la suma dels descomptes. És possible prescriure les coordenades tu mateix o escollir del document.
- A continuació, heu d'anar a la configuració d'altres paràmetres. A la secció "A:", és possible establir els límits màxim i mínim o un nombre exacte.
- A continuació, s'omple el camp "Canviant els valors de les variables:". Aquí s'introdueixen les dades de la cel·la desitjada, que conté un valor específic. Les coordenades es registren de manera independent o es fa clic a la cel·la corresponent del document.
- A continuació, s'edita la pestanya "Segons restriccions:", on s'estableixen les restriccions a les dades aplicades. Per exemple, s'exclouen les fraccions decimals o els nombres negatius.
- Després d'això, s'obre una finestra que us permet afegir restriccions als càlculs. La línia inicial conté les coordenades d'una cel·la o d'un rang sencer. Seguint les condicions de la tasca, s'indiquen les dades de la cel·la desitjada, on es mostra l'indicador de descompte. Aleshores es determina el signe de comparació. S'estableix en "més gran o igual a" de manera que el valor final no estigui amb un signe menys. El "Límit" establert a la línia 3 és 0 en aquesta situació. També és possible establir un límit amb "Afegeix". Els següents passos són els mateixos.
- Quan s'han completat els passos anteriors, el límit establert apareix a la línia més gran. La llista pot ser gran i dependrà de la complexitat dels càlculs, però, en una situació particular, 1 condició és suficient.
- A més, és possible seleccionar altres configuracions avançades. A la part inferior dreta hi ha una opció "Opcions" que us permet fer-ho.
- A la configuració, podeu establir la "Precisió de la limitació" i els "Límits de la solució". En la nostra situació, no cal utilitzar aquestes opcions.
- Quan s'hagi completat la configuració, s'inicia la funció en si, feu clic a "Troba una solució".
- Després que el programa realitza els càlculs necessaris i emet els càlculs finals a les cel·les requerides. Aleshores s'obre una finestra amb els resultats, on es guarden/cancel·len els resultats, o es configuren els paràmetres de cerca segons un de nou. Quan les dades compleixen els requisits, es desa la solució trobada. Si marqueu el quadre de diàleg "Torna al quadre de diàleg d'opcions de cerca de solucions" per endavant, s'obrirà una finestra amb la configuració de les funcions.
- Hi ha la possibilitat que els càlculs resultin ser erronis o cal modificar les dades inicials per obtenir altres indicadors. En aquesta situació, cal que torneu a obrir la finestra de configuració i comproveu la informació.
- Quan les dades són precises, es pot utilitzar un mètode alternatiu. Per a aquests propòsits, cal que feu clic a l'opció actual i seleccioneu el mètode més adequat de la llista que apareix:
- Trobar una solució utilitzant un gradient generalitzat per a problemes no lineals. Per defecte, s'utilitza aquesta opció, però és possible utilitzar-ne altres.
- Trobar solucions per a problemes lineals basats en el mètode simplex.
- Utilitzar la cerca evolutiva per completar una tasca.
Atenció! Quan les opcions anteriors no han pogut fer front a la tasca, hauríeu de tornar a comprovar les dades a la configuració, ja que sovint aquest és l'error principal en aquestes tasques.
- Quan es rep el descompte desitjat, queda aplicar-lo per calcular l'import de descomptes per a cada article. Amb aquest propòsit, es destaca l'element inicial de la columna "Import de descompte", s'escriu la fórmula «=D2*$G$2» i premeu "Enter". Els signes de dòlar es posen de manera que quan la fórmula s'estira a les línies adjacents, G2 no canvia.
- Ara s'obtindrà l'import de descompte per a l'article inicial. Aleshores, hauríeu de moure el cursor per sobre de la cantonada de la cel·la, quan es converteixi en un "plus", es prem el LMB i la fórmula s'estira a les línies requerides.
- Després d'això, la taula estarà a punt.
Carrega/Desa les opcions de cerca
Aquesta opció és útil quan s'apliquen diverses opcions de restricció.
- Al menú Opcions del cercador de solucions, feu clic a Carrega/Desa.
- Introduïu l'interval de l'àrea del model i feu clic a Desa o Carrega.
En desar el model, s'introdueix una referència a 1 cel·la d'una columna buida on es col·locarà el model d'optimització. Durant la càrrega del model, s'introdueix una referència a tot el rang que conté el model d'optimització.
Important Per desar la darrera configuració al menú Opcions de solució, es desa un llibre de treball. Cada full té les seves pròpies opcions de complement de Solver. A més, és possible configurar més d'1 tasca per a un full fent clic al botó "Carrega o desa" per desar tasques individuals.
Un exemple senzill d'utilitzar Solver
Cal carregar el contenidor amb contenidors perquè la seva massa sigui màxima. El dipòsit té un volum de 32 metres cúbics. m. Una caixa plena té un pes de 20 kg, el seu volum és de 0,15 metres cúbics. m. Caixa – 80 kg i 0,5 cu. m. Es requereix que el nombre total d'envasos sigui d'almenys 110 unitats. Les dades s'organitzen així:
Les variables del model estan marcades en verd. La funció objectiu està ressaltada en vermell. Restriccions: pel nombre més petit d'envasos (superior o igual a 110) i pel pes (=SUMAPRODUCTE(B8:C8;B6:C6) – Pes de tara total al recipient.
Per analogia, considerem el volum total: =SUMAPRODUCTE(B7:C7;B8:C8). Aquesta fórmula és necessària per establir un límit al volum total dels contenidors. Després, a través de la "Cerca d'una solució", s'introdueixen enllaços a elements amb variables, fórmules i els propis indicadors (o enllaços a cel·les concretes). Per descomptat, el nombre de contenidors és un nombre enter (també és una limitació). Premem "Troba una solució", com a resultat de la qual cosa trobem un nombre tan gran de contenidors quan la massa total és màxima i es tenen en compte totes les restriccions.
La cerca de la solució no ha pogut trobar solucions
Aquesta notificació apareix quan la funció en qüestió no ha trobat combinacions de puntuacions variables que compleixin cada restricció. Quan s'utilitza el mètode Simplex, és molt possible que no hi hagi solució.
Quan s'utilitza un mètode per resoldre problemes no lineals, en tots els casos a partir dels indicadors inicials de les variables, això indica que la solució possible està lluny d'aquests paràmetres. Si executeu la funció amb altres indicadors inicials de les variables, probablement hi hagi una solució.
Per exemple, quan s'utilitza el mètode no lineal, els elements de la taula amb variables no s'han omplert i la funció no ha trobat solucions. Això no vol dir que no hi hagi solució. Ara, tenint en compte els resultats d'una determinada valoració, s'introdueixen altres dades als elements amb variables properes a les rebudes.
En qualsevol situació, primer hauríeu d'examinar el model per detectar l'absència d'un conflicte de restriccions. Sovint, això està interconnectat amb una selecció incorrecta de la proporció o de l'indicador limitant.
A l'exemple anterior, l'indicador de volum màxim és de 16 metres cúbics. m en lloc de 32, perquè aquesta restricció contradiu els indicadors del nombre mínim de seients, ja que correspondrà al nombre de 16,5 metres cúbics. m.
Conclusió
En base a això, l'opció "Cerca una solució" a Excel ajudarà a resoldre problemes específics que són bastant difícils o impossibles de resoldre de les maneres habituals. La dificultat d'aplicar aquest mètode és que inicialment aquesta opció està oculta, per això la majoria d'usuaris no són conscients de la seva presència. A més, la funció és força difícil d'aprendre i utilitzar, però amb una investigació adequada, aportarà grans beneficis i facilitarà els càlculs.