Adatok szűrése Excelben
Nemrég írtam egy cikket az összefoglaló függvények használatáról az Excelben, hogy könnyen összegezhessem a nagy mennyiségű adatot, de ez a cikk figyelembe vette a munkalap összes adatait. Mi van, ha csak az adatok egy részhalmazát szeretné megnézni, és összefoglalja az adatok részhalmazát?
Az Excel programban olyan oszlopokat hozhat létre, amelyek elrejtik azokat a sorokat, amelyek nem felelnek meg a szűrőnek. Ezenkívül speciális funkciókat is használhat az Excelben az adatok összegzésére, csak a szűrt adatok felhasználásával.
Ebben a cikkben a szűrők Excelben történő létrehozásához és a beépített funkciók használatával járunk el, hogy összefoglaljuk a szűrt adatokat.
Egyszerű szűrők létrehozása Excelben
Az Excel programban egyszerű szűrőket és összetett szűrőket hozhat létre. Kezdjük egyszerű szűrőkkel. A szűrőkkel végzett munka során mindig egy sor legyen a címkékhez. Nem szükséges ez a sor, de ez megkönnyíti a szűrőkkel való munkát.
Fölöttem van néhány hamis adataim, és szeretnék egy szűrőt létrehozni a Város oszlop. Az Excel programban ez nagyon egyszerű. Menj előre és kattints a Adat lap a szalagon, majd kattintson a Szűrő gomb. Nem kell kiválasztania az adatokat a lapon vagy az első sorban sem.
Ha a Szűrőre kattint, az első sor minden oszlopának automatikusan van egy kis legördülő gombja.
Most menj előre, és kattints a City oszlopban található legördülő nyílra. Néhány különböző lehetőséget fogsz látni, amelyeket az alábbiakban fogok megmagyarázni.
A tetején gyorsan sorba rendezheti az összes sort a City oszlopban található értékekkel. Ne feledje, hogy az adatok rendezése során az egész sort, nem csak a város oszlopában lévő értékeket mozgatja. Ez biztosítja, hogy az adatok éppúgy maradjanak, mint korábban.
Szintén jó ötlet egy oszlopot hozzáadni az azonosítóhoz, amelyet azonosítónak nevezünk, és számozd be egytől a sok munkalapon lévő sorhoz. Így mindig azonosíthatja az azonosító oszlopot, és visszaállíthatja az adatokat ugyanabban a sorrendben, mint az eredetileg, ha ez fontos az Ön számára.
Mint látható, a táblázatban lévő összes adat most a City oszlopban szereplő értékek alapján rendeződik. Eddig nincs sor rejtve. Most nézzük meg a szűrőablak alján található jelölőnégyzeteket. Példaomban csak három egyedi érték van a City oszlopban, és ezek a három a listában jelennek meg.
Elmentem, és két várost ellenőriztem, és elmentem egy ellenőrzést. Most már csak 8 sornyi adat jelenik meg, a többi pedig rejtve van. Könnyen megmondhatja, hogy a szűrt adatokra néz, ha a bal oldali sorszámokat ellenőrzi. Attól függően, hogy hány sor van rejtve, néhány extra vízszintes vonal látható, és a számok színe kék lesz.
Tegyük fel, hogy egy második oszlopon szeretnék szűrni az eredmények számának további csökkentése érdekében. A C oszlopban az egyes családok összes tagja van, és az eredményeket csak a több mint két tagú családok számára szeretném látni.
Menj előre, és kattints a C oszlopban található legördülő nyílra, és ugyanazt a jelölőnégyzetet látod az oszlop minden egyes egyedi értékéhez. Ebben az esetben azonban kattintsunk Számszűrők majd kattintson a gombra Nagyobb, mint. Mint látható, van egy csomó más lehetőség is.
Új párbeszédablak jelenik meg, és itt megadhatja a szűrő értékét. Az AND vagy OR funkcióval egynél több kritérium is hozzáadható. Azt mondhatjuk, hogy olyan sorokat szeretne, ahol az érték nagyobb, mint 2, és nem egyenlő 5-tel.
Most már csak 5 sornyi adatot használok: csak a New Orleans-i családok és 3 vagy több tag. Elég könnyű? Ne feledje, hogy könnyen törölheti a szűrőt az oszlopon, ha rákattint a legördülő listára, majd a Szűrő törlése az "Oszlop neve" link.
Tehát ez az Excel egyszerű szűrőiről szól. Nagyon könnyen használhatóak, és az eredmények meglehetősen egyszerűek. Most nézzük meg a komplex szűrőket a Fejlett szűrők párbeszédablak.
Speciális szűrők létrehozása Excelben
Ha fejlettebb szűrőket szeretne létrehozni, akkor a Fejlett szűrő párbeszédablak. Tegyük fel például, hogy minden családot szeretnék látni, akik a New Orleans-ban élnek, több mint 2 taggal a családjukban VAGY minden család a Clarksville-ben több mint 3 taggal rendelkezik családjukban ÉS csak az a .EDU az e-mail cím befejezése. Most már nem teheted ezt egy egyszerű szűrővel.
Ehhez egy kicsit másképp kell beállítanunk az Excel lapot. Menjen előre, és helyezzen be egy pár sort az adatkészlet fölé, és másolja át a címsorcímkéket az első sorba, mint az alább látható.
Most itt van a fejlett szűrők működése. Először írja be a kritériumokat a tetején lévő oszlopokba, majd kattintson a gombra Fejlett gomb alatt Rendezés és szűrés a Adat lap.
Tehát pontosan mit írhatunk be ezekbe a sejtekbe? OK, kezdjük a példánkkal. Csak a New Orleans vagy a Clarksville adatait szeretnénk látni, ezért írjuk be az E2 és E3 cellákba.
Ha az értékeket különböző sorokra írja be, akkor azt OR. Most azt szeretnénk, ha a New Orleans családok több mint két taggal rendelkeznek, és több mint 3 tagú Clarksville családok. Ehhez írja be a > 2 a C2 és > 3 C3-ban.
Mivel a> 2 és a New Orleans ugyanazon a soron van, ez egy AND operátor lesz. Ugyanez igaz a fenti 3. sorra is. Végül csak azokat a családokat akarjuk használni, amelyeknek az .EDU vége e-mail címe van. Ehhez írjon be csak * Edu D2-be és D3-ba. A * szimbólum a karakterek számát jelenti.
Ha ezt megtette, kattintson bárhová az adatkészletre, majd kattintson a Fejlett gomb. A Lista RangA mező automatikusan kitalálja az adatkészletet, miután rákattintott rá, mielőtt a Speciális gombra kattintott volna. Most kattintsunk a kis jobb oldali gombra A kritériumok tartománya gomb.
Válasszon mindent az A1-től E3-ig, majd kattintson ismét a gombra, hogy visszatérjen a Speciális szűrő párbeszédablakhoz. Kattintson az OK gombra, és az adatokat most szűrjük!
Mint látható, most már csak 3 olyan eredmény van, amely megfelel az összes ilyen kritériumnak. Ne feledje, hogy a kritériumokhoz tartozó címkéknek pontosan meg kell egyezniük az adatkészlet címkéivel, hogy ez működjön.
Ezzel a módszerrel nyilvánvalóan sokkal bonyolultabb lekérdezéseket hozhat létre, így játszhatsz vele, hogy megkapd a kívánt eredményeket. Végül beszéljünk arról, hogy az összevont függvényeket szűrt adatokra alkalmazzuk.
A szűrt adatok összegzése
Tegyük fel, hogy szeretném összefoglalni a családtagok számát a szűrt adatokon, hogyan juthatnék hozzá? Nos, tisztázzuk a szűrőnkre kattintva Egyértelmű gomb a szalagban. Ne aggódj, nagyon egyszerű a fejlett szűrő ismételt alkalmazása, ha egyszerűen rákattint a Speciális gombra, majd ismét az OK gombra kattint.
Adatbázisunk alján adjunk hozzá egy hívott cellát Teljes majd adjon hozzá egy összegfüggvényt a teljes családtagok összegzéséhez. Példámban csak gépeltem = SUM (C7: C31).
Tehát, ha minden családra nézek, 78 tagja van. Most menjünk előre, és alkalmazzuk újra az Advanced szűrőt, és nézzük meg, mi történik.
Hoppá! A helyes szám helyett 11 helyett még mindig látom, hogy a teljes szám 78! Miért van az, hogy? Nos, a SUM funkció nem hagyja figyelmen kívül a rejtett sorokat, így a számítás még mindig az összes sorban történik. Szerencsére van néhány funkció, amellyel figyelmen kívül hagyhatja a rejtett sorokat.
Az első az SUBTOTAL. Mielőtt bármilyen speciális funkciót használnánk, törölni szeretné a szűrőt, majd írja be a funkciót.
A szűrő törlése után lépjen be és írja be = SUBTOTAL ( és látnod kell egy legördülő listát, ahol egy csomó lehetőség van. Ezzel a funkcióval először válassza ki a használni kívánt összegzési funkció típusát.
Példánkban szeretném használni ÖSSZEG, így beírnám a 9-es számot, vagy csak kattintson rá a legördülő menüből. Ezután írjon be egy vesszőt, és válassza ki a cellák tartományát.
Ha megnyomja az Enter billentyűt, akkor a 78 érték megegyezik a korábban megadott értékkel. Ha azonban újra alkalmazza a szűrőt, 11-et fogunk látni!
Kiváló! Pontosan ezt akarjuk. Most beállíthatja a szűrőket, és az érték mindig csak a jelenleg megjelenő sorokat tükrözi.
A második funkció, amely eléggé pontosan ugyanaz, mint a SUBTOTAL funkció AGGREGÁLT. Az egyetlen különbség az, hogy van egy másik paraméter az AGGREGATE függvényben, ahol meg kell adni, hogy figyelmen kívül hagyja a rejtett sorokat.
Az első paraméter a használni kívánt összegző függvény, és mint a SUBTOTAL esetében, 9 a SUM funkciót jelenti. A második lehetőség, ha be kell írnod az 5-ös számot a rejtett sorok figyelmen kívül hagyásához. Az utolsó paraméter ugyanaz, és a cellák tartománya.
Az összefoglaló funkciókról szóló cikkem is olvasható az AGGREGATE funkció és más funkciók, például a MODE, MEDIAN, AVERAGE stb..
Remélhetőleg ez a cikk jó kiindulópontot nyújt a szűrők létrehozásához és használatához az Excel programban. Ha bármilyen kérdése van, írjon egy megjegyzést. Élvez!
