Kako koristiti Excel: 12 tehnika za napredne korisnike

Excel je odličan, ali pokušati samostalno shvatiti kako se koristi Excel moguće je do određene granice jer on nije intuitivan. No ako budete koristili tehnike i savjete iz ovog tutoriala, svoj posao obavit ćete brže i sa manje stresa.

Naučite kako primjeniti Excel formule, izračune, filtriranje, manipulaciju podacima i još mnogo toga. Evo dvanaest Excel tehnika i značajki koje je potrebno poznavati.

Screencast

Prije nego krenete, preuzmite besplatnu datoteku Excel listova. Datoteka uključuje više listova, jedan za vježbanje svake od dvanaest Excel tehika. Pogledajte screencast kompletnog tutorijala gore ili vježbajte korak po korak pisane verzije dolje.

1. Osnove pisanja formule

Svaka formula mora započeti znakom jednakosti. Ako nema znaka jednakosti, Excel neće računati; samo će prikazati ono što ste natipkali. To je način na koji Excelu kažete "Hej, izračunaj ovo." Zamislite da je rezultat vaše formule ono što bi trebalo biti na lijevoj strani znaka jednakosti da koristite olovku i papir.

A gdje upisujete formulu? Upisujete je u ćeliji u kojoj želite da se pojavi rezultat. Za ovu vježbu, pogledajte 'jednostavni' list u besplatnoj Excel datoteci.

Primjer: zbroji vrijednosti B2, B3 i B4 ćelija, te zbroj upiši u ćeliju B6

U ćeliju B6 upišite ovu formulu:

=B2 + B3 + B4

Zatim pritisnite Enter kako biste dobili rezultat. Ako ne pritisnete Enter (ili ne kliknete na malu kvačicu odmah iznad stupca A), nište se neće dogoditi.

Oduzimanje, množenje, dijeljenje i eksponenti rade na isti način. Uglavnom se koriste simboli koje i inače koristite ili možete koristiti ovu tabelu:

2. Za složenije kalkulacije, koristite funkcije

Ako želite složeniji izračun od jednostavne formule, umetnite funkciju u vašu formulu. Excel ima oko 400 funkcija, pa ćete vjerojatno pronaći onu koja vama treba. Tu su funkcije za poslovanje, statistiku, financije, datum i vrijeme, tekst, informacije o dokumentu i još više.

Sintaksa svih funkcija je:

=NAZIV FUNKCIJE(...)

Uvijek se započinje znakom jednakosti ako se funkcija nalazi na početku formule, zatim slijedi naziv funkcije, a zatim zagrade. Obično se nešto nalazi u zagradama, no ne uvijek.

Primjer: Pronađite zbroj i prosjek stupca brojeva. Za ovu vježbu pogledajte list 'funkcije' u besplatnoj Excel datoteci.

U B10 unesite funkciju:

=SUM(B2:B8)

U B11 unesite funkciju:

=AVERAGE(B2:B8)

Pritisnite Enter kako biste dobili rezultate.

3. Za brzo i inteligentno kopiranje, koristite AutoFill

Recimo da upišete formulu na vrhu stupca i želite je ponovno koristiti negdje dalje u stupcu (ili imate formulu na početku retka i želite je ponovno koristiti dalje u retku). Umjesto da ručno kopirate i ljepite, koristite AutoFill značajku kako biste to brže obavili i automatski podesili formulu. AutoFill možete koristiti i za automatski unos mjeseci ili dana u tjednu.

Za korištenje AutoFilla, potražite malu točku u donjem desnom kutu odabrane ćelije (Ako ste odabrali više ćelija, one djele zajedničku točku.) Kada pokazivač miša stavite na točku, pokazivač se pretvara u križić. Zatim povucite križić niz stupac ili uzduž retka.

Primjer: U D2 napravite jednostavno množenje i zatim koristite AutoFill do D8. Za ovu vježbu pogledajte list 'autofill' u besplatnoj Excel datoteci.

Prvu formulu napišite kao i obično.

Unesite formulu. Ukoliko je potrebno, kliknite natrag na D2. Usmjerite pokazivač miša na točku u donjem desnom kutu ćelije i pokazivač će postati križić.

Ili povucite krićić niz stupac D ili dvaput kliknite. Stupac će sadržavati ispravnu formulu i ispravne rezultate u stupcu

4. Filtriranje i sortiranje podataka

