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

Podatkovna tablica je raspon ćelija u kojima možete promijeniti vrijednosti u nekim ćelijama i smisliti različite odgovore na problem. Dobar primjer podatkovne tablice koristi funkciju PMT s raznim količinama kredita i kamatnim stopama da bi izračunao pristupačne iznose na kredit za kućnu hipoteku. Eksperimentiranje s drugačijim vrijednostima radi promatranja odgovarajuće varijacije u rezultatima jest uobičajen zadatak u analizi podataka.

U programu Microsoft Excel podatkovne tablice su dio paketa naredbi poznatih kao Alati za analizu "što ako". Kada konstrukcijski i analizirate podatkovne tablice, radite analizu što-ako.

Analiza što-if postupak je promjene vrijednosti u ćelijama da bi se prikazalo kako će te promjene utjecati na ishod formula na radnom listu. Možete, primjerice, pomoću podatkovne tablice razlikovati kamatnu stopu i duljinu termina za kredit – da biste ocijenili potencijalne mjesečne uplate.

Napomena: Brže izračunavati možete izvoditi uz podatkovne tablice i Visual Basic for Applications (VBA). Dodatne informacije potražite u odjeljku podatkovne tablice programa Excel: brži izračun s VBA.

Vrste analiza što-if    

Postoje tri vrste alata za analizu što-ako u programu Excel: scenariji, podatkovne tablicei traženje ciljeva. Scenariji i podatkovne tablice koriste skupove ulaznih vrijednosti da bi izračunali moguće rezultate. Traženje ciljeva izrazito je različito, koristi jedan rezultat i izračunava moguće ulazne vrijednosti koje bi rezultirala rezultatom.

Primjeri scenarija, podatkovne tablice omogućuju vam da istražite skup 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 detaljno ispitivanje raspona 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.

Podatkovna tablica ne može primiti više od dvije varijable. Ako želite analizirati više od dvije varijable, umjesto toga trebali biste koristiti scenarije. Iako je ograničena samo na jednu ili dvije varijable (jedna za ulaznu ćeliju retka i jednu za ulaznu ćeliju stupca), podatkovna tablica može uvrstiti koliko god različitih vrijednosti varijabli želite. Scenarij može imati najviše 32 različitih vrijednosti, no možete stvoriti onoliko scenarija koliko god želite.

Dodatne informacije potražite u članku Uvod u analizu što-if.

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

Podatkovne tablice s jednom varijablom    

Pomoću podatkovne tablice s jednom varijablom želite vidjeti kako će promjene 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 razne kamatne stope utječu na mjesečnu uplatu hipoteke pomoću funkcije PMT. Vrijednosti varijable unosite u jedan stupac ili redak, a rezultati se prikazuju u susjednom stupcu ili retku.

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

Podatkovna tablica s jednom varijablom

Podatkovne tablice s dvije varijable    

Pomoću podatkovne tablice s dvije varijable možete vidjeti kako će razne vrijednosti dviju varijabli u jednoj formuli promijeniti rezultate te formule. Pomoću podatkovne tablice s dvije varijable možete, primjerice, vidjeti kako će razne kombinacije kamatnih stopa i uvjeta kredita utjecati na mjesečnu uplatu hipoteke.

U sljedećoj ilustraciji ćelija C2 sadrži formulu za plaćanje, = PMT (B3/12, B4,-B5), koja koristi dvije ulazne ćelije, B3 i B4.

Data table with two variables
 

Proračuni podatkovne tablice    

Svaki put kada se radni list ponovno izračunava, sve podatkovne tablice također će se ponovno izračunati – čak i ako se podaci ne promijene. Da biste ubrzali izračun radnog lista koji sadrži podatkovnu tablicu, možete promijeniti mogućnosti izračuna da biste automatski ponovno izračunali radni list, ali ne i podatkovne tablice. Dodatne informacije potražite u odjeljku ubrzanje izračuna na radnom listu koji sadrži podatkovne tablice.

Podatkovna tablica s jednom varijablom sadrži ulazne vrijednosti u jednom stupcu (orijentirana prema stupcu) ili preko retka (orijentiran prema retku). Bilo koja formula u podatkovnoj tablici s jednom varijablom mora se odnositi na samo jednu ulazna ćelija.

