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 - Razdvajanje jedne ćelije u više ćelija u stupac, Prebacivanje teksta iz jedne ćelije u kolonu, Transpose One Cell to Multiple Cells into One Columns,

Razdvajanje i dijeljenje jedne ćelije u više ćelija - transponiranje ili zaokretanje


Na ovom dijelu web stranice već sam napisao nekoliko tema vezanih za transponiranje-zaokretanje-konveriranje u Excelu. Pri kraju ovog tutorijala možete pronaći linkove na ostale tutorijale za nekoliko primjera Transpose funkcije.

Split or Extract and Transpose Text from One a Cell to Rows-Column in Excel

U ovom tutorijalu ja ću prikazati početnicima u Excelu, kako možemo izvršiti izdvajanje svih riječi (strihg) iz jedne ćelije i konvertirati u više ćelija jednog stupca ali tako da svaka riječ pripada uvjetovanom podatku iz 'B' stupca. U ovom tutorijalu prikazat ću nekoliko opcija rješavanja zadatka.

Situacija je kao na slici ispod. Naš cilj je razdvojiti sve riječi iz ćelija u 'B' stupca aili tako da svaka riječ pripara podatku iz 'A' stupca. Sve to treba konvertirati z dva stupca tako da izdvojene riječi (string) budu jedna ispiod druge

tekst odvojen delimiterom ili separatorom

Razdvajanje riječi iz jedne ćelije u višestruke ćelije:

U prvoj varijanti možemo pomoću naredbe 'Text-To-Columns' razdvojiti tj. konvertirati ćeliju iz 'B' stupca u višestruke stupce u istom redu. Dakle selektirajte sve ćelije u 'B' stupcu (u ovom slučaju B2:B4). Kliknite na tab 'DATA' -> 'Text To Columns'. Vidi sliku ispod.

Prva varijanta koristeći naredbu "Text To Columns"

Split tekst to columns

U slijedećem dijalog prozoru "Convert Test To Columns Wizard" imate slijedeću situaciju kao na slici ispod. Uočite da sam označio/uključio/čekirao tri opcije. 'Tab' je po defaultu već uključen, ja sam uključio/označio opciju 'Semicolon' (točka-zarez) jer je to glavni delimiter ali također sam uključio/ounačio i opciji 'Space' (razmak) da bi uklonio prazne razmake koji se nalaze iza točka-zarez ali ispred svake slijedeće riječi.

razdvajanje teksta koji sadrži više dilimitera ili razmaka

Klikom na 'Finish na slici iznad imamo rezultat kao na slici ispod

razdvojene riječi ili tekst iz jedne Excel ćelije naredbom Text to Columns

Druga varijanta koristeći formulu za razdvajanje teksta iz jedne ćelije koji sadrži višestruke delimitere ili separatore.

U ovoj drugoj varijanti možemo korisiti formulu da bi razdvojili iz jedne ćelije svaku riječ (string) u zasebnu ćeliju. To izgleda kao na slici ispod.

Razdojeni tekse koristeći Excel formulu

U ćeliju 'A7' postavite formulu =A2 i kopirajte je prema dolje (da bi povukli sva imena iz izvornih podataka).

U ćeliju 'B7' postavite formulu =TRIM(MID(SUBSTITUTE($B2;";";REPT(" ";99));99*(COLUMNS($B:B)-1)+1;99)).
Ovu formulu kopirajte u desnu stranu pa sve prema dolje. Sada imate izdvojene sve riječi iz jedne ćelije za svako ime iz 'A' stupca (slika iznad).

Sada imamo početnu situaciju za daljnje korake i konvertiranje u stupce tako da za svako ime prikažemo pripadajući podatak iz ostalih ćelija/stupaca.

OPCIJA 1.

Jedno od rješenja je koristeći pomoćni stupac (helper) i određene formule koje će vratiti popis imena iz 'A' stupca' i pripadajuće string-riječi za svako ime odvojeno. Vidi sliku ispod.

