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 -Automatsko povećanje broja reda prilikom kopiranja adrese raspona ili range, Kako kreirati formulu koja će automatski povećati broj reda i mijenjati raspon ćelija prilikom kopiranja. Increase number of row when copying down, Prikaži ili kopiraj posljednju popunjenu ćeliju u stupcu ili redu u Excelu, Prikaži podatak tekst iz zadnje popunjene ćelije u stupcu, Kako ugnijezditi formulu, Kako automatski uvećati range za određeni broj redova prilikom kopiranja formule.

Automatsko povećanje broja reda prilikom kopiranja adrese raspona ili range




sponzor



Automatski povećaj broj reda za određeni raspon prilikom kopiranja formule

Vjerojatno ste se nekada zapitali kako automatski prilikom kopiranja formule povećati broj redova za određenu vrijednost. Ponekada nam je potreban pomoćni stupac sa apsolutnim ili relativnim adresama raspona podataka koji koristimo kao izvor adrese ili raspona ćelija iz kojeg trebamo uzeti rezultat ili kopirati određenu vrijednost.

U ovom primjeru ja ću pokazati kako možemo kreirati formulu koja će automatski mijenjati raspon adresa prilikom kopiranja formule. Također uz rezultate ove formula prikazat ću gdje dotične raspone adresa mi možemo koristiti.
Startnu situaciju pogledajte na slici ispod.

Vi uočite u stupcu 'C' raspone ćelija koje sam ja označio različitim bojama (zbog lakšeg razlikovanja za izradu ovog tutorijala).
Naš cilj je za svaki različiti raspon ćelija vratiti podatak iz prve ćelije i posljednje ćelije u određenom rasponu. U stupcu 'E' vi uočite već gotove rezultate nakon što sam ja kreirao formulu koju ću objasniti u ovom tutorijalu. U stupcima 'F' i 'G' potrebno je prikazati podatke iz prve i zadnje ćelije u dotičnom rasponu.

Odmah ste vi mogli uočiti da je stupac 'E' pomoćni, koji ćemo koristiti za daljnje izračune. U ovom stupcu nalaze se rasponi ćelija koji su formatirani kao tekst, a mi želimo prilikom kopiranja mijenjati raspone ćelija.

Automatsko povećanje broja reda prilikom kopiranja formule

Kako kreirati formulu koja će automatski povećati broj reda i mijenjati raspon ćelija prilikom kopiranja.

Dakle, mi trebamo kreirati formulu za uvećanje raspona prema određenom kriteriju. U ovom slučaju ja želim prilikom kopiranja povećavati tj. mijenjati raspon ćelija za grupu redova kojoj je uvjet devet redova (9 rows). Poslije kopiranja formule dolje, ja želim imati raspone koji su automatski pomaknuti za devet redova.

Na slikama ispod ja ću prikazati korak po korak kako možemo kreirati formulu od više ugniježđenih funkcija. U stvari, cilj ovog tutorijala je pokazati kako se radi korak po korak kada želimo ugnijezditi više funkcija u jednu formulu.

Automatsko uvećanje redova u rasponu adrese

Na slici iznad vi uočite, šest koraka koje sam ja zamislio da bi bili potrebni tokom kreiranja složenije formule. Odmah vi uočite i rezultate koje ću ja objasniti u slijedećim koracima ovog tutorijala. Brojevi redova koje vraćaju formule ispod baziraju se na prvom retku u ćeliji 'C2' odakle počinje brojanje 1,2,3,4...

Prvi dio adrese raspona u ćeliji 'E2' (separator je dvotočka)

1st (prvi korak)

S obzirom da smo rekli, u ćeliji 'C2' nalazi se fiksna adresa raspona (kao tekst) i ona je startna adresa raspona. MI ćemo prvu formulu postaviti u ćeliju 'E3'.

2nd (drugi korak)

U ćeliji 'F12' nalazi se slijedeća formula ispod.
(Ova formula kao rezultat vraća nam prve dvije znamenke teksta u ćeliji 'E2', ove dvije znamenke su fiksne i ne mijenjaju se. Ova formula kasnije će nam poslužiti da njen rezultat spojimo (Concatenate) sa ostalim rezultatima)
=LEFT(E2;2)

3rd (treći korak)

