Kako se Excel želi tražiti i riješiti za rješavanje nepoznatih varijabli

  • William Charles
  • 0
  • 3913
  • 68
Oglas

Excel je vrlo sposoban kad imate sve potrebne podatke za svoje proračune.

Ali ne bi li bilo lijepo kad bi moglo riješiti za nepoznate varijable?

S traženjem cilja i dodatkom Solver može. A mi ćemo vam pokazati kako. Pročitajte cjeloviti vodič o tome kako riješiti jednu ćeliju s traženjem cilja ili složeniju jednadžbu s Solverom.

Kako se koristi Traženje cilja u Excelu

Traženje cilja već je ugrađeno u Excel. To je ispod Podaci na kartici Što ako analiza izbornik:

Za ovaj primjer, koristit ćemo vrlo jednostavan skup brojeva. Imamo tri četvrtine prodajnih brojeva i godišnji cilj. Pomoću pretraživanja cilja možemo shvatiti koji brojevi trebaju biti u Q4 da bi se postigao cilj.

Kao što vidite, trenutna prodaja ukupno iznosi 114.706 jedinica. Ako želimo prodati 250.000 do kraja godine, koliko nam je potrebno da prodamo u Q4? Excel će nam tražiti cilj.

Evo kako koristiti Traženje cilja, korak po korak:

  1. Klik Podaci> Što ako analiza> Traženje cilja. Vidjet ćete ovaj prozor:
  2. Stavi “jednak” dio vaše jednadžbe u 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. Unesite vrijednost cilja u polje Cijeniti polje. Tražimo ukupno 250.000 prodanih jedinica, pa ćemo staviti “250000” u ovom polju.
  4. Recite Excelu koju varijablu riješiti u Promjenom ćelije polje. Želimo vidjeti kakva je prodaja u Q4. Stoga ćemo reći Excelu da riješi za stanicu D2. Izgledat će ovako kad bude spreman za rad:
  5. Pogoditi u redu riješiti za svoj cilj. Kad dobro izgleda, samo pogodite u redu. Excel će vas obavijestiti kada Goal Seek pronađe rješenje.
  6. Klik u redu ponovo ćete vidjeti 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 jedinica. Naravno, to smo mogli upravo otkriti oduzimanjem ukupnog iznosa od godišnjeg cilja. No, Traženje cilja može se koristiti i na stanici koja već ima podatke u sebi. I to je korisnije.

Imajte na umu da Excel prepisuje naše prethodne podatke. To je dobra ideja pokrenite Traženje cilja na kopiji vaših podataka. Također je dobra ideja zabilježiti na kopiranim podacima koji su nastali pomoću Traženja cilja. Ne želite ga brkati za trenutne, točne podatke.

Dakle, Traženje cilja korisna je značajka Excela 16 Formula Excel koja će vam pomoći u rješavanju stvarnih problema 16 Formule Excela koje će vam pomoći u rješavanju problema iz stvarnog života Pravi alat pola je posla. Excel može riješiti proračune i obrađivati ​​podatke brže nego što možete pronaći svoj kalkulator. Pokazujemo vam ključne formule Excela i pokazujemo kako ih koristiti. , ali nije sve tako impresivno. Pogledajmo alat koji je mnogo zanimljiviji: dodatak Solver.

Što radi Excel-ov Solver?

Ukratko, Solver je poput multivarijantna verzija Traženja cilja. Potrebna je jedna varijabla cilja i prilagođava niz drugih varijabli dok ne dobije odgovor koji želite.

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

I djeluje unutar ograničenja, tako da ako se jedna varijabla ne može promijeniti ili se može mijenjati samo u određenom rasponu, Solver će to uzeti u obzir.

Odličan je način rješavanja više nepoznatih varijabli u Excelu. No, pronalaženje i korištenje nije jednostavno.

Pogledajmo umetanje dodatka Solver, a zatim uskočimo kako koristiti Solver u Excelu 2016.

Kako učitati dodatak Solver

Excel prema zadanim postavkama nema Solver. To je dodatak, kao i drugi moćni Excelovi značajke Power Up Excel s 10 dodataka za obradu, analizu i vizualizaciju podataka poput Pro Power Up Excel-a s 10 dodataka za obradu, analizu i vizualizaciju podataka poput Pro Vanilla Excel-a nevjerojatna je, ali možete je učiniti još snažnijom dodacima. Bez obzira na podatke koje trebate obraditi, vjerovatno je da je netko stvorio Excel aplikaciju za to. Evo izbora. , morate je prvo učitati. Srećom, to je već na vašem računalu.

