Generisk formel
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Resumé
For kun at udtrække unikke værdier fra en liste eller kolonne kan du bruge en matrixformel baseret på INDEX, MATCH og COUNTIF. I det viste eksempel er formlen i D5, kopieret ned,:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
hvor "liste" er det navngivne område B5: B11.
Bemærk: dette er en matrixformel og skal indtastes ved hjælp af kontrol + skift + enter.
Forklaring
Kernen i denne formel er en grundlæggende opslag med INDEX:
=INDEX(list,row)
Med andre ord, giv INDEX listen og et række nummer, og INDEX henter en værdi, der skal føjes til den unikke liste.
Det hårde arbejde er at finde ud af ROW-nummeret for at give INDEX, så vi kun får unikke værdier. Dette gøres med MATCH og COUNTIF, og det vigtigste trick er her:
COUNTIF($D$4:D4,list)
Her tæller COUNTIF, hvor mange gange elementer, der allerede findes på den unikke liste, vises på mastelisten ved hjælp af en udvidet reference for området $ D $ 4: D4.
En ekspanderende reference er absolut på den ene side, relativt på den anden. I dette tilfælde, da formlen kopieres ned, udvides referencen til at omfatte flere rækker på den unikke liste.
Bemærk, at referencen starter i D4, en række over den første entydige post, på den unikke liste. Dette er forsætligt - vi vil tælle emner * allerede * på den unikke liste, og vi kan ikke medtage den aktuelle celle uden at oprette en cirkulær reference. Så vi starter på rækken ovenfor.
Vigtigt: Sørg for, at overskriften til den unikke liste ikke vises på masterlisten.
For kriterierne i COUNTIF bruger vi selve masterlisten. Når der gives flere kriterier, returnerer COUNTIF flere resultater i en matrix. På hver nye række har vi et andet array 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
Bemærk: COUNTIF håndterer flere kriterier med en "ELLER" -relation (dvs. COUNTIF (interval, ("rød", "blå", "grøn")) tæller rød, blå eller grøn.
Nu har vi de arrays, vi har brug for for at finde positioner (række numre). Til dette bruger vi MATCH, opsat til nøjagtig matchning, for at finde nulværdier. Hvis vi sætter arrays oprettet af COUNTIF ovenfor i MATCH, her er hvad 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 emner ved at kigge efter et antal på nul (dvs. søge efter emner, der endnu ikke vises på den unikke liste). Dette fungerer, fordi MATCH altid returnerer den første kamp, når der er duplikater.
Endelig tilføres positionerne til INDEX som rækkenumre, og INDEX returnerer navnet på den position.
Ikke array-version med LOOKUP
Du kan oprette en ikke-matrixformel til at udtrække unikke emner ved hjælp af den fleksible LOOKUP-funktion:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Formelkonstruktionen svarer til INDEX MATCH-formlen ovenfor, men LOOKUP kan håndtere arrayoperationen indbygget.
- COUNTIF returnerer tællinger af hver værdi fra "liste" i det ekspanderende interval $ D $ 4: D4
- Sammenligning med nul opretter en matrix med SANDE og FALSKE værdier
- Nummeret 1 divideres med arrayet, hvilket skaber en array med 1s og # DIV / 0-fejl
- Denne matrix bliver lookup_vector inden for LOOKUP
- Opslagsværdien på 2 er større end nogen værdi i opslagsvektoren
- LOOKUP matcher den sidste ikke-fejlværdi i opslagsarrayet
- LOOKUP returnerer den tilsvarende værdi i result_vector, det navngivne interval "liste"
Uddrag emner, der vises kun én gang
LOOKUP-formlen ovenfor er let at udvide med boolsk logik. For at udtrække en liste over unikke emner, der kun vises en gang i kildedataene, kan du bruge en formel som denne:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Den eneste tilføjelse er det andet COUNTIF-udtryk:
COUNTIF(list,list)=1
Her returnerer COUNTIF en matrix af varetællinger som denne:
(2;2;2;2;2;1;2)
som sammenlignes med 1, hvilket resulterer i en matrix med SAND / FALSK-værdier:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
som fungerer som et "filter" for at begrænse output til elementer, der kun forekommer en gang i kildedataene.
