Kako koristiti Excel -ovo traženje i rješavanje ciljeva za rješavanje nepoznatih varijabli

Kako koristiti Excel -ovo traženje i rješavanje ciljeva za rješavanje nepoznatih varijabli

Excel je vrlo sposoban ako imate sve podatke koji su vam potrebni za izračune.





Ali ne bi bilo lijepo da može riješiti nepoznate varijable ?



Uz Goal Seek i dodatak Solver to može. A mi ćemo vam pokazati kako. Čitajte dalje za potpuni vodič o tome kako riješiti jednu ćeliju s ciljem traženja cilja ili složeniju jednadžbu s rješavačem.





Kako koristiti traženje cilja u Excelu

Cilj je već ugrađen u Excel. To je ispod Podaci karticu, u Što-ako analiza Jelovnik:

U ovom primjeru koristit ćemo vrlo jednostavan skup brojeva. Imamo tri četvrtine vrijednosti prodaje i godišnji cilj. Možemo upotrijebiti Traženje cilja da shvatimo koje brojke trebaju biti u četvrtom tromjesečju da bi se postigao cilj.



Kao što vidite, trenutna ukupna prodaja je 114.706 jedinica. Ako želimo prodati 250.000 do kraja godine, koliko ih moramo prodati u četvrtom tromjesečju? Excel će nam reći Traženje cilja.

Evo kako korak po korak koristiti Traženje cilja:



  1. Klik Podaci> Što ako analiza> Traženje cilja . Vidjet ćete ovaj prozor:
  2. Stavite dio jednakosti u jednadžbu Postavi ćeliju polje. Ovo je broj koji će Excel pokušati optimizirati. U našem slučaju, to je ukupni broj naših prodajnih brojeva u ćeliji B5.
  3. Upišite vrijednost cilja u Cijeniti polje. Tražimo ukupno 250.000 prodanih jedinica, pa ćemo u ovo polje staviti '250.000'.
  4. Recite Excelu koju varijablu treba riješiti u Promjenom ćelije polje. Želimo vidjeti kakva bi trebala biti naša prodaja u četvrtom tromjesečju. Reći ćemo Excelu da riješi za ćeliju D2. To će izgledati ovako kad bude spremno za rad:
  5. Pogoditi u redu riješiti za svoj cilj. Kad dobro izgleda, samo udri u redu . Excel će vas obavijestiti kada Goal Seek pronađe rješenje.
  6. Klik u redu opet i vidjet ćete vrijednost koja rješava vašu jednadžbu u ćeliji za koju ste odabrali Promjenom ćelije .

U našem slučaju rješenje je 135.294 jedinice. Naravno, to smo mogli jednostavno otkriti oduzimanjem tekućeg zbroja od godišnjeg cilja. No, Goal Seek se također može koristiti na ćeliji koja već ima podatke u sebi . I to je korisnije.

Imajte na umu da Excel briše naše prethodne podatke. To je dobra ideja pokrenite Traženje cilja na kopiji svojih podataka . Također je dobra ideja zabilježiti vaše kopirane podatke da su oni generirani pomoću cilja. Ne želite ga zamijeniti za trenutne, točne podatke.



kako obrisati SSD uređaj

Dakle, Traženje cilja je a korisna značajka programa Excel , ali nije sve tako impresivno. Pogledajmo alat koji je mnogo zanimljiviji: dodatak Solver.

Što radi Excelov rješavač?

Ukratko, Solver je poput a više varijabilne verzije cilja . Potrebna je jedna varijabla cilja i prilagođava niz drugih varijabli dok ne dobije željeni odgovor.

Može riješiti najveću vrijednost broja, minimalnu vrijednost broja ili točan broj.

I radi unutar ograničenja, pa ako se jedna varijabla ne može promijeniti ili može varirati samo unutar određenog raspona, Solver će to uzeti u obzir.

To je sjajan način za rješavanje više nepoznatih varijabli u Excelu. Ali pronaći i koristiti ga nije jednostavno.

Pogledajmo učitavanje dodatka Solver, a zatim skočimo u to kako koristiti Solver u programu Excel 2016.

Kako učitati programski dodatak Solver

Excel prema zadanim postavkama nema Solver. To je dodatak pa ga, kao i druge moćne značajke programa Excel, morate prvo učitati. Srećom, već je na vašem računalu.

Krenite na Datoteka> Opcije> Dodaci . Zatim kliknite na Ići pored Upravljanje: programski dodaci programa Excel .

Ako ovaj padajući izbornik govori nešto drugo osim 'Dodaci programa Excel', morat ćete to promijeniti:

U rezultirajućem prozoru vidjet ćete nekoliko opcija. Provjerite okvir pored Dodatak Solver je provjereno i pritisnuto u redu .

