Általános képlet
=FILTER(list1,COUNTIF(list2,list1))
Összegzés
Két lista összehasonlításához és a közös értékek kibontásához használjon a SZŰRŐ és a COUNTIF függvényen alapuló képletet. A bemutatott példában az F5 képlete a következő:
=FILTER(list1,COUNTIF(list2,list1))
ahol a list1 (B5: B15) és a list2 (D5: D13) tartományokat nevezik meg. Az eredmény, a mindkét listában megjelenő érték, az F5: F11 tartományba ömlik.
Magyarázat
A FILTER függvény elfogad egy értéktömböt és egy "include" argumentumot, amely egy logikai kifejezés vagy érték alapján szűri a tömböt.
Ebben az esetben a tömb a "list1" nevű tartományként van megadva, amely a B5: B15 összes értékét tartalmazza. Az include argumentumot a COUNTIF függvény szolgáltatja, amely be van ágyazva a FILTER-be:
=FILTER(list1,COUNTIF(list2,list1))
A COUNTIF úgy van beállítva, hogy a list2 a tartomány , a list1 pedig a feltétel . Mivel a COUNTIF-nek tizenegy kritériumértéket adunk meg, a COUNTIF tizenegy eredményt ad vissza egy ilyen tömbben:
(1;1;0;1;0;1;0;1;0;1;1)
Figyelje meg, hogy az 1-ek megfelelnek a 2. listában szereplő elemeknek, amelyek megjelennek a 1. listában.
Ez a tömb közvetlenül a FILTER függvénybe kerül, az "include" argumentumként:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
A SZŰRŐ funkció a COUNTIF által megadott értékek felhasználásával kiszűri az 1. listát. A nullához tartozó értékeket eltávolítjuk; egyéb értékek megmaradnak.
A végeredmény mindkét listában létező értéktömb, amely az F5: F11 tartományba ömlik.
Kiterjesztett logika
A fenti képletben a COUNTIF nyers eredményeit használjuk szűrőként. Ez azért működik, mert az Excel a nullától eltérő értékeket IGAZként, a nulla HAMISAKként értékeli. Ha a COUNTIF 1-nél nagyobb számot ad vissza, a szűrő továbbra is megfelelően fog működni.
Az IGAZ és HAMIS eredmények kifejezett kikényszerítéséhez használhatja a következőt: "> 0":
=FILTER(list1,COUNTIF(list2,list1)>0)
Távolítsa el az ismétlődéseket vagy rendezze
Az ismétlések eltávolításához egyszerűen helyezze be a képletet az UNIQUE függvénybe:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Az eredmények rendezéséhez fészkelje be a SORT funkciót:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
A listából hiányoznak a list2 értékek
Az 1. listában a 2. listából hiányzó értékek kimenetéhez a logikát így fordíthatja:
=FILTER(list1,COUNTIF(list2,list1)=0)