Kreni prema Datoteka> Opcije> Dodaci. Zatim kliknite na Ići pored Upravljanje: Excel dodaci.

Ako ovo padanje govori nešto drugo osim “Excel dodaci,” morat ćete ga promijeniti:

U rezultirajućem prozoru vidjet ćete nekoliko opcija. Provjerite okvir pokraj Solver dodatak je provjeren i pogodio u redu.

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

Ako ste već koristili Toolpak za analizu podataka Kako napraviti osnovnu analizu podataka u Excelu Kako napraviti osnovnu analizu podataka u Excelu Excel, nije namijenjen analizi podataka, ali on i dalje može obraditi statistiku. Pokazat ćemo vam kako koristiti dodatak Alat za analizu podataka za pokretanje Excel statistika. , vidjet ćete gumb Analiza podataka. Ako ne, Solver će se pojaviti sam.

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

Kako koristiti Solver u Excelu

Svaka Solverova radnja ima tri dijela: cilj, promjenjive ćelije i ograničenja. Proći ćemo kroz svaki od koraka.

  1. Klik Podaci> Solver. Dolje ćete vidjeti prozor Solver Parameters. (Ako ne vidite gumb solver, pogledajte prethodni odjeljak o umetanju dodatka Solver.)
  2. Postavite cilj svoje ćelije i recite Excelu svoj cilj. Cilj se nalazi na vrhu prozora Solver, a ima dva dijela: ćeliju cilja i izbor za maksimiziranje, minimiziranje ili određenu vrijednost..

    Ako odaberete maksimum, Excel će prilagoditi vaše varijable kako bi u vašoj objektivnoj ćeliji dobio najveći mogući broj. min je suprotno: Solver će minimizirati broj cilja. Vrijednost omogućuje vam da odredite određeni broj koji Solver traži.
  3. Odaberite varijabilne ćelije koje Excel može promijeniti. Promjenjive ćelije su postavljene s Promjenom varijabilnih stanica polje. Kliknite strelicu pored polja, a zatim kliknite i povucite za odabir ćelija s kojima Solver treba raditi. Imajte na umu da su to sve ćelije koja može varirati. Ako ne želite da se stanica mijenja, nemojte je odabrati.
  4. Postavite ograničenja za više ili pojedinačnih varijabli. Napokon dolazimo do ograničenja. Ovdje je Solver stvarno moćan. Umjesto da bilo koju varijablu ćelije promijenite u bilo koji broj koji želite, možete odrediti ograničenja koja moraju biti ispunjena. Pojedinosti potražite u odjeljku kako postaviti ograničenja u nastavku.
  5. Jednom kada su sve te informacije na mjestu, pogodite Riješiti da dobijem vaš odgovor. Excel će ažurirati vaše podatke i uključiti nove varijable (zato preporučujemo da prvo stvorite kopiju podataka).

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

Kako postaviti ograničenja u Solveru

Možete reći Excel-u da jedna varijabla mora biti veća 200. Kada isprobava različite vrijednosti varijabli, Excel neće prijeći 201 ispod te varijable.

Da biste dodali ograničenje, kliknite gumb Dodati gumb pokraj popisa ograničenja. Dobit ćete novi prozor. Odaberite ćeliju (ili ćelije) koja će biti ograničena u Referenca stanica a zatim odaberite operatera.

Ovdje su dostupni operateri:

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

AllDifferent malo je zbunjujuće. Pokazuje da je svaka ćelija u rasponu koji ste odabrali Referenca stanica mora biti drugačiji broj. Ali također specificira da moraju biti između 1 i broja stanica. Dakle, ako imate tri ćelije, završit ćete 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 referentne stanice. Na primjer, ako želite da šest varijabli ima vrijednosti iznad 10, možete ih odabrati sve i reći Solveru da moraju biti veće od ili jednake 11. Ne morate dodati ograničenje za svaku ćeliju.

Također možete upotrijebiti potvrdni okvir u glavnom prozoru Solver da provjerite jesu li sve vrijednosti za koje niste odredili ograničenja negativne. Ako želite da vaše varijable postanu negativne, poništite ovaj okvir.

Primjer rješenja

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

U njemu imamo pet različitih poslova od kojih svaki plaća drugačiju stopu. Imamo i broj sati u kojima je teorijski radnik radio na svakom od tih poslova u određenom tjednu. Pomoću dodatka Solver možemo saznati kako maksimizirati ukupnu plaću uz zadržavanje određenih varijabli u okviru nekih ograničenja.

