Generieke formule
=FILTER(list1,COUNTIF(list2,list1))
Samenvatting
Om gegevens te filteren om overeenkomende waarden in twee lijsten te extraheren, kunt u de functie FILTER en de functie AANTAL.ALS of AANTAL.ALS gebruiken. In het getoonde voorbeeld is de formule in F5:
=FILTER(list1,COUNTIF(list2,list1))
waarbij lijst1 (B5: B16) en lijst2 (D5: D14) benoemde bereiken zijn. Het resultaat dat door FILTER wordt geretourneerd, bevat alleen de waarden in lijst1 die in lijst2 voorkomen .
Opmerking: FILTER is een nieuwe dynamische matrixfunctie in Excel 365.
Uitleg
Deze formule is gebaseerd op de FILTER-functie om gegevens op te halen op basis van een logische test gebouwd met de AANTAL.ALS-functie:
=FILTER(list1,COUNTIF(list2,list1))
werkend van binnen naar buiten, wordt de AANTAL.ALS-functie gebruikt om het eigenlijke filter te creëren:
COUNTIF(list2,list1)
Merk op dat we lijst2 gebruiken als het bereikargument en lijst1 als het criteriumargument. Met andere woorden, we vragen AANTAL.ALS om alle waarden in lijst1 te tellen die in lijst2 voorkomen. Omdat we AANTAL.ALS meerdere waarden geven voor criteria, krijgen we een array terug met meerdere resultaten:
(1;1;0;1;0;1;0;0;1;0;1;1)
Merk op dat de array 12 counts bevat, één voor elke waarde in list1 . Een nulwaarde geeft een waarde in lijst1 aan die niet in lijst2 wordt gevonden . Elk ander positief getal geeft een waarde aan in lijst1 die wordt gevonden in lijst2 . Deze array wordt direct teruggestuurd naar de FILTER-functie als het include-argument:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
De filterfunctie gebruikt de array als filter. Elke waarde in lijst1 die aan een nul is gekoppeld, wordt verwijderd, terwijl elke waarde die aan een positief getal is gekoppeld, blijft bestaan.
Het resultaat is een reeks van 7 overeenkomende waarden die overlopen in het bereik F5: F11. Als de gegevens veranderen, berekent FILTER opnieuw en retourneert een nieuwe lijst met overeenkomende waarden op basis van de nieuwe gegevens.
Niet-overeenkomende waarden
Om niet-overeenkomende waarden uit lijst1 te extraheren (dwz waarden in lijst1 die niet in lijst2 voorkomen ), kunt u de NOT-functie als volgt aan de formule toevoegen:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
De NOT-functie keert effectief het resultaat van AANTAL.ALS om - elk niet-nul getal wordt FALSE en elke nulwaarde wordt WAAR. Het resultaat is een lijst met de waarden in lijst1 die niet in lijst2 voorkomen .
Met INDEX
Het is mogelijk om een formule te maken om overeenkomende waarden te extraheren zonder de FILTER-functie, maar de formule is complexer. Een optie is om de functie INDEX te gebruiken in een formule als deze:
De formule in G5, gekopieerd, is:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
De kern van deze formule is de functie INDEX, die list1 ontvangt als het matrixargument. De meeste van de resterende formule berekent eenvoudig het rijnummer dat moet worden gebruikt voor overeenkomende waarden. Deze uitdrukking genereert een lijst met relatieve rijnummers:
ROW(list1)-ROW(INDEX(list1,1,1))+1
die een array van 12 getallen retourneert die de rijen in lijst1 vertegenwoordigen :
(1;2;3;4;5;6;7;8;9;10;11;12)
Deze worden gefilterd met de ALS-functie en dezelfde logica die hierboven wordt gebruikt in FILTER, op basis van de AANTAL.ALS-functie:
COUNTIF(list2,list1) // find matching values
De resulterende array ziet er als volgt uit:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Deze array wordt rechtstreeks aan de SMALL-functie geleverd, die wordt gebruikt om het volgende overeenkomende rijnummer op te halen terwijl de formule door de kolom wordt gekopieerd. De k-waarde voor SMALL (denk n-de) wordt berekend met een uitbreidend bereik:
ROWS($G$5:G5) // incrementing value for k
De functie IFERROR wordt gebruikt om fouten op te vangen die optreden wanneer de formule wordt gekopieerd en geen overeenkomende waarden meer heeft. Zie deze formule voor een ander voorbeeld van dit idee.
