Ok, så i mine tidlige dager med dataanalyse måtte jeg automatisk hente de unike elementene i excel fra en liste, i stedet for å fjerne dupliserte hver gang. Og jeg fant ut en måte å gjøre det på. Etter det var excel -dashbordet mitt enda mer dynamisk enn før. Så la oss se hvordan vi kan gjøre det …
For å trekke ut en liste over unike verdier fra en liste, bruker vi INDEX, MATCH og COUNTIF. Jeg vil også bruke IFERROR, bare for å ha rene resultater, det er valgfritt.
Og ja, det vil være en matriseformel … Så la oss få det gjort …
Generisk formel for å trekke ut unike verdier i Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (expanding_output_range, ref_list), 0))}
Ref_list: Listen du vil trekke ut unike verdier fra
Expanding_output_range: Nå er dette veldig viktig. Dette er området der du vil se den utpakkede listen. Dette området må ha en tydelig overskrift som ikke er en del av listen, og formelen din vil være under overskriften (hvis overskriften er i E1 enn formelen vil være i E2).
Nå betyr utvidelse at når du drar ned formelen, bør den utvides over utgangsområdet. For å gjøre dette må du angi overskriften som $ E $ 1: E1 (Overskriften min er i E1). Når jeg drar det ned, vil det utvide seg. I E2 blir det $ E $ 1: E1. I E3 blir det $ E $ 1: E2. I E2 blir det $ E $ 1: E3 og så videre.
La oss nå se et eksempel. Det vil gjøre det klart.
Ekstrahering av unike verdier Excel -eksempel
Så her har jeg denne kundelisten i kolonnen EN innen rekkevidde A2: A16. Nå i kolonne E, vil jeg bare få unike verdier fra kunder. Nå kan dette området A2: A16 også øke, så jeg vil at formelen min skal hente et nytt kundenavn fra listen, når listen øker.
Ok, nå for å hente unike verdier fra kolonne A, skriv denne formelen inn Celle E2, og slo CTRL+SKIFT+ENTER for å gjøre det til en matriseformel.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Jeg forventer at listen vil utvide seg og kan ha nye unike verdier som jeg vil trekke ut. Det er derfor jeg har forlatt det åpent fra bunnen av ikke absolutt referanse til A16. Den lar den utvides når du kopierer formelen nedenfor.
Så vi vet hvordan INDEX og MATCH -funksjonen fungerer. Hoveddelen her er:
TELLING ($ E $ 1: E1, $ A $ 2: A16): Denne formelen vil returnere en rekke med 1s og 0s. Når som helst en verdi i området $ E $ 1: E1 finnes i kriterielisten $ A $ 2: A16, verdien konverteres til 1 på sin posisjon i området $ A $ 2: A16.
Ved å bruke MATCH -funksjonen leter vi etter verdier 0. Match vil returnere posisjonen til de første 0 funnet i array returnert av COUNTIF -funksjonen. Enn INDEX vil se nærmere på A $ 2: A16 for å returnere verdien funnet ved indeks returnert av MATCH -funksjonen.
Det er kanskje litt vanskelig å forstå, men det fungerer. 0 i enden av listen indikerer at det ikke er flere unike verdier. Hvis du ikke ser at 0 til slutt, bør du kopiere formelen i cellene nedenfor.
Nå for å unngå #NA i kan du bruke IFERROR -funksjonen til excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Så ja, du kan bruke denne formelen til å få unike verdier fra en liste. I Excel 2019 med abonnement på Office 365 tilbyr Microsoft en funksjon som heter UNIK. Det tar ganske enkelt et område som argument og returnerer en rekke unike verdier. Det er ikke tilgjengelig i Microsoft Excel 2016 engangskjøp.
Last ned fil:
Excel -formel for å trekke ut unike verdier fra en listeExcel -formel for å trekke ut unike verdier fra en liste
Hvordan telle unike verdier i Excel
Populære artikler:
50 Excel -snarveier for å øke produktiviteten
Slik bruker du VLOOKUP -funksjonen i Excel
Slik bruker du COUNTIF -funksjonen i Excel
Slik bruker du SUMIF -funksjonen i Excel