1. Uvod
  2. Osnovni pojmi
    1. Celica
      1. Vrednost (ali vsebina) celice
      2. Naslavljanje celic
        1. Relativno naslavljanje
        2. Absolutno naslavljanje
        3. Reference na celice na drugih listih
        4. Reference na celice drugih datotek
      3. Oblika celice 
    2. Formule, funkcije in konstante
  3. Dogodkovno gnano programiranje
  4. Izdelava preglednice varčevanja
  5. Izdelava grafa

Povzetek


Preglednice so, podobno kot druga orodja, obstajale mnogo pred programskimi orodji, kot jih poznamo in uporabljamo danes. Preglednice so uporabljala podjetja za lepši in razvidnejši prikaz poslovanja, računovodstva in podobnih dejavnosti, ki zahtevajo veliko količino finančnih podatkov za skupni prikaz in analizo. Pisati jih je bilo potrebno na roke, in pri spremembi enega podatka je bilo potrebno ročno popraviti cel niz drugih podatkov. To je zahtevalo ogromno časa in natančnosti, saj je najmanjša napaka pirpeljala do cele kopice napačnih podatkov. 

Danes je s programskimi paketi to mnogo lažje. Zato ogromno poklicev zahteva poznavanje osnov programov za delo s preglednicami. To znanje je priročno tudi za domačo uporabo pri mnogih opravilih (računanje, finance, statistika, ...). V nadaljevanju si bomo ogledali delo v dveh najpogosteje uporabljenih programih za delo s pregednicami in podali nekaj primerov uporabe.

Viri

Uvod

Preglednice so, podobno kot druga orodja, obstajale mnogo pred programskimi orodji, kot jih poznamo in uporabljamo danes. Preglednice so uporabljala podjetja za lepši in razvidnejši prikaz poslovanja, računovodstva in podobnih dejavnosti, ki zahtevajo veliko količino finančnih podatkov za skupni prikaz in analizo. Pisati jih je bilo potrebno na roke, in pri spremembi enega podatka je bilo potrebno ročno popraviti cel niz drugih podatkov. To je zahtevalo ogromno časa in natančnosti, saj je najmanjša napaka pirpeljala do cele kopice napačnih podatkov. Primer take preglednice vidimo spodaj na Sliki 1.

Z razvojem računalništva je ročno vpisovanje zamenjal računalniški izpis. A so bili prvi programi za delo s prosojnicami zelo drugačni, kot so programi v današnji uporabi. Potrebna je bila skupina programerjev, ki so pisali programe tako, da so dobili izpis želene oblike. V začetku sedemdesetih let prejšnjega stoletja je prišel v uporabo program LANPAR. Zasnovo in ideje so nato prevzeli tudi drugi in napisali programe za delo s preglednicami z grafičnimi vmesniki, kot jih poznamo danes. VisioCalc, Lutus 1-2-3, Quattro so bili prvi, ki so zelo pospešili prodajo Apple in osebnih računalnikov. Nato je v začetku devedesetih let prejšnjega stoletja Excel v paketu z urejevalnikom besedil zelo pripomogel k visoki prodaji osebnih računalnikov (in k široki uporabi Windows operacijskega sistema). Danes ima ravno Excel največji delež pri uporabi programov za delo s preglednicami. A se mu ostali kot so Calc iz paketa OpenOffice.org in spletni programi za delo s preglednicami (na primer GoogleDocs) hitro približujejo.



Slika 1: Primer preglednice

Obstajajo pa še drugi paketi, ki so ravno tako namenjeni delu s preglednicami a so usmerjeni na specifična področja (na primer podatkovno rudarjenje, več dimenzionalne preglednice (pogled na iste podatke iz različnih finančnih prespektiv), ...).

Osnovni pojmi

Preden se lotimo dela s preglednicami, se je potrebno ustaviti pri osnovnih pojmih. Vsi programi za delo s preglednicami vsebujejo enake koncepte, ki jih je potrebno razumeti za normalno delo. Preglednica je dvorazsežnostna tabela sestavljena iz celic. Osnoven pojem je torej vsekakor celica.

Celica

