logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali - Kako dodati broj nula ispored jednocifrenog broja, Dodavanje nula znamenke ispred broja sa jednom znamenkom.

Kako dodati nulu ispred broja




sponzor



Izdvajanje brojeva iz teksta i dodavanje nule ispred jednoznamenkastog broja

Excel program koristi puno Excel funkcija. Svaka funkcija ima sintaksu i sadrži argumente razdjeljene delimiterom ili separatorom. Da bi znali kako ugnijezditi formulu unutar formule potrebno je poznavati argumente u sintaksi neke Excel funkcije.

U ovom Excel tutorijalu, pokušat ću vam prikazati kako dodati znamenku nula ispred jednoznamenkastog broja. Brojevi se nalaze u tekstu jedne ćelije. Između brojeva nalazi se kosa crta naprijed. Uočite na slici ispod situaciju našeg problema.

Na slici ispod imamo izvorne podatke u 'A' stupcu. Ćelije su formatirane kao tekst. Kao što možete vidjeti, postoje tri grupe brojeva koje su razdjeljene kosom crtom. Svaka grupa može sadržavati jednu ili dvije znamenke brojeva. Naš cilj je konvertirati sve jednoznamenkaste brojeve u dvoznamenkasti broj ali tako da se ispred nalazi broj nula. Konačni rezultat nalazi se u 'T' stupcu. Na slici uočite međustupce. Namjerno sam postavio sliku sa ovim međustupcima jer ću pokušati objasniti korak po korak, kako ja kreiram konačnu formulu u koju ugnijezdim više formula. Na kraju ovog tutorijala vidjet ćete dvije dugačke formule. One su kreirane na sličan način ali sam koristio drugačije Excel funkcije, ipak rezultat je isti.

  • Korak 1 izdvajanje grupe brojeva ispred prve kose crte (crveni font)
  • Korak 2 izdvajanje brojeva između dvije kose crte (plavi font)
  • Korak 3 izdvajanje brojeva iza zadnje (iza druge) kose crte (zeleni font)

Dodavanje nule ispred jednoznamenkastog broja

Korak 1

Kako izdvojiti sve brojeve ispred prve kose crte

U prvom koraku postavio sam grupu rezultata u 'C', 'D' i 'E' stupcima. U ovim stupcima išao sam riješavati prvu grupu znamenki koja se nalazi ispred prve kose crte (crveni font na slici u 'A' stupcu). Da bi ekstrahirao ili izdvojio sve znamenke ispred kose crte koristio sam slijedeću formulu:

Formula u 'C1' ćeliji (rezultat je broj formatiran kao tekst): (Ovu formulu ću objasniti detaljnije a vama ostavljam proučavanje ostalih formula, naravno koristeći tipku F9 i Evaluate formula. Uočite da ova formula izdvaja sve znamenke ispred prve kose crte bez obzira ima li jedna ili dvije znamenke ili broja). Koristeći Excel 'FIND' funkciju tražio sam poziciju prve kose crte a rezultat ove funkcije je broj 2 tj. pozicija na kojoj se nalazi prva kosa crta. Koristeći IFERROR funkciju vratio sam rezultat broja znamenki uključujući i kosu crtu ali sam oduzeo '-1' zbog kose crte. I na kraju upotrijebivši 'LEFT' funkciju izdvojio rezultat iz 'A1' ćelije. Naravno formulu kopirate dole.
=LEFT(A1;IFERROR(FIND("/";A1);99)-1)

Formula u 'D1' ćeliji broji koliko znamenki ima u 'C1' ćeliji. (rezultat je broj formatiran kao vrijednost): (Ova 'LEN' formula broji koliko znamenki postoji u 'C1' ćeliji). Kada kopiramo formulu dole tada možemo uočiti za svaki red koliko znamenki ima za sve brojeve koji se nalaze ispred prve kose crte.
=LEN(C1)

