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

U programu Microsoft Excel podatkovne tablice dio su paketa naredbi poznatih kao alati za analizu „što ako”. Kada stvarate i analizirate podatkovne tablice, provodite analizu „što ako”.

Analiza „što ako” postupak je promjene vrijednosti u ćelijama radi utvrđivanja kako te promjene utječu na rezultat formula na radnom listu. Na primjer, podatkovnu tablicu možete upotrijebiti za variranje kamatne stope i trajanja zajma kako biste procijenili potencijalne mjesečne iznose rate.

Vrste analize „što ako”    

Tri su vrste alata za analizu „što ako” u programu Excel: scenariji, podatkovne tablicei traženje rješenja. Scenariji i podatkovne tablice upotrebljavaju skupove ulaznih vrijednosti za izračun mogućih rezultata. Značajka Traženje rješenja mnogo je drugačija; upotrebljava jedan rezultat i izračunava moguće ulazne vrijednosti koje bi rezultirale tim rezultatom.

Kao i scenariji, podatkovne tablice pomažu vam istražiti skup mogućih rezultata. Za razliku od scenarija, podatkovne tablice prikazuju sve rezultate u jednoj tablici na jednom radnom listu. Upotreba podatkovnih tablica omogućuje brz pregled niza mogućnosti. Budući da se usredotočujete na jednu ili dvije varijable, rezultate je jednostavno pročitati i zajednički koristiti u tabličnom obliku.

U podatkovnoj tablici nije moguće upotrijebiti više od dvije varijable. Ako želite analizirati više od dvije varijable, trebali biste upotrijebiti scenarije. Iako je ograničena na samo jednu ili dvije varijable (jednu za ulaznu ćeliju retka i jednu za ulaznu ćeliju stupca), podatkovna tablica može obuhvaćati onoliko različitih vrijednosti varijabli koliko želite. Scenarij može sadržavati najviše 32 različite vrijednosti, ali možete stvoriti scenarija koliko želite.

Saznajte više u članku Uvod u analizu „što ako”.

Stvorite podatkovne tablice s jednom ili dvije varijable, ovisno o broju varijabli i formula koje morate testirati.

Podatkovne tablice s jednom varijablom    

Podatkovnu tablicu s jednom varijablom upotrijebite ako želite vidjeti kako različite vrijednosti jedne varijable u jednoj ili više formula mijenjaju rezultate tih formula. Na primjer, podatkovnu tablicu s jednom varijablom možete upotrijebiti kako biste vidjeli utjecaj različitih kamatnih stopa na mjesečno rate hipoteke pomoću funkcije PMT. Vrijednosti varijabli unosite u jedan stupac ili redak, a rezultati se prikazuju u susjednom stupcu ili retku.

Na sljedećoj ilustraciji ćelija D2 sadrži formulu za ratu, =PMT(B3/12,B4,-B5), koja se odnosi na ulaznu ćeliju B3.

Podatkovne tablice s dvije varijable    

Pomoću podatkovne tablice s dvije varijable možete vidjeti kako različite vrijednosti dviju varijabli u jednoj formuli mijenjaju rezultate te formule. Na primjer, podatkovnu tablicu s dvije varijable možete upotrijebiti kako biste vidjeli utjecaj različitih kombinacija kamatnih stopa i trajanja zajma na mjesečne rate hipoteke.

Na sljedećoj ilustraciji ćelija C2 sadrži formulu za ratu, =PMT(B3/12,B4,-B5), koja upotrebljava dvije ulazne ćelije, B3 i B4.

 

Izračuni podatkovne tablice    

Svaki put kada se radni list ponovo izračuna, ponovo će se izračunati i sve podatkovne tablice, čak i ako nema promjena u podacima. Da biste ubrzali izračun radnog lista koji sadrži podatkovnu tablicu, možete promijeniti mogućnosti izračuna za automatsko ponovno izračunavanje radnog lista, ali ne i podatkovnih tablica. Dodatne informacije potražite u sekciji Ubrzavanje izračuna na radnom listu koji sadrži podatkovne tablice.

Podatkovna tablica s jednom varijablom sadrži ulazne vrijednosti u jednom stupcu (usmjerena na stupac) ili u retku (usmjerena na redak). Sve formule u podatkovnoj tablici s jednom varijablom smiju se pozivati samo na jednu ulazna ćelija.

Slijedite ove korake:

1. Unesite popis vrijednosti koje želite zamijeniti u ulaznoj ćeliji – u stupcu ili retku. 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 (vrijednosti varijable nalaze se u stupcu), unesite formulu u ćeliju jedan redak iznad i jednu ćeliju desno od stupca vrijednosti. Ova podatkovna tablica s jednom varijablom usmjerena je na stupac, a formula se nalazi u ćeliji D2.
     
     
     
     Ako želite ispitati učinke različitih vrijednosti na druge formule, unesite dodatne formule u ćelije desno od prve formule.

   • Ako je podatkovna tablica usmjerena na redak (vrijednosti varijable nalaze se u retku), unesite formulu u ćeliju jedan stupac lijevo od prve vrijednosti i jednu ćeliju ispod retka vrijednosti.
     
     Ako želite ispitati učinke 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 odaberite Analiza „što ako” >Podatkovna tablica (u grupi Alati podataka ili grupi Predviđanje za Excel 2016). 

