Korištenje Data Table mogućnosti u programu Microsoft Excel

Program Microsoft Excel nastao je 1985. godine u verziji za Macintosh, a na PC računalima se pojavio dvije godine kasnije. Od 1993. predstavlja standard na području programa za tablične kalkulacije (spreadsheet), a taj položaj je zadržao i danas. Premda postoje programi koji mu predstavljaju konkurenciju (spomenimo samo OpenOffice Calc, dio besplatnog uredskog paketa OpenOffice), čim korisnik zatreba neku složeniju funkcionalnost jedini pravi izbor je Excel. Razloga za to ima više, a među najvažnije možemo ubrojiti veliki broj ugrađenih funkcija i postupaka, kompatibilnost s ostalim programima iz Microsoft Office paketa te mogućnost dodavanja korisnički definiranih funkcija i proširenje funkcionalnosti pisanjem programa u VBA programskom jeziku.

Kako je već napisano, unutar Excela je ugrađeno mnogo funkcionalnosti i malo je korisnika koji poznaju sve mogućnosti. No, važno  je znati što se u Excelu može napraviti, a kada se ukaže potreba, korisnik će već naći načina da napravi što želi.

Data Table

Data Table je jedna od mogućnosti Excela koju korisnici razmjerno rijetko koriste, a može im značajno olakšati život u određenim situacijama.  Data Table se može koristiti kao jedno-parametarska tablica, odnosno dvo-parametarska tablica, a glavna prednost je što korisnik ne treba voditi računa o načinu adresiranja ćelija.  Upotreba mogućnosti Data Table prikazat će se na jednom primjeru.

Jedno-parametarski Data Table

Primjer: Potrebno je izračunati koliki će biti godišnji anuitet (rata) zajma u iznosu 100.000, podignutog uz godišnju kamatnu stopu 5%. Kako iznos rate zajma ovisi o duljini razdoblja u kojem se zajam vraća, zanima nas iznos anuiteta za slučajeve u kojima je rok otplate zajma 5, 10, 15, 20, 25 i 30 godina. Osim toga, potrebno je naći i ukupnu kamatu koju će korisnik platiti, te odnos (u postotcima) između ukupne kamate i iznosa zajma.

Iznos anuiteta ovisi iznosu zajma, kamatnoj stopi i roku otplate zajma. Od spomenute tri veličine, nas zanima iznos anuiteta uz fiksiran iznos zajma i kamatnu stopu, dok se broj godine mijenja.

U tom smislu prirediti ćemo radni list na način prikazan na slici 1.

Slika 1. Priprema radnog lista za jedno-parametarski Data Table

Iznos zajma i kamatna stopa su izdvojeni kao posebni parametri što nam omogućuje bolje razumijevanje sadržaja koji je prikazan u tablici.
Idući korak je da upišemo formule koje će nam biti potrebne da napravimo traženi izračun.
U ćeliju E2 upisat ćemo formulu za računanje anuiteta: =ABS(PMT(B2;B3;B1))
U izračunu je korištena je funkcija PMT (PMT stoji za payment) koja računa iznos anuiteta za zadanu kamatnu stopu, broj anuiteta koji treba platiti te iznosu zajma, a moguće je još dodati i ostatak vrijednost na kraju razdoblja otplate te vrijeme obračuna kamata. U svom najjednostavnijem obliku, sintaksa funkcije PMT je
                              =PMT( kamatna stopa; broj anuiteta koje treba platiti; iznos zajma)

Također, U izračunu u ćeliji E2  koristi se funkcija ABS (apsolutna vrijednost broja) jer PMT vraća negativnu vrijednost (smjer kapitala!).
U ćeliji F2 nalazi se izračun ukupne kamate, kao razlike između nominalne vrijednosti vraćenog i posuđenog novca, dok se u ćeliji G2 nalazi omjer ukupne kamate i iznosa zajma.

Sada možemo iskoristiti Data Table:

Postupak je sljedeći:

• Označimo ćelije za Data Table (na slici 1 – ćelije od D2 do G7).
• Data > What-If Analysis > Data Table … .
• U dijalogu koji se pojavi, upišite pod Column Input Cell ćeliju D2 (dovoljno je kliknuti na ćeliju D2). Ovim je zapravo rečeno da će se u trećem retku naći iste formule kao u drugom retku, s jedinom razlikom što se umjesto ćelije D2 koristi ćelija D3, u četvrtom retku se umjesto D2 koristi vrijednost iz ćelije D4 itd.

Rezultat korištenja Data Table prikazan je na slici 2.

Slika 2. Rezultat primjene jedno-parametarske DataTable

Promjena parametara u ćelijama B1 i B2 uzrokuje promjenu vrijednosti u tablici. Također, promjena formule u drugom retku (ćelijama E2, F2 ili G2) automatski se odražava na preostale ćelije u stupcu u kojem je napravljena formula.

Postoje i drugačiji načini korištenja jedno-parametarskog Data Table, ali prostor nam ne dozvoljava daljnja objašnjenja.

Dvo-parametarski Data Table

Osim jedno-parametarskog, moguće je koristiti i dvo-parametarski Data Table. Pretpostavimo da želimo vidjeti za zadani iznos zajma kako se mijenja iznos anuiteta za različite rokove otplate zajma (5, 10, 15, 20, 25 i 30 godina) i različite iznose kamatne stope (recimo 3%, 4%, 5%, 6%, 7% i 8%).

Krećemo slično kao u prethodnom slučaju: navedemo parametre i napravimo obris tablice (Slika 3).

Slika 3. Priprema radnog lista za dvo-parametarski Data Table

Primijetite da smo sada kao parametre postavili i godišnju kamatnu stopu i rok otplate zajma.  Nadalje, u ćeliju D1 (presjek prvog stupca i prvog retka tablice) upišemo formulu za izračun anuiteta.

Konačno, slično kao i za jedno-parametarski Data Table, radimo sljedeće korake:

• Označimo ćelije za Data Table (na slici 3 - ćelije D1 do J7).
• Data > What-If Analysis > Data Table … .
• U dijalogu koji se pojavi, upišite pod Row Input Cell ćeliju B2, a za Column Input Cell ćeliju B3. Ovim je zapravo rečeno da će se prilikom primjene formule u nekoj ćeliji tablice, umjesto sadržaja ćelije B2 koristiti vrijednost ćelije koja se nalazi u prvom redu tablice (među ćelijama E1 – J1), dok će se umjesto sadržaja ćelije B3 koristiti vrijednost ćelije koja se nalazi u prvom stupcu tablice (tj. ćelijama D2 – D7).

Rezultat postupka je prikazan na slici 4.

Slika 4. Rezultat primjene dvo-parametarske Data Table

Daljnje razmatranja prelaze okvire ovog članka, navedimo samo da promjene ćelija B2 i B3 ne utječu na tablicu, dok promjena formule u ćeliji D1, kao i sadržaja ćelije B2, sadržaja ćelija E1 – J1 odnosno D2 – D7, rezultira promjenom sadržaja odgovarajućih ćelija u tablici.

I na kraju …

Korištenjem Data Table korisnik može dobiti lijepe tablične izračune bez potrebe za poznavanjem načina na koji Excel adresira ćelije. Također, moguće je i složene izračune prikazati u jednostavnoj tablici. S druge strane, ako korisnika ne zanimaju vrijednosti za sve kombinacije parametara, već samo neke, onda je bolji izbor korištenje Excelove mogućnosti upravljanja scenarijima (Scenario Manager).

mr. sc. Ivo Beroš

Ako se želite upoznati s različitim mogućnostima primjene Excela, prijavite se na CERTIFICIRANI EXCEL® PROGRAM koji počinje 16.09.2020. ili na pojedinačne module programa (temeljni, napredni, ekspertni), a više detaljniji sadržaj programa i pojedinačnim modulima možete pronaći ovdje.