Excel Napredni Kurs

0

EXCEL NAPREDNI KURS

2010

EXCEL NAPREDNI KURS ZA SAVA OSIGURANJE

Velibor Saban [email protected]

EXCEL NAPREDNI KURS SUBTOTAL

Kada raspolažete složenim podacima u kojima je potrebno da vidite parcijalne zbirove, često ste u potrebi da umećete dodatne redove u kojima ćete postavljati funkcuju za sabiranje. Kada tih podataka ima relativno malo, nema osobitih problema da to napravite kao na ovoj slici:

Prvi zbirovi su izvedeni verovatno kao klik na polje AutoSum, program je ispravno označio podatke koje treba sabrati, a vi ste na kraju samo pripazil ida konačni zbir u redu SVE UKUPNO bude izveden kao prosti izraz tri međuzbira. Naravno, taj zbir je moguće izvesti i kao funkciju: mogli ste napisati =SUM(B7;B13;B19) i dobili biste isti rezultat. Izraz u primeru je napisan čisto kao prosta ilustracija. Međutim, situacija postaje znatno komplikovanija ukoliko postoji mnogo veći obim podataka, pa još i njihova višestruka hijerarhija, kao u sledećem slučaju sa slike:

1

EXCEL NAPREDNI KURS

Pripazite na to da u situaciji na slici ima više sakrivenih redova: reč je o prostoj, ali velikoj strukturi u kojoj postoje polazni podaci za tri kase u pet radnih dana tokom četiri nedelje, sa svim međuzbirovima za svaku nedelju, za svaku kasu i za sve kase. U ovom času bi se već moglo govoriti o tome da li su polazni podaci oblikovani optimalno: bilo bi zgodnije da je okupljanje organizovano kao uređena lista, kako biste mogli da agažujete alatke za međuzbirove ili izvedenu tabelu. Ali, praksa je nemilosrdna: odveć često biva da polazni podaci nisu tako sređeni. Glavni štos koji nas ovog časa interesuje, a koji ćemo iskoristiti u slučaju iz primera je sledeći: da li ste znali da funkcija SUBTOTAL ignoriše sve druge funkcije SUBTOTAL u naznačenom opsegu? Samo trenutak, kažete vi: šta to znači? Pogledajte još jednom prvu sliku (tri kase, tri dana): bilo bi najlakše da ste postavili funkciju =SUM(B3:B19), ali tako biste uhvatili i parcijalne sume. Neko bi se dosetio, pa bi napisao =SUM(B3:B19)/2 i tim trikom bi došao do tačnog rezultata... Ali, sve je to labavo: da li biste u slučaju trostepene hijerarhije delili to sa tri? Da li su svi međurezultati izvedeni valjano? Ama, kako bi bilo da svaki međurezultat, bez obzira na nivo u hijerarhiji, bude napravljen kao rezultat polaznih podataka? Ali, funkcija SUM bi onda morala da glasi ovako: =SUM(B4:B6;B10:B12;B16:B18) Sasvim u redu, valjano i tačno, ali šta će se desiti kada se tabela iskomplikuje? Šta će biti kada budemo dodali sve dane, sve radne nedelje, petnaest prodavnica? Preuređenje formula pešice bi dovelo do greške sa verovatnoćom od 99%, budite uvereni u to. Ovde uskače pomenuta osobina funkcije SUBTOTAL. Pogledajte sliku:

2

EXCEL NAPREDNI KURS

Obratite pažnju na formulu u ćeliji B21. Sasvim je očigledno da i tri međuzbira ulaze u opseg, ali rezultat je valjan: funkcija SUBTOTAL je uradila svoj posao efikasnije od funkcije SUM. Dakle, isplatilo se da je primenimo. Pazite: ovde sam namerno povukao da opseg vrednosti u funkciji bude do ćelije B20, a ne do B19. To sam učinio zato što u tom slučaju obezbeđujem kontekstualnu izmenu formule u slučaju da dodajem redove za sledeće kase!

3

EXCEL NAPREDNI KURS A ako vam još uvek nije najjasnije, hajde da pogledamo izmenjenu situaciju sa druge slike:

Da li je potreban ikakav komentar? OK, i ovde sam, ilustracije radi, zadržao skrivene redove. U ovom slučaju, važno je da napomenem taj detalj. Uskoro ćete saznati zašto je tako. Ovakva struktura međurezultata je daleko fleksibilnija, jer su sve formule direktno osetljive na promenu dimenzija polaznih podataka. Štaviše, ovde se oslanjam i na izvesnu inteligentnost programa kada dodam red (npr. podaci za subotu), gde ćete nakon ubacivanja reda i dodavanja podatka uočiti da se međurezultat prvog nivoa menja - zapravo, menja se referenca u formuli; to možete proveriti sami u datoteci sa primerom.

4

EXCEL NAPREDNI KURS O alatki za dobijanje automatskih međuzbirova govorićemo drugom prigodom, a tada ćete videti da Excel koristi upravo funkciju SUBTOTAL da bi što fleksibilnije pružio međurezultate u željenom obliku. A kad smo kod tog pojma "željeni oblik", hajde da vidimo šta znači prvi argument funkcije SUBTOTAL. Ako koristite Excel 2007, onda prilikom pisanja imate značajnu pomoć; ali, u svakom slučaju, u času pisanja možete kliknuti na dugme fx levo od polja za unos formula, što će vas odvesti u čarobnjak za pisanje funkcija; tu ćete pronaći i hipervezu ka sadržaju iz sistema pomoći...

Postoji 11 agregatnih funkcija u funkciji SUBTOTAL, koje prozivate određenim brojem: 1 - AVERAGE (prosek), 2 - COUNT (prebrojavanje numeričkih ćelija), 3 - COUNTA (prebrojavanje nepraznih ćelija), 4 MAX (maksimum), 5 - MIN (minimum), 6 - PRODUCT (proizvod), 7 - STDEV (standardna devijacija uzorka), 8 - STDEVP (standardna devijacija ukupne populacije podataka), 9 - SUM (zbir), 10 - VAR (varijansa uzorka) i 11 - VARP (varijansa ukupne populacije podataka). Agregatne funkcije su pobrojane abecednim redom, što je malo blesavo sa stanovišta frekventnosti upotrebe (SUM se koristi u barem 90% slučajeva), ali tako je to, sad je kasno da se išta menja. Pa dobro, upamtite makar taj broj 9 za najčešću funkciju. Kritično važan detalj: postoje dve varijacije prozivanja funkcija. To su brojevi 1-11 i brojevi 101-111. Naoko, rezultat je isti; međutim, funkcije prozvane brojem uvećanim za 100 su karakteristične po tome da će sadržaji u skrivenim ćelijama biti ignorisane. Nemojte se čuditi, to je veoma celishodna stvar. Recimo, agregatne funkcije koje se postavljaju na dnu eksplicitnih tabela (lista) se pišu baš tako. Kada filtrirate podatke u listi, videćete agregatne vrednosti samo za vidljivi opseg podataka. U opštem slučaju, kada funkciju SUBTOTAL dobijate kao rezultat rada neke napredne operacije u programu, Excel će postaviti pravilan oblik za prozivanje funkcije; ako koristite funkciju sami, pripazite da ne pogrešite. Primera radi, situacija na četvrtoj slici sadrži skrivene redove. Sve je u redu baš zato što sam koristio broj 9 kao prvi argument; da sam upotrebio sabiranje prozivajući ga brojem 109, posle sakrivanja redova bih dobio netačne rezultate!

