Splošna formula
=FILTER(list1,COUNTIF(list2,list1))
Povzetek
Če želite primerjati dva seznama in izvleči skupne vrednosti, lahko uporabite formulo, ki temelji na funkcijah FILTER in COUNTIF. V prikazanem primeru je formula v F5:
=FILTER(list1,COUNTIF(list2,list1))
kjer sta seznama1 (B5: B15) in seznam2 (D5: D13) imenovana obsega. Rezultat, vrednosti, ki se pojavijo na obeh seznamih, se prelije v obseg F5: F11.
Pojasnilo
Funkcija FILTER sprejema niz vrednosti in argument "vključi", ki matriko filtrira na podlagi logičnega izraza ali vrednosti.
V tem primeru je polje na voljo kot imenovani obseg "list1", ki vsebuje vse vrednosti v B5: B15. Vključujejo argument podano s funkcijo COUNTIF, ki je ugnezdena znotraj FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF je nastavljen s seznamom2 kot obsegom in seznam1 kot merilom . Ker damo enajst merilnih vrednosti COUNTIF, COUNTIF vrne enajst rezultatov v matriki, kot je ta:
(1;1;0;1;0;1;0;1;0;1;1)
Upoštevajte, da 1 ustreza elementom na seznamu2, ki se pojavijo na seznamu1.
Ta matrika je dostavljena neposredno funkciji FILTER kot argument "vključi":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Seznam funkcij FILTER filtrira seznam1 z uporabo vrednosti, ki jih poda COUNTIF. Vrednosti, povezane z ničlo, se odstranijo; ostale vrednote so ohranjene.
Končni rezultat je niz vrednosti, ki obstajajo na obeh seznamih, ki se prelije v obseg F5: F11.
Razširjena logika
V zgornji formuli za filter uporabljamo surove rezultate iz COUNTIF. To deluje, ker Excel vsako vrednost, ki ni nič, izračuna kot TRUE in nič kot FALSE. Če COUNTIF vrne štetje, večje od 1, bo filter še vedno deloval pravilno.
Če želite izrecno vsiliti TRUE in FALSE rezultate, lahko uporabite "> 0" takole:
=FILTER(list1,COUNTIF(list2,list1)>0)
Odstranite dvojnike ali razvrstite
Če želite odstraniti dvojnike, preprosto vgnestite formulo v funkcijo UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Če želite rezultate razvrstiti, ugnezdi v funkciji SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Vrednosti seznama manjkajo v seznamu2
Če želite izpisati vrednosti v seznamu1, ki manjkajo na seznamu2, lahko logiko obrnete tako:
=FILTER(list1,COUNTIF(list2,list1)=0)
