Kuinka käyttää Excelin tavoitetta ja ratkaisua tuntemattomien muuttujien ratkaisemiseen

Excel on erittäin kykenevä, kun sinulla on kaikki laskelmissasi tarvitsemasi tiedot.

Mutta eikö olisi hienoa, jos voisi ratkaise tuntemattomia muuttujia ?

Tavoitteenhaun ja Ratkaisulaajennuksen avulla se voi. Ja näytämme kuinka. Lue täydellinen opas siitä, miten voit ratkaista yksittäisen solun tavoitteen etsinnällä tai monimutkaisemman yhtälön Solverilla.

Tavoitteenhaun käyttäminen Excelissä

Tavoitteenhaku on jo rakennettu Exceliin. Se on alla Tiedot -välilehdellä Mitä-jos-analyysi menu:

Tässä esimerkissä käytämme hyvin yksinkertaista numerosarjaa. Meillä on kolmen neljänneksen arvoinen myyntiluku ja vuotuinen tavoite. Voimme käyttää Goal Seek -toimintoa selvittääksemme, mitä numeroita tarvitaan Q4: ssä tavoitteen saavuttamiseksi.

Kuten näette, nykyinen myynti on yhteensä 114 706 yksikköä. Jos haluamme myydä 250 000 vuoden loppuun mennessä, kuinka monta meidän on myytävä Q4: llä? Excelin Goal Seek kertoo meille.

Käytä Goal Seek -toimintoa vaihe vaiheelta seuraavasti:

Klikkaus Tiedot> Mitä jos -analyysi> Tavoitteenhaku . Näet tämän ikkunan:
Laita yhtälön osa 'yhtä suuri' kohtaan Aseta solu ala. Tämä on numero, jonka Excel yrittää optimoida. Meidän tapauksessamme se on myyntilukujemme juokseva summa solussa B5.
Kirjoita tavoitearvo kohtaan Arvostaa ala. Etsimme yhteensä 250 000 yksikköä, joten kirjoitamme tähän kenttään '250 000'.
Kerro Excelille, mitä muuttujaa haluat ratkaista Vaihtamalla solua ala. Haluamme nähdä, mikä on myynnimme Q4: llä. Joten käskemme Excelin ratkaisemaan solun D2. Se näyttää tältä, kun se on valmis lähtemään:
Osuma OK ratkaisemaan tavoitteesi. Kun se näyttää hyvältä, lyö vain OK . Excel ilmoittaa sinulle, kun Tavoitteenhaku on löytänyt ratkaisun.
Klikkaus OK uudelleen ja näet arvon, joka ratkaisee yhtälön valitsemassasi solussa Vaihtamalla solua .

Meidän tapauksessamme ratkaisu on 135 294 yksikköä. Olisimme tietysti voineet vain havaita sen vähentämällä juoksevan summan vuositavoitteesta. Mutta Tavoitteenhakua voidaan käyttää myös solussa, joka siinä on jo dataa . Ja se on hyödyllisempää.

Huomaa, että Excel korvaa aiemmat tiedot. Se on hyvä idea suorita Tavoitteen etsintä kopio tiedoistasi . On myös hyvä tehdä muistiinpano kopioiduista tiedoista, että ne on luotu Goal Seek -toiminnon avulla. Et halua sekoittaa sitä nykyisiin, tarkkoihin tietoihin.

mitä rng tarkoittaa pelaamisessa

Tavoitteenhaku on siis a hyödyllinen Excel -ominaisuus mutta se ei ole niin vaikuttavaa. Katsotaanpa työkalua, joka on paljon mielenkiintoisempi: Solver-apuohjelma.

Mitä Excelin ratkaisija tekee?

Lyhyesti sanottuna Solver on kuin a Tavoitteenhaun monimuuttujaversio . Se vaatii yhden tavoitemuuttujan ja säätää useita muita muuttujia, kunnes se saa haluamasi vastauksen.

Se voi ratkaista luvun maksimiarvon, luvun vähimmäisarvon tai tarkan luvun.

