Az adatok szűrése Excelben
Nemrég írtam egy cikket arról, hogyan használhatunk összefoglaló függvényeket az Excelben(how to use summary functions in Excel) nagy mennyiségű adat egyszerű összefoglalására, de ez a cikk a munkalapon szereplő összes adatot figyelembe vette. Mi van, ha csak az adatok egy részhalmazát szeretné megnézni, és az adatok részhalmazát összegezni?
Az Excelben(Excel) szűrőket hozhat létre az oszlopokon, amelyek elrejtik a szűrőnek nem megfelelő sorokat. Ezenkívül az Excel speciális függvényeit is használhatja adatok összegzésére, csak a szűrt adatok felhasználásával.
Ebben a cikkben végigvezetem a szűrők Excelben(Excel) való létrehozásának lépésein, valamint a beépített függvények használatával a szűrt adatok összegzéséhez.
Hozzon létre egyszerű szűrőket Excelben
Az Excelben(Excel) egyszerű és összetett szűrőket hozhat létre. Kezdjük az egyszerű szűrőkkel. Ha szűrőkkel dolgozik, mindig legyen egy sor a tetején, amelyet a címkékhez használnak. Ez a sor nem kötelező, de megkönnyíti a szűrőkkel való munkát.
Fent van néhány hamis adatom, és szeretnék létrehozni egy szűrőt a Város(City) oszlopban. Excelben(Excel) ez nagyon egyszerű . Menjen tovább, és kattintson az Adatok(Data) fülre a szalagon, majd kattintson a Szűrő(Filter) gombra. Nem kell kijelölni az adatokat a lapon, vagy az első sorba kattintani.
Ha rákattint a Szűrő(Filter) elemre , az első sor minden oszlopában automatikusan megjelenik egy kis legördülő gomb a jobb oldalon.
Most lépjen tovább, és kattintson a legördülő nyílra a Város(City) oszlopban. Néhány különböző lehetőséget fog látni, amelyeket alább ismertetek.
Felül gyorsan rendezheti az összes sort a Város(City) oszlopban található értékek szerint. Vegye figyelembe, hogy az adatok rendezésekor a teljes sort áthelyezi, nem csak a Város(City) oszlop értékeit. Ez biztosítja, hogy az adatok sértetlenek maradjanak, mint korábban.
Ezenkívül érdemes a legelejére felvenni egy ID nevű oszlopot, és számozni egytől akárhány sorig van a munkalapon. Így mindig rendezheti az azonosító oszlopot, és visszakaphatja adatait az eredeti sorrendben, ha ez fontos Önnek.
Amint látja, a táblázat összes adata a Város(City) oszlopban szereplő értékek alapján van rendezve. Eddig egyetlen sor sem volt elrejtve. Most vessünk egy pillantást a szűrő párbeszédpanel alján található jelölőnégyzetekre. Példámban csak három egyedi érték van a Város(City) oszlopban, és ez a három jelenik meg a listában.
Továbbmentem, két város bejelölését töröltem, egyet pedig bejelölve hagytam. Most már csak 8 adatsor jelenik meg, a többi pedig rejtve van. Könnyen megállapíthatja, hogy szűrt adatokat néz, ha megnézi a bal szélen lévő sorszámokat. Attól függően, hogy hány sor van elrejtve, néhány extra vízszintes vonal jelenik meg, és a számok színe kék lesz.
Tegyük fel, hogy egy második oszlopra szeretnék szűrni, hogy tovább csökkentsem az eredmények számát. A C oszlopban szerepel az egyes családok teljes létszáma, és csak a kettőnél több taggal rendelkező családok eredményeit szeretném látni.
Menjen tovább, és kattintson a legördülő nyílra a C oszlopban(Column C) , és ugyanazokat a jelölőnégyzeteket fogja látni az oszlop minden egyedi értékéhez. Ebben az esetben azonban a Számszűrők(Number Filters) , majd a Nagyobb mint( Greater Than) lehetőségre szeretnénk kattintani . Amint látja, van egy csomó más lehetőség is.
Megjelenik egy új párbeszédablak, ahol beírhatja a szűrő értékét. Egynél több feltételt is hozzáadhat egy ÉS vagy VAGY függvénnyel. Mondhatjuk például, hogy olyan sorokat szeretne, ahol az érték nagyobb, mint 2, és nem egyenlő például 5-tel.
Most már csak 5 adatsorra jutottam: családok csak New Orleansból(New Orleans) és 3 vagy több taggal. Elég könnyű ? (Easy)Vegye figyelembe, hogy könnyen törölheti az oszlopok szűrőit, ha rákattint a legördülő menüre, majd a Szűrő törlése az „Oszlopnévből”(Clear Filter From “Column Name”) hivatkozásra.
Tehát nagyjából ennyi az egyszerű szűrőkről az Excelben(Excel) . Használatuk nagyon egyszerű, és az eredmények meglehetősen egyértelműek. Most pedig vessünk egy pillantást az összetett szűrőkre a Speciális(Advanced) szűrők párbeszédpanelen.
Hozzon létre speciális szűrőket az Excelben
Ha fejlettebb szűrőket szeretne létrehozni, használja a Speciális(Advanced) szűrő párbeszédablakot. Tegyük fel például, hogy látni szerettem volna az összes New Orleans-ban(New Orleans) élő családot 2-nél több taggal VAGY(OR) minden olyan Clarksville -i családot, amelynek családjában több mint 3 tag van, ÉS(AND) csak azokat, amelyek .EDU végződésű e-mail-címmel rendelkeznek. Most ezt egy egyszerű szűrővel nem tudod megtenni.
Ehhez egy kicsit másképp kell beállítanunk az Excel lapot. Szúrjon be néhány sort az adatkészlete fölé, és másolja be a fejléccímkéket pontosan az első sorba az alábbiak szerint.
Íme, hogyan működnek a fejlett szűrők. Először be kell írnia a feltételeket a felső oszlopokba, majd kattintson a Speciális(Advanced) gombra a Rendezés és szűrés( Sort & Filter) alatt az Adatok(Data) lapon.
Tehát pontosan mit írhatunk be ezekbe a cellákba? Rendben, akkor kezdjük a példánkkal. Csak New Orleansból(New Orleans) vagy Clarksville -ből akarunk adatokat látni , ezért írjuk be ezeket az E2 és E3 cellákba.
Ha különböző sorokba ír be értékeket, az VAGY-t jelent. Most két főnél több tagú New Orleans-i(New Orleans) családokat és 3-nál több tagú Clarksville -i családokat akarunk . Ehhez írja be a >2 -t a C2-be és a >3 -at a C3-ba.
Mivel a >2 és a New Orleans egy sorban vannak, ez egy ÉS(AND) operátor lesz. Ugyanez igaz a fenti 3. sorra is. Végül csak az .EDU végződésű e-mail címmel rendelkező családokat szeretnénk. Ehhez csak írja be a *.edu a D2 és a D3 mezőbe. A * szimbólum tetszőleges számú karaktert jelent.
Miután ezt megtette, kattintson bárhová az adatkészletben, majd kattintson a Speciális(Advanced) gombra. A Lista Tartomány(List Rang) mező automatikusan kiszámolja az adatkészletet, mivel Ön rákattintott, mielőtt a Speciális(Advanced) gombra kattintott volna. Most kattintson a Criteria range gomb jobb oldalán található kis kis gombra.
Válasszon ki(Select) mindent A1-től E3-ig, majd kattintson újra ugyanarra a gombra, hogy visszatérjen a Speciális szűrő(Advanced Filter) párbeszédpanelhez. Kattintson az OK gombra(Click OK) , és az adatokat most szűrni kell!
Amint látja, most csak 3 olyan eredményem van, amely megfelel ezeknek a kritériumoknak. Vegye figyelembe, hogy a feltételtartomány címkéinek pontosan meg kell egyeznie az adatkészlet címkéivel, hogy ez működjön.
Nyilvánvalóan sokkal bonyolultabb lekérdezéseket hozhat létre ezzel a módszerrel, ezért játsszon vele, hogy elérje a kívánt eredményt. Végül beszéljünk az összegzési függvények szűrt adatokra történő alkalmazásáról.
Szűrt adatok összegzése
Tegyük fel, hogy szeretném összesíteni a családtagok számát a szűrt adataimon, hogyan jutottam hozzá? Nos, töröljük a szűrőnket a szalagon található Törlés gombra kattintva. (Clear)Ne aggódjon, nagyon egyszerű újra alkalmazni a speciális szűrőt, egyszerűen kattintson a Speciális(Advanced) gombra, majd ismét az OK gombra.
Adatkészletünk alján adjunk hozzá egy Összesen(Total) nevű cellát , majd adjunk hozzá egy összegező függvényt a család összes tagjának összegzéséhez. Példámban a =SUM(C7:C31) írtam be .
Tehát ha az összes családot nézem, akkor összesen 78 tagom van. Most lépjünk tovább, és alkalmazzuk újra a Speciális(Advanced) szűrőnket, és nézzük meg, mi történik.
Hoppá! Ahelyett, hogy a helyes számot, 11-et mutatnám, továbbra is 78-at látok! Miert van az? Nos, a SUM függvény nem hagyja figyelmen kívül a rejtett sorokat, így továbbra is az összes sor felhasználásával végzi a számítást. Szerencsére van néhány funkció, amellyel figyelmen kívül hagyhatja a rejtett sorokat.
Az első a SUBTOTAL . Mielőtt ezeket a speciális funkciókat használnánk, törölje a szűrőt, majd írja be a függvényt.
A szűrő törlése után írja be, hogy =SUBTOTAL( , és egy legördülő menü jelenik meg egy csomó opcióval. Ezzel a funkcióval először válassza ki a használni kívánt összegző függvény típusát egy szám használatával.
Példánkban a SUM értéket szeretném használni , ezért beírom a 9-es számot, vagy egyszerűen rákattintok a legördülő listából. Ezután írjon be egy vesszőt, és válassza ki a cellák tartományát.
Amikor megnyomja az entert, látnia kell, hogy a 78-as érték megegyezik az előzővel. Ha azonban most újra alkalmazza a szűrőt, 11-et fogunk látni!
Kiváló! Pontosan ezt akarjuk. Most már módosíthatja a szűrőket, és az érték mindig csak az éppen megjelenő sorokat fogja tükrözni.
A második függvény, amely nagyjából ugyanúgy működik, mint a SUBTOTAL függvény, az AGGREGATE . 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 kell hagyni a rejtett sorokat.
Az első paraméter a használni kívánt összegző függvény, és a SUBTOTAL -hoz hasonlóan a 9 a SUM függvényt jelöli. A második lehetőség az, amikor be kell írnia az 5-öt a rejtett sorok figyelmen kívül hagyásához. Az utolsó paraméter ugyanaz, és a cellák tartománya.
Olvassa el az összefoglaló függvényekről szóló cikkemet is, ahol megtudhatja, hogyan kell részletesebben használni az AGGREGATE függvényt(use the AGGREGATE function) és más funkciókat, mint például a MODE , a MEDIAN , A AVERAGE stb.
Remélhetőleg ez a cikk jó kiindulási alapot nyújt a szűrők létrehozásához és használatához az Excelben(Excel) . Ha bármilyen kérdése van, nyugodtan írjon megjegyzést. Élvezd!
Related posts
Az Excel adatok diagramozása
Használja az Excel Mobile új „Adatok beszúrása képből” funkcióját
Használja az Összefoglaló függvényeket az adatok összegzéséhez az Excelben
Használja az Excelt az adatok webről történő másolására szolgáló eszközként
Adjon hozzá egy lineáris regressziós trendvonalat egy Excel szóródiagramhoz
Két Excel-fájl összehasonlítása és a különbségek kiemelése
Folyamatábra létrehozása Wordben és Excelben
Megjegyzések hozzáadása egy Excel munkalap cellához
Hogyan lehet kivonni a dátumokat az Excelben
Word-dokumentum automatikus biztonsági mentése a OneDrive-ra
Hogyan csoportosítsunk munkalapokat Excelben
A 40 legjobb Microsoft Excel billentyűparancs
Címkék létrehozása a Wordben Excel-táblázatból
A lapok, cellák, oszlopok és képletek elrejtése az Excelben
Hogyan válthatunk a munkalapok között az Excelben
Excel-háttérképek hozzáadása és nyomtatása
Cellák összekapcsolása lapok és munkafüzetek között Excelben
Elfelejtett Excel XLS jelszó eltávolítása, feltörése vagy feltörése
Hogyan találhat megfelelő értékeket az Excelben
A Sparklines használata Excelben