Podatkovna tablica raspon je ćelija u kojem možete promijeniti vrijednosti u nekim ćelijama i pronaći različite odgovore na problem. Dobar primjer podatkovne tablice koristi funkciju PMT s različitim iznosima kredita i kamatnim stopama za izračun pristupačnog iznosa na home hipotekarni kredit. Eksperimentiranje s različitim vrijednostima za promatranje odgovarajućih varijacija u rezultatima uobičajeni je zadatak u analizi podataka.
Podatkovne tablice u programu Microsoft Excel dio su paketa naredbi koje se nazivaju What-If za analizu. Kada sastavljate i analizirate podatkovne tablice, radite analizu "što ako".
Analiza "što ako" postupak je promjene vrijednosti u ćelijama da biste vidjeli kako će te promjene utjecati na ishod formula na radnom listu. Možete, primjerice, koristiti podatkovnu tablicu da biste promijenili kamatnu stopu i trajanje zajma – da biste procijenili potencijalne mjesečne iznose otplate.
Vrste analize "što ako"
U programu Excel postoje tri vrste alata za analizu "što ako": scenariji, tablice podataka i traženje rješenja. Scenariji i tablice podataka koriste skupove ulaznih vrijednosti za izračun mogućih rezultata. Traženje rješenja jasno se razlikuje, koristi jedan rezultat i izračunava moguće ulazne vrijednosti koje bi proizašlo iz tog rezultata.
Kao i scenariji, podatkovne tablice olakšavaju istraživanje skupa mogućih ishoda. Za razliku od scenarija, podatkovne tablice prikazuju sve ishode u jednoj tablici na jednom radnom listu. Korištenje podatkovnih tablica olakšava pregled širokog raspona mogućnosti na prvi pogled. Budući da se usredotočujete na jednu ili dvije varijable, rezultate je jednostavno pročitati i zajednički koristiti u tabličnom obliku.
Podatkovna tablica ne može sadržavati više od dvije varijable. Ako želite analizirati više od dvije varijable, umjesto toga koristite scenarije. Iako je ograničen na samo jednu ili dvije varijable (jednu za ulaznu ćeliju retka i jednu za ulaznu ćeliju stupca), podatkovna tablica može sadržavati koliko god različitih vrijednosti varijabli želite. Scenarij može imati najviše 32 različite vrijednosti, ali možete stvoriti koliko god scenarija želite.
Saznajte više u članku Uvod u What-If analizu.
Stvorite tablice podataka s jednom varijablom ili dvije varijable, ovisno o broju varijabli i formula koje morate testirati.
Podatkovne tablice s jednom varijablom
Koristite tablicu podataka s jednom varijablom ako želite vidjeti kako će različite vrijednosti jedne varijable u jednoj ili više formula promijeniti rezultate tih formula. Možete, primjerice, koristiti podatkovnu tablicu s jednom varijablom da biste vidjeli kako različite kamatne stope utječu na mjesečnu otplatu hipoteka pomoću funkcije PMT. Vrijednosti varijabli unosite u jedan stupac ili redak, a ishodi se prikazuju u susjednom stupcu ili retku.
Na sljedećoj ilustraciji ćelija D2 sadrži formulu plaćanja =PMT(B3/12;B4;-B5), koja se odnosi na ulaznu ćeliju B3.
Podatkovne tablice s dvije varijable
Pomoću podatkovne tablice s dvije varijable pogledajte kako će različite vrijednosti dviju varijabli u jednoj formuli promijeniti rezultate te formule. Možete, primjerice, koristiti podatkovnu tablicu s dvije varijable da biste vidjeli kako će različite kombinacije kamatnih stopa i uvjeta kredita utjecati na mjesečnu otplatu hipoteka.
Na sljedećoj ilustraciji ćelija C2 sadrži formulu plaćanja =PMT(B3/12;B4;-B5), koja koristi dvije ulazne ćelije, B3 i B4.
Izračuni podatkovne tablice
Svaki put kada se radni list ponovno izračuna, sve će se podatkovne tablice ponovno izračunavati, čak i ako podaci nisu promijenjeni. Da biste ubrzali izračun radnog lista koji sadrži podatkovnu tablicu, možete promijeniti mogućnosti izračuna tako da se radni list automatski ponovno izračunava, ali ne i podatkovne tablice. Dodatne informacije potražite u odjeljku Ubrzavanje izračuna na radnom listu koji sadrži podatkovne tablice.
Podatkovna tablica s jednom varijablom sadrži ulazne vrijednosti u jednom stupcu (usmjerenom stupcu) ili preko retka (usmjereno prema retku). Svaka formula u tablici podataka s jednom varijablom mora se odnositi samo na jednu ulazna ćelija.
Slijedite ove korake:
-
Upišite popis vrijednosti koje želite zamijeniti u ulaznu ćeliju – jedan stupac ili jedan redak prema dolje. Ostavite nekoliko praznih redaka i stupaca na bilo kojoj strani vrijednosti.
-
Napravite nešto od sljedećeg:
-
Ako je podatkovna tablica usmjerena na stupac ( varijable se nalaze u stupcu), formulu upišite u ćeliju jedan redak iznad i jednu ćeliju desno od stupca vrijednosti. Ova je podatkovna tablica s jednom varijablom usmjerena na stupac, a formula se nalazi u ćeliji D2.
-
Ako je podatkovna tablica usmjerena na redak ( varijable su u retku), upišite formulu u ćeliju jedan stupac lijevo od prve vrijednosti i jednu ćeliju ispod retka vrijednosti.Ako želite ispitati učinak različitih vrijednosti na druge formule, unesite dodatne formule u ćelije ispod prve formule.
-
-
Odaberite raspon ćelija koji sadrži formule i vrijednosti koje želite zamijeniti. Na gornjoj slici taj je raspon C2:D5.
-
Na kartici Podaci odaberite Analiza "što ako" > podatkovnu tablicu (u grupi Alati za podatke ili Grupa Predviđanje Excel 2016 ).
-
Napravite nešto od sljedećeg:
-
Ako je podatkovna tablica usmjerena na stupac, unesite adresa ćelije za ulaznu ćeliju u polje ulazne ćelije Stupac . Na gornjoj slici ulazna je ćelija B3.
-
Ako je podatkovna tablica usmjerena prema retku, unesite referencu ćelije za ulaznu ćeliju u polje ulazne ćelije Redak.
Napomena: Kada stvorite podatkovnu tablicu, možda ćete htjeti promijeniti oblik ćelija s rezultatima. Na slici ćelije s rezultatima oblikuju se kao valuta.
-
Formule koje se koriste u podatkovnoj tablici s jednom varijablom moraju se odnositi na istu ulaznu ćeliju.
Učinite sljedeće
-
Učinite nešto od sljedećeg:
-
Ako je podatkovna tablica usmjerena na stupac, unesite novu formulu u praznu ćeliju desno od postojeće formule u gornjem retku podatkovne tablice.
-
Ako je podatkovna tablica usmjerena na redak, unesite novu formulu u praznu ćeliju ispod postojeće formule u prvom stupcu podatkovne tablice.
-
-
Odaberite raspon ćelija koji sadrži podatkovnu tablicu i novu formulu.
-
Na kartici Podaci odaberite Analiza "što ako" > podatkovnu tablicu (u grupi Alati podataka ili Grupa Predviđanje Excel 2016 ).
-
Učinite nešto od sljedećeg:
-
Ako je podatkovna tablica usmjerena na stupac, unesite referencu ćelije za ulaznu ćeliju u okvir Ulazna ćelija stupca .
-
Ako je podatkovna tablica usmjerena na redak, unesite referencu ćelije za ulaznu ćeliju u okvir Ulazna ćelija redaka.
-
Podatkovna tablica s dvije varijable koristi formulu koja sadrži dva popisa ulaznih vrijednosti. Formula se mora odnositi na dvije različite ulazne ćelije.
Slijedite ove korake:
-
U ćeliju na radnom listu unesite formulu koja se odnosi na dvije ulazne ćelije.
U sljedećem primjeru , u kojem se početne vrijednosti formule unose u ćelije B3, B4 i B5, u ćeliju C2 upišite formulu =PMT(B3/12;B4;-B5 ).
-
Ispod formule upišite jedan popis ulaznih vrijednosti u isti stupac.
U tom slučaju upišite različite kamatne stope u ćelije C3, C4 i C5.
-
Unesite drugi popis u isti redak kao i formula – s desne strane.
U ćelije D2 i E2 upišite uvjete zajma (u mjesecima).
-
Odaberite raspon ćelija koji sadrži formulu (C2), i redak i stupac vrijednosti (C3:C5 i D2:E2) i ćelije u kojima želite izračunatih vrijednosti (D3:E5).
U tom slučaju odaberite raspon C2:E5.
-
Na kartici Podaci u grupi Alati podataka ili Grupa Predviđanje (u programu Excel 2016 ) odaberite Analiza "Što ako" > Podatkovna tablica (u grupi Alati podataka ili Grupa Predviđanje Excel 2016).
-
U polje Ulazna ćelija retka unesite referencu na ulaznu ćeliju za ulazne vrijednosti u retku.U okvir Ulazna ćelija retka upišite ćeliju B4.
-
U polje Ulazna ćelija stupca unesite referencu na ulaznu ćeliju za ulazne vrijednosti u stupcu.U okvir Ulazna ćelija stupca upišite B3.
-
Odaberite U redu.
Primjer tablice podataka s dvije varijable
Podatkovna tablica s dvije varijable može pokazati kako će različite kombinacije kamatnih stopa i uvjeta kredita utjecati na mjesečnu otplatu hipoteka. Na slici ovdje ćelija C2 sadrži formulu plaćanja =PMT(B3/12;B4;-B5), koja koristi dvije ulazne ćelije, B3 i B4.
Kada postavite tu mogućnost izračuna, pri ponovnom izračunu u cijeloj radnoj knjizi neće doći do izračuna podatkovne tablice. Da biste ručno ponovno izračunali podatkovnu tablicu, odaberite njezine formule, a zatim pritisnite F9.
Slijedite ove korake da biste poboljšali performanse izračuna:
-
Odaberite Mogućnosti > datoteka > formule.
-
U odjeljku Mogućnosti izračuna odaberite Automatski.
Savjet: Ako želite, na kartici Formule odaberite strelicu na izborniku Mogućnosti izračuna, a zatim odaberite Automatski.
Ako imate određene ciljeve ili veće skupove varijabilnih podataka, možete koristiti nekoliko drugih alata programa Excel za analizu "što ako".
Traženje rješenja
Ako znate rezultat koji možete očekivati od formule, ali ne znate točno koju ulaznu vrijednost formula treba za dobivanje tog rezultata, koristite značajku Goal-Seek rezultata. Pogledajte članak Korištenje značajke traženja rješenja da biste pronašli željeni rezultat prilagodbom ulazne vrijednosti.
Alat za rješavanje programa Excel
Pomoću dodatka rješavača programa Excel možete pronaći optimalnu vrijednost za skup ulaznih varijabli. Alat za rješavanje funkcionira s grupom ćelija (koje se nazivaju varijablama odlučivanja ili jednostavno varijabilnim ćelijama) koje se koriste u izračunu formula u ciljnim ćelijama i ćelijama ograničenja. Alat za rješavanje prilagođava vrijednosti u ćelijama varijabli odlučivanja tako da zadovoljava ograničenja ćelija ograničenja i daje željeni rezultat za ciljnu ćeliju. Saznajte više u ovom članku: Definiranje i rješavanje problema pomoću alata za rješavanje.
Uključivanjem različitih brojeva u ćeliju možete brzo pronaći različite odgovore na problem. Odličan primjer je korištenje funkcije PMT s različitim kamatnim stopama i razdobljima kredita (u mjesecima) da biste saznali koliko kredita možete priuštiti za kuću ili automobil. Brojeve unosite u raspon ćelija pod nazivom podatkovna tablica.
Ovdje je podatkovna tablica raspon ćelija B2:D8. Vrijednost u ćeliji B4, iznos kredita i mjesečne uplate u stupcu D možete automatski ažurirati. Koristeći kamatnu stopu od 3,75%, D2 vraća mjesečnu uplatu od 1042,01 USD pomoću ove formule: =PMT(C2/12;$B$3,$B$4).
Možete koristiti jednu ili dvije varijable, ovisno o broju varijabli i formula koje želite testirati.
Upotrijebite test s jednom varijablom da biste vidjeli kako će različite vrijednosti jedne varijable u formuli promijeniti rezultate. Možete, primjerice, promijeniti kamatnu stopu za mjesečnu otplatu hipotekom pomoću funkcije PMT. Vrijednosti varijabli (kamatne stope) unosite u jedan stupac ili redak, a rezultati se prikazuju u obližnjem stupcu ili retku.
U ovoj live radnoj knjizi ćelija D2 sadrži formulu plaćanja =PMT(C2/12;$B$3,$B$4). Ćelija B3 varijabilna je ćelija u kojoj možete priključiti drugu duljinu razdoblja (broj mjesečnih razdoblja plaćanja). U ćeliji D2 funkcija PMT priključava kamatnu stopu od 3,75%/12, 360 mjeseci i zajam od 225 000 USD i izračunava 1 042,01 USD mjesečno plaćanje.
Upotrijebite test s dvije varijable da biste vidjeli kako će različite vrijednosti dviju varijabli u formuli promijeniti rezultate. Možete, primjerice, testirati različite kombinacije kamatnih stopa i broj mjesečnih razdoblja otplate da biste izračunali otplatu hipotekom.
U ovoj live radnoj knjizi ćelija C3 sadrži formulu za plaćanje =PMT($B$3/12,$B$2,B4), koja koristi dvije varijabilne ćelije, B2 i B3. U ćeliji C2 funkcija PMT priključava kamatnu stopu od 3,875%/12, 360 mjeseci i zajam od 225 000 USD i izračunava mjesečnu uplatu od 1 058,03 USD.
Je li vam potrebna dodatna pomoć?
Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.