Formula u 'E1' ćeliji (crveni font a rezultat je broj sa dvije znamenke formatiran kao tekst): (Koristeći 'IF' funkciju postavio sam slijedeći uvjet. Ako je broj znamenki u 'D1' ćeliji manji od '2' tada dodaj znamenku NULA ispred broja koji se nalazi u 'C1' ćeliji.
Ovaj izraz "0"&C1 možemo zamijeniti sa formulom CONCATENATE("0";C1). U slučaju da je logički uvjet ispunjen IF funkcija vratila bi broj znamenki veći jednak 2.
=IF(D1<2;"0"&C1;C1)

Ova IF formula je važna za ovaj tutorijal i moje pojašnjenje. Za početnike želim objasniti kako ugnijezditi formulu unutar druge formule. Još jednom pogledajte IF formulu iznad. Uočite da se u njoj nalaze dvije ćelije kao argumenti i to 'C1' i 'D1'. Ako želimo izbjeći pomoćne stupce 'C' i 'D', tada možemo ugnijezditi formule iz dotičnih stupaca u ovu IF formulu. To znači da umjesto svake adres ećelije 'C1' i 'D1' možemo staviti formulu koja se nalazi u dotičnim ćelijama.
=IF(D1<2;"0"&C1;C1)

To izgleda ovako kao formula koja se nalazi u 'Q1' ćeliji (crveni font a rezultat je broj sa dvije znamenke formatiran kao tekst). Dakle riješio sam formulu za prvu grupu znamenki ispred kose crte.
=IF(LEN(LEFT(A1;IFERROR(FIND("/";A1);99)-1))<2;"0"&LEFT(A1;IFERROR(FIND("/";A1);99)-1);LEFT(A1;IFERROR(FIND("/";A1);99)-1))

Korak 2

Kako izdvojiti sve brojeve između dvije kose crte

Slijedeća grupa formula nalazi se u stupcima 'G', 'H' i 'I'. Opet slične formule kao kod prve grupe ali razlika je za izdvajanje brojeva između dvije kose crte. Ostale dvije formule su iste.

Formula u 'G1' ćeliji: (Ova formula izdvaja sve brojeve između dvije kose crte. Uočite rezultate na slici za ostale redove u kojima se nalazi ova formula.)
=TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255))

Formula u 'H1' ćeliji broji koliko znamenki ima u ''H1 ćeliji. (rezultat je broj formatiran kao vrijednost):
=LEN(G1)

Formula u 'I1' ćeliji (plavi font a rezultat je broj sa dvije znamenke formatiran kao tekst):
=IF(H1<2;"0"&G1;G1)

Naravno, opet želimo izbjeći pomoćne stupce pa u 'R1' ćeliji kreirana je slijedeća formula (po istom principu ugniježđenih funkcija kao u prethodnom slučaju).
=IF(LEN(TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))<2;"0"&TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255));TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))

Kako izdvojiti sve brojeve iza zadnje kose crte

Korak 3

Slijedeća grupa formula nalazi se u stupcima 'K', 'L' i 'M'. Naravno, također sam koristio slične formule kao kod prve i druge grupe ali razlika je za izdvajanje brojeva iza zadnje kose crte. Ostale dvije formule su iste.

Formula u 'K1' ćeliji: (Ova formula izdvaja sve brojeve iza zadnje kose crte. Uočite rezultate na slici za ostale redove u kojima se nalazi ova formula.)
=RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))))

Formula u 'L1' ćeliji broji koliko znamenki ima u ''K1 ćeliji. (rezultat je broj formatiran kao vrijednost):
=LEN(K1)

Formula u 'M1' ćeliji (zeleni font a rezultat je broj sa dvije znamenke formatiran kao tekst):
=IF(L1<2;"0"&K1;K1)

Naravno, opet želimo izbjeći pomoćne stupce pa u 'S1' ćeliji (zeleni font) kreirana je slijedeća formula (po istom principu ugniježđenih funkcija kao u prethodnom slučaju).
=IF(LEN(RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))<2;"0"&RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))));RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))