Slijedite ove upute:

  1. Upišite popis vrijednosti koje želite zamijeniti u ulaznim ćelijama – bilo prema dolje u jednom stupcu ili u jednom retku. Ostavite nekoliko praznih redaka i stupaca na obje strane vrijednosti.

  2. Učinite nešto od sljedećeg:

    • Ako je podatkovna tablica orijentirana na stupac (vrijednosti varijable nalaze se u stupcu), upišite formulu u ćeliju jedan redak iznad i jednu ćeliju desno od stupca vrijednosti. Podatkovna tablica s jednom varijablom usmjerena je na stupce, a formula se nalazi u ćeliji D2.

      Podatkovna tablica s jednom varijablom

      Ako želite ispitati efekte različitih vrijednosti u drugim formulama, unesite dodatne formule u ćelije s desne strane prve formule.

    • Ako je podatkovna tablica orijentirana na retke (vrijednosti varijable su u retku), upišite formulu u ćeliju jedan stupac s desne strane prve vrijednosti i jednu ćeliju ispod retka vrijednosti.

      Ako želite ispitati efekte različitih vrijednosti u drugim formulama, unesite dodatne formule u ćelije ispod prve formule.

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

  4. Na kartici Podaci kliknite Analiza >podatkovne tablice (u grupi alati za podatke ili u grupi predviđanja Excel 2016 ). 

  5. Učinite nešto od sljedećeg:

    • Ako je podatkovna tablica orijentirana na stupac, unesite adresa ćelije za ulaznu ćeliju u polje ulazne ćelije stupca . Na gornjoj slici ulazni broj je B3.

    • Ako je podatkovna tablica orijentirana na redak, unesite referencu ćelije za ulaznu ćeliju u polje ulazne ćelije retka .

      Napomena: Nakon stvaranja podatkovne tablice možda želite promijeniti oblik ćelija rezultata. Na slici su ćelije rezultata oblikovane 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 bilo što od sljedećeg:

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

    • Ako je podatkovna tablica orijentirana 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> podatkovne tablice (u grupi alati za podatke ili u grupi predviđanjaExcel 2016 ).

  4. Učinite nešto od sljedećeg:

    • Ako je podatkovna tablica orijentirana na stupac, unesite referencu ćelije za ulaznu ćeliju u okvir ulazne ćelije stupca .

    • Ako je podatkovna tablica orijentirana na redak, unesite referencu ćelije za ulaznu ćeliju u okvir ulazne ćelije retka .

Podatkovna tablica s dvije varijable koristi formulu koja sadrži dva popisa ulaznih vrijednosti. Formula se mora odnositi na dvije vrste ulaznih ćelija.

Slijedite ove upute:

  1. U ćeliju na radnom listu unesite formulu koja se odnosi na dvije ulazne ćelije.

    U sljedećem primjeru – u kojem su početne vrijednosti formule unesene u ćelije B3, B4 i B5, upišite formulu = PMT (B3/12, B4,-B5) u ćeliju C2.

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

    U ovom slučaju upišite razne kamatne stope u ćelije C3, C4 i C5.

  3. Unesite drugi popis u istom retku kao i formula – s desne strane.

    Unesite uvjete kredita (u mjesecima) u ćelijama 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čunati vrijednosti (D3: E5).

    U ovom slučaju odaberite raspon C2: E5.

  5. Na kartici Podaci u grupi Alati za podatke ili u grupi predviđanja (u Excel 2016 ) kliknite Analiza >podatkovne tablice (u grupi Alati za podatke ili u grupi predviđanja Excel 2016 ). 

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

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

  8. Kliknite U redu.

Primjer podatkovne tablice s dvije varijable

Podatkovna tablica s dvije varijable može prikazati kako će različita kombinacije kamatnih stopa i uvjeta kredita utjecati na mjesečnu uplatu hipoteke. Na slici ovdje ćelija C2 sadrži formulu za plaćanje, = PMT (B3/12, B4,-B5), koja koristi dvije ulazne ćelije, B3 i B4.

Data table with two variables

Kada postavite tu mogućnost izračuna, u cijeloj radnoj knjizi neće doći do izračuna podatkovne tablice. Da biste ručno ponovno izračunali podatkovnu tablicu, odaberite njegove formule, a zatim pritisnite F9.

