Kuidas kasutada Exceli eesmärgiotsijat ja lahendajat tundmatute muutujate lahendamiseks

Excel on väga võimekas, kui teil on kõik arvutuste jaoks vajalikud andmed.

Aga kas poleks tore, kui saaks lahendada tundmatute muutujate jaoks ?

Eesmärgiotsingu ja lahendaja lisandmooduli abil saab seda teha. Ja me näitame teile, kuidas. Siit leiate täieliku juhendi selle kohta, kuidas lahendada ühe lahtri jaoks eesmärgiotsing või keerulisem võrrand Solveriga.

Kuidas kasutada eesmärgiotsingut Excelis

Eesmärgiotsing on juba Excelisse sisse ehitatud. See on all Andmed vahekaardil Mis-kui analüüs menüü:

Selle näite puhul kasutame väga lihtsat numbrikomplekti. Meil on kolmveerandi väärtuses müüginumbreid ja iga -aastane eesmärk. Me võime eesmärgiotsingu abil välja selgitada, millised numbrid peavad olema neljandas kvartalis eesmärgi saavutamiseks.

Nagu näete, on praegune müük kokku 114 706 ühikut. Kui tahame aasta lõpuks müüa 250 000, siis kui palju on meil vaja neljandas kvartalis müüa? Exceli eesmärgiotsing ütleb meile.

Eesmärgiotsingu kasutamine samm -sammult toimub järgmiselt.

Klõpsake nuppu Andmed> Mis-kui-analüüs> Eesmärgiotsing . Näete seda akent:
Pange oma võrrandi osa 'võrdne' lahtrisse Määra lahter valdkonnas. See on number, mida Excel püüab optimeerida. Meie puhul on see meie müüginumbrite jooksev kogus lahtris B5.
Sisestage oma eesmärgi väärtus lahtrisse Hindama valdkonnas. Otsime kokku 250 000 ühikut, seega paneme sellele väljale „250 000”.
Öelge Excelile, millist muutujat lahendada Lahtrit vahetades valdkonnas. Tahame näha, milline peab olema meie neljanda kvartali müük. Nii et me ütleme Excelile, et see lahendaks lahtri D2. See näeb välja selline, kui see on valmis minema:
Tabas Okei oma eesmärgi nimel lahendada. Kui tundub hea, siis lihtsalt löö Okei . Excel annab teile teada, kui eesmärgiotsing on lahenduse leidnud.
Klõpsake nuppu Okei uuesti ja näete väärtust, mis lahendab teie võrrandi lahtris, mille valisite Lahtrit vahetades .

Meie puhul on lahendus 135 294 ühikut. Muidugi oleksime võinud selle lihtsalt leida, kui lahutame jooksvast kogusummast aastase eesmärgi. Kuid eesmärgiotsingut saab kasutada ka lahtris, mis selles on juba andmed . Ja see on kasulikum.

Pange tähele, et Excel kirjutab meie varasemad andmed üle. See on hea mõte käivitage oma andmete koopial eesmärkide otsimine . Samuti on hea mõte oma kopeeritud andmetele märkida, et need on loodud eesmärgiotsingu abil. Te ei soovi seda ajakohaste ja täpsete andmetega segi ajada.

rakendus filmide tasuta vaatamiseks

Nii et eesmärgiotsing on a kasulik Exceli funktsioon , kuid see pole kõik nii muljetavaldav. Vaatame tööriista, mis on palju huvitavam: lisandmoodul Solver.

Mida teeb Exceli lahendaja?

Lühidalt, Solver on nagu a eesmärgiotsingu mitme muutujaga versioon . See võtab ühe eesmärgimuutuja ja kohandab mitmeid teisi muutujaid, kuni saab soovitud vastuse.

See võib lahendada arvu maksimaalse väärtuse, arvu minimaalse väärtuse või täpse arvu.

Ja see töötab piirangute piires, nii et kui ühte muutujat ei saa muuta või see võib varieeruda ainult määratud vahemikus, võtab Solver seda arvesse.

See on suurepärane võimalus lahendada mitu tundmatut muutujat Excelis. Kuid selle leidmine ja kasutamine pole lihtne.

Heidame pilgu Solveri lisandmooduli laadimisele ja uurime seejärel, kuidas lahendust Excelis 2016 kasutada.

Lahendaja lisandmooduli laadimine

Excelis pole vaikimisi lahendit. See on lisandmoodul, nii nagu ka muud võimsad Exceli funktsioonid, peate selle esmalt laadima. Õnneks on see juba teie arvutis.

Suunduge Fail> Valikud> Lisandmoodulid . Seejärel klõpsake nuppu Mine kõrval Halda: Exceli lisandmoodulid .

Kui see rippmenüü ütleb midagi muud kui „Exceli lisandmoodulid”, peate seda muutma.

Tulemuseks olevas aknas näete mõnda valikut. Veenduge, et kast oleks kõrval Lahendaja lisandmoodul kontrollitakse ja tabatakse Okei .

