Excel za početnike

U prethodnoj lekciji, Excel za početnike I, naučili ste kako da organizujete radnu svesku, kako se pojedini tipovi podataka formatiraju i ponašaju nakon unosa, kako se radi sa kolonama i vrstama, što podrazumeva selektovanje, formatiranje, dodavanje i brisanje istih.
Ova lekcija je nastavak nadogradnje osnovnih znanja iz Excela. Tu pre svega spadaju formule, funkcije, filtriranje i sortiranje podataka. Na kraju, kada napravite tabelu, treba da zanate i da je odštampate. Kada prođete kroz ovu lekciju, shvatićete zašto je za svakodnevni rada u kancelariji neophodan rad sa tabelama.

Zadatak 1. Obnavljanje prethodne lekcije i priprema datoteke za novu lekciju. Potrebnu datoteku možete da preuzmete ovde.

1. Napravite kopiju radnog lista “Podaci” i preimenujte tako dobijeni radni list u “Cenovnik”.
2. Umetnite kolonu za redni broj na početku tabele i popunite je.
3. Obrišite kolonu “Barcode”.
4. Desno od cene dodajte kolone za: PDV (20% je za svu robu), Iznos PDV-a, Cena sa PDV.
5. Formatirajte sve brojeve da se prikazuju sa dve decimale, osim PDV koji treba da bude procenat.
6. Iskopirajte kolonu “Cena sa PDV” u kolonu K, i u zaglavlju neka bude “MP cena”. Ove dve cene su iste, ali želim da vam u nastavku pokažem različite načine dodavanja procentnog iznosa.
7. Umetnite vrstu iznad zaglavlja i unesite naslov “VP i MP cenovnik robe za 2016.”
8. Unesite sada komentare u zaglavlju tabele, a za ćelije redom: Iznos PDV-a, Cena sa PDV i MP cena:
   • =Cena [€]*PDV[%]
   • =CENA[€]+Iznos PDV-a
   • =Cena[€]*1,2

Kada uradite sve zadatke, vaša tabala treba da izgleda kao na slici. Većinu zadataka sam objasnio u prvom delu, tako da neću da se ponavljam. Ostaje jedino dodavanje komentara što možete da pročitate u nastavku.

Komentari u ćelijama

Svakoj ćeliji možete da dodate komentar. Komentari se uglavnom dodaju u zaglavlju i to kao uputstvo za popunjavanje kolone. Komentar se u ćeliju dodaje tako što na istu primenite desni klik mišem, pa iz kontekst menija izaberete Insert Comment.

Kada na neku ćeliju dodate komentar, na istu se u gornjem desnom uglu pojavi crven trouglić. Da bi videli komentar na nekoj ćeliji, dovoljno je da dovedete pokazivač miša na istu, i komentar će se pojaviti. Kasnije, možete da menjate komentar sa Edit Comment ili pak da ga obrišete sa Delete Comment.

Formule u Excelu

Kada imate malu tabelu, potrebne podatke možete da izračunate i na digitronu. Međutim kod velikih tabela Excel je pravo olakšanje. Napravite formulu u jednoj ćeliji, i prostim povlačenjem ručice, iskopirate formulu u svim ostalim ćelijama.

Formule u excelu funkcionišu kao i one u matematici. To znači da važe ista pravila: prioritet računskih radnji, zagrade, aritmetički i logički operatori, konstante, procenti i promenljive. Primena izgleda ovako. Prvo, izaberete ćeliju koja „prihvata“ rezultat, unesete znak jednakosti i kucate formulu poštujući matematička pravila. Kada završite sa unosom formule, potvrdite enterom.

U našem slučaju, selektujete ćeliju I3 i otkucate znak jednakosti. Sada ste u modu za editovanje formule, što znači da ne treba da klikćete bilo gde, već samo na ćeliju koja vam treba jer se u formulu automatski unosi adresa ćelije na koju ste kliknuli. Kliknite na ćeliju G3 gde je cena i program će automatski upisati adresu G3 u formulu. Unesite sada zvezdicu (znak za množenje), a potom kliknite na ćeliju H3 u kojoj je stopa PDV-a. Kada završite sa unosom formule, potvrdite enterom.

