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 grupirati i kronološki sortirati podakte po datumu iz dvije tablice u Excelu, Izdane fakture i izvodi faktura iz banke, Automatsko grupiranje i kronološko sortiranje faktura po datumima

Kopiranje, spajanje podataka iz dvije tablice i kronološko sortiranje po datumu u Excelu




sponzor



Kako grupirati i kronološki sortirati podatke po datumu iz dvije tablice u Excelu

U ovom tutorijalu prikazat ću kako možemo dvije tablice koje sadrže iste podatke ali različite redoslijede stupaca, grupirati i automatski sortirati po datumu. Uzmimo za primjer da imamo dvije tablice koje su rezultat našeg knjigovodstva. Prva tablica (Tablica1) prikazuje izdane fakture a druga tablica (Tablica2) izvode iz banke. Uočite različiti redoslijed stupaca na slici ispod sa njihovim naslovima. Ovaj redoslijed nam otežava grupiranje podataka pa zbog toga moramo koristiti pomoćni radni list i pomoćne tablice da bi unificirali redoslijed podataka.

Izdane fakture i izvodi faktura iz banke

Radni listovi "Tablica1" i "Tablica2"

Ovo su izvorni podaci na osnovu kojih želimo grupirati brojeve faktura po datumima i kronološki sortirati na jednom radnom listu, u ovom slučaju na radnom listu "results". Ovdje imamo dvije "stvarne" Excel tablice. Raspon podataka na ovim radnim listovima je A1:C21. Uočite da na ova dva radna lista u tablicama naslov stupca "KUPAC" (B / A) nije isti redoslijed ali i sam tekstualni naziv (Kupac / kupac), zašto je ovo važno vidjet ćete u nastavku pregleda tutorijala.

Kako grupirati i kronološki sortirati podatke po datumu iz dvije tablice u Excelu

Imenovani rasponi podataka u Excelu

Ja sam uvijek za imenovanje raspona ćelija u Excelu. Tako sam i u ovom primjeru imenovao neke raspone podataka radi jednostavnosti formula i lakšeg razumijevanja. U ovom tutorijalu uočite kako izgledaju formule sa apsolutnim adresama i kako izgledaju formule sa imenovanim ćelijama. Na slici ispod uočite imenovane raspone ćelija u Name Manageru. Također u Name Manageru uočite da Table1 i Table2 predstavljaju "stvarne" Excel tablice a sve ostale "tablice" koje sam ja označio bojama, samo su rasponi ćelija uokvireni linijama. U koliko ne znate kako kreirati realnu ili stvarnu tablicu u Excelu pogledajte tutorijal: Kreiranje Tablice u Excelu.

Imenovani rasponi podataka u Excelu 2013

Automatsko grupiranje i kronološko sortiranje faktura po datumima

Radni list 'results'

Rezultat koji želimo možete uočiti na slici ispod na radnom listu "results". Dakle želimo da nam Excel automatski sortira po datumima sve brojeve faktura i kupaca kojima su dotične fakture izdane. Raspon podataka u dvije tablice ispod prilagodite svojim potrebama što zavisi o zbroju faktura koje imate na izvornim tablicama (Tablica1 i Tablica2).

Pojašnjenje tablica na slici ispod. Formule možete vidjeti pri kraju tutorijala u objašnjenju svakog radnog lista posebno. Uočite dvije tablice (žuta tablica i siva tablica). U obje tablice vršimo sortiranje po datumima. Žuta tablica ima unikatne datume a siva tablica ima duplikate datuma. Više o ovim tablicama i kreiranim formulama pri kraju tutorijala.

Automatsko grupiranje i kronološko sortiranje faktura

Konsolidacija podataka iz više Excel tablica

Radni list "pomocni"

Na ovom radnom listu konsolidiramo podatke i prilagođavamo svojim potrebama tj. traženom rezultatu na radnom listu "results". Primjer koji trenutno čitate je samo jedan od mojih zamisli, ja vjerujem da postoje i drugačija i jednostavnija rješenja. No, ja vjerujem da ste čuli poslovicu "cilj ponekada opravdava sredstva". Tako i ovdje, nije važno "kako?" već je važno dobiti rezultat.

Pojašnjenje "tablica" tj. raspona ćelija na slici ispod:

Žuta tablica (raspon podataka) : Ja sam za ovaj primjer postavio raspon podataka A1:C21. Ova tablica povlači (kopira) podatke sa radnog lista 'Tablica1' tj. iz Table1. U žutoj tablici nalaze se slijedeće formule koje je potrebno kopirati prema dolje. S obzirom da je Table1 realna tablica u formulama možemo koristiti drugačije argumente od uobičajenih (u stvari Excel će sam prilagoditi formulu tokom kreiranja formule). Uz pojašnjenje ispod pogledajte sliku iznad Name Manager i imenovane raspone podataka.