Izdvajanje stringa iz jedne ćelije u stupac

U situaciji na slici iznad uočite da sam već razdvojio sve string/riječi odvojene delimiterom/separatorom iz drugog worksheet koji ima naziv "by Formula". U stupcima 'K' i 'L' uočite naš željeni rezultat.

U ovom slučaju formule na slici iznad, su slijedeće:

Formula u 'A2' ćeliji: (kopirana dolje)
='by Formula'!A2

Formula u 'B2' ćeliji: (kopirana desno pa dolje)
=TRIM(MID(SUBSTITUTE('by Formula'!$B2;";";REPT(" ";99));99*(COLUMNS($B$2:B$2)-1)+1;99))

Formula u 'F2' ćeliji: (kopirana dolje). Ovo je pomoćni stupac (helper) i njega možemo sakriti ako želimo. On nam služi da izbrojimo neprazne ćelije u istom redu a svaka slijedeća formula pribraja brojanje.
Ćelija 'F1' mora biti prazna ili vrijednost nula (0).
=COUNTIF(B2:E2;"<>"&"")+F1

ARRAY formula u 'K2' ćeliji: (kopirana dolje)
=IF(ROWS(K$2:K2)>=MAX($F$2:$F$4);"";INDEX($A$2:$A$4;MIN(IF(ROWS(K$2:K2)<=$F$2:$F$4;ROW($F$2:$F$4)-ROW($F$2)+1))))

ARRAY formula u 'L2' ćeliji: (kopirana dolje)
=IFERROR(TRIM(INDEX($B$2:$E$7;MATCH(K2;$A$2:$A$7;0);COUNTIF(K$2:K2;K2)));"")

Nakon dobivenih rezultata potrebno ke selektirati cijeli raspon podataka 'K1:K14' pam desnom tipkom miša aktivirati 'Copy' naredbu pa zatim ponovo 'Paste As Values' (Paste Special -> u okviru 'Paste' označite opciju 'Values')

Slijedeći korak je filtriranje i uklanjanje/brisano praznih ćelija u 'L' stupcu. Dakle to možemo izvršiti na više načina.

- Obrisati ručno redove '9' i '12' (u situaciji kao na slici iznad za dotični raspon ćelija 'K1:L12')
- Konvertirati range 'K1:L12' u realnu Excel tablicu (INSERT tab -> Table) i tada preko filtriranja izbrisati redove koji imaju praznu ćeliju u 'L' stupcu.

