Vispārēja formula
=FILTER(list1,COUNTIF(list2,list1))
Kopsavilkums
Lai salīdzinātu divus sarakstus un iegūtu kopīgas vērtības, varat izmantot formulu, kuras pamatā ir funkcijas FILTER un COUNTIF. Parādītajā piemērā formula F5 ir:
=FILTER(list1,COUNTIF(list2,list1))
kur List1 (B5: B15) un List2 (D5: D13) ir nosaukts diapazoniem. Rezultāts, vērtības, kas parādās abos sarakstos, izplūst diapazonā F5: F11.
Paskaidrojums
Funkcija FILTER pieņem vērtību masīvu un argumentu "iekļaut", kas filtrē masīvu, pamatojoties uz loģisku izteiksmi vai vērtību.
Šajā gadījumā masīvs tiek norādīts kā nosauktais diapazons "list1", kurā ir visas vērtības B5: B15. Ietver arguments tiek piegādāts ar COUNTIF funkciju, kas ir ligzdota iekšpusē FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF ir iestatīts ar list2 kā diapazonu un list1 kā kritēriju . Tā kā mēs piešķiram COUNTIF vienpadsmit kritēriju vērtības, COUNTIF atgriež vienpadsmit rezultātus masīvā šādi:
(1;1;0;1;0;1;0;1;0;1;1)
Ievērojiet, ka 1 ir saistīti ar 2. saraksta vienumiem, kas parādās 1. sarakstā.
Šis masīvs tiek piegādāts tieši funkcijai FILTER kā arguments "iekļaut":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Funkcija FILTRS filtrē sarakstu1, izmantojot COUNTIF sniegtās vērtības. Tiek noņemtas ar nulli saistītās vērtības; tiek saglabātas citas vērtības.
Galīgais rezultāts ir vērtību masīvs, kas pastāv abos sarakstos, kas izplūst diapazonā F5: F11.
Paplašināta loģika
Iepriekš minētajā formulā kā filtru izmantojam neapstrādātus rezultātus no COUNTIF. Tas darbojas tāpēc, ka Excel jebkuru vērtību, kas nav nulle, novērtē kā PATIESU un nulli kā FALSE. Ja COUNTIF atgriež skaitli, kas lielāks par 1, filtrs joprojām darbosies pareizi.
Lai nepārprotami uzspiestu patiesus un nepatiesus rezultātus, varat izmantot “> 0” šādi:
=FILTER(list1,COUNTIF(list2,list1)>0)
Noņemiet dublikātus vai kārtojiet
Lai noņemtu dublikātus, vienkārši ievietojiet formulu funkcijā UNIKĀLĀ:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Lai kārtotu rezultātus, ievietojiet ligzdā funkciju SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Sarakstā2 trūkst saraksta vērtību
Lai izvestu 1. saraksta vērtības, kas trūkst 2. sarakstā, loģiku var mainīt šādi:
=FILTER(list1,COUNTIF(list2,list1)=0)
