Upotreba VLOOKUP funkcije u Excel-u

VLOOKUP je jedna od najkorisnijih Excel funkcija. To je takodje, i jedna od najmanje razumljivih excel funkcija. U ovom tekstu ćemo pokušati da “demistifikujemo” VLOOKUP funkciju na način što ćemo karakteristike njenog rada objasniti kroz realizaciju jednog realnog finansijskog dokumenta – kreiraćemo potpuno upotrebljiv šablon fakture za neko hipotetičko preduzeće.

Sa funkcijama kao matematičkom kategorijom smo se počeli susretati u djetinjstvu – već prvih dana školovanja. Druženje sa funkcijama je u početku najčešće bilo kroz  matematiku kao školski predmet, mada ni neki drugi školski predmeti nisu oskudjevali u korištenju kategorija čiji je imenitelj funkcija. Naše prve funkcije su bile: apsolutna vrijednost, kvadratni korjen,sinus, kosinus, tanges… Kada smo počeli učiti programske jezike – funkcije su se pojavile kao sastavni dio razvoja programskog proizvoda a predefinisane funkcije koje su dolazile uz programske jezike su najčešće bile grupisane prema podršci koju su one pružale u odnosu na specifične tipove podataka (stringovi su imali svoj set funkcija, brojevi – svoj, datumi – svoj). Skoro svi programski jezici (pascal, basic, JAVA, C++, PHP…) su imali skoro identičan set funkcija opšte namjene. Kad smo “ušli u Excel vode”, nije nam bilo “čudno” kad smo otkrili  da i on ima funkcije (“poveži 2 niza”, “oduzmi dva datuma”, “pretvori niz u broj” itd). Continue reading

Advertisements

List controle u Excelu

UVOD – APSOLUTNA I RELATIVNA ADRESA ĆELIJE U TABELI U MS EXCEL-u

RELATIVNA ADRESA ĆELIJE

Sve adrese ćelija (u obliku kako su prikazane na narednoj slici ) A2, A3, B4… su relativne adrese.

Prilikom upisa formule u jednu ćeliju (npr u C2) i daljnjim kopiranjem u ostale ćelije (C3 i C4) Excel sam određuje adrese ćelija u formuli i mijenja njihove relativne adrese.

Npr. ako u ćeliji C2 množimo ćelije A2*B2, Excel će u narednom redu, redu broj 3, ukoliko izvršimo kopiranje sadržaja ćelije C2 u C3, u ćeliji u koloni C promijeniti relativne adrese i dobićemo proizvod ćelija A3*B3..

Continue reading

Pivot tabele

Pivot tabele su svojstvo Excel-a koje bi trebalo da znate da koristite. Umjesto da vršite analizu podataka nad gomilom redova, pivot tabele vam omogućuju da izvršite grupisanje podataka i prezentirate podatke na novi način u samo nekoliko poteza mišem. Takodje ste u mogućnosti da kolone tabele prevedete u redove i obrnuto. Problem je u tome što ljudi unaprijed imaju stav da je to previše komplikovano za naučiti pa od svega u startu odustaju, što nije sasvim tačno.

Šta su Pivot Tabele

Pivot tabele možete zamisliti kao korisnički kreirane sumarne tabele vaše originalne Excel tabele. Pivot tabelu ćete kreirati na način što ćete definisati koja polja želite da se vide u njoj i kako se podaci trebaju prikazati. Na osnovu vaših izabranih polja, Excel će izvršiti sumiranje i reorganizaciju originalnih podataka na način da će vam ranije unesene podatke prezentirati na novi način

Kao primjer je uzet skup podataka od 4000 glasača (iz SAD) za koje postoje sljedeći podaci:

– ID glasača

– Pripadnost partiji

– Glasačko mjesto

– Starosna grupa

– Kada je poslednji put glasao

– Godina kada su bili registrovani

– Status glasačkog listića

Pogledajmo prvih 20 podataka. Podaci su zbunjujući i nepregledni. U ovakvom formatu, ključno pitanje je koliko imamo glasača na glasačkim mjestima.

Koristeći Excel pivot tabele, možemo organizovati i grupisati podatke na način da dobijemo odgovore na sljedeća pitanja:

– Koje partije su smanjile broj glasača po regijama

– Da li glasači izbjegavaju da izlaze na izbore

– Koja glasačka mjesta imaju najviše demokrata

– Da li na izbore izlaze glasači izmedju 18 i 21 godine

Pivot tabele omogućuju da grupišete spoljnje izvorne podatke po bilo kojem od polja. Sljedeća slika npr prikazuje broj glasača po partijama i po izbornim mjestima

Upotrebom pivot tabela, u mogućnosti sam da dalje analiziram podatke birajući dodatna polja iz PivotTable Field List. Na primjer, mogu uzeti iste podatke i segmente i grupisati ih po starosnoj grupi glasača

Razumjevanje strukture Pivot tabela

