U redu, pa sam u prvim danima analize podataka dobio potrebu za automatskim preuzimanjem jedinstvenih stavki u excelu sa popisa, umjesto da svaki put uklanjam duplicirane. I smislio sam način kako to učiniti. Nakon toga moja nadzorna ploča u Excelu bila je još dinamičnija nego prije. Pa da vidimo kako to možemo učiniti …
Za izdvajanje popisa jedinstvenih vrijednosti s popisa koristit ćemo INDEX, MATCH i COUNTIF. Također ću koristiti IFERROR, samo da bih imao čiste rezultate, nije obavezno.
I da, to će biti formula niza … Pa hajde da to učinimo …
Opća formula za izdvajanje jedinstvenih vrijednosti u Excelu
{= INDEX (ref_list, MATCH (0, COUNTIF (expand_output_range, ref_list), 0))}
Ref_list: Popis iz kojeg želite izdvojiti jedinstvene vrijednosti
Expanding_output_range: Sada je ovo vrlo važno. Ovo je raspon u kojem želite vidjeti svoj izdvojeni popis. Ovaj raspon mora imati poseban naslov koji nije dio popisa, a vaša formula bit će ispod naslova (ako je naslov u E1, tada će formula biti u E2).
Sada proširenje znači da, kad povučete formulu prema dolje, ona bi se trebala proširiti preko izlaznog raspona. Da biste to učinili, morate dati referencu naslova kao $ E $ 1: E1 (Moj je naslov u E1). Kad ga povučem prema dolje, proširit će se. U E2 bit će $ E $ 1: E1. U E3 bit će $ E $ 1: E2. U E2 bit će $ E $ 1: E3 i tako dalje.
Pogledajmo sada primjer. Bit će jasno.
Izdvajanje jedinstvenih vrijednosti Primjer Excela
Dakle, ovdje imam ovaj popis kupaca u stupcu A u dometu A2: A16. Sada u stupcu E želim dobiti jedinstvene vrijednosti samo od kupaca. Sada se i ovaj raspon A2: A16 može povećati, pa želim da moja formula dohvati svako novo ime kupca s popisa, kad god se popis poveća.
U redu, sada za dohvaćanje jedinstvenih vrijednosti iz stupca A upišite ovu formulu Ćelija E2, i pogodio CTRL+SHIFT+ENTER kako bi to bila formula niza.
{= INDEKS ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
2 USD: A16: Očekujem da će se taj popis proširiti i možda će imati nove jedinstvene vrijednosti koje bih volio izdvojiti. Zato sam ga ostavio otvorenim odozdo, a ne apsolutnom referencom A16. Omogućit će mu proširenje kad god kopirate formulu ispod.
Znamo kako funkcionira funkcija INDEX i MATCH. Glavni dio ovdje je:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Ova formula će vratiti niz od 1s i 0s. Kad god je vrijednost u rasponu $ E $ 1: E1 nalazi se na popisu kriterija $ A $ 2: A16, vrijednost se pretvara u 1 na svom položaju u rasponu $ A $ 2: A16.
Sada pomoću funkcije MATCH tražimo vrijednosti 0. Podudaranje će vratiti položaj prvih 0 pronađenih u nizu koje vraća funkcija COUNTIF. INDEX će to ispitati 2 USD: A16 da vrati vrijednost pronađenu u indeksu vraćenom funkcijom MATCH.
Možda je to malo teško shvatiti, ali radi. 0 na kraju popisa označava da nema više jedinstvenih vrijednosti. Ako na kraju ne vidite 0, trebali biste kopirati formulu u ćelije ispod.
Sada za izbjegavanje #NA u možete koristiti IFERROR funkciju programa excel.
{= IFERROR (INDEKS ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}}
Dakle, da, možete koristiti ovu formulu da biste dobili jedinstvene vrijednosti s popisa. U Excelu 2019. s pretplatom na Office 365, Microsoft nudi funkciju pod nazivom UNIQUE. Jednostavno uzima raspon kao argument i vraća niz jedinstvenih vrijednosti. Nije dostupno za jednokratnu kupnju u programu Microsoft Excel 2016.
Preuzmi datoteku:
Excel formula za izdvajanje jedinstvenih vrijednosti s popisaExcel formula za izdvajanje jedinstvenih vrijednosti s popisa
Kako brojati jedinstvene vrijednosti u Excelu
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti
Kako koristiti funkciju VLOOKUP u Excelu
Kako koristiti funkciju COUNTIF u Excelu
Kako koristiti funkciju SUMIF u Excelu