Za formule u ostalim ćelijama dole nema smisla ponavljati ovakav postupak, jer u n
ašem zadatku ima 100 stavki (pojedine tabele imaju i po par hiljada stavki), tako da će mo kopirati formulu. Selektujte ćeliju I3. Na liniji za formule se ne vidi 2,11 već formula =G3*H3. Uhvatite sada ručicu ćelije I3 u donjem desnom uglu, i ne puštajući levi taster miša, povucite miša do kraja tabele. Dobićete formule u svim ćelijama i to sa ispravno napisanim adresama. Nije se kopirala formula =G3*H3 u svim ćelijama, već je u sledećoj =G4*H4, pa =G5*H5, i tako dalje, upravo onako kako nama treba.  Adrese ćelija se menjaju relativno, pa se ovakav način zove relativno adresiranje. Postoji i apsolutno adresiranje, ali o njemu ću kasnije u nastavku, kada budemo konvertovali cene u dinarima.

Napomena: formule možete da unosite i na liniji za formule, kucajući karakter po karakter. Takođe, postojeću formulu možete i da editujete tako što ubacite kursor na liniji za formule.

Na isti način se popunjavaju i ostale kolone (slika dole), s tim što se u poslednjoj koloni koristi konstanta. Vrednosti koje se dobijaju u poslednje dve kolone su iste, a način na koji se izračunavaju različit.

Za ovaj model pokazivanja sam se odlučio jer mnogi učenici (i u prvom, a i u višim razredima) imaju problem sa primenom procentnog računa, tako da će mo se u sledećem naslovu baviti ovom temom. Nije dobro da neko završi prvi razred srednje ekonomske škole, a da nezna da snizi cenu za npr. 7%.

Rad sa procentima

Kada u formulama radite sa procentima, treba da shvatite da 1% možete da napišete i kao razlomak 1/100 i kao decimalni broj 0,01. Excel će “razumeti” svaki oblik, samo treba da vodite računa o tome kako ste formatirali ćelije tj. kako ste podesili tipove podataka. Evo još nekoliko primera.

Druga važna stvar je u tome šta vam se traži u tabeli. Može to da bude osnovica, pa procentni iznos uvećanja ili umanjenja, pa na kraju konačna uvećana ili umanjena vrednost osnovice za odgovarajući procenat.

U našem slučaju (prethodni zadatak) osnovica je cena[€] na koju smo dodali procentni znos PDV-a od 20%, i na kraju dobili cenu sa PDV-om prostim sabiranjem prethodne dve vrednosti.

U poslednjoj koloni je drugi slučaj. Tražilo se da se odmah izračuna krajnja vrednost cene sa PDV-om (cena uvećana za 20%). To je urađeno tako što smo cenu pomnožili sa 1,2 a evo i zašto. Jedno celo ima 100%, a pošto uvećavamo vrednost, onda dodajemo još 20%, pa je to ukupno 120% odnosno 1,20 ili ti samo 1,2. Znači, ako želite da neku vrednost X uvećate za 20%  dovoljno je da je pomnožite sa 1,2.

Evo još jednog primera. Ako je npr. “skočio” kurs evra, i vi hoćete da uvećate postojeće cene za 2% dovoljno je da ih pomnožite sa 1,02 jer je 100%+2%=102%=1,02.

Slično povećanju, radi se i umanjenje cena za određeni procenat. To može da bude zbog ostvarenog rabata ili bonusa, zbog količinskog popusta pri kupovini, zbog akcijske prodaje ili pak zbog roka trajanja na proizvodima. Na slici dole, dat je primer kako se to radi u trgovini. Ako izuzmemo prehranu, veliki broj kupaca se “pogađa”za popust.

Apsolutno adresiranje ćelija