Na ovaj način riješili smo pojedinačno izdvajanje svake grupe brojeva ispred, između i iza kose crte. Slijedeći zadatak je spojiti sve tri formule u jednu formulu da bi imali konačni željeni rezultat.


2. Kako izdvojiti brojeve između specifičnog znaka i dodati nulu ispred jednoznamenkastog broja

U 'O1' ćeliji uočite jednostavnu formulu. Dakle, ova formula zamjenjuje CONCATENATE formulu i izgleda kao ispod.
=Q1&"/"&R1&"/"&S1

Naravno da bi opet sve sveli na jednu formulu, idemo ugnijezditi u ovu formulu iznad, sve konačne formule koje smo kreirali u tri koraka na početku tutorijala. Dakle, Umjesto adresa ćelija mi možemo staviti formulu koja se nalazi u ditičnim ćelijama.

Formula u 'T1' ćeliji (pink font) je slijedeća: (Uočite da u formuli ispod da postoji samo adresa 'A1' ćelije. Ova formula sadrži 575 znakova).
=IF(LEN(LEFT(A1;IFERROR(FIND("/";A1);99)-1))<2;"0"&LEFT(A1;IFERROR(FIND("/";A1);99)-1);LEFT(A1;IFERROR(FIND("/";A1);99)-1))&"/"&IF(LEN(TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))<2;"0"&TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255));TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))&"/"&IF(LEN(RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))<2;"0"&RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))));RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))

Ova formula ispod vraća isti rezultat kao i formula iznad. Dakle, također u 'T1' ćeliji (pink font) može biti slijedeća formula: (Uočite da u formuli ispod da postoji samo adresa 'A1' ćelije. Ova formula sadrži 869 znakova).
=IF(LEN(IF(MID(A1;2;1)="/";LEFT(A1;1);LEFT(A1;2)))<2;"0"&IF(MID(A1;2;1)="/";LEFT(A1;1);LEFT(A1;2));IF(MID(A1;2;1)="/";
LEFT(A1;1);LEFT(A1;2)))&"/"&IF(LEN(IF(MID(MID(A1;SEARCH("/";A1;1)+1;4);2;1)="/";LEFT(MID(A1;SEARCH("/";A1;1)+1;4);1);
LEFT(MID(A1;SEARCH("/";A1;1)+1;4);2)))<2;"0"&IF(MID(MID(A1;SEARCH("/";A1;1)+1;4);2;1)="/";LEFT(MID(A1;
SEARCH("/";A1;1)+1;4);1);LEFT(MID(A1;SEARCH("/";A1;1)+1;4);2));IF(MID(MID(A1;SEARCH("/";A1;1)+1;4);2;1)="/";
LEFT(MID(A1;SEARCH("/";A1;1)+1;4);1);LEFT(MID(A1;SEARCH("/";A1;1)+1;4);2)))&"/"&IF(LEN(TRIM(LEFT(SUBSTITUTE
(MID(SUBSTITUTE($A1;"/";REPT(" ";LEN($A1)));LEN($A1)*2;LEN($A1));"/";REPT(" ";255));255)))<2;"0"&TRIM(LEFT(
SUBSTITUTE(MID(SUBSTITUTE($A1;"/";REPT(" ";LEN($A1)));LEN($A1)*2;LEN($A1));"/";REPT(" ";255));255));
TRIM(LEFT(SUBSTITUTE(MID (SUBSTITUTE($A1;"/";REPT(" ";LEN($A1)));LEN($A1)*2;LEN($A1));"/";REPT(" ";255));255)))

Nadam se da sam donekle uspio početnicima pojasniti kako kreirati vrlo dugačku formulu i kako ugnijezditi formulu unutar druge formule.

I na kraju u prilogu ovog tutorijala imate mogućnost Download datoteke na kojoj sam radio ovaj tutorijal.




     

sponzor



COPYRIGHT © - 2005 - 2020 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela