Excel Trikovi

Dakle, idemo – Trikovi u Egzelu by Boki.

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 deliju B2 i napiši formulu :

=TRIM(PROPER(A2))

U deliji B2 dobideš ispravno napisano Novak Đokovid.

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 (treda odozgo). I – to je to.

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š sledede 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…

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.

Dobideš rezultat kao na slededoj slici.

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 delije (selektuj). Stani u donji, desni ugao delije A2. Pojavide se mali kvadratid, a kursor de se pretvoriti u mali krstid. Povuci nadole. To je to.

Probaj ovo da uradiš i sa slededim 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 DELIJU. NPR, NALAZIM SE NA DELIJI A2 I ŽELIM DA ISKOPIRAM DELIJU A1. JA OBIČNO IDEM GORE, PA COPY, PA SE SPUSTIM DOLE PA PASTE…

E, jarane moj, jarane moj…  

Napiši u deliji A1 BOKI – TI SI CAR, EXCELOVOG SRCA GOSPODAR! Siđi do delije A2. Pritisni CTRL + D. CTRL + D kopira deliju iznad.

7. SUPER TI JE OVA SKRADENICA. 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 tredi fajl. Napiši u njemu “Tredi fajl”.   

Pritisni CTRL + TAB. Pritisni još jednom CTRL + TAB. Pritiskaj do besvesti CTRL + TAB. Kapito? CTRL + TAB prebacuje iz fajla u fajl.

8. HODEMO JOŠ, HODEMO JOŠ, HODEMO 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 ved bio zaludan i kada si otvorio toliko sheetova, onda bi bilo zgodno i da naučiš kako se kredeš između sheetova.      

Pritisni CTRL + PageDown Pritisni još jednom CTRL + PageDown Pritisni CTRL + PageUP Pritisni CTRL + PageDown Pritisni CTRL + PageUP ma, pritisni šta god hodeš… valjda si ved ukapirao

CTRL + PageDown/PageUp služi za šetanje između sheetova.

Pored ovog teksta, možeš pogledati i nastavak:  

Trikovi u Excelu, 2. deo Trikovi u Excelu, 3. deo

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 kontrolor 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?