Formula u ćeliji A2: =IF(Table1[@Datum]<>"";Table1[@Datum];"")
Formula u ćeliji B2: =IF(Table1[@[broj fakture]]<>"";Table1[@[broj fakture]];"")
Formula u ćeliji C2: =IF(Table1[@Kupac]<>"";Table1[@Kupac];"")

Zelena tablica (raspon podataka) : Ja sam za ovaj primjer postavio raspon podataka E1:G21. Ova tablica povlači (kopira) podatke sa radnog lista 'Tablica2' tj. iz Table2. U zelenoj tablici nalaze se slijedeće formule koje je potrebno kopirati prema dolje.

Formula u ćeliji E2: =IF(Table2[@Datum]<>"";Table2[@Datum];"")
Formula u ćeliji F2: =IF(Table2[@[broj fakture]]<>"";Table2[@[broj fakture]];"")
Formula u ćeliji G2: =IF(Table2[@kupac]<>"";Table2[@kupac];"")

Kreiranjem ove dvije tablice (žuta i zelena) ja sam dobio isti redoslijed stupaca iz dvije tablice, što mi je polazna osnova za daljnju kalkulaciju. Uočite razliku formula u ćelijama C2 i G2. Uočite da je Excel prepoznao da naslovi stupaca kupaca se razlikuju. Dakle prepoznao je početno slovo "Kupac" i "kupac". Da bi vam bilo jasnije pogledajte prvu sliku u ovom tutorijalu.

U slučaju da nije situacija kakva jeste tj. da nam Table1 nije stvarna već samo raspon podataka mi bi mogli koristiti slijedeće formule.

Formula u ćeliji A2 bi izgledala ovako: =IF(Tablica1!A2<>"";Tablica1!A2;"")
Formula u ćeliji B2 bi izgledala ovako: =IF(Tablica1!C2<>"";Tablica1!C2;"")
Formula u ćeliji C2 bi izgledala ovako: =IF(Tablica1!B2<>"";Tablica1!B2;"")

Formula u ćeliji E2 bi izgledala ovako: =IF(Tablica2!B2<>"";Tablica2!B2;"")
Formula u ćeliji F2 bi izgledala ovako: =IF(Tablica2!C2<>"";Tablica2!C2;"")
Formula u ćeliji G2 bi izgledala ovako: =IF(Tablica2!A2<>"";Tablica2!A2;"")

Smeđa tablica (raspon podataka): Ja sam za ovaj primjer postavio raspon podataka I1:K41. Ova tablica povlači (kopira) podatke iz ' žute' i 'zelene' tablice. Gornji dio ove tablice obuhvaća raspon ćelija I2:K21 i u njemu se kopiraju podaci iz 'žute' tablice. Donji dio ove tablice obuhvaća raspon ćelija I22:K41 i u njemu se kopiraju podaci iz 'zelene'.
U 'smeđoj' tablici nalaze se slijedeće formule koje je potrebno kopirati prema dolje.

Formula u ćeliji I2: =A2
Formula u ćeliji J2: =B2
Formula u ćeliji K2: =C2

Formula u ćeliji I22: =E2
Formula u ćeliji J22: =F2
Formula u ćeliji K22: =G2

U smeđoj tablici u ovom rasponu podataka I1:K41 ja sam imenovao raspon ćelija I2:I41 nazivom "BlanskRange". Ako se pitate zašto? Zato jer će mi ovaj imenovani raspon ćelija biti važan u formuli u stupcu M 'crvene tablice'. I na kraju se možda pitate zašto sam uopće kreirao 'smeđu' tablicu. Zato da bi posložio podatke iz 'žute' i 'zelene' tablice u stupac jedno ispod drugoga i olakšao si grupiranje podataka u daljnjoj kalkulaciji.

Crvena tablica (raspon podataka) : Ja sam za ovaj primjer postavio raspon podataka M1:O41. Ova tablica povlači (kopira) podatke iz 'smeđe' tablice i grupira ih jedan ispod drugoga bez praznih redova ili bez praznih ćelija. Array formule treba kopirati prema dolje (ne zaboravite da Array formule završavate sa Ctrl+Shift+Enter).

ARRAY Formula u ćeliji M2: =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange);"";INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"";ROW(BlanksRange);ROW()+ROWS(BlanksRange)));ROW()-ROW(NoBlanksRange)+1);COLUMN(BlanksRange);4)))

ARRAY Formula u ćeliji N2: =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange);"";INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"";ROW(BlanksRange);ROW()+ROWS(BlanksRange)));ROW()-ROW(NoBlanksRange)+1);COLUMN(BlanksRange)+1;4)))