Excel možete koristiti i kao malu bazu podataka, sortirajući retke podataka po abecedi ili po brojetvima, te filtrirajući samo one podatke koje želite. To najbolje funkcionira kada stupci imaju zaglavlja kao što su Ime, Prezime, itd.

Najvažnija stvar koju morate zapamtiti je da ne odaberete stupac prije sortiranja. To bi sortiralo stupac neovisno o ostalim podacima, a to vjerojatno nije ono što želite. Kada kliknete u stupac, Excel je dovoljno pametan da zna što radite.

Što se tiče filtriranja, Excel ima odlične automatske alate.

Primjer: Sortirajte listu po prezimenima, gradovima ili nekom drugom stupcu  uzlazno ili silazno. Za ovu vježbu pogledajte list 'filtriranje i sortiranje' u besplatnoj Excel datoteci.

Kliknite na bilo koju ćeliju u stupcu Last Name (Prezime). U kartici Home na traci kliknite na Sort and Filter pa odaberite A to Z ili Z to A. Pokušajte ovo i u bilo kojem drugom stupcu.

Možda ste primjetili da možete izvršiti filtriranje podataka iz istog padajućeg menija.

Primjer: Sada filtrirajte popis tako da prikaže adrese samo iz Aljaske i Kalifornije.

Kliknite bilo gdje u području sa podacima i iz izbornika Sort and Filter odaberite Filter.

Čim to napravite primjetit ćete da su zaglavlja stupaca dobila strelice padajućeg izbornika. Sada:

• Kliknite na strelicu padajućeg izbornika u stupcu State.
• Zatim kliknite na kvadratić Select All kako biste oslobodili sve kvadratiće saveznih država.
• Ponovno odaberite samo AK i CA i zatim kliknite OK.

Lista sada prikazuje samo retke za te dvije savezne države. Ako želite, sortirajte savezne države od A do Z. To će grupirati sve retke koji sadrže Aljasku iznad redaka koji sadrže Kaliforniju (označeno u donjem prikazu zaslona).

Ako želite očistiti filter, ponovno kliknite na strelice padajućeg izbornika u stupcu State i odaberite Clear Filter.

Strelice padajućeg izbornika možete ukloniti na isti način na koji ste ih dobili: kliknite na Sort and Filter izbornik i ponovno odaberite Filter.

5. Uklanjanje dupliciranih redaka

Ako imate mnogo podataka, posebno ako su uvezeni sa mjesta kao što je web formular, možete imati duplicirane retke. Excel izvrsno uklanja duplikate i omogućava vam da odlučite koliko retci moraju biti slični da bi ih se smatralo duplikatima.

Primjer: Upotrijebite informacije o adresi kako biste odlučili koji retci su duplikati. Za ovu vježbu, referirajte se na list 'remove duplicates' u besplatnoj Excel datoteci.

Kako se pomičemo niz ovu datoteku, vidimo da postoje tri identična retka gdje je ime Samantha Carter: podaci u svim stupcima su identični pa znamo da se radi o jednoj osobi navedenoj tri puta. Također, postoje i dva retka sa imenom Sharon Valeri, ali sve druge informacije su različite, pa znamo da se radi o dvije osobe sa istim imenom.

Pobrinite se da kliknete negdje na području podataka. Tada u kartici Data na traci kliknite na Remove Duplicates.

Ostavite sve kvadratiće označenima. Na ovaj način, Excel će smatrati da su duplikati samo oni retci koji sve stupce imaju iste.

Kliknite OK. Reći će nam da su dva duplikata uklonjena, što smo i očekivali. Kada se pomičemo niz datoteku, vidimo da su uklonjena dva od tri zapisa Samanthe Carter i da su ostala oba zapisa za Sharon Valeri.

6. Odvajanje podataka u jednom stupcu u više stupaca

Ako imate stupac s podacima koje morate odvojiti u više stupaca, upotrijebite naredbu Text to Columns. Samo budite svjesni da nije savršen jer ne može čitati vaše misli, pa ćete možda morati podatke ručno dotjerati.

Primjer: Podijelite stupac sa punim imenima na odvojene stupce za ime i prezime. Za ovu vježbu pogledajte list 'splitting' i besplatnoj Excel datoteci.

Ovdje imamo slične podatke kao u prijašnjim primjerima. Primjetite da tri osobe na listi imaju tri imena, pa će nam biti potrebna dva prazna stupca.

Prvo, umetnite dva prazna stupca desno od stupca A:

