Izračun većeg broja rezultata pomoću podatkovne tablice

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:

1. 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.

2. 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 želite ispitati učinak različitih vrijednosti na druge formule, unesite dodatne formule u ćelije desno od prve formule.

   • 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.

3. Odaberite raspon ćelija koji sadrži formule i vrijednosti koje želite zamijeniti. Na gornjoj slici taj je raspon C2:D5.

4. Na kartici Podaci kliknite Analiza "što ako" > podatkovnu tablicu (u grupi Alati podataka ili Grupa predviđanja Excel 2016 ). 

5. 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

1. 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.

2. Odaberite raspon ćelija koji sadrži podatkovnu tablicu i novu formulu.

3. Na kartici Podaci kliknite Analiza "što ako" > podatkovnu tablicu (u grupi Alati podataka ili Grupa predviđanja Excel 2016 ).

4. 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:

1. 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 ).

2. 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.

3. Unesite drugi popis u isti redak kao i formula – s desne strane.

   U ćelije D2 i E2 upišite uvjete zajma (u mjesecima).

4. 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.

5. Na kartici Podaci u grupi Alati podataka ili Grupa Predviđanje (u programu Excel 2016 ) kliknite Analiza "što ako" > podatkovnu tablicu (u grupi Alati podataka ili Grupa Predviđanje Excel 2016). 

6. U polje Ulazna ćelija retka unesite referencu na ulaznu ćeliju za ulazne vrijednosti u retku.
   U okvir Ulazna ćelija retka upišite ćeliju B4.

7. U polje Ulazna ćelija stupca unesite referencu na ulaznu ćeliju za ulazne vrijednosti u stupcu.
   U okvir Ulazna ćelija stupca upišite B3.

8. Kliknite 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:

1. Kliknite Mogućnosti > datoteke > formule.

2. U odjeljku Mogućnosti izračuna u odjeljku Izračun kliknite Automatski , osim podatkovnih tablica.

   Savjet: Ako želite, na kartici Formule kliknite strelicu u odjeljku Mogućnosti izračuna, a zatim kliknite Automatske tablice osim podatkovnih tablica (u grupi Izračun).

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.