Generisk formel
=FILTER(list1,COUNTIF(list2,list1))
Sammanfattning
För att jämföra två listor och extrahera vanliga värden kan du använda en formel baserad på FILTER- och COUNTIF-funktionerna. I exemplet som visas är formeln i F5:
=FILTER(list1,COUNTIF(list2,list1))
där list1 (B5: B15) och list2 (D5: D13) heter områden. Resultatet, värden som visas i båda listorna, spills in i intervallet F5: F11.
Förklaring
FILTER-funktionen accepterar en matris med värden och ett "inkludera" -argument som filtrerar matrisen baserat på ett logiskt uttryck eller värde.
I detta fall tillhandahålls matrisen som det namngivna intervallet "list1", som innehåller alla värden i B5: B15. Den omfattar argument levereras av COUNTIF funktionen, som är kapslad inuti FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF är inställt med list2 som intervall och list1 som kriterier . Eftersom vi ger COUNTIF elva kriterievärden returnerar COUNTIF elva resultat i en matris så här:
(1;1;0;1;0;1;0;1;0;1;1)
Observera att 1: er motsvarar objekt i lista2 som visas i lista1.
Denna matris levereras direkt till FILTER-funktionen som "inkludera" -argument:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
FILTER-funktionen filtrerar lista 1 med de värden som COUNTIF tillhandahåller. Värden associerade med noll tas bort; andra värden bevaras.
Det slutliga resultatet är en rad värden som finns i båda listorna, som spills in i intervallet F5: F11.
Utökad logik
I ovanstående formel använder vi de råa resultaten från COUNTIF som filter. Detta fungerar eftersom Excel utvärderar alla icke-nollvärden som SANT och noll som FALSE. Om COUNTIF returnerar ett antal större än 1, fungerar filtret fortfarande korrekt.
För att tvinga fram SANT och FALSKT resultat uttryckligen kan du använda "> 0" så här:
=FILTER(list1,COUNTIF(list2,list1)>0)
Ta bort dubbletter eller sortera
För att ta bort dubbletter, häck bara formeln i UNIQUE-funktionen:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
För att sortera resultat, bo i SORT-funktionen:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Listvärden saknas i list2
För att mata ut värden i lista1 från list2 kan du vända logiken så här:
=FILTER(list1,COUNTIF(list2,list1)=0)