Sada ćete vidjeti Rješavač gumb u Analiza grupa od Podaci kartica:

Ako ste već koristili Paket alata za analizu podataka , vidjet ćete gumb Analiza podataka. Ako ne, Solver će se pojaviti sam po sebi.

Sada kada ste učitali dodatak, pogledajmo kako ga koristiti.

Kako koristiti Solver u Excelu

Postoje tri dijela svake akcije Solver: cilj, varijabilne ćelije i ograničenja. Proći ćemo kroz svaki korak.

  1. Klik Podaci> Riješivač . U nastavku ćete vidjeti prozor Parametri rješavača. (Ako ne vidite gumb za rješavanje, pogledajte prethodni odjeljak o tome kako učitati dodatak Riješivač.)
  2. Postavite cilj svoje ćelije i recite Excelu svoj cilj. Cilj je na vrhu prozora rješavača i ima dva dijela: ćeliju cilja i izbor povećavanja, minimiziranja ili određene vrijednosti. Ako odaberete Maks , Excel će prilagoditi vaše varijable kako bi dobio najveći mogući broj u vašoj ćeliji cilja. Min je suprotno: Solver će minimizirati objektivni broj. Vrijednost omogućuje vam da navedete određeni broj koji će Solver tražiti.
  3. Odaberite varijabilne ćelije koje Excel može promijeniti. Promjenljive ćelije postavljene su s Promjenom promjenjivih ćelija polje. Pritisnite strelicu pokraj polja, a zatim kliknite i povucite da biste odabrali ćelije s kojima bi Solver trebao raditi. Imajte na umu da su to sve ćelije koje mogu varirati. Ako ne želite da se ćelija promijeni, nemojte je odabrati.
  4. Postavite ograničenja na više ili pojedinačne varijable. Konačno, dolazimo do ograničenja. Ovdje je Solver zaista moćan. Umjesto da promijenite bilo koju varijabilnu ćeliju na bilo koji broj koji želite, možete navesti ograničenja koja morate zadovoljiti. Za pojedinosti pogledajte dolje odjeljak o postavljanju ograničenja.
  5. Kad sve ove informacije budu na svom mjestu, pritisnite Riješiti da dobijem vaš odgovor. Excel će ažurirati vaše podatke uključivanjem novih varijabli (zato vam preporučujemo da prvo stvorite kopiju svojih podataka).

Također možete generirati izvješća, koja ćemo ukratko pogledati u našem primjeru Solvera u nastavku.

Kako postaviti ograničenja u programu Solver

Excelu biste mogli reći da jedna varijabla mora biti veća od 200. Prilikom isprobavanja različitih vrijednosti varijabli Excel neće ići ispod 201 s tom varijablom.

Da biste dodali ograničenje, kliknite na Dodati gumb pored popisa ograničenja. Dobit ćete novi prozor. Odaberite ćeliju (ili ćelije) za ograničenje u Referenca ćelije polje, a zatim odaberite operatora.

Ovdje su dostupni operatori:

  • <= (manje ili jednako)
  • = (jednak)
  • => (veće ili jednako)
  • int (mora biti cijeli broj)
  • am (mora biti 1 ili 0)
  • AllDifferent

AllDifferent je malo zbunjujuće. Određuje da svaka ćelija u rasponu za koji odaberete Referenca ćelije mora biti drugačiji broj. Ali također navodi da moraju biti između 1 i broja ćelija. Dakle, ako imate tri ćelije, završit ćete s brojevima 1, 2 i 3 (ali ne nužno tim redoslijedom)

Na kraju, dodajte vrijednost ograničenja.

Važno je zapamtiti da možete odaberite više ćelija za referencu stanica. Na primjer, ako želite da šest varijabli ima vrijednosti veće od 10, možete ih sve odabrati i reći Solveru da moraju biti veće ili jednake 11. Ne morate dodavati ograničenje za svaku ćeliju.

Također možete upotrijebiti potvrdni okvir u glavnom prozoru Solvera kako biste bili sigurni da su sve vrijednosti za koje niste naveli ograničenja negativne. Ako želite da vaše varijable postanu negativne, poništite ovaj okvir.

Primjer rješavača

Da bismo vidjeli kako sve ovo funkcionira, upotrijebit ćemo dodatak Solver za brzi izračun. Evo podataka s kojima počinjemo:

U njemu imamo pet različitih poslova, od kojih svaki plaća različitu stopu. Imamo i broj sati koje je teoretski radnik odradio na svakom od tih poslova u određenom tjednu. Dodatak Solver možemo upotrijebiti da saznamo kako povećati ukupnu plaću uz zadržavanje određenih varijabli unutar nekih ograničenja.

