Formula generică
=FILTER(list1,COUNTIF(list2,list1))
rezumat
Pentru a compara două liste și a extrage valori comune, puteți utiliza o formulă bazată pe funcțiile FILTER și COUNTIF. În exemplul prezentat, formula din F5 este:
=FILTER(list1,COUNTIF(list2,list1))
unde list1 (B5: B15) și list2 (D5: D13) sunt denumite intervale. Rezultatul, valorile care apar în ambele liste, se revarsă în intervalul F5: F11.
Explicaţie
Funcția FILTER acceptă o matrice de valori și un argument „include” care filtrează matricea pe baza unei expresii logice sau a unei valori.
În acest caz, matricea este furnizată ca interval numit „list1”, care conține toate valorile din B5: B15. Includ argumentul este livrat de funcția COUNTIF, care este încorporată în interiorul filtrului:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF este configurat cu list2 ca interval și list1 ca criteriu . Deoarece oferim COUNTIF unsprezece valori de criteriu, COUNTIF returnează unsprezece rezultate într-o matrice ca aceasta:
(1;1;0;1;0;1;0;1;0;1;1)
Observați că valorile 1 corespund articolelor din lista2 care apar în lista1.
Această matrice este livrată direct la funcția FILTER ca argument "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Funcția FILTRU filtrează lista1 folosind valorile furnizate de COUNTIF. Valorile asociate cu zero sunt eliminate; se păstrează alte valori.
Rezultatul final este o serie de valori care există în ambele liste, care se revarsă în intervalul F5: F11.
Logică extinsă
În formula de mai sus, folosim rezultatele brute de la COUNTIF ca filtru. Acest lucru funcționează deoarece Excel evaluează orice valoare diferită de zero ca ADEVĂRAT și zero ca FALS. Dacă COUNTIF returnează un număr mai mare de 1, filtrul va funcționa în continuare corect.
Pentru a forța rezultatele ADEVĂRATE și FALSE în mod explicit, puteți utiliza „> 0” astfel:
=FILTER(list1,COUNTIF(list2,list1)>0)
Eliminați duplicatele sau sortați
Pentru a elimina duplicatele, trebuie doar să cuibăriți formula în funcția UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Pentru a sorta rezultatele, cuibăriți în funcția SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Valorile listei lipsesc din list2
Pentru a afișa valorile din lista1 lipsă din lista2, puteți inversa logica astfel:
=FILTER(list1,COUNTIF(list2,list1)=0)