Osnova vsake preglednice je celica. Preglednica je sestavljena iz celic in vsaka celica ima svoj naslov. Tukaj bomo govorili o načinu naslavljanja celic, ki ga uporablja večina programov za delo s preglednicami. Vodoravno so celice (oziroma stolpci) naslovljene s črkami (A, B, C, ... do 265 stolpcev). Navpično (vrstice) pa s stevilkami (1, 2. 3, ... do 65536). Obarvana celica v spodnji preglednici ima naslov B2.

A B C
1
2
3

Polje vseh celic se imenuje List ali Sheet oz. Worksheet v angleščini. Ena preglednica lahko tako vsebuje več listov, ki se predstavljeni ponavadi z zavihki na dnu vmesnika, kot je prikazano na sliki 2.


Slika 2: Listi

Vrednost ene celice lahko prenašamo v druge celice s pomočjo naslova (to bomo spoznali kasneje.). Vsi listi so med seboj povezani in znotraj ene preglednice lahko vrednost vsake celice uporabimo na tudi na vseh ostalih listih. Vrednost celice je torej globalna v eni preglednici in jo tako lahko uporabimo na vseh listih preglednice.

Vsebino v celico vnašamo na dva načina. S klikom na celico lahko začnemo vpisovati vsebino. Ista vsebina se pojavi tudi v vsebinski vrstici nad preglednico. Če popravljamo vsebino celice, jo moramo vedno popravljati v tej vrstici.

Get the Flash Player to see this player.

Snami .ogg

Vrednost (ali vsebina) celice

Celica je lahko prazna. Lahko vsebuje podatek, ki se ne spreminja (številka, črka, niz, datum, ...). Ali pa vsebuje formulo, ki se spreminjajo glede na vrednosti celic, ki so uporabljene v formuli. Ravno formule (oziroma funkcije) in dogodkovno programiranje (o tem malo kasneje) prinesejo preglednicam vrednost, ki je pri prapirnatih preglednicah nimamo.

Formule (oziroma funkcije) se začnejo z znakom =. Tako bi lahko preprosto v izbrano celico vpisali:

=5+3

Ta celica ima sedaj vrednost 8. Lahko računamo tudi z vrednostmi v drugih celicah

=C2+C3

Sedaj vsebuje celica seštevek vrednosti celic C2 in C3. Če vrednosti teh dveh celic spremenimo, se spremeni tudi celica s to formulo.

Get the Flash Player to see this player.

Snami .ogg

Formule se ne omejijo samo na osnovne numerične operacije. Nad celicami lahko izvajamo še mnogo več, kot bomo videli kasneje pri primerih. Definirane formule se ponavadi kličejo funkcije. Poleg funkcij lahko v formuli tudi primerjamo vsebino celic (na primer, če je ena večja od druge) in izvajamo osnovno kontrolo toka, kot jo poznajo programski jeziki.

Excel: =IF(B2=0,"Ne morem deliti z 0",B1/B2)
Calc:   =IF(B2=0;"Ne morem deliti z 0";B1/B2)

Zgornji primer pogleda, če je vsebina celice B2 enaka 0. Če je, izpiše "Ne morem deliti z 0". V nasprotnem primeru deli vsebino celice B1 z vsebino B2.

Get the Flash Player to see this player.

Snami .ogg

Naslavljanje celic

Relativno naslavljanje

Osnovno naslavljanje celice je z njenim naslovom. V spodnjem primeru imata obe celici (A1 in B1) isto vrednost. To je vrednost celice A1. Če se vrednost celice A1 spremeni, se spremeni tudi vrednost celice B1.

A B
1 3 =A1

Get the Flash Player to see this player.

Snami .ogg

Če želimo referenco na obseg celic, lahko uporabimo dvopičje :. Če torej želimo referenco na celice A1, B1 in C1 naredimo to tako:

A B C D
1 1 2 3 =SUM(A1:A3)

Get the Flash Player to see this player.

Snami .ogg

Formula SUM sešteje vse vrednosti v celicah A1, B1 in C1. Reference obsega lahko uporabimo tudi na stolpcih. Na primer A1:A20 je referenca na prvi (A) stolpec in sicer samo prvih 20 vrstic. Lahko pa naredimo referenco na blok celic. Na primer A1:C3 obsega A1, A2, A3, B1, B2, B3, C1, C3 in C3.

