Az Excel FILTER funkciójának használata

Az Office 365 olyan nagyszerű funkciókat kínál, mint az XLOOKUP, a SORT és a FILTER.

Az adatok Excelben történő szűrésekor az Office 365 előtti világban leginkább az Excel beépített szűrőire, vagy legfeljebb fejlett szűrőkre vagy összetett SUMPRODUCT képletekre hagyatkoztunk.Ez gyakran bonyolult megoldás, ha szűrnie kell az adatkészlet egy részét.

De az új SZŰRŐ funkcióval most már nagyon egyszerű az adatkészlet egyes részeinek gyors szűrése a feltételek alapján.

Ebben az oktatóanyagban megmutatom, milyen fantasztikus az új SZŰRŐ funkció, és néhány hasznos dolgot, amit megtehet vele.

Mielőtt azonban rátérnék a példára, vessünk egy pillantást a FILTER függvény szintaxisára.

Ha szeretné elérni ezeket az új funkciókat az Excelben, megtehetiFrissítsen Office 365-re(Csatlakozzon a belső programhoz az összes funkció/képlet eléréséhez)

Excel szűrő funkció – Szintaxis

A következő a FILTER függvény szintaxisa:

=SZŰRŐ(tömb,tartalmazza,[ha_üres])

• sor - ez az a cellatartomány, ahol az adatok vannak, és ki akarsz szűrni belőle néhány adatot
• tartalmaz - Ez az a feltétel, amely megmondja a függvénynek, hogy mely rekordokat kell szűrni
• [ha_üres] – Ez egy opcionális paraméter, ahol megadhatja, hogy mit adjon vissza, ha a FILTER függvény nem talál eredményt.Alapértelmezés szerint (ha nincs megadva) a #CALC!hiba

Most nézzünk meg néhány elképesztő példát a szűrőfunkciókra, és arra, hogy mire képes, ami korábban nagyon bonyolult volt nélküle.

1. példa: Adatok szűrése egy feltétel (régió) alapján

Tegyük fel, hogy az alábbihoz hasonló adatkészlettel rendelkezik, és csak az Egyesült Államokban lévő összes rekordot szeretné szűrni.

Íme a FILTER képlet, amely ezt teszi:

=SZŰRŐ($A$2:$C$11,$B$2:$B$11="US")

A fenti képlet tömbként veszi az adatkészletet, és a feltétel: $B$2:$B$11=”US”

Ez a feltétel azt eredményezi, hogy a SZŰRŐ függvény minden cellát ellenőriz a B oszlopban (a tartományt tartalmazó cellák), és csak azokat a rekordokat szűri, amelyek megfelelnek ennek a feltételnek.

Ebben a példában is ugyanarra a lapra tettem fel az eredeti és a szűrt adatokat, de külön lapokba vagy akár munkafüzetekbe is rakhatod őket.

A szűrőfüggvény az eredmények dinamikus tömbjét adja vissza (ami azt jelenti, hogy ahelyett, hogy értéket adna vissza, egy olyan tömböt ad vissza, amely túlcsordul más cellákba).

Ehhez olyan területre van szükség, ahol az eredmény üres.A tartomány bármelyik cellájában már van valami (ebben a példában E2:G5), és a függvény a #SPILL hibát adja ki.

Ezenkívül, mivel ez egy dinamikus tömb, nem módosíthatja az eredmény egy részét.A teljes tartományt törölheti az eredménnyel vagy az E2 cellával (ahova a képletet beírjuk).Mindkettő törli a teljes eredménytömböt.De egyetlen cellát sem módosíthat (vagy törölhet).

A fenti képletben a tartomány értékét keményen kódoltam, de be is rakhatod egy cellába, és hivatkozhatsz arra a cellára a tartomány értékével.

Például az alábbi példában a tartomány értéke az I2 cellában van, majd hivatkozom rá a képletben:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Ez még hasznosabbá teszi a képletet, most már csak az I2 cellában kell megváltoztatni a tartomány értékét, és a szűrő automatikusan megváltozik.

Az I2 cellában lehet egy legördülő menü is, ahol egyszerűen kiválaszthat egyet, és azonnal frissíti a szűrt adatokat.

2. példa: Adatok szűrése egy feltétel alapján (nagyobb vagy kisebb, mint)

Összehasonlító operátorokat is használhat a szűrőfunkciókban, és kivonhatja az összes, egy bizonyos értéknél nagyobb vagy kisebb rekordot.

Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, és ki szeretné szűrni az összes olyan rekordot, ahol az eladások száma meghaladja a 10000 XNUMX-et.

A következő képlet képes erre:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

A tömb argumentum a teljes adatkészletre vonatkozik, ebben az esetben a feltételre ($C$2:$C$11>10000).

A képlet minden rekordban ellenőrzi a C oszlopban lévő értéket.Ha az érték nagyobb, mint 10000, akkor a rendszer kiszűri, ellenkező esetben figyelmen kívül hagyja.

Ha azt szeretné, hogy az összes rekord 10000 XNUMX-nél kisebb legyen, használja a következő képletet:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

A FILTER formulával kreatívabb is lehet.Például, ha az első három rekordot eladások alapján szeretné szűrni, használhatja a következő képletet:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

A fenti képlet a LARGE függvényt használja, hogy megkapja az adatkészlet harmadik legnagyobb értékét.Ezután használja ezt az értéket egy SZŰRŐ függvényfeltételben, hogy megkapja az összes olyan rekordot, amelynek értékesítése nagyobb vagy egyenlő a harmadik legnagyobb értékkel.

3. példa: Adatszűrés több feltétel használatával (ÉS)

