Generisk formel
=FILTER(list1,COUNTIF(list2,list1))
Sammendrag
For å filtrere data for å trekke ut samsvarende verdier i to lister, kan du bruke FILTER-funksjonen og COUNTIF- eller COUNTIFS-funksjonen. I eksemplet vist er formelen i F5:
=FILTER(list1,COUNTIF(list2,list1))
der liste1 (B5: B16) og liste2 (D5: D14) er kalt områder. Resultatet som returneres av FILTER inkluderer bare verdiene i liste1 som vises i liste2 .
Merk: FILTER er en ny dynamisk matrisefunksjon i Excel 365.
Forklaring
Denne formelen er avhengig av FILTER-funksjonen for å hente data basert på en logisk test bygget med COUNTIF-funksjonen:
=FILTER(list1,COUNTIF(list2,list1))
arbeider fra innsiden og ut, COUNTIF-funksjonen brukes til å lage det faktiske filteret:
COUNTIF(list2,list1)
Legg merke til at vi bruker liste2 som rekkeviddeargument, og liste1 som kriterierargument. Med andre ord ber vi COUNTIF om å telle alle verdiene i liste1 som vises i liste2. Fordi vi gir COUNTIF flere verdier for kriterier, får vi tilbake en matrise med flere resultater:
(1;1;0;1;0;1;0;0;1;0;1;1)
Merk at matrisen inneholder 12 tellinger, en for hver verdi i liste1 . En nullverdi indikerer en verdi i liste1 som ikke finnes i liste2 . Ethvert annet positivt tall indikerer en verdi i liste1 som finnes i liste2 . Denne matrisen returneres direkte til FILTER-funksjonen som inkluderingsargument:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filterfunksjonen bruker matrisen som et filter. Enhver verdi i liste1 assosiert med et null fjernes, mens en hvilken som helst verdi knyttet til et positivt tall overlever.
Resultatet er en matrise med 7 samsvarende verdier som sprer seg inn i området F5: F11. Hvis data endres, vil FILTER beregne på nytt og returnere en ny liste med samsvarende verdier basert på de nye dataene.
Ikke-samsvarende verdier
For å trekke ut verdier som ikke samsvarer fra liste1 (dvs. verdier i liste1 som ikke vises i liste2 ), kan du legge til IKKE-funksjonen i formelen slik:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
IKKE-funksjonen reverserer effektivt resultatet fra COUNTIF - ethvert tall som ikke er null blir FALSE, og en nullverdi blir SANT. Resultatet er en liste over verdiene i liste1 som ikke er tilstede i liste2 .
Med INDEX
Det er mulig å lage en formel for å trekke ut samsvarende verdier uten FILTER-funksjonen, men formelen er mer kompleks. Et alternativ er å bruke INDEX-funksjonen i en formel som denne:
Formelen i G5, kopiert ned er:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Merk: dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Kjernen i denne formelen er INDEX-funksjonen, som mottar liste1 som arrayargument . Det meste av den gjenværende formelen beregner bare radnummeret som skal brukes til samsvarende verdier. Dette uttrykket genererer en liste over relative radnumre:
ROW(list1)-ROW(INDEX(list1,1,1))+1
som returnerer en matrise med 12 tall som representerer radene i liste1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Disse filtreres med IF-funksjonen og den samme logikken som brukes ovenfor i FILTER, basert på COUNTIF-funksjonen:
COUNTIF(list2,list1) // find matching values
Den resulterende matrisen ser slik ut:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Denne matrisen leveres direkte til SMALL-funksjonen, som brukes til å hente neste matchende radnummer når formelen kopieres nedover i kolonnen. K-verdien for SMALL (think nth) beregnes med et utvidende område:
ROWS($G$5:G5) // incrementing value for k
IFERROR-funksjonen brukes til å fange feil som oppstår når formelen kopieres og går tom for samsvarende verdier. For et annet eksempel på denne ideen, se denne formelen.
