Trikovi u Excelu 2.deo
Ukoliko te zanima Excel, pogledaj i sledeće tekstove. Dovoljno je da klikneš na link:
Trikovi u Excelu 3. deo (shortcuts 1/3)
Trikovi u Excelu 4. deo (Custom Sort)
Trikovi u Excelu 6. deo (Custom View)
Trikovi u Excelu 7. dio: Sekundarna osa, ili kako prikazati podatke u dvije skale na chartu
Trikovi u Excelu 8. deo (shortcuts u Excelu 3/3)
Trikovi u Excelu 9. deo: Print Titles ili kako da se prvi red ponavlja na svakom listu u Excelu
Trikovi u Excelu 10. deo: Sparklines ili grafikon unutar jedne ćelije
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 vraćena 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 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 sljedeći način:
- selektujete ćelije za koje definišete moguće unose;
- definišete moguće vrijednosti (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 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.
Ukoliko te zanima Excel, pogledaj i sledeće tekstove. Dovoljno je da klikneš na link:
Trikovi u Excelu 3. deo (shortcuts 1/3)
Trikovi u Excelu 4. deo (Custom Sort)
Trikovi u Excelu 6. deo (Custom View)
Trikovi u Excelu 7. dio: Sekundarna osa, ili kako prikazati podatke u dvije skale na chartu
Trikovi u Excelu 8. deo (shortcuts u Excelu 3/3)
Trikovi u Excelu 9. deo: Print Titles ili kako da se prvi red ponavlja na svakom listu u Excelu
Trikovi u Excelu 10. deo: Sparklines ili grafikon unutar jedne ćelije
Autor
Almir A. Budalica
Microsoft Certified Trainer
http://www.excelbezbola.com
+38761509288 (Viber, WhatsApp)
60 Komentara
Moram da priznam da su mi Blogovi vezani za Excel omiljeni… Ne znam stvarno zasto 😀 ali je tako…
Data validation je definitivno odlican izbor pogotovo kada se odredjeni podaci salju dalje na popounjavanje, na nekoliko strana, a kasanije te podatke trbea imati ujednacene i spremne za analizu…
Po principu, bolje spreciti nego leciti, napravite liste i sve ce biti uniformisano…
I nad popom ima pop
Mislim da će ti se i Almirovi tekstovi izuzetno svideti. Sada smo Almir i ja odlučili da krenemo u “Excel rat” – kao Coca Cola i Pepsi – ko će biti bolji
On je Coca Cola Zero a ja sam Pepsi Twist…
Veoma zanimljivo. Mi verni citatelji, ocekujemo da ce ovakvih postova biti sve vise. Jedva cekam da pocne ovaj Pepsi Cola Excel rat. Nadam se da cete nas obavestiti o svim frontovima. Samo vi ratujte. Zivio Almir i Bojan. Nemojte da se upucate medjusobno.
Pozdrav.
[…] tekst (Trikovi u Excelu, 2. deo) je imao za temu – Data […]
[…] Trikovi u Excelu, 2. deo […]
Ovo je dobro.
Super stvar! Sve pohvale.
Iskreno prvi put vidim da ovakav blog postoji u Srbiji…
Samo jedna mala konstatacija..
Covek sa iskustvom rada u Excelu pri prikupljanju podataka nikada ne bi ponudio slobodan unos, vec bi tabelu zastitio i kreirao validaciju (iako i ona moze da se obrise ako neko kopira podatke preko celije).
S druge strane slozicete se da smo se svi mi susreli sa ovim problemom, jer smo slali ne zasticenu tabelu, a nakon toga se dovijali kako da slozimo podatke
Slažem se… Data Validation je zgodan-ako znaš da ga koristiš
A, ako ne znaš… e, pa onda sledi malo više posla
[…] tekst (Trikovi u Excelu, 2. deo) je imao za temu – Data […]
[…] tekst (Trikovi u Excelu, 2. dio) je imao za temu – Data […]
[…] tekst (Trikovi u Excelu, 2. deo) je imao za temu – Data […]
[…] tekst (Trikovi u Excelu, 2. deo) je imao za temu – Data […]
Bojane,
Interesuje me da li postoji mogucnost da se zakljucane kolone u Excelu filtriraju? da li moze raditi filtert ako se zakljuca kompletna tabela ili par kolona?
Koliko ja znam – ne.
Pokušaj da podatke iskopiraš u drugi sheet – trebalo bi da se skine zaštita.
Probaj da zaštitiš sheet, tako da omogućiš filtriranje (Protect Sheet, Enable users to use AutoFilter).
Imam jedan problem i molbu… Potrebna mi je pomoć… Imam tabelu u excelu koja se sastoji od 45 kolona i 120 redova. U ćelije su uneti bar kodovi (numeric) od po 10 cifara. Potrebno mi je da imam sledeće podatke: broj popunjenih ćelija po koloni (rešio sam), pronaći uneti bar kod poredjenjem unosa u posebnu ćeliju (rešio sam preko COUNTIF). Potrebno mi je da kod unosa bar koda u posebnu ćeliju, ukoliko je broj već unet, podatak dobijam preko COUNTIF, da se ćelija ukojoj je upisan broj locira i da je mogu osenčiti bojom. Nadam se da sam dobro objasnio šta mi treba, hvala na razumevanju.
Pozdrav,
Probaj sa kondicionalnim formatiranjem kolone u kojoj se nalaze bar kodovi na sljedeći način: ukoliko je vrijednost u koloni jednaka posebnoj ćeliji koju koristiš za provjeru, neka se ta ćelija osjenči. Tako ćeš svaki put dobiti osjenčen barkod kada uneseš isti kod u kontrolnu ćeliju.
Mani ovo nije uspelo.
Zasto mi u padajucem meniju izlazi:
-Musko;Zensko umesto -Musko
-Zensko
?
Milana,
probaj sa “,” umesto “;”
Ovaaj
Sad sam uspela, na mom racunaru je potrebno odvojiti zarezom, a ne sa ;
Hvala puno na korisnim tutorijalima
Možeš i napraviti posebnu listu opcija i dati joj ime (Named Ranges). Zatim u Data Validation prozoru odaberi “List”, =ime_raspona. Onda nije bitno je li zarez ili tačka-zarez.
Veoma korisni sitni i neki bas ne tako sitni trikovi,…
Korisno! Voleo bih jos ovako korisnih postova o Excelu! Hvala!
Đorđe,
pa imaš jedno 10-ak tekstova o Excelu
Poštovani,
Imam pitanje: Prilikom korišćenja funkcije “sumifs” dobijem rezultat “0”.
U čemu je problem? Formula je dobro postavljena, jer da nije, prijavio bi grešku, ali je očito da nešto nije dobro.
Molim vas za pomoć.
Hvala!
BelaRado,
da bih ti pomogao, potreban mi je fajl. Pošalji mi na mcb@eunet.rs.
BelaRado,
Govorim napamet, ali možda je potrebno da uslov koji definišeš staviš među navodnike.
Pozdrav!
Hvala na prethodnim odgovorima.
Zanima me još nešto – kada idemo na format cells, pa na custom, kakvo je pravilo prilikom upotrebe znaka taraba (#), tj. nije mi jasno po kom principu se postavlja struktura u kojoj se upotrebljava taj znak. Da li tu postoji neko pravilo ili se te strukture uče napamet?
Hvala!
Bela Rado,
Ovaj znak se koristi za kreiranje tzv. “custom” formata. Najčešće se koristi za jednostavniji prikaz velikih brojeva (npr. milionskog iznosa u hiljadama i sl.).
Primjer:
ZA PRIKAZKAOKORISTI OVAJ KOD
1234.59
1234.6
####.#
8.9
8.900
#.000
.631
0.6
0.#
121234.568
12.01234.57
#.0#
44.398102.652.8
44.398102.65 2.8(with aligned decimals)
???.???
5.255.3
5 1/45 3/10(with aligned fractions)
# ???/???
Ups, tekst se nije baš kopirao kako treba, ali suština je u sljedećem:
– ako želiš 0,631 prikazati kao 0,6, formatiraj kao: 0,#
– za prikaz 1234,56 kao 1234,6 formatiraj kao: ####,#.
Eventualno, moguće je da je kod tebe umjesto zareza potrebno staviti tačku, što zavisi od postavki kompjutera.
Aha. Iste te strukture mogu da se primene za prikazivanje strukture datuma, npr.”sreda, 10/ jul 2013″ gde koristi strukturu [$-81A]dddd, d/ mmmm yyyy;@ Ali me upravo to zanima, sta znaci to sa dolarom i -81A ?
Nasla na netu.
Evo, za sve koje zanima ova tema, postoji spisak kodova za datume za sve zemlje:
http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx
To sa dolarom i brojem je oznaka, tj. kod jezika prikaza.
Imam tabel u njoj kolona koja sadrži tri ćelije za svaku godinu života.Ćelija 1 m pol,ćelija2žpol i ćelija 3 zbir ćelija 1i 2.Kada sam to pokušao uraditi na klasičan način formulom =sum().Izašla mi je poruka previše argumenata za formulu.Raspon godina je od 1-100. što znači 100 argumenata.Molim Vas ako možete da mi pomognete jer mi podaci trebaju za izradu plana.
Ako ti javlja previše argumenata, vjerovatno si klikao ćeliju po ćeliju u formuli za zbir. Umjesto toga, otkucaj: =SUM( ovdje selektuj sve ćelije, zatvori zagradu i pritisni tipku ENTER.
Postovani, treba mi pomoć
U računaru imam jedan folder sa npr. 20 fajlova. Svaki fajl ima jedan sheet i na njemu otpremnica. U E6 na toj otpremnici mi se nalazi traženi podatak. Svaki dan broj fajlova se povećava.
U drugom fajlu ( van tog foldera ) na računaru imam tabelu u koju stavljam taj podatak.
Želim da u toj tabeli prenesem sve podatke iz E6 jedan ispod drugog. Pokušao sam da u toj tabeli napišem = i da uhvatim na krstić i povučem i da mi ostaje E5 a da mi se menjaju fajlovi. E kad mih saznao kako bio bih zahvalan.
Pozdrav,
Ratko, za ove potrebe preporučujem prelaz na manju bazu podataka poput Accessa ili sl.
Odlično, odlično!
Sretna sam što sam vas pronašla.
Postovani,
potrebna mi je pomoc oko izracunavanja max vrednosti u koloni B a koja se trazi kod zadate vrednosti koja se ponavlja u koloni A. Funkciju maxif nisam nasao. Pored toga kako da formatiram celije tako da mi razdvaja sate i minute sa :. Recimo ja upisem 1230, a po izlasku i celije se postavi 12:30. Hvala
Prvo pitanje: nisam shvatio
Drugo pitanje: Format cells –> Custom format –> hh:mm. Sa druge strane, vreme da upisuješ u obliku 12:30. Dakle, direktno upiši u ćeliju 12:30
Primer: Kolona A ima oznake artikala koja se na nekim celijama ponavljaju jogurt, hleb, cokolada, Jogurt, Hleb… Kolona B ima registrovane cene tih artikala. Zelim da izdvojim max cenu za Jogurt.
={MAX(IF($A$2:$A$10=”jogurt”;$B$2:$B$10))} ili kraće: ={MAX(($A$2:$A$10=”jogurt”)*($B$2:$B$10))}. Vitičaste zagrade ne unosiš već pritisni kombinaciju tipki CTRL+SHIFT+ENTER (s običnim ENTER-om formula neće raditi). Ne postoji MAXIF funkcija pa je potrebna tzv. matrična formula. Matrične formule se unose pomoću CTRL+SHIFT+ENTER. Pretpostavka je da su nazivi artikala u ćelijama od A2-A10 a cijene u ćelijama od B2-B10. U prvom redu su nazivi kolona. Promjeni raspone ćelija po potrebi. Za više detalja o matričnim formulama možeš naći ovdje: http://www.mcb.rs/blog/2014/06/12/trikovi-u-excelu-17-deo-matricne-formule-formule-nizova-array-formule-nemoguce-kalkulacije-su-ipak-moguce
Hvala na pomoci. Presudna je bila aktivnost načina unošenja matričnih formula. Ostalo je samo prvo pitanje. Mislim da nisam dobro objasnio. Naime u accesu je bila opcija zadate forme unosa npr. telefonskog broja, datuma, poštanskog broja i sl. U excelu mislim da je to malo komplikovanije. Želeo bi se rešiti čestih kucanja separatora vremena “:” odnosno želim ubrzati kucanje vremena tako što ću ukucati četiri broja a separator će automatski razdvojiti sate i minute. Unapred hvala
U Format Cell=>Custom=> unesi: #”:”00. Samo pazi: ako ovo će onemogućiti tačno računanje na osnovu podataka o unešenom vremenu zbog načina kako Excel pohranjuje ovako unešene podatke. Ako ti je potreban samo prikaz i lakši unos, koristi ovaj format. Ukoliko, ipak, treba da računaš tako unešena vremena, onda moraš unositi dvotačku.
Ok, hvala
Poštovani,potrebna mi je pomoć oko nekih zadatka u excelu.
1. Zadatak glasi ovako: Dohodak radnika se računa kao proizvod cene rada i koeficijenta,koji se uvećava za 2% za svaku godinu minulog rada,a umanjeno za postojeće obustave. U tabeli imam sledeće podatke:
Lukic Ana, koeficijent je 2,3 ;minuli rad godine je 12, obustave su 12 000. Treba da izračunam dohodak? U nekom rešenju stoji da je dohodak 38 765,60 din. Ali ne znam kako da dobijem to?
@Milan nedostaje cena rada
Nema cene rada u zadatku,nisu je naveli,zato mi zadatak i predstvalja problem.
Sta ako hocemo da unesemo rucno, a ko unosi rucno unese malo pocetno slovo, a kod nas je definisao u listi da budemo pocetno veliko slovo? Jel moze to da se resi, kroz data validation?
Hvala
Poštovani,potrebna mi je pomoć. Imam tabelu i u kolonama mi se nalaze zemlje a u redove unosim cifre. E sad u kolnama sam stavila data validation listu zemlaja njih cca.250 i treba mi da kada počenm kucati naziv zemlje tipa FRA da mise u tom polju austomatski pojavi FRANCE. Ima puno zemlaja pa mi je mrsko stalno tražiti u listi. Neke znam napamet ali ima onih Republika itd pa bi mi ovo puno olakšalo posao.
Unaprijed hvala
Probaj s opcijom AutoCorrect. Nalazi se u opcijama: Proofing=>AutoCorrect Options. Dodaj npr. FRA a u polju desno “Francuska” pa klikni na dugme “Add”. kada ubuduće otkucaš FRA, Excel (a i ostali Office aplikacije) če to automatksi pretvoriti u “Francuska”.
Može li se u padajućem meniju uz pomoć data validation ubaciti više od 8 if funkcija koje će opredeliti izbor odgovarajuće liste. Konkretno treba mi 12 lista.
Boško, pretpostavljam da ti treba tzv. “pametna” padajuća lista, tj. da sadržaj druge liste zavisi od izbora u prvoj listi. Od 12 listi napravi imenovane raspone tako da se svaki imenovani raspon zove po jednoj opciji u prvoj listi. U drugu listu ubaci funkciju =INDIRECT(adresa_celije_sa_prvom_listom). Na primjer: napravi 12 imenovanih raspona na osnovu 12 listi (PrvaLista, DrugaLista, TrecaLista itd.). Zatim napravi prvi padajući meni pomoću Data Validation tako da sadrži spisak ovih listi (PrvaLista, DrugaLista, TrecaLista itd.). Onda napravi drugu (zavisnu) padajuću listu u kojoj ćeš u Data Validation staviti formulu =INDIRECT(adresa_celije_sa_prvom_listom). Na ovaj način, kada promjeniš izbor liste u prvom padajućem meniju, izbor opcija u drugoj (zavisnoj listi) će se promjeniti tako da prikazuje sadržaj odgovarajuće liste.
Zahvaljujem, funkcioniše odlično.
Iz “pametne” liste biram 60-tak šifara, po 5 za svaku od 12 imenovanih lista, od ukupno oko 300 (šifra) mogućnosti. Svaka šifra se odnosi na jedno ime. Ideja mi je da filtriranjem odabranih šifara dođem do liste imena i da ih uz pomoć makroa rasporedim u odgovarajuće ćelije radi dalje obrade.
Ima li jednostavnijeg načina uspostavljenja te relacije.
Sada to radim tako što sam uradio sa svaku imenovanu listu formulu sa 10 do 30 IF funkcija tipa =IF(d4=800;”Boško Diviki;IF(d4=795;”Almir Budalica;….)), ali je problem što liste menjaju pozicije na tabeli, sada rešeno uz Vašu pomoć, ručno kopiram i prenosim formulu u odgovarajuće ćelije ali je to nepovoljno pošto tabelu treba da popunjavaju drugi na terenu sa kojima često imam kontakt samo e-mail-om
Lep pozdrav.
Boško, na tabu “Review” uključi opciju “Share Workbook” i pošalji fajl ljudima na terenu. Kada vrate popunjene fajlove, koristi opciju “Compare and Merge Workbooks” za objedinjavanje podataka u svoj originalni fajl (opcija se ne vidi pa je treba ručno dodati na komandnu traku). Ukoliko može doći do preklapanja podataka na istim ćelijama, podjeli listove po korisnicima i neka svako radi na svom listu.
Pozdrav! Napravila sam dokument koji ima 3 “zavisne padajuce liste” koristeci data validation i indirect i on za sada funkcionise kako treba. “Baza” iz koje se “vuku” opcije se nalazi u drugom work sheetu. Sad mi treba isto to ali da “baza” bude u zasebnom DOKUMENTU, moguce cak i u okviru drugog foldera jer ce se ta baza s vremena na vreme dopunjavati. Da li je to moguce? Dokument ce popunjavati nekoliko ljudi svako svoju verziju
@Alex, uradi sljedeće: U izvornom fajlu gdje je baza (npr.”SourceList.xlsx”) odaberi bazu i kreiraj imenovani raspon, npr. “SourceList”. Zatim u fajl gdje želiš napraviti padajuću listu napravi imenovani raspon, npr. “Novi” a za njegov izvor unesi “=SourceData.xlsx!SourceList “. Na ovaj način izvor za padajuću listu je definisan kao imenovani raspon SourceList unutar fajla SourceData.xlsx.
@Alex: O imenovanim rasponima imaš više na: http://www.mcb.rs/blog/trikovi-u-excelu-12-deo-imenovani-rasponi-ili-kako-komplikovane-formule-naciniti-kracim-i-jasnijim/
Drugari, potrebna mi je pomoć. Ne znam da li je pogodjena tema, ali evo pitanja. Odnosi se na prebrojavanje jedinstvenih vrednosti u kolonama gde ima duplikata.
Na ovoj adresi
https://support.office.com/sr-latn-rs/article/brojanje-jedinstvenih-vrednosti-me%C4%91u-duplikatima-8d9a69b3-b867-490e-82e0-a929fbc1e273
je to objašnjeno ali mi ne funkcioniše ako ubacim IF(LEN…
Rešio sam, donekle taj problem sa izbacivanjem ovog dela funkcije.
Sad ide glavno pitanje:
Ako imam tri kolone sa duplikatima u svakoj koloni, kako da nadjem broj jedinstvenih redova.
Primer:
RB Planina Država Vrh NV
1 Venčac Sr tt 658
2 Vidojevica Sr Bandera 1.155
3 Vidojevica Sr Bandera 1.155
Rezultat treba da bude 2. (red: Vidojevica, Sr, Bandera se ponavlja 2 puta)
Napominjem da imam Excel 2007 i da mi treba formula, ne umetnuta tabela ili filter.
Hvala
@Gbole: Ima više načina. Najbrži način je uklanjanje duplikata i prebrojavanje preostalih redova. Objašnjenje imaš na: http://www.excelbezbola.com/blog/kako-ukloniti-duplikate-iz-liste. Drugi način je pomoću formule: http://www.excelbezbola.com/blog/kako-izracunati-broj-jedinstvenih-vrijednosti-u-rasponu-celija. Ipak, pošt o u tvom slučaju duplikat čini kombinacija više kolona, predlažem ti da napraviš pomoćnu kolonu koja bi bila kombinacija tri kolone a onda koristi formulu hza brojanje jedinstvenih vrijednoti po toj pomoćnoj koloni.