Generisk formel
=FILTER(list1,COUNTIF(list2,list1))
Sammendrag
For å sammenligne to lister og trekke ut vanlige verdier, kan du bruke en formel basert på funksjonene FILTER og ANTELL. I eksemplet vist er formelen i F5:
=FILTER(list1,COUNTIF(list2,list1))
der liste1 (B5: B15) og liste2 (D5: D13) er kalt områder. Resultatet, verdier som vises i begge lister, søles inn i området F5: F11.
Forklaring
FILTER-funksjonen godtar en rekke verdier og et "inkluder" -argument som filtrerer matrisen basert på et logisk uttrykk eller en verdi.
I dette tilfellet er matrisen gitt som det navngitte området "liste1", som inneholder alle verdiene i B5: B15. Den omfatter Argumentet er levert av COUNTIF funksjon, som er nestet inne FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF er satt opp med liste2 som område , og liste1 som kriterier . Fordi vi gir COUNTIF elleve kriterieverdier, returnerer COUNTIF elleve resultater i en matrise som dette:
(1;1;0;1;0;1;0;1;0;1;1)
Legg merke til at 1-ene tilsvarer elementene i liste2 som vises i liste1.
Denne matrisen leveres direkte til FILTER-funksjonen som "inkluderer" -argumentet:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
FILTER-funksjonen filtrerer liste1 ved hjelp av verdiene gitt av COUNTIF. Verdier knyttet til null fjernes; andre verdier er bevart.
Det endelige resultatet er en rekke verdier som finnes i begge lister, og som spills inn i området F5: F11.
Utvidet logikk
I formelen ovenfor bruker vi råresultatene fra COUNTIF som filter. Dette fungerer fordi Excel evaluerer en verdi som ikke er null som SANT, og null som FALSK. Hvis COUNTIF returnerer et antall større enn 1, vil filteret fortsatt fungere skikkelig.
For å tvinge SANTE og FALSE resultater eksplisitt, kan du bruke "> 0" slik:
=FILTER(list1,COUNTIF(list2,list1)>0)
Fjern duplikater eller sorter
For å fjerne duplikater, bare hekk formelen i UNIQUE-funksjonen:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
For å sortere resultatene, nest i SORT-funksjonen:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Listeverdier mangler fra liste2
For å sende verdier i liste1 som mangler fra liste2, kan du snu logikken slik:
=FILTER(list1,COUNTIF(list2,list1)=0)
