Kako uporabiti Excelovo iskanje in reševanje ciljev za reševanje neznanih spremenljivk

Kako uporabiti Excelovo iskanje in reševanje ciljev za reševanje neznanih spremenljivk

Excel je zelo zmogljiv, če imate vse podatke, ki jih potrebujete za izračune.





Ampak ne bi bilo lepo, če bi lahko rešiti neznane spremenljivke ?



Z Goal Seek in dodatkom Solver lahko. In pokazali vam bomo, kako. Preberite celoten vodnik o tem, kako rešiti eno celico z iskanjem cilja ali bolj zapleteno enačbo z reševalcem.





Kako uporabljati iskanje ciljev v Excelu

Iskanje ciljev je že vgrajeno v Excel. To je pod Podatki zavihek, v Kaj-če analiza meni:

V tem primeru bomo uporabili zelo preprost niz številk. Imamo tri četrtine vrednosti prodaj in letni cilj. Z iskanjem cilja lahko ugotovimo, kakšne številke morajo biti v četrtem četrtletju, da dosežemo cilj.



Kot lahko vidite, je trenutna skupna prodaja 114.706 enot. Če želimo do konca leta prodati 250.000, koliko jih moramo prodati v četrtem četrtletju? Excelov cilj iskanja nam bo povedal.

Evo, kako korak za korakom uporabljati iskanje cilja:



  1. Kliknite Podatki> Analiza kaj-če> Iskanje cilja . Videli boste to okno:
  2. Del enačbe postavite v enačbo Nastavi celico polje. To je število, ki ga bo Excel poskušal optimizirati. V našem primeru gre za skupni seštevek naših prodajnih številk v celici B5.
  3. Vnesite vrednost cilja v Ceniti polje. Iščemo skupaj 250.000 prodanih enot, zato bomo na tem področju postavili '250.000'.
  4. Povejte Excelu, katero spremenljivko je treba rešiti v datoteki S spremembo celice polje. Želimo videti, kakšna naj bo naša prodaja v četrtem četrtletju. Tako bomo Excelu povedali, naj reši za celico D2. Ko bo pripravljen, bo videti tako:
  5. Zadel v redu rešiti za svoj cilj. Ko je videti dobro, samo zadeti v redu . Excel vas bo obvestil, ko bo iskanje cilja našlo rešitev.
  6. Kliknite v redu spet boste videli vrednost, ki rešuje vašo enačbo v celici, za katero ste se odločili S spremembo celice .

V našem primeru je rešitev 135.294 enot. Seveda bi to lahko ugotovili le tako, da odštejemo tekoči seštevek od letnega cilja. Toda Goal Seek lahko uporabite tudi na celici, ki že vsebuje podatke . In to je bolj uporabno.

Upoštevajte, da Excel prepiše naše prejšnje podatke. To je dobra ideja zaženite iskanje cilja na kopiji svojih podatkov . Prav tako je dobro, da si zabeležite kopirane podatke, da so bili ustvarjeni s pomočjo iskalnika ciljev. Ne želite ga zamenjati za trenutne in točne podatke.



ali je moj računalnik združljiv z operacijskim sistemom Windows 10

Torej je iskanje cilja a uporabna funkcija Excel , vendar ni vse tako impresivno. Oglejmo si orodje, ki je veliko bolj zanimivo: dodatek Solver.

Kaj počne Excel's Solver?

Skratka, Solver je kot a več variabilna različica iskanja cilja . Potrebuje eno ciljno spremenljivko in prilagodi številne druge spremenljivke, dokler ne dobi želenega odgovora.

Lahko reši največjo vrednost števila, najmanjšo vrednost števila ali natančno število.

Deluje znotraj omejitev, zato če ene spremenljivke ni mogoče spremeniti ali pa se lahko spreminja le v določenem obsegu, bo Solver to upošteval.

To je odličen način za reševanje več neznanih spremenljivk v Excelu. Toda iskanje in uporaba nista enostavna.

Oglejmo si nalaganje dodatka Solver, nato pa pojdimo, kako uporabljati Solver v Excelu 2016.

Kako naložiti dodatek Solver

Excel privzeto nima Solverja. To je dodatek, zato ga morate, tako kot druge zmogljive Excelove funkcije, najprej naložiti. Na srečo je že v računalniku.

Namenjen Datoteka> Možnosti> Dodatki . Nato kliknite na Pojdi zraven Upravljanje: dodatki Excel .

Če ta spustni meni pove nekaj drugega kot »dodatki Excel«, ga morate spremeniti:

V oknu, ki se prikaže, boste videli nekaj možnosti. Prepričajte se, da je polje zraven Dodatek Solver je preverjeno in pritisnjeno v redu .

