IZBORNIK HOME FORUM ACCESS 2003 EXCEL 2003 WORD 2003  .
   
   
HOME
FORUM Win Tips&Tricks
   
KAKO INSTALIRATI
WINDOWS XP ?
Kako instalirati Win XP sa USB STICKA
Kako instalirati WINDOWS 7 ?
Naučite za 15 minuta raditi u Windows XP
Naučite Internet Explorer i Outlook Express
za 15 minuta
Kako kreirati BOOT CD za instalaciju Win95
   
MS OFFICE 2003
MS OFFICE 2007
   
   
HOME NETWORK
tutorijal za mreže
Network Windows 7 - XP
   
ZANIMLJIVI LINKOVI
BROJEVNI SUSTAVI
(DEC, OKT, BIN, HEX )
CMD - Command Prompt
CISCO - CCNA tutoriali
VLSM and SUBNETTING
   
Tutorijali za phpBB forum
JAVASCRIPT
VISUAL BASIC 6.0
AUTOCAD 2007
 
.
MICROSOFT EXCEL 2007 - Copy range but skip blank rows, Copy column without empty cells - nonblank, nonblanks, non blank

Kako kopirati podatke bez prazne ćelije
(Kopiranje raspona podataka bez prazne ćelije)

Search This Web Site

Kako kopirati podatke iz jednog stupca u drugi i grupirati bez praznog reda ili ćelije
Copy range but skip blank rows

Ako imate potrebu kopirati podatke (vrijednosti ili tekst) iz jednog stupca u drugi, ili pak raspon podataka ali samo u jenom stupcu koji sadrži prazne redove ili ćelije tada iskoristite naredbu GO TO.

Situacija je slijedeća:

Na slici lijevo ispod uočite podatke u stupcu "D" koje sam obojao da bi shvatili što je bit problema. Podatke je potrebno kopirati i grupirati u stupac "A" sa što kraće muke ali bez praznih redova između podataka (da ne kopiramo grupu podataka po grupu).
U prvom koraku potrebno je selektirati cijeli raspon podataka, ako se podaci protežu u stotine redova tada selektirajte cijeli stupac "A" klikom na SLOVO stupca.



Sada je potrebno pokrenuti naredbu GO TO. Naredbu Go To pokrećete sa izbornika Find&Select => Go To... (CTRL+F5)
Otvara vam se dijalog prozor "Go To" i na njemu kliknite na gumb Special...

U dijalog prozoru Go To Special selektirajte radio button CONSTANTS pa potom klik na gumb OK. Sada su svi podaci u stupcu "D" selektirani svaka grupa zasebno. Pokrenite naredbu COPY ili pritisnite kombinaciju tipki CTRL+C

Nakon pokretanja naredbe Copy uočite da su se svaka grupa zasebno uokvirile iscrtkanim okvirom. Sada kliknite na ćeliju u koju želite započeti kopiranje prvog podatka. U ovom slučaju to je ćelija A1 pa pokrenite naredbu PASTE ili pritisnite kombinaciju tipki CTRL+V. I na kraju imamo kopirane i grupirane sve naše podatke iz stupca D.

Kopirani i grupirani podaci bez prazne ćelije ili reda

Kako AUTOMATSKI kopirati jedan stupac u drugi bez praznih redova i grupirati podatke, ignorirati prazne ćelije
(How to automatically copy one column to another with no blank cells and grouped data)

U slučaju da imate situaciju kao na slici ispod. U stupcu F nalazi se formula koja zbraja vrijednosti iz stupaca D i E. Prilikom unosa vrijednosti u ćelije stupca D i E potrebno je da se Zbroj AUTOMATSKI kopira (rezidentno u realnom vremenu) u stupac H. Uz ovaj uvjet potrebno je i da se podaci u stupcu H grupiraju jedan ispod drugog bez praznih ćelija. Vrijednosti u stupcima D i E unosimo periodično.

Da bi izbjegli pojavljivanje bilo kakvog rezultata u stupcu F ako nisu ispunjeni uvjeti za stupce D i E tj upisane nekakve vrijednosti u njih postavit ćemo jednu od formula u stupac F.

Za ovo rješenje koristiti ćemo funkciju IF kao i funkciju AND ili funkciju OR koje ćemo ugraditi u funkciju IF.

=IF(AND(D3>0;E3>0);D3+E3;"")

Ova formula ima dva uvjeta i oba moraju biti ispunjena. Dakle i vrijednost u stupcu D i E moraju biti veće od nule (0) da bi se Zbroj izvršio u protivnom je rezultat prazna ćelija

 

Ako jedan od uvjeta tj. vrijednosti može biti nula (0), tada koristimo formulu

=IF(OR(D3>0;E3>0);D3+E3;"")

Ova formula ima dva uvjeta i samo jedan uvjet mora  biti ispunjen. Dakle ili vrijednost u stupcu D ili vrijednost u stupcu E mora biti veći od nule (0) da bi se Zbroj izvršio u protivnom je rezultat prazna ćelija

Ovisno o vašim potrebama koristite jednu od formula

 