Zakaj je to relativno naslavljanje? Ker je relativno glede na pozicijo. Poglejmo si spodnji primer.

A B
1 1 =A1
2 2

Get the Flash Player to see this player.

Snami .ogg

Če sedaj kopiramo celico B1 (postavimo se na B1 in izberemo iz menuja Edit->Copy) in jo prilepimo v celico B2 (postavimo se v celico B2 in izberemo Edit->Paste). Vsebina celice B2 je 2 oziroma v njej je referenca na celice A2 (=A2). Kopirali nismo torej referenco iz celice B1, ampak relativno referenco glede na pozicijo. Če bi celico B1 kopirali v C1 bi tam dobili =B1 in tako dalje.

Absolutno naslavljanje

Pri absolutnem naslavljanu referenca pri kopiranju vedno kaže na določeno vrstico in stolpec. Poglejmo si primer.

A B
1 1 =$A$1
2 2

Get the Flash Player to see this player.

Snami .ogg

Če sedaj celico B1 kopiramo v B2 ali C2 ali bilo katero drugo celico, bo ta vedno imela vrednost 1 oz. vrednost celice A1. Z $ znakom tako zagotovimo absolutno referenco na vrstico in stolpec. Lahko pa absolutno referenco naredimo samo na vrstico (=A$1) ali na stolpec (=$A1).

A B C
1 1 =$A1
1 2
A B C
1 1 =A$1
1 2

Če v prvem primeru kopiramo celico B1 na C1 bo tam =$A1. Če jo kopiramo na B2 bo tam =$A2. Torej je referenca na vrstico relativna. Če v drugem primeru kopiramo celico B1 in jo prilepimo na C1, bomo tam dobili =B$1. Če jo kopiramo na B2, tam dobimo =A$1, ker je vrstica 1 vedno absolutno naslovljena.

Reference na celice na drugih listih

Če želimo narediti referenco na celico na drugem listu, se sintaksa razlikuje pri Excelu in OpenOffice Calcu.

Excel: =Sheet1!A1
Calc : =Sheet1.A1

Reference na celice drugih datotek

Pri referencah na celice drugih datotek sesintaksa ravno tako razlikuje pri omenjenih programih.

Excel: ='c:\pot\do\datoteke\[test.xls]Sheet1!A1
Calc : ='c:\pot\do\datoteke\test.xls'#Sheet1.A1

Oblika celice 

Oblikovanje poteka kot pri urejevalniku besedil. Določimo lahko tip, velikost, obliko in barvo pisave. Vsebino celice lahko poravnamo desno, levo ali sredinsko. Celice lahko obrobimo (dodamo okvir) in izberemo podlago. Večina oblikovnih možnosti se nahaja v grafičnem meniju, ki je zelo podoben tistemu v urejevalniku besedil.



Get the Flash Player to see this player.

Snami .ogg

Ravno tako lahko določimo višino in širino celice. Če se v vrstici stolpcev (A, B, C, ..., AA, ..., IV) premaknemo s kazalcem miške med A in B se kazalec miške spremeni v <->. S kiklom in vlečenjem lahko premikamo pokončno linijo med stolpcema A in B. Podobno lahko naredimo z vrsticami (1, 2, ...).

Get the Flash Player to see this player.

Snami .ogg

Celice lahko tudi zaklenemo. Vsebino teh celic ne moremo spreminjat. Zaklepanje je uporabno, če želimo ohraniti formule, konstantne vrednosti, ... nedotaknjene. Tako ne pride do neželenih spremenb.

Excel: Označimo vse celice, ki jih bomo lahko popravljali (zaklenili bomo vse ostale)
           Format -> Cells in zavihek Protection (odstranimo locked kljukico)
           Tools -> Protection -> Protect sheet and apply protection
           
Calc: Označimo vse celice, ki jih bomo lahko popravljali
         Format -> Cells in zavihek Protection (odklenemo celice)
         Tools -> Protect Document -> Sheet

V nekaterih primerih pa hočemo celici dodeliti tip vsebine. Celica je lahko datum, lahko je število, procentualno število, besedilo, ... Tip izberemo, če z desnim gumbom miške kliknemo na celico in izberemo Format cells.