5

6

EXCEL NAPREDNI KURS COUNTIF(range;criteria) Range predstavlja opseg ćelija od kojeg želite da prebrojavate ćelije. Criteria predstavljaju kriterijume u obliku broja, izraza ili teksta koji definišu koje ćelije će biti prebrojavane. Na primer, kriterijumi mogu biti izraženi kao 32, "32", ">32", "jabuke". Primedba Microsoft Excel obezbeđuje dodatne funkcije koje mogu da se koriste za analizu vaših podataka u zavisnosti od nekog uslova. Na primer, da biste izračunali sumu zasnovanu na niski teksta ili na broju unutar niza, upotrebite funkciju za radni list SUMIF. Da bi formula dala jednu ili dve vrednosti zasnovane na nekom uslovu, na primer na udeo u dobiti u zavisnosti od nivoa prodaje, upotrebite funkciju IF. A

B

1

Podaci

Podaci

2

jabuke

32

3

pomorandže

54

4

breskve

75

5

jabuke

86

Formula

Opis (rezultat)

=COUNTIF(A2:A5;"jabuke")

Broj ćelija sa jabukama u prvoj gornjoj koloni (2)

=COUNTIF(B2:B5;">55")

Broj ćelija sa vrednošću većom od 55 u drugoj gornjoj koloni (2)

EXCEL NAPREDNI KURS

SumIf

Za sumiranje podataka u Excel-ovom radnom listu mogu se koristiti dve funkcije: "SUM" i "SUMIF". Kao što smo u prethodnim prilozima obradili funkciju "SUM", do ove funkcije dolazimo unošenjem formule: =SUM (blok celija) ili preko dugmeta "AutoSum" na Standard paleti. Druga funkcija, "SUMIF", se koristi ukoliko želimo da sumiramo podatke iz određenih ćelija, tj. vršimo selektivno sumiranje po određenom kriterijumu. Funkcija izgleda ovako: =SUMIF (blok celija, kriterijum, podrucje sumiranja) gde je "blok celija" skup ćelija koje se proveravaju da li zadovoljavaju određeni "kriterijum", i čije će se vrednosti iz područja "podrucje sumiranja", sumirati. U sledećem primeru videćemo kako se radi sa ovom funkcijom. U Excel-ov radni list unesite podatke kao na slici, znači, imamo neke magacine u kojima se nalaze neki artikli, i to u određenoj količini. Prvo ćemo imenovati blok ćelija od C4 do C9, i to imenom "Artikli". Ovo ćemo uraditi tako što selektujemo dati blok ćelija i pozovemo padajući meni "Insert -> Name -> Define...", i u sledećem dijalog prozoru unesemo u polje "Names in workbook" naziv datog bloka ćelija. Ovo isto ćemo uraditi i za blok od D4 do D9, koji ćemo nazvati "Kolicine". U ćeliji D11 (Uslovni zbir) izvršićemo uslovno sumiranje. Napisaćemo sledeću formulu: =SUMIF ( Artikli ; "=Sok" ; Kolicine) Šta će desiti, izvršiće se sumiranje podataka iz bloka "Kolicine", i to onih koji u bloku ćelija "Artikli" sadrže naziv "Sok". Znači na ovaj način smo sumirali sve "Sokove", iz svih magacina (od 1 do 6) i dobili ukupno stanje artikla "Sok" na "lageru".

7

8

EXCEL NAPREDNI KURS

Filteri Postavljanje filtera je jednostavan i brz nacin da se pronadje i radi sa dijelom podataka koji su navedeni u tabeli.Filter lista pokazuje samo redove koji zadovoljavaju kriterijume koje ste naznacili za kolonu.Excel omogucuje komande za dvije filter liste: · Auto filter koji ukljucuje biranje · Napredni filter za kompleksne kriterijume

po

izboru

za

jednostavnije

kriterijume

Za razliku od sortiranja filtriranje ne preuredjuje tabelu.Filtriranje privremeno sakriva listu koju, zbog lakseg snalazenja, ne zelite prikazati. Kada Excel filtrira redove mozete editovati,formatirati i stampati podtabelu bez preuredjivanja. Osnovni auto filter Kada koristote autofilter komandu pojavljuju se autofilter strelice na desnoj strani naslovnih celija kolona. Kako filtrirati listu ?

Koristenje autofilter strelica Klikom misa na autofilter strelicu dobijamo listu jedinstvenih vidljivih objekata u koloni ukljucujuci i praznine.Biranjem objekta iz liste mozete odmah sakriti sve redove koji ne sadrze selektovanu vrijednost.Lista u ovom primjeru je filtrirana tako da pokaze podatke samo za prodavca Pajica.

Brzo filtriranje vrijednosti Ako filtrirate listu brojeva mozete brzo pogledati najvece vrijednosti u tabeli biranjem TOP 10 objekta u autofilter listi.Biranjem objekta All ponovo cete vratiti tabelu na staro stanje ( svi redovi ).

EXCEL NAPREDNI KURS Napomena MC Excel oznacava filtrirane objekte nekim vizuelnim tragovima. U nasem primjeru, autofilter strelica, kolone koja je selektovana, je plava.

Uobicajeni autofilter Koristi se da bi se pokazali redovi koji sadrze ili jednu ili drugu vrijednost. Takodje, mozete koristiti uobicajeni autofilter da prikazete redove koji odgovaraju na vise uslova u jednoj koloni kao sto su redovi kji sadrze vrijednosti u okviru odredjenog opsega, npr. vrijednosti izmedju 2000 i 3000. Lista u ovom primjeru je filtrirana da pokaze redove koji sadrze "Pajic" i "Grujicic" u koloni PRODAVAC.

9

10

EXCEL NAPREDNI KURS Opcije automatskog filtera:

Opis

Klikni

Pokazuje sve redove

All

Pokazuje sve redove koji ulaze u okvire Granice (donje ili gornje) koje ste postavili , bilo po Top 10 vrijednostima bilo po procentu.Npr.kolicine koje spadaju u najvecih 10. Primjenjuje da kriterija u okviru tekuce kolone ili koristi opratore poredjenja druge nego AND ( Custom operator po difoltu) Prikazuje samo redove koji sadrze celiju u datoj Blanks koloni Prikazuje samo celije koje sadrze upisane Nonblanks vrijednosti

EXCEL NAPREDNI KURS Filtriranje selekcijom (kao Custom) Dok gledate veliku listu, mozete otici na red koji sadrzi podatke od interesa a onda pozeljeti da vidite bilo koji red koji sadrzi iste podatke.Mozete filtrirati listu da dobijete sve redove koji sadrze iste vrijednosti kao i ona u selektovanoj celiji. Filtriranje: Oznacite celiju u tabeli a onda kliknite AutoFilter da filtrirate po izboru. Napredni filter