U ćeliji 'G12' nalazi se slijedeća formula ispod.
(Ova formula u sebi sadrži prethodnu koja je ugniježđena, ova formula ispod vraća prvi dio raspona iz ćelije 'E2'. Dakle separator je dvotočka (:). Sve što se nalazi ispred dvotočke u tekstu u ćeliji 'E2' ova formula će prikazati. Ovo nam je važno jer prilikom kopiranja formule dolje, pojaviti će se broj redova koji će imati više znamenki koje nisu fiksirane dužinom broja znamenki. Kao što vidite u ćeliji 'G12' rezultat je tekst '$C2'. Kada bi ovu formulu kopirali dolje, u devetom redu vratila bi nam rezultat '$C65'. Nadam se da vam je jasno što sam ja htio reći)
=IFERROR(LEFT(E2;FIND(":";E2)-1);E2)

4th (četvrti korak)

U ćeliji 'I12' nalazi se slijedeća formula ispod.
(Ova formula koristi rezultat iz ćelije 'G12' i vraća nam sve brojeve koji se nalaze iza prva dva znaka '$C'. Naravno ovdje će biti više brojeva nakon višestrukog kopiranja formule dolje. Uočite broj '7' u formuli, ja sam ga stavio proizvoljno, taj broj može biti manji ili veći a on određuje maksimalan broj znamenki redova prilikom kopiranja. Sve zavisi koliko redova vi očekujete nakon kopiranja).
=MID(G12;3;7)

5th (peti korak)

U ćeliji 'J12' nalazi se slijedeća formula ispod.
(Ova formula povezana je sa ćelijom 'I2' a kao rezultat vraća broj 10. To je broj redova za slijedeći range koji želimo kreirati. Pojedini range sadrži devet redova, dakle mi trebamo u slijedećem desetom redu imati novi range. Dakle, formula u ćeliji 'I2' vratila nam je rezultat 2, ovom broju dodajemo osam redova da bi ukupno bilo deset redaka).
=MID(G12;3;7)+8

6th (šesti korak)

U ćeliji 'K12' nalazi se slijedeća formula ispod. (Ova formula vraća konačan rezultat za prvi dio raspona adresa u odnosu na source data u 'E2' ćeliji. Ova formula sastavljena je od dvije formula (Concatenate) kako slijedi ispod
=LEFT(E2;2)&MID(G12;3;7)+9

Pogledajte kako izgleda formula 'G12', nju ćemo staviti umjesto adrese ćelije 'G12' u formuli iznad, pa naša formula izgleda ovako.
=LEFT(E2;2)&MID(IFERROR(LEFT(E2;FIND(":";E2)-1);E2);3;7)+9 i vraća rezultat '$C11'

 

Drugi dio adrese raspona u ćeliji 'E2' (separator je dvotočka)

U ćelijama koje se nalaze u trinaestom redu, formule vraćaju potrebne rezultate za drugio dio raspona iz ćelije 'E2'. Princip izračuna je isti kao u prethodno opisanom dijelu iznad, ovog tutorijala.

1st (prvi korak)

U ćeliji 'F13' nalazi se slijedeća formula ispod. (Formula je ista kao u ćeliji 'F12' u prethodnom dijelu tutorijala)
=LEFT(E2;2)

2nd (drugi korak)

U ćeliji 'G13' nalazi se slijedeća formula ispod.
(Ova formula je ista kao i u ćeliji 'K12'. Ova formula treba nam kao prvi dio konačnog rezultata)
=LEFT(E2;2)&MID(IFERROR(LEFT(E2;FIND(":";E2)-1);E2);3;7)+9

3rd (treći korak)

U ćeliji 'H13' nalazi se slijedeća formula ispod.
(Ova formula je u stvari fiksni dio drugog dijela adrese raspona. Ja sam ovdje spojio (Concatenate) dvotočku i prva dva znaka iz ćelije 'E2'. Naravno ovo možemo uraditi jer se radi samo o jednom stupcu. Ovu formulu kasnije ćemo spojiti sa ostalim formulama da bi dobili konačnu formulu)
=":"&LEFT(E2;2)

4th (četvrti korak)

U ćeliji 'I13' nalazi se slijedeća formula ispod.
(Ova formula pronalazi poziciju dvotočke u tekstu ćelije 'E2'. U ovom slučaju to je četvrta znamenka na koju dodajemo broj 3 i kao rezultat imamo broj 7 koji će nam biti startna pozicija u slijedećoj formuli).
=FIND(":";E2;1)+3

5th (četvrti korak)

U ćeliji 'J13' nalazi se slijedeća formula ispod.
(Ova formula kao rezultat vraća sve znamenke iza '$C8:$C' teksta u ćeliji 'E2'). U našem slučaju to je broj 10 iz ćelije 'E2'. Napomena, ovaj broj 10 nema nikakve veze sa ćelijom 'J12' iznad. Formula će vratiti maksimalno sedam znamenki. Ako pogledate na sedmoj poziciji u tekstu ćelije 'E2' nalazi se znamenka 1 a na osmoj poziciji je znamenka 0).
=MID(E2;FIND(":";E2;1)+3;7)

