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 - Jedinstveni ili jedinstveni različiti podaci u Excelu, Unique vs Distinct, SUM Unique values, SUM distinct values, Count Unique valiues, Count Distinct values, Zbrajanje jedinstvenih vrijednosti koje se ne ponavljaju, Advanced Filter u službi kopiranja jedinstvenih vrijednosti

UNIQUE vs UNIQUE DISTINCT u Excelu




sponzor



Razlika između "jedinstvenih" podataka i "jedinstvenih različitih" podataka

Čitajući razne forume uočavam sukobljavanje oko definicije "Unique" i "Unique Distinct" vrijednosti ili podataka, to me potaklo da napišem ovaj tutorijal. Čitajući razne forume i tutorijale uočio sam da je definicija ' Unique' i 'Unique Distinct' vrijednosti ili podataka dosta različita od osobe do osobe koja je učestvovala u nekom topicu na nekom forumu, blogu ili web stranici. Ja ću u ovom tutorijalu dati svoje viđenje definicije a vi odlučite kome ćete vjerovati.

Kroz kontinuirani rad u Excelu i čitajući razne forume na internetu, uočio sam da mnogi korisnici Excela pa i ja sam, koriste naziv Unikati ili Jedinstveni (Unique) za podatke ili vrijednsti koje treba izdvojiti u neku listu i u stvarnosti koriste jedan naziv (Unique) za dvije vrste podataka, bilo "jedinstveni" ili "jedinstveni različiti" podaci ili vrijednosti u nekom rasponu ćelija. O "UNIQUE" podacima već sam pisao na tutorijalu Unique data in Excel. U ovom tutorijalu koji čitate ja ću pokušati pokazati razliku tj. 'Unique vs Unique Distinct' data. Neki će se pitati, zašto i ja sam koristim taj izraz "Unique". Zato što sam i ja kao početnik ušao u to korištenje Excela sa ne razumijevanjem. Ovaj tutorijal je zbirka formula za UNIQUE podatke koja je prikupljena sa interneta a sve zasluge pripadaju njihovim autorima.

Primjer:

U stupcu 'A' nalaze se neki podaci (text/vrijednosti). Ti podaci ili vrijednosti mogu se pojaviti kako slijedi

  • "UNIQUE" vrijednosti/podaci su one koje se pojavljuju samo jednom. To znači da svi podaci koji se ponavljaju i imaju duplikate nisu jedinstveni.
  • "DISTINCT" vrijednosti/podaci su one koje se pojavljuju barem jednom u skupu podataka. Ako se podatak pojavi dva ili tri puta, to se još uvijek računa kao jedan poseban podatak. To se može postići uklanjanjem dupliciranih vrijednosti/podataka i zadržavanjem svih različitih.

Postoje dvije vrste podataka koje možemo izdvojiti iz stupca 'A'. To su 'Unique' i 'Unique Distinct' podaci ili vrijednosti.

Kako prikazati koje su 'Unique' a koje su 'Unique Distinct' vrijednosti?

Jednostavno. U Excelu imate ugrađenu opciju da vam Excel automatski izdvoji Unique Distinct podatke ili vrijednosti, to je opcija 'Advanced Filter' (vidi sliku ispod).

--------------------------------------------------------------------------------------------------------------------------------------------------------

Advanced Filter u službi kopiranja jedinstvenih vrijednosti koje se mogu ponavljati

Dakle, idemo iskoristiti Excelovu opciju izdvajanja Unique Distinct vrijednosti pomoću Advanced Filtera. Kopiranje jedinstvenih vrijednosti iz stupca 'A' možemo učiniti koristeći napredni filtar na slijedeći način (slika 1).

  1. Selektirajte vrijednosti u stupcu 'A'
  2. Na ribonu na kartici DATA kliknite na Advanced (grupa Sort&Filter)
  3. Na skočnom dijalog prozoru "Advanced Filter" izaberite opciju radio button "Copy to another location" ,
  4. Unesite adresu prve ćeliju u koju će se izdvojiti ili kopirati jedinstvene vrijednosti,
  5. Obavezno uključite opciju "Unique records only", završite klikom na OK button
  6. Sada imate u stupcu 'C' imate vrijednosti; 100, 10, 50, 20, 325, 700, 40, 60 (uočite da ukupno ima 8 vrijednosti)