Mozete koristiti napredni filter ako zelite primjeniti vise kriterija na vise kolona ili ako zelite primjeniti kriterijum koji je rezultat formule Kako filtrirati listu koristeci napredni filter (Advanced filter) Kad koristite napredni filter, strelice padajuceg menija se ne pojavljuju. Umjesto toga unosite kriterijume u opseg kriterijuma (Criteria range) na vasoj radnoj povrsini. Ukoliko zelite da promjenite nacin na koji je tabela filtrirana morate unijeti nove vrijednosti u opsegu kriterijuma Na primjer ova tabela je filtrirana da pokaze one prodavce koji su imali vise od 40 a manje od 70 radnih dana

11

EXCEL NAPREDNI KURS

Popuna padajuće liste Da vas podsetim: da biste napravili padajuću listu u ćeliji u Excelu 2003, pozovite dijalog Data / Validation (Podaci / Proveri valjanost), pa na prvoj kartici dijaloga, u listi za izbor odaberite opciju List; tada u polju za izvorne podatke (source) upišite ili obeležite opseg poželjnih vrednosti. Koincidentno, postupak u Excelu 2007 ima istu putanju u glavnoj traci. Sadržaj u padajućoj listi će biti sortiran u istom redosledu kao i izvor, pa ponovo sortirajte polazni šifarnik, ako je potrebno.

Validacija podataka (Data Validation)

Ograničenje za unos brojeva, datuma i vremena Ako želite da ograničeite broj stavki na najviše pet po jednom nalogu, uradite sledeće: selektujte ćeliju D4,zatim selektujte cijelu kolonu "Redni broj" (pritisnite kombinaciju tastera Ctrl+Shift+↓), izaberite komandu Data - Validation. Izaberite karticu Settings, iz padajuće liste Allow izaberite stavku Whole number. Aktiviraće se polje Data, u kojem će biti upisana stavka between, a ispod toga polje pojaviće se dva polja za unos vrijednosti, Minimum i Maximum. Izbor stavke Whole number znači da ste se oredijelili da uvedete ograničenje pri unosu brojeva. kliknite u polje Minimum i upišite broj 1. Polje Minimum služi za definisanje najmanje vrijednosti koju može imati selektovana ćelija ili opseg, kliknite u polje Maximum i upišite broj 5. Polje Maximum služi za definisanje najveće vrijednosti koju može imati selektovana ćelija ili opseg. Kliknite na dugme OK. Na ovaj način ste uveli ograničenje za unos brojeva u kolonu Redni broj.

12

EXCEL NAPREDNI KURS

Kreiranje imena Najjednostavniji način za kreiranje imena je korištenjem Name Box-a. Name Box se nalazi lijevo od Formula Bar-a i u njemu se obično vidi adresa aktivne ćelije. Ukoliko hoćemo definirati ime za neki raspon ili ćeliju, prvo ih selektiramo (npr. A1), kliknemo u Name Box i upišemo ime koje želimo (npr. proba), te zatim pritisnemo ENTER. Ako sada kliknemo na simbol koji se nalazi uz Name Box pojavit će se dropdown lista s našim imenom.

Ako ga kliknemo, Excel će selektirati raspon na koji se naše ime odnosi (A1). Bilo gdje da smo u workbooku selekcijom imena u Name Box-u Excel nas vodi na taj raspon, tako da nam imena mogu poslužiti za brzu navigaciju kroz workbook. Drugi put za definiranje imena je putem menija Insert -> Name -> Define... U Define Names dijalogu tada upišemo željeno ime u Names in workbook: box, te selektiramo ili upišemo željeni raspon u Refers to: box.

U Define Names dijalogu su također prikazana sva postojeća imena u workbooku i ako kliknemo na neki od njih, u Refers to: boxu će se pojaviti referenca na koju se odnosi. Putem Define Names dijaloga možemo još i dodavati, ili brisati imena i mijenjati im reference. S imenima možemo najnormalnije računati i koristiti ih u formulama. Recimo da raspon A2:A5 kojeg smo nazvali prodaja sadrži podatke o prodaji za 4 kvartala, , godišnji iznos prodaje dobit ćemo formulom =SUM(prodaja)

13

EXCEL NAPREDNI KURS

Rad sa datumima

Ime funkcije

Koristi se za pronalaženje

NETWORKDAYS

neto broja radnih dana između dva datuma

WORKDAY

datuma koji je određen broj radnih dana nakon drugog datuma

DATE

datuma koji pada navedeni broj godina, meseci ili dana nakon drugog datuma

TODAY

trenutnog datuma

Pronađite broj dana između dva datuma

1. Formula u radnom listu 2. Rezultat formule 3. Formula u polju za formulu Zamislite da je danas 9.jun 2004. Vaš odmor počinje 21.avgusta 2004. i želite da saznate koliko ima dana do odmora. Da biste izračunali, otkucaćete jednostavnu formulu u praznu ćeliju (u ovom primeru, ćelija B5):

=A3-A2

14

EXCEL NAPREDNI KURS Ima 73 dana između ova dva datuma. Možete da koristite datume u formuli jer Excel prepoznaje vrednosti u ćelijama A2 i A3 kao datume po njihovim formatima i skladišti ih kao serijske brojeve. Excel oduzima serijski broj za 09.06.2004. od serijskog broja za 21.08.2004: 38220 minus 38147 jednako je 73. Dok kucate, znak jednakosti (=) saopštava programu Excel da je ovo formula. Znak minus, tj. operator (-), saopštava programu Excel da oduzme jednu vrednost od druge. A3 i A2 su reference ćelija koje se odnose na vrednosti u tim ćelijama. Ako se kasnije budete pitali kako ste dobili ovaj rezultat, formula je vidljiva u polju za formulu blizu vrha radnog lista svaki put kada je izabrana ćelija koju ste koristili. Takođe možete da kliknete dva puta na ćeliju da biste videli formulu u ćeliji. Napomena 73 kao rezultat formule će se prvo pojaviti u formatu datuma, zato što su vrednosti u A2 i A3 u formatu datuma. U sesiji za vežbanje na kraju lekcije videćete kako da brzo ponovo oblikujete rezultat, tako da se pojavljuje kao broj.

Pronađite broj radnih dana između dva datuma

1. Formula u radnom listu 2. Rezultat formule 3. Formula u polju za formulu Pronalaženje broja radnih dana (od ponedeljka do petka) između dva datuma se ne može uraditi jednostavnim oduzimanjem jednog datuma od drugog. To zahteva prethodno napisanu formulu koja se zove NETWORKDAYS funkcija, koja računa neto broj radnih dana između dva datuma. I dalje je 09.06 .2004. a vaš odmor i dalje počinje 21.08.2004, ali sada želite da saznate koliko ima radnih dana do odmora. Otkucaćete formulu u praznu ćeliju: =NETWORKDAYS(A2,A3) Ima 53 radna dana do odmora. Vrednosti u ćelijama A2 i A3 su argumenti, informacije koje saopštavaju funkciji NETWORKDAYS šta da računa. Zagrade odvajaju funkciju od argumenata. U okviru zagrada, zarez međusobno odvaja argumente.

15

