E-book Trikovi U Excelu 1-20

Trikovi u Excelu 1 do 20

Beograd, 2015.

MCB Menadžment Centar Beograd

Partner u controllingu

BESPLATAN E-BOOK

Ova je besplatan e-book. 

Ako ti se svidi ovaj e-book, slobodno ga prosledi e-mailom prijateteljima, kolegama, rođacima…



Ako budeš želeo da kopiraš delove e-booka za svoje potrebe, slobodno to uradi. Dovoljno je da napomeneš izvor podataka (www.mcb.rs/blog)

Ako te zanima da još više naučiš, moja preporuka je: Menadžment 

www.mcb.rs/blog

(MCB Blog)



http://goo.gl/Lt3O2r

(LinkedIN grupa MCB)



http://www.facebook.com/MCBBeograd

(Facebook MCB)



http://www.youtube.com/user/mcbbeograd (Youtube MCB)

Controlling 

www.icv.rs

(ICV Srbija)



http://goo.gl/Ci8Gms

(LinkedIN grupa ICV Srbija)



https://www.facebook.com/ICVSrbija

(Facebook ICV Srbija)

Bojan Šćepanović Februar 2015.

www.mcb.rs/blog

1

MCB Menadžment Centar Beograd

Partner u controllingu

Sadržaj Trikovi u Excelu 1.deo: Različiti trikovi ............................................................................................ 3 Trikovi u Excelu 2.deo: Data validation ........................................................................................... 9 Trikovi u Excelu 3.deo: Shortcuts u Excelu 1/3 ............................................................................. 16 Trikovi u Excelu 4.deo: Custom sort .............................................................................................. 17 Trikovi u Excelu 5.deo : Shortcuts u Excelu 2/3 ............................................................................ 23 Trikovi u Excelu 6.deo: Custom View ............................................................................................ 24 Trikovi u Excelu 7. deo: Sekundarna osa, ili kako prikazati podatke u dve skale na chartu ........... 29 Trikovi u Excelu 8.deo: Shortcuts u Excelu 3/3 ............................................................................. 33 Trikovi u Excelu 9.deo: Print Titles ili kako da se prvi red ponavlja na svakom listu u Excelu ........ 34 Trikovi u Excelu 10.deo: Sparklines ili grafikon unutar jedne ćelije ................................................ 37 Trikovi u Excelu 11.deo: VLOOKUP, druga najčešće korišćena Excel funkcija ili: Šta povezuje Excel, biologiju i gospodina Bulajića.............................................................................................. 42 Trikovi u Excelu, 12 deo: Imenovani rasponi ili Kako komplikovane formule načiniti kraćim i jasnijim ..................................................................................................................................................... 50 Trikovi u Excelu 13. deo: Kako od Pivot tabele brzo napraviti tabelu sa popunjenim ćelijama ....... 57 Trikovi u Excelu 14.deo: Besplatno konvertovanje .PDF fajlova u Excel ....................................... 58 Trikovi u Excelu 15.deo: Lakše održavanje kompleksnih formula – pišite ih kao što programeri pišu svoj kôd ........................................................................................................................................ 60 Trikovi u Excelu 16.deo: Proverite da li vrednost iz jedne tabele postoji u drugoj tabeli ................. 61 Trikovi u Excelu 17.deo: Matrične formule (formule nizova, “array” formule): Nemoguće kalkulacije su (ipak) moguće .......................................................................................................................... 62 Trikovi u Excelu 20.deo: Zašto Excel ne može da pronađe neki broj (Excel Find and Replace)? .. 73

www.mcb.rs/blog