Get the Flash Player to see this player.

Snami .ogg

Formule, funkcije in konstante

Najprej si poglejmo matematične operacije. Pri vsebini celice smo že omenili osnovne matematične operacije: seštevanje, odštevanje, množenje in deljenje. Naj omenimo, da ima množenje in deljenje prednost pred seštevanjem in odštevanjem.

Primeri na desni uporabljajo števila. Lahko uporabimo tudi vsebino drugih celic
=2+4 Seštevek števil  2 in 4
=2-4 Odštevek števil  2 in 4
=2*4 Zmnožek števil  2 in 4
=2/4 Deljenje števil  2 in 4

Lahko računamo tudi bolj kompleksne račune. V spodnjem primeru imamo formulo v kateri računamo z vsebino drugih celic in števili.

=(A1+A2)/100

Lahko izračunamo tudi potence in korene:

=3^2 Potenciranje: tri na drugo (32)
=3^(-2) Potenciranje: tri na minus drugo (3-2)
=8^(1/3) Korenjenje: tretji koren iz osem (3√8)
=100^(3/2) Korenjenje: kvadratni koren iz 100 na 3 (√(1003))

Poleg matematičnih osnovnih formul poznajo programi tudi funkcije. Dobimo jih v menuju Insert -> Function. Omenimo samo nekaj najpogosteje uporabljenih.

=MAX(A1:A20) Izbere največje število izmed celic A1, A2, ... A20
=MIN(A1:A20) Izbere najmanjše število izmed celic A1, A2, ... A20
=SUM(A1:A20) Sešteje števila v celicah A1, A2, ... A20
=AVERAGE(A1:A20) Izračuna povprečje števil v celicah A1, A2, ... A20
=SQRT(A20) Izračuna kvadratni koren celice A20
=COUNTA(A1:A20) Vrne število polnih celic v obsegu A1 do A20
=COUNTBLANK(A1:A20) Vrne število praznih celic v obsegu A1 do A20
=AND(D2>5,C2>10) Logični in: če je D2 večji od 5 in hkrati C2 večji od 10
=OR(D2>5,C2>10) Logični ali: če je D2 večji od 5 ali C2 večji od 10

Get the Flash Player to see this player.

Snami .ogg

Funkcije lahko tudi gnezdimo:

=SUM(MIN(A1:A20),MAX(A1:A20))  

Ta nam sešteje vsoto dveh števil in sicer najmanjšega in največjega izmed obsega celic A1 do A20.

Že zgoraj smo omenili funkcijo, ki izvede logični test IF (=IF(B2=0;"Ne morem deliti z 0";B1/B2)). Tudi ta pogoj lahko gnezdimo:

=IF((AND(D2>5,C2>5)),3,(IF((OR(D2>5,C2>5)),2,1)))

Poskusimo zgornji pogoj razdeliti na 3 dele:
Pogoj nam izpiše 3, če sta obe celici D2 in C2 večji od 5.
Če nista pa izpiše: 2, če je ali C2 ali D2 večji od 5 oziroma izpiše 1, če nobena calica med njima ni večja od 5.

Dogodkovno gnano programiranje

Tukaj se bomo samo malo ustavili in obrazložili koncepte delovanja programov za delo s preglednicami. Poglejmo najprej spodnji primer

A B C D
1 4 5 6 =SUM(A1:C1)

V celici D1 imamo sedaj vrednost 15. Kaj pa če celici A1 spremenimo vrednost v 5? Vsebina celice D1 se spremeni v 16. Temu pravimo dogodkovno gnano programiranje.

Dogodek je sprememba vrednosti, ki jo vnesemo.
Vsaka celica ve, vrednost katerih celic  je odvisna od njene spremebe in jih ustrezno popravi.

Matematično se to predstavi z usmerjenim grafom celic.

Izdelava preglednice varčevanja

Želimo začeti varčevati za avto za katerega želimo zapraviti okoli 10.000 €. Denar želimo privarčevati v petih letih. Pri tem bomo denar nalagali mesečno v sklad in pričakovali donos 6%. Kakašen znesek moramo plačevati mesečno?

