Általános képlet
=FILTER(list1,COUNTIF(list2,list1))
Összegzés
Az adatok szűréséhez az egyező értékek két listában történő kibontásához használhatja a SZŰRŐ és a COUNTIF vagy COUNTIFS függvényeket. A bemutatott példában az F5 képlete a következő:
=FILTER(list1,COUNTIF(list2,list1))
ahol a list1 (B5: B16) és a list2 (D5: D14) tartományokat nevezik meg. A FILTER által visszaadott eredmény csak a list1 értékeit tartalmazza, amelyek megjelennek a list2-ben .
Megjegyzés: A SZŰRŐ egy új dinamikus tömbfüggvény az Excel 365-ben.
Magyarázat
Ez a képlet a SZŰRŐ függvényre támaszkodik az adatok lekérdezéséhez a COUNTIF függvénnyel felépített logikai teszt alapján:
=FILTER(list1,COUNTIF(list2,list1))
belülről kifelé haladva a COUNTIF függvény használható a tényleges szűrő létrehozására:
COUNTIF(list2,list1)
Figyeljük meg, hogy a list2 argumentumot használjuk a tartomány argumentumaként, és a list1 kritérium argumentumként. Más szóval, azt kérjük COUNTIF számolni minden érték list1 megjelenő list2. Mivel a COUNTIF számára több értéket adunk meg a feltételekhez, egy tömböt kapunk vissza, amelynek több eredménye van:
(1;1;0;1;0;1;0;0;1;0;1;1)
Vegye figyelembe, hogy a tömb 12 számot tartalmaz, egyet a list1 minden értékéhez . A nulla érték a list1 olyan értékét jelzi , amely nem található meg a 2. listában . Bármely más pozitív szám a list1 értékét jelzi , amely megtalálható a list2-ben . Ez a tömb közvetlenül a FILTER függvényhez kerül vissza, az include argumentumként:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
A szűrő funkció a tömböt használja szűrőként. A listában a nullához társított értékek törlődnek, míg a pozitív számhoz társított értékek fennmaradnak.
Az eredmény 7 egyező érték tömb, amely az F5: F11 tartományba ömlik. Ha az adatok megváltoznak, a FILTER újraszámolja, és az új adatok alapján új listát ad a megfelelő értékekről.
Nem egyező értékek
A nem egyező értékek kivonásához a list1-ből (azaz az 1. listában szereplő értékek , amelyek nem jelennek meg a 2. listában ) hozzáadhatja a NOT függvényt a képlethez:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
A NOT függvény hatékonyan megfordítja a COUNTIF eredményét - minden nem nulla szám HAMIS, és minden nulla érték IGAZ lesz. Az eredmény a list1 azon értékeinek felsorolása , amelyek nincsenek benne a list2-ben .
INDEX-szel
Létrehozhat egy képletet az egyező értékek kinyerésére a SZŰRŐ funkció nélkül, de a képlet összetettebb. Az egyik lehetőség az INDEX függvény használata egy ilyen képletben:
A G5 képlete, lemásolva:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Ennek a képletnek a lényege az INDEX függvény, amely a list1- et kapja meg tömb argumentumként. A fennmaradó képlet nagy része egyszerűen kiszámítja az értékek egyezéséhez használt sorszámot. Ez a kifejezés a relatív sorszámok listáját generálja:
ROW(list1)-ROW(INDEX(list1,1,1))+1
amely egy 12 számból álló tömböt ad vissza, amelyek a list1 sorait reprezentálják :
(1;2;3;4;5;6;7;8;9;10;11;12)
Ezeket az IF függvénnyel és a FILTER-ben fent használt logikával szűrjük a COUNTIF függvény alapján:
COUNTIF(list2,list1) // find matching values
A kapott tömb így néz ki:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Ezt a tömböt közvetlenül a SMALL függvényhez juttatjuk el, amely a következő egyező sorszám beolvasására szolgál, amint a képlet az oszlopba másolódik. A SMALL k-értékét (gondoljuk az n-edikre) táguló tartományban számoljuk:
ROWS($G$5:G5) // incrementing value for k
Az IFERROR függvény olyan hibák befogására szolgál, amelyek akkor fordulnak elő, amikor a képletet lemásolják, és kifognak az egyező értékek. Ennek az ötletnek egy másik példáját lásd ebben a képletben.