Ja se toimii rajoitteiden rajoissa, joten jos yhtä muuttujaa ei voi muuttaa tai se voi vaihdella vain tietyn alueen sisällä, Solver ottaa sen huomioon.

Se on loistava tapa ratkaista useita tuntemattomia muuttujia Excelissä. Mutta sen löytäminen ja käyttäminen ei ole yksinkertaista.

Katsotaanpa Solver-apuohjelman lataamista ja siirry sitten ratkaisun käyttämiseen Excel 2016: ssa.

Ratkaisulaajennuksen lataaminen

Excelissä ei ole oletuksena Ratkaisua. Se on apuohjelma, joten muiden tehokkaiden Excel-ominaisuuksien tavoin sinun on ladattava se ensin. Onneksi se on jo tietokoneellasi.

Suunnata Tiedosto> Asetukset> Apuohjelmat . Napsauta sitten Mennä vieressä Hallinnoi: Excel-apuohjelmat .

Jos tämä pudotusvalikko sanoo jotain muuta kuin Excel-apuohjelmat, sinun on muutettava se:

Tuloksena olevassa ikkunassa näet muutamia vaihtoehtoja. Varmista, että vieressä oleva ruutu on Ratkaisulaajennus tarkistetaan ja lyö OK .

Näet nyt Ratkaisija -painiketta Analyysi ryhmä Tiedot välilehti:

Jos olet jo käyttänyt Data Analysis Toolpak näet Tietojen analysointi -painikkeen. Jos ei, Ratkaisu tulee näkyviin itsestään.

Nyt kun olet ladannut lisäosan, katsotaanpa, miten sitä käytetään.

Ratkaisun käyttäminen Excelissä

Ratkaisutoiminnassa on kolme osaa: tavoite, muuttujasolut ja rajoitukset. Käymme läpi kaikki vaiheet.

Klikkaus Tiedot> Ratkaisija . Näet Ratkaisuparametrit -ikkunan alla. (Jos et näe ratkaisijapainiketta, katso edellisestä osiosta Ratkaisulaajennuksen lataaminen.)
Aseta solutavoite ja kerro Excelille tavoitteesi. Tavoite on Ratkaisu -ikkunan yläosassa, ja siinä on kaksi osaa: kohdesolu ja vaihtoehto maksimoida, minimoida tai tietty arvo. Jos valitset Max , Excel säätää muuttujasi saadaksesi mahdollisimman suuren määrän tavoitesolusi. Min on päinvastoin: Ratkaisija minimoi objektiiviluvun. Jonkin arvo voit määrittää tietyn numeron, jota Solver etsii.
Valitse muuttujasolut, joita Excel voi muuttaa. Muuttuvat solut asetetaan näppäimellä Vaihtamalla muuttuvia soluja ala. Napsauta kentän vieressä olevaa nuolta ja valitse sitten solut, joiden kanssa Ratkaisijan tulisi toimia, napsauttamalla ja vetämällä. Huomaa, että nämä ovat kaikki solut joka voi vaihdella. Jos et halua solun muuttuvan, älä valitse sitä.
Aseta rajoituksia useille tai yksittäisille muuttujille. Lopuksi pääsemme rajoituksiin. Tässä Solver on todella voimakas. Sen sijaan, että muutat mitä tahansa muuttujasolua haluamaasi numeroon, voit määrittää rajoitukset, jotka on täytettävä. Katso lisätietoja alla olevasta rajoitusten asettamista koskevasta osiosta.
Kun kaikki nämä tiedot ovat paikallaan, paina Ratkaista saadaksesi vastauksesi. Excel päivittää tietosi sisältämään uudet muuttujat (siksi suosittelemme, että luot ensin kopion tiedoistasi).

Voit myös luoda raportteja, joita tarkastelemme lyhyesti alla olevassa Solver -esimerkissämme.

Rajojen asettaminen ratkaisijaan

Voit kertoa Excelille, että yhden muuttujan on oltava suurempi kuin 200. Kun yrität eri muuttuja -arvoja, Excel ei mene alle 201: lle kyseisen muuttujan kanssa.