Problem automatskog kopiranja i preskakanja praznih ćelija možemo riješiti formulom koja slijedi.

1. Prvo je potrebno imenovati raspone podataka koji su nam važni. U ovom slučaju imamo DVA raspona podataka.
Prvi raspon F3:F26 koji imenujemo nazivom npr: BlanksRange
Drugi raspon H3:H26 koji imenujemo nazivom npr: NoBlanksRange (nonblank, nonblanks, non blank)

2. Nakon imenovanja raspona podataka potrebno je u ćeliju H3 upisati slijedeću matričnu ARRAY formulu (formulu polja)

=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)))

Zbog toga što je ova formula matrična formula (formula polja), nakon upisa formule potrebno je na tipkovnici pritisnuti kombinaciju tipki CTRL+SHIFT+ENTER u isto vrijeme, da bi se automatski unijele vitičaste zagrade na početku i na kraju formule. To izgleda ovako u ćeliji H3



Formulu iz ćelije H3 potrebno je kopirati prema dolje do kraja našeg raspona (H3:H26). Ovo baš i ne morate. Sve zavisi o broju praznih ćelija koje se pojavljuju u stupcu "F". Ali ako imate preko stotinu redova i barem 30 mogućih praznih ćelija (razmak podataka između redova) tada je poželjan što veći raspon u stupcu "H"

I na kraju naš rezultat izgleda ovako kao na slici ispod. Uočite da je formula u stupcu "H" ignorirala prazne ćelije iz stupca "F" i uz to grupirala sve rezultate od prvog do zadnjeg. Ako upišemo vrijednosti u ćelije D16 i E16 automatski će nam se u ćeliji "F16" pojaviti rezultat kao Zbroj a taj zbroj AUTOMATSKI će se kopirati u prvu praznu ćeliju "H10"

Automatic copy from column without nonblank or nonblanks or non blank row or cells

Kako automatski kopirati podatke bez praznih ćelija u drugi stupac i ignorirati NULU (0) kao vrijednost

U ovom primjeru imamo situaciju gdje trebamo kopirati podatke iz stupaca R i S u rasponima R10:R23 i S10:S23 u stupce T i U ali tako da se brojevi grupiraju bez praznih ćelija kao i da se ignorira NULA kao vrijednost.

Adsense sponzor



 
 

PRVI NAČIN rješavanja zadatka je pomoću formule. Za ovaj primjer evo dvije formule koje možete koristiti za isto rješenje problema.

Prva formula je: ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter

=IF(ISERROR(INDEX($R$10:$R$23;SMALL(IF(VALUE($R$10:$R$23)>0;ROW($R$10:$R$23)-9;1000);ROW()-9);1));"";INDEX($R$10:$R$23;SMALL(IF(VALUE($R$10:$R$23)>0;ROW($R$10:$R$23)-9;1000);ROW()-9);1))

Druga formula je:  ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter

=INDEX($S$10:$S$23;SMALL(IF(VALUE($S$10:$S$23)>0;ROW($S$10:$S$23)-9;1000);ROW()-9);1)

Uočite u obje formule dio formule koji je vezan uz funkciju ROW a to je -9. Ovaj broj -9 označava da nam se prvi podatak nalazi u desetom (10) redu. Ako bi ovu formulu primijenili na podatke koji kreću od prvog reda tada bi ovaj -9 izbacili iz formule a ako nam je prvi podatak u sedmom (7) redu tada bo ovaj broj zamijenili sa -6.

DRUGI NAČIN rješavanja zadatka je pomoću makronaredbe. Ovu makronaredbu kopirajte u sam dotični Sheet u VBE.
Uočite da je makronaredba uređena za dva stupca, ako želite samo jedan stupac tada izbacite dio koda koji je obojan tamno-plavom bojom a ostale boldane dijelove izmijenite prema potrebi.
Makronaredbu pokrećete sa ALT+F8 => Select => RUN ili preko buttona koji povežete sa makronaredbom

Sub KopirajIgnoreNula()

 

Application.ScreenUpdating = False

 

Range("T10:U23").ClearContents  'BRISANJE PRETHODNIH UNOSA

 

    Columns("R:R").Select 'izvorni raspon podataka

    Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd 'uvjet samo podaci veći od nula

    Selection.Copy

    Range("T9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'prva ćelija u koju se kopiraju podaci

    Application.CutCopyMode = False

    Selection.AutoFilter

   

    Columns("S:S").Select 'izvorni raspon podataka

    Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd 'uvjet samo podaci veći od nula

    Selection.Copy

    Range("U9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'prva ćelija u koju se kopiraju podaci

    Application.CutCopyMode = False

    Selection.AutoFilter

        Range("A1").Select 'nakon kopiranja selektira se ćelija A1

 End Sub

 

Na jednom mjestu popis svih tema vezanih za kopiranje (copy) u Excelu: Tutorijali vezani za radnje kopiranja u Excelu

Adsense sponzor




Adsense sponzor




©- 2006 - 2018 - IvanC  - Sva prava pridržana.  ic.ims.hr