Evo ograničenja koja ćemo koristiti:

  • Nema poslova može pasti ispod četiri sata.
  • Posao 2 mora biti veće od osam sati.
  • Posao 5 mora biti manje od jedanaest sati.
  • Mora biti ukupno odrađenih sati jednaka 40.

Može vam biti korisno napisati svoja takva ograničenja prije upotrebe Solvera.

Evo kako smo to postavili u Solveru:

Prvo, primijetite to Napravio sam kopiju tablice tako da ne prepisujemo izvorni, koji sadrži naše trenutno radno vrijeme.

I drugo, pogledajte da su vrijednosti u većim i manjim ograničenjima jedan viši ili niži nego što sam gore spomenuo. To je zato što ne postoje opcije veće ili manje. Postoje samo veći od-ili-jednaki-i-manje-nego-ili-jednaki.

Udarimo Riješiti i vidjeti što se događa.

Solver je našao rješenje! Kao što možete vidjeti lijevo od prozora iznad, naša zarada povećana je za 130 USD. I sva ograničenja su ispunjena.

Da biste zadržali nove vrijednosti, pobrinite se Zadržite rješenje provjerava i pogodi u redu.

Ako želite više informacija, možete odabrati izvještaj s desne strane prozora. Odaberite sva izvješća koja želite, recite Excel-u da li ih želite izložiti (preporučujem ih) 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 prošao dodatak Solver da biste dobili svoj odgovor.

U našem slučaju, izvještaji nisu baš uzbudljivi, a nema puno zanimljivih podataka. No ako pokrenete složeniju Solverovu jednadžbu, u ovim ćete novim radnim listovima možda pronaći neke korisne informacije o izvještavanju. Samo kliknite gumb + gumb sa bilo kojeg izvješća za dodatne informacije:

Riješite napredne mogućnosti

Ako ne znate puno o statistici, možete zanemariti Solverove napredne opcije i jednostavno je pokrenuti onakvom kakva jest. Ali ako radite velike, složene proračune, možda ćete ih htjeti pogledati.

Najočitija je metoda rješavanja:

Možete birati između GRG-a, nelinearnog, jednostavnog LP-a i evolucijskog. Excel pruža jednostavno objašnjenje u vezi s trenutkom korištenja svakog od njih. Bolje objašnjenje zahtijeva neko znanje o statistici. Naučite statistiku besplatno sa ovih 6 resursa. Saznajte statistiku besplatno sa ovih 6 resursa. Statistika ima reputaciju predmeta koji je teško razumjeti. No, učenje iz pravih resursa pomoći će vam da u bilo kojem trenutku shvatite rezultate ankete, izborna izvješća i vaše zadatke vezane uz statistiku. i regresije.

Za podešavanje dodatnih postavki samo pritisnite tipku Opcije dugme. Možete reći Excel-u o cjelobrojnoj optimalnosti, postaviti vremensko ograničenje izračuna (korisno za ogromne skupove podataka) i prilagoditi način na koji GRG i Evolucijsko rješavanje metoda prolaze u izračunu.

Opet, ako ne znate što bilo što od toga znači, ne brinite se. Ako želite znati više o tome koji način rješavanja koristiti, Engineer Excel ima dobar članak koji vam donosi. Ako želite maksimalnu točnost, Evolucionar je vjerojatno dobar put. Samo budite svjesni da će trebati puno vremena.

Traženje i rješavanje cilja: Prelazak Excela na sljedeću razinu

Sad kad vam je drago s osnovama rješavanja nepoznatih varijabli u Excelu, otvoren vam je potpuno novi svijet proračuna proračunske tablice..

Traženje cilja može vam pomoći uštedjeti vrijeme bržim pravljenjem nekih izračuna, a Solver dodaje veliku količinu energije u Excelove računske sposobnosti Kako izračunati osnovnu statistiku u Excelu: Vodič za početnike Kako izračunati osnovnu statistiku u Excelu: Vodič za početnike Microsoft Excel može napraviti statistiku! Možete izračunati postotke, prosjeke, standardno odstupanje, standardnu ​​pogrešku i studentske T-testove. .

Samo je stvar s njima ugodno. Što ih više upotrebljavate, oni će vam postati korisniji.

Koristite li Traženje cilja ili Solver u svojim proračunskim tablicama? Koje druge savjete možete pružiti za dobivanje najboljih odgovora na njih? Podijelite svoje misli u komentarima u nastavku!




Još ne komentari

O modernoj tehnologiji, jednostavnoj i pristupačnoj.
Vaš vodič u svijetu moderne tehnologije. Naučite kako koristiti tehnologije i uređaje koji nas okružuju svaki dan i naučite kako otkriti zanimljivosti na Internetu.