Generička formula
=FILTER(list1,COUNTIF(list2,list1))
Sažetak
Da biste usporedili dva popisa i izvukli uobičajene vrijednosti, možete koristiti formulu koja se temelji na funkcijama FILTER i COUNTIF. U prikazanom primjeru formula u F5 je:
=FILTER(list1,COUNTIF(list2,list1))
gdje su popis1 (B5: B15) i popis2 (D5: D13) imenovani rasponi. Rezultat, vrijednosti koje se pojavljuju na oba popisa, prelijeva se u raspon F5: F11.
Obrazloženje
Funkcija FILTER prihvaća niz vrijednosti i argument "uključi" koji filtrira niz na temelju logičkog izraza ili vrijednosti.
U ovom se slučaju niz pruža kao imenovani raspon "list1", koji sadrži sve vrijednosti u B5: B15. Uključuju argument se isporučuje s funkcijom COUNTIF, koja je postavljena unutar FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF se postavlja s list2 kao rasponom , a list1 kao kriterijima . Budući da dajemo COUNTIF jedanaest vrijednosti kriterija, COUNTIF vraća jedanaest rezultata u nizu poput ovog:
(1;1;0;1;0;1;0;1;0;1;1)
Primijetite da jedinice 1 odgovaraju stavkama na popisu2 koje se pojavljuju na popisu1.
Ovaj se niz isporučuje izravno funkciji FILTER kao argument "uključi":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Popis funkcija FILTER filtrira popis1 koristeći vrijednosti koje daje COUNTIF. Vrijednosti povezane s nulom uklanjaju se; ostale vrijednosti su sačuvane.
Konačni rezultat je niz vrijednosti koji postoje na oba popisa, a koji se prelijeva u raspon F5: F11.
Proširena logika
U gornjoj formuli kao filtar koristimo sirove rezultate iz COUNTIF-a. To djeluje jer Excel svaku vrijednost koja nije nula procjenjuje kao TRUE, a nulu kao FALSE. Ako COUNTIF vrati broj veći od 1, filtar će i dalje raditi ispravno.
Da biste izričito forsirali TRUE i FALSE rezultate, možete upotrijebiti "> 0" ovako:
=FILTER(list1,COUNTIF(list2,list1)>0)
Uklonite duplikate ili sortirajte
Da biste uklonili duplikate, samo ugnijezdi formulu unutar funkcije UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Da biste sortirali rezultate, ugnijezdi se u funkciji SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Vrijednosti popisa nedostaju na popisu2
Za izlaz vrijednosti u listi1 koja nedostaje s liste2, logiku možete obrnuti ovako:
=FILTER(list1,COUNTIF(list2,list1)=0)