Zdaj boste videli Reševalec gumb v Analiza skupine Podatki zavihek:

Če ste že uporabljali Paket orodij za analizo podatkov , boste videli gumb Analiza podatkov. Če ne, se bo Solver pojavil sam.

Zdaj, ko ste dodali dodatek, poglejmo, kako ga uporabljati.

Kako uporabljati Solver v Excelu

Vsako dejanje reševalca ima tri dele: cilj, spremenljive celice in omejitve. Šli bomo skozi vsak korak.

  1. Kliknite Podatki> Reševalnik . Spodaj boste videli okno Parametri reševalca. (Če ne vidite gumba za reševanje, glejte prejšnji razdelek o tem, kako naložite dodatek Solver.)
  2. Določite cilj svoje celice in Excelu povejte svoj cilj. Cilj je na vrhu okna Solver in ima dva dela: celico cilja in izbiro povečanja, zmanjšanja ali določene vrednosti. Če izberete Maks , Excel bo prilagodil vaše spremenljivke, da bo v ciljno celico dobil čim večje število. Min je obratno: Solver bo zmanjšal število ciljev. Vrednost vam omogoča, da določite posebno številko, ki jo bo Solver iskal.
  3. Izberite spremenljive celice, ki jih lahko Excel spremeni. Spremenljive celice so nastavljene z S spreminjanjem spremenljivih celic polje. Kliknite puščico poleg polja, nato kliknite in povlecite, da izberete celice, s katerimi bi moral delati Solver. Upoštevajte, da so to vse celice ki se lahko razlikujejo. Če ne želite, da se celica spremeni, je ne izberite.
  4. Nastavite omejitve za več ali posamezne spremenljivke. Končno pridemo do omejitev. Tu je Solver resnično močan. Namesto da spremenite katero koli spremenljivko v poljubno število, lahko podate omejitve, ki jih morate upoštevati. Za podrobnosti glejte spodnji razdelek o nastavitvi omejitev.
  5. Ko so vsi ti podatki na mestu, pritisnite Rešiti da dobim vaš odgovor. Excel bo posodobil vaše podatke in vključil nove spremenljivke (zato vam priporočamo, da najprej ustvarite kopijo svojih podatkov).

Ustvarite lahko tudi poročila, ki jih bomo na kratko pogledali v spodnjem primeru Solverja.

Kako nastaviti omejitve v Solverju

Excelu lahko rečete, da mora biti ena spremenljivka večja od 200. Ko preizkušate različne vrednosti spremenljivk, Excel s to posebno spremenljivko ne bo šel pod 201.

Če želite dodati omejitev, kliknite Dodaj poleg seznama omejitev. Dobili boste novo okno. Izberite celico (ali celice), ki jih želite omejiti v Referenca celice polje, nato izberite operaterja.

Tu so na voljo operaterji:

  • <= (manjše ali enako)
  • = (enako)
  • => (večji ali enak)
  • int (mora biti celo število)
  • zjutraj (mora biti 1 ali 0)
  • AllDifferent

AllDifferent je malo zmedeno. Določa, da vsaka celica v obsegu, za katero izberete Referenca celice mora biti drugačna številka. Določa pa tudi, da morajo biti med 1 in številom celic. Če imate torej tri celice, boste na koncu dobili številke 1, 2 in 3 (vendar ne nujno v tem vrstnem redu)

Na koncu dodajte vrednost omejitve.

Pomembno si je zapomniti, da lahko izberite več celic za referenco celic. Če želite, da imajo na primer šest spremenljivk vrednosti nad 10, jih lahko izberete vse in poveste Solverju, da morajo biti večje ali enake 11. Ni vam treba dodati omejitve za vsako celico.

Uporabite lahko tudi potrditveno polje v glavnem oknu Solver, da se prepričate, da so vse vrednosti, za katere niste podali omejitev, negativne. Če želite, da so spremenljivke negativne, počistite to polje.

Primer reševalca

Če želite videti, kako vse to deluje, bomo za hiter izračun uporabili dodatek Solver. Tu so podatki, s katerimi začenjamo:

V njem imamo pet različnih delovnih mest, od katerih vsako plačuje drugačno stopnjo. Imamo tudi število ur, ki jih je teoretični delavec opravil na vsakem od teh delovnih mest v določenem tednu. Z dodatkom Solver lahko ugotovimo, kako povečati skupno plačilo, hkrati pa ohraniti nekatere spremenljivke v okviru nekaterih omejitev.

Tu so omejitve, ki jih bomo uporabili:

  • Brez delovnih mest lahko pade pod štiri ure.
  • Job 2 mora biti več kot osem ur .
  • Delo 5 mora biti manj kot enajst ur .
  • Skupno število opravljenih ur mora biti enako 40 .

