- Excel szűrő funkció - szintaxis
- 1. példa: Adatok szűrése egy kritérium (régió) alapján
- 2. példa: Adatok szűrése egy kritérium alapján (több vagy kevesebb)
- 3. példa: Adatok szűrése több kritériummal (AND)
- 4. példa: Adatok szűrése több kritériummal (OR)
- 5. példa: Az adatok szűrése az átlag feletti/alatti rekordok eléréséhez
- 6. példa: Csak az EVEN számrekordok (vagy ODD számrekordok) szűrése
- 7. példa: Rendezze a szűrt adatokat képlettel
Videó megtekintése - Példák az Excel szűrő funkcióira
Az Office 365 fantasztikus funkciókat kínál - például XLOOKUP, RENDEZÉS és SZŰRÉS.
Ami az adatok Excelben történő szűrését illeti, az Office 365 előtti világban leginkább az Excel beépített szűrőjétől vagy a speciális szűrőtől vagy a komplex SUMPRODUCT képletektől függünk. Abban az esetben, ha az adathalmaz egy részét szűrni kellett, az általában egy összetett megoldás volt (ezt itt leírtam).
De az új FILTER funkcióval most nagyon könnyű gyorsan szűrni az adatkészlet egy részét egy feltétel alapján.
És ebben az oktatóanyagban megmutatom, milyen fantasztikus az új FILTER funkció és néhány hasznos dolog, amit ezzel megtehet.
Mielőtt azonban belekezdenék a példákba, gyorsan ismerkedjünk meg a FILTER függvény szintaxisával.
Ha szeretné megszerezni ezeket az új funkciókat az Excelben, megteheti frissítsen az Office 365 -re (csatlakozzon a bennfentes programhoz, hogy hozzáférjen az összes funkcióhoz/képlethez)Excel szűrő funkció - szintaxis
Az alábbiakban a FILTER függvény szintaxisa látható:
= FILTER (tömb, tartalmazza, [if_empty])
- sor - ez a cellatartomány, ahol megvannak az adatok, és ki akar szűrni belőle néhány adatot
- tartalmazza - ez az a feltétel, amely megmondja a függvénynek, hogy milyen rekordokat kell szűrni
- [if_empty] - ez egy opcionális argumentum, ahol megadhatja, hogy mit adjon vissza, ha a FILTER funkció nem talál eredményt. Alapértelmezés szerint (ha nincs megadva) a #CALC értéket adja vissza! hiba
Most nézzünk meg néhány elképesztő szűrőfunkciós példát és olyan dolgokat, amelyeket képes elvégezni, amelyek hiányában korábban meglehetősen bonyolultak voltak.
Kattintson ide a Példa fájl letöltéséhez, és kövesse
1. példa: Adatok szűrése egy kritérium (régió) alapján
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és az összes rekordot csak az Egyesült Államokra szeretné szűrni.
Az alábbiakban látható a SZŰRŐ képlet, amely ezt teszi:
= SZŰRŐ ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "US")
A fenti képlet az adatkészletet használja tömbként, és a feltétel $ B $ 2: $ B $ 11 = ”US”
Ez a feltétel arra kényszerítené a SZŰRŐ függvényt, hogy ellenőrizze a B oszlop minden celláját (amely régióval rendelkezik), és csak azokat a rekordokat szűrjük, amelyek megfelelnek ennek a feltételnek.
Továbbá, ebben a példában az eredeti adatok és a szűrt adatok ugyanazon a lapon vannak, de ezeket külön lapokban vagy akár munkafüzetekben is megtalálhatja.
A szűrőfunkció egy dinamikus tömb eredményt ad vissza (ami azt jelenti, hogy egy érték visszaadása helyett egy tömböt ad vissza, amely más cellákba ömlik).
Ahhoz, hogy ez működjön, rendelkeznie kell egy olyan területtel, ahol az eredmény üres lesz. A terület bármely cellájában (ebben a példában E2: G5) már van valami, a függvény megadja a #SPILL hibát.
Továbbá, mivel ez egy dinamikus tömb, nem módosíthatja az eredmény egy részét. Törölheti az eredményt tartalmazó teljes tartományt, vagy az E2 cellát (ahol a képletet megadta). Mindkettő törli a teljes kapott tömböt. De egyetlen cellát sem módosíthat (vagy nem törölhet).
A fenti képletben keményen kódoltam a régió értékét, de azt is megadhatja egy cellában, majd hivatkozhat arra a cellára, amely rendelkezik a régió értékével.
Például az alábbi példában a régió értéke van az I2 cellában, és erre hivatkozik a képlet:
= SZŰRŐ ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)
Ez még hasznosabbá teszi a képletet, és most egyszerűen megváltoztathatja a régió értékét az I2 cellában, és a szűrő automatikusan megváltozik.
Az I2 cellában is van egy legördülő menü, ahol egyszerűen kiválaszthatja, és azonnal frissíti a szűrt adatokat.
2. példa: Adatok szűrése egy kritérium alapján (több vagy kevesebb)
Használhat összehasonlító operátorokat is a szűrőfunkción belül, és kinyerheti az összes rekordot, amelyek több vagy kevesebbek, mint egy adott érték.
Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, és szűrni szeretné az összes rekordot, ahol az értékesítési érték meghaladja a 10000 -at.
Ezt az alábbi képlet teheti meg:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))
A tömb argumentum a teljes adatkészletre vonatkozik, és a feltétel ebben az esetben ($ C $ 2: $ C $ 11> 10000).
A képlet minden rekordot ellenőriz a C oszlopban lévő értéken. Ha az érték több mint 10000, akkor a rendszer kiszűri, ellenkező esetben figyelmen kívül hagyja.
Ha az összes rekordot 10000 -nál kevesebbre szeretné elérni, akkor használja az alábbi képletet:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))
A FILTER képlet segítségével kreatívabbá is válhat. Ha például az értékesítési érték alapján szeretné szűrni a három legjobb rekordot, akkor használja az alábbi képletet:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = NAGY (C2: C11,3)))
A fenti képlet a LARGE függvényt használja az adathalmaz harmadik legnagyobb értékének megszerzésére. Ezt az értéket ezután a FILTER függvényfeltételekben használják, hogy megkapják az összes rekordot, ahol az értékesítési érték nagyobb vagy egyenlő a harmadik legnagyobb értékkel.
Kattintson ide a Példa fájl letöltéséhez, és kövesse
3. példa: Adatok szűrése több kritériummal (AND)
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és szűrni szeretné az Egyesült Államok összes rekordját, ahol az értékesítési érték több mint 10000.
Ez egy ÉS feltétel, ahol két dolgot kell ellenőriznie - a régiónak az USA -ba kell, és az értékesítésnek több mint 10000 -nak kell lennie. Ha csak egy feltétel teljesül, az eredményeket nem szabad szűrni.
Az alábbiakban látható a SZŰRŐ képlet, amely az Egyesült Államokat mint régiót és több mint 10000 eladást tartalmazó rekordokat szűri:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000))
Ne feledje, hogy a feltétel (az úgynevezett include argumentum): ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)
Mivel két feltételt használok, és mindkettőnek igaznak kell lennie, ezért a szorzó operátort használtam e két feltétel összekapcsolására. Ez 0 és 1 tömböt ad vissza, ahol az 1 csak akkor kerül visszaadásra, ha mindkét feltétel teljesül.
Abban az esetben, ha nincs olyan rekord, amely megfelel a feltételeknek, a függvény a #CALC értéket adja vissza! hiba.
És ha valami jelentést szeretne visszaadni (a hiba helyett), akkor az alábbi képletet használhatja:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA"))*($ C $ 2: $ C $ 11> 10000), "Semmi sem található")
Itt a „Not Found” -t használtam harmadik érvként, amelyet akkor használunk, ha nem találunk a kritériumoknak megfelelő rekordokat.
4. példa: Adatok szűrése több kritériummal (OR)
Módosíthatja az „include” argumentumot a FILTER függvényben is, hogy ellenőrizze a VAGY feltételeket (ahol a megadott feltételek bármelyike igaz lehet).
Tegyük fel például, hogy rendelkezik az alábbi ábra szerinti adatkészlettel, és szeretné szűrni azokat a rekordokat, ahol az ország az Egyesült Államok vagy Kanada.
Az alábbiakban ezt a képletet találja meg:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "Kanada"))
Vegye figyelembe, hogy a fenti képletben egyszerűen hozzáadtam a két feltételt az összeadási operátor használatával. Mivel ezek a feltételek mindegyike IGAZ és HAMIS tömböt ad vissza, hozzá tudok adni egy kombinált tömböt, ahol IGAZ, ha valamelyik feltétel teljesül.
Egy másik példa lehet az, amikor az összes rekordot szűrni szeretné, ahol az ország az Egyesült Államok, vagy az értékesítési érték több mint 10000.
Az alábbi képlet ezt teszi:
= SZŰRŐ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))
Megjegyzés: Amikor ÉS kritériumokat használ a SZŰRŐ függvényben, használja a szorzó operátort (*), és ha a VAGY feltételeket, akkor használja az összeadás operátort (+).
5. példa: Az adatok szűrése az átlag feletti/alatti rekordok eléréséhez
A SZŰRŐ funkción belüli képletekkel szűrheti és kinyerheti azokat a rekordokat, ahol az érték az átlag felett vagy alatt van.
Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, és szűrni szeretné az összes rekordot, ahol az eladási érték átlag feletti.
Ezt a következő képlet segítségével teheti meg:
= SZŰRŐ ($ A $ 2: $ C $ 11, C2: C11> ÁTLAG (C2: C11))
Hasonlóképpen, az átlag alatti értékekhez használhatja az alábbi képletet:
= SZŰRŐ ($ A $ 2: $ C $ 11, C2: C11<>
Kattintson ide a Példa fájl letöltéséhez, és kövesse
6. példa: Csak az EVEN számrekordok (vagy ODD számrekordok) szűrése
Abban az esetben, ha gyorsan ki kell szűrnie és ki kell vonnia az összes rekordot a páros vagy páratlan számsorokból, ezt megteheti a FILTER funkcióval.
Ehhez ellenőriznie kell a FILTER funkción belüli sorszámot, és csak azokat a sorszámokat kell szűrnie, amelyek megfelelnek a sorszám feltételnek.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és csak páros számú rekordokat akarok kinyerni ebből az adatkészletből.
Az alábbiakban ezt a képletet találja meg:
= SZŰRŐ ($ A $ 2: $ C $ 11, MOD (SOR (A2: A11) -1,2) = 0)
A fenti képlet a MOD funkciót használja az egyes rekordok sorszámának ellenőrzésére (amelyet a ROW függvény ad meg).
A MOD (SOR (A2: A11) -1,2) = 0 képlet IGAZ értéket ad vissza, ha a sorszám páros, és hamis, ha páratlan. Ne feledje, hogy kivettem 1 -et a ROW (A2: A11) részből, mivel az első rekord a második sorban van, és ez beállítja a sorszámot, hogy a második sort az első rekordnak tekintse.
Hasonlóképpen szűrheti az összes páratlan rekordot az alábbi képlet segítségével:
= SZŰRŐ ($ A $ 2: $ C $ 11, MOD (SOR (A2: A11) -1,2) = 1)
7. példa: Rendezze a szűrt adatokat képlettel
Ha a FILTER funkciót más funkciókkal együtt használja, sokkal többet tehetünk.
Például, ha egy adathalmazt szűr a FILTER függvénnyel, akkor a RENDELÉS függvénnyel használhatja a már rendezett eredményt.
Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, és szűrni szeretné az összes rekordot, ahol az értékesítési érték meghaladja a 10000 -at. A RENDEZÉS funkcióval a függvénnyel ellenőrizheti, hogy a kapott adatok az értékesítési érték alapján vannak -e rendezve.
Az alábbi képlet ezt teszi:
= RENDELÉS (SZŰRÉS ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)
A fenti függvény a FILTER függvényt használja azoknak az adatoknak a beszerzésére, ahol a C oszlop eladási értéke meghaladja a 10000 -at. Ezt a FILTER függvény által visszaadott tömböt a SORT függvény használja az adatok rendezésére az értékesítési érték alapján.
A RENDEZÉS függvény második argumentuma a 3, amely a harmadik oszlop alapján rendezendő. A negyedik argumentum pedig -1, azaz csökkenő sorrendbe kell rendezni ezeket az adatokat.
Kattintson ide a Példa fájl letöltéséhez
Tehát ez a 7 példa a FILTER funkció használatára az Excelben.
Remélem, hasznosnak találta ezt az oktatóanyagot!
A következő Excel oktatóanyagok is tetszhetnek:
- A cellák szűrése vastag betűformázással Excelben
- Dinamikus Excel szűrő keresőmező
- Hogyan lehet szűrni adatokat az Excel pivot táblájában?