5. Napravite nešto od sljedećeg:

   • Ako je podatkovna tablica usmjerena na stupac, unesite adresa ćelije za ulaznu ćeliju u polje Ulazna ćelija stupca. Na gornjoj slici ulazna je ćelija B3.

   • Ako je podatkovna tablica usmjerena na redak, unesite referencu ćelije za ulaznu ćeliju u polje Ulazna ćelija retka.

     Napomena: Kada stvorite podatkovnu tablicu, možda ćete morati promijeniti oblik ćelija s rezultatima. Na slici su ćelije rezultata oblikovane kao valuta.

Formule koje se upotrebljavaju u podatkovnoj tablici s jednom varijablom moraju se pozivati na istu ulaznu ćeliju.

Učinite sljedeće

1. Učinite nešto od ovog:

   • Ako je podatkovna tablica usmjerena na stupac, novu formulu unesite u praznu ćeliju s desne strane postojeće formule u gornjem retku podatkovne tablice.

   • Ako je podatkovna tablica usmjerena na redak, novu formulu unesite 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 odaberite Analiza „što ako”> Podatkovna tablica (u grupi Alati podataka ili grupi Predviđanje za 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 retka.

Podatkovna tablica s dvije varijable upotrebljava formulu koja sadrži dva popisa ulaznih vrijednosti. Formula se mora pozivati na dvije različite ulazne ćelije.

Slijedite ove korake:

1. U ćeliju na radnom listu unesite formulu koja se poziva 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 unesite formulu =PMT(B3/12;B4;-B5).

2. Unesite jedan popis ulaznih vrijednosti u isti stupac ispod formule.

   U ovom slučaju unesite različite kamatne stope u ćelije C3, C4 i C5.

3. Drugi popis unesite u isti redak kao i formulu – s desne strane.

   Unesite trajanje zajma (u mjesecima) u ćelije D2 i E2.

4. Odaberite raspon ćelija koji sadrži formulu (C2), redak i stupac vrijednosti (C3:C5 i D2:E2) i ćelije u kojima želite izračunate vrijednosti (D3:E5).

   U ovom slučaju odaberite raspon C2:E5.

5. Na kartici Podaci, u grupi Alati za podatke ili grupi Predviđanje (u Excel 2016), odaberite Analiza „što ako” > Podatkovna tablica (u grupi Alati za podatke ili grupi Predviđanje za Excel 2016).

6. U polju Ulazna ćelija retka unesite referencu na ulaznu ćeliju za ulazne vrijednosti u retku.
   Unesite ćelija B4 u okvir Ulazna ćelija retka.

7. U polju Ulazna ćelija stupca unesite referencu na ulaznu ćeliju za ulazne vrijednosti u stupcu.
   Unesite B3 u okvir Ulazna ćelija stupca.

8. Odaberite U redu.

Primjer podatkovne tablice s dvije varijable

Podatkovna tablica s dvije varijable može pokazati kako različite kombinacije kamatnih stopa i trajanja zajma utječu na mjesečne rate hipoteke. Na ovoj slici ćelija C2 sadrži formulu za ratu, =PMT(B3/12,B4,-B5), koja upotrebljava dvije ulazne ćelije, B3 i B4.

Kada postavite ovu mogućnost izračuna, pri ponovnom izračunu cijele radne knjige neće se izračunavati podatkovne tablice. Da biste ručno ponovo izračunali podatkovnu tablicu, odaberite formule, a zatim pritisnite F9.

Slijedite ove korake za poboljšanje performansi izračuna:

1. Odaberite Datoteka > Mogućnosti > Formule.

2. U sekciji Mogućnosti izračuna odaberite Automatski.

   Savjet: Ako želite, na kartici Formule odaberite strelicu u Mogućnostima izračuna, a zatim odaberite Automatski.

Ako imate određene ciljeve ili veće skupove varijabilnih podataka, možete upotrijebiti nekoliko drugih alata programa Excel za analizu „što ako”.

Traženje rješenja

Ako znate koji rezultat očekivati od formule, ali niste sigurni koju ulaznu vrijednost trebate unijeti, upotrijebite značajku Traženje rješenja. Pogledajte članak Upotreba značajke Traženje rješenja za pronalaženje željenog rezultata prilagodbom ulazne vrijednosti.

Excel Solver

Pomoću dodatka Excel Solver možete pronaći optimalnu vrijednost za skup ulaznih varijabli. Solver funkcionira s grupom ćelija (koje se nazivaju varijablama odlučivanja ili jednostavno ćelijama varijabli) koje se upotrebljavaju u izračunu formula u ćelijama cilja i ograničenja. Solver prilagođava vrijednosti u ćelijama varijabli odlučivanja radi zadovoljavanja ograničenja ćelija ograničenja i vraća željeni rezultat za ćeliju cilja. Saznajte više u ovom članku: Definiranje i rješavanje problema pomoću dodatka Solver.