Tegyük fel, hogy rendelkezik a következő adatkészlettel, és ki szeretné szűrni az összes olyan rekordot az Egyesült Államokban, amelyek értékesítési értéke meghaladja a 10000 XNUMX-et.

Ez egy ÉS feltétel, két dolgot kell ellenőriznie – a régiónak az Egyesült Államokban kell lennie, és az eladásoknak 10000 XNUMX felett kell lenniük.Ha csak egy feltétel teljesül, az eredményeket nem szabad szűrni.

Íme a szűrőképlet, amely kiszűri a rekordokat az Egyesült Államok régiójával és több mint 10000 XNUMX értékesítéssel:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Mivel két feltételt használtam, és mindkettőnek igaznak kellett lennie, a szorzási operátorral kombináltam a két feltételt.Ez egy 0-ból és 1-ből álló tömböt ad vissza, ahol 1-et csak akkor ad vissza, ha mindkét feltétel teljesül.

Ha nincsenek egyező rekordok, a függvény a #CALC!hiba.

Ha valami értelmeset (nem hibát) szeretne visszaadni, használhatja a következő képletet:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Itt a "not found"-t használtam harmadik paraméterként, amelyet akkor használunk, ha nem található egyező rekord.

4. példa: Adatszűrés több feltétel használatával (VAGY)

Módosíthatja a "contains" paramétert is a FILTER függvényben, hogy ellenőrizze a VAGY feltételeket (ahol bármely adott feltétel igaz lehet).

Tegyük fel például, hogy rendelkezik az alábbi adatkészlettel, és olyan rekordokat szeretne szűrni, ahol az ország az Egyesült Államok vagy Kanada.

Íme a képlet ennek elvégzéséhez:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Vegye figyelembe, hogy a fenti képletben csak a két feltételt adom hozzá az addíciós operátor segítségével.Mivel ezek a feltételek mindegyike egy TRUE és FALSE tömböt ad vissza, hozzáadhatok egy kombinált tömböt, amely IGAZ lesz, ha bármelyik feltétel teljesül.

Egy másik példa lehet, amikor az összes olyan rekordot ki szeretné szűrni, ahol az ország az Egyesült Államok, vagy az értékesítési érték meghaladja az 10000 XNUMX-et.

A következő képlet fogja ezt megtenni:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

MEGJEGYZÉS: Ha a SZŰRŐ funkcióban az ÉS feltételt használja, használja a szorzó operátort (*), a VAGY feltétel használatakor pedig az összeadás operátort (+).

5. példa: Adatok szűrése átlag feletti/alatti rekordokhoz

A FILTER funkció képleteivel szűrheti és kinyerheti az átlag feletti vagy alatti értékű rekordokat.

Tegyük fel például, hogy rendelkezik az alábbi adatkészlettel, és ki szeretné szűrni az összes olyan rekordot, amelynek értékesítési értéke meghaladja az átlagot.

Ezt a következő képlettel teheti meg:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Ismét az átlag alatti képletet használhatja:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

6. példa: Csak a páros rekordok szűrése (vagy a páratlan rekordok)

Ha gyorsan ki kell szűrnie és ki kell bontania a páros vagy páratlan sorok összes rekordját, használhatja a SZŰRŐ funkciót.

Ehhez ellenőrizni kell a sorszámot a SZŰRŐ funkcióban, és csak azokat a sorszámokat kell szűrni, amelyek megfelelnek a sorszám-feltételnek.

Tegyük fel, hogy van egy adatkészlete, mint az alábbi, és csak rekordokat akarok kinyerni ebből az adatkészletből.

Íme a képlet ennek elvégzéséhez:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

A fenti képlet a MOD függvényt használja az egyes rekordok sorszámának (a ROW függvény által adott) ellenőrzésére.

A MOD(ROW(A2:A11)-1,2)=0 képlet IGAZ értéket ad vissza, ha a sor száma páros, és HAMIS értéket, ha páratlan.Vegye figyelembe, hogy a ROW(A2:A11) részből kivontam 1-et, mert az első rekord a második sorban van, ami úgy módosítja a sorszámokat, hogy a második sort az első rekordként kezelje.

Hasonlóképpen szűrheti az összes páratlan rekordot a következő képlettel:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

7. példa: Szűrt adatok rendezése képlet segítségével

A SZŰRŐ funkció más funkciókkal való használata lehetővé teszi számunkra, hogy többet tegyünk.

Például, ha a SZŰRŐ funkciót használja egy adatkészlet szűrésére, akkor a SORT függvény segítségével rendezett eredményeket kaphat.

Tegyük fel, hogy van egy adatkészlete az alábbiak szerint, és ki szeretné szűrni az összes olyan rekordot, ahol az eladások száma meghaladja az 10000-et.Ezzel a funkcióval a RENDEZÉS funkciót használhatja annak biztosítására, hogy az eredményül kapott adatok az eladások szerint legyenek rendezve.

A következő képlet fogja ezt megtenni:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

A fenti függvény a FILTER függvényt használja a C oszlop adatainak lekéréséhez 10000-nél nagyobb eladások esetén.Ezután használja a SZŰRŐ függvény által visszaadott tömböt a SORT függvényben az adatok értékesítés szerinti rendezéséhez.

A SORT függvény második paramétere a 3, ami a harmadik oszlop szerinti rendezést jelenti.A negyedik paraméter a -1, amely csökkenő sorrendbe rendezi az adatokat.

Tehát ez 7 példa a FILTER funkció használatára az Excelben.

Remélem hasznosnak találta ezt az oktatóanyagot.