Oké, dus in mijn begindagen van gegevensanalyse had ik de behoefte om de unieke items in Excel automatisch uit een lijst te halen, in plaats van elke keer dubbele items te verwijderen. En ik heb een manier bedacht om het te doen. Daarna was mijn Excel-dashboard nog dynamischer dan voorheen. Dus laten we eens kijken hoe we het kunnen doen…
Om een lijst met unieke waarden uit een lijst te halen, gebruiken we INDEX, MATCH en COUNTIF. Ik zal ook IFERROR gebruiken, alleen om schone resultaten te krijgen, het is optioneel.
En ja, het zal een matrixformule zijn … Dus laten we het voor elkaar krijgen …
Generieke formule om unieke waarden in Excel te extraheren
{=INDEX(ref_list,MATCH(0,COUNTIF(expanding_output_range,ref_list),0))}
Ref_lijst: De lijst waaruit u unieke waarden wilt extraheren
Expanding_output_range: Dit is nu erg belangrijk. Dit is het bereik waar u uw uitgepakte lijst wilt zien. Dit bereik moet een aparte kop hebben die geen deel uitmaakt van de lijst en uw formule staat onder de kop (als de kop in E1 staat, dan staat de formule in E2).
Uitbreiden betekent nu dat wanneer u uw formule naar beneden sleept, deze over het uitvoerbereik moet worden uitgebreid. Om dit te doen, moet u de referentie van de kop opgeven als $E$1:E1 (Mijn rubriek staat in E1). Als ik het naar beneden sleep, wordt het groter. In E2 zal het zijn $E$1:E1. In E3 zal het zijn $E$1:E2. In E2 zal het zijn $E$1:E3 enzovoort.
Laten we nu een voorbeeld bekijken. Het zal het duidelijk maken.
Excel-voorbeeld van unieke waarden extraheren
Dus hier heb ik deze lijst met klanten in Column EEN binnen bereik A2:A16. Nu wil ik in kolom E alleen unieke waarden van klanten krijgen. Nu kan dit bereik A2:A16 ook toenemen, dus ik wil dat mijn formule elke nieuwe klantnaam uit de lijst haalt, wanneer de lijst toeneemt.
Ok, om nu unieke waarden uit kolom A op te halen, schrijf deze formule in cel E2, en raak CTRL+SHIFT+ENTER om er een matrixformule van te maken.
{=INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}
A$2:A16: Ik verwacht dat die lijst zal uitbreiden en mogelijk nieuwe unieke waarden zal hebben die ik graag wil extraheren. Dat is de reden waarom ik het van onderen open heb gelaten door geen absolute verwijzing naar A16. Het zal het mogelijk maken om uit te breiden wanneer u de onderstaande formule kopieert.
We weten dus hoe de INDEX- en MATCH-functie werkt. Het belangrijkste deel hier is:
AANTAL.ALS($E$1:E1,$A$2:A16): Deze formule retourneert een matrix van enen en nullen. Wanneer een waarde binnen bereik $E$1:E1 is te vinden in de criterialijst $A$2:A16, wordt de waarde geconverteerd naar 1 op zijn positie in het bereik $A$2:A16.
Nu we de MATCH-functie gebruiken, zoeken we naar waarden 0. Match retourneert de positie van de eerste 0 gevonden in de array die wordt geretourneerd door de AANTAL.ALS-functie. Dan zal INDEX kijken naar A$2:A16 om de waarde te retourneren die is gevonden in de index die is geretourneerd door de MATCH-functie.
Het is misschien een beetje moeilijk te begrijpen, maar het werkt. De 0 aan het einde van de lijst geeft aan dat er geen unieke waarden meer zijn. Als u die 0 uiteindelijk niet ziet, moet u de formule in de onderstaande cellen kopiëren.
Nu om te vermijden #NA in kunt u de IFERROR-functie van Excel gebruiken.
{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:$E1,$A$2:A16),0)),"")}
Dus ja, je kunt deze formule gebruiken om unieke waarden uit een lijst te halen. In Excel 2019 met abonnement Office 365 biedt Microsoft een functie genaamd UNIQUE. Het neemt gewoon een bereik als argument en retourneert een reeks unieke waarden. Het is niet beschikbaar in Microsoft Excel 2016 eenmalige aankoop.
Download bestand:
Excel-formule om unieke waarden uit een lijst te halenExcel-formule om unieke waarden uit een lijst te halen
Unieke waarden tellen in Excel
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken?
Hoe de AANTAL.ALS-functie in Excel te gebruiken?
Hoe de SUMIF-functie in Excel te gebruiken?