Generička formula
=FILTER(list1,COUNTIF(list2,list1))
Sažetak
Da biste filtrirali podatke za izdvajanje odgovarajućih vrijednosti na dva popisa, možete koristiti funkciju FILTER i funkciju COUNTIF ili COUNTIFS. U prikazanom primjeru formula u F5 je:
=FILTER(list1,COUNTIF(list2,list1))
gdje su popis1 (B5: B16) i popis2 (D5: D14) imenovani rasponi. Rezultat koji je vratio FILTER uključuje samo vrijednosti u listi1 koje se pojavljuju u listi2 .
Napomena: FILTER je nova funkcija dinamičkog niza u programu Excel 365.
Obrazloženje
Ova se formula oslanja na funkciju FILTER za dohvaćanje podataka na temelju logičkog testa izgrađenog pomoću funkcije COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
radeći iznutra prema van, funkcija COUNTIF koristi se za stvaranje stvarnog filtra:
COUNTIF(list2,list1)
Primijetite da koristimo list2 kao argument raspona, a list1 kao argument kriterija. Drugim riječima, tražimo od COUNTIF-a da prebroji sve vrijednosti na popisu1 koje se pojavljuju na popisu2. Budući da za kriterije dajemo COUNTIF višestruke vrijednosti, vraćamo niz s više rezultata:
(1;1;0;1;0;1;0;0;1;0;1;1)
Imajte na umu da niz sadrži 12 brojanja, po jedan za svaku vrijednost u list1 . Nulta vrijednost označava vrijednost na listi1 koja nije pronađena na listi2 . Bilo koji drugi pozitivan broj označava vrijednost u listi1 koja se nalazi u listi2 . Ovaj se niz vraća izravno u funkciju FILTER kao argument uključuju:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Funkcija filtra koristi polje kao filtar. Uklanja se bilo koja vrijednost u listi1 povezana s nulom, dok svaka vrijednost povezana s pozitivnim brojem preživljava.
Rezultat je niz od 7 podudarnih vrijednosti koji se prelijevaju u raspon F5: F11. Ako se podaci promijene, FILTER će ponovno izračunati i vratiti novi popis odgovarajućih vrijednosti na temelju novih podataka.
Vrijednosti koje se ne podudaraju
Da biste iz liste1 izdvojili nepodudarajuće vrijednosti (tj. Vrijednosti na popisu1 koje se ne pojavljuju na popisu2 ), u formulu možete dodati funkciju NOT poput ove:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkcija NOT učinkovito poništava rezultat iz COUNTIF - bilo koji broj koji nije nula postaje FALSE, a svaka nulta vrijednost postaje TRUE. Rezultat je popis vrijednosti u listi1 koje nisu prisutne u listi2 .
Uz INDEX
Moguće je stvoriti formulu za izdvajanje odgovarajućih vrijednosti bez funkcije FILTER, ali formula je složenija. Jedna od mogućnosti je uporaba funkcije INDEX u formuli poput ove:
Kopirana formula u G5 je:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Jezgra ove formule je funkcija INDEX koja prima argument1 kao argument niza. Većina preostale formule jednostavno izračunava broj retka koji se koristi za podudaranje vrijednosti. Ovaj izraz generira popis relativnih brojeva redaka:
ROW(list1)-ROW(INDEX(list1,1,1))+1
koji vraća niz od 12 brojeva koji predstavljaju retke u list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Oni se filtriraju pomoću funkcije IF i iste logike korištene gore u FILTERU, na temelju funkcije COUNTIF:
COUNTIF(list2,list1) // find matching values
Dobiveni niz izgleda ovako:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Ovaj se niz isporučuje izravno funkciji SMALL koja se koristi za dohvaćanje sljedećeg odgovarajućeg broja retka dok se formula kopira niz stupac. Vrijednost k za SMALL (mislite nth) izračunava se s opsegom koji se širi:
ROWS($G$5:G5) // incrementing value for k
Funkcija IFERROR koristi se za hvatanje pogrešaka koje se javljaju kada se formula kopira i ostane bez podudarnih vrijednosti. Za drugi primjer ove ideje pogledajte ovu formulu.