Smjestite pokazivač miša na zaglavlje stupca B, kako bi ono dobilo strelicu prema dolje. Povucite prema desno, kako bi stupci B i C bili odabrani.

Desni klik će otvoriti iskočni izbornik u kojem odaberete Insert. Prethodno ste odabrali dva stupca, pa ćete sada imati dva prazna stupca.

Odaberite sva imena iz stupca A. (Brzi način: kliknite na prvo ime u A3, zatim pritisnite Ctrl+Shift+strelica prema dolje. Zatim otkližite natrag na vrh.)

Na kartici Data odaberite Text to Columns. Odaberite Delimited i zatim kliknite Next.

Odaberite Space kao jedini razdvojnik. Tretiranje svih slijedećih razdvojnika kao jednog je zadana vrijednost, pa to nemojte mijenjati. Klizačem se spustite niz listu kako biste vidjeli imena koja će premostiti tri stupca.

Kliknite Next, zatim kliknite Finish. Stupac A će sada sadržavati samo imena, stupac B prezimena, a stupac C će imati tri imena.

Ova tri imena možete ručno ispraviti, a zatim izbrisati stupac C koji bi trebao biti prazan.

7. Spajanje više stupaca u jedan stupac

Excel je dobar i u obrnutoj proceduri: kada imate više stupaca i želite ih spojiti u jedan stupac. Ima nekoliko načina nakoje se to može napraviti, ali pokazat ću vam najjednostavniji, a to je korištenjem formule. Za ovu vježbu pogledajte list 'joining' u besplatnoj Excel datoteci.

Kao i u prethodnoj vježbi, umetnite novi stupac iza stupca B. Postavite pokazivač na zaglavlje stupca C kako bi postao strelica prema dolje...

...zatim odaberite Insert iz iskočnog izbornika. Novom stupcudodjelite naslov Full Name (Ime i prezime).

Kliknite u ćeliju C3 na vrhu novog stupca za prvu formulu. Kako bi vam bilo jasnije, napravit ćemo malu pogrešku i zatim je ispraviti.

Operator kojeg ćemo koristiti je & jer želimo ujediniti sadržaj ćelija, a ne zbrojiti brojeve. Upišite formulu:

=A3 & B3

No kad unesete tu formulu, imena i prezimena su napisana zajedno, bez razmaka između njih. Izbrišite ju. Ponovno napišite formulu, ali sa jednim dodatkom:

=A3 & " " & B3

Stavljanje razmaka između navodnika znači da formula mora uključivati prazan prostor.

Pritisnite Enter i koristite AutoFill do kraja.

8. Brzo formatiranje brojeva i radnih listova

Evo nekih brzih načina za formatiranje brojeva (dolara, postotaka, itd.) i radnih listova (boje, fontovi).

Za ovu vježbu pogledajte list 'formatting' u besplatnoj Excel datoteci.

Brojevi u ovom listu bi trebali imati zareze iza tisuća, ali ne trebaju im decimale. Mgli biste dodati znak dolara prvom retku, retku Total (Ukupno) i stupcu Total (Ukupno). Također, zadnj stupac je formatiran kao decimale, a trebao bi izgledati kao postotci.

Odaberite najviši redak brojeva kroz cijeli stupac Total.

Pritisnite tipku Ctrl i odaberite redak Total na dnu (oba retka, i na vrhu i na dnu, bi trebali biti označeni).

Na traci, kliknite na gumb sa znakom dolara ili pritisnite Ctrl + Shift + $. To se odnosi na znakove za dolar, zareze i decimale. (Decimale ćemo ukloniti uskoro.)

Sada odaberite sve brojeve između i kliknite na gumb Comma style ili pritisnite Ctrl + Shift + !. Ovo je primjenjivo na zareze i decimale.

Za uklanjanje svih decimala, odaberite sve brojeve na listu osim stupca Percent (postotak) na kraju i dva puta kliknite na gumb Decrese Decimal.

Sada primjenimo postotke.

Odaberite sve brojeve niz stupac % of Total, zatim kliknite na znak % ili pritisnite Ctrl + Shift + %.

Ako želite, kliknite jednom ili dvaput na gumb Increase Decimal.

Postoje tri načina formatiranja lista:

• Odaberite zaglavlja redaka i stupaca, te na svakom zasebno primjenite boje, fontove, itd.
• Odaberite zaglavlja redaka i stupaca i primjenite ugrađene stilove.
• Formatirajte cijeli list kao tablicu i dopustite Excelu da primjeni dizajn.