Dakle, Excel je kopirao svaku vrijednost koja se pojavljuje najmanje jedan puta, duplikate je ignorirao. Ako ćemo vjerovati Microsoft programerima koji su programirali Excel tada za UNIQUE vrijednosti ili podatke tada vrijedi slijedeća definicija.

UNIQUE DISTINCT ili unikatni podaci koji se mogu ponavljati, su svi podaci iz raspona ćelija koji se pojavljuju najmanje jedan put bez obzira da li se dupliciraju ili ne.

Slika 1.

Izdvajanje jedinstvenih vrijednosti koristeći Excel Advanced filtar

-----------------------------------------------------------------------------------------------------------------------------------------------------------

UNIQUE DISTINCT podaci u rasponu ćelija (All Unique DISTINCT data)

Popis Unique Distinct vrijednosti u rasponu ćelija je kopiranje svih vrijednosti (podataka) koji se pojavljuju najmanje jedan puta i mogu se ponavljati. Na slici ispod uočite načine pomoću kojih možemo kopirati UNIQUE Distinct vrijednosti iz nekog raspona ćelija. Kao što možete vidjeti vrijednosti se nalaze u stupcu 'A'. U ostalim stupcima nalaze se formule pomoću kojih možemo kopirati Unique Distinct vrijednosti. Uočite da neke formule automatski sortiraju vrijednosti uzlazno A-Z.

Slika 2.

Kopiranje jedinstvenih vrijednosti iz stupca u Excelu

Kopiranje 'Unique Distinct' vrijednosti koje se mogu ponavljati

Formule u ćelijama na slici 2. iznad su slijedeće:

Formula u ćeliji C2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter. Formula sortira vrijednosti uzlazno.