Ovdje moram napomenuti da bi bilo dobro da konačne rezultate (K:L) ne kreirate u istim redovima kao što je izvorni raspon podataka (moglo bi se dogoditi da brisanjem redova u 'K:L' stupcima morate obrisati i izvorne podatke ako se nalaze u istom redu. Ovo je jedan od primjera brisanja višestrukih redova (za više detalja pogledajte tutorijal kako selektirati višestruke ROW u Excelu)

Brisanje višestrukih redova u Excelu

I naš konačni rezultat koristeći formule u iznad opisanim koracima je vidljiv na slici ispod

Očekivani rezultat nakon razdvajanja riječi odvojenih delimiterom

Isto tako možete konvertirati range u stvarnu tablicu (Table) i filtriranjem i brisanjem viška redova doći do istog rezultata.

Korištenje Pivot Table u Excelu za konvertiranje

Ako ste ljubitelj 'PivotTable' u Excelu evo još jedan primjer korak po korak. Možda će vam se činiti komplicirano ali dolazite do istog rezultata. Ov dje nam nije potreban 'helper' stupac.

Situacija je ista kao u prethodno opisanom početku ovog tutorijala.

Startna pozicija izvornih podataka

Alt+D+P za pokretanje PivotTable -> Lijevom rukom pritisnite tipku ALT i zadržite je -> desnom rukom pritisnite slovo 'D' pa zatim slovo 'P'
Sada imate dijalog prozor i na njemu označite opciju "Multiple consolidation ranges"

Multiple Consolidation ranges u Excelu

Kliknite NEXT na gornjem dijalog prozoru. Na slijedećem dijalog prozoru označite (check) "I will create the page fields"

I will create the page fields

Kliknite NEXT button na gornjem dijalog prozoru.

Na slijedećem dijalog prozoru (slika ispod) u gornjem dijelu okvira/pdijalog prozora klikninite na gumb da pokrenete mogućnost selektiranja raspona ćelija.
Selektiraj cijeli range ('A1:E4')
Kliknite na 'Add' gumb

selektiranje raspona podataka za dodavanje u Pivot Table

Na dijalog prozoru (slika iznad) kliknite 'NEXT' button.

Na slijedećem dijalog prozoru označi na kojem Sheetu želite raditi tj. kreirati 'Pivot Table' (ja sam izabrao "Existing worksheet") i ćeliju 'A7'. Kliknite na 'Finish' button (slika ispod).

Pozicioniranje Pivot Table na istom radnom listu

Sada imate kreiranu 'Pivot Table' na istom radnom listu. To izgleda kao na slici ispod.

PivotTable Fields

Na slici iznad uočite dijelove 'PivotTable Fields' u okviru kada kliknete na jednu od ćelija unutar 'PivotTable'.
U slijedećem koraku potrebno je isključiti opcije: ROW i COLUMN. Nakon toga imamo situaciju (kao na slici ispod).

Uočite sada promjenu izgleda Pivot Table. Kliknite dvoklik mišem na vrijednost (u ovom slučaju broj 12) tj. 'A8' ćelija.

Isključivanje određenih elemenata PivotTable unutar Pivot Table Fields

Nakon dvoklika mišem (double mouse click) na 'A8' ćeliju imamo situaciju kao na slici ispod. Uočite da smo u ovom koraku konvertirali Pivot Table u Excel Table (pogledajte 'Formulas' tab -> 'Name manager' i uočite 'Table1'). Također uočite da se ova Table1 nalazi na novo-kreiranom radnom listu (u ovom slučaju pod nazivom 'Sheet6')

Konvertiranje Pivot Table u običnu Table

Slijedeći korak je uklanjanje (Remove) ili brisanje 'B' stupca. Nakon toga imamo situaciju kao na slici ispod.

Brisanje stupca u Excel Table i filtriranje

Na slici iznad uočite da sada u 'B' stupcu imamo drugačije podatke (jer smo u prethodnom koraku obrisali podatke iz 'B' stupca). dakle podaci koji su se nalazili u 'C' stupcu sada se nalaze u 'B' stupcu.

Kliknite na padajući izbornik Header 'B' stupca, da otvorite opciju za filtriranje (slika iznad)

Filtriranje po stupcu u Excelu

Sada imamo slijedeću situaciju kao na slici ispod

Filtrirani stupac u Excelu

Sada selektirajte sve tri ćelije koji sadrže prazne ćelije u 'B' stupcu (B9, B12, B13) i obrišite ih (vidi sliku ispod)

Kako obrisati samo prazne ćelije u Excel Table

Sada isključite filter na 'B' stupcu, tj. nakon aktiviranja filtera kliknite na prvu stavku 'Select All' i potvrdite na OK button. i Na kraju ovo je naš rezultat (uočite da prikazana tablica je zaista Table a ne samo običan raspon podataka formatiran borderima i bojama. Razliku između navedenog možete vidjeti u tutorijalu Table1 vs Tablica u Excelu.

Rezultat nakon konvertiranja Pivot Table i filtriranja stupca

Power Query i konvertiranje teksta koji sadrži delimiter

Ako ste ljubitelj Power Query tada pogledajte još jedno jednostavnije rješenje koje je dao P.Jovanović na forumu
www.elitesecurity.org/t506436-0#3997684

Ako želite možete preuzeti primjer datoteke iz ovog tutorijala na kojem sam isti radio.

Također pogledajte vezane teme uz Excel funkciju TRANSPOSE

     

COPYRIGHT © - 2005 - 2021 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