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 - kopiranje jedinstvenih (unique) vrijednosti iz jedne kolone u drugu kolonu ili drugi Sheet

Kako kopirati jedinstvene (unikatne) podatke iz stupca
(How to copy unique data from column)




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





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