Pred uporabo Solverja je lahko v pomoč, če tako zapišete svoje omejitve.

Tako smo to nastavili v Solverju:

Najprej upoštevajte, da Ustvaril sem kopijo tabele zato ne prepišemo izvirnega, ki vsebuje naš trenutni delovni čas.

In drugič, poglejte, da so vrednosti v omejitvah večje in manjše eno višje ali nižje od tega, kar sem omenil zgoraj. To je zato, ker ni možnosti, ki so večje ali manjše. Obstajajo le večji ali enaki in manjši ali enaki.

Udarimo Rešiti in poglej, kaj se bo zgodilo.

Solver je našel rešitev! Kot lahko vidite na levi strani zgornjega okna, se je naš zaslužek povečal za 130 USD. In vse omejitve so bile izpolnjene.

prisilno zapre program Windows 10

Če želite ohraniti nove vrednosti, se prepričajte Ohranite rešitev Solver se preveri in zadene v redu .

Če želite več informacij, lahko poročilo izberete na desni strani okna. Izberite vsa poročila, ki jih želite, Excelu povejte, ali jih želite opisati (priporočam) in pritisnite v redu .

Poročila so ustvarjena na novih listih v vašem delovnem zvezku in vam dajejo informacije o postopku, ki ga je dodatek Solver opravil, da bi dobili vaš odgovor.

V našem primeru poročila niso zelo razburljiva in tam ni veliko zanimivih informacij. Če pa zaženete bolj zapleteno enačbo reševalca, boste v teh novih delovnih listih našli nekaj koristnih informacij o poročanju. Samo kliknite na + gumb ob strani vsakega poročila za več informacij:

Dodatne možnosti reševalca

Če ne veste veliko o statistiki, lahko zanemarite napredne možnosti Solverja in ga preprosto zaženete. Če pa izvajate velike in zapletene izračune, jih boste morda želeli preučiti.

Najbolj očitna je metoda reševanja:

Izbirate lahko med GRINE Nonlinear, Simplex LP in Evolutionary. Excel ponuja preprosto razlago, kdaj morate uporabiti vsako od njih. Boljša razlaga zahteva nekaj znanja o statistiki in regresiji.

Če želite prilagoditi dodatne nastavitve, pritisnite tipko Opcije gumb. Excelu lahko poveste o optimalnosti celih števil, nastavite časovne omejitve pri izračunu (uporabno za množične nabore podatkov) in prilagodite, kako metode GRG in evolucijske rešitve izvajajo svoje izračune.

Še enkrat, če ne veste, kaj vse to pomeni, naj vas to ne skrbi. Če želite izvedeti več o tem, katero metodo reševanja uporabiti, ima inženir Excel datoteko dober članek, ki vam to pove . Če želite največjo natančnost, je Evolutionary verjetno dobra pot. Samo zavedajte se, da bo trajalo dolgo.

Iskanje in reševanje ciljev: dvig Excela na naslednjo stopnjo

Zdaj, ko ste seznanjeni z osnovami reševanja neznanih spremenljivk v Excelu, vam je odprt povsem nov svet izračuna preglednic.

Z iskanjem cilja lahko prihranite čas, tako da pospešite nekatere izračune, Solver pa doda ogromno energije Excelove računske sposobnosti .

Samo vprašanje, kako se z njimi sprijazniti. Bolj ko jih uporabljate, bolj uporabni bodo.

Ali v svojih preglednicah uporabljate iskanje ciljev ali reševanje? Katere druge nasvete lahko zagotovite, da iz njih dobite najboljše odgovore? Delite svoje misli v spodnjih komentarjih!

Deliti Deliti Cvrkutati E-naslov 5 nasvetov za polnjenje vaših strojev VirtualBox Linux

Ste naveličani slabe zmogljivosti, ki jo ponujajo virtualni stroji? Tukaj je tisto, kar morate storiti za povečanje zmogljivosti VirtualBox.

Preberite Naprej Sorodne teme
  • Produktivnost
  • Preglednica
  • Microsoft Excel
  • Nasveti za Microsoft Office
O avtorju Potem Albright(Objavljenih 506 člankov)

Dann je svetovalec za vsebinsko strategijo in trženje, ki podjetjem pomaga ustvarjati povpraševanje in potencialne stranke. Na spletnem mestu dannalbright.com piše tudi o strategijah in vsebinskem trženju.

Več od Danna Albrighta

Naročite se na naše novice

Pridružite se našemu glasilu za tehnične nasvete, ocene, brezplačne e -knjige in ekskluzivne ponudbe!

Kliknite tukaj, če se želite naročiti