Če imamo 6% letno rast, moramo za mesečne obresti (iz obrestno obrestnega računa)  računati (1+6/10)^(1/12). Kar znese (to bomo izračunali seveda v Excelu ali Calcu) 1,00486755.

Mesečne obresti so torej 0,00486755 ((1+6/10)^(1/12)-1) zaokoroženo na 8 decimalk).

Če želimo šparati 5 let, moramo vplačevati določen znesek 60 mesecev (5let*12mesecev=60). V našem primeru smo znesek 143,9€ priredili tako, da na koncu dobimo 10.000€. Skupaj smo vplačali:

Vplačano=60*143,9€=8634€

V petih letih smo tako pridobili 1366€ (10.000-8634). Na ta dobiček plačamo še 20% davek kar znaša 273€ (1360*20/100).

Skupaj bomo tako plačali 8907€, kar pomeni da smo prišparali 1093€.



Mesečni znesek, ki ga vplačujemo, in procente donosa (ti so lahko pričakovano višji od 6%) lahko spreminjamo in gledamo kako se končni znesek temu primerno tudi spreminja. Pogledamo lahko, da bi z varčevanjem na banki s 4% obrestmi morali vplačevati 151,1€ mesečno. S preminjanjem obresti (D2) in zneska (D5) lahko vidimo, kako se spreminjajo zneski po mesecih in koliko na koncu našparamo.

Pogledamo še kako je s formulami, ki smo jih vnesli v posamezno celico.

D3 =(1+D2/100)^(1/12)-1 Formulo smo že zgoraj obrazložili, a ji moramo odšteti še 1, da dobimo pravo mesečno obrestno mero.
C8 =D5 V to celico samo skopiramo vrednost celice mesečnega vnosa denarja.
D8 =C8*$D$3 Zračunamo obresti na znesek, ki smo ga privarčevali tisti mesec. Pri tem uporabimo absolutno naslavljanje na mesečno obrestno mero, ker bomo kasneje to formulo kopirali po celotnem stolpcu D in mesečna obrestna mera mora biti vedno enaka.
E8 =C8+D8 V tej celici preprosto seštejemo znesek in obresti nanj in dobimo novi znesek. Tudi to formulo kopiramo po celem stolpcu E.
C9 =E8+$D$5 Nov znesek je prejšnji znesek plus mesečni polog. To formulo ravno tako kopiramo navzdol v stolpcu C. Absolutno naslavljanje uporabimo zato, ker vsah mesec plačujemo enak znesek.

Preglednico dobimo tukaj.
obrestnoobrestni.xls

Kaj pa če danes najamemo bančno posojilo 10.000€ in pričakujemo, da se EURIBOR v naslednjih petih letih ne bo spreminjal? V povprečju bi na banki plačevali 198€ mesečno (podatki november 2007), kar v 60 obrokih znaša 11.880€. Razlika med posojilom in varčevanjem je 2973€ ali kar lepo prišparan znesek.

Kot dodatek k nalogi naj omenimo še formulo za obrestno obrestni račun, ki gre takole:
Gn=G0*(1+p/100)n

n je število let varčevanja
Gn je privarčevani denar po n letih varčevanja skupaj z obrestmi.
G0 je denar, ki ga na začetku vložimo.
p so letne obresti.

Izdelava grafa

Pri prejšnji nalogi poskusimo izdelati še graf. Na x osi želimo imeti mesece (torej od 1 do 60) na y osi pa rast denarja.

Najprej označimo vse celice od B8 do C67. To je stolpec z meseci in tisti z zneski. Nato izberemo Insert -> Chart. Odpre se nam novo pogovorno okno v katerem oblikujemo graf po lasni želji. Postopek je razviden iz spodnjih slik.

1. Izberemo obliko grafa



2. Določimo legendo stolpca C in vrednosti na x osi



3. Dodamo še naslov in opis x in y osi.



Končni graf ni sicer nič posebnega, saj je donosnost vedno enaka in temu primerno graf pokaže linearno premico, ki izgleda tako:



Celoten potek izdelave grafa v programu Calc si lahko pogledamo na spodnjem posnetku:

..
Get the Flash Player to see this player.

Download .ogg