EXCEL NAPREDNI KURS

Pronađite datum nakon više radnih dana

1. Formula u radnom listu 2. Rezultat formule 3. Formula u polju za formulu Pretpostavimo da je potrebno da pronađete datum, kao što je krajnji rok fakture ili datum završetka projekta, koji se pojavljuje u više radnih dana (od ponedeljka do petka) pre ili posle određenog datuma. To je lako: koristite funkciju WORKDAY.

Zamislite da imate 80 radnih dana tokom kojih treba da dovršite projekat koji počinje 31.12.2004. a potrebno je da nađete datum završetka projekta. Prvo biste uneli te vrednosti u ćelije A2 i A3, kao što je prikazano na slici. Zatim, uneli biste datume svih praznika koji mogu da utiču na zbir, svaki praznik u sopstvenu ćeliju. Zatim biste otkucali formulu u praznu ćeliju:

=WORKDAY(A2,A3,A4:A6)

Projekat mora biti dovršen do 26.04.05. Excel je dodao 80 dana (ćelija A3) na 31.12.2004. (ćelija A2), automatski isključujući vikende kao i praznike u periodu navedenom u argumentu A4:A6 (dve tačke označavaju opseg ćelija). Zarez odvaja svaki individualni argument. Zagrade odvajaju argumente od funkcije.

16

EXCEL NAPREDNI KURS

Pronađite datum nakon više meseci

1. Formula u radnom listu 2. Rezultat formule 3. Formula u polju za formulu Recimo da imate 25 meseci od 09.06.2007. da dovršite projekat i potrebno je da pronađete datum završetka projekta. To možete da uradite pomoću DATE funkcije. Funkcija DATE ima tri argumenta: godinu, mesec i dan. Nakon što unesete 25 u ćeliju B2, otkucaćete ovu formulu u ćeliju A4: =DATE(2007,6+B2,9) 2007 je argument godine, 6 i vrednost u ćeliji B2 su argument meseca, 9 je argument dana. Zarezi razdvajaju argumente, a zagrade obuhvataju sve argumente. Projekat se završava 09.07.2009. Zato što ste otkucali referencu ćelije (B2) umesto vrednosti u toj ćeliji (25), Excel može automatski da ažurira rezultat ako se vrednost promeni. Na primer, ako se vremenski period promeni sa 25 meseca na 23 meseca, mogli biste da dobijete ažurirani datum 09.05.2009. tako što ćete promeniti vrednost u ćeliji B2 sa 25 na 23, bez ponovnog kucanja formule.

17

18

EXCEL NAPREDNI KURS

Pronađite datum nakon više godina, meseci i dana

1. Formula u radnom listu 2. Rezultat formule 3. Formula u polju za formulu Sada zamislite da vam je data 1 godina, 7 meseci i 5 dana, od 9. juna 2007. da završite projekat i da je potrebno da pronađete datum završetka projekta. Ponovo biste koristili funkciju DATE, tako što biste uneli te vrednosti u ćelije kao što je prikazano na slici i zatim biste otkucali ovu formulu u ćeliju A6:

=DATE(2007+B2,6+B3,9+B4)

2007 i vrednost u ćeliji B2 su argument godine, 6 i vrednost u B3 su argument meseca, 9 i vrednost u B4 su argument dana. Projekat se završava 14.01.2009. Kao i u poslednjoj formuli, zato što ste otkucali reference ćelija B2, B3 i B4 umesto samih vrednosti, Excel može automatski da ažurira rezultat ako se te vrednosti promene. Ako vam je dato manje vremena da dovršite projekat, Excel će automatski ažurirati rezultat kada promenite vrednost u ćelijama B2, B3 ili B4. Automatsko stvaranje rezervnih kopija Predstavljamo vam

Excel vam moze automatski stvarati duplikat vase datoteke na istom disku i unutar istog direktorija u kojem se nalazi original. Odaberite naredbu Save As i kliknite na dugme Options kako biste prikazali okvir za dijalog Save Options, pokazan na slici. Zatim odaberite opciju Always Create Backup.

EXCEL NAPREDNI KURS Rezervna kopija datoteke je duplikat koji nosi jednako ime kao i originalna datoteka, ali je na pocetku dodana sekvenca “Backup of”, a nastavak je .xlk. Ako ste datoteku spremili po prvi put, rezervna kopija i radna biljeznica su identicne. Ako ste datoteku vec prije spremali uz odabranu opciju Always Create Backup, Excel mijenja ime zadnje spremljene verzije datoteke, dajuci joj nastavak .xlk, i prepisuje sadrzaj prethodne .xlk datoteke. Zastita datoteka Svoje datoteke mozete zastititi s dvije vrste lozinki: zastitnom ili samo za citanje. Lozinke mogu sadrzavati do 15 znakova, a razlikuju se mala i velika slova. Stoga, ako datoteci pridruzite lozinku Tajna, ne mozete je ponovno otvoriti upisujuci TAJNA ili tajna. Zbog dodatne sigurnosti, pri unosenju lozinke zasticene datoteke, Excel ne prikazuje znakove na ekranu. Da biste svojim datotekama pridruzili bilo koju od ovih zastita, postupite ovako: 1. Iz menija File odaberite naredbu Save As I , ako je potrebno, otkucaj te ime u polju File Name. 2. Kliknite na dugme Options 3. Odaberite opcije koje zelite i, ako je potrebno, unesite lozinke. 4. Kliknite na OK kako biste zatvorili okvir za dijalog Save Options. 5. Ponovno utkucajte lozinku u okviru za dijalog Confirm Password i kliknite na OK. 6. Kliknite na Save kako biste zatvorili okvir za dijalog Save As.

Zastita vasih podataka Uz mogucnost zastite vasih datoteka putem lozinke, Excel nudi i nekoliko naredbi kojima mozete zastititi vase radne stranice, radne biljeznice, poje- dine celije, graficke objekte, grafove, scenarije i prozore od pristupa i promjene od strane drugih korisnika. Excel prema osnovnoj postavi “zakljucava” (stiti) celije, graficke objekte, grafove, scenarije i prozore, ali zastita nije djelotvorna dok iz menija Tools ne odaberete Protection, a zatim Protect Sheet. Slika pokazuje okvir za dijalog koji se pojavljuje nakon izvo|enja navedene naredbe. Status zastite koji odredite odnosi se samo na trenutnu radnu stranicu.

Nakon postavljanja zastite vise niste u stanju promijeniti sticeni element. Ako pokusate promijeniti zasticenu celiju, Excel prikazuje poruku o pogresci Locked cells cannot be changed.

19

EXCEL NAPREDNI KURS

Skidanje zastite s pojedinih celija U novoj radnoj stranici svaka celija je prema osnovnoj postavci zakljucana i spremna za aktiviranje naredbom Protect Sheet. Ali, u najvecem broju slucajeva necete zeljeti zastititi sve celije radne stranice. Mozda cete htjeti u radnoj stranici koju dijelite zastititi formule i format a pojedine celije ostaviti nezasticene tako da se potrebni podaci mogu unijeti bez otkljucavanja citave stranice. Prije zastite radne stranice, odaberite celije koje zelite drzati nezasticenima, a zatim iz menija Format odaberite naredbu Cells i kliknite na Protection. Ponistite opciju Locked. Slika pokazuje okvir za dijalog nakon odabira Protection naredbe Format Cells.

