Všeobecný vzorec
=FILTER(list1,COUNTIF(list2,list1))
Zhrnutie
Ak chcete porovnať dva zoznamy a extrahovať bežné hodnoty, môžete použiť vzorec založený na funkciách FILTER a COUNTIF. V zobrazenom príklade je vzorec v F5:
=FILTER(list1,COUNTIF(list2,list1))
kde list1 (B5: B15) a list2 (D5: D13) sú pomenované rozsahy. Výsledok, hodnoty, ktoré sa vyskytujú v oboch zoznamoch, sa rozlejú do rozsahu F5: F11.
Vysvetlenie
Funkcia FILTER prijíma pole hodnôt a argument „include“, ktorý filtruje pole na základe logického výrazu alebo hodnoty.
V tomto prípade je pole poskytované ako pomenovaný rozsah „list1“, ktorý obsahuje všetky hodnoty v B5: B15. Argument include je doručený funkciou COUNTIF, ktorá je vnorená do FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF je nastavený s list2 ako rozsah a list1 ako kritériá . Pretože dávame COUNTIF jedenásť hodnôt kritérií, COUNTIF vráti jedenásť výsledkov v poli ako je toto:
(1;1;0;1;0;1;0;1;0;1;1)
Všimnite si, že jedničky zodpovedajú položkám v zozname2, ktoré sa vyskytujú v zozname1.
Toto pole sa dodáva priamo do funkcie FILTER ako argument „zahrnúť“:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Funkcia FILTER filtruje zoznam1 pomocou hodnôt poskytnutých parametrom COUNTIF. Hodnoty spojené s nulou sa odstránia; ostatné hodnoty sú zachované.
Konečným výsledkom je pole hodnôt, ktoré existujú v obidvoch zoznamoch a ktoré sa šíria do rozsahu F5: F11.
Rozšírená logika
Vo vyššie uvedenom vzorci používame ako filter nespracované výsledky z COUNTIF. Funguje to, pretože program Excel vyhodnotí každú nenulovú hodnotu ako TRUE a nulu ako FALSE. Ak funkcia COUNTIF vráti počet väčší ako 1, filter bude stále fungovať správne.
Ak chcete explicitne vynútiť výsledky TRUE a FALSE, môžete použiť „> 0“ takto:
=FILTER(list1,COUNTIF(list2,list1)>0)
Odstráňte duplikáty alebo zoraďte
Ak chcete odstrániť duplikáty, stačí vnoriť vzorec do funkcie UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Ak chcete výsledky zoradiť, vložte ich do funkcie Triedenie:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
V zozname2 chýbajú hodnoty zoznamu
Ak chcete vypísať hodnoty v zozname1, ktoré chýbajú v zozname2, môžete obrátiť logiku takto:
=FILTER(list1,COUNTIF(list2,list1)=0)