Jos haluat lisätä rajoituksen, napsauta Lisätä -painiketta pakotusluettelon vieressä. Saat uuden ikkunan. Valitse solu (tai solut), jota haluat rajoittaa Soluviite -kenttään ja valitse sitten operaattori.

Tässä ovat käytettävissä olevat operaattorit:

<= (Pienempi kuin tai yhtä suuri kuin)
= (yhtä kuin)
=> (suurempi tai yhtä suuri)
int (täytyy olla kokonaisluku)
olen (täytyy olla joko 1 tai 0)
Kaikki erilaiset

Kaikki erilaiset on vähän hämmentävää. Se määrittää, että jokainen solu valitsemallasi alueella Soluviite täytyy olla eri numero. Mutta se myös määrittää, että niiden on oltava välillä 1 ja solujen lukumäärä. Joten jos sinulla on kolme solua, päädyt numeroihin 1, 2 ja 3 (mutta ei välttämättä tässä järjestyksessä)

Lisää lopuksi rajoituksen arvo.

On tärkeää muistaa, että voit valitse useita soluja soluviitettä varten. Jos haluat esimerkiksi, että kuuden muuttujan arvot ovat yli 10, voit valita ne kaikki ja kertoa Ratkaisijalle, että niiden on oltava suurempia tai yhtä suuria kuin 11. Sinun ei tarvitse lisätä rajoitusta kullekin solulle.

Voit myös käyttää Ratkaisu-pääikkunan valintaruutua varmistaaksesi, että kaikki arvot, joille et ole määrittänyt rajoituksia, eivät ole negatiivisia. Jos haluat muuttujien muuttuvan negatiivisiksi, poista tämän valintaruudun valinta.

Ratkaisuesimerkki

Nähdäksemme, miten tämä kaikki toimii, käytämme Solver-apuohjelmaa nopean laskelman tekemiseen. Tässä on tiedot, joista aloitamme:

Siinä on viisi erilaista työtä, joista jokainen maksaa eri hinnan. Meillä on myös tuntimäärä, jonka teoreettinen työntekijä on työskennellyt kussakin näistä tehtävistä tietyn viikon aikana. Voimme Solver-apuohjelman avulla selvittää, kuinka maksimoida kokonaispalkka pitäen tietyt muuttujat joidenkin rajoitusten sisällä.

Tässä ovat käyttämämme rajoitukset:

Ei töitä voi laskea alle neljän tunnin.
Työn 2 on oltava yli kahdeksan tuntia .
Työn 5 on oltava alle yksitoista tuntia .
Työtuntien kokonaismäärän on oltava yhtä suuri kuin 40 .

Voi olla hyödyllistä kirjoittaa tällaiset rajoituksesi ennen Solverin käyttöä.

Määritimme sen Solverissa seuraavasti:

Huomaa ensin, että Olen luonut kopion taulukosta joten emme korvaa alkuperäistä, joka sisältää nykyisen työaikamme.

Ja toiseksi, katso, että suurempien ja pienempien rajoitusten arvot ovat yksi korkeampi tai alempi kuin mitä edellä mainitsin. Tämä johtuu siitä, että vaihtoehtoja ei ole enemmän tai vähemmän. On vain suurempia tai yhtä suuria ja pienempiä kuin tai yhtä suuria.

Lyödään Ratkaista ja katso mitä tapahtuu.

Ratkaisija löysi ratkaisun! Kuten yllä olevan ikkunan vasemmalla puolella näkyy, tulomme ovat nousseet 130 dollarilla. Ja kaikki rajoitukset on täytetty.

tilavuus on liian suuri rasvalle32

Jos haluat säilyttää uudet arvot, varmista Säilytä Ratkaisuratkaisu tarkistetaan ja lyödään OK .

Jos haluat lisätietoja, voit kuitenkin valita raportin ikkunan oikeasta reunasta. Valitse kaikki haluamasi raportit, kerro Excelille, haluatko ne hahmotella (suosittelen sitä), ja paina OK .

