Splošna formula
=FILTER(list1,COUNTIF(list2,list1))
Povzetek
Če želite filtrirati podatke za izločanje ujemajočih se vrednosti na dveh seznamih, lahko uporabite funkcijo FILTER in funkcijo COUNTIF ali COUNTIFS. V prikazanem primeru je formula v F5:
=FILTER(list1,COUNTIF(list2,list1))
kjer sta seznama1 (B5: B16) in seznam2 (D5: D14) imenovana obsega. Rezultat, ki ga vrne FILTER, vključuje samo vrednosti v seznamu1, ki so prikazane v seznamu2 .
Opomba: FILTER je nova funkcija dinamičnega polja v programu Excel 365.
Pojasnilo
Ta formula se opira na funkcijo FILTER za pridobivanje podatkov na podlagi logičnega testa, zgrajenega s funkcijo COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
pri delu od znotraj navzven se funkcija COUNTIF uporablja za ustvarjanje dejanskega filtra:
COUNTIF(list2,list1)
Opazili smo z Seznam2 kot argument območju, in List1 kot argument meril. Z drugimi besedami, od COUNTIF-a zahtevamo, da prešteje vse vrednosti na seznamu1, ki se pojavijo na seznamu2. Ker za kriterije damo COUNTIF več vrednosti, vrnemo matriko z več rezultati:
(1;1;0;1;0;1;0;0;1;0;1;1)
Upoštevajte, da matrika vsebuje 12 odštevanj , po eno za vsako vrednost v seznamu1 . Ničelna vrednost označuje vrednost na seznamu1, ki je ni mogoče najti na seznamu2 . Katero koli drugo pozitivno število pomeni vrednost v seznamu1, ki ga najdemo v seznamu2 . Ta matrika se vrne neposredno v funkcijo FILTER kot argument za vključitev:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Funkcija filtra uporablja matriko kot filter. Katera koli vrednost v seznamu1, povezana z ničlo, se odstrani, medtem ko se ohrani vrednost, povezana s pozitivnim številom.
Rezultat je niz 7 ujemajočih se vrednosti, ki se prelijejo v obseg F5: F11. Če se podatki spremenijo, bo FILTER na podlagi novih podatkov preračunal in vrnil nov seznam ustreznih vrednosti.
Neujemajoče se vrednosti
Če želite iz seznama1 izvleči neujemajoče se vrednosti (tj. Vrednosti na seznamu1, ki se ne prikažejo na seznamu2 ), lahko formuli dodate funkcijo, kot je ta:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkcija NOT učinkovito razveljavi rezultat iz COUNTIF - vsako ne-ničlo število postane FALSE in vsaka nič-vrednost postane TRUE. Rezultat je seznam vrednosti v seznamu1, ki niso na seznamu2 .
Z INDEX
Možno je ustvariti formulo za pridobivanje ujemajočih se vrednosti brez funkcije FILTER, vendar je formula bolj zapletena. Ena od možnosti je uporaba funkcije INDEX v taki formuli:
Kopirana formula v G5 je:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Jedro te formule je funkcija INDEX, ki prejme list1 kot argument matrike. Večina preostale formule preprosto izračuna številko vrstice za ujemanje vrednosti. Ta izraz ustvari seznam relativnih številk vrstic:
ROW(list1)-ROW(INDEX(list1,1,1))+1
ki vrne matriko 12 številk, ki predstavljajo vrstice v seznamu1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Ti se filtrirajo s funkcijo IF in isto logiko, ki je bila uporabljena zgoraj v FILTER, na podlagi funkcije COUNTIF:
COUNTIF(list2,list1) // find matching values
Nastala matrika je videti tako:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Ta matrika je dostavljena neposredno v funkcijo SMALL, ki se uporablja za pridobivanje naslednje ujemajoče se številke vrstice, ko se formula kopira v stolpec. Vrednost k za SMALL (mislim nth) se izračuna z razširjenim obsegom:
ROWS($G$5:G5) // incrementing value for k
Funkcija IFERROR se uporablja za zajemanje napak, ki se pojavijo, ko se formula kopira in zmanjka ujemajočih se vrednosti. Za drug primer te ideje glejte to formulo.
