Generisk formel
=FILTER(list1,COUNTIF(list2,list1))
Resumé
For at sammenligne to lister og udtrække almindelige værdier kan du bruge en formel, der er baseret på FILTER- og COUNTIF-funktionerne. I det viste eksempel er formlen i F5:
=FILTER(list1,COUNTIF(list2,list1))
hvor liste1 (B5: B15) og liste2 (D5: D13) er navngivet områder. Resultatet, værdier, der vises på begge lister, spildes i området F5: F11.
Forklaring
FILTER-funktionen accepterer et array af værdier og et "include" -argument, der filtrerer arrayet baseret på et logisk udtryk eller en logisk værdi.
I dette tilfælde leveres arrayet som det navngivne område "list1", som indeholder alle værdier i B5: B15. Den omfatter argument afleveres af TÆL.HVIS funktion, som er indlejret inde FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF er opsat med liste2 som rækkevidde og liste1 som kriterier . Da vi giver COUNTIF elleve kriterieværdier, returnerer COUNTIF elleve resultater i en matrix som denne:
(1;1;0;1;0;1;0;1;0;1;1)
Bemærk, at 1'erne svarer til emner i liste2, der vises i liste1.
Denne matrix leveres direkte til FILTER-funktionen som "include" -argumentet:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
FILTER-funktionen filtrerer liste1 ved hjælp af de værdier, der leveres af COUNTIF. Værdier tilknyttet nul fjernes; andre værdier bevares.
Det endelige resultat er en række værdier, der findes i begge lister, som spilder ind i området F5: F11.
Udvidet logik
I ovenstående formel bruger vi de rå resultater fra COUNTIF som filter. Dette fungerer, fordi Excel evaluerer enhver værdi, der ikke er nul, som SAND og nul som FALSK. Hvis COUNTIF returnerer et antal større end 1, fungerer filteret stadig korrekt.
For at tvinge SANDE og FALSKE resultater eksplicit, kan du bruge "> 0" på denne måde:
=FILTER(list1,COUNTIF(list2,list1)>0)
Fjern dubletter eller sorter
For at fjerne dubletter skal du bare rede formlen inde i UNIQUE-funktionen:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
For at sortere resultater skal du reden i SORT-funktionen:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Listeværdier mangler fra liste2
For at outputværdier i liste1 mangler fra liste2 kan du vende logikken sådan:
=FILTER(list1,COUNTIF(list2,list1)=0)