Radi brzine, evo druge i treće metode.

Odaberite sva zaglavlja stupaca, a zatim na traci kliknite na Cell Styles. Odaberite jedan stil.

Učinite isto za zaglavlja redaka.

Kliknite Undo nekoliko puta.

Sada kliknite na Format as Table i izaberite dizajn.

Kliknite OK u dijaloškom okviru koji će se pojaviti. Obratite pažnju na to da stupci imaju strelice padajućeg izbornika kako biste ih mogli filtrirati.

9. Manipulacija stupcima, retcima, prijelomima stranica i listovima

Promjena veličine redaka i stupaca

Ako je stupac previše uzak, moža ćete imati tekst koji je odrezan ili brojke koje se prikazuju kao znakovi za funtu. Ako je redak prekratak možda ga nećete moći pročitati. Za ovu vježbu pogledajte list 'rows & columns' u besplatnoj Excel datoteci.

Prilagođavanje širine stupca i visine retka radi se zapravo na isti način: postavite pokazivač na desni ili donji rub retka ili stupca, tako da pokazivač postane dvoglava strelica, zatim povučete dok redak ili stupac ne postignu željenu veličinu. Ili možete kliknuti dvaput kako bi se veličina retka ili stupca automatski podesila. (Ovo funkcionira na isti način i u pregledu detalja u Windows File Exploreru.)

Manipulacija prijelomima stranica

Postoje dva načina kreiranja prijeloma stranica.

• Umetnite ih ručno, iz kartice Page Layout
• Koristeći Page Break Preview

Kako biste ručno umetnuli prijelom stranice, odaberite zaglavlje retka ili stupca gdje želite prijelom, zatim iz kartice Page Layout na traci kliknite na Breaks drop-down i odaberite Insert Page Break.

Za prilagodbu prijeloma, koristite Page Break Preview.

Kliknite na ikonu Page Break Preview u donjem desnom kutu i zatim povucite linije. Točkaste linije su automatski prijelomi stranice, a ako umetnete prijelome stranice, te linije bit će pune.

Podešavanje listova

Za umetanje, brisanje, premještanje ili preimenovanje listova koristite kartice u lijevom donjem kutu ekrana.

Ova radna knjiga ima samo jedan list, koji se zove Sheet1. Kako biste mu promjenili ime:

• Dvaput kliknite karticu.
• Utipkajte novo ime (kao N. America), zatim pritisnite Enter.

Za umetanje novog, praznog lista kliknite na okrugli Plus znak do kartice.

Za kopiranje lista:

• Povucite karticu lista dok se kraj nje ne pojavi trokut koji pokazuje prema dolje.
• Pritisnite Ctrl kako biste u pokazivaču ugledali mali znak plusa.

Prvo otpustite tipku na mišu. Vidjet ćete novi list sa istim imenom i oznakom "(2)" iza njega.

Listove možete povući na bilo koju poziciju koja vam odgovara. Listove se ispušta tamo gdje vidite trokut koji pokazuje prema dolje.

10. Održavanje zaglavlja stupaca vidljivima

Kada imate dugačak list, lako se dogodi da zaglavlje stupca otkliže izvan pogleda, ali postoji značajka koja to sprečava. Kada ispisujete dugačak list, želite biti sigurni da će se zaglavlje stupca pojavljivati na vrhu svake stranice - i molim vas, nemojte to raditi kopiranjem i ljepljenjem zaglavlja. To će postati noćna mora.

Za ovu vježbu, pogledajte list 'headers' i besplatnoj Excel datoteci.

Održavanje zaglavlja vidljivima pri pomicanju

U ovom listu, kliknite na ćeliju A3, koja se nalazi odmah ispod zaglavlja stupaca. Tada u kartici View na traci kliknite Freeze Panes. Iz padajućeg izbornika izaberite Freeze Panes.

Sada možete pomicati list gore i dolje i još uvijek vidjeti zaglavlja.

Isključivanje ove značajke se vrši na isti način: kliknite Freeze Panes, zatim odaberite Unfreeze Panes iz padajućeg izbornika.

Ipak, ovo ne utječe na ispis. Za ispis zaglavlja stupaca na svakoj stranici:

• Otiđite na karticu Page Layout na traci i kliknite na Print Titles.
• Kliknite unutar Rows to Repeat na kvadratiću Top.
• Na radnom listu odaberite zaglavlja redaka 1 i 2. To upisuje $1:$2 u kvadratić.