Mozete upotrijebiti i naredbu Protect Workbook podmenija Protection iz menija Tools. Na taj nacin mozete sprijeciti promjenu strukture radne biljeznice, te fiksirati polozaj prozora same radne biljeznice. Okvir za dijalog koji se pojavljuje nakon pokretanja naredbe Protect Workbook prikazan je na slici.

Unos lozinki U okviru za dijalog koji se pojavljuje nakon odabira Protect Sheet ili Protect Workbook iz menija Tools, mozete pridruziti lozinku koja se mora ukucate kako bi se prosla zastita radne stranice ili radne biljeznice. (Mozete upotrijebiti i razlicite lozinke za svaku od njih.) Da biste pridruzili lozinku, ucinite sljedece: 1. Iz menija Tools odaberite Protection, a zatim Protect Sheet ili Protect Workbook. 2. Upisite lozinku i kliknite na OK. 3. Kada se to od vas zatrazi, ponovo upisite lozinku i kliknite na OK kako biste se vratili u radnu stranicu ili radnu biljeznicu.

20

21

EXCEL NAPREDNI KURS

Uklanjanje zastite Ako je na radnoj stranici ili radnoj biljeznici postavljena zastita, tada se naredba Protection u meniju Tools mijenja u naredbu Unprotect Sheet, odnosno Unprotect Workbook, ili obje. Da biste uklonili zastitu, odaberite odgovarajucu naredbu Unprotect. Ako ste pri postavljanju zastite radne stranice ili radne biljeznice primijenili lozinku, zastitu ne mozete ukloniti dok ne utipkate lozinku. Unos krive lozinke rezultira porukom The Password you supplied is not correct. Verify the CAPS lock key is off and that you used the correct capitallization. Lozinka koju kucate mora u potpunosti odgovarati lozinci radne stranice ili radne biljeznice, sto znaci da se moraju poklopiti i mala i velika slova.

Ugnijezdene funkcije IF Ponekad ne mozete rijesiti logicki problem upotrebljavajuci samo logicke operatore i funkcije AND, OR i NOT. U takvim slucajevima mozete ugnijezditi funkcije IF kako biste stvorili hijerarhiju uvjeta. Primjer, formula =IF(A1=100,”Uvijek”,IF(AND(A1>=80,A1<100),”Obicno”, briga?”)))