Nüüd näete Lahendaja nuppu Analüüs rühm Andmed sakk:

Kui olete juba kasutanud Andmeanalüüsi tööriistakomplekt , näete nuppu Andmete analüüs. Kui ei, ilmub Solver iseenesest.

Nüüd, kui olete lisandmooduli laadinud, vaatame, kuidas seda kasutada.

Kuidas lahendust Excelis kasutada?

Igal lahendaja toimingul on kolm osa: eesmärk, muutuvad lahtrid ja piirangud. Kõnnime läbi kõik sammud.

Klõpsake nuppu Andmed> Lahendaja . Näete allpool lahendaja parameetrite akent. (Kui te ei näe lahendaja nuppu, vaadake eelmist jaotist lahendaja lisandmooduli laadimise kohta.)
Määrake lahtri eesmärk ja öelge Excelile oma eesmärk. Eesmärk on lahendaja akna ülaosas ja sellel on kaks osa: eesmärgi lahter ja valik maksimeerida, minimeerida või konkreetne väärtus. Kui valite Max , Excel kohandab teie muutujaid nii, et teie eesmärgi lahtris oleks võimalikult suur arv. Min on vastupidi: lahendaja minimeerib objektiivse arvu. Väärtus võimaldab määrata lahendaja jaoks konkreetse numbri.
Valige muutujarakud, mida Excel saab muuta. Muutuvrakud on seatud klahviga Muutuvate lahtrite muutmisega valdkonnas. Klõpsake välja kõrval olevat noolt, seejärel klõpsake ja lohistage lahtrid, millega lahendaja peaks töötama. Pange tähele, et need on kõik rakud mis võib varieeruda. Kui te ei soovi lahtrit muuta, ärge seda valige.
Seadke piirangud mitmele või üksikule muutujale. Lõpuks jõuame piiranguteni. Siin on Solver tõesti võimas. Selle asemel, et muuta mis tahes muutuja lahtreid soovitud arvuks, saate määrata piirangud, mida tuleb täita. Lisateavet leiate allolevast jaotisest, kuidas piiranguid seada.
Kui kogu see teave on paigas, klõpsake nuppu Lahenda et saada oma vastus. Excel värskendab teie andmeid uute muutujate lisamiseks (seetõttu soovitame teil kõigepealt oma andmetest koopia luua).

Samuti saate koostada aruandeid, mida vaatame lühidalt allpool meie lahendaja näites.

Kuidas lahendajale piiranguid seada

Võite Excelile öelda, et üks muutuja peab olema suurem kui 200. Erinevate muutujaväärtuste proovimisel ei lähe Excel selle muutujaga alla 201.

Piirangu lisamiseks klõpsake nuppu Lisama nuppu piirangute loendi kõrval. Saate uue akna. Valige lahtris (või lahtrites), mida piirata Lahtri viide väljale, seejärel valige operaator.

Siin on saadaolevad operaatorid:

<= (väiksem või võrdne)
= (võrdne)
=> (suurem või võrdne)
int (peab olema täisarv)
olen (peab olema 1 või 0)
AllDifferent

AllDifferent on natuke segane. See määrab, et iga teie valitud vahemiku lahter Lahtri viide peab olema erinev number. Kuid see täpsustab ka, et need peavad olema vahemikus 1 ja lahtrite arv. Nii et kui teil on kolm lahtrit, saate lõpuks numbrid 1, 2 ja 3 (kuid mitte tingimata selles järjekorras)

Lõpuks lisage piirangu väärtus.

Oluline on meeles pidada, et saate valige mitu lahtrit lahtri viite jaoks. Kui soovite näiteks, et kuue muutuja väärtused oleksid üle 10, saate need kõik valida ja öelda lahendajale, et need peavad olema suuremad või võrdsed 11. Te ei pea iga lahtri jaoks piiranguid lisama.

Võite kasutada ka lahendaja peaakna märkeruutu, et veenduda, et kõik väärtused, millele te piiranguid ei määranud, ei ole negatiivsed. Kui soovite, et teie muutujad muutuksid negatiivseks, tühjendage see ruut.

Lahendaja näide

Et näha, kuidas see kõik töötab, kasutame kiire arvutuse tegemiseks lisandmoodulit Solver. Siin on andmed, millest me alustame:

Selles on meil viis erinevat tööd, millest igaüks maksab erinevat määra. Meil on ka see tundide arv, mille teoreetiline töötaja on igal nädalal igal töökohal töötanud. Saame lisandmooduli Solver abil teada saada, kuidas maksta kogupalka, hoides teatud muutujaid teatud piirangute piires.

Siin on piirangud, mida kasutame:

Tööd pole võib langeda alla nelja tunni.
Töö 2 peab olema rohkem kui kaheksa tundi .
Töö 5 peab olema vähem kui üksteist tundi .
Töötatud tundide koguarv peab olema võrdne 40 -ga .