6th (šesti korak)

U ćeliji 'K13' nalazi se slijedeća formula ispod. (Ova formula vraća konačan rezultat za drugi dio raspona adresa u odnosu na source data u 'E2' ćeliji. Ova formula sastavljena je od dvije formula (Concatenate) kako slijedi ispod).
=":"&LEFT(E2;2)&MID(E2;FIND(":";E2;1)+3;7)+9

Konačna formula u ćeliji 'E3' koju možemo kopirati dolje je slijedeća. Prilikom kopiranja ove formule vi uočite kako se automatski povećavaju redovi za određeni razmak prilikom kopiranja formule dolje.

=LEFT(E2;2)&MID(IFERROR(LEFT(E2;FIND(":";E2)-1);E2);3;7)+9&":"&LEFT(E2;2)&MID(E2;FIND(":";E2;1)+3;7)+9

 

Na kraju nam ostaje da postavimo formule u ćelije 'F2' i 'G2'

Prikazivanje prve i posljednje vrijednosti u stupcu različite od nule

Formula u ćeliji 'F2' je slijedeća ispod (formulu kopirajte dolje). (Formula u ćeliji 'F2', koristi range podataka u samoj formuli za vraćanje rezultata. Ovu formulu nemožemo kopirati dolje jer će vratiti netočan rezultat. To je zbog toga što bi rasponi ćelija mijenjali prilikom kopiranja dolje, ako bi postavili apsolutne adrese opet ne bi bilo dobro.)
=INDEX(C2:C10;MATCH(1;INDEX(ISNUMBER(C2:C10)*(C2:C10>0);0);0))

(Ova formula vezana je za ćeliju 'E2' i nju možemo kopirati dolje. Ova formula je u stvari ista kao i prethodna ali koristi INDIRECT funkciju koja je vezana za ćeliju E2. Vratit će nam prvu vrijednost koja je veća ili različita od nule (0), iz dotičnog range.)
=INDEX(INDIRECT(E2);MATCH(1;INDEX(ISNUMBER(INDIRECT(E2))*(INDIRECT(E2)>0);0);0))

Formula u ćeliji 'G2' je slijedeća ispod (formulu kopirajte dolje). (Ova formula kao rezultat vraća zadnju popunjenu ćeliju koja je različita ili veća od nule (0) iz specifiranog raspona u ćeliji 'E2'. za ove dvije formule vrijedi isto što i za prethodne dvije)
=LOOKUP(2;1/(C2:C10<>0);C2:C10)
=LOOKUP(2;1/(INDIRECT(E2)<>0);INDIRECT(E2))

Vi uočite da sam ovdje koristio INDIRECT funkciju koja u odnosu na adresu raspona iz ćelije 'E2' vraća ispravan rezultat. Nadam se da ste shvatili kako kreirati formulu koja će prilikom kopiranja povećavati raspon redova za određeni broj redaka.

Automatsko povećanje raspona za 10 redova (increase block range by 10 rows)

Ovaj primjer ispod prikazuje kako prilikom kopiranja možete povećati broj redova za deset (10).

Zadatak je slijedeći:
Mi trebamo pronaći MAX i MIN vrijednost u određenom bloku raspona ćelija. Trebamo kreirati formulu koja će prilikom kopiranja prema dolje, uvećati range za 10 redova.

Na slici ispod uočite slijedeće raspone: Pomak bloka ćelija je deset redaka.
- $A2:$A11, B2:B11, C2;C11
- $A12:$A21, B12:B21, C11:C21
- $A22:$A31, B22:C31, C22:C31

Dakle, prilikom kopiranja formule mi trebamo automatski povećati range redova iz B2:B11 => B12:B21 => B22:B31

Automatsko povećanje raspona za 10 redova

Na slici iznad uočite rezultate u stupcima 'E', 'F', 'G', 'H'.

