Ok, så i mine tidlige dage med dataanalyse fik jeg et behov for automatisk at få de unikke varer i excel fra en liste, frem for at fjerne kopieret hver gang. Og jeg fandt ud af en måde at gøre det på. Herefter var mit excel -dashboard endnu mere dynamisk end før. Så lad os se, hvordan vi kan gøre det …
For at udtrække en liste over unikke værdier fra en liste bruger vi INDEX, MATCH og COUNTIF. Jeg vil også bruge IFERROR, bare for at få rene resultater, det er valgfrit.
Og ja, det vil være en matrixformel … Så lad os få det gjort …
Generisk formel til at udtrække unikke værdier i Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (expanding_output_range, ref_list), 0))}
Ref_list: Listen, hvorfra du vil udtrække unikke værdier
Expanding_output_range: Nu er dette meget vigtigt. Dette er det område, hvor du vil se din udtrækkede liste. Denne rækkevidde skal have en særskilt overskrift, som ikke er en del af listen, og din formel vil være under overskriften (hvis overskriften er i E1, vil formlen være i E2).
Udvidelse betyder nu, at når du trækker din formel ned, skal den udvide sig over outputområdet. For at gøre dette skal du angive overskriften som $ E $ 1: E1 (Min overskrift er i E1). Når jeg trækker det ned, udvides det. I E2 vil det være $ E $ 1: E1. I E3 vil det være $ E $ 1: E2. I E2 vil det være $ E $ 1: E3 og så videre.
Lad os nu se et eksempel. Det vil gøre det klart.
Udtrækning af unikke værdier Excel -eksempel
Så her har jeg denne liste over kunder i kolonne EN inden for rækkevidde A2: A16. Nu i kolonne E vil jeg kun få unikke værdier fra kunder. Nu kan dette område A2: A16 også stige, så jeg vil have min formel til at hente ethvert nyt kundenavn fra listen, når listen stiger.
Ok, nu for at hente unikke værdier fra kolonne A, skriv denne formel i Celle E2, og slå CTRL+SKIFT+ENTER for at gøre det til en matrixformel.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Jeg forventer, at listen udvides og kan have nye unikke værdier, som jeg gerne vil udtrække. Det er derfor, jeg har ladet det stå åbent fra bunden ved ikke absolut reference til A16. Det vil tillade det at udvide sig, når du kopierer formlen nedenfor.
Så vi ved, hvordan INDEX og MATCH -funktionen fungerer. Hoveddelen her er:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Denne formel returnerer en matrix med 1'er og 0'er. Når som helst en værdi i området $ E $ 1: E1 findes i kriterieliste $ A $ 2: A16, værdien konverteres til 1 på sin position i intervallet $ A $ 2: A16.
Nu ved hjælp af MATCH -funktionen leder vi efter værdier 0. Match vil returnere positionen for de første 0 fundet i array returneret af COUNTIF -funktionen. End INDEX vil undersøge A $ 2: A16 for at returnere værdien fundet ved indeks returneret af MATCH -funktionen.
Det er måske lidt svært at forstå, men det virker. 0 i slutningen af listen angiver, at der ikke er flere unikke værdier. Hvis du ikke kan se, at 0 i sidste ende skal du kopiere formlen i cellerne herunder.
Nu for at undgå #NA i kan du bruge IFERROR -funktionen i excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Så ja, du kan bruge denne formel til at få unikke værdier fra en liste. I Excel 2019 med abonnement på Office 365 tilbyder Microsoft en funktion, der hedder UNIK. Det tager simpelthen et område som argument og returnerer en række unikke værdier. Det er ikke tilgængeligt i Microsoft Excel 2016 engangskøb.
Download fil:
Excel -formel til at udtrække unikke værdier fra en listeExcel -formel til at udtrække unikke værdier fra en liste
Sådan tæller du unikke værdier i Excel
Populære artikler:
50 Excel -genveje til at øge din produktivitet
Sådan bruges VLOOKUP -funktionen i Excel
Sådan bruges funktionen COUNTIF i Excel
Sådan bruges SUMIF -funktionen i Excel