Na prethodnoj slici su označeni glavni dijelovi pivot tabele

(1) PivotTable Field List – ova sekcija u gornjem desnom dijelu ekrana prikazuje polja u vašem spreadsheet-u. Možete uraditi check polja ili željena polja prevući mišem u donji kvadrant sa 4 dijela

(2) Donji desni kvadrant – ovaj prostor definiše gdje i kako će podaci biti prikazani na vašoj pivot tabeli. Možete imati polja prikazana ili u redovima ili u kolonama. Takodje, možete označiti da li podaci trebaju biti prebrojani, sabrani , izračunati prosjeci itd..

(3) Prostor označen crvenom bojom na lijevom djelu prethodne slike predstavlja rezultat selekcija koje su napravljene u dijelovima (1) i (2). Primjetićete da je jedina razlika izmedju 2 poslednje slike ovog teksta u tome što je u poslednjoj slici prevučeno polje AGE GROUP ispod polja PRECINCT u Row Labels kvadrantu.

Kako kreirati Pivot Tabele

Postoji nekoliko načina da se naprave pivot tabele. Excel ima u sebi ugradjenu logiku koja poznaje tipove polja koje ste definisali i on uvijek pokušava da ih smjesti u korektne kolone ili redove ukoliko ih izaberete. Na primjer, numerički podaci kao što je npr. izborno mjesto trebaju da se pojavljuju u desnom dijelu tabele u kolonama. Podaci tekstualnog tipa, kao što je naziv partije, bi trebali da se pojavljuju u redovima tabele.

Ajde da probamo..

1. Otvorite vaš izvorni Excel dokumenat sa podacima i pobrišite iz tabele sve prazne redove i kolone.

2. Provjerite da svaka kolona ima svoje zaglavlje kako bi se mogla izabrati iz liste polja kasnije.

3. Provjerite da su vaša polja u tabeli korektno formatirana za tip podataka koji se nalazi u njima.

4. Označite skup podataka koji vas interesuje

5. Odaberite Insert tab.

6. Odaberite PivotTable taster iz Tables grupe.

7. Odaberite PivotTable iz list.

Pojavljuje se Create PivotTable dialog.

8. „kliknite“ 2* na Table/Range: vrijednost.

9. Odaberite radio button za New Worksheet.

10. Odaberite OK.

Nova radna tabela se otvara sa praznom pivot tabelom. Vidjećete da su se imena kolona iz izvorne tabele pojavila u dijelu PivotTable Field List.

11. Prevucite mišem polje npr PRECINCT iz PivotTable Field List u donji segmenat, u Row Labels kvadrant. Poslije ove akcije na lijevoj strani od Excel tabele bi trebalo da se prikažu redovi sa svim vrijednostima izbornih mjesta. Možete primjetiti da se pojavio „check“ pored polja PRECINCT.

12. Sljedeći korak, je da se pitate šta bi volili da znate o svakom izbornom mjestu. Mišem ću „povući“ polje PARTY iz PivotTable Field List u Column Labels kvadrant. Ovo će imati za rezultat pojavljivanje dodatnih kolona – za svaku partiju po jedna u pivot tabeli. Uočite da u ovom trenutku nemate nikakve numeričke podatke.

13. Da bi vidjeli broj članova za svaku partiju, treba da isto polje prenesem i u kvadrant Values. U ovom slučaju, Excel zaključuje da ja želim da izračunam „Count of PARTY“.

Ali čekajte …. ima tu i više ….

Pošto ste odlučili da pravite pivot tabele, vjerovatno ste razmišljali o nekom dodatnom načinu grupisanja podataka. Npr. možda želite da znate raspon godina glasača po glasačkim mjestima i po partijama. U ovom slučaju, trebao bih da mišem povučem polje AGE GROUP iz PivotTable Field List u donji kvadrant ispod PRECINCT imena Row Labels.

Svaka starosna grupa je sada podjeljena po glasačkim mjestima.

U ovom momentu, možete početi razmišljati o upotrebljivosti i preglednosti dobijenih podataka. Kao i kod običnih spreadsheet-ova, možete manipulisati sa poljima. Npr, možete izmjeniti naziv “Grand Total” u nešta prihvatljivije ili možete čak sakriti vrijednosti za godine na neki podskup podataka. Možete sakriti cijele kolone ili redove. Sve ovo se radi kao i kod običnih excel tabela.

Jedna stvar koja je u ovom dijelu drugačija je da pivot tabele imaju svoje vlastite, dodatne opcije. Možete im pristupiti birajući desni taster miša i unutar dobijenog menia PivotTable Options… Npr, možete poželiti da vam se prikažu samo ukupni zbirovi za kolone a ne i redovi. Takodje, postoje i načini za filtriranje podataka izborom ikonice koja se nalazi na labeli kolone po kojoj želite da uradite filtriranje. Filtriranje možete definisati i preko Report Filter quadranta.