IF(AND(A1>=60,A1<80),

“Ponekad”,”Koga

upotrebljava tri zasebne funkcije IF. Ako je vrijednost u celiji A1 uvijek cijeli broj, formula se moze citati kao: Ako je vrijednost celije A1 jednaka 100, vrati niz Uvijek, inace, ako je vrijednost u celiji A1 izmedju 80 i 100, vrati niz Obicno; ako je vrijednost u celiji A1 izmdju 60 i 80, vrati niz Ponekad, te napokon, ako niti jedan od ovih uvjeta nije ispunjen, vrati niz Koga briga? Ugnijezditi mozete do 7 funkcija IF sve dok postujete ogranicenje od 255 znakova za pojedinacni unos u celiji.

Primjena uvjetnog formatiranja Moze li formatiranje biti poput alarma? Moze, radi se o uvjetnom formati-ranju. Uvjetno vam formatiranje omogucava primjenu formata koji su u prikazu nevidljivi (“spavaju”) sve dok vrijednosti u celijama ne dostignu zadane vrijednosti. Ovakav nacin formatiranja nazivamo “hvatanjem” vrijednosti. Primjerice, mogli biste postaviti uvjetni format na celije koje pokazuju rezultate prodaje tako da se format promijeni ako u bilo kojoj celiji prodaja padne ispod 10 000 kn. Odaberite celije koje zelite formatirati, a zatim iz menija Format pokrenite naredbu Conditional Formatting. Pojavit ce se okvir za dijalog istog imena, pokazan na slici 7-28.

Prvi padajuci popis ovog okvira za dijalog omogucava vam zadati na sto ce se uvjet odnositi – vrijednosti u celijama ili samu formulu. Obicno cete odabrati opciju Cell Value Is koja ce primijeniti format na temelju vrijednos- ti u celiji (drugu opciju, Formula Is, upotrijebit cete jedino kada se formula sama mijenja na temelju drugih vrijednosti u radnoj stranici; ako je tome tako, tocnu cete formulu unijeti u drugo polje). Drugi popis odnosi se na uvjet usporedbe kojim zadajete treba li vrijednost celije biti manja, manja ili jednaka, jednaka, veca, veca ili jednaka, unutar ili izvan zadanog intervala. Trece (i cetvrto) polje koristit cete za unos vrijednosti prema kojoj se uspore|uje vrijednost u celiji. Normalno je prikazano samo jedno polje, sto pokazuje slika 7-30 na prethodnoj stranici, ali ako iz

EXCEL NAPREDNI KURS drugog popisa odaberete Between ili Not Between (unutar ili izvan intervala) tada ce se pojaviti i drugo polje pa su vam za zadavanje uvjeta potrebne dvije vrijed- nosti; gornja i donja granica; u suprotnom se pojavljuje samo jedno polje, kao na slici . Nakon zadavanja uvjeta kliknite na dugme Format. Pojavit ce se okvir za dijalog Format Cells. Sada zadajte zeljenu kombinaciju pisma, ruba i uzorka koju namjeravate primijeniti. Primjerice, na slici , ako vrijednost u celiji padne ispod 10 000, celija dobiva crvenu boju pozadine, crni rub i masno pismo. Primijetite da je citav stupac F koji sadrzi mjesecne brojke o prodaji jednako uvjetno formatiran, ali samo celija F7 odskace prikazom. Primjena se formata ponistava cim vrijednost u celiji poraste iznad 10000. Celiji ili rasponu mozete istovremeno pridruziti do tri razlicita uvjeta. Da biste to ucinili, kliknite na dugme Add. Primjerice, ako istovremeno zelite oznaciti i dobre vijesti – prodaja prelazi 25 000 – tada kliknite na Add i istom rasponu zadajte i taj dodatni uvjet, sto je pokazano na slici.

Da biste uklonili uvjetne formate, odaberite celiju ili raspon i zatim pokre- nite naredbu Conditional Formatting iz menija Format. Tada kliknite na dugme Delete. Pojavit ce se okvir za dijalog Delete Conditional Formatting sa slike dolje. Odaberite uvjet(e) koje zelite ukloniti i kliknite na OK. Uvjet ce nestati iz prikaza, ali jos se uvijek mozete predomisliti tako da kliknete na dugme Cancel. Uvjet nije uklonjen sve dok ne kliknete na OK u okviru za dijalog Conditional Formatting.

Naredba Goal Seek Naredbom Goal Seek menija Tools mozete izracunati nepoznatu vrijednost koja daje zeljeni rezultat. (Ova naredba se normalno ne pojavljuje na skracenim menijima. Kliknite na dvostruku strelicu na dnu menija Tools da biste je prikazali.) Primjerice, pretpostavimo da zelite znati najveci iznos 30-godisnjeg kredita koji mozete otplatiti uz kamatnu stopu od 6.5%, te ako su vam mjesecne otplate granicene na 2000$. Da biste upotrijebili naredbu Goal Seek za odgovor na ovo pitanje, slijedite naredne korake: 1. Postavite problem s probnim vrijednostima. Na primjer, u problemu najveceg iznosa pokazanom na slici dolje, kredit od 500 000$ zahtijevao bi mjesecne otplate vece od 2000$. Da biste definirali imena za celije B1:B4, odaberite celije A1:B4 i pokrenite naredbu Name iz menija Insert, a zatim odaberite Create. Potvrdite opciju Left Column i kliknite na OK.

22

EXCEL NAPREDNI KURS

2. Aktivirajte celiju s formulom – u ovom slucaju B4. (Ovo nije sasvim neophodno, ali pojednostavnjuje postupak.) 3. Iz menija Tools pokrenite naredbu Goal Seek. (Ova naredba se normalno ne pojavljuje na skracenim menijima. Kliknite na dvostruku strelicu na dnu menija Tools da biste je prikazali.) 4. U okviru za dijalog Goal Seek, pokazanom na slici 16-22, prihvatite vrijednost u polju Set Cell (apsolutna adresa aktivne celije).

Popunjavanje područja teksturom ili slikom Ako vam se ne sviđaju čiste boje, prijelazi ili uzorci, kako bi bilo da svoja područja popunite slikama? Slike možete upotrijebiti iz niza podržanih grafičkih formata, a mogli biste iskoristiti i jednostavne teksture koje nudi Excel. Teksture imitiraju poznate materijale, poput hrasta, mramora i slično. Slika pokazuje teksturu “fosila ribe” primijenjenu na područje iscrtavanja dijagrama.

Da biste na područje primijenili teksturu, kliknite na dugme Fill Effects na desnoj strani kartice Patterns i zatim kliknite na jahač Texture okvira za dijalog Fill Effects. Rezultat je prikazan na slici.

23

EXCEL NAPREDNI KURS

Da biste na područje postavili sliku, kliknite na Fill Effects i zatim na karticu Picture okvira za dijalog Fill Effects. Rezultat je prikazan na slici ispod

Ako sliku postavite na marker (a ne na pozadinsko područje), možete odabrati između tri opcije formatiranja. Osnovno postavljena opcija, Stretch, prikazuje jednu kopiju slike i po potrebi je smanjuje ili povecava kako bi popunila marker. Opcija Stack postavlja onoliko kopija slika koliko je potrebno da se popuni čitav marker, a slike pritom zadržavaju polaznu veličinu. Opcija Stack and Scale omogucavaju da svaka kopija slike predstavlja neki broj jedinica na osi vrijednosti.

24

EXCEL NAPREDNI KURS

Stampanje i prijelom stranice Za stampanje tablica i grafikona na papir, pogledajte ovo poglavlje. Ovdje ćemo objasniti kako se određuje veličina stranice, margine, zaglavlja i podnožja, njena orijentacija i skaliranje, te poredak stranica. Nakon što napravite tablice i grafikone u Excel -u, vrlo vjerovatno ćete ih htjeti stampanje na papir. Pregled dokumenata na kompjuteru je vrlo naporan, te se zbog toga preporuča njihov stampanje na stampacu. Za razliku od worda, gdje je stranica na ekranu identična listu papira, ovdje radni list može imati desetke stranica udesno i prema dolje. Kako stampanjeati tablice, a da se pritom ne dogodi njihovo cijepanje, te da budu pregledne i oku ugodne? Nema smisla napraviti tablice i prebacivati ih u Word radi stampanja, sve se može bez problema napraviti u Excel – u. Sam stampanje se sastoji od tri koraka:
Postavljanje stranice za stampanje (dijaloški okvir Page Setup)
Pretpregled stampanjea (Print Preview)
Stampanje dokumenta korištenjem naredbe Print Podrazumijeva se da vaše racunar ima instaliran stampac, inače ništa od ovoga neće raditi. Podešavanje stampaca se obavlja u Control Panelu.

25

EXCEL NAPREDNI KURS

Primjer: Napravite slijedeću tablicu u Excel – u i pripadajući graf. Datoteku snimite pod nazivom "Stampanje.xls" na vaše racunar. Imamo četiri države i pripadajuće im prihode odnosno rashode. Dobit se računa kao Prihodi minus Rashodi, a udjeli kao dio / ukupno. Graf prikazuje Udjele u dobiti.. Pitanje je hoće li jedna ovako raširena tablica te veliki grafikon stati na jedan list papira, formata A4.

26

27

EXCEL NAPREDNI KURS

Postavljanje stranice (Page Setup) Prvi korak je priprema stranice za stampanje, što se postiže odabirom opcije Page Setup iz padajućeg menija File. Otvara se dijaloški okvir koji sadrži četiri rubrike: Page, Margins, Header / Footer, Sheet.

Opcije stranice

1

2

3

4

Legenda: 1 – Orijentacija papira. Ovisno kako želite da vam bude postavljen papir, odaberite jednu od ove dvije opcije: a. Portrait – uspravno orijentirana stranica, standardno uključena opcija b. Landscape – položeno orijentirana stranica. Ako nemate dovoljno mjesta na papiru za vaše tablice, uvijek možete okrenuti papir. Za radne listove grafikona je ovo standardno uključena opcija. 2 – Skaliranje. U slučaju da vaše tablice ne stanu na jedan list papira, nego ih Excel i dalje siječe ili želite odrediti na koliko će točno stranica izaći vaša tablica, podatke možete prilagoditi (skalirati). Smanjivanjem postotka smanjuje se i veličina svih podataka na radnom listu (početna vrijednost je 100 %). Možete i odrediti i na koliko će točno listova papira izaći vaš dokument.

28

EXCEL NAPREDNI KURS 3 – Veličina papira i kvaliteta stampanjea. Želite li promijeniti veličinu papira, odaberite ovu opciju. Veličine papira i kvaliteta stampanjea ovise o vrsti printera koju imate instalirane na vaše racunar. 4 – Početni broj stranice. Po želji taj broj možete promijeniti, u početku je postavljen na broj jedan. Opcije margine i centriranja Druga rubrika vam omogućuje postavljanje margina i postavljanje vaših tablica i grafikona na sredinu papira. Ponekad vam ta dva centimetra koja dobijete smanjenjem praznog obruba oko lista papira mogu pomoći da lijepo smjestite svoje podatke.

1

2

Legenda: 1 – Prostor za uređivanje razmaka između ruba papira i sadržaja dokumenta. Požete podesiti vrijednosti za sve četiri margine (gore, dolje, lijevo, desno), te vrijednosti koje određuju razmak između ruba papira i zaglavlja/podnožja. 2 – Centriranje podataka na stanici. Dokumenti mogu izgledati jako ružno ako se nalaze stisnuti u gornjem lijevom kutu papira, a okolo prazan papir. Zato je potrebno podatke centrirati na sredinu lista prije stampanjea. Pri tome dolazi samo do grafičke promjene, Excel ne premješta podatke po radnom listu i nema promjena adresa. a. Horizontalno – centrira sadržaj dokumenta između lijeve i desne margine b. Vertikalno – centrira sadržaj između gornje i donje margine

29

EXCEL NAPREDNI KURS Savjet: neki stampaci ne mogu stampanjeivati uz sam rub papira. U slučaju da je neki dio dokumetna pri stampanjeu odsječen, povećajte margine. Nemojte marginama dodijeliti manje vrijednosti nego zaglavlju i podnožju jer će Excel stampanjeati dokument preko zaglavlja ili podnožja.

Zaglavlje i podnožje (Header/Footer) Na svakom listu papira možete imati predefinirano zaglavlje ili podnožje (kao i u Wordu.). Meniji Header (zaglavlje) i Footer (podnožje) su identični. Možete raditi na dva načina, izabrati već predefinirana zaglavlja ili podnožja, ili ići raditi vlastito.

1

2

3

4 Legenda: 1- Područje uređenja zaglavlja 2 – Ako želite urediti vlastito zaglavlje ili podnožje, odaberite opciju Custom Header/Footer 3 – Područje uređenja podnožja 4 – Predefinirana zaglavlja i podnožja

30

EXCEL NAPREDNI KURS

Kada odaberete opciju Custom Header ili Footer, dobijete gornji meni, uz pomoć kojega radite vlastito zaglavlje ili podnožje. Meni je podijeljen na dva dijela, u gornjem dijelu odabirete što posebno želite ubaciti (broj stranice, naziv datoteke, slika), a u donjem dijelu prvo birate poziciju (lijevo, desno, sredina) te pišete ručno tekst.

Opcije radnog lista (Sheet) Ova kartica određuje opcije samog radnog lista koji se šalje na stampanje. Čak i kad namjestite dokument da se stampanjeuje na više listova koje ste okrenuli, tablica bez naziva kolone ili reda na drugom ili trećem listu papira može biti vrlo problematična za razumijevanje.

1

2

3

4

EXCEL NAPREDNI KURS

Legenda: 1 – Ako želite stampanjeati samo dio nekog radnog lista, u okvir Područje stampanjea (Print area) unesite adresu raspona koji želite stampanjeati. 2 – Da biste na svakoj stranici dugačkog izvještaja prikazali naslove kolona ili redova, u okvire Nazivi redaka koje treba ponoviti (Rows to repeat at the top) ili Nazivi kolona koje treba ponoviti (Columns to repeat at left) unesite raspone redova ili kolona (ili oboje) čije naslove želite prikazati. 3 – Opcija Stampanje (Print) određuje:
Crte rešetke (Gridlines) – ako uključite ovu opciju stampanjeivat će se linije rešetki na radnom listu.
Crno – bijelo (Black and white) – crno – bijeli stampanje.
Kvaliteta skice (Draft Quality) stampanje na razini kvalitete skice, vrlo brzi stampanje i štednja boje na stampacu
Naslovi redaka i kolona (Row and column headings) – stampanjeuju se slova kolona i brojevi redova radnog lista 4 – Redoslijed stranica (Page order) – ova ipcija određuje kojim će se redpslijedom stampanjeivati vaše stranice. Stampanje može ići na dva načina:
Dolje, pa prijeko (Down, then over) – prvo se stampanjeuju stranice odozgora prema dolje, a onda se kreće udesno
Prijeko, pa dolje (Over, then down), prvo se stampanjeuju stranice udesno, a tek onda prema dolje

Pretpregled stampanjea (Print Preview) Nakon što ste postavili sve opcije, slijedi korak provjere. Pomoću pretpregleda stampanjea vidite kako će sve to izgledati na papiru. Poželjno je uvijek provjeriti izgled dokumenta prije nego što ga pošaljete na stampanje, jer onda stignete popraviti moguće nedostatke. Do pretpregleda se dolazi preko izbornika File, opcija Print Preview ili preko ikonice na toolbaru. Na slijedećoj slici vidite našu tablicu za vježbanje stampanjea u pogledu Print Preview.

31

32

EXCEL NAPREDNI KURS

3

1

2

Legenda: 1 – Print – ako ste zadovoljni s izgledom onoga što ćete dobiti, pošaljite dokument na stampanje. 2 – Margine – to su iscrtkane linije koje se mogu pomicati. Njihovim pomicanjem povećavate odnosno smanjujete radnu površinu vašeg dokumenta. Što su margine manje, više stane. 3 – Close – ako želite još nešto popraviti, odaberite opciju Close i vratite se u normalni pogled (način rada).

33

EXCEL NAPREDNI KURS Stampanje dokumenta Nakon čitavog posla slijedi stampanje na papir. Stampanje se može obaviti preko ikonice za stampanje na toolbaru ili preko naredbe Print koja se nalazi u padajućem izborniku File. Ako šaljete dokument na sipis preko ikonice, racunar će poslati sve na stampanje u jednom primjerku, a ako želite promijeniti neku od opcija, onda pokrenite slijedeći izbornik preko naredbe Print iz menija File

1

2

3

4

Legenda: 1 – Odabir stampaca na koji šaljete datoteku. Ako imate više štampača u mreži, možete poslati dokument na stampanje na neki drugi štampač koji nije zauzet u tom trenutku, a ne čekati red. 2 – Raspon stampanjea (Print Range) određuje što ćete poslati na stampanje. Možete odabrati sve ili samo neke stranice. 3 – Broj kopija koje šaljete na stampanje. 4 – Ispiši (Print what) određuje što ćete točno poslati na stampanje. Možete poslati:
Odabrano (Selection) stampa samo selektirane ćelije, listove ili objelte
Aktivne listove (Active sheets) stampa trenutačno selektirane listove
Cijela radna knjiga (Entire Workbook) stampa sve listove radne knjige koji nisu prazni

EXCEL NAPREDNI KURS

Stvaranje izvještaja pivot tabele Unesite sljedeću tabelu

Izvještaji pivot tabele i pivot grafikona (PivotTable and PivotChart reports) mogu vam pomoći pri mijenjanju načina prikaza podataka. Kada stvorite izvještaje, možete zamijenjiti redove i kolone tabele da biste promijenili način prikaza brojeva. Da biste stvorili izvještaj pivot tabele: 1. Kliknite selektirano područje podataka. 2. U imeniju Data (Podaci) odaberite PivotTable and PivotChart Report (Izvještaj zaokretne tabele i zaokretnog grafikona) da biste pokrenuli čarobnjak PivotTable and PivotChart (čarobnjak za zaokretne tabele i zaokretne grafikone).

34

EXCEL NAPREDNI KURS 3. Uključite opciju PivotTable (Zaokretna tablica), specificirajte izvor podataka i odredite kakvu vrstu izvještaja želite stvoriti. 4. Odredite položaj elemenata pivot tabele. Elementi koje možete umetnuti u pivot tabelu navedni su u prozoru PivotTable Field List (Popis polja zaokretne tabele).

5. Kliknite dugme Format Report (Oblikuj izvještaj) na alatnoj traci PivotTable (Zaokretna tabela). 6. Odaberite željeno automatsko oblikovanje u dijaloškom okviru AutoFormat (Samooblikovanje). 7. Kliknite Ok (U redu). 8. Povlačenjem polja iz Pivot Table Field List do željenog mjesta u tablici (Drop Row Fields Here, Drop Column Fields Here, Drop Page Fields Here, Drop Data Items Here) možemo dobiti različite tabele. Npr.

35

EXCEL NAPREDNI KURS

Napomena: Da biste iz pivot tabele stvorili grafikon, kliknite dugme ChartWizard (čarobnjak za grafikone) na alatnoj traci PivotTable (Zaokretna tabela).

Stvaranje izvještaja pivot grafikona Kada stvorite izvještaj pivot grafikona, Excel stvara i izvještaj pivot tabblice. Izvještaj pivot grafikona grafički predstavlja podatke, a u tablici i grafikonu možete zamijenjiti redove i kolone. Da biste stvorili izvještaj pivot grafikona:

36

EXCEL NAPREDNI KURS 1. Kliknite selektirano područje podataka. 2. Odaberite PivotTable and PivotChart Report (Izvještaj zaokretne tabele i zaokretnog grafikona) da biste pokrenuli čarobnjak PivotTable and PivotChart (čarobnjak za zaokretne tabele i zaokretne grafikone). 3. Uključite opciju PivotChart report (Izvještaj zaokretnog grafikona), odredite izvor podataka i naznačite hoćete li da izvještaj bude umetnut u novi ili postojeći radni list. 4. Odredite položaj elemenata grafikona. Elemente pivot grafikona oblikujete kao elemente bilo kojeg drugog grafikona.

37

38

EXCEL NAPREDNI KURS

BAZE PODATAKA I FILTRIRANJE BAZE PODATAKA Baza podataka=skup podataka organizovan tako da se lako mogu izdvojiti željene informacije Sastoji se iz zapisa i polja zapis = redovi polja = kolone Stvaranje i izmjene baze podataka: kao tabela ili preko: Data/Form Zadatak: Napraviti sljedeću bazu podataka (kolonu Racun izračunati). 1 impuls= Red. Br. 4 12 8 2 6 10 9 5 1 7 11 3

Prezime Goranovic Goranovic Goranovic Jankovic Jankovic Jankovic Markovic Markovic Markovic Petrovic Petrovic Petrovic

Ime Goran Marko Petar Janko Marko Petar Goran Janko Marko Goran Janko Petar

Telefon 213-489 625-593 632-345 211-789 632-657 54-987 212-228 243-900 212-537 623-980 211-980 212-321

Br. Impulsa 285 365 230 615 430 470 465 375 315 570 1280 125

Sortiranje baze podataka: Data / Sort Primjer: a) Sortirati tabelu po dva kljuca, prvo po abecednom redu prezimena, pa po abecednom redu imena. Rješenje: Data / Sort / Sort by: Prezime-ascending, Then by: ime-ascending b) Sortirati tabelu po velicini racuna tako da redosled bude od vecih ka manjim. Rješenje: Data / Sort / Sort by / Racun - Descending Filtriranje baze podataka: Data / Filter / Autofilter Primjer: a) Pomoću automatskog filtriranja (autofilter) izdvojiti samo Markovice (tj. izvršiti filtriranje tabele tako da se prikazu samo Markovici) Rješenje: Data / Filter / Autofilter / na Prezime:Markovic b) Prikazati samo one zapise sa računom većim od 100. Rješenje: Data / Filter / Autofilter / Racun/ Custom / is greater than 100 c) Prikazati samo one zapise sa računom većim od 70, a manjim od 130.