U pojedinim proračunima u praksi, čest je slučaj da vrednosti svih ćelija u nekoj koloni zavise od samo jedne vrednosti. To na primer može da bude koeficijent za obračun zarada zaposlenih koji se najčešće smešta u jednu ćeliju. Drugi primer je cenovnik u evrima gde je vrednost kursa evra u jednoj ćeliji, a da vi treba da izračunate cene u dinarima za sve proizvode.

Iz gore navedenog ste videli da je svaka ćelija jednoznačno određena svojom adresom tj. referencom. Adrese mogu biti relativne jer se menjaju prilikom kopiranja (A1,C3, …), apsolutne jer se ne menjaju  ($A$1, $C$3, …) ili mešovite ($A1, C$3, …). Ako u formuli koristite ćeliju sa nekog drugog radnog lista, njena referenca sadrži i naziv tog radnog lista (Sheet3!A1). Međutim, na ovom nivou obradićemo još samo apsolutno adresiranje i to kroz zadatak u nastavku.

Zadatak 2. Vremenom, vaši kupci su se podelili na “velike” i “male”. Odlučili ste da velikim kupcima odobravate rabat na VP cece od 7%, a malim kupcima rabat od 3%. Shodno tome, potrebno je da napravite dinarski cenovnik za obe grupe i to na sledeći način:

1. Napravite kopiju radnog lista “Cenovnik” i preimenujte tako dobijeni radni list u “Dinarski cenovnik”.
2. Naslov prepravite u “VP cenovnik robe za 2016.”
3. Obrišite kolone “PDV”, “Iznos PDV-a”, “Cena sa PDV” i “MP cena”.
4. Desno od cene dodajte kolone za: Cena [din.], Cena-3% [din.] i Cena-7% [din.].
5. U ćeliju Cena [din.] ubacite komentar “Računa se kao proizvod cene u evrima i vrednosti kursa evra”.
6. Umetnite jednu vrstu ispod naslova (a iznad zaglavlja tabele) i u ćeliju F2 unesite “Kurs €”, a u ćeliju G2 vrednost “120”. U ćeliju H1 unesite datum.
7. Izračunajte sada sve tražene cene.
8. U ćeliju G2 promenite kurs sa 120 na 123 i analizirajte šta se dešav sa cenama.

U odnosu na prethodni sadržaj, novina je samo apsolutno adresiranje koje se koristi za izračunavanje cene u dinarima (kolona H), pa ću vam samo to i pokazati. Za prvi proizvod iz tabele, cena je u ćeliji G4, za sledeći u G5, i tako redom, što je u redu. Međutim kurs evra je samo u ćeliji G2. To znači da, prilikom kopiranja formule, treba da obezbedimo da se G2 ne menja u G3, G4, … več da ta adresa bude apsolutna. To se postiže tako što se u prvoj ćeliji, gde unosimo formulu (ćelija H4), ispred slova G i broja 2 unese znak za dolar. Drugi lakši način je da prilikom unosa formule, nakon klika na ćeliju G2 pritisnete funkcijski taster  F4, pa onda enter. Sada kada pomoću ručice iskopirate formulu na dole adresa G4 se menja u G5, G6,… a adresa $G$2 ostaje nepromenjena u svim ćelijama.

Ako koristite ekselove fajlove u kombinaciji sa fiskalnom kasom, a za izdavanje faktura, može da se desi da se izračunate vrednosti ne poklapaju. To je zato što u ekselu, kada formatirate broj da se prikazuje sa dve decimale, on se samo prikazuje tako, a u formuli se koriste sve decimale, dok fiskalna kasa koristi samo dve decimale. Rešenje za ovaj problem je funkcija Round koja zaokružuje broj na dve decimale (tj. kako vi zadate) kao što se uči iz matematike. Ako vas ovo interesuje, preuzmite datoteku sa linka formule u Excelu i na radnom listu “Matematika-R” pogledajte kolone L, M i N. Takođe, tamo će te naći još zadataka ali nešto višeg nivoa.

