U ovom tutorijalu prikazat ću nekoliko načina
na koje možemo izdvojiti jedinstvene (UNIQUE) podatke iz
nekog stupca ili kako usporediti dva stupca i izvući
(kopirati) samo one podatke (vrijednosti) koje su
jedinstvene. Ovaj primjer može se iskoristiti također ako
želite izdvojiti sve unikatne e-mail adrese iz neke tablice
ili neke financijske podatke slično.
Kako kopirati Unique data u drugi stupac
U prvom primjeru imamo jednostavno kopiranje
jedinstvenih (unique) podataka iz stupca B u stupac D. U
Excelu 2003 koristite Data => Filter => Advanced Filter, U
Excelu 2007 na tabu Data u grupi Sort & Filter kliknite na
Advanced button. Selektirajte izvorne podatke, postavite
ciljnu ćeliju (stupac) i uključite opcije prema slici.
DRUGI PRIMJER
pomoću VBA makronaredbe
Za sliku iznad koristite VBA Macro koji
kopira unikatne podatke iz stupca B u stupac F.
Macro umetnuti na na kojem radimo operaciju (Sheet1 u ovom
slučaju). Makornaredbu povežite sa button
Excel 2007 ili
Excel 2003
Sub KopirajUnique()
'KOPIRANJE B NA F -
SAMO UNIKATI
Application.ScreenUpdating = False
Dim aa As Long
aa = Sheets(1).Range("B1").End(xlDown).Row
Range("B1:B" & aa).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"),
Unique:=True
Application.CutCopyMode = False
Range("B1").Select
End Sub
Sub Ponisti()
'ponistava gornju proceduru i
brise podatke
Dim bb As Long
bb = Sheets(1).Range("F1").End(xlDown).Row
Range("F2:F" & bb).ClearContents
End Sub
Kako izvršiti usporedbu podataka
dva stupca (kolone) i izdvojiti UNIQUE (unikatne) podatke
U prvom slučaju imamo dva stupca sa
tekstualnim podacima koja smo
imenovali nekim
nazivom. U ovom sluaju stupac A tj. ćelije A2:A11 imenovane
su nazivom "List1" a raspon podataka B2:B11 nazivom "List2".
(naslovi stupaca se ne uzimaju u obzir i ovdje su neki
slučajno isti)
Raspon podataka C2:C11 imenovan je nazivom "Unique1" a
raspon podataka D2:D11 nazivom "Comon" i raspon podataka
E2:E11 nazivom "In1Not2"
U stupcu "C" želimo imati jedinstvene (unoque)
podatke iz oba stupca A i B. Dakle želimo imati sve nazive a
da se ne ponavljaju. Formula u C2 izgleda ovako (samo je
kopirajte prema dolje)
=IF(COUNTIF(INDIRECT(ADDRESS(ROW(List1);COLUMN(List1);1)&":"&ADDRESS(ROW(OFFSET(List1;ROW()-ROW(Unique1);0;1;1));COLUMN(List1);4));INDIRECT(ADDRESS(ROW(OFFSET(List1;ROW()-ROW(Unique1);0;1;1));COLUMN(List1))))=1;INDIRECT(ADDRESS(ROW(OFFSET(List1;ROW()-ROW(Unique1);0;1;1));COLUMN(List1)));"")
U stupcu D želimo znati koji podaci nam se
nalaze u jednom i drugom stupcu. Formula u D2 izgleda ovako
(samo je kopirajte prema dolje)
=IF(NOT(ISERROR(MATCH(List1;List2;0)));List1;"")
ovo je
ARRAY formula koju
trebate završiti sa Ctrl+Shift+Enter
U stupcu E želimo znati koji podaci nam se
nalaze u stupcu A a ne nalaze se u stupcu B. Formula u
E2 izgleda ovako (samo je kopirajte prema dolje)
=IF(ISERROR(MATCH(List1;List2;0));List1;"")
ovo je
ARRAY formula koju
trebate završiti sa Ctrl+Shift+Enter
Kako prebrojati jedinstvene (unique) podatke
u stupcu
Slijedeći primjer je kako izbrojati koliko je
jedinstvenih podtaka (Unique data) u određenom rasponu
podataka na drugom Sheetu. Uzmimo za primjer da imamo Sheet
"Imena" na kojem se nalaze imena djelatnika neke firme i ona
se dupliraju (dupliciraju) a mi želimo saznati koliko je
osoba u stvarnosti na tom Sheetu.
Da bi dobili podatak koliko je UNIQUE
podataka (tj. da izbrojimo jedinstvene podatke) možemo
koristiti makronaredbu ispod. Refresh (osvježavanje) vršite
sa F9.
Ovu makronaredbu kopirajte u MODULE vaše Workbook
Function
CountUnique(ByVal
MyRange As Range) As Integer
Dim Cell As Range
Dim J As Integer
Dim iNumCells As Integer
Dim iUVals As Integer
Dim sUCells() As String
iNumCells = MyRange.Count
ReDim sUCells(iNumCells) As String String
iUVals = 0
For Each Cell In MyRange
If Cell.Text > "" Then
For J = 1 To iUVals
If sUCells(J) = Cell.Text Then
Exit For
End If
Next J
If J > iUVals Thenls Then
iUVals = iUVals + 1
sUCells(iUVals) = Cell.Text
End If
End If
Next Cell
CountUnique = iUVals
End Function
U ćeliju u kojoj želimo dobiti rezultat broja
jedinstvenih upišimo formulu
=CountUnique(Imena)
kao rezultat pojavit će nam se broj imena a
da se ne ponavljaju.
=>>>>>>>>>>>>>>>> Isto tako
možemo iskoristiti ovu makronaredbu ispod
Function
CountUniqueValues(InputRange
As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value)
' add the
unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function
U ovom slučaju formula nam je slijedeća:
=CountUniqueValues(Imena)
|
|
Formule za izračune UNIQUE (Unikata) podataka
iz nekog raspona
U slijedećem primjeru imamo nekoliko opcija
(formula) vezanih za UNIQUE data (jedinstvene podatke).
Uočite da je na primjeru izvršeno imenovanje podataka (u
stupcima A, B, C i D i to u rasponu od 2 do 18 reda) npr:
A2:A18.
Svaki stupac imenovan je nazivom po rednom broju (prvi,
drugi, treci, cetvrti)
Ove nazive raspona podataka određenog stupca
koristimo u formulama
Uočite sa slike opcije koje su date kao
primjer, a formule su slijedeće:
U ćeliji F3: Koliko je jedinstvenih u stupcu
A (samo brojevi)
=SUM(IF(FREQUENCY(prvi;prvi)>0;1))
U ćeliji F6: koliko je jedinstvenih u stupcu
B (text)
=SUM(IF(FREQUENCY(MATCH(drugi;drugi;0);MATCH(drugi;drugi;0))>0;1))
U ćeliji F9: Koliko je jedinstvenih u stupcu
C (miješano brojevi i text)
=SUM(IF(FREQUENCY(MATCH(treci;treci;0);MATCH(treci;treci;0))>0;1))
U ćeliji F12: Koliko je jedinstvenih u stupcu
D uključujući i prazne ćelije
ovo je
ARRAY formula koju
trebate završiti sa Ctrl+Shift+Enter
=SUM(IF(FREQUENCY(IF(LEN(cetvrti)>0;MATCH(cetvrti;cetvrti;0);"");IF(LEN(cetvrti)>0;MATCH(cetvrti;cetvrti;0);""))>0;1))
U ćeliji F16: Koji je najčešće oponavljani
tekst u stupcu B
ovo je
ARRAY formula koju
trebate završiti sa Ctrl+Shift+Enter
=INDEX(drugi;MATCH(MAX(COUNTIF(drugi;drugi));COUNTIF(drugi;drugi);0))
U ćeliji F19: Koliko puta se pojavljuje
najčešće ponavljani tekst u stupcu B
ovo je
ARRAY formula koju
trebate završiti sa Ctrl+Shift+Enter
=COUNTIF(drugi;INDEX(drugi;MATCH(MAX(COUNTIF(drugi;drugi));COUNTIF(drugi;drugi);0)))
Ovo je jedan klasičan i neobičan primjer gdje
je prva ćelija bez formule. Cilj je dobiti imena svih
učenika u jednom razredu bez obzira što se ponavljaju. Dakle
tražimo unikate (unique) u stupcu B
Prva ćelija C2 je bez formule a u ćeliji C3
ispod nalazi se formula
=INDEX($B$2:$B$10;MATCH(0;COUNTIF($C$2:C2;$B$2:$B$10
);0))
koju kopiramo prema dolje. Ovo je
ARRAY formula koju
trebate završiti sa Ctrl+Shift+Enter
=>>>>>>>>>>>>>>> isto tako možemo
odrediti koje sve brojeve imamo u stupcu bez obzira što se
ponavljaju.
Formula je kombinacija funkcija IF i COUNTIF
=IF(COUNTIF($A$2:A2;A2)>1;"";"UNIKAT")
Kako kopirati UNIKATE (Unique) sa dva Sheeta
u jedan Sheet
U slijedećem primjeru pogledajmo kako možemo
usporediti dva Sheeta i izdvojiti samo UNIKATNE podatke.
Konkretno imamo na Sheetu "Svibanj" imena osoba koja su
primila nekakvu novčanu naknadu u dotičnom mjesecu. U Sheetu
"Lipanj" također imamo isto za dotični mjesec. želimo znati
koje su to osobe koje su uopće primile nekakvu naknadu za
oba mjeseca a da ne moramo pregledavati svaki Sheet posebno
i negdje sa strane bilježiti imena.
Ovdje imamo dvije makronaredbe koje rade istu
radnju ali u različitom poretku imena.
Prva makronaredba u Module dotične WorkBook.
Ova makronaredba rezultate prikazuje na Sheetu "Unikati" u
stupcu A počevši od ćelije A1
Sub KopirajUnique1()
sq = Filter([transpose(IF(countif(Offset(Svibanj!$A$1,,,ROW(Svibanj!A1:A200)),Svibanj!A1:A200)=1,Svibanj!A1:A200,"#"))],
"#", False)
sn = Filter([transpose(IF(countif(Offset(Lipanj!$A$1,,,ROW(Lipanj!A1:A200)),Lipanj!A1:A200)=1,Lipanj!A1:A200,"#"))],
"#", False)
For j = 0 To UBound(sn)
If UBound(Filter(sq, sn(j))) > -1 Then sn(j) = "#"
Next
sq = Split(Join(sq, "|") & "|" & Join(Filter(sn, "#",
False), "|"), "|")
Sheets("Unikati").Cells(1,
1).Resize(UBound(sq)
+ 1) = Application.Transpose(sq)
End Sub
Druga makronaredba nalazi se u istom Module
dotične WorkBook. Ova makronaredba rezultate prikazuje na
Sheetu "Unikati" u stupcu B počevši od ćelije B1
Sub KopirajUnique2()
For Each sh In Sheets(Array("Lipanj", "Svibanj"))
For Each cl In sh.Columns(1).SpecialCells(2)
If InStr(c01, cl.Value) = 0 Then c01 = c01 & "|"
& cl.Value
Next
Next
Sheets("Unikati").Cells(1,
2).Resize(UBound(Split(c01,
"|"))) = Application.Transpose(Split(Mid(c01, 2), "|"))
End Sub
Po želji pokrećemo određenu makronaredbu sa
ALT+F8 => Select => Run
Automatsko trenutno kopiranje UNIKATA (unique
text) sa drugog Sheeta
U ovom primjeru pokazat ću kako možemo
trenutno AUTOMATSKI kopirati UNIKATE (UNIQUE) podatke
sa drugog Sheeta. Poanta je u tome da dok popunjavamo neki
Sheet npr: Sheet "B" automatski nam se evidentiraju trenutne
promjene na Sheetu "A". Dakle nema potrebe pokretati
makronaredbu.
Dok mi pišemo na Sheetu B automatski s evrši promjena na
Sheetu A
Ovdje koristimo slijedeću makronaredbu
Function UNIQUE(InputRange As Range, ItemNo As Long)
As Variant
Dim cl As Range, cUnique As New Collection, cValue
As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UNIQUE = ""
If ItemNo = 0 Then = 0 Then
UNIQUE = cUnique.Count
Else
If ItemNo <= cUnique.Count Then
UNIQUE = cUnique(ItemNo)
End If
End If
On Error GoTo 0
|
|
Ovu makronaredbu kopiramo u MODULE dotične
workbook
Na Sheetu "A" gdje trebamo dobiti rezultate
(automatsku promjenu) upišemo formule redom kako slijedi
(samo se mijenjaju brojevi). Ovdje trebamo voditi računa da
napišemo onoliko formula koliko smatramo da imamo UNIKATA (Unique
podataka)
=UNIQUE(imena;1)
=UNIQUE(imena;2)
=UNIQUE(imena;3)
=UNIQUE(imena;4)
itd …
Na jednom mjestu popis svih tema vezanih
za kopiranje (copy) u Excelu:
Tutorijali vezani za radnje
kopiranja u Excelu |