Kako biste vidjeli da funkcionira, otiđite na Print Preview: File > Print ili pritisnite Ctrl + P.

Kliknite na kartice stranica na dnu za pomicanje kroz sranice i vidjet ćete zaglavlja.

11. IF funkcija: Kako vrijednost ćelije učiniti ovisnim od uvjeta

Excel ima nekoliko funkcija koje mogu odlučivati o tome koje vrijednosti će biti prikazane. Sve se zasnivaju na IF funkciji. Jednom kada ste se upoznali s njom, biti će vam lakše koristiti kompleksnije funkcije.

IF funkcija ima tri argumenta:

• Koji je uvjet koji testirate.
• Što ćelija mora prikazati ako je uvjet istinit.
• Što ćelija mora prikazati ako uvjet nije istinit.

Kada funkcija ima nekoliko argumenata, njih odvajate zarezima. Sintaksa je ova:

=IF (uvjet koji se testira, što prikazati ako je istinit, što prikazati ako nije istinit)

Za ovu vježbu pogledajte list 'functions' u besplatnoj Excel datoteci.

Primjer: Imamo listu narudžbi osoba iz različitih saveznih država. Ako je narudžba iz New Jerseya, primjenjuje se porez na promet od 7%. Ako je narudžba iz bilo koje savezne države, porez se ne primjenjuje.

Na vrhu stupca Tax (H3), unesite ovu formulu:

=IF(C3="nj",G3* .0,7, "")

To znači:

• Uvjet koji se testira je: da li ćelija u stupcu State sadrži vrijednost "nj"? (Primjetite da vrijednost nije osjetljiva na veličinu slova, a zato što se radi o tekstu, mora se nalaziti između navodnika.)
• Ukoliko je uvjet istinit, ćelija bi trebala sadržavati vrijednost prodaje * 7%.
• Ukoliko uvjet nije istinit, ćelija bi trebala ostati prazna (prazni navodnici).

AutoFillom ispunite ćelije do kraja liste. Većina ćelija u stupcu Tax će biti prazna, a one koje nisu prazne imat će upisan porez na promet jer taj red sadrži NJ u stupcu State, kako je prikazano dolje.

12. SUMIF i AVERAGEIF: Zbrajanje i izračunavanje prosjeka ovisno o uvjetu

Slično kao i IF funkcija, funkcije SUMIF i AVERAGEIF će pronaći zbroj i prosjek raspona ćelija kod kojih postoji određena vrijednost ili uvjet.

Funkcije imaju tri argumenta:

• U kojem rasponu tražimo uvjet?
• Koji je uvjet ili vrijednost koja se traži?
• U retcima u kojima ta vrijednost postoji, iz kojeg raspona uzimamo sumu ili prosjek?

Sintaksa dviju funkcija je slična - različita imena, no isti argumenti:

=SUMIF(raspon koji se pretražuje, vrijednost koja se traži, raspon kojem se dodaje)

=AVERAGEIF(raspon koji se pretražuje, vrijednost koja se traži, raspon kojem se dodaje)

Za ovu vježbu pogledajte list 'SUMIF' u besplatnoj Excel datoteci.

Primjer: Izračunajte sumu svih narudžbi za Kaliforniju i prosjek narudžbe za Konu.

Najprije napravite SUMIF

Na dnu stupca Sale, u ćeliji G203, upišite ovu formulu kako biste dobili zbroj svih narudžbi iz Kalifornije:

=sumif(C3:C201,"ca",G3:G201)

To znači:

• Pogledaj niz stupac C.
• U stupcu C, odaberi retke koji sadrže "ca".
• U istom redu gdje se nalazi "ca", dodaj zbroju vrijednost stupca G.

Napravite AVERAGEIF

Sada koristite istu tehniku za izračunavanje prosjeka prodaje Kone.

Otiđite na G204 i upišite ovu formulu:

=averageif(D3:D201,"kona",G3:G201)

To znači:

• Pogledaj niz stupac D.
• U stupcu D, odaberi retke koje sadrže "kona".
• U istom retku gdje je pronađena "kona", uključi vrijednost iz stupca G u prosjek.

Ovo bi trebao biti rezultat:

Korištenje ovih tehnika u Excelu

Naučiti koristiti Excel je vrijedno truda. Ovim tehnikama i savjetima možete u vašim proračunskim tablicama koristiti više snažnih značajki Excela koje štede vrijeme. Vježbajte gore navedene lekcije, a zatim uključite ove tehnike u vaš tijek rada.