0,05 Racun 14,25 18,25 11,5 30,75 21,5 23,5 23,25 18,75 15,75 28,5 64 6,25 276,25

39

EXCEL NAPREDNI KURS Rješenje:

Data / Filter / Autofilter / Racun / Custom / is greater than 70 / AND / is less than 130

d) Prikazati račune koji su manji od 60 ili veći od 120. Rješenje: Data / Filter / Autofilter / Racun / Custom / is less than 60 / OR / is greater than 120

e) Prikazati Jankovice koji imaju račun veći od 90. Rješenje: Data / Filter / Autofilter / Prezime /Jankovic Na Racun / Custom / is greater than 90 f) Prikazati samo zapise kod kojih brojevi telefona počinju sa 2. Rješenje: … Telefon / Custom / Begin with 2 f) Prikazati samo zapise kod kojih brojevi telefona počinju sa 2 a predzadnji broj je 3. Rješenje: … Telefon / Custom / equal 2????3? Napomena: ? - zamjenjuje jedan karakter * - zamjenjuje niz karaktera Filtrianje istaknutih zapisa: Data / filter / autofilter / top10 Primjer: Prikazati samo pet najvećih računa Rješenje: … Racun / Top 10 / Top , 5, Items Funkcije za baze podataka Napomena:Radićemo samo neke i to: Dsum, Dmin, Dmax, Daverage, Dcount Sintaksa funkcije za bazu podataka (D (D-funkcije):

=Dfunkcija(database,field,criteria) database = baza podataka - tačnije opseg baze podataka field = polje - adresa sa nazivom polja ili redni broj polja ili pod znacima navoda naziv polja. criteria = kriterijum -(potrebno (potrebno je kopirati prvi red tj. nazive polja na proizvoljno mjesto i u redu ispod upisati kriterijum - kriterijum je opseg)

Primjer: Koliko ljudi se zove Marko Rješenje: Red. Br. Prezime

Rezultat:

3

Ime Marko

Telefon

Br. Impulsa

Racun

=DCOUNT(B14:G26;B14;B88:G89)

Napomena: za funkciju Dcount polje je bilo koji naziv kolone koji sadrži samo brojeve.

40

EXCEL NAPREDNI KURS

Primjer: Naći sumu računa, maksimalni pojedinačni račun,minimalni pojedinačni račun, minimalni pojedinačni broj impulsa i srednju vrijednost impulsa za Jankoviće. Rješenje: Red. Br.

Ukupni r.:

Prezime Jankovic

75,75 ili ili

Ime

Telefon

Br. Impulsa

Racun

=DSUM(B14:G26;G14;B101:G102) =DSUM(B14:G26;"Racun";B101:G102) =DSUM(B14:G26;6;B101:G102)

Najveći r.:

30,75

=DMAX(B14:G26;"Racun";B101:G102)

Najmanji r.:

21,5

=DMIN(B14:G26;"Racun";B101:G102)

Min. imp.:

430

=DMIN(B14:G26;F101;B101:G102)

Sred. vr.:

505

=DAVERAGE(B14:G26;F101;B101:G102)

Primjer: Koliko ljudi ima račun veći 20 ? Rješenje: Red. Br. Prezime

6

Ime

Telefon

Racun >20

=DCOUNT(B14:G26;B120;B120:G121)

Primjer: Koliko Petrovića ima račun manji od 30 ? Rješenje: Red. Br. Prezime Ime Telefon Petrovic

2

Br. Impulsa

Br. Impulsa

=DCOUNT(B14:G26;B127;B127:G128)

Napomena: U kriterijumu se mogu koristiti znakovi: >, <, >=, <=, <>.

Racun <30