Generisk formel
=FILTER(list1,COUNTIF(list2,list1))
Resumé
For at filtrere data for at udtrække matchende værdier på to lister kan du bruge FILTER-funktionen og COUNTIF- eller COUNTIFS-funktionen. I det viste eksempel er formlen i F5:
=FILTER(list1,COUNTIF(list2,list1))
hvor liste1 (B5: B16) og liste2 (D5: D14) er navngivet områder. Resultatet returneret af FILTER inkluderer kun de værdier i liste1, der vises i liste2 .
Bemærk: FILTER er en ny dynamisk matrixfunktion i Excel 365.
Forklaring
Denne formel er afhængig af FILTER-funktionen for at hente data baseret på en logisk test bygget med COUNTIF-funktionen:
=FILTER(list1,COUNTIF(list2,list1))
arbejder indefra og ud, COUNTIF-funktionen bruges til at oprette det aktuelle filter:
COUNTIF(list2,list1)
Bemærk, at vi bruger liste2 som rækkevidde-argument, og liste1 som kriterieargument. Med andre ord beder vi COUNTIF om at tælle alle værdier i liste1, der vises i liste2. Da vi giver COUNTIF flere værdier for kriterier, får vi tilbage en matrix med flere resultater:
(1;1;0;1;0;1;0;0;1;0;1;1)
Bemærk, at matrixen indeholder 12 optællinger, en for hver værdi i liste1 . En nulværdi angiver en værdi i liste1 , der ikke findes i liste2 . Ethvert andet positivt tal angiver en værdi i liste1 , der findes i liste2 . Denne matrix returneres direkte til FILTER-funktionen som include-argumentet:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filterfunktionen bruger arrayet som et filter. Enhver værdi i liste1, der er knyttet til et nul, fjernes, mens enhver værdi, der er knyttet til et positivt tal, overlever.
Resultatet er en matrix med 7 matchende værdier, der spilder ind i området F5: F11. Hvis data ændres, genberegner FILTER og returnerer en ny liste med matchende værdier baseret på de nye data.
Ikke-matchende værdier
For at udtrække ikke-matchende værdier fra liste1 (dvs. værdier i liste1 , der ikke vises i liste2 ), kan du tilføje IKKE-funktionen til formlen sådan:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
IKKE-funktionen vender effektivt resultatet fra COUNTIF - ethvert tal, der ikke er nul, bliver FALSK, og enhver nulværdi bliver SAND. Resultatet er en liste over de værdier i liste1 , der ikke findes i liste2 .
Med INDEX
Det er muligt at oprette en formel for at udtrække matchende værdier uden FILTER-funktionen, men formlen er mere kompleks. En mulighed er at bruge INDEX-funktionen i en formel som denne:
Formlen i G5, kopieret ned er:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Kernen i denne formel er INDEX-funktionen, som modtager liste1 som arrayargument . Det meste af den resterende formel beregner simpelthen det række, der skal bruges til matchende værdier. Dette udtryk genererer en liste med relative rækkenumre:
ROW(list1)-ROW(INDEX(list1,1,1))+1
der returnerer en matrix på 12 tal, der repræsenterer rækkerne i liste1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Disse filtreres med IF-funktionen og den samme logik, der er anvendt ovenfor i FILTER, baseret på COUNTIF-funktionen:
COUNTIF(list2,list1) // find matching values
Den resulterende matrix ser sådan ud:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Denne matrix leveres direkte til SMALL-funktionen, som bruges til at hente det næste matchende række nummer, da formlen kopieres ned i kolonnen. K-værdien for SMALL (tænk nth) beregnes med et ekspanderende interval:
ROWS($G$5:G5) // incrementing value for k
IFERROR-funktionen bruges til at fange fejl, der opstår, når formlen kopieres og løber tør for matchende værdier. For et andet eksempel på denne idé, se denne formel.