ARRAY Formula u ćeliji O2: =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange);"";INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"";ROW(BlanksRange);ROW()+ROWS(BlanksRange)));ROW()-ROW(NoBlanksRange)+1);COLUMN(BlanksRange)+2;4)))

Uočite na slici ispod u 'crvenoj tablici' grupirane podatke iz 'smeđe tablice' bez praznih redova. Ako ste pogledali sliku iznad "Name Manager" mogli ste uočiti da sam imenovao raspon ćelija u stupcu M2:O41 nazivom "skupni_podaci". Nekako mi se činio prigodan da bi shvatili da sam podatke iz dvije tablice skupio u jednu bez praznih ćelija.

Konsolidacija podataka iz više Excel tablica

Siva tablica (raspon podataka) : I na kraju još jedna među tablica koja će nam poslužiti za daljnju kalkulaciju i željeni cilj. Ova tablica (vidi sliku iznad) nam je važna u slučaju da imamo duplikata u području uvjeta. U ovom slučaju to je datum tj. uvjet prema kojem želimo automatski sortirati fakture. Da bi dobio unikatne podatke datuma ja sam odlučio izbrojiti koliko se koji datum ponavlja u stupcu 'M'. Zbog toga sam u stupcu 'Q' tj. u ćeliju 'Q2' postavio formulu (kopirao je prema dolje)

=IF(M2="";"";COUNTIF(M$2:N2;M2))

Pomoću ove formule imam uvid u broj ponavljanja određenog datuma (uzlazno). Uočite da se datum 24.1.2015 pojavljuje dva puta u stupcu 'M' a formula će za ta dva datuma vratiti redne brojeve 1 i 2.

U stupcu 'R' pomoću formule tj. Excel funkcije CONCATENATE spojio sam datume i redni broj ponavljanja određenog datuma. Ćelije u stupcu 'R' formatirane su kao "general" tako da Excel prikazuje broj umjesto datuma.

Formula u ćeliji 'R2' je slijedeća: =IFERROR(VALUE(IF(M2="";"";CONCATENATE(M2;Q2)));"")

Ova formula koristi Excel funkciju IFERROR za spriječavanje prikazivanje grešaka i VALUE funkciju da konvertira tekst u brojevnu vrijednost. Također uočite da datum ima 5 znamenki a svakom datumu pridružena je znamenka pojavljivanja rednog broja iz stupca 'Q'. Npr. datum 24.1.2015 je u stvari brojevna vrijednost 42028 kada je ćelija formatirana kao "general". Ova 'siva tablica' mi je polazna osnova za sortiranje na radnom listu "results".

Ako ste pogledali sliku iznad "Name Manager" mogli ste uočiti da sam imenovao raspon ćelija u stupcu R2:R41 nazivom "sljepljeno". Nekako mi se činio prigodan da bi shvatili da sam zalijepio redni broj pojavljivanja na generalni broj datuma.

Automatsko sortiranje datuma u Excelu

Radni list "results"

Žuta tablica (raspon podataka): je varijanta kada imamo jedinstvene datume. Dakle datumi se ne ponavljaju. U ovom slučaju ovo je važno jer u stupcu 'B' koristimo dvije vrste formula a u stupcima 'C' i 'D' koristimo Excel VLOOKUP funkciju koja nije dobra za rad sa podacima kao uvjetom ako se ponavljaju. Na slici ispod uočite u 'žutoj tablici' sortirane datume uzlazno i njihove pripadajuće podatke. Ovdje je važno da uočite da u ovom slučaju Excel nije dobro kopirao sve podatke, nedostaje nam jedan datum u žutoj tablici (to je datum 24.1.2015 za fakturu sa brojem 4)

Formule u 'žutoj tablici' su slijedeće:

ARRAY Formula u ćeliji B2: =INDEX(NoBlanksRange;MATCH(0;COUNTIF(NoBlanksRange;"<"&NoBlanksRange);0))

Ova array formula mogla bi izgledati i ovako:
=INDEX(pomocni!$M$2:$M$41;MATCH(0;COUNTIF(pomocni!$M$2:$M$41;"<"&pomocni!$M$2:$M$41);0))

ARRAY Formula u ćeliji B3, ovu formulu kopirati prema dolje.
=IF(COUNTIF(NoBlanksRange;">"&B2);INDEX(NoBlanksRange;MATCH(COUNTIF(NoBlanksRange;"<="&B2);
COUNTIF(NoBlanksRange;"<"&NoBlanksRange);0));"")

Formula u ćeliji C2: =IFERROR(VLOOKUP(B2;skupni_podaci;2;FALSE);"")

Ova formula mogla bi izgledati i ovako:
=IFERROR(VLOOKUP(B2;pomocni!$M$2:$O$41;2;FALSE);"")

Formula u ćeliji D2: =IFERROR(VLOOKUP(B2;skupni_podaci;3;FALSE);"")