Funkcije u Excelu

Razlika između formule i funkcije je u tome što formulu sami pravite, a funkcija je već napravljena i isprogramirana da nešto uradi tako da je vi samo “pozivate”. Na primer, treba da izračunate prosečnu cenu u evrima svih artikala iz prethodnog zadatka. Formula bi izgledala =(G4+G5+…+G102+G103)/100 što znači da treba sto puta da kliknete na ćelije za cene (od G4 do G103), 99 puta da otkucate znak plus i da prebrojite sabirke. Zamislite šta bi tek bilo da imate par hiljada slogova. Funkcija bi izgledala =Average(G4:G103). Praktično samo selektujete oblast sa cenama, a funkcija je isprogramirana da sama sabere cene, prebroji sabirke i izračuna prosečnu vrednost.

Funkcije u excelu imaju veoma jednostavnu primenu. Prvo, izaberete ćeliju koja „prihvata“ rezultat, kliknete na Insert Function  (f_x) gde birate funkciju koja vam treba, popunite argumente (jedan ili više)  i potvrdite sa enter. Struktura je jednostavna i izgleda ovako =IMEFUNKCIJE(ARGUMENT). Argumenti mogu biti: oblasti, brojevi, tekst ili logičke vrednosti.  Ako ih ima više, odvajaju se znakom „;“

Funkcije koje se obrađuju na ovom nivou učenja su:

=SUM(arg) je funkcija za sabiranje. Argument je oblast koja se sabira (npr. A1:A20).

=AVERAGE(arg) je funkcija za prosek. Argument je oblast koja ulazi u prosek.

=MAX(arg) je funkcija koja traži najveću vrednost. Argument je oblast koja se pretražuje.

=MIN(arg) je funkcija koja traži najmanju vrednost. Argument je oblast koja se pretražuje.

=COUNT(arg) je funkcija koja služi za prebrojavanje. Argument je oblast koja se prebrojava.

Za sve gore navedene funkcije argumenti mogu da budu i više oblasti.

=COUNTIF(Range;Criteria) je funkcija za uslovno prebrojavanje. Criteria je u stvari uslov koji tražimo, a Range je oblast u kojoj tražimo koliko se puta javlja zadati uslov.

=SUMIF(Range;Criteria;Sum_range) je funkcija za uslovno sabiranje. Criteria je u stvari uslov koji tražimo, a Range oblast gde da nađemo traženi uslov. Sum_range je oblast iz koje se uzima vrednost za sabiranje, a koja zadovoljava traženi uslov.

Zadatak 3.  Kupac, koji ostvaruje rabat od 7%, je kupio robu sa spiska “Dinarski cenovnik” i to prvih 20 artikala sa količinama 2, 4, 6, …, 40 redom. Napravite fakturu za kupca i odgovorite na pitanja u nastavku.

1. Napravite kopiju radnog lista “Dinarski cenovnik” i preimenujte tako dobijeni radni list u “Funkcije”.
2. Sakrijte drugu vrstu gde je kurs evra i obrišite nepotrebne vrste (ostaje zaglavlje i prvih 20 stavki).
3. Sakrijte sve nepotrebne kolone za cene. Ostaje samo “Cena-7% [din.]”.
4. Dodajte kolonu za količinu, ispred kolone “J.mere” i unesite količine od 2 do 40 sa korakom 2.
5. Napravite novu kolonu “Iznos” i izračunajte vrednost =Količina*Cena u svim ćelijama, kao i ukupnu vrednost fakture.
6. Pomoću funkcija dajte odgovore na sledeća pitanja: “Prosečna cena artikala je:”, “Najveća vrednost iznosa:”, “Najmanja vrednost iznosa:”, “Koliko je stavki na fakturi:”, “Broj stavki je iz grupe “Profili”:”, “Promet od prodaje Stiropora:”

