Ok, så under mina första dagar med dataanalys fick jag ett behov av att automatiskt hämta de unika objekten i excel från en lista, snarare än att ta bort dubbletter varje gång. Och jag kom på ett sätt att göra det. Efter det var min excel -instrumentpanel ännu mer dynamisk än tidigare. Så låt oss se hur vi kan göra det …
För att extrahera lista över unika värden från en lista använder vi INDEX, MATCH och COUNTIF. Jag kommer också att använda IFERROR, bara för att få rena resultat, det är valfritt.
Och ja, det blir en matrisformel … Så låt oss göra det …
Generisk formel för att extrahera unika värden i Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (expanding_output_range, ref_list), 0))}
Ref_list: Listan som du vill extrahera unika värden från
Expanding_output_range: Nu är detta mycket viktigt. Detta är intervallet där du vill se din extraherade lista. Detta sortiment måste ha en distinkt rubrik som inte ingår i listan och din formel kommer att ligga under rubriken (om rubriken är i E1 än kommer Formel att vara i E2).
Nu expanderar betyder att när du drar ner din formel bör den expandera över utgångsområdet. För att göra det måste du ange rubriken som $ E $ 1: E1 (Min rubrik är i E1). När jag drar ner det kommer det att expandera. I E2 blir det $ E $ 1: E1. I E3 blir det $ E $ 1: E2. I E2 blir det $ E $ 1: E3 och så vidare.
Låt oss nu se ett exempel. Det kommer att göra det klart.
Extrahera unika värden Excel Exempel
Så här har jag den här kundlistan i kolumnen A inom räckhåll A2: A16. Nu i kolumn E vill jag bara få unika värden från kunder. Nu kan detta intervall A2: A16 också öka, så jag vill att min formel ska hämta ett nytt kundnamn från listan, när listan ökar.
Ok, nu för att hämta unika värden från kolumn A, skriv den här formeln Cell E2, och slog CTRL+SKIFT+ENTER för att göra det till en matrisformel.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Jag förväntar mig att listan kommer att expandera och kan ha nya unika värden som jag vill extrahera. Det är därför jag har lämnat det öppet från botten genom inte absolut referens till A16. Det kommer att tillåta det att expandera när du kopierar formeln nedan.
Så vi vet hur INDEX och MATCH -funktionen fungerar. Huvuddelen här är:
RÄKNING ($ E $ 1: E1, $ A $ 2: A16): Denna formel returnerar en array med 1s och 0s. När som helst ett värde i intervallet $ E $ 1: E1 finns i kriterielistan $ A $ 2: A16, värdet omvandlas till 1 vid sin position i intervallet $ A $ 2: A16.
Nu när vi använder MATCH -funktionen letar vi efter värden 0. Match kommer att returnera positionen för de första 0 som finns i arrayen som returneras av COUNTIF -funktionen. Än INDEX kommer att undersöka A $ 2: A16 för att returnera värde som hittas vid index som returneras av MATCH -funktionen.
Det är kanske lite svårt att förstå men det fungerar. 0 i slutet av listan indikerar att det inte finns fler unika värden. Om du inte ser att 0 i slutändan ska du kopiera formeln i cellerna nedan.
Nu för att undvika #NA i kan du använda IFERROR -funktionen i excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Så ja, du kan använda den här formeln för att få unika värden från en lista. I Excel 2019 med prenumeration på Office 365 erbjuder Microsoft en funktion som heter UNIK. Det tar helt enkelt ett intervall som argument och returnerar en rad unika värden. Det är inte tillgängligt i Microsoft Excel 2016 engångsköp.
Nedladdning fil:
Excel -formel för att extrahera unika värden från en listaExcel -formel för att extrahera unika värden från en lista
Hur man räknar unika värden i Excel
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet
Så här använder du VLOOKUP -funktionen i Excel
Så här använder du funktionen COUNTIF i Excel
Hur man använder SUMIF -funktionen i Excel