Formula generică
=FILTER(list1,COUNTIF(list2,list1))
rezumat
Pentru a filtra datele pentru a extrage valorile potrivite în două liste, puteți utiliza funcția FILTER și funcția COUNTIF sau COUNTIFS. În exemplul prezentat, formula din F5 este:
=FILTER(list1,COUNTIF(list2,list1))
unde list1 (B5: B16) și list2 (D5: D14) sunt denumite intervale. Rezultatul returnat de FILTER include numai valorile din list1 care apar în list2 .
Notă: FILTER este o nouă funcție de matrice dinamică în Excel 365.
Explicaţie
Această formulă se bazează pe funcția FILTER pentru a prelua date pe baza unui test logic construit cu funcția COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
funcționând din interior spre exterior, funcția COUNTIF este utilizată pentru a crea filtrul real:
COUNTIF(list2,list1)
Observați că folosim list2 ca argument al intervalului și list1 ca argument al criteriului. Cu alte cuvinte, îi cerem lui COUNTIF să numere toate valorile din lista1 care apar în lista2. Deoarece oferim COUNTIF valori multiple pentru criterii, primim înapoi o matrice cu rezultate multiple:
(1;1;0;1;0;1;0;0;1;0;1;1)
Rețineți că matricea conține 12 numărări, câte una pentru fiecare valoare din list1 . O valoare zero indică o valoare din lista1 care nu se găsește în lista2 . Orice alt număr pozitiv indică o valoare din lista1 care se găsește în lista2 . Această matrice este returnată direct la funcția FILTER ca argument include:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Funcția de filtrare folosește tabloul ca filtru. Orice valoare din lista1 asociată cu un zero este eliminată, în timp ce orice valoare asociată cu un număr pozitiv supraviețuiește.
Rezultatul este o serie de 7 valori potrivite care se varsă în intervalul F5: F11. Dacă datele se schimbă, FILTER va recalcula și va returna o nouă listă de valori potrivite pe baza noilor date.
Valori care nu se potrivesc
Pentru a extrage valori care nu se potrivesc din listă1 (adică valori din listă1 care nu apar în listă2 ) puteți adăuga funcția NU la formulă astfel:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funcția NOT inversează efectiv rezultatul din COUNTIF - orice număr diferit de zero devine FALS și orice valoare zero devine ADEVĂRATĂ. Rezultatul este o listă a valorilor din list1 care nu sunt prezente în list2 .
Cu INDEX
Este posibil să creați o formulă pentru a extrage valorile potrivite fără funcția FILTER, dar formula este mai complexă. O opțiune este utilizarea funcției INDEX într-o formulă de genul acesta:
Formula din G5, copiată este:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Nucleul acestei formule este funcția INDEX, care primește list1 ca argument al matricei. Majoritatea formulei rămase calculează pur și simplu numărul rândului pe care să îl utilizați pentru potrivirea valorilor. Această expresie generează o listă de numere de rând relative:
ROW(list1)-ROW(INDEX(list1,1,1))+1
care returnează o matrice de 12 numere reprezentând rândurile din lista1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Acestea sunt filtrate cu funcția IF și cu aceeași logică folosită mai sus în FILTER, pe baza funcției COUNTIF:
COUNTIF(list2,list1) // find matching values
Matricea rezultată arată astfel:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Această matrice este livrată direct la funcția SMALL, care este utilizată pentru a prelua următorul număr de rând care se potrivește pe măsură ce formula este copiată în coloană. Valoarea k pentru SMALL (cred că nth) este calculată cu un interval de expansiune:
ROWS($G$5:G5) // incrementing value for k
Funcția IFERROR este utilizată pentru a capta erorile care apar atunci când formula este copiată și rămâne fără valori potrivite. Pentru un alt exemplu al acestei idei, a se vedea această formulă.