Enne Solveri kasutamist võib olla kasulik oma piirangud niimoodi välja kirjutada.

Selle seadistame Solveris järgmiselt.

Esiteks pange tähele, et Olen loonud tabelist koopia nii et me ei kirjutaks üle esialgset, mis sisaldab meie praegust tööaega.

Ja teiseks, vaadake, et piirangutes on suuremad ja väiksemad väärtused üks kõrgem või madalam kui see, mida ma eespool mainisin. Selle põhjuseks on asjaolu, et valikuid pole rohkem ega vähem. On ainult suuremaid või võrdseid ja vähem kui võrdseid.

Hakkame pihta Lahenda ja vaata, mis saab.

Lahendaja leidis lahenduse! Nagu näete ülaltoodud aknast vasakul, on meie tulud kasvanud 130 dollari võrra. Ja kõik piirangud on täidetud.

tekst võltsitud numbrirakendusest

Uute väärtuste säilitamiseks veenduge Lahendaja lahendus kontrollitakse ja tabatakse Okei .

Kui soovite siiski rohkem teavet, saate aruande valida akna paremast servast. Valige kõik soovitud aruanded, öelge Excelile, kas soovite neid visandada (ma soovitan seda), ja vajutage Okei .

Aruanded genereeritakse teie töövihiku uutele lehtedele ja need annavad teile teavet protsessi kohta, mille lahendaja lisandmoodul teie vastuse saamiseks läbis.

Meie puhul ei ole aruanded eriti põnevad ja seal pole palju huvitavat teavet. Kuid kui kasutate keerukamat lahendaja võrrandit, võite nendest uutest töölehtedest leida kasulikku aruandlusteavet. Lihtsalt klõpsake + lisateabe saamiseks klõpsake mis tahes aruande küljel olevat nuppu:

Lahendaja täpsemad valikud

Kui te ei tea statistikast palju, võite ignoreerida Solveri täpsemaid valikuid ja lihtsalt käivitada see nii, nagu see on. Aga kui teete suuri ja keerukaid arvutusi, võiksite neid uurida.

Kõige ilmsem on lahendamismeetod:

Saate valida GRG mittelineaarse, Simplex LP ja Evolutionary vahel. Excel pakub lihtsat selgitust selle kohta, millal peaksite neid kasutama. Parem selgitus nõuab mõningaid teadmisi statistikast ja regressioonist.

Lisaseadete kohandamiseks klõpsake lihtsalt nuppu Valikud nuppu. Saate Excelile rääkida täisarvude optimaalsusest, seada arvutamise ajapiirangud (kasulik massiivsete andmekogumite jaoks) ja reguleerida, kuidas GRG ja Evolutionary lahendamismeetodid arvutusi teevad.

Jällegi, kui te ei tea, mida see kõik tähendab, ärge muretsege selle pärast. Kui soovite rohkem teada saada, millist lahendamismeetodit kasutada, on Engineer Excelil a hea artikkel, mis selle teile ette näeb . Kui soovite maksimaalset täpsust, on Evolutionary tõenäoliselt hea viis. Pidage ainult meeles, et see võtab kaua aega.

Eesmärkide otsija ja lahendaja: Exceli viimine järgmisele tasemele

Nüüd, kui tunnete Exceli tundmatute muutujate lahendamise põhitõdesid, on teile avatud täiesti uus arvutustabeli arvutamise maailm.

Eesmärgiotsing aitab teil aega säästa, tehes mõned arvutused kiiremini ja Solver lisab sellele tohutult energiat Exceli arvutamisvõime .

Küsimus on vaid selles, et nendega mugavalt hakkama saada. Mida rohkem neid kasutate, seda kasulikumaks need muutuvad.

Kas kasutate arvutustabelites eesmärgiotsijat või lahendajat? Milliseid näpunäiteid saate veel anda nende parimate vastuste saamiseks? Jagage oma mõtteid allolevates kommentaarides!

Jaga Jaga Piiksuma E -post 5 näpunäidet VirtualBox Linuxi masinate ülelaadimiseks

Kas olete väsinud virtuaalmasinate halvast jõudlusest? Siin on, mida peaksite tegema oma VirtualBoxi jõudluse suurendamiseks.

Loe edasi Seotud teemad

Tootlikkus
Arvutustabel
Microsoft Excel
Microsoft Office'i näpunäited

Autori kohta Siis Albright(Avaldatud 506 artiklit)

Dann on sisustrateegia ja turunduskonsultant, kes aitab ettevõtetel nõudlust ja müügivihjeid luua. Samuti peab ta blogi strateegiast ja sisuturundusest saidil dannalbright.com.

Veel Dann Albrightilt

Telli meie uudiskiri

Liituge meie uudiskirjaga, et saada tehnilisi näpunäiteid, ülevaateid, tasuta e -raamatuid ja eksklusiivseid pakkumisi!

Tellimiseks klõpsake siin