Generisk formel
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Sammendrag
For å trekke ut bare unike verdier fra en liste eller kolonne, kan du bruke en matriseformel basert på INDEX, MATCH og COUNTIF. I eksemplet vist er formelen i D5, kopiert ned,:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
hvor "liste" er det navngitte området B5: B11.
Merk: dette er en matriseformel og må angis ved hjelp av kontroll + skift + enter.
Forklaring
Kjernen i denne formelen er en grunnleggende oppslag med INDEX:
=INDEX(list,row)
Med andre ord, gi INDEX listen og et radnummer, og INDEX vil hente en verdi som skal legges til i den unike listen.
Det harde arbeidet er å finne ut ROW-tallet for å gi INDEX, slik at vi bare får unike verdier. Dette gjøres med MATCH og COUNTIF, og hovedtrikset er her:
COUNTIF($D$4:D4,list)
Her teller COUNTIF hvor mange ganger elementene som allerede er i den unike listen vises i hovedlisten, ved hjelp av en utvidende referanse for området, $ D $ 4: D4.
En utvidende referanse er absolutt på den ene siden, relativt på den andre. I dette tilfellet, når formelen kopieres ned, utvides referansen til å omfatte flere rader i den unike listen.
Merk at referansen starter i D4, en rad over den første unike oppføringen, i den unike listen. Dette er forsettlig - vi vil telle elementer * allerede * i den unike listen, og vi kan ikke inkludere den nåværende cellen uten å lage en sirkulær referanse. Så vi begynner på raden over.
Viktig: vær sikker på at overskriften til den unike listen ikke vises i hovedlisten.
For kriteriene i COUNTIF bruker vi selve hovedlisten. Når det gis flere kriterier, vil COUNTIF returnere flere resultater i en matrise. På hver nye rad har vi et annet utvalg som dette:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Merk: COUNTIF håndterer flere kriterier med et "ELLER" forhold (dvs. COUNTIF (område, ("rødt", "blått", "grønt")) teller rødt, blått eller grønt.
Nå har vi matriser vi trenger for å finne posisjoner (radnumre). For dette bruker vi MATCH, satt opp for nøyaktig samsvar, for å finne nullverdier. Hvis vi legger matriser opprettet av COUNTIF ovenfor i MATCH, er det vi får:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH lokaliserer gjenstander ved å lete etter en telling på null (dvs. lete etter elementer som ennå ikke vises i den unike listen). Dette fungerer fordi MATCH alltid returnerer den første kampen når det er duplikater.
Finally, the positions are fed into INDEX as row numbers, and INDEX returns the name at that position.
Non array version with LOOKUP
You can build a non-array formula to extract unique items using the flexible LOOKUP function:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
The formula construction is similar to the INDEX MATCH formula above, but LOOKUP can handle the array operation natively.
- COUNTIF returns counts of each value from "list" in the expanding range $D$4:D4
- Comparing to zero creates an array of TRUE and FALSE values
- The number 1 is divided by the array, creating an array of 1s and #DIV/0 errors
- This array becomes the the lookup_vector inside LOOKUP
- The lookup value of 2 is larger than any values in the lookup_vector
- LOOKUP will match the last non-error value in the lookup array
- LOOKUP returns the corresponding value in result_vector, the named range "list"
Pakk ut elementer som vises bare én gang
LOOKUP-formelen ovenfor er enkel å utvide med boolsk logikk. For å trekke ut en liste over unike elementer som vises bare en gang i kildedataene, kan du bruke en formel som denne:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Det eneste tillegget er det andre COUNTIF-uttrykket:
COUNTIF(list,list)=1
Her returnerer COUNTIF en rekke varetellinger slik:
(2;2;2;2;2;1;2)
som sammenlignes med 1, noe som resulterer i en rekke SANNE / FALSE verdier:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
som fungerer som et "filter" for å begrense utdata til elementer som forekommer bare en gang i kildedataene.