Kada završite sa prepravkama tabele (a da bi uvežbali gradivo iz lekcije Excel za početnike I), treba da izračunate ukupnu vrednost fakture. Selektujete ćeliju L24, kliknete na fx na liniji za formule i izaberete funkciju Sum. U sledećem koraku selektujete oblast za sabiranje (L4:L23) i potvrdite enterom.

Pošto se funkcija Sum često koristi, napravljena je i prečica za ovu funkciju tako da  možete da koristite i AutoSum koja se inače nalazi na tabu Formulas ili na tabu Home u gornjem desnom uglu. U ovom slučaju program “predlaže” oblast za sabiranje, tako da ako je predložena oblast tačna, dovoljno da potvrdite enterom. U slučaju da je program “pogrešio” mišem selektujete željenu oblast i potvrdite enterom.

Prosečna cena se računa pomoću funkcije Average. Selektujete ćeliju E26, kliknete na fx na liniji za formule i izaberete funkciju Average. U sledećem koraku selektujete oblast za koju računate prosečnu vrednost (K4:K23) i potvrdite enterom.

Najveća vrednost iznosa se računa pomoću funkcije Max. Selektujete ćeliju E27, kliknete na fx na liniji za formule i izaberete funkciju Max. U sledećem koraku selektujete oblast iz koje treba da izvučete maksimalnu vrednost (L4:L23) i potvrdite enterom. Na isti način se traži i najmanja vrednost, s tim što se koristi funkcija Min u ćeliji E28.

Broj stavki na fakturi se računa pomoću funkcije Count. Selektujete ćeliju E29, kliknete na fx na liniji za formule i izaberete funkciju Count. U sledećem koraku selektujete oblast za brojanje ( bilo koja kolona koja sadrži brojeve a ne slova, npr. F4:F23) i potvrdite enterom.

Može da se zaključi da sve prethodne funkcije imaju po jedan argument, a to je oblast na kojoj se izvršavaju. Odgovor na poslednja dva pitanja u zadatku daju funkcije sa dva odnosno tri argumenta.

Za razliku od funkcije Count, koja redom broji sve stavke, funkcija Countif broji samo stavke koje zadovoljavaju zadati kriterijum. U našem slučaju treba da izbrojimo koliko puta se javlja reč “profili”. To je ujedno i kriterijum, a oblast gde program treba da broji i traži je C4:C23. Sa slike se vidi rešenje (broj 4) i pre nego što pritisnete enter.

Funkcija Simif radi na sličnom principu kao i Countif, tj. ne sabira sve sabirke iz zadate oblasti kao funkcija Sum, već samo one koji zadovoljavaju zadati kriterijum. To je upravo ono što nama treba da bi rešili poslednji zadatak. U polju za kriterijum zadajemo koju reč program treba da pronađe (Stiropor), u polju za oblast gde da je traži (C4:C23) i u poslednjem polju šta da sabere. U našem slučaju se tražio promet ostvaren od prodaje stiropora, pa treba uneti oblast L4:L23. Da je bilo pitanje koliko je ukupno komada stiropora prodato, tada bi uneli oblast kde je količinatj. F4:F23.

Složenije primere na ovu temu možete pogledati na linku funkcije u Excelu.

Zadatak 4.  Potrebno je da pripremite datoteku za sledeći deo “Excel za početnike III” u kome se obrađuje sortiranje i filtriranje podataka, izrada grafikona i štampanje tabela. Zadatak je da napravite kopiju radnog lista ”Cenovnik” u novu radnu svesku. Ukoliko mislite da niste dobro uradili ovaj radni list (zadatak 1) možete da iskoristite radni list “Cenovnuk-U” koji je sakriven.

Da bi otkrili sakriven radni list primenite desni klik na neki jezičak, pa izaberite Unhide / Cenovnik-U. Sada na otkriveni jezičak ide desni klik, pa Move or Copy… U polje To book: izaberite (new book) i na kraju, pre nego što potvrdite sa OK, nemojte da zaboravite da čekirate Create a copy. Sačuvate novu datoteku.