Com s'utilitza l'objectiu de cerca i el solucionador d'Excel per resoldre variables desconegudes

Com s'utilitza l'objectiu de cerca i el solucionador d'Excel per resoldre variables desconegudes

Excel és molt capaç quan disposeu de totes les dades que necessiteu per als vostres càlculs.





Però no seria bo si pogués resoldre les variables desconegudes ?





Amb Goal Seek i el complement Solver, pot. I us mostrarem com fer-ho. Seguiu llegint per obtenir una guia completa sobre com resoldre una sola cel·la amb Goal Seek o una equació més complicada amb Solver.





Com s'utilitza la cerca d'objectius a Excel

Objectiu de cerca ja està integrat a Excel. Es troba sota el Dades , a la pestanya Anàlisi de què passa? menú:

Per a aquest exemple, utilitzarem un conjunt de números molt senzill. Tenim tres trimestres de vendes i un objectiu anual. Podem utilitzar Goal Seek per esbrinar quins números han de ser a la quarta quarta part per aconseguir l’objectiu.



Com podeu veure, el total actual de vendes és de 114.706 unitats. Si volem vendre 250.000 a finals d’any, quants n’hem de vendre al quart trimestre? L’objectiu de Search Seek ens ho dirà.

A continuació s'explica com utilitzar l'objectiu Seek, pas a pas:





  1. Feu clic a Dades> Què passa si anàlisi> Cercar objectius? . Veureu aquesta finestra:
  2. Poseu la part 'igual' de la vostra equació al fitxer Estableix la cel·la camp. Aquest és el número que Excel intentarà optimitzar. En el nostre cas, és el total corrent dels nostres números de vendes a la cel·la B5.
  3. Escriviu el valor de l'objectiu a Valorar camp. Estem buscant un total de 250.000 unitats venudes, de manera que posarem '250.000' en aquest camp.
  4. Digueu a Excel quina variable voleu resoldre a Canviant de cel·la camp. Volem veure quines han de ser les nostres vendes en el quart trimestre. Per tant, direm a Excel que resolgui la cel·la D2. Es veurà així quan estigui a punt:
  5. Encertat D'acord per resoldre el vostre objectiu. Quan es vegi bé, només cal que premeu D'acord . Excel us avisarà quan Goal Seek hagi trobat una solució.
  6. Feu clic a D'acord de nou i veureu el valor que resol la vostra equació a la cel·la per a la qual heu triat Canviant de cel·la .

En el nostre cas, la solució és de 135.294 unitats. Per descomptat, podríem haver trobat això restant el total corrent de l’objectiu anual. Però Goal Seek també es pot utilitzar en una cel·la que ja té dades . I això és més útil.

Tingueu en compte que Excel sobreescriu les nostres dades anteriors. És una bona idea fer-ho executeu Goal Seek en una còpia de les vostres dades . També és una bona idea fer una nota sobre les dades que heu copiat que es van generar mitjançant la cerca d’objectius. No el voleu confondre amb dades actuals i precises.





com configurar el compte de Gmail per defecte

Així que Goal Seek és un útil funció d'Excel , però no és tan impressionant. Vegem una eina molt més interessant: el complement Solver.

Què fa el solucionador d'Excel?

En resum, Solver és com un versió multivariant de Goal Seek . Agafa una variable d’objectiu i ajusta una sèrie d’altres variables fins a obtenir la resposta que desitgeu.

Pot resoldre un valor màxim d’un nombre, un valor mínim d’un nombre o un nombre exacte.

I funciona dins de les limitacions, de manera que si una variable no es pot canviar o només pot variar dins d’un interval especificat, Solver ho tindrà en compte.

És una bona manera de resoldre diverses variables desconegudes a Excel. Però trobar-lo i utilitzar-lo no és senzill.

Fem una ullada a la càrrega del complement Solver i, a continuació, expliquem com utilitzar Solver a Excel 2016.

Com es carrega el complement Solver

Excel no té Solver per defecte. És un complement, de manera que, com altres funcions excel·lents d'Excel, primer heu de carregar-lo. Afortunadament, ja està al vostre ordinador.

Cap a Fitxer> Opcions> Complements . A continuació, feu clic a Vaja Al costat de Gestiona: complements d'Excel .

Si aquest menú desplegable diu alguna cosa que no sigui 'Complements d'Excel', l'haureu de canviar:

A la finestra resultant, veureu algunes opcions. Assegureu-vos que la casella que hi ha al costat Complement Solver es comprova i prem D'acord .

