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 EXCE 2013 tutoriali - Kopiranje duplikata šifri na određeni radni list, Kako kopirati redove za sve iste šifre na specifičan radni list, Kopiraj sve iste šifre u odnosu na broj pojavljivanja na master radnom listu, Kopiranje duplikata pod uvjetom na drugi radni list, Kako kopirati sve redove koji sadrže istu šifru, Kako kopirati određene redove koji sadrže uvjet koristeći VBA makronaredbe

Kopiranje redova za sve duplikate pod uvjetom




sponzor



Kako kopirati sve redove i pripadajuće podatke za sve šifre koje su duplikati u Excelu

U ovom tutorijalu prikazati ću kako možemo kopirati pod uvjetom određene redove na druge specifične radne listove. Ovaj tutorijal sadrži dvije varijante rješenja. Prvi dio tutorijala riješenje je koristeći formule. Drugi dio rješenja je koristeći VBA makronaredbe.

Uočite situaciju problema na slici ispod. U stupcima imamo određene stavke a u redovima imamo ID šifre i pripadajuće podatke za dotičnu šifru. Svaka ID šifra pojavljuje se više puta. Dakle imamo duplikate ID šifri. Naš cilj je kopirati podatke na specifične druge radne listove, ako je uvjet broj pojavljivanja određene ID šifre.

Kopiranje duplikata redova na drugi radni list

Na slici ispod uočite ostale specifične radne listove na kojima želimo imati rezultate za sve duplikate koje se pojavljuju određeni broj puta. Tako uočite da imamo nazive radnih listova (1 ID, 2 ID, 3 ID, 4 ID, 5 ID, 6 ID). Radni listovi imaju broj u nazivu a time smo definirali koliko je određenih duplikata.

npr1. Ako se neka ID šifra pojavljuje 2x tada dotičnu ID šifru i sve pripadajuće podatke trebamo kopirati na radni list "2 ID"
npr2. Ako se neka ID šifra pojavljuje 4x tada dotičnu ID šifru i sve pripadajuće podatke trebamo kopirati na radni list "4 ID"

Kopirani redovi za specifičnu ID šifru koja se ponavlja

Kako kopirati sve redove koji sadrže istu šifru

Rješavanje ovog problema možemo izvesti koristeći formulu za brojanje pojavljivanja određene ID šifre. Ja sam ispred tablice dodao jedan stupac u kojem sam postavio formulu koja će izbrojiti koliko puta se pojavljuje određena ID šifra u 'B' stupcu.

Formula u ćeliji 'A2' je slijedeća: (ova formula se može iskoristiti ako želimo i neki drugi uvjet prema kojem kopiramo redove. npr. Ako želimo kopirati prema broju pojavljivanja podatka u 'D' stupcu tada formulu izmjenimo

(prebrojava broj pojavljivanja u B stupcu)
=COUNTIF($B$2:$B$27;B2)

ili

(prebrojava broj pojavljivanja u D stupcu)
=COUNTIF($D$2:$D$27;D2)

kopiranje redova pod uvjetom u stupcu

Ako želite možete skratiti put do postavljanja uvjeta u ovom slučaju koristiti ćemo Data Validation da bi postavili padajuću listu uvjeta. Dakle u ćeliju 'A1' na radnom listu "master" za listu uvjeta unesite B;C;D;E

U ćeliju 'A2' postavite slijedeću formulu (kopirajte je do zadnjeg reda)

=COUNTIF(INDIRECT("$"&$A$1&"$"&2&":"&"$"&$A$1&"$27");INDIRECT($A$1&ROW(A2)))

Na ovaj način dovoljno je izabrati stupac kao uvjet sa padajuće liste u ćeliji 'A1' i formula će automatski prebrojati stavke sa popisa a time i automatski kopirati sve redove prema zadanom uvjetu na ostale radne listove.

Kopiranje duplikata pod uvjetom na drugi radni list koristeći formulu

Na slici ispod uočite da sam na svakom radnom listu dodao stupac ispred tablice i njemu uradio slijedeće.

U ćeliji 'A1' postavio sam formulu koja kao rezultat vraća broj iz naziva aktivnog radnog lista. Ovaj broj je uvjet za daljnju kalkulaciju, a to znači da na ovom aktivnom radnom listu, Excel treba prikazati sve redove sa radnog lista "master" koji se ponavljaju 2x. Isto tako na radnom listu "4 ID" rezultat je broj '4' a to je uvjet da nam Excel prikaže sve redove sa radnog lista "master" koji sadrže ID šifru koaj se ponavlja 4x. itd itd...

=VALUE(LEFT(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256);1))