=INDEX($A$2:$A$12;MATCH(0;COUNTIF($A$2:$A$12;"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12;"="&C$1:C1));0))

Formula u ćeliji E2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter. Formula sortira vrijednosti uzlazno.

=SMALL(IF(FREQUENCY($A$2:$A$12;$A$2:$A$12);$A$2:$A$12);ROWS($1:1))

Formula u ćeliji G2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.

=INDEX($A$2:$A$12;MATCH(0;FREQUENCY(IF(EXACT($A$2:$A$12;TRANSPOSE($G$1:G1));MATCH(ROW($A$2:$A$12);
ROW($A$2:$A$12));"");MATCH(ROW($A$2:$A$12);ROW($A$2:$A$12)));0))

Formula u ćeliji I2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter. Ova formula radi dobro u slučajevima kada između vrijednosti ima praznih ćelija.

=INDEX($A$2:$A$12;MATCH(1;INDEX((COUNTIF($I$1:I1;$A$2:$A$12)=0)*($A$2:$A$12<>"");0);0))

Formula u ćeliji K2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter. Ova formula radi dobro u slučajevima kada između vrijednosti ima praznih ćelija.

=INDEX($A$2:$A$12;MATCH(0;IF(ISBLANK($A$2:$A$12);1;COUNTIF($K$1:K1;$A$2:$A$12));0))

Formula u ćeliji M2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.

=INDEX($A$2:$A$12;MATCH(0;COUNTIF($M$1:M1;$A$2:$A$12);0))

--------------------------------------------------------------------------------------------------------------------------------------------------------

Copy Unique/Unique Distinct values from range

Ovaj tekst odnosi se na Slika 3. ispod. Ako imate potrebu kreirati popis samo "Jedinstvenih vrijednosti" iz nekog raspona ćelija ili više stupaca tada iskoristite formulu ispod. Formula kopirana iz www.extendoffice.com

'E' stupac - UNIQUE DISTINCT

Formula za tekst

=INDIRECT(TEXT(MIN(IF(($A$2:$C$6<>"")*(COUNTIF($E$1:E1;$A$2:$C$6)=0);ROW($2:$6)*100+COLUMN($A:$C);
7^8));"R0C00");)&""

Formula za vrijednosti

=INDIRECT(TEXT(MIN(IF(($A$2:$C$6<>"")*(COUNTIF($E$1:E1;$A$2:$C$6)=0);ROW($2:$6)*100+COLUMN($A:$C);
7^8));"R0C00");)

ili ARRAY formula ispod

=IFERROR(INDEX($A$1:$C$6;SMALL(IF(($A$2:$C$6<>"")*ISERROR(MATCH($A$2:$C$6;$E$1:$E1;0));ROW($A$2:$C$6));1)
;MOD(SMALL(IF(($A$2:$C$6<>"")*(ISERROR(MATCH($A$2:$C$6;$E$1:$E1;0)));ROW($A$2:$C$6)+COLUMN($A$2:$C$6)/100);1)
*100;100));"")

'G' stupac - UNIQUE only

Na slici ispod uočite razliku u prikazanim rezultatima u 'E' i 'G' stupcu. U 'G' stupcu su prikazani samo jedinstvene vrijednosti koje se ne ponavljaju. Dakle ARRAY formula u 'G2' ćeliji na slici 3 je slijedeća:
=IFERROR(INDEX($A$2:$C$6;MIN(IF((COUNTIF($A$2:$C$6;$A$2:$C$6)=1)*(COUNTIF(G$1:$G1;$A$2:$C$6)<>1)
;ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1));MATCH(FALSE;COUNTIF($G$1:G1;INDEX($A$2:$C$6;
MIN(IF((COUNTIF($A$2:$C$6;$A$2:$C$6)=1) *(COUNTIF(G$1:$G1;$A$2:$C$6)<>1);ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1));;1))+COUNTIF($A$2:$C$6;INDEX($A$2:$C$6;MIN(IF((COUNTIF($A$2:$C$6;$A$2:$C$6)
=1)*(COUNTIF(G$1:$G1;$A$2:$C$6)<>1);ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1));;1))<>1;0);1);"")

Slika 3.

Kopiranje jedinstvenih vrijednosti iz višestrukih stupaca

----------------------------------------------------------------------------------------------------------------------------------------------------------

Jedinstveni podaci u rasponu ćelija (Unique data only)

UNIQUE (jedinstveni) podaci, su oni podaci koji se pojavljuju u rasponu ćelija ali se ne ponavljaju. Kada se izdvajaju tada kreiramo popis svih vrijednosti koje se pojavljuju u rasponu ćelija samo jedan puta i nemaju duplikata.

Uočite na slici izdvojene ili kopirane vrijednosti iz stupca 'A'. Za razliku od podataka na slici 2, ovdje uočite da imamo šest kopiranih vrijednosti. Dakle izdvojili smo sve vrijednosti koje se pojavljuju samo jedanput i nemaju duplikata.

Slika 4.

Display Unique and Unique Distinct values from Column Cells in Excel

Formule u ćelijama na slici iznad su slijedeće:

Formula u ćeliji C2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.

=IF(COUNT(IF(COUNTIF($A$2:$A$12;$A$2:$A$12)=1;ROW($A$2:$A$12)-ROW($A$2)+1;""))<ROW()-1;"";INDEX($A$2:$A$12;SMALL(IF(COUNTIF($A$2:$A$12;$A$2:$A$12)=1;ROW($A$2:$A$12)-ROW($A$2)+1;"");ROW()-1)))

Formula u ćeliji E2 je slijedeća: Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.

=INDEX($A$2:$A$12;MATCH(0;INDEX(COUNTIF($E$1:E1;$A$2:$A$12)+(COUNTIF($A$2:$A$12;$A$2:$A$12)<>1);0;0);0))

SUM jedinstvenih vrijednosti.
U ćeliji 'G2' nalazi se ARRAY formula koja zbraja samo on e podatke koji se pojavlju jednom i nemaju duplikata.
=SUM(IF(COUNTIF(A2:A12;A2:A12)=1;A2:A12;""))

COUNT jedinstvenih vrijednosti
U ćeliji 'G7' nalazi se ARRAY formula koja broji koliko puta se pojavila jedinstvena vrijednost (a koja se ne ponavlja)
=COUNT(IF(COUNTIF(A2:A12;A2:A12)=1;ROW(A2:A12)-ROW(A2)+1;""))

Ako koristite Excel 2013 također pogledajte kako izbrojati Unique vrijednosti koristeći PowerPivot na tutorijalu http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/

----------------------------------------------------------------------------------------------------------------------------------------------------------

Brojanje jedinstvenih vrijednosti (Count Unique values)

Slijede formule (slika 2) koje prebrojavaju Unique podatke/vrijednosti

(Formula ispod broji numeričke vrijednosti, Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.)
=SUM(IF(FREQUENCY(A2:A12;A2:A12)>0;1))

(Ako postoje negativni brojevi i nula (0) formula ispod ih neće brojati)
=SUM(--(FREQUENCY(A1:A12;A1:A12)>0))

=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12;A2:A12&""))

(Formula ispod neće funkcionirati dobro ako ima praznih ćelija između vrijednosti)
=SUMPRODUCT(1/COUNTIF(A2:A12;A2:A12))

(Formula ispod je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.)
=SUM(--(FREQUENCY(IF(A2:A12<>"";MATCH("~"&A2:A12;A1:A12&"";0));ROW(A2:A12)-ROW(A1)+1)>0))

(Formula ispod broji alfanumeričke podatke i ignorira prazne ćelije. Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.)
=SUM(IF(FREQUENCY(IF(LEN(A2:A12)>0;MATCH(A2:A12;A2:A12;0);"");IF(LEN(A2:A12)>0;MATCH(A2:A12;A2:A12;0);""))>0;1))

(Formula ispod neće funkcionirati dobro ako ima praznih ćelija između vrijednosti. Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.)
=SUM(IF(FREQUENCY(MATCH(A2:A12;A2:A12;0);ROW(A2:A12)-ROW(A1)+1);1))

(Formula ispod neće funkcionirati dobro ako ima praznih ćelija između vrijednosti. Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.)
=SUM(1/COUNTIF(A2:A12;A2:A12))

Zbrajanje jedinstvenih vrijednosti (SUM Unique Distinct values)

Formula u ćeliji O2 (slika 2) zbraja sve jedinstvene vrijednosti. Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter. Formula neće funkcionirati dobro ako ima praznih ćelija između vrijednosti.

=SUM(IF(IF(MATCH(A2:A12;A2:A12;0)=(ROW(A2:A12)-ROW(A2)+1);(ROW(A2:A12)-ROW(A2)+1);0)<>0;A2:A12;""))

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Count Unique values

Formula u ćeliji G7 (slika 4) prebrojava sve jedinstvene (Unique). Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.

=COUNT(IF(COUNTIF(A2:A12;A2:A12)=1;ROW(A2:A12)-ROW(A2)+1;""))

SUM Unique values

Formula u ćeliji G2 (slika 4) zbraja sve jedinstvene (UNIQUE). Ovo je ARRAY formula koju trebate završiti sa Ctrl+Shift+Enter.

=SUM(IF(COUNTIF(A2:A12;A2:A12)=1;A2:A12;""))

REZIME:

UNIQUE DISTINCT data= pojavljuju se jednom ili više puta ali kao rezultat vraća se samo jedan podatak
UNIQUE data= pojavljuju se samo jednom i nemaju duplikata

Također pogledajte tutorijal www.cpearson.com/excel/distinctvalues.aspx

Kopiranje Unique Distinct vrijednosti koristeći VBA makronaredbu (https://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html)

Sub UniqueListFromColumn()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'source column

ActiveSheet.Range("A2:A" & lastrow).AdvancedFilter _ 'source range
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("B2"), _ 'destination first cell
UNIQUE:=True

End Sub

sponzor


 


Primjer kako odabrati formulu za brojanje Unique vrijednosti

Count Unique by formula
image source idea (www.excelhowto.com)

   

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