Ara veureu el fitxer Solucionari botó al fitxer Anàlisi grup de la Dades pestanya:

Si ja heu estat utilitzant el fitxer Toolpak d’anàlisi de dades , veureu el botó Anàlisi de dades. Si no, Solver apareixerà per si sol.

Ara que heu carregat el complement, fem una ullada a com utilitzar-lo.

Com s'utilitza Solver a Excel

Hi ha tres parts en qualsevol acció de Solver: l’objectiu, les cel·les variables i les restriccions. Caminarem per cadascun dels passos.

  1. Feu clic a Dades> Solucionador . A continuació, veureu la finestra Paràmetres del solucionador. (Si no veieu el botó Solver, consulteu la secció anterior sobre com carregar el complement Solver.)
  2. Establiu l'objectiu de la vostra cel·la i expliqueu a Excel el vostre objectiu. L’objectiu es troba a la part superior de la finestra Solver i té dues parts: la cel·la objectiu i una selecció de maximitzar, minimitzar o un valor específic. Si seleccioneu Màx , Excel ajustarà les vostres variables per obtenir el major nombre possible a la cel·la objectiu. Mín és el contrari: Solver minimitzarà el nombre objectiu. El valor de us permet especificar un número específic per a Solver.
  3. Trieu les cel·les variables que pot canviar Excel. Les cel·les variables s’estableixen amb el fitxer Canviant les cel·les variables camp. Feu clic a la fletxa que hi ha al costat del camp i, a continuació, feu clic i arrossegueu per seleccionar les cel·les amb les quals hauria de treballar Solver. Tingueu en compte que aquests són totes les cèl·lules que pot variar. Si no voleu que canviï una cel·la, no la seleccioneu.
  4. Estableix restriccions a diverses variables individuals o individuals. Finalment, arribem a les limitacions. Aquí és on Solver és realment potent. En lloc de canviar qualsevol de les cel·les variables a qualsevol número que vulgui, podeu especificar restriccions que s'han de complir. Per obtenir més informació, consulteu la secció sobre com establir restriccions a continuació.
  5. Un cop tota aquesta informació estigui al seu lloc, premeu Resoldre per obtenir la vostra resposta. Excel actualitzarà les vostres dades per incloure les variables noves (per això us recomanem que creeu primer una còpia de les vostres dades).

També podeu generar informes, que veurem breument en el nostre exemple de Solver a continuació.

Com establir restriccions a Solver

És possible que digueu a Excel que una variable ha de ser superior a 200. Quan proveu valors de variables diferents, Excel no passarà per sota de 201 amb aquesta variable en particular.

Per afegir una restricció, feu clic a Afegeix al costat de la llista de restriccions. Rebreu una finestra nova. Seleccioneu la cel·la (o cel·les) que voleu restringir el fitxer Referència cel·lular , trieu un operador.

Aquests són els operadors disponibles:

  • <= (inferior o igual a)
  • = (igual a)
  • => (superior o igual a)
  • int (ha de ser un nombre enter)
  • sóc (ha de ser 1 o 0)
  • Totes diferents

Totes diferents és una mica confús. Especifica que cada cel·la de l’interval per a la qual seleccioneu Referència cel·lular ha de ser un número diferent. Però també s’especifica que han d’estar entre 1 i el nombre de cel·les. Per tant, si teniu tres cel·les, acabareu amb els números 1, 2 i 3 (però no necessàriament en aquest ordre)

Finalment, afegiu el valor de la restricció.

És important recordar que podeu seleccioneu diverses cel·les per a referència cel·lular. Si voleu que sis variables tinguin valors superiors a 10, per exemple, podeu seleccionar-les totes i dir-li a Solver que han de ser superiors o iguals a 11. No heu d'afegir cap restricció per a cada cel·la.

També podeu utilitzar la casella de selecció de la finestra principal del solucionador per assegurar-vos que tots els valors per als quals no heu especificat restriccions no siguin negatius. Si voleu que les vostres variables siguin negatives, desmarqueu aquesta casella.

Un exemple de resolució

Per veure com funciona tot això, utilitzarem el complement Solver per fer un càlcul ràpid. A continuació, es detallen les dades amb què comencem:

Hi tenim cinc llocs de treball diferents, cadascun dels quals paga una taxa diferent. També tenim el nombre d’hores que un treballador teòric ha treballat en cadascun d’aquests llocs de treball en una setmana determinada. Podem utilitzar el complement Solver per esbrinar com maximitzar el sou total mantenint certes variables dins d’algunes restriccions.