Da biste povećali performanse izračuna, slijedite ove korake:

  1. Učinite nešto od sljedećeg:

    • U Excel 2007 kliknite gumb Microsoft Office slika gumba , zatim Mogućnosti programa Excel, a potom kategoriju formule .

    • U svim ostalim verzijama kliknite mogućnosti datoteka > > formule.

  2. U odjeljku Mogućnosti izračuna u odjeljku Izračunkliknite automatski, osim za podatkovne tablice.

    Savjet: Po želji, na kartici formule kliknite strelicu na mogućnostima izračuna, a zatim kliknite automatski osim podatkovnih tablica (u grupi Izračun ).

Ako imate konkretne ciljeve ili veće skupove podataka, možete koristiti nekoliko drugih alata za Excel da biste izvršili analizu što-ako.

Traženje rješenja

Ako znate rezultat koji očekujete od formule, ali ne znate točno koja je vrijednost ulazne vrijednosti koju formula mora dobiti, koristite značajku traženja ciljeva. Pogledajte članak Korištenje značajke traženja ciljeva da biste pronašli željeni rezultat prilagođavanjem ulazne vrijednosti.

Alat za rješavanje programa Excel

Pomoću dodatka za rješavača programa Excel možete pronaći optimalnu vrijednost za skup ulaznih varijabli. Rješavatelj funkcionira s grupom ćelija (nazivnih varijabli odluke ili jednostavno promjenjivim ćelijama) koje se koriste u računalstvu formula u objektivnim i ograničenjima ćelija. Rješavatelj prilagođava vrijednosti u stanicama rješenja varijablu da bi zadovoljio ograničenja na ćelijama ograničenja i producirao željeni rezultat za ciljnu ćeliju. Dodatne informacije u ovom članku: Definiranje i rješavanje problema pomoću alata za rješavanje.

Uključivanjem raznih brojeva u ćeliju možete brzo naići na razne odgovore na problem. Odličan primjer koristi funkciju PMT s raznim kamatnim stopama i razdobljima kredita (u mjesecima) da biste shvatili koliko kredita možete priuštiti za kućnu ili auto. U raspon ćelija koje se nazivaju podatkovnom tablicom unosite brojeve.

Ovdje je podatkovna tablica raspon ćelija B2: D8. Vrijednost u obliku B4, iznos kredita i mjesečne uplate u stupcu D automatski se ažuriraju. Pomoću kamatne stope 3,75%, D2 vraća mjesečnu uplatu od $1.042,01 pomoću ove formule: = PMT (C2/12, $B $3, $B $4).

Ovaj raspon ćelija – B2:D8 – podatkovna je tablica

Možete koristiti jednu ili dvije varijable, ovisno o broju varijabli i formulama koje želite testirati.

Pomoću testa s jednom varijablom Pogledajte kako će promjene vrijednosti jedne varijable u formuli promijeniti rezultate. Možete, primjerice, promijeniti kamatnu stopu za mjesečnu uplatu hipoteke pomoću funkcije PMT. Vrijednosti varijable (kamatne stope) unosite u jedan stupac ili redak, a rezultati se prikazuju u obližnjem stupcu ili retku.

U ovoj radnoj knjizi, ćelija D2 sadrži formulu za plaćanje =PMT (C2/12, $B $3, $B $4). Ćelija B3 je varijabilna ćelija u kojoj možete priključiti različitu duljinu termina (broj mjesečnih razdoblja plaćanja). U ćeliji D2, funkcija PMT prikazuje se u kamatnoj stopi od 3.75%/12, 360 mjeseci i $225.000 kredit te izračunava $1.042,01 mjesečne uplate.

Koristite dva varijabilna testa da biste vidjeli kako će razne vrijednosti dviju varijabli u formuli promijeniti rezultate. Možete, primjerice, testirati razne kombinacije kamatnih stopa i broja mjesečnih razdoblja otplate da biste izračunali uplatu hipoteke.

U ovoj radnoj knjizi, ćelija C3 sadrži formulu za uplatu, =PMT ($B $3/12, $B $2, B4), koja koristi dvije varijable ćelije, B2 i B3. U ćeliji C2 funkcija PMT prikazuje se u kamatnoj stopi 3.875%/12, 360 mjeseci i $225.000 kredit te izračunava $1.058,03 mjesečne uplate.

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Napomena:  Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Jesu li vam te informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.​

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×