Raportit luodaan työkirjasi uusille arkeille, ja ne antavat sinulle tietoa prosessista, jonka Solver-apuohjelma kävi vastauksen saamiseksi.

Meidän tapauksessamme raportit eivät ole kovin jännittäviä, eikä siellä ole paljon mielenkiintoista tietoa. Mutta jos suoritat monimutkaisempaa Ratkaisija -yhtälöä, saatat löytää hyödyllisiä raportointitietoja näistä uusista laskentataulukoista. Napsauta vain + -painiketta, jos haluat saada lisätietoja:

Ratkaisun lisäasetukset

Jos et tiedä paljon tilastoista, voit jättää huomiotta Solverin lisäasetukset ja käyttää sitä sellaisenaan. Mutta jos käytät suuria ja monimutkaisia laskelmia, sinun kannattaa ehkä tutkia niitä.

Ilmeisin on ratkaisumenetelmä:

Voit valita GRG Nonlinear, Simplex LP ja Evolutionary. Excel tarjoaa yksinkertaisen selityksen siitä, milloin sinun pitäisi käyttää kutakin. Parempi selitys vaatii jonkin verran tietoa tilastoista ja regressiosta.

Voit säätää lisäasetuksia painamalla vain Asetukset -painiketta. Voit kertoa Excelille kokonaislukuoptimaalisuudesta, asettaa laskenta -aikarajoituksia (hyödyllistä massiivisille tietojoukoille) ja säätää, miten GRG- ja Evolutionary -ratkaisumenetelmät tekevät laskelmiaan.

Jos et tiedä mitä tämä tarkoittaa, älä huoli siitä. Jos haluat tietää enemmän siitä, mitä ratkaisumenetelmää käyttää, Engineer Excel tarjoaa hyvä artikkeli, joka esittelee sen sinulle . Jos haluat maksimaalisen tarkkuuden, Evolutionary on luultavasti hyvä tapa edetä. Huomaa vain, että se kestää kauan.

Tavoitteenhaku ja ratkaisija: Excelin siirtyminen seuraavalle tasolle

Nyt kun olet tyytyväinen Excelin tuntemattomien muuttujien ratkaisemisen perusasioihin, täysin uusi laskentataulukkolaskennan maailma on sinulle avoinna.

Tavoitteenhaku voi auttaa sinua säästämään aikaa tekemällä laskutoimituksia nopeammin, ja Solver lisää siihen valtavasti voimaa Excelin laskentataidot .

Kyse on vain viihtymisestä heidän kanssaan. Mitä enemmän käytät niitä, sitä hyödyllisempiä niistä tulee.

Käytätkö tavoitehakua tai ratkaisijaa laskentataulukoissasi? Mitä muita vinkkejä voit antaa parhaiden vastausten saamiseksi? Jaa ajatuksesi alla oleviin kommentteihin!

Jaa Jaa Tweet Sähköposti 5 vinkkiä VirtualBox Linux -koneiden lataamiseen

Oletko kyllästynyt virtuaalikoneiden heikkoon suorituskykyyn? Tässä on, mitä sinun pitäisi tehdä parantaaksesi VirtualBox -suorituskykyäsi.

Lue seuraava Liittyvät aiheet

Tuottavuus
Laskentataulukko
Microsoft Excel
Microsoft Office -vinkkejä

Kirjailijasta Sitten Albright(506 artikkelia julkaistu)

Dann on sisältöstrategia- ja markkinointikonsultti, joka auttaa yrityksiä luomaan kysyntää ja liidejä. Hän kirjoittaa myös blogia strategiasta ja sisältömarkkinoinnista osoitteessa dannalbright.com.

Lisää Dann Albrightilta

tilaa uutiskirjeemme

Liity uutiskirjeeseemme saadaksesi teknisiä vinkkejä, arvosteluja, ilmaisia e -kirjoja ja ainutlaatuisia tarjouksia!

Klikkaa tästä tilataksesi