2

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 1.deo: Različiti trikovi Excel sam počeo da koristim 1991. godine. Ustvari, to nije bio ni Excel već Lotus 1-2-3. I, verovali ili ne, ali sam tabele pravio bez miša s obzirom da miš nije ni postojao 1991. godine. A nije postojao ni Windows, tada smo se zvali Jugoslavija, imao sam kosu do pola leđa (ovo zadnje i nije baš tačno. Da li izbaciti? 

Čekaj, čekaj… hajde mani YU, Tita i tvoj hipi fazon… da li to znači da si zaista pravio tabele bez miša?

Da, zaista sam pravio tabele bez miša. Čak i bez Windowsa, sve u DOS-u. Samo nemoj da me pitaš šta je DOS (nije Demokratska Opozicija Srbije). Moj prvi računar je bio 286 i imao je čitavih 2 Mb memorije! Nije štamparska greška – 2 Mb memorije. Mislim da je hard disk bio oko 20 Mb memorije. A tabele – k’o bela lala. Obračun plata, autorski honorari, obračun amortizacije, obračun poreza, završni račun… Sve to u vidu tabela, a mnoge od njih još uvek koristim – iako sam ih napravio još pre 20 godina. Believe it or not!

Ova anegdota deluje čudno za novu “mouse generaciju” (rođeni posle 198x…) a deluje i malo staromodno. Da ne bude zabune – ja sam 1971. godište i još uvek sebe ne smatram mnogo matorim. No, ponekad je zanimljivo vratiti se u prošlost .Posle 20 godina rada u Excelu još uvek se fasciniram koliko ima novih stvari koje mogu da se nauče. Odavno sam shvatio da “znam koliko ne znam” i da je Excel čudovišno moćan program. Elem, da bih vam malo pomogao da i vi zaradite koju paricu – ili barem da uštedite malo vremena – rešio sam da podelim sa vama nekoliko Excel trikova. Teško je reći šta su dobri Excel trikovi; za nekoga će ovo biti otkrovenje od teksta, suva genijalnost, predlog za sledeći “Ja imam talenta”… Sa druge strane, za nekoga će ovo biti boza i bespotrebno trošenje piksela po ekranuo, barem sam se potrudeo da vam pomognem – i to nešto vredi, zar ne? Ako imate još neko pitanje, ideju, želju, čestitku ili pozdrave oko Egzela – slobodno pišite. Dobićete odgovor. By the way, Egzel nije greška u kucanju već originalni izgovor jedne od mojih simpa polaznice na treningu. Dakle, idemo – Trikovi u Excelu by Boki.

www.mcb.rs/blog

3

MCB Menadžment Centar Beograd

Partner u controllingu

1. Nepismena sekretarica mi je poslala loše prekucan spisak zaposlenih. Neka slova su velika, neka mala, postoji preveliki razmak između slova. Imam 500 imena i ja sam očajan!

Menjaj sekretaricu… bolje uzmi neku zgodnu cica-macu. Ovo je klasičan problem koji se rešava vrlo jednostavno. Treba da upotrebiš funkcije TRIM i PROPER. Stani i ćeliju B2 i napiši formulu: =TRIM(PROPER(A2)) U ćeliji B2 dobićeš ispravno napisano Novak Đoković.

2. Wauuuuuu, ovo zaista deluje. A šta ako želim da iskopiram podatke iz kolone B u neku drugu kolonu? Kada radim kopiranje, excel napiše samo prazna mesta? Treba da koristiš opciju PASTE SPECIAL → VALUES.  Označi kolonu B  Kopiraj podatke (Copy)  Idi na neku drugu kolonu (npr. kolona D)  Klikni desno dugme. Odaberi opciju Paste Special. Odaberi opciju Values (treća odozgo). I – to je to.

www.mcb.rs/blog

4

MCB Menadžment Centar Beograd

Partner u controllingu

3. Ok, rešeno. Sada sam napisao imena i prezimena ispravno. Inače, zaposlio sam i novu sekretaricu (mjauuu ). Želim da odvojim ime i prezime u posebnu kolonu. Kako to da uradim?

Za ovaj zadatak koristiš sledeće funkcije:    

LEFT FIND MID LEN

Rešenje je dato na slici:

E, a da li Excel može automatski da mi napiše broj Marije Šarapove? Da, naravno… samo u tvojim snovima. Sedi gdi si…

www.mcb.rs/blog

5

MCB Menadžment Centar Beograd

Partner u controllingu

4. Počeo sam da pišem podatke u redovima/horizontalno (januar, februar, mart…). Međutim, pogrešio sam i želim da mi se podaci nalaze u kolonama/vertikalno. Pomagaj…

Koristi opciju PASTE SPECIAL → TRANSPOSE.   

Obeleži podatke od B2 do E2 Kopiraj (Copy) Klikni na B3. Klikni desnim dugmetom i odaberi opciju Paste Special. Štikliraj opciju Transpose.

Dobićeš rezultat kao na sledećoj slici.

www.mcb.rs/blog

6

MCB Menadžment Centar Beograd

Partner u controllingu

Ova opcija radi i obrnutno (iz horizontale u vertikalu). Jedino ne radi sa sekretaricama – njih nikako ne možeš da prebaciš iz vertikale u horizontalu . 5. Želim da brzo napišem ponedeljak, utorak, sreda…    

Napiši u A1 Ponedeljak. Napiši u A2 Utorak. Obeleži obe ćelije (selektuj). Stani u donji, desni ugao ćelije A2. Pojaviće se mali kvadratić, a kursor će se pretvoriti u mali krstić. Povuci nadole. To je to.

Probaj ovo da uradiš i sa sledećim kombinacijama:      

1, 2 100, 200 1000, 2000 Pon, Uto Jan, Feb Januar, Februar

Ukoliko ti se ne pojavljuje Sreda, Četvrtak… to znači da nemaš default kodni raspored za Srbiju. Idi na Control Panel, odaberi Regional Settings i promeni kodni raspored na Srbija. 6. Često mi se dešava da želim da iskopiram gornju ćeliju. npr, nalazim se na ćeliji A2 i želim da iskopiram ćeliju A1. ja obično idem gore, pa copy, pa se spustim dole pa paste… E, jarane moj, jarane moj…  Napiši u ćeliji A1 BOKI – TI SI CAR, EXCELOVOG SRCA GOSPODAR!  Siđi do ćelije A2. Pritisni CTRL + D. 

CTRL + D kopira ćeliju iznad. 7. Super ti je ova skraćenica. Da li možeš da mi šapneš još neku? Otvori novi fajl. Napiši u njemu “Prvi fajl”. Otvori drugi fajl (CTRL + N). Napiši u njemu “Drugi fajl” Otvori treći fajl.Napiši u njemu “Treći fajl”.  Pritisni CTRL + TAB.  Pritisni još jednom CTRL + TAB.  Pritiskaj do besvesti CTRL + TAB. Kapito?CTRL + TAB prebacuje iz fajla u fajl. www.mcb.rs/blog

7

MCB Menadžment Centar Beograd

Partner u controllingu

8. Hoćemo još, hoćemo još, hoćemo još….  Otvori novi fajl.  Pritisni SHIFT + F11  Pritisni opet SHIFT + F11  Pritisni još jednom SHIFT + F11 

SHIFT + F11 služi za otvaranje novih sheetova. Možeš ovako do besvesti… i nazad.

9. pleease…. Kada si već bio zaludan i kada si otvorio toliko sheetova, onda bi bilo zgodno i da naučiš kako se krećeš između sheetova.  Pritisni CTRL + PageDown  Pritisni još jednom CTRL + PageDown  Pritisni CTRL + PageUP  Pritisni CTRL + PageDown  Pritisni CTRL + PageUP  ma, pritisni šta god hoćeš… valjda si već ukapirao CTRL + PageDown/PageUp služi za šetanje između sheetova. 10. I NA KRAJU… S obzirom da si bio toliko uporan do pročitaš ovaj tekst do kraja evo i jedne male zagonetke. Radiš kao kontroler u kompaniji i analiziraš prodaju po prodavnicama. BUD znači BUDGET, a ACT znači ACTUAL. Razlika se zove VARIANCE ANALYSIS. Kažu da slika vredi hiljadu reči. Kako da dobiješ ovu sliku?

Ukoliko ti se sviđa ovaj tekst, PIŠI KOMENTARE. Napiši šta te zanima u Excelu, koje probleme imaš, šta bi voleo da naučiš iz Excela. Ja ću ti svakako odgovoriti.

www.mcb.rs/blog

8

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 2.deo: Data validation Baš dok sam razmišljao o čemu da pišem, kolegica sa posla me zamolila da joj pomognem oko ujednačavanja unosa podataka u Excel tabeli. Naime, napravila je praznu tabelu u Excelu koju je dala na popunjavanje drugima. Naravno, kada nešto može krenuti naopako, obično se to i desi. Pošto je unos bio slobodan, svako je unosio tekst na različit način: sa malim slovima, velikim slovima, sa početnim velikim slovom, neko je zaboravio isključiti Caps Lock itd. Nakon popunjavanja vraćena joj je tabela sa kolonom u kojoj je trebalo da budu dve različite vriednosti (“Muški” ili “Ženski”) a dobila ih je desetak. Kada se gleda tako popunjena tabela, čak se i može “pročitati”. Problem nastaje kada se pokušaju napraviti sumarni pregledi, filtriranja i sortiranja. Naime, za Excel je ista reč napisana malim slovima različita od iste reči napisane velikim slovima. Kolegica je rekla da joj ne predstavlja problem ispraviti podatke tako da budu ujednačeni jer poznaje odgovarajuće funkcije (TRIM, PROPER, UPPER, LOWER). Pitala je kako sprečiti buduće pogrešne unose, odnosno kako omogućiti samo unose sa padajuće liste. Odgovor na to je “Data Validation”. Najveća korist koju ova opcija pruža je ograničavanje mogućih unosa u ćelije. Excel prihvata samo unose koje definišete. Na taj način imate ujednačene podatke, što je veoma bitno kod sumarnih pregleda, kalkulacija, filtriranja i sortiranja. Ukratko, ova opcija radi na sledeći način:  selektujete ćelije za koje definišete moguće unose;  definišete moguće vrednosti (u obliku liste, raspona brojeva, datuma, dužine teksta itd.);  pri unosu se dobija padajuća lista sa definisanim opcijama;  Excel ne prihvata unose koje niste definisali i javlja grešku. Osim toga, možete:  definisati poruku za pomoć pri unosu;  definisati poruke pri pokušaju pogrešnog unosa;  koristiti postojeći spisak kao listu raspoloživih opcija;  istaći ćelije čiji sadržaj ne odgovara zadanim opcijama (ovo se dešava kada su podaci uneseni pre nego koristite “Data Validation”). Evo kako se koristi “Data Validation”. Napomena: ovde su prikazane slike iz Excela 2007/2010. Skoro isti način rada je i u “staroj” verziji Excela 2003.

www.mcb.rs/blog

9

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 1.

Korak 2.

Korak 3.

www.mcb.rs/blog

10

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 4.

Korak 5.

Korak 6.

www.mcb.rs/blog

11

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 7.

Korak 8.

www.mcb.rs/blog

12

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 9.

Korak 10.

www.mcb.rs/blog

13

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 11.

Korak 12.

www.mcb.rs/blog

14

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 13.

Korak 14.

Korak 15.

www.mcb.rs/blog

15

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 3.deo: Shortcuts u Excelu 1/3 E, sada dolaze na red i shortcuts (skraćenice). Često me polaznici pitaju:  A zašto ljudi koriste skraćenice kada postoji miš? Postoje dva razloga:  Tabele su nastale 80-ih godina (Lotus 1-2-3, VisiCalc…). Miš (mouse) je izmišljen tek posle ’90 godine. Ukratko, mnogi korisnici su radili u tabelama bez miša. Npr, ja sam skoro 5 godina radeo u Lotusu 1-2-3 bez upotrebe miša  Mnogo je brže raditi sa tastature. Imate 10 prstiju, a sa mišom koristite samo 2 prsta. Šta reći? Dakle, evo i spiska. Napravili smo spisak 45 shortcuts. Ovo je prvi spisak od 15 korisnih skraćenica. 1. Formatiranje obekta, CTRL + 1 2. Insertovanje tekućeg datuma, CTRL + 3. Insertovanje tekućeg vremena , CTRL + SHIFT + 4. Ponavljanje poslednje akcije, F4 5. Editovanje comment, SHIFT + F2 6. Autosum selektovanih ćelija, ALT + = 7. Drop down lista, ALT + strelica nadole 8. Unošenje nekoliko linija u ćeliju, ALT + ENTER 9. Insertovanje novog sheeta, SHIFT + F11 10. Editovanje ćelije, F2 11. Hajdovanje reda, CTRL + 9 12. Hajdovanje kolone, CTRL + 0 13. Unhajdovanje reda, CTRL + SHIFT + 9 14. Unhajdovanje kolone, CTRL + SHIFT + 0 15. Rekalkulisanje formula, F9

www.mcb.rs/blog

16

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 4.deo: Custom sort

Novi trikovi u Excelu – ovog puta o Custom Sortu.

Sigurno ste bili u situaciji da tabelu u Excelu prikažete tako da je sortirana (poređana) po redosledu sadržaja u ćelijama. Tada biste koristili komandu za sortiranje. Ukoliko je u pitanju tekst, sortirali biste abecedno (A-Z, ili Z-A) a ukoliko su u pitanju brojevi, sortirali biste od najmanjeg do najvećeg (0-9), ili od najvećeg do najmanjeg (9-0). Za komplikovanija sortiranja sortirali biste tabelu po dve, tri ili više kolona. A šta ste radili da sortirate tabelu po mesecima, gde su imena meseci napisana u obliku teksta? Kliknuli biste na “Sort” a Excel ih poreda: april, avgust, decembar, februar, januar, juli, juni, maj, mart, novembar, oktobar i septembar. Isto se dešava ako sortirate kolonu po danima u sedmici: četvrtak, nedelja, petak, ponedeljak, sreda, subota, utorak. Ovo se dešava jer Excel sortira tekst po abecedi. 

Kako objasniti Excelu da redosled imena meseci i imena dana u sedmici ne sledi abecedu?

Neki pomoću formula izvlače redne brojeve meseci u posebnu kolonu pa sortiraju po njoj, drugi ručno preslaguju tabelu, treći… Eh, treći… 

Što rek’o Maca: “Niđe veze, oči moje lepe.”

Rešenje je u opciji “Custom Sort”. Ova opcija omogućava da sami napravite “svoj” redosled sortiranja. I taj redosled ne mora biti ni abecedni ni po redosledu brojeva. Pri sortiranju umesto uobičajenog abecednog ili sortiranja po brojevima odaberete svoje sortiranje. Idealno rešenje za nestandardno sortiranje, poput sortiranja po imenu meseci ili po imenu dana u sedmici. Ili po bilo čemu drugom. Tabela u primeru sadrži podatke o ljudima, gradovima, iznosima i danima u sedmici. Recimo da želimo sortirati tabelu po danima u sedmici (ponedeljak, utorak…). Obično sortiranje bi poredalo dane u sedmici abecedno (četvrtak, nedelja, petak, ponedeljak, sreda, subota, utorak).

www.mcb.rs/blog

17

MCB Menadžment Centar Beograd

Partner u controllingu

Da bi Excel znao koji je redosled dana u sedmici, potrebno je da definišete posebnu listu za sortiranje. To ćete uraditi na sledeći način: Korak 1: Kliknite na samo jednu ćeliju unutar tabele i na tabu “Data” odaberite “Sort”.

Korak 2: Odaberite kolonu po kojoj želite sortirati (“Dan”).

www.mcb.rs/blog

18

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 3: Na listi “Order” odaberite “Custom List”.

Korak 4: U levom prozoru odaberite novu listu (NEW LIST).

www.mcb.rs/blog

19

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 5: U desnom prozoru unesite dane u sedmici po željenom redosledu (ponedeljak, utorak, sreda, četvrtak, petak, subota, nedelja). Nakon unosa svakog dana pritisnite tipku ENTER.

Korak 6: Kliknite na dugme “Add” za dodavanje nove liste za sortiranje u spisak postojećih.

www.mcb.rs/blog

20

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 7: Lista se pojavljuje u levom prozoru. Proverite da je ona odabrana i kliknite na dugme “OK”.

Korak 8: Sada se vidi da će kolona biti sortirana po danima u sedmici na odgovarajući način. Kliknite još jednom na dugme “OK”.

www.mcb.rs/blog

21

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 9: Kolona je sortirana po danima u sedmici.

Idući put kada imate potrebu za ovakvim sortiranjem po danu u sedmici, samo odaberite listu u levom prozoru i kliknite na dugme “OK”. Liste za sortiranje možete dodavati, menjati i brisati. Ovo je 4.tekst o trikovima u Excelu. Da li ima neka tema koja vas posebno zanima?

www.mcb.rs/blog

22

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 5.deo : Shortcuts u Excelu 2/3

Ovde je novih 15 shorhcuts (prečica sa tastature). 1. Selektovanje podataka u regionu, CTRL + A (CTRL + *) 2. Pokazivanje formula, CTRL + SHIFT + ` (CTRL + ~) 3. Absolutne i relativne reference, F4 (dok editujete ćeliju) 4. Formatiranje kao currency, CTRL + SHIFT + 4 5. Formatiranje kao number, CTRL + SHIFT + 1 6. Ouline border, CTRL + SHIFT + 7 7. Otvaranje dijaloga za makro, ALT + F8 8. Kopiranje gornje ćelije, CTRL + D 9. Sledeći worksheet, CTRL + TAB 10. Prethodni worksheet, CTRL + SHIFT + TAB 11. Sledeći sheet, CTRL + PgDn 12. Prethodni sheet, CTRL + PgUp 13. Kraj baze podataka, CTRL + End 14. Početak baze podataka, CTRL + Home 15. Šetanje po bazi podataka, CTRL + strelice levo/desno/gore/dole

www.mcb.rs/blog

23

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 6.deo: Custom View Kako sačuvati postavke filtera i skrivenih redova i kolona za kasniju upotrebu? Dugo se nisam javljao jer sam bio u fazi preispitivanja. Pitanje je glasilo: da li da nastavim da izvlačim iz naftalina najkomplikovanije Excel zahvate i objašnjavam ih nadugo i naširoko jer ih je teško objasniti u samo nekoliko rečenica? Alternativa je bila da smislim nešto lakše, korisnije i zanimljivije, a naročito kraće (da, ovde je dužina bitna). Ta alternativa mi je bila jako privlačna. Problem je predstavljala samo jedna sitnica: nisam imao pojma šta da stavim u alternativu. Jedan patuljak na levom (ili desnom, ne sećam se baš najbolje) ramenu kaže: „Samo ti nastavi sa naftalinskim zahvatima, ljudima je to potrebno, tako da ne moraju da čitaju hrpe knjiga i odlaze na tamo neke duge kurseve gde upoznaju samo bubuljičave štrebere i ružne ribe. Pa šta ako je nadugo i naširoko? Ako hoće da nauče, neka se malo i pomuče“. Pazi, majku mu, stvarno je tako“, pomislim ja i počnem da pišem tekst o Excelovim 3-D matričnim formulama. Ali ne lezi vraže: jednom zakotrljana grudva krenula je niz brdo i pretvorila se u lavinu. Tako je i alternativa sa početka priče počela da poprima jasnije obrise. Na kraju je stala ispred mene i rekla: „Hoću van, vodi me u šetnju. “Rekao sam da ću je izvesti pod uslovom da ne plaši prolaznike koji zastanu da je bolje pogledaju, da ne grize one koji je žele pomilovati i, ukratko, da ne zažalim što sam je poveo među ljude. Tokom te prve šetnje sam pobliže upoznao Alternativu: umiljata je, zna kada treba da prestane da gnjavi (za razliku od autora ovog teksta) a najvažnije je da ne zaudara po naftalinu. Ipak, trenutak otkrovenja se desio kada je jedna gospođa uzviknula oduševljeno: „Ih, pa nisam ni znala da na svetu ima ovakvih pasa“. I tako ja shvatim o čemu treba da pišem (ili bar ja mislim da treba): o stvarima koje nisu obavezno komplikovane, koje su potrebne ljudima, koje im olakšavaju život i, najvažnije od svega, o mogućnostima Excela o kojima se malo zna, još manje govori a pogotovo malo koristi. Govorim o običnim opcijama koje se vide na trakama s alatima, ali ih većina ljudi ne vidi ili ne zna čemu služi. Prva u nizu (verujte mi na reč, ima ih mnogo) je Pohranjeni ili prilagođeni prikaz. Ukratko, promenite prikaz radnog lista na način da npr. uključite filtere, sakrijete neke redove i kolone. Zatim taj prikaz pohranite. Vratite se na prvobitni prikaz i kada želite da ponovo vidite podatke s uključenim filterima i skrivenim redovima i kolonama prosto odaberete kreirani prikaz. Ovo je korisno ukoliko pravite kompleksne prikaze sa mnoštvom filtera i sakrivanjem kolona i redova.

www.mcb.rs/blog

24

MCB Menadžment Centar Beograd

Partner u controllingu

Dakle, kako napraviti Custom View? Prvo sačuvajte originalan (uobičajeni) prikaz na sedeći način: na traci View kliknite na Custom Views. Korak 1: Custom View na traci View

Korak 2: Prozor za dodavanje prikaza Kliknite na dugme Add za dodavanje novog prikaza.

Dajte prikazu prikladno ime i Kliknite na OK dugme.

Korak 3: Imenovanje originalnog prikaza

www.mcb.rs/blog

25

MCB Menadžment Centar Beograd

Partner u controllingu

A sad, rokenrol: izmenimo prikaz aktivnog radnog lista. Kako? Pa, eto, uključite nekoliko filtera, sakrijte poneki stubac, sakrijte nekoliko redova. Korak 4: menjanje originalnog prikaza

Pošto smo podesili željeni prikaz, treba ga sačuvati. Klik na View traku pa na Custom Views. Opet dugme Add za dodavanje novog pregleda: Korak 5: Kreiranje prikaza

Dajemo ime novoj bebici… Ah, da! Usput, uključite kvačice ukoliko želite da se u prikazu sačuvaju i podešavanja za štampu (prva kvačica) i/ili skriveni redovi i stupci i postavke filtera (druga kvačica).

www.mcb.rs/blog

26

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 6: Imenovanje prikaza i podešavanje opcija

Klik na dugme OK za pohravnjivanje prikaza. Eh, sada možemo da se igramo prekidačem. Ima redova, nema redova; ima kolona, nema kolona, uključen filter, isključen filter…

Da vidimo da li radi: Na traci View klik na Custom Views. Aha, evo ih: Originalni (prvi kojeg smo napravili) i Filter_1 (ovaj sa filterima). Ima još nekih, ali oni nisu tema današnje emisije. Odaberimo „Originalni“ i klik na Show dugme za prikaz. I evo ih, svi su tu. A sad „Filter_1“ pa dugme Show i vide se samo oni koji su prošli kroz sito, pardon – filter. Korak 7: Izbor prikaza

Sad ga ima, sad ga nema. Kada nam ovi prekidači dosade, možemo ih se otarasiti na sledeći način: na traci View klik na Custom Views, odaberemo prikaz i kliknemo na dugme Delete.

www.mcb.rs/blog

27

MCB Menadžment Centar Beograd

Partner u controllingu

Korak 8: Brisanje prikaza

Važna napomena: prikaz se može „prozvati“ samo kada je aktivan onaj radni list na kome je prikaz kreiran. A o 3-D matričnim formulama u jednom od narednih nastavaka. Ko čita, taj i dočeka (ili tako nekako).

www.mcb.rs/blog

28

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 7. deo: Sekundarna osa, ili kako prikazati podatke u dve skale na chartu Nakon 6 (šest) tekstova o raznim funkcijama, skraćenicama, menijima, filterima i sortiranjima, mislim da je red da se malo pozabavimo i grafičkim aspektom Excela. Za početak dosta jednostavna, ali ništa manje moćna stvar: sekundarna (dvojna) osa. Kada sam je otkrio, zažalio sam što ranije nisam više čitao uputstva a manje učio metodom pokušaja (pa šta bude). Uštedeo bih tada mnogo muke i vremena. Verovatno ste bar jednom bili u situaciji da na istom grafikonu treba da prikažete dve serije podataka, sa velikom razlikom u vrednostima.

Slika 1: Tabela sa dve serije podataka, gde su brojevi u jednoj seriji mnogo veći

www.mcb.rs/blog

29

MCB Menadžment Centar Beograd

Partner u controllingu

Zamislimo da treba da prikažemo odnos dobi zaposlenika i njihovih plata. Jedna serija će prikazivati podatke o dobi a druga o visini plate.

Slika 2: Grafikon sa dve serije podataka gde se ne vidi korelacija između podataka usled prevelike razlike.

U takvoj situaciji grafikon je tačan, ali i neupotrebljiv. Zašto? Pa, zato što je razlika u brojevima tolika da se korelacija između dobi i plate ne može uočiti. U takvim situacijama najčešće se jedna serija obriše, ili se naprave dva grafikona, ili se izračuna neki koeficent pa se on stavi na grafikon, ili…da ne nabrajam. Dosta često ljudi sasvim odustanu od prikaza takvih podataka. Sekundarna (dvojna) osa omogućava smislen prikaz dve serije podataka na grafikonu, čak i kada je razlika između vrednosti jako velika. Dvojna osa je dodatna vertikalna osa sa desne strane koja prikazuje jednu seriju podataka u različitoj skali. Taj prikaz je proporcionalan primarnoj osi (koja prikazuje drugu seriju podataka), tako da se lako može uočiti korelacija između serija podataka (u ovom slučaju, između dobi i plate).

Slika 3: Prikaz dve serije podataka pomoću sekundarne ose

www.mcb.rs/blog

30

MCB Menadžment Centar Beograd

Partner u controllingu

Da biste napravili prikaz sa sekundarnom osom, prvo napravite obični grafikon sa prikazom svih potrebnih podataka.

Korak 1: Kreiranje grafikona sa dve serije podataka

Selektujte jednu seriju podataka i kliknite na desnu tipku miša. Odaberite “Format Data Series“.

Korak 2: Selektovanje serije podataka

www.mcb.rs/blog

31

MCB Menadžment Centar Beograd

Partner u controllingu

U “Series Options” odaberite “Secondary Axis” i kliknite na dugme “Close” za zatvaranje prozorčića.

Korak 3: Odabir sekundarne ose

Sa desne strane ćete videti sekundarnu osu u sopstvenoj skali koja je različita od primarne ose na levoj strani.

Korak 4: Prikaz druge serije podataka na sekundarnoj osi u različitoj skali

Na taj način ćete uočiti korelaciju između dve serije podataka.

www.mcb.rs/blog

32

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 8.deo: Shortcuts u Excelu 3/3

16 novih shortcuts (prečica sa tastature). 1. Zumiranje, CTRL + Točkić na mišu 2. Selektovanje kolone, CTRL + SPACE 3. Selektovanje reda, SHIFT + SPACE 4. Ubacivanje reda, CTRL + Numerička tastatura PLUS 5. Brisanje reda, CTRL + Numerička tastatura MINUS 6. Spisak names , F3 (kada editujete ćeliju) 7. Only Visible Cells; F5 (Go To) zatim Special i odaberi Visible Cells Only 8. Kopiranje; CTRL + C 9. Paste; CTRL + V 10. Otvaranje fajla; CTRL + O 11. Štampanje; CTRL + P 12. Novi fajl; CTRL + N 13. Find; CTRL + F 14. Replace; CTRL + H 15. Undo; CTRL + Z 16. Insertovanje hiperlinka, CTRL + K

www.mcb.rs/blog

33

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 9.deo: Print Titles ili kako da se prvi red ponavlja na svakom listu u Excelu Za ovaj put pripremili smo nešto jednostavno, ali ništa manje korisno: Print Titles. Za početak, zamislite tabelu sa petnaestak stubaca, popunjenu brojkama. Zamislili ste takvu tabelu na papiru? Lepo. A sada, zamislite da ta tabela ima nekoliko stotina ili, ne daj Bože, hiljada redova… Zamislili ste? Dobro, a sada, kako takva tabela izgleda na papiru? Izgleda lepo, zar ne? Sada uzmite, recimo, dvanaesti list i pogledajte deveti stubac. Znate li o kojem podatku je reč? Naravno da ne znate jer su imena stubaca prikazana samo na prvom listu a na ostalim ne. To je problem kad odštampate veliku tabelu na više listova: imena stubaca su prikazana samo na prvom listu. Kako se ljudi dovijaju u ovakvim situacijama? Stavljaju papire “pod konac“, lepe, kopiraju i šta sve ne. Užas! Srećom, u Excelu postoji opcija za prikaz imena kolona na svakom listu: zove se “Print Titles“. Prosto k’o pasulj: Page Layout, Print Titles, Odaberete red ili više njih za koje želite da se ponavljaju na vrhu tabele na svakom listu i to je to. Evo i kako: Na meniju “Page Layout” odaberite “Print Titles“. Ako koristite Excel 2003, idete na opciju “File” pa onda “Page Setup”.

Korak 1: Opcija “Print Titles” na meniju “Page Layout”

www.mcb.rs/blog

34

MCB Menadžment Centar Beograd

Partner u controllingu

Odaberite “Sheet“.

Korak 2: Tab “Sheet” opcije “Print Titles”

www.mcb.rs/blog

35

MCB Menadžment Centar Beograd

Partner u controllingu

Kliknite na “Rows to repeat on top” (redovi koji će se ponavljati na vrhu). Sada kliknite na red (ili više njih) za koji želite da se ponavlja na svakom listu.

Korak 3: Odabrani redovi koji će se ponavljati na svakom listu

Kliknite na “OK“. Usput, možete selektovati i stupce za koje želite da se uvek prikazuju sa leve strane (“Columns to repeat at left“). Ovo je korisno ukoliko je tabela jako široka, tako da se na jednom listu ne mogu prikazati svi stupci. U takvim situacijama korisno je uvek videti krajnji levi stubac. Na ovom linku je interaktivni multimedijalni prikaz podešavanja ove opcije .Svi komentari su dobrodošli.

www.mcb.rs/blog

36

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 10.deo: Sparklines ili grafikon unutar jedne ćelije Išamara me Bojan neki dan, i to sa punim pravom: na relativno jednostavno pitanje odgovorio sam da takva opcija ne postoji. Zaboravio sam na jedno od najvažnijih pravila, a to je da kada nešto ne koristiš redovno, počneš da zaboravljaš. Pouka priče: Ukoliko želite da budete dobri u Excelu, stalno ga koristite. Najgore što možete da uradite je da se bojite greške. E, dobro, dosta teorije, da pređemo na konkretne stvari. Bojane, predlažem da za ove tekstove uvedemo ograničenje na dužinu teksta jer ću opet krenuti od “Kulina bana”, preko “Markovih konaka” pa kad stignemo do srži… Elem, za ovaj put mislim da je vreme da predstavimo novinu koja je stigla sa verzijom Excela 2010. Čini mi se da je Irma tražila tekst o Sparklines, pa evo… Sparklines su potpuna novina koja je stigla sa verzijom 2010. Ukratko, to je grafikon unutar jedne ćelije. Kako radi? Ubacite sparkline, odaberete raspon ćelija (koje sadrže brojeve) koje želite prikazati i na kraju ćeliju u koju želite smestiti sparkline. Izmenom vrednosti u ćelijama mjenja se i sparkline. Postoje tri oblika sparkline a mogu se i dodatno formatirati po pitanju boja, prikaza najvećih i najmanjih vrednosti itd. Čemu služi? Vrlo je zgodan kod velikih tabela sa mnogo redova i kolona koje sadrže brojčane podatke jer se trendovi ne mogu tako lako uočiti a grafikon sa prikazom svih vrednosti bi bio prenatrpan. Dakle, otvorite fajl sa tabelom koja sadrži brojčane podatke. U ovom primeru napravićemo prikaz za svaku osobu, tj.za svaki red brojčanih podataka. Kliknite u ćeliju u koju želite ubaciti sparkline.

Slika 1: Dodavanje sparkline

www.mcb.rs/blog

37

MCB Menadžment Centar Beograd

Partner u controllingu

Selektujte raspon ćelija koje prikazujete i ćeliju u koju ćete smestiti sparkline.

Slika 2: Odabir ćelija

Dobili ste sparkline za odabrane ćelije, tj.samo za jedan red podataka. Sada ga “svucite” naniže – baš kao kod kopiranja formula.

Slika 3: Kopiranje sparkline

www.mcb.rs/blog

38

MCB Menadžment Centar Beograd

Partner u controllingu

Za promenu vrste sparkline selektujte sve ćelije sa sparklineom kojima želite izmeniti izgled i kliknite na meni “Design”.

Slika 4: Selektovanje sparkline

Odaberite Line (Linijski), Column (Stubići) ili Win/Loss (Dobit/Gubitak).

Slika 5: Promena vrste sparkline

www.mcb.rs/blog

39

MCB Menadžment Centar Beograd

Partner u controllingu

Određene podatke možete i naglasiti: minimalne i maksimalne vrednosti, početak i kraj te negativne vrednosti.

Slika 6: Promena opcija prikaza sparkline

Za promenu boje sparklinea odaberite odgovarajući uzorak u meniju “Design”.

Slika 7: Promena formata sparkline

www.mcb.rs/blog

40

MCB Menadžment Centar Beograd

Partner u controllingu

Obrisati možete celu grupu (niz) sparklines ili samo odabrane. Kliknite desnom tipkom miša na sparkline, zatim na “Sparklines” i odaberite da li želite da obrišete odabranu grupu ili samo odabrani sparkline.

Slika 8: Brisanje sparkline

Sve u svemu, napokon smo dobili dugo očekivanu mogućnost grafičkog prikaza unutar jedne ćelije. Najviše koristi ćemo imati kod velikih tabela sa mnogo redova i kolona čiji bi prikaz na jednom grafikonu bio u najmanju ruku “nezgrapan”. Na ovom linku je interaktivni prikaz korištenja opcije “Sparklines”. Uključite zvučnike i sledite upute (kliknite tamo gde pokazuju žute strelice).

www.mcb.rs/blog

41

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 11.deo: VLOOKUP, druga najčešće korišćena Excel funkcija ili: Šta povezuje Excel, biologiju i gospodina Bulajića PROLOG Pre petnaestak godina, baš negde u ovo doba, dođe meni dobra prijateljica i – umesto „dobar dan“ ili nečeg sličnog osu paljbu još sa vrata: „J… te tvoj Excel šugavi, da te j…! Umalo da izgubim posao zbog njega! Poslušavši tvoj savet, gospodina ‘Bulajića’ sam prekrstila u ‘Biologic’ a, kako je jako sujetan i još više uticajan, malo je nedostajalo da dobijem otkaz.“ Pitate me šta se desilo? Pa, kad već navaljujete, evo… Čin prvi Daleko najkorištenija funkcija u Excelu je sigurno suma. A znate li koja je druga? Ne znam ni ja, ali gajim određene sumnje. Nakon više od 15 godina korištenja Excela jedna funkcija „odskače“ po učestalosti korištenja. Šta je zajedničko izdavačkim kućama, vojsci, bankama, javnom sektoru, privatnim, međunarodnim i nevladinim organizacijama? Osim činjenice da je moja malenkost radila u bar po dva predstavnika svakog od njih? E, pa… zajedničko im je da svi pri korištenju Excela osim funkcije za zbir najčešće koriste funkciju LOOKUP.Postoje VLOOKUP i HLOOKUP, uz napomenu da se VLOOKUP daleko češće koristi pa ćemo se ovde baviti samo njim. Čemu služi VLOOKUP? Prosto rečeno, ova funkcija koristi vrednost iz jedne tabele, nalazi tu vrednost u drugoj tabeli i daje vrednost određene kolone u istom redu. Ovako verovatno zvuči komplikovano pa ćemo demonstrirati na jednostavnom primeru. Čin drugi Recimo da u jednoj tabeli imate spisak osoba sa podacima o JMBG, imenu i prezimenu.

Slika 1. Tabela s imenima u koju treba dodati adrese iz druge tabele

www.mcb.rs/blog

42

MCB Menadžment Centar Beograd

Partner u controllingu

Potrebni su vam podaci o adresi tih osoba. Nažalost, adrese se nalaze u drugoj tabeli koja ne sadrži imena i prezimena već samo njihove JMBG-ove s adresama.

Slika 2. Tabela s JMBG i adresama iz koje treba dobiti adrese

Potrebno je na neki način povezati ove dve tabele, tako da svakom JMBG-u iz prve tabele pridružite odgovarajuću adresu iz druge tabele. Za one koji se bave bazama podataka ovo ne predstavlja problem jer koriste „join“. Ovaj tekst pokazuje kako se to radi u Excelu. Šta je potrebno za povezivanje ove dve tabele? Zajednički podatak, odnosno kolona koja se nalazi u obe tabele. U našem slučaju to je kolona JMBG.

Slika 3. Obe tabele s označenim zajedničkim kolonama (JMBG)

www.mcb.rs/blog

43

MCB Menadžment Centar Beograd

Partner u controllingu

Da bi se dobio podatak o adresi za svaki JMBG i dodao u prvu tabelu, VLOOKUP funkcija koristi vrednost u prvoj tabeli (JMBG) (korak 1), nalazi istu vrednost (odabrani JMBG) u drugoj tabeli s adresama (korak 2) i očitava sadržaj određene kolone (u našem slučaju adresu odgovarajućeg JMBG-a) – korak 3. Konačno, vraća rezultat u ćeliju u koju ste ubacili funkciju (korak 4).

Slika 4. Šema rada funkcije VLOOKUP

Ovo je najvažnije u vezi LOOKUP-a. Bitno je da shvatite kako funkcija radi, sve ostalo je tehnika. Ima još nekoliko sitnica na koje morate obratiti pažnju pri korištenju funkcije VLOOKUP, ali više o njima na kraju.

www.mcb.rs/blog

44

MCB Menadžment Centar Beograd

Partner u controllingu

Dakle, odaberite ćeliju u koju želite dodati adresu. Ubacite funkciju (Insert=>Function, ili kombinacija tipki SHIFT+F3). Ukucajte „VLOOKUP“ i pritisnite dugme „Go“. U listi odaberite „VLOOKUP“ i kliknite na „OK“. Pojavljuje se prozor za definisanje argumenata funkcije.

Slika 5. Prozor u kome se definišu argumenti funkcije VLOOKUP

Intermezzo Ovde moram napraviti digresiju i iskoristiti trenutak da dam malo objašnjenje o funkcijama uopšte. Mada Excel ima preko 300 ugrađenih funkcija, gotovo sve rade na manje-više isti način: odaberete funkciju, nahranite je („definišete argumente“) i dobijete rezultat. Argumenti su parametri koje funkcija koristi da bi radila. Funkcije imaju različit broj argumenata: od jednog ili nijednog pa do 3-4 ili čak i više. Npr. za funkciju sabiranja morate definisati brojeve koje sabirate. Ti brojevi su argumenti funkcije sabiranja. Čin treći Dakle, VLOOKUP ima 3+1 argument: 1. lookup_value (vrednost iz prve tabele, u našem slučaju to je JMBG čiju adresu tražimo), 2. table_array (lookup tabela, ili raspon ćelija u drugoj tabeli u kojoj tražimo vrednost), 3.col_index_num ili redni broj kolone/stupca iz kojeg tražimo rezultat (kolona s adresom), ali brojano od kolone koja je zajednička obema tabelama (JMBG) udesno. Kada sam naveo 3+1 argument, to znači da su 3 argumenta obavezna a jedan opcioni (neobavezan). U gornjem prozoru (važi za sve funkcije) obavezni argumenti su označeni podebljanim slovima a opcioni običnim tekstom. Ipak, bolje je definisati i neobavezne argumente a videćete i zašto. Usput, verovatno se već pitate kakve veze sve ovo ima sa gospodinom Bulajićem sa početka? Hehehe, bez brige, uskoro dolazimo i do toga. www.mcb.rs/blog

45

MCB Menadžment Centar Beograd

Partner u controllingu

Dakle, idemo definisati argumente funkcije VLOOKUP (stručni termin), ili „nahraniti funkciju“ (moj izraz).

Lookup_value je vrednost iz prve tabele s imenima za koju tražimo adresu u drugoj tabeli. Kliknite u ovo polje a zatim na JMBG u tabeli s imenima. Dakle, tražimo taj JMBG.

Slika 6. Odabir vrednosti koju tražimo

Table_array je raspon ćelija u koji gledamo kako bismo pronašli odgovarajući JMBG i željenu adresu. Kliknite u ovo polje i odaberite raspon ćelija u tabeli s adresama (lookup tabeli), ali tako da selektujete prvo zajedničku kolonu (u ovom slučaju JMBG) i kolone desno od nje. Ovo je bitno jer možda kolona JMBG nije prva kolona u lookup tabeli. Pri odabiru obavezno uključite i imena kolona. Pazite na to da raspon ćelija sadrži dolasrke oznake ($) ispred imena kolona i broja redova (npr. $A$1:$D$8).Ovim ste definisali raspon ćelija u kome tražite JMBG-ove.

www.mcb.rs/blog

46

MCB Menadžment Centar Beograd

Partner u controllingu

Slika 7. Odabir raspona ćelija u kome tražimo odabranu vrednost

Col_index_num je redni broj kolone koja sadrži vrednost koju tražimo (adresa), brojano od zajedničke vrednosti (JMBG) u lookup tabeli. Zajednička kolona (JMBG) u ovom računanju je uvek prva a ako je adresa 1 kolonu udesno, onda ovde unesite broj 2. Znači, ovo je redni broj stupca s traženom vrednošću (adresom) u odabranom rasponu („table_array“).Ovim ste definisali redni broj kolone čiji sadržaj tražite u odabranom rasponu.

Slika 8. Unos rednog broja kolone u selektovanom rasponu ćelija koja sadrži traženu vrednost

www.mcb.rs/blog

47

MCB Menadžment Centar Beograd

Partner u controllingu

Range_Lookup definiše šta funkcija da radi ukoliko ne pronađe odgovarajuću vrednost u lookup tabeli (ukoliko traženi JMBG ne postoji u tabeli s adresama). „False“ znači da traži tačnu vrednost (odgovarajući JMBG) a ako ga ne pronađe, da javi grešku, dok „True“ znači da u slučaju nepostojanja odgovarajućeg JMBG-a kao rezultat vrati vrednost najsličnijeg JMBG-a. Ukoliko ostavite ovo polje prazno, Excel će se ponašati kao da ste uneli „True“, odnosno, ako ne pronađe traženi JMBG, vratiće adresu JMBG-a koji je najsličniji traženom. U ovom slučaju (odabrali ste “True”, ili ostavili prazno), čak i ako se traženi JMBG nalazi u tabeli a neki sličan njemu je zbog sortiranja bliži početku tabele, funkcija će odabrati pogrešan JMB. Preporučujem da koristite „False“, osim ukoliko vam je baš svejedno hoćete li dobiti tačan ili približan rezultat. Ovim ste definisali da funkcija traži izričito identične JMBG-ove.

Slika 9. Podešavanje da li je prihvatljiva tačno odabrana vrednost, ili je prihvatljiva i približna ili slična

  

Ukoliko ste pravilno uneli argumente, u ovom prozoru ćete već videti rezultat za odabrani JMBG. Kliknite na dugme OK, kopirajte formulu u ćelije ispod i proverite rezultate. Eto, nadam se da nisam previše zapetljao.

EPILOG Ah, da… Kao što verovatno pretpostavljate (a ukoliko ne pretpostavljate, znači da sam loše objasnio), prijateljica iz prologa nije unela „FALSE“ u argumentu „Range_Lookup“ a funkcija (pošto nije pronašla gospodina Bulajića) „pokupila“ je najsličniju reč („Biologic“). Važne napomene (gotovo da ima više napomena nego osnovnog teksta, ali takva je funkcija a bojim se da nešto ne izostavim):

www.mcb.rs/blog

48

MCB Menadžment Centar Beograd

Partner u controllingu

 Kada selektujete lookup tabelu („table_array“ argument), uvek selektujte od kolone koja je zajednička obema tabelama udesno, tako da je zajednička kolona prva u odabranom rasponu ćelija. Ovo je bitno jer nekada zajednička kolona možda neće biti prva kolona u lookup tabeli.  Vrednost koju treba da dobijete treba da je u lookup tabeli u koloni koja je desno od zajedničke kolone u istoj tabeli. VLOOKUP funkcioniše samo udesno, ali ne i ulevo.  Kada selektujete table_array, pazite na to da u prozoru definisanja argumenata funkcije raspon ćelija sadrži dolarske oznake ($) ispred imena kolona i broja redova (npr. $G$1:$H$25). Najlakše ćete to uraditi ako u nakon odabira Table_Array-a selektujete raspon i pritisnete tipku F4 dok se ne pojave dolarske oznake ispred oznake kolone i broja reda. Ovo (apsolutna referenca) je bitno zbog kopiranja formule: ukoliko ne postavite dolarske oznake, raspon će se pri kopiranju promeniti i funkcija neće dati tačne rezultate (prosto je neverovatno koliko se ovo često javlja). O apsolutnim i relativnim referencama nekom drugom prilikom.  Toplo preporučujem da definišete argument Range_Lookup (TRUE/FALSE). Ako ostavite prazno a Excel ne nađe traženu vrednost u lookup tabeli (ili je u poretku slična vrednost bliža vrhu table), onda će uzeti najsličniju vrednost, što najčešće ne želite (setite se gospodina Bulajića).  Negde se navodi da lookup tabela mora biti sortirana po zajedničkoj vrednosti obe tabele, inače funkcija neće dati tačan rezultat. Po mom iskustvu, nije obavezno da tabela bude sortirana, ali je bitno da sadrži jedinstvene vrednosti (vidi dalje).  Za pravilan rad LOOKUP funkcije obavezno je da lookup tabela sadrži jedinstvene vrednosti zajedničke kolone. U našem primeru to znači da se jedan JMBG u tabeli s adresama ne sme ponavljati, odnosno može se pojaviti samo jednom. Kada bi se pojavio više puta i to sa različitim adresama, funkcija LOOKUP bi „pokupila“ onu adresu koja je prva u tabeli a ostale zanemarila.  Jako često se dešava da funkcija javi da nema odgovarajuće vrednosti u lookup tabeli, mada ona tamo postoji. Najčešće se to dešava u slučajevima gde su brojevi u jednoj od tabela formatirani kao tekst. Takvi brojevi izgledaju jednako, ali ih Excel tretira kao tekst i ne može da poveže „babe i žabe“. U takvim slučajevima je potrebno brojeve unesene kao tekst konvertovati u brojeve i funkcija će dati pravi rezultat. Ovo je takođe jedan od kandidata za najčešći uzrok greške u rezultatu funkcije VLOOKUP.

www.mcb.rs/blog

49

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu, 12 deo: Imenovani rasponi ili Kako komplikovane formule načiniti kraćim i jasnijim IMENOVANI RASPONI

U vreme kada sam bio mlad i nadobudan (naučio ponešto o Excelu), mislio sam da znam mnogo. Sve do jednog dana… Kolega mi je poslao fajl pripremljen u Excelu sa podacima o artiklima, naručenim količinama i jediničnim cenama, sa ciljem da ja taj fajl malo vizuelno doteram i proverim tačnost formula. „Formule nisu komplikovane, lako ćeš se snaći“ – bilo je poslednje što sam čuo pre nego što je kolega otišao na godišnji odmor. Hajd’, lepo, pomislih i bacih se na posao. Uistinu, tabela nije bila komplikovana, ali je kontrola formula počela da mi zadaje glavobolje. Da se odmah razumemo: nije bilo mnogo kolona ni komplikovanih proračuna, ali je bilo dosta redova pa sam se u jednom momentu uhvatio kako se pitam da li ćelija $A5655 na jednom radnom listu odgovara zbiru raspona ćelija G$696:O$891 sa drugog i još četiri slična raspona sa trećeg radnog lista, podeljenog sa nekim čudnim koeficentom.

Slika 1. Prvobitni izgled tabele

www.mcb.rs/blog

50

MCB Menadžment Centar Beograd

Partner u controllingu

Pošto je rok za završetak kontrole bio „juče“, nazovem kolegu (koji je već bio na plaži), unapred se radujući što ću mu bar malo pokvariti odmor. - Ahaaaaa, znači, to te „žulja“ – vedro je odvratio. – Nikakav problem, daj mi par minuta pa ću ti poslati verziju koja te neće zbunjivati. Ostadoh u čudu, pitajući se kako to misli pojednostaviti za nekoliko minuta. Već sam sebe zamišljao kako čitam uputstvo od 300 stranica u kome „sve lepo piše“ i, naravno, ne završavam posao na vreme. Ma, kakvo vreme, ne završavam ga nikako! Uistinu, za nekoliko minuta stigao mi je e-mail sa fajlom koji je izgledao identično onome kojeg sam već imao. Samo izgledao….

Slika 2. Izgled tabele nakon “izmena”

www.mcb.rs/blog

51

MCB Menadžment Centar Beograd

Partner u controllingu

Umesto slova, brojki i dolarskih znakova u formulama je pisalo: „cena*količina“, „SUM (količina), AVERAGE (cena)“, „cena u eurima“, „ukupna cena u eurima“ itd. Osećao sam se poput čoveka koji je mislio da je slep a onda je odjednom skinuo sunčane naočari.

Slika 3. Prikaz formula nakon „izmena“

Nisam mogao izdržati pa sam ga uveče opet nazvao (nisam se usuđivao da mu još jednom pokvarim dan na plaži) kako bih saznao kako je to napravio, a naročito kako je to uspeo za tako kratko vreme. Pa, vidi, u pitanju su tzv. imenovani rasponi. Odabereš raspon ćelija i daš mu neko ime. Nakon toga, u formulama koristiš ime tog raspona umesto referenciranja na ćelije. Tako su formule mnogo kraće i jasnije. Naravno, onda ih i neko ko nije upoznat sa fajlom – poput tebe, može lako razumeti.

www.mcb.rs/blog

52

MCB Menadžment Centar Beograd

Partner u controllingu

A kako praviš imenovane raspone? Odabereš raspon ćelija i u gornjem levom uglu (gde se vidi adresa aktivne ćelije) ukucaš ime raspona (u ovom primeru „Količina“) i pritisneš tipku ENTER. Pazi da ime raspona ne počinje donjom crtom ili brojem i da ne sadrži razmake.

Slika 4 Pravljenje imenovanog raspona pomoću Address Cell

- Onda, verovatno, u formuli samo ukucam ime raspona? - Upravo tako: Excel čak prepoznaje prva slova imena raspona i automatski ti nudi raspone koji odgovaraju unesenim slovima. A spisak kreiranih imenovanih raspona možeš videti ili ubaciti u formulu klikom na adresu aktivne ćelije i odabirom željenog raspona.

Slika 5. Excel “prepoznaje” imenovani raspon

www.mcb.rs/blog

53

MCB Menadžment Centar Beograd

Partner u controllingu

- Mogu li se rasponi ćelija na koje se odnose imenovani rasponi promeniti? Mislim na situacije kada npr. dodam nove redove koje želim uključiti u već imenovani raspon? - Da, ali moraš ići na meni (tab, ribbon) „Formulas“ pa odabrati „Name Manager“. Odabereš raspon, klikneš na „Edit“, selektuješ željene ćelije, potvrdiš pritiskom na kvačicu i klikneš na dugme „Close“.

Slika 6. Izmena postojećeg imenovanog raspona

Brisanje je takođe lako: odabereš imenovani raspon i klikneš na „Delete“. Naravno, brisanje raspona ne znači i brisanje podataka na koje se imenovani raspon odnosi, već samo brisanje imena. Kod brisanja zapamti da će sve formule koje koriste imenovani raspon koji brišeš javiti grešku. - Lako je kreirati imenovani raspon za jednu grupu ćelija. Pretpostavimo da imam tabelu sa 20 kolona, moram li 20 puta ponoviti postupak za kreiranje imenovanih raspona? - Ne, postoji mnogo brži način: selektuj kolone (uključujući i imena kolona) i na meniju „Formulas“ odaberi „Create from selection“, uključi opciju „Top Rows“ i dobićeš 20 imenovanih raspona koji se odnose na 20 kolona. Svaki raspon će dobiti ime po imenu kolone.

Slika 7. Pravljenje imenovanih raspona na osnovu odabranih podataka

www.mcb.rs/blog

54

MCB Menadžment Centar Beograd

Partner u controllingu

- Želim promeniti kurs eura? Vidim imenovani raspon u formulama i formule ispravno funkcionišu, ali ga ne vidim u spisku imenovanih raspona u gornjem levom uglu.

Slika 8 Imenovani raspon “EUR” se vidi u formulama, ali se ne vidi na spisku imenovanih raspona

- Pa izgledalo bi neprofesionalno da imamo usamljenu ćeliju na koju će se formule referencirati. Rešenje je da se napravi imenovani raspon koji neće biti nijedna ćelija već će biti pohranjen u memoriji fajla. U ovom slučaju to je kurs eura (1,95583 KM). Kada se u formuli bude koristio raspon pod imenom „EUR“, koristiće se vrednost koju si uneo (1,95583). Na meniju „Formulas“ odaberi „Define Name“. Dodeli ime (EUR) a u polju „Refers to“ unesi kurs eura. Jako bitna stvar je da li se imenovani raspon odnosi samo na određeni radni list ili na ceo fajl.

Slika 9. Kreiranje imenovanog raspona u memoriji fajla

www.mcb.rs/blog

55

MCB Menadžment Centar Beograd

Partner u controllingu

Napomene:  

Kod brisanja imenovanih raspona paziti na formule koje se referenciraju na imenovani raspon koji želiš obrisati jer formule više neće funkcionisati. Kod kreiranja imenovanih raspona je bolje koristiti meni „Formulas“ i definisati odmah je li imenovani raspon na nivou radnog lista ili fajla. Ovo je jako osetljivo pitanje jer mogu postojati dva imenovana raspona s istim imenom na dva radna lista. Kod komplikovanijih formula ovo lako može da zbuni. Preporučujem da se imenovani rasponi kreiraju na nivou fajla, ili bar da imaju jedinstvene nazive.

Najava: Pošto imamo sve sastojke (Data Validation, VLOOKUP i Imenovane raspone), „uz jednu žlicu“ INDIRECT funkcije, u jednom od narednih tekstova ćemo pokazati kako napraviti „pametne padajuće liste“, odnosno, kako napraviti da kada se u jednoj padajućoj listi odabere jedna opcija da se sadržaj druge padajuće liste automatski mjenja.

www.mcb.rs/blog

56

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 13. deo: Kako od Pivot tabele brzo napraviti tabelu sa popunjenim ćelijama Pivot tabele su odličan alat, ali imaju manu: “radi preglednosti” dosta polja prikazuju praznim. Ovo je naročito nezgodno kada želite podatke iz pivot tabele da prikažete na “klasičan” način. Ovo je lako rešiti ukoliko imate Excel 2010 ili 2013: uključite opciju “Repeat Row Labels” i rešena stvar. Ukoliko koristite Excel 2007 ili – ne daj Bože – stariju verziju, onda imate problem. Originalna pivot tabela se ne može menjati, tako da je potrebno napraviti kopiju podataka pa ih urediti.

www.mcb.rs/blog

57

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 14.deo: Besplatno konvertovanje .PDF fajlova u Excel Do ruku mi je nedavno došla adresa sajta za besplatnu konverziju .PDF fajlova u Excel (http://www.convertpdftoxls.com). Radi dosta jednostavno: uradite upload svog .PDF fajla na njihovoj stranici, a oni vam pošalju link za download Excel fajla. Odmah pri uploadu dobijete procenu koliko dugo će konverzija trajati.

Slika 1.: Stranica za konverziju .PDF fajlova u Excel format

A čemu to (kao u onom vicu o 4 vrste ljubavi)? Stvarno, zašto konvertovati .PDF fajl? Obično je razlog potreba da se podaci dodatno urede, bilo da je u pitanju sadržaj ili format, a nemate originalni fajl već samo verziju sačuvanu u .PDF formatu. Zašto nemate originalni fajl? Zato što je originalna verzija izgubljena, ili se ne zna ko je autor originalnog fajla, ili je .PDF fajl “skinut” sa nekog sajta, ili… Da ne nabrajam. Na tržištu imate mnoštvo alata za konverziju .PDF fajlova u Excel. Namerno koristim reč “tržište” jer se većina tih alata naplaćuje a kvalitet uveliko varira. Od onih koji se mogu dobiti besplatno malo ih je koji zadovoljavaju kvalitetom. Elem, posetim sajt http://www.convertpdftoxls.com i uradim upload .PDF fajla koji se sastojao od jednostavne tabele na jednoj stranici. Razmišljao sam ovako: ako ne uradi dobro prostu tabelu sastavljenu samo od brojki i slova, neću dalje ni pokušavati. Prvi korak je bio prilično obeshrabujući: procenjeno vreme konverzije: 30 minuta (kao: zbog opterećenosti servera. Pokušao sam narednih dana više puta i svaki put dobio istu poruku, tako da mislim da je to standardno vreme potrebno za konverziju jedne stranice). Ipak kada sam nakon pola sata dobio link za download konvertovanog fajla, prijatno sam se iznenadeo: fajl je konvertovan upravo kao da je napravljen u Excelu, s oblikom slova i bojama pozadine ćelija. www.mcb.rs/blog

58

MCB Menadžment Centar Beograd

Partner u controllingu

Onda sam odlučio pokušati postepeno sa sve komplikovanijim i obimnijim fajlovima. Na istu stranicu sam dodao jednostavan grafikon i .PDF je porastao sa 33 KB na 182 KB. Procenjeno vreme konverzije: opet 30 minuta i nisu lagali. Što se tiče kvaliteta, sadržaj i raspored ćelija je sačuvan, ali je potpuno izgubljen grafikon koji je bio na istom radnom listu kao i tabela. Takođe je izgubljena i boja pozadine ćelija. Pokušao sam sa grafikonom preko celog lista i sa pivot tabelom. Rezultati? Opet je grafika izgubljena a sačuvani su samo brojevi. Što se tiče pivot tabele, dosta vjerno je konvertovana, uz napomenu da kolone treba ostaviti nešto širim, inače može doći do greške pri konverziji. Ukratko: ukoliko ste u žurbi, dobar alat za jednokratne konverzije kraćih dokumenata ili pojedinačnih stranica. Kvalitetna konverzija sadržaja ćelija, dok se formatiranje gubi kako kompleksnost izvornog fajla raste. Takođe, ne obrađuje dobro grafičke elemente. Za obimnije dokumente baš treba dosta vremena. Vreme potrebno za konverziju je prilično tačno procenjeno. i najbolje od svega: besplatan je. Da ne zaboravim: nakon konverzije vaš Excel fajl će vam biti dostupan 24 sata, nakon čega će biti automatski obrisan. Takođe ga možete obrisati i ranije. Naravno, ukoliko vam je dugo čekati po pola sata, na stranici imate link za drugi alat koji radi brže i koji, naravno, nije besplatan. Za:   

ukoliko ste u žurbi dobar alat za jednokratne konverzije kraćih i jednostavnih dokumenata ukoliko vam format ćelija nije bitan, njihov sadržaj je izuzetno vjerno sačuvan najveće prednosti: očuvanje sadržaja ćelija, cena

 

očuvanje formata ćelija opada kako raste kompleksnost izvornog fajla najveće mane: vreme potrebno za konverziju, gubljenje grafičkih elemenata

Protiv:

Može poslužiti zavisno od potreba i mislim da će svako pronaći nešto za sebe.

www.mcb.rs/blog

59

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 15.deo: Lakše održavanje kompleksnih formula – pišite ih kao što programeri pišu svoj kôd Ako niste znali, svoje formule možete pisati i slično načinu na koji programeri pišu svoj kôd radi lakše čitljivosti. Taj pristup je naročito koristan pri pravljenju kompleksnih i ugnježdenih formula (formula unutar formula), kao i formula koje uključuju više logičkih operatora poput IF, OR, AND i sličnih. Zašto biste pisali formule na takav način? Pa, kod složenijih formula vrlo lako se izgubiti: Gde je početak? Šta je uslov čemu? Gde staviti zagradu? Gde mi nedostaje zagrada? Gde sam pogrešio? itd. Pogledajte formulu ispod i pokušajte otkriti šta ona radi:: =IF(DAY(AT$11)=1;INDIRECT(“Volumes!”&ADDRESS(ROW();COLUMN());TRUE);AVERAGE(IN DIRECT(“Volumes!”&ADDRESS(ROW();COLUMN()(DAY(AS$11)));TRUE):INDIRECT(“Volumes!”&ADDRESS(ROW();COLUMN());TRUE))) Ukratko, formula proverava ćeliju sa datumom i ako je prvi dan u mesecu, vraća određenu vrednost a ako nije, vraća različitu vrednost. Naravno da ste shvatili logiku formule. Pravo pitanje je: koliko vam je vremena trebalo? Pogledajte istu formulu napisanu malo drugačije:

Za uvlačenje redova koristio sam razmak a za prelom reda unutar ćelije kombinaciju tipki ALT+ENTER. Ne brinite: to ni na koji način ne utiče na funkcionalnost ili tačnost formule. Programeri imaju mogućnost dodavanja komentara na kraju svake linije koda. Na taj način i nakon dosta vremena (a lako se zaboravi) lako otkriju šta koji red programa radi. Takođe, to je dobro kada neko drugi treba da uređuje kod. U Excelu možete dodati komentar na nešto drugačiji način a da opet ne poremetite funkcionalnost ili ispravnost formule. Dodajte sledeće: + N (“vaš opis formule ili jednog njenog dela”) Svoj tekst stavite između navodnika. N funkcija konvertuje brojeve i datume u brojčane vrednosti, ali kada je u zagradi tekst uvek vraća nulu. Ovako ste logiku kompleksne formule prikazali vizuelno i stavili napomene unutar formule. Za lakše razumevanje složenih formula preporučujem korištenje imenovanih raspona. O imenovanim rasponima možete više pročitati ovde. www.mcb.rs/blog

60

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 16.deo: Proverite da li vrednost iz jedne tabele postoji u drugoj tabeli Pretpostavimo da imate dve tabele i da želite da brzo nađete koji podaci iz jedne tabele se nalaze u drugoj tabeli. U ćeliju pored prve tabele unesite sledeću formulu: =IF(COUNTIF($D$2:$D$10;A2)>0;”Postoji u Tabeli 2″;”Nema”) Gde je A2 adresa ćelije u prvoj tabeli koju proveravamo a raspon D2:D10 grupa ćelija u drugoj tabeli gde proveravamo da li postoji vrednost iz ćelije A2. Obratite pažnju na dolarske znakove ($).

Kopirajte formulu do dna prve tabele. U svakom redu dobićete rezultat koji govori da li vrednost u tom redu u koloni A postoji u Tabeli 2 (u rasponu ćelija D2:D10). Nije bitno koliko se puta vrednost ponavlja i tabele ne moraju biti sortirane.

www.mcb.rs/blog

61

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 17.deo: Matrične formule (formule nizova, “array” formule): Nemoguće kalkulacije su (ipak) moguće MATRIČNIM FORMULAMA (FORMULAMA NIZOVA ILI „ARRAY“ FORMULAMA) Formule nizova, formule matrica ili „array“ formule (sve su ovo sinonimi) spadaju među najmoćnije a opet najmanje poznate i korištene Excelove alate. One se zasnivaju na korištenju niza ćelija (raspona) umesto jedne ćelije u formuli. Na taj način moguće je napraviti kalkulacije za koje su inače potrebne dodatne kolone, ili ih je nemoguće napraviti. Ovde ćemo zagrebati samo vrh ledenog brijega jer bi detaljna obrada ove teme zahtevala poseban blog. Dakle, imajte na umu da ono što sledi nikako nije sve već tek mali deo onoga što je moguće postići ovim formulama. Dve osnovne vrste matričnih formula su višećelijske i jednoćelijske. Višećelijska matrična formula se unosi u više ćelija odjednom, dok se jednoćelijska unosi samo u jednu ćeliju. Na slici 1. je primer nabavke kancelarijskog materijala s opisom artikala, jediničnim cenama i količinom. Pretpostavimo da želite da izračunate ukupnu cenu nabavke. Verovatno ćete u novoj koloni ubaciti formulu koja množi cenu sa količinom. Nakon toga biste napravili zbir vrednosti u toj koloni.

Slika 1: Tabela sa jediničnim cenama i količinama

Istu stvar možete napraviti i pomoću matrične formule na sledeći način:  

-odaberite raspon ćelija (dakle, ne samo jednu) u koji želite da ubacite rezultat i otkucajte znak jednakosti (=) Odaberite ćelije sa količinama (C9:C13) a zatim otkucajte znak za množenje (*)

Slika 2: Selektovanje nizova za unos višećelijske matrične formule

www.mcb.rs/blog

62

MCB Menadžment Centar Beograd

Partner u controllingu

Odaberite ćelije sa cenama (D9:D13). Trebalo bi da piše: =C9:C13*D9:D13. Pritisnite kombinaciju tipki CTRL+SHIFT+ENTER (zato što se matrične formule ubacuju pomoću ove kombinacije tipki a ne pomoću tipke ENTER).

Slika 3: Rezultat višećelijske matrične formule

Dobili ste isti rezultat. Vitičaste zagrade ne ukucavate već ih Excel sam dodaje kada pritisnete CTRL+SHIFT+ENTER. Po njima prepoznajete da je u pitanju matrična formula.

Slika 4: Matričnu formulu prepoznajete po vitičastim zagradama Kako ova vrsta formula radi? Upoređuje dva odabrana niza i množi svaku ćeliju iz jednog niza sa ćelijom u istom redu u drugom nizu. Ovo je bio primer višećelijskih matričnih formula. Ah, da, već čujem pitanje: zašto bih to radeo baš tako? Paaaaa, ima nekoliko prilično čvrstih argumenata a nabrojaću samo najvažnije:    

Na ovaj način ste sigurni da je u svim ćelijama ista formula Matrična formula se ne može lako slučajno obrisati zato što se ne može menjati deo matrične formule, već samo celi niz odjednom Ne mogu se dodati ni novi redovi ni obrisati postojeći unutar formule nizova (ovo može biti i mana pa treba paziti gde koristiti formulu nizova) Ovakvu formulu ne može promeniti baš svako a naročito ne neki početnik itd.

www.mcb.rs/blog

63

MCB Menadžment Centar Beograd

Partner u controllingu

Dobro, ali zadatak je bio dobiti zbir svih troškova a ne po artiklima. Naravno, i to možete dobiti pomoću matrične formule, ovaj put jednoćelijske. Naročito je bitno da vam za ovaj eksperiment nije potrebna dodatna kolona pa ćemo je odmah odbaciti (Vidi vraga, šta je sad ovo?).

Slika 5: Odabir dva niza koji daju rezultat jednoćelijske matrične formule

U željenu ćeliju otkucajte: =SUM(C9:C13*D9:D13) i CTRL+SHIFT+ENTER. Dobićete zbir proizvoda cena i količina.

pritisnite

kombinaciju

tipki

Slika 6: Jednoćelijska matrična formula za izračun zbira proizvoda dva niza

I, taman kad sam pomislio da sam ušutkao dosadnog pametnjakovića sa početka priče, javlja se drugi: „oprostite, ali ja mislim da smo isti rezultat mogli dobiti i pomoću funkcije SUMPRODUCT. Znate, ta funkcija množi dva niza ćelija i daje zbir proizvoda“. Svakako, mogli ste, mogli ste (grrrrrrrrrrrrr… cenzurisano). Inače, već dugo muku mučim sa kolegom u kancelariji da prestane koristiti SUMPRODUCT za sve moguće izračune, ali uzalud. Uhvatio se k’o pijan plota i ne pušta. Ne osporavam, SUMPRODUCT ima odličnu osobinu: računa i podatke iz linkovanih fajlova bez potrebe da ih se otvara. Ali to je sve. SUMPRODUCT je samo to: zbir proizvoda i ništa više. www.mcb.rs/blog

64

MCB Menadžment Centar Beograd

Partner u controllingu

Sa druge strane, pomoću matričnih formula možete koristiti bilo koju funkciju i primeniti je na nizove ćelija (SUM, AVERAGE, VLOOKUP (!) itd.). E, sad… ako vam je potreban samo zbir proizvoda, u redu, eto vam SUMPRODUCT (baš kao u onom vicu kada čovek odgovara samoubicu koji pokušava da se baci sa mosta i govori mu kako je vreme lepo a ovaj kaže da mrzi vrućine, pa mu govori o lepim ženama koje šetaju okolo a on kaže kako ih mrzi jer ga je njegova napustila pa… Na kraju nesuđeni spasilac ispaljuje poslednji adut: „Evo, i Zvezda će osvojiti titulu“. Na to samoubica odgovara: „E, baš me briga za Zvezdu!“ – E, skači onda, p… ti materina grobarska!“). Gde sam ono stao? Aha, da: =SUM(C9:C13*D9:D13) a zatim CTRL+SHIFT+ENTER. Verovatno ste primetili da se ova formula unosi samo u jednu ćeliju. Ovakve formule nizova se nazivaju jednoćelijske formule nizova. Inače, ma koliko višećelijska formula niza sa početka izgledala atraktivnije, jednoćelijska je mnogo moćnija, kao što ćete videti u nastavku. Kad god učim neku novu funkcionalnost, volim da znam kako radi. Dakle, hajde da se zavučemo „pod haubu“ poslednje formule =SUM(C9:C13*D9:D13) i da vidimo kako to ona radi a da joj nije potrebna dodatna kolona. Kada posmatrate jednoćelijsku formulu niza, gledajte uvek od sredine prema van. U našem slučaju =SUM(C9:C13*D9:D13) u sredini je množenje ćelija iz dva raspona: C9 se množi sa D9, C10 se množi sa D10, C11 se množi sa D11, C12 se množi sa D12 a C13 se množi sa D13. Rezulati tih množenja čine niz brojeva (125;300;62.5;18.75;29)). Ovaj niz međurezultata nije vidljiv i postoji samo u Excelovoj memoriji, a izgleda ovako: =SUM({125;300;62.5;18.75;29}). Sada idemo prema van i dolazimo do funkcije sabiranja (SUM). Ona uzima prethodno dobijeni niz proizvoda i pravi zbir njegovih članova. Na ovaj način dobija se zbir proizvoda dva niza (količine i cene). Dakle, Excel prvo na nizu ćelija ili više njih napravi kalkulaciju a onda se na niz tako dobijenih rezultata primeni druga funkcija. Selektovanje celog niza kod višećelijskih matričnih formula.

Slika 7: Prozor „Go To“ dobijen pritiskom na tipku F5

www.mcb.rs/blog

65

MCB Menadžment Centar Beograd

Partner u controllingu

Kada iz bilo kog razloga želite promeniti ili obrisati višećelijsku matričnu formulu, morate je prethodno celu odabrati jer ne možete obrisati ili promeniti samo jedan njen deo. Ukoliko ne znate koje su dimenzije željenog niza, kliknite bilo gde unutar niza sa višećelijskom matričnom formulom, pritisnite tipku F5 a zatim dugme „Special“.

Slika 8: Odabir aktivnog niza

Odaberite „Current Array“ (aktivni niz) i kliknite na dugme „OK“. Tako ćete odabrati celi niz koji sadrži matričnu formulu. IZMENA POSTOJEĆE FORMULE NIZA

Nekada će biti potrebno promeniti (smanjiti, povećati i sl.) niz ili nizove koji se koriste u formuli. Odaberite celu matričnu formulu (kao što je objašnjeno u prethodnom pasusu), pritisnite tipku F2, napravite izmene i pritisnite kombinaciju tipki CTRL+SHIFT+ENTER. Pazite: ako slučajno zaborative pristisnuti CTRL+SHIFT+ENTER pa pritisnete samo ENTER ili CTRL+ENTER, dobićete poruku sa greškom ili pogrešne rezultate. U tom slučaju, samo pritisnite tipku F2 a zatim CTRL+SHIFT+ENTER. MANE MATRIČNIH FORMULA /FORMULA NIZA/“ARRAY“ FORMULA Na nekoliko mesta sam naišao na napomene da ova vrsta formula usporava rad kompjutera, ali to još nikad nisam primetio (a radim sa jako velikim nizovima i kompleksnim formulama), tako da mislim da je ovde u pitanju prilično slabašan kompjuter. Kako jednom reče Lane Gutović: „Imala je toliki nos da kada bi kiša padala, cigareta ne bi pokisnula… A pušila je na muštiklu.“ Stvarni problem nastaje kada fajl sa takvim formulama date nekome ko nije upoznat sa njima a poželi da nešto mijenja a ne prihvata ponuđenu pomoć. Kad god se to desi, ponesemo meze i piće i jako se dobro zabavimo.

www.mcb.rs/blog

66

MCB Menadžment Centar Beograd

Partner u controllingu

Isto tako, lako se može desiti da nakon nekog vremena ni sami ne znate kako radi formula koju ste napravili. Zato je dobro dokumentovati, koliko god to dosadno bilo. Za zgodan način dokumentovanja unutar same formule pogledajte ovde.

Ukratko Kako reče jedan moj prijatelj: „Pa, da remiziramo“. (Nije greška u kucanju, on tako kaže a ja nemam srca da ga ispravljam. Samo: psssssssssst!).      

Formule niza (matrične formule, „array“ formule) služe sa kalkulacije koje koriste nizove ćelija i za koje inače trebaju dodatne kolone ili ih je nemoguće izvesti Kod višećelijskih formula se unosi u više ćelija odjednom a kod jednoćelijskih u jednu ćeliju Formula se potrvđuje pritiskom na kombinaciju tipki CTRL+SHIFT+ENTER. Prepoznaju se po vitičastim zagradama. Inače, njih se ne ukucava već ih Excel sam dodaje Višećelijske se mogu menjati ili brisati isključivo kada se odabere celi niz koji se koristi Mogu usporiti rad kompjutera ako je reč o jako velikim nizovima (među nama, stalno ih koristim, ali takvu situaciju još nisam imao), ali inače su brže i od VBA (makro) koda koji ima istu funkcionalnost.

Mala napomena: formule će biti puno čitljivije ako koristite imenovane raspone umesto nerazumljivih adresa ćelija. Za objašnjenje imenovanih raspona pogledajte ovde. Korisni primeri („a sad, spektakl!“) U nastavku je nekoliko primera jednoćelijskih matričnih formula koje bi vam mogle poslužiti, ili dati ideju za nove. Ukoliko ste pročitali tekst dovde a još ne vidite prednosti formula nizova, nastavite čitati. Vjerujte mi na reč, kada primenite neku od ovih formula, neupućeni će misliti da ste čarobnjak.

www.mcb.rs/blog

67

MCB Menadžment Centar Beograd

Partner u controllingu

ZBIR 3 NAJMANJE VREDNOSTI U RASPONU Funkcija LARGE nalazi 3 najveće vrednosti i pohranjuje ih u međuniz. Zatim funkcija SUM sabira sve članove tog međuniza.

Slika 9: Formula za sabiranje 3 najveće vrednosti iz odabranog raspona

ZBIR RASPONA ĆELIJA KOJI SADRŽI GREŠKU

Slika 10: Formula za izračun zbira uz ignorisanje grešaka

Ova formula sabira ćelije u zadanom rasponu i ignoriše ćelije sa greškama (tretira ih kao prazne ćelije). www.mcb.rs/blog

68

MCB Menadžment Centar Beograd

Partner u controllingu

Prosek raspona tako da ne uzima u obzir nule. Problem sa funkcijom AVERAGE (prosek) je što u obzir uzima i ćelije koje sadrže nulu. Jedino ne uzima u obzir potpuno prazne ćelije. U slučaju da imate nule u nekim ćelijama a treba vam prosek koji ih ne uzima u obzir, možete probati ovu formulu:

Slika 11: Formula za izračun proseka uz ignorisanje nula

www.mcb.rs/blog

69

MCB Menadžment Centar Beograd

Partner u controllingu

REŠENJE PROBLEMA KOD SABIRANJA ZAOKRUŽENIH VREDNOSTI Zaokruživanje je čest problem a jednostavan primer je na slici 12. Sve formule su tačne, ali ukupna cena (obojena crveno) nije identična zbiru ukupnih cena po artiklima. Razlog je što vidimo samo dve decimale a u stvari ih ima više, tako da one u zbiru daju različit iznos. Ta je razlika najčešće 0,01 i zna se da nastaje zbog zaokruženja pa joj se ne daje prevelika važnost. Ipak, može postati bitna ako npr. prikazujete iznose u hiljadama. Rešenje je formula ispod (crna boja) koja zaokružuje ukupne cene po proizvodu a zatim daje zbir tog međuniza: =SUM(ROUND(F4:F8;2)) postaje: =SUM({122.66;6074.4;182.86;22.8;184.9}), što na kraju daje 6,587.62.

Slika 12: Formula za tačno sabiranje zaokruženih iznosa

www.mcb.rs/blog

70

MCB Menadžment Centar Beograd

Partner u controllingu

VLOOKUP sa više kriterija (naravno, radi i u levu stranu) Za kraj, primer koji verovatno ne bi ugledao svetlo dana da nije bilo Nenada i molbe da rešimo VLOOKUP po više kriterija pa mu ovim putem još jednom zahvaljujem. Ovde je primer VLOOKUP funkcionalnosti i na desnu i na levu stranu te po 4 kriterija. Nadam se da sam odgovoro zahtevu. Usput, ovde možete videti objašnjenje funkcije VLOOKUP. Kao što verovatno znate, funkcija VLOOKUP radi samo u desnu stranu i na osnovu jednog zajedničkog kriterija između dve tabele. Funkcije INDEX i MATCH omogućavaju rad i u levu stranu a matrična formula u ovom primeru omogućava i povezivanje po više kriterija (u našem primeru 4 kriterija). Formula radi na sledeći način (opet, naravno, idemo od sredine prema van):

Slika 13: Formula za dvostrani VLOOKUP po više kriterija

   

Kolona H se proverava i traži se red koji je jednak ćeliji A6 (H5:H261=A6). Kolona I se proverava i traži se red koji je jednak ćeliji B6. (I5:I261=A6) Kolona J se proverava i traži se red koji je jednak ćeliji C6. (J5:J261=A6) Kolona K se proverava i traži se red koji je jednak ćeliji D6. (K5:K261=A6)

Kada se u odabranom nizu (INDEX(G5:K261)) nađe red koji zadovoljava sva 4 nabrojana uslova (oznaka 1 u „MATCH(1“, što je u binarnom sistemu jednako „Da“ ili „Tačno“ ), onda se kao rezultat vraća sadržaj prve kolone u tom redu ( „0);1)“ ) U ovom primeru postoje 4 osobe sa kombinacijom imena, srednjeg imena i prezimena „Peter Jeremiah Willamson“, ali samo jedan u dobi od 21 godine. Postoje 4 „Petera Williamsona“ u dobi od 21 godine, ali samo jedan kome je srednje ime „Jeremiah“. Postoje 4 „Petera“ kojima je srednje ime „Jeremiah“ a dob 21 godina, ali se samo jedan od njih preziva „Williamson“ itd.

www.mcb.rs/blog

71

MCB Menadžment Centar Beograd

Partner u controllingu

Zahvaljujući matričnoj formuli, funkcije INDEX i MATCH nalaze onoga koji zadovoljava sva 4 uslova (ime, srednje ime, prezime i dob) i kao rezultat vraćaju krajnju levu kolonu (broj mobilnog) u ćeliji E6.

Slika 14: Rezultat dvostranog VLOOKUP-a po više kriterija

Drugim rečima: formula očitava u odabranom rasponu sadržaj prve kolone (ili bilo koje) iz onog reda u kome su sve 4 kolone jednake zadanim uslovima. Eto, znam da je ovaj poslednji primer prilično komplikovan, ali mislim da nije loše da vidite šta se sve može napraviti uz malo truda. Onako, lično, kad pravim formule, ne umirem u lepoti (mrzim Farselonu), ali volim rešenja koja štede vreme i koja omogućavaju da se izbjegne „pešadija“ (ručno računanje, odnosno „peške“), a to je ono što formule nizova upravo i rade.

www.mcb.rs/blog

72

MCB Menadžment Centar Beograd

Partner u controllingu

Trikovi u Excelu 20.deo: Zašto Excel ne može da pronađe neki broj (Excel Find and Replace)? Korišćenje Excel funkcije Find and Replace ne garantuje uvek da će program “prepoznati” šta hoćete da pronađe.

PROBLEM KORISNIKA: Excel dijalog Find and Replace me izluđuje. Uvek moram da idem na dugme Options kako bih naznačio da treba da traži u vrednostima (Values). Na Slici 1, kursor pokazuje da vrednost za koju Excel kaže da je nema zapravo postoji. Zašto Excel ne može da pronađe neki broj? Hej, Excele, probaj da pogledaš ispod kursora. REŠENJE: Istakli ste više problema s funkcijom Find and Replace. Hajde da ih brzo pregledamo i da neke od njih razjasnimo. Prvo, kada izaberete redom Home, Find and Select pa Find, Excel će prikazati pojednostavljenu verziju dijaloga Find and Replace bez važnih postavki prikazanih na sredini Slike 1. Find and Replace.

Slika 1. Find and Replace

www.mcb.rs/blog

73

MCB Menadžment Centar Beograd

Partner u controllingu

U detaljnom dijalogu postoje važne postavke koje se kriju iza dugmeta Options (ne vide se na Slici 1. Find and Replace). Te postavke će često dovesti do toga da pronalaženje zadatog ne uspe. Recimo da u koloni D imate obračun poreza na promet a da ćelija D3 prikazuje 70,81 kao rezultat formule. Podrazumevana postavka je da Excel pretražuje formule a ne vrednosti. Ako pokušate da izvršite pretragu bez promene parametra Formulas u Values, on neće pronaći vrednost $70,81. Pretraživanje teksta formula je pomalo iritantno. Koliko često kažete sebi: “Uh, pitam se u kojoj sam ćeliji upotrebio funkciju SQRTPI?” Ali još više iritiraju druge postavke, kao što su Match Case i Match Entire Cell Contents. Te postavke mogu da budu korisne, ali ako se desilo da ste ih promenili danas u 8.04 ujutro i od tada niste zatvorili Excel, čak i ako ste otvorili i zatvorili 40 drugih fajlova i radite na nečemu sasvim drugačijem Excel će pamtiti tu prethodnu postavku. Često će Vas iznervirati čudna postavka koja je ostala iz ranijeg dela dana, ili čak i postavka izmenjena kada je neki makro pokušao da koristi komandu Find uz uključenu opciju Match Entire Cell Contents. Dakle, zašto Excel ne može da vidi vrednost 1364,80 na Slici 1? Excel prikazuje ćeliju C16 sa oznakom valute i zapetomom, i da biste pronašli tu ćeliju, morate da tražite $1.364,80! Budući da je glavna funkcija Excela rad s brojevima, prilično je razočaravajuće da Excel ovako funkcioniše.Ali, kada to shvatite, možete da zaobiđete taj problem. Dodatna napomena: Ljudi često pitaju kako mogu da pretražuju sve radne tabele (worksheet) u jednom fajlu. To radite tako što u padajućem meniju Within umesto Sheet izaberete Workbook. Još jedna napomena: Iznenađujuće je da Excel može da pronađe ćelije koje umesto brojeva prikazuju znakove za broj – #.Recimo da imate kolonu u kojoj je 5% brojeva prikazano kao #####. E sad, svaka normalna osoba bi proširila kolonu ili uključila opciju Shrink to Fit, ali Vam Excel dozvoljava da napravite sledeći prilično šašav niz koraka: 

Izaberite opseg brojeva. Pritisnite Ctrl+F da se prikaže dijalog Find. U polju Find What unesite ###.



Ako dijalog ne prikazuje opcije, izaberite dugme Options.



Osigurajte da Look In bude podešeno na Values i da ne bude uključena opcija Match Entire Cell Contents.



Umesto da izaberete Find Next, izaberite Find All. Excel će dijalogu dodati novi deo s listom svih ćelija koje sadrže ###.



Dok je dijalog još uvek aktivan, pritisnite Ctrl+A. Time ćete izabrati sve ćelije na dnu dijaloga Find All.

Sada možete da formatirate samo izabrane ćelije. Na primer, mogli biste da izaberete manje decimala ili manju veličinu fonta ili biste mogli da izaberete da prikažete brojeve u hiljadama. PAŽNJA: U koraku br. 6 treba da pritisnete Ctrl+A da biste izabrali sve pronađene ćelije.Pazite da polje za dijalog bude aktivno pre no što pritisnete Ctrl+A. Na primer, ako promenite veličinu fonta, postaće aktivna radna tabela (worksheet) iako je i dalje prikazan dijalog.Pritiskanjem Ctrl+A u tom trenutku www.mcb.rs/blog

74

MCB Menadžment Centar Beograd

Partner u controllingu

bile bi izabrane sve ćelije u radnoj tabeli umesto samo odgovarajućih ćelija navedenih u dijalogu. Da biste ponovo aktivirali dijalog, treba da odaberete mišem naslovnu traku dijaloga Find and Replace.

www.mcb.rs/blog

75