Formula u ćeliji 'E2' je slijedeća. (formula vraća maksimalnu vrijednost (najveći broj) iz raspona 'B2:B11'.
Prilikom kopiranja formule dolje, automatski se povećava broj retka za deset redova (blok ćelija u rasponu)
=IF(MAX(INDIRECT("$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9))=0;"";MAX(INDIRECT("$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9)))

Formula u ćeliji 'F2' je slijedeća. (formula vraća datum iz stupca 'A' za maksimalnu vrijednost iz raspona 'B2:B11'.
=IFERROR(INDEX(INDIRECT("$"&CHAR(64+COLUMN(A1))&10*ROW()-18&":$"&CHAR(64+COLUMN(A1))&""&10*ROW()-9);MATCH(E2;INDIRECT("$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9);0));"")

Formula u ćeliji 'G2' je slijedeća. (formula vraća minimalnu vrijednost (najmanji broj) iz raspona 'C2:C11'.
=IF(MIN(INDIRECT("$"&CHAR(64+COLUMN(C1))&10*ROW()-18&":$"&CHAR(64+COLUMN(C1))&""&10*ROW()-9))=0;"";MIN(INDIRECT("$"&CHAR(64+COLUMN(C1))&10*ROW()-18&":$"&CHAR(64+COLUMN(C1))&""&10*ROW()-9)))

Formula u ćeliji 'H2' je slijedeća. (formula vraća datum iz stupca 'A' za maksimalnu vrijednost iz raspona 'C2:C11'.
=IFERROR(INDEX(INDIRECT("$"&CHAR(64+COLUMN(A1))&10*ROW()-18&":$"&CHAR(64+COLUMN(A1))&""&10*ROW()-9);MATCH(G2;INDIRECT("$"&CHAR(64+COLUMN(C1))&10*ROW()-18&":$"&CHAR(64+COLUMN(C1))&""&10*ROW()-9);0));"")

Formula u ćeliji 'J2' samo je primjer kako se mijenja raspon ćelija prilikom kopiranja dolje, a ona izgleda ovako ispod.
="$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9

Ovdje želim napomenuti da je ova formula koju vidite iznad zavisna u kojem redu se nalazi. Ukoliko bi ovu formulu kopirali u ćeliju 'J14', tj. započeli kreiranje raspone u ćeliji 'J14' tada bi nam formula vratila range '$B122:$B131'. Dakle, u ovom slučaju ako bi nam to bila prva formula za kopiranje tada bi trebali izmjeniti neke parametre u samoj formuli.

Tako slijedi.
Ako iznad navedenu formulu želimo postaviti u četrnaesti red a da nam vrati range 'B2:B11', tada trebamo postaviti formulu u slijedećem obliku. (uočite crvenom bojom označene dijelove formule)
="$"&CHAR(64+COLUMN(B1))&10*ROW()-148&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-139

Zaključak:
Dakle, imamo četiri formule koje kopiramo dolje. Nema potrebe da kopiramo cijeli blok ćelija kao u primjeru ispod.

Automatsko povećanje raspona kopiranjem bloka ćelija

Postoji još jedan način kako prikazati maksimalnu i minimalnu vrijednost za određeni raspon ćelija. Na slici ispod uočite situaciju, u kojoj kopiranjem bloka ćelija automatski mijenjamo raspon ćelija.

Automatsko povećanje raspona kopiranjem bloka ćelija

Formula u ćeliji 'D11' je slijedeća.
=MAX(B2:B11)

Formula u ćeliji 'E11' je slijedeća.
=MIN(B2:B11)

Označite blok ćelija od 'D2:D11' i kada se pojavi 'križić' kliknite mišem na "Fill handle" zadržite lijevu tipku miša i povucite prema dolje. Automatski se mijenja range za 10 redaka i vraća rezultat za dotičnu formulu.

Tutorijali vezani za prvu ili posljednju ćeliju u Excelu

 

Također savjetujem da pogledate što su to Volatile funkcije. Neke od njih korištene su u ovom tutorijalu.

- Prikaži posljednju popunjenu ćeliju iz filtriranog stupca
- Prikaži posljednju popunjenu ćeliju u stupcu
- Kopiraj podatak iz zadnje popunjene ćelije u istom retku
- Kako ugnijezditi funkciju u formulu
- Prikaži zadnji datum za određenu grupu šifri
- Automatsko numeriranje ne-susjednih redova ili ćelija
- INDIRECT funkcija u Excelu sa primjerima

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