Aquí hi ha les restriccions que utilitzarem:

  • Sense feines pot caure per sota de les quatre hores.
  • El treball 2 ha de ser superior a vuit hores .
  • El treball 5 ha de ser menys d’onze hores .
  • El total d'hores treballades ha de ser igual a 40 .

Pot ser útil escriure les vostres restriccions com aquesta abans d'utilitzar Solver.

A continuació s’explica com ho configuraríem a Solver:

En primer lloc, tingueu en compte això He creat una còpia de la taula de manera que no sobreescrivim l’original, que conté l’horari laboral actual.

I, en segon lloc, vegeu que els valors de les restriccions superiors i inferiors són un més alt o més baix que el que he esmentat anteriorment. Això es deu al fet que no hi ha més o menys que opcions. Només n’hi ha de majors o iguals i menors que o iguals.

Anem a colpejar Resoldre i veure què passa.

Solver ha trobat una solució. Com podeu veure a l'esquerra de la finestra superior, els nostres guanys han augmentat en 130 dòlars. I s’han complert totes les restriccions.

és il·legal baixar música de youtube per a ús personal

Assegureu-vos de mantenir els nous valors Mantingui la solució Solver es comprova i es prem D'acord .

Tanmateix, si voleu més informació, podeu seleccionar un informe a la part dreta de la finestra. Seleccioneu tots els informes que vulgueu, digueu a Excel si voleu que es mostrin (el recomano) i premeu D'acord .

Els informes es generen en fulls nous del vostre llibre de treball i us proporcionen informació sobre el procés que ha seguit el complement Solver per obtenir la vostra resposta.

En el nostre cas, els informes no són gaire emocionants i no hi ha molta informació interessant. Però si executeu una equació Solver més complicada, és possible que trobeu informació útil sobre els informes en aquests nous fulls de treball. Només cal que feu clic a + al lateral de qualsevol informe per obtenir més informació:

Opcions avançades de Solver

Si no coneixeu molt les estadístiques, podeu ignorar les opcions avançades de Solver i executar-les tal qual. Però si feu càlculs complexos i grans, és possible que vulgueu examinar-los.

El més evident és el mètode de resolució:

Podeu triar entre GRG Nonlinear, Simplex LP i Evolutionary. Excel proporciona una explicació senzilla sobre quan heu d’utilitzar-les. Una millor explicació requereix un cert coneixement d’estadístiques i regressió.

Per ajustar configuracions addicionals, només cal que premeu el botó Opcions botó. Podeu informar a Excel sobre l’optimitat de l’enter, establir restriccions de temps de càlcul (útils per a conjunts de dades massius) i ajustar la forma en què els mètodes de resolució GRG i evolutiva fan els seus càlculs.

Una vegada més, si no sabeu què significa això, no us preocupeu. Si voleu obtenir més informació sobre quin mètode de resolució heu d’utilitzar, Engineer Excel té un bon article que us el proposa . Si voleu la màxima precisió, Evolutionary és probablement un bon camí a seguir. Tingueu en compte que trigarà molt de temps.

Cercador d’objectius i solucionador: portar Excel al següent nivell

Ara que esteu còmode amb els conceptes bàsics de resolució de variables desconegudes a Excel, us obre un món completament nou de càlcul de fulls de càlcul.

Objectiu de cerca pot ajudar a estalviar temps fent alguns càlculs més ràpid, i Solver afegeix una gran quantitat de potència a Capacitats de càlcul d'Excel .

Només es tracta de posar-se a gust amb ells. Com més les utilitzeu, més útils seran.

Feu servir Object Seek o Solver als vostres fulls de càlcul? Quins altres consells podeu proporcionar per obtenir-ne les millors respostes? Comparteix les teves opinions als comentaris següents.

Compartir Compartir Tweet Correu electrònic 5 consells per sobrecarregar les vostres màquines VirtualBox Linux

Cansat del baix rendiment que ofereixen les màquines virtuals? Això és el que heu de fer per millorar el rendiment del VirtualBox.

Llegiu a continuació
Temes relacionats
  • Productivitat
  • Full de càlcul
  • Microsoft Excel
  • Consells de Microsoft Office
Sobre l'autor Després, Albright(506 articles publicats)

Dann és un assessor d’estratègia i màrqueting de contingut que ajuda les empreses a generar demanda i clients potencials. També fa blogs sobre màrqueting d’estratègia i contingut a dannalbright.com.

Més de Dann Albright

Subscriu-te al nostre butlletí

Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.

Feu clic aquí per subscriure-us