Tabela podataka je opseg ćelija u kojem možete da promenite vrednosti u nekim ćelijama i da dobijete različite odgovore na problem. Dobar primer tabele podataka koristi funkciju PMT sa različitim iznosima kredita i kamatnim stopama za izračunavanje pristupačnog iznosa kredita za hipoteku. Eksperimentisanje sa različitim vrednostima za posmatranje odgovarajuće varijante u rezultatima uobičajen je zadatak u analizi podataka.
U programu Microsoft Excel tabele podataka su deo paketa komandi poznatih kao What-If alatke za analizu. Kada pravite i analizirate tabele podataka, radite analizu šta-ako.
Analiza šta-ako je proces promene vrednosti u ćelijama da bi se videlo kako će te promene uticati na ishod formula na radnom listu. Na primer, tabelu podataka možete da koristite za menjanje kamatne stope i dužine trajanja kredita – za procenu potencijalnih mesečnih iznosa plaćanja.
Napomena: Možete da izvršite brža izračunavanja sa tabelama podataka i programom Visual Basic for Applications (VBA). Više informacija potražite u članku Excel What-If podataka: Brže izračunavanje pomoću VBA.
Tipovi analize šta-ako
Postoje tri tipa alatki za analizu šta-ako u programu Excel: scenariji, tabele podataka i traženje cilja. Scenariji i tabele podataka koriste skupove ulaznih vrednosti za izračunavanje mogućih rezultata. Traženje cilja se posebno razlikuje, koristi jedan rezultat i izračunava moguće ulazne vrednosti koje bi dale taj rezultat.
Poput scenarija, tabele podataka vam pomažu da istražite skup mogućih ishoda. Za razliku od scenarija, tabele podataka vam pokazuju sve rezultate u jednoj tabeli na jednom radnom listu. Korišćenje tabela podataka olakšava da za tren oka ispitate opseg mogućnosti. Pošto se fokusirate samo na jednu ili dve promenljive, rezultati se lako čitaju i dele u tabelarnom obliku.
Tabela podataka ne može da sadrži više od dve promenljive. Ako želite da analizirate više od dve promenljive, trebalo bi da koristite scenarije. Iako je ograničena na samo jednu ili dve promenljive (jednu za ulaznu ćeliju reda, a drugu za ulaznu ćeliju kolone), tabela podataka može da sadrži koliko god želite različitih promenljivih vrednosti. Scenario može da ima najviše 32 različite vrednosti, ali možete da kreirate koliko god želite scenarija.
Saznajte više u članku Uvod u What-If Analiza.
Kreirajte tabele sa podacima sa jednom promenljivom ili dve promenljive, u zavisnosti od broja promenljivih i formula koje treba da testirate.
Tabele sa podacima sa jednom promenljivom
Koristite tabelu sa podacima sa jednom promenljivom ako želite da vidite kako će različite vrednosti jedne promenljive u jednoj ili više formula promeniti rezultate tih formula. Na primer, možete da koristite tabelu sa podacima sa jednom promenljivom da biste videli kako različite kamatne stope utiču na mesečnu ratu hipoteke pomoću funkcije PMT. Promenljive vrednosti unosite u jednu kolonu ili red, a rezultati se prikazuju u susednoj koloni ili redu.
Na sledećoj ilustraciji ćelija D2 sadrži formulu plaćanja =PMT(B3/12,B4,-B5) koja upućuje na ulaznu ćeliju B3.
Tabele sa podacima sa dve promenljive
Koristite tabelu sa podacima sa dve promenljive da biste videli kako će različite vrednosti dve promenljive u jednoj formuli promeniti rezultate te formule. Na primer, možete da koristite tabelu sa podacima sa dve promenljive da biste videli kako će različite kombinacije kamatnih stopa i uslova kredita uticati na mesečnu ratu hipoteke.
Na sledećoj ilustraciji, ćelija C2 sadrži formulu plaćanja =PMT(B3/12,B4,-B5) koja koristi dve ulazne ćelije, B3 i B4.
Izračunavanja tabele podataka
Svaki put kada se radni list ponovo izračuna, sve tabele podataka će se takođe ponovo izračunati – čak i ako nije bilo promene podataka. Da biste ubrzali izračunavanje radnog lista koji sadrži tabelu podataka, možete da promenite opcije izračunavanja da biste automatski ponovo izračunali radni list, ali ne i tabele podataka. Da biste saznali više, pogledajte odeljak Ubrzavanje izračunavanja na radnom listu koji sadrži tabele podataka.
Tabela sa podacima sa jednom promenljivom sadrži ulazne vrednosti u jednoj koloni (orijentisanu na kolonu) ili u redu (orijentisane na red). Svaka formula u tabeli sa podacima sa jednom promenljivom mora da se odnosi samo na ulazna ćelija.
Sledite ove korake:
-
Otkucajte listu vrednosti koje želite da zamenite u ulaznoj ćeliji – nadole za jednu kolonu ili u jednom redu. Ostavite nekoliko praznih redova i kolona sa obe strane vrednosti.
-
Uradite nešto od sledećeg:
-
Ako je tabela podataka orijentisana na kolonu ( promenljive vrednosti su u koloni), otkucajte formulu u ćeliji jedan red iznad i jednu ćeliju desno od kolone sa vrednostima. Ova tabela sa podacima sa jednom promenljivom je orijentisana na kolone, a formula se nalazi u ćeliji D2.
-
Ako je tabela podataka orijentisana na red ( promenljive vrednosti su u redu), otkucajte formulu u ćeliji jednu kolonu sa leve strane prve vrednosti i jednu ćeliju ispod reda vrednosti.Ako želite da ispitate efekte različitih vrednosti na druge formule, unesite dodatne formule u ćelije ispod prve formule.
-
-
Izaberite opseg ćelija koji sadrži formule i vrednosti koje želite da zamenite. Na slici iznad, ovaj opseg je C2:D5.
-
Na kartici Podaci izaberite stavku Analiza šta-ako >podataka (u grupi Alatke za podatke ili grupi Predviđanje Excel 2016 ).
-
Uradite nešto od sledećeg:
-
Ako je tabela podataka orijentisana na kolonu, unesite referenca ćelije ulaznu ćeliju u polje Ulazna ćelija kolone . Na slici iznad, ulazna ćelija je B3.
-
Ako je tabela podataka orijentisana na red, unesite referencu ćelije za ulaznu ćeliju u polje ulazne ćelije Red .
Napomena: Kada kreirate tabelu sa podacima, možda ćete želeti da promenite oblikovanje ćelija rezultata. Na slici ćelije rezultata oblikovane su kao valuta.
-
Formule koje se koriste u tabeli podataka sa jednom promenljivom moraju da se odnose na istu ulaznu ćeliju.
Pratite ove korake
-
Uradite nešto od sledećeg:
-
Ako je tabela podataka orijentisana na kolone, unesite novu formulu u praznu ćeliju sa desne strane postojeće formule u gornjem redu tabele podataka.
-
Ako je tabela podataka orijentisana na red, unesite novu formulu u praznu ćeliju ispod postojeće formule u prvoj koloni tabele sa podacima.
-
-
Izaberite opseg ćelija koji sadrži tabelu podataka i novu formulu.
-
Na kartici Podaci izaberite stavku Analiza šta-ako > podataka (u grupi Alatke za podatke ili grupi Predviđanje Excel 2016 ).
-
Uradite nešto od sledećeg:
-
Ako je tabela podataka orijentisana na kolonu, unesite referencu ćelije za ulaznu ćeliju u polje Ulazna ćelija kolone .
-
Ako je tabela podataka orijentisana na red, unesite referencu ćelije za ulaznu ćeliju u polje Ulazna ćelija reda .
-
Tabela sa podacima sa dve promenljive koristi formulu koja sadrži dve liste ulaznih vrednosti. Formula mora da upućuje na dve različite ulazne ćelije.
Sledite ove korake:
-
U ćeliju na radnom listu unesite formulu koja upućuje na dve ulazne ćelije.
U sledećem primeru – u koji se unose početne vrednosti formule u ćelije B3, B4 i B5, otkucaćete formulu =PMT(B3/12,B4,-B5) u ćeliji C2.
-
Otkucajte jednu listu ulaznih vrednosti u istoj koloni, ispod formule.
U ovom slučaju otkucajte različite kamatne stope u ćelijama C3, C4 i C5.
-
Unesite drugu listu u isti red kao formula – sa njene desne strane.
Otkucajte uslove kredita (u mesecima) u ćelijama D2 i E2.
-
Izaberite opseg ćelija koji sadrži formulu (C2), red i kolonu vrednosti (C3:C5 i D2:E2) i ćelije u kojima želite da izračunate vrednosti (D3:E5).
U ovom slučaju, izaberite opseg C2:E5.
-
Na kartici Podaci, u grupi Alatke za podatke ili Predviđanje (u programu Excel 2016 ) izaberite stavku Analiza šta-ako > Tabela podataka (u grupi Alatke za podatke ili Excel 2016 ).
-
U polje ulazne ćelije Reda unesite referencu na ulaznu ćeliju za ulazne vrednosti u redu.Otkucajte ćeliju B4 u polje Ulazna ćelija reda.
-
U polje ulazne ćelije kolone unesite referencu na ulaznu ćeliju za ulazne vrednosti u koloni.Otkucajte B3 u polju Ulazna ćelija kolone.
-
Kliknite na dugme U redu.
Example of a two-variable data table
Tabela sa podacima sa dve promenljive može da pokazuje kako će različite kombinacije kamatnih stopa i uslova kredita uticati na mesečnu ratu hipoteke. Na slici ovde, ćelija C2 sadrži formulu plaćanja =PMT(B3/12,B4,-B5) koja koristi dve ulazne ćelije, B3 i B4.
Kada podesite ovu opciju izračunavanja, ne dolazi do izračunavanja tabele podataka kada se izvrši ponovno izračunavanje u celoj radnoj svesci. Da biste ručno ponovo izračunali tabelu podataka, izaberite njene formule, a zatim pritisnite taster F9.
Pratite ove korake da biste poboljšali performanse izračunavanja:
-
Izaberite stavku Opcije > datoteke > formulama.
-
U odeljku Opcije izračunavanja , u okviru Izračunavanje izaberite stavku Automatsko osim za tabele podataka.
Savet: Opcionalno, na kartici Formule kliknite na strelicu na kartici Opcije izračunavanja , a zatim izaberite stavku Automatske izuzev tabela podataka (u grupi Izračunavanje ).
Možete da koristite još nekoliko Excel alatki da biste izvršili analizu šta-ako ako imate određene ciljeve ili veće skupove promenljivih podataka.
Traženje cilja
Koristite funkciju Goal-Seek ako znate rezultat koji treba očekivati od formule. Pogledajte članak Korišćenje traženja cilja za pronalaženje željenog rezultata prilagođavanjem ulazne vrednosti.
Excel Solver
Programski dodatak Excel Solver možete da koristite da biste pronašli optimalnu vrednost za skup ulaznih promenljivih. Rešavač radi sa grupom ćelija (koje se nazivaju promenljive odluke ili jednostavno promenljive ćelije) koje se koriste za izračunavanje formula u ćelijama cilja i ograničenja. Rešavač prilagođava vrednosti u promenljivim ćelijama odluke da bi zadovoljio ograničenje u ćeliji ograničenja i dao željeni rezultat za ćeliju cilja. Saznajte više u ovom članku: Definisanje i rešavanje problema pomoću programskog dodatka "Rešavač".
Priključivanje različitih brojeva u ćeliju brzo možete da pronađete različite odgovore na problem. Odličan primer je korišćenje funkcije PMT sa različitim kamatnim stopama i periodima kredita (u mesecima) da biste izračunali koliko kredita možete da priuštite za kuću ili automobil. Brojeve unosite u opseg ćelija koji se naziva tabela podataka.
Ovde tabela podataka predstavlja opseg ćelija B2:D8. Možete da promenite vrednost u ćeliji B4, iznos kredita i mesečne rate u koloni D koje se automatski ažuriraju. Koristeći kamatnu stopu od 3,75%, D2 daje mesečnu ratu od 1.042,01 USD koristeći ovu formulu: =PMT(C2/12,$B$3,$B$4).
Možete da koristite jednu ili dve promenljive, u zavisnosti od broja promenljivih i formula koje želite da testirate.
Koristite test sa jednom promenljivom da biste videli kako će različite vrednosti jedne promenljive u formuli promeniti rezultate. Na primer, možete da promenite kamatnu stopu za mesečnu ratu hipoteke pomoću funkcije PMT. Promenljive vrednosti (kamatne stope) unosite u jednu kolonu ili red, a rezultati se prikazuju u obližnjoj koloni ili redu.
U ovoj radnoj svesci uživo, ćelija D2 sadrži formulu plaćanja =PMT(C2/12,$B$3,$B$4). Ćelija B3 je promenljiva ćelija u kojoj možete da uključite različitu dužinu perioda (broj mesečnih perioda plaćanja). U ćeliji D2 funkcija PMT dodaje kamatnu stopu od 3,75%/12, 360 meseci i kredit od 225.000 USD i izračunava mesečnu ratu od 1.042,01 USD.
Koristite test sa dve promenljive da biste videli kako će različite vrednosti dve promenljive u formuli promeniti rezultate. Na primer, možete da testirate različite kombinacije kamatnih stopa i broja mesečnih perioda otplate da biste izračunali ratu hipoteke.
U ovoj radnoj svesci uživo, ćelija C3 sadrži formulu plaćanja =PMT($B$3/12,$B$2,B4) koja koristi dve promenljive ćelije, B2 i B3. U ćeliji C2 funkcija PMT dodaje kamatnu stopu od 3,875%/12, 360 meseci i kredit od 225.000 USD i izračunava mesečnu ratu od 1.058,03 USD.
Potrebna vam je dodatna pomoć?
Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u Zajednicama.