Posle prvog teksta o trikovima u Excelu (Bojan, Trikovi u Excelu) evo i nastavka. 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 vradena joj je tabela sa kolonom u kojoj je trebalo da budu dvije različite vrijednosti (“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 riječ napisana malim slovima različita od iste riječi napisane velikim slovima. Kolegica je rekla da joj ne predstavlja problem ispraviti podatke tako da budu ujednačeni jer poznaje odgovarajude funkcije (TRIM, PROPER, UPPER, LOWER). Pitala je kako sprečiti budude pogrešne unose, odnosno kako omoguditi samo unose sa padajude liste.

Odgovor na to je “Data Validation”. Najveda korist koju ova opcija pruža je ograničavanje mogudih unosa u delije. 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 sljededi način:    

selektujete delije za koje definišete mogude unose; definišete mogude vrijednosti (u obliku liste, raspona brojeva, datuma, dužine teksta itd.); pri unosu se dobija padajuda lista sa definisanim opcijama; Excel ne prihvata unose koje niste definisali i javlja grešku. Osim toga, možete:    

definisati poruku za pomod pri unosu; definisati poruke pri pokušaju pogrešnog unosa; koristiti postojedi spisak kao listu raspoloživih opcija; istadi delije čiji sadržaj ne odgovara zadanim opcijama (ovo se dešava kada su podaci uneseni prije 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.

Korak 1.

Korak 2.

Korak 3.

Korak 4.

Korak 5.

Korak 6.

Korak 7.

Korak 8.

Korak 9.

Korak 10.

Korak 11.

Korak 12.

Korak 13.

Korak 14.

Korak 15.

E, sada dolaze na red i shortcuts (skradenice). Često me polaznici pitaju:  A zašto ljudi koriste skradenice 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 radio 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 redi ?

Dakle, evo i spiska. Napravili smo spisak 45 shortcuts. Ovo je prvi spisak od 15 korisnih skradenica. 1. Formatiranje objekta, CTRL + 1 2. Insertovanje tekudeg datuma, CTRL + ;

3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.

Insertovanje tekudeg vremena , CTRL + SHIFT + ; Ponavljanje poslednje akcije, F4 Editovanje comment, SHIFT + F2 Autosum selektovanih delija, ALT + = Drop down lista, ALT + strelica nadole Unošenje nekoliko linija u deliju, ALT + ENTER Insertovanje novog sheeta, SHIFT + F11 Editovanje delije, F2 Hajdovanje reda, CTRL + 9 Hajdovanje kolone, CTRL + 0 Unhajdovanje reda, CTRL + SHIFT + 9 Unhajdovanje kolone, CTRL + SHIFT + 0 Rekalkulisanje formula, F9

Sigurno ste bili u situaciji da tabelu u Excelu prikažete tako da je sortirana (poredana) po redoslijedu sadržaja u delijama. 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 najvedeg (0-9), ili od najvedeg do najmanjeg (9-0). Za komplikovanija sortiranja sortirali biste tabelu po dvije, tri ili više kolona.

A šta ste radili da sortirate tabelu po mjesecima, gdje su imena mjeseci 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, srijeda, subota, utorak. Ovo se dešava jer Excel sortira tekst po abecedi. 

Kako objasniti Excelu da redoslijed imena mjeseci i imena dana u sedmici ne sliijedi abecedu?

Neki pomodu formula izvlače redne brojeve mjeseci u posebnu kolonu pa sortiraju po njoj, drugi ručno preslaguju tabelu, tredi… Eh, tredi… 

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

Rješenje je u opciji “Custom Sort”. Ova opcija omogudava da sami napravite “svoj” redoslijed sortiranja. I taj redoslijed ne mora biti ni abecedni ni po redoslijedu brojeva. Pri sortiranju umjesto uobičajenog abecednog ili sortiranja po brojevima odaberete svoje sortiranje. Idealno rješenje za nestandardno sortiranje, poput sortiranja po imenu mjeseci ili po imenu dana u sedmici. Ili po bilo čemu drugom. Tabela u primjeru 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, srijeda, subota, utorak).

Da bi Excel znao koji je redoslijed dana u sedmici, potrebno je da definišete posebnu listu za sortiranje. To dete uraditi na sljededi način:

Korak 1: Kliknite na samo jednu deliju unutar tabele i na tabu “Data” odaberite “Sort”.

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

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

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

Korak 5: U desnom prozoru unesite dane u sedmici po željenom redoslijedu (ponedeljak, utorak, srijeda, č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 postojedih.

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

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

Korak 9: Kolona je sortirana po danima u sedmici.

Idudi put kada imate potrebu za ovakvim sortiranjem po danu u sedmici, samo odaberite listu u lijevom prozoru i kliknite na dugme “OK”. Liste za sortiranje možete dodavati, mjenjati i brisati. 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 deliju) 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 delije, CTRL + D 9. Slededi worksheet, CTRL + TAB 10. Prethodni worksheet, CTRL + SHIFT + TAB 11. Slededi 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

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 krade (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 lijevom (ili desnom, ne sjedam 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 gdje upoznaju samo bubuljičave štrebere i ružne ribe. Pa šta ako je nadugo i naširoko? Ako hode 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: „Hodu van, vodi me u šetnju.“ Rekao sam da du 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 svijetu 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 mogudnostima 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 vedina ljudi ne vidi ili ne zna čemu služi. Prva u nizu (vjerujte mi na riječ, ima ih mnogo) je Pohranjeni ili prilagođeni prikaz. Ukratko, promjenite 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. Dakle, kako napraviti Custom View? Prvo sačuvajte originalan (uobičajeni) prikaz na sljededi način: na traci View kliknite na Custom Views.

Korak 1: Custom View na traci View Kliknite na dugme Add za dodavanje novog prikaza.

Korak 2: Prozor za dodavanje prikaza Dajte prikazu prikladno ime i Kliknite na OK dugme.

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

Korak 4: Mjenjanje 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).

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 sljededi način: na traci View klik na Custom Views, odaberemo prikaz i kliknemo na dugme Delete.

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). Za početak dosta jednostavna, ali ništa manje modna 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štedio bih tada mnogo muke i vremena. Vjerovatno ste bar jednom bili u situaciji da na istom grafikonu treba da prikažete dvije serije podataka, sa velikom razlikom u vrijednostima.

Slika 1: Tabela sa dvije serije podataka, gdje su brojevi u jednoj seriji mnogo vedi Zamislimo da treba da prikažemo odnos dobi zaposlenika i njihovih plata. Jedna serija de prikazivati podatke o dobi a druga o visini plate.

Slika 2: Grafikon sa dvije serije podataka gdje se ne vidi korelacija između podataka uslijed 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šde se jedna serija obriše, ili se naprave dva grafikona, ili se izračuna neki koeficijent pa se on stavi na grafikon, ili…da ne nabrajam. Dosta često ljudi sasvim odustanu od prikaza takvih podataka. Sekundarna (dvojna) osa omogudava smislen prikaz dvije serije podataka na grafikonu, čak i kada je razlika između vrijednosti 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 dvije serije podataka pomodu sekundarne ose Da biste napravili prikaz sa sekundarnom osom, prvo napravite obični grafikon sa prikazom svih potrebnih podataka.

Korak 1: Kreiranje grafikona sa dvije serije podataka Selektujte jednu seriju podataka i kliknite na desnu tipku miša. Odaberite “Format Data Series“.

Korak 2: Selektovanje serije podataka U “Series Options” odaberite “Secondary Axis” i kliknite na dugme “Close” za zatvaranje prozorčida.

Korak 3: Odabir sekundarne ose Sa desne strane dete vidjeti sekundarnu osu u sopstvenoj skali koja je različita od primarne ose na lijevoj strani.

Korak 4: Prikaz druge serije podataka na sekundarnoj osi u različitoj skali Na taj način dete uočiti korelaciju između dvije serije podataka. A sad, mala molba čitaocima: na sljededem linku je interaktivni multimedijalni prikaz korištenja sekundarne ose. Skinite fajl na svoj računar (kao EXE), uključite zvuk i slijedite upute na ekranu. Svi komentari su dobrodošli. Hvala!

16 novih shortcuts (prečica sa tastature).

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16.

Zumiranje, CTRL + Točkid na mišu Selektovanje kolone, CTRL + SPACE Selektovanje reda, SHIFT + SPACE Ubacivanje reda, CTRL + Numerička tastatura PLUS Brisanje reda, CTRL + Numerička tastatura MINUS Spisak names , F3 (kada editujete deliju) Only Visible Cells; F5 (Go To) zatim Special i odaberi Visible Cells Only Kopiranje; CTRL + C Paste; CTRL + V Otvaranje fajla; CTRL + O Štampanje; CTRL + P Novi fajl; CTRL + N Find; CTRL + F Replace; CTRL + H Undo; CTRL + Z Insertovanje hiperlinka, CTRL + K