Evo ograničenja koja ćemo koristiti:

  • Nema posla može pasti ispod četiri sata.
  • Posao 2 mora biti duže od osam sati .
  • Posao 5 mora biti manje od jedanaest sati .
  • Ukupni radni sati moraju biti jednako 40 .

Moglo bi biti od pomoći da ovako napišete svoja ograničenja prije korištenja Solvera.

Evo kako bismo to postavili u Solveru:

Prvo, imajte na umu da Napravio sam kopiju tablice kako ne bismo prepisali izvorni koji sadrži naše trenutno radno vrijeme.

I drugo, pogledajte da su vrijednosti u ograničenjima veće od i manje jedan viši ili niži nego što sam gore spomenuo. To je zato što nema mogućnosti veće ili manje od. Postoje samo veći ili jednaki i manji od ili jednaki.

Udarimo Riješiti i vidjeti što će se dogoditi.

Solver je pronašao rješenje! Kao što vidite lijevo od gornjeg prozora, naša zarada porasla je za 130 USD. I sva ograničenja su ispunjena.

je li sigurno izbrisati hiberfil.sys

Kako biste zadržali nove vrijednosti, pobrinite se Zadržite Solver Solution provjerava se i pogađa u redu .

Ako želite više informacija, možete odabrati izvješće s desne strane prozora. Odaberite sva izvješća koja želite, recite Excelu želite li ih ocrtati (preporučujem) i pritisnite u redu .

Izvješća se generiraju na novim listovima u vašoj radnoj knjizi i daju vam informacije o procesu kroz koji je programski dodatak Solver prošao kako bi dobio vaš odgovor.

U našem slučaju, izvješća nisu baš uzbudljiva i tu nema puno zanimljivih informacija. No, ako pokrenete složeniju jednadžbu rješavača, možda ćete u ovim novim radnim listovima pronaći neke korisne informacije o izvješćivanju. Samo kliknite na + gumb sa strane bilo kojeg izvješća za više informacija:

Solver Napredne opcije

Ako ne znate mnogo o statistici, možete zanemariti napredne opcije Solvera i jednostavno ga pokrenuti kakav jest. No, ako vodite velike i složene izračune, možda ćete ih htjeti proučiti.

Najočitiji je način rješavanja:

Možete birati između GRG Nonlinear, Simplex LP i Evolutionary. Excel nudi jednostavno objašnjenje kada biste trebali koristiti svaki od njih. Za bolje objašnjenje potrebno je poznavanje statistike i regresije.

Da biste prilagodili dodatne postavke, samo pritisnite Opcije dugme. Excelu možete reći o optimalnosti cijelog broja, postaviti vremenska ograničenja izračuna (korisna za masovne skupove podataka) i prilagoditi način na koji metode rješavanja GRG -a i Evolucije rade svoje izračune.

Opet, ako ne znate što ovo znači, ne brinite se zbog toga. Ako želite znati više o tome koju metodu rješavanja koristiti, Inženjer Excel ima dobar članak koji vam to iznosi . Ako želite maksimalnu točnost, Evolutionary je vjerojatno dobar način za to. Samo budite svjesni da će to potrajati dugo.

Traženje i rješavanje ciljeva: podizanje Excela na sljedeću razinu

Sada kada ste upoznati s osnovama rješavanja nepoznatih varijabli u Excelu, otvoren vam je potpuno novi svijet proračuna proračunskih tablica.

Goal Seek može vam pomoći uštedjeti vrijeme ubrzavajući neke izračune, a Solver dodaje ogromnu snagu Excel -ove računske sposobnosti .

Bitno je samo ugoditi s njima. Što ih više koristite, postat će korisnije.

Koristite li Traženje cilja ili Riješivač u svojim proračunskim tablicama? Koje druge savjete možete dati kako biste iz njih izvukli najbolje odgovore? Podijelite svoja razmišljanja u komentarima ispod!

Udio Udio Cvrkut E -pošta 5 savjeta za nadopunu vaših VirtualBox Linux strojeva

Umorni ste od loših performansi koje nude virtualni strojevi? Evo što biste trebali učiniti kako biste povećali performanse VirtualBox -a.

Pročitajte Dalje Povezane teme
  • Produktivnost
  • Proračunska tablica
  • Microsoft Excel
  • Savjeti za Microsoft Office
O autoru Zatim Albright(Objavljeno 506 članaka)

Dann je konzultant za strategiju sadržaja i marketing koji pomaže tvrtkama u stvaranju potražnje i potencijalnih klijenata. Također piše o strategiji i sadržajnom marketingu na dannalbright.com.

Više od Dann Albright

Pretplatite se na naše obavijesti

Pridružite se našem biltenu za tehničke savjete, recenzije, besplatne e -knjige i ekskluzivne ponude!

Kliknite ovdje za pretplatu