ARRAY formula u ćeliji 'A2' je slijedeća. (ova CSE formula nam kao rezultat vraća popis redova ili podatke iz dotičnog reda sa radnog lista "master" ako je ispunjen uvjet iz ćelije 'A1'. Formulu kopirajte u desnu stranu do zadnjeg stupca pa zatim prema dolje

=IFERROR(INDEX(master!$A$2:$E$27;SMALL(IF($A$1=master!$A$2:$A$27;ROW(master!$A$2:$A$27)-MIN(ROW(master!$A$2:$A$27))+1; ""); ROW(A1));COLUMN(A1));"")

Kako kopirati određene redove koji sadrže uvjet koristeći formule

Kako kopirati redove pod uvjetom koristeći VBA makronaredbe

-------------------Rješenje VBA - Opcija 1 ---------------------

Ovaj problem možemo riješiti koristeći VBA makronaredbe. Slijedeće VBA makronaredbe kopirajte u vaš VBE za Excel i pokrenite koristeći VBA button.

Prva VBA makronaredba koja čisti ili briše postojeće podatke na radnim listovima je ispod. Ovu VBA makronaredbu kopirajte u standardni Module1

Private Sub ClearRange()
'Clear Range on Multiple Sheets
On Error Resume Next
With Worksheets("1 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("2 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("3 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("4 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("5 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("6 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub

Druga VBA makronaredba koja kopira sve redove koji ispunjavaju uvjet je slijedeća. Ovoj VBA makronaredbi pridružite VBA button na radnom listu "master". Ovu VBA makronaredbu kopirajte u standardni Module1

Sub CopyAllRowsIfMatchCondition()
'Sheet master Button1
'Copy rows if match condition from "master" sheet to another specific sheets to A column

Call ClearRange 'calling clear procedure
Dim i, LastRow
LastRow = Sheets("master").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("master").Cells(i, "A").Value = 1 Then 'condition number 1
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("1 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 2 Then 'condition number 2
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("2 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 3 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("3 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 4 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("4 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 5 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("5 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 6 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("6 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
Range("A1").Select
End Sub

-----------------Rješenje VBA - Opcija 2 --------------------------

Postoji još opcija ili varijanti rješenja koristeći VBA makronaredbe. Evo još jedan primjer

U Module2 kopirajte ovu VBA makronaredbu ispod. Ova VBA makronaredba kopira redove koji ispunjavaju uvjet iz stupca A na radnom listu "master". Ovu VBA makronaredbu ispod, umnožite šest puta jednu ispod druge i izmijenite broj uvjeta. Obratite pažnju na naziv izvornog i destinacijskog radnog lista (bold slova).

Sub CopyRowsWithCondition1()
'Copy Rows Data for Condition in A column
Dim i, LastRow
LastRow = Sheets("master").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("1 ID").Range("A2:E100").ClearContents
For i = 2 To LastRow
If Sheets("master").Cells(i, "A").Value = 1 Then 'condtion number 1
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("1 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

U Module2 postavite i ovu VBA makronaredbu ispod. Ova VBA procedura poziva sve pojedinačne procedure za kopiranje redaka koji sadrže uvjet u 'A' stupcu. Klikom na VBA button Excel će automatski kopirati sve redove koje sadrže zadani uvjet na specifičan radni list

Sub CopyRowsWithConditionAll2()
'calling all certain procedures in Module2
'Sheet "master" Button2

Call CopyRowsWithCondition1
Call CopyRowsWithCondition2
Call CopyRowsWithCondition3
Call CopyRowsWithCondition4
Call CopyRowsWithCondition5
Call CopyRowsWithCondition6
Range("A1").Select
End Sub

sponzor


Da se razumijemo, ova iznad pojedinačna VBA procedura može se iskoristiti za svaki destinacijski radni list pojedinačno pomoću VBA gumba, tako da je pozivamo direktno sa destinacijskog radnog lista. I na kraju, naravno, experti u programiranju VBA za Excel ovo bi riješili na jednostavniji ili drugačiji način, ali od mene je dovoljno i ovo.

Ja se nadam da sam vam uspio objasniti kako možemo na više načina riješiti kopiranje svih redova koji sadrže određeni uvjet.




   

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