Generieke formule
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Samenvatting
Om alleen unieke waarden uit een lijst of kolom te extraheren, kunt u een matrixformule gebruiken die is gebaseerd op INDEX, MATCH en AANTAL.ALS. In het getoonde voorbeeld is de formule in D5, naar beneden gekopieerd,:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
waarbij "lijst" het benoemde bereik is B5: B11.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
De kern van deze formule is een basiszoekopdracht met INDEX:
=INDEX(list,row)
Met andere woorden, geef INDEX de lijst en een rijnummer, en INDEX haalt een waarde op om toe te voegen aan de unieke lijst.
Het harde werk is het ROW-nummer uitzoeken om INDEX te geven, zodat we alleen unieke waarden krijgen. Dit wordt gedaan met WEDSTRIJD en AANTAL.ALS, en de belangrijkste truc is hier:
COUNTIF($D$4:D4,list)
Hier telt AANTAL.ALS hoe vaak items die al in de unieke lijst staan, in de hoofdlijst verschijnen, met behulp van een uitdijende referentie voor het bereik, $ D $ 4: D4.
Een zich uitbreidende referentie is aan de ene kant absoluut, aan de andere kant relatief. In dit geval, terwijl de formule naar beneden wordt gekopieerd, wordt de verwijzing uitgevouwen om meer rijen in de unieke lijst op te nemen.
Merk op dat de verwijzing begint in D4, een rij boven het eerste unieke item, in de unieke lijst. Dit is opzettelijk - we willen items * al * tellen in de unieke lijst en we kunnen de huidige cel niet opnemen zonder een kringverwijzing te maken. Dus we beginnen op de rij hierboven.
Belangrijk: zorg ervoor dat de kop voor de unieke lijst niet voorkomt in de hoofdlijst.
Voor de criteria in AANTAL.ALS gebruiken we de hoofdlijst zelf. Wanneer meerdere criteria worden gegeven, retourneert AANTAL.ALS meerdere resultaten in een matrix. Bij elke nieuwe rij hebben we een andere array, zoals deze:
(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
Opmerking: AANTAL.ALS verwerkt meerdere criteria met een "OF" -relatie (dwz AANTAL.ALS (bereik, ("rood", "blauw", "groen")) telt als rood, blauw of groen.
Nu hebben we de arrays die we nodig hebben om posities (rijnummers) te vinden. Hiervoor gebruiken we MATCH, ingesteld voor exacte overeenkomst, om nulwaarden te vinden. Als we de arrays die zijn gemaakt door AANTAL.ALS hierboven in MATCH plaatsen, krijgen we het volgende:
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 lokaliseert items door te zoeken naar een telling van nul (dwz zoeken naar items die nog niet in de unieke lijst voorkomen). Dit werkt, omdat MATCH altijd de eerste overeenkomst retourneert als er duplicaten zijn.
Ten slotte worden de posities in INDEX ingevoerd als rijnummers, en INDEX retourneert de naam op die positie.
Niet-arrayversie met ZOEKEN
U kunt een niet-matrixformule maken om unieke items te extraheren met behulp van de flexibele ZOEKEN-functie:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
De formuleconstructie is vergelijkbaar met de INDEX MATCH-formule hierboven, maar LOOKUP kan de matrixbewerking native afhandelen.
- AANTAL.ALS retourneert tellingen van elke waarde uit "lijst" in het uitbreidende bereik $ D $ 4: D4
- Als u met nul vergelijkt, wordt er een array van WAAR en ONWAAR waarden gemaakt
- Het getal 1 wordt gedeeld door de array, waardoor een array van 1s en # DIV / 0-fouten ontstaat
- Deze array wordt de lookup_vector binnen LOOKUP
- De opzoekwaarde van 2 is groter dan alle waarden in de lookup_vector
- LOOKUP komt overeen met de laatste niet-foutwaarde in de lookup-array
- ZOEKEN retourneert de overeenkomstige waarde in resultaat_vector, het benoemde bereik "lijst"
Extraheer items die slechts één keer voorkomen
De bovenstaande LOOKUP-formule is eenvoudig uit te breiden met booleaanse logica. Om een lijst met unieke items te extraheren die slechts één keer in de brongegevens voorkomen, kunt u een formule als volgt gebruiken:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
De enige toevoeging is de tweede AANTAL.ALS-uitdrukking:
COUNTIF(list,list)=1
Hier retourneert AANTAL.ALS een reeks itemtellingen als volgt:
(2;2;2;2;2;1;2)
die worden vergeleken met 1, wat resulteert in een reeks TRUE / FALSE-waarden:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
die fungeren als een "filter" om de uitvoer te beperken tot items die slechts één keer voorkomen in de brongegevens.