Ova formula mogla bi izgledati i ovako:
=IFERROR(VLOOKUP(B2;pomocni!$M$2:$O$41;3;FALSE);"")

Siva tablica (raspon podataka): je varijanta "Duplikati u Excelu" kada imamo datume koji se ponavljaju, dakle trebamo sortirati duplikate. U stupcu 'F' koristimo iste formule kao i u stupcu 'B', u stupcima 'G' i 'H' koristimo kombinaciju Excel funkcija INDEX-MATCH koje nam služe za vraćanje pripadajućih podataka u istom redu za postavljeni uvjet. Vjerojatno se pitate, odakle duplikati datuma? Postoji mogućnost da za isti datum imamo više izdatih faktura pa prema tome će nam se jedan te isti datum pojaviti više puta ali trebamo sortirati tako da nam redoslijed broja fakture bude uzlazno. Uočite stupac 'A' sive boje koji nam služi kao pomoćni za 'sivu tablicu'. U ovom stupcu povlačimo datume sa radnog lista "pomocni" ali tako da su datumi formatirani kao broj (general).

Stupac 'A' je pomoćni stupac koji povlači (kopira) podatke iz stupca 'R' sa radnog lista "pomoćni". Ovaj stupac nam je važan za 'sivu tablicu' kada imamo duplikate datuma (uvjeta prema kojem sortiramo). Ovaj stupac 'A' nije nam važan za 'žutu tablicu.'

U ovoj 'sivoj tablici' glavne funkcije su nam INDEX i MATCH koje su zamjena za VLOOKUP u ponekim slučajevima jer je njima lakše manipulirati.

Formule u ćelijama su slijedeće: Uočite koliko su formule jednostavnije kada upotrebljavamo imenovane raspone podataka.

ARRAY Formula u ćeliji A2: =INDEX(sljepljeno;MATCH(0;COUNTIF(sljepljeno;"<"&sljepljeno);0))

Ova formula mogla bi izgledati i ovako:
=INDEX(pomocni!$R$2:$R$41;MATCH(0;COUNTIF(pomocni!$R$2:$R$41;"<"&pomocni!$R$2:$R$41);0))

ARRAY Formula u ćeliji A3, ovu formulu kopirati prema dolje:
=IF(COUNTIF(sljepljeno;">"&A2);INDEX(sljepljeno;MATCH(COUNTIF(sljepljeno;"<="&A2);COUNTIF(sljepljeno;"<"&sljepljeno);0));"")

Ova formula mogla bi izgledati i ovako:
=IF(COUNTIF(pomocni!$R$2:$R$41;">"&A2);INDEX(pomocni!$R$2:$R$41;MATCH(COUNTIF(pomocni!$R$2:$R$41;"
<="&A2);COUNTIF(pomocni!$R$2:$R$41;"<"&pomocni!$R$2:$R$41);0));"")

Pomoću formula u ćelijama 'A2' i 'A3:A100' automatsko sortiramo brojevne vrijednosti uzlazno.

Formula u ćeliji F2 i kopirati je prema dolje: =IFERROR(VALUE(MID(A2;1;5));"")
Ova formula pomoću Excel funkcije MID ekstrahira prvih 5 znamenki iz brojevne vrijednosti što je u stvari generalna vrijednost za datum ali njen rezultat je tekstualnog formata, VALUE pretvara tekst u brojevnu vrijednost a Excel prikazuje datum kada formatiramo ćelije za datumski prikaz.

Formula u ćeliji G2 i kopirati je prema dolje: =INDEX(pomocni!$N$2:$N$41;MATCH(A2;sljepljeno;0))
Formula u ćeliji H2 i kopirati je prema dolje: =INDEX(pomocni!$O$2:$O$41;MATCH(A2;sljepljeno;0))

 

Automatsko grupiranje i kronološko sortiranje faktura po datumima u Excelu

Uočite formule u ćelijama 'G2' i 'H2'. Ja namjerno nisam htio imenovati raspone ćelija u stupcima 'N' i 'M' na radnom listu "pomocni" da bi ste shvatili i uočili razliku jednostavnosti formula kada se radi sa apsolutnim adresama i imenovanim podacima.

I na kraju pogledajte rezultate u 'sivoj tablici'. Dobili smo željeni rezultat a to su podaci sortirani po datumima uzlaznim redoslijedom bez obzira što nam se datumi ponavljaju. U stupcu 'F' oblikujte ćelije u datumski format. Naravno, stupac 'A' možemo sakriti (Hiden)

Temu vezanu uz Fakture u Excelu (mogućnost downloada kompletne datoteke sa svim funkcionalnim formulama i VBA makronaredbama) možete pogledati na slijedećem linku, tutorijal o izradi fakture u Excelu korak po korak.

Ostali tutorijali vezani za kopiranje podataka u Excelu

sponzor





     

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