Nemrég írtam egy cikket arról, hogyan lehet az Excel összefoglaló funkcióit felhasználni a nagy mennyiségű adat könnyű összefoglalására, de ez a cikk figyelembe vette a munkalap összes adatát. Mi lenne, ha csak egy részhalmazt szeretne megnézni, és összefoglalni az adatok részhalmazát?

Az Excel programban szűrőket hozhat létre olyan oszlopokban, amelyek elrejtik a sorokat, amelyek nem felelnek meg a szűrőnek. Ezenkívül az Excel speciális funkcióit is felhasználhatja az adatok összegzésére, csak a szűrt adatok felhasználásával.

Ebben a cikkben bemutatom a szűrők létrehozásának lépéseit az Excel programban, valamint a beépített funkciókat is felhasználva a szűrt adatok összegzésére.

Készítsen egyszerű szűrőket az Excel programban

Az Excel programban egyszerű szűrőket és összetett szűrőket hozhat létre. Kezdjük az egyszerű szűrőkkel. A szűrőkkel való munka során mindig tetején legyen egy sor, amelyet a címkékhez használnak. Ez a sor nem követelmény, de egy kicsit megkönnyíti a szűrőkkel való munkát.

mintaadatok excel

Fent van néhány hamis adatom, és szeretnék szűrőt létrehozni a Város oszlopban. Az Excel programban ezt nagyon könnyű megtenni. Haladjon tovább, kattintson a szalagon található Adatok fülre, majd kattintson a Szűrés gombra. Nem kell, hogy válassza ki az adatokat a lapon, vagy kattintson az első sorba.

excel adatszűrő

Ha rákattint a Szűrőre, az első sor minden oszlopához automatikusan egy kis legördülő gomb kerül hozzáadásra a jobb oldalon.

hozzáadott Excel szűrő

Most menjen tovább, és kattintson a Város oszlopban található legördülő nyílra. Lát egy pár különféle lehetőséget, amelyeket az alábbiakban magyarázom.

a szűrési lehetőségek kiválóak

A tetején az összes sort gyorsan rendezheti a Város oszlopban szereplő értékek alapján. Vegye figyelembe, hogy az adatok rendezésekor az egész sort mozgatja, nem csak a Város oszlopban szereplő értékeket. Ez biztosítja, hogy adatai változatlanok maradjanak, mint korábban.

Ezenkívül jó ötlet egy oszlop hozzáadása a legelején, az úgynevezett ID azonosítóval, és sorszámozva egytől egy-egy sorig, bármennyi sor van a munkalapján. Ilyen módon az ID oszlop szerint rendezhet, és adatait ugyanabban a sorrendben kaphatja vissza, mint az eredetileg volt, ha ez fontos neked.

adatok szerint rendezett excel

Mint láthatja, a táblázat minden adatát a város oszlopban szereplő értékek alapján rendezték. Eddig egyetlen sor sem rejtett. 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 oszlopban, és ez a három megjelenik a listában.

a szűrt sorok kiemelkednek

Előmentem, és megválasztottam két várost, és egyet hagytam ellenőrizve. Most csak 8 adatsorom van, és a többi rejtett. Könnyedén megmondhatja, hogy a szűrt adatokat néz-e meg, ha a bal oldali sorok számát ellenőrzi. Attól függően, hogy hány sor van rejtve, lát egy pár extra vízszintes vonalat, és a számok színe kék lesz.

Tegyük fel, hogy szűrni akarok egy második oszlopra az eredmények számának további csökkentése érdekében. A C oszlopban az összes család tagjainak száma van, és csak azoknak a családoknak az eredményeit szeretném látni, amelyeknél a két tag több.

számszűrő excel

Haladjon tovább, kattintson a C oszlop legördülő nyílára, és ugyanazokat a jelölőnégyzeteket fogja látni az oszlopban szereplő minden egyes egyedi értéknél. Ebben az esetben azonban szeretnénk kattintani a Számszűrőkre, majd a Nagyobb elemre. Mint láthatja, van egy csomó más lehetőség is.

nagyobb, mint a szűrő

Egy új párbeszédpanel jelenik meg, és itt beírhatja a szűrő értékét. Felvehet egynél több feltételt is AND vagy OR funkcióval. Azt mondhatja, hogy olyan sorokat szeretne, ahol az érték például nagyobb, mint 2, és nem egyenlő 5-del.

két szűrő kiemelkedik

Most mindössze 5 adatsorra van szükségem: csak New Orleansből származó családok és 3 vagy több taggal. Elég könnyű? Vegye figyelembe, hogy egy szűrőt egy oszlopban könnyen törölhet, ha rákattint a legördülő menüre, majd rákattint a Szűrő törlése a “Oszlop neve” linkre.

tiszta szűrő excel

Tehát erről van szó az Excel egyszerű szűrőivel kapcsolatban. Nagyon könnyű használni, és az eredmények nagyon egyértelműek. Most vessünk egy pillantást az összetett szűrőkre az Advanced szűrők párbeszédpanelen.

Hozzon létre speciális szűrőket az Excel programban

Ha fejlettebb szűrőket szeretne létrehozni, akkor a Speciális szűrő párbeszédpanelt kell használnia. Tegyük fel például, hogy szerettem volna látni minden olyan családot, amely New Orleans-ban él, és a családjában több mint 2 tag van, VAGY minden Clarksville-i családot, ahol a családban több mint 3 tag van, és csak azokat, amelyek .EDU végû e-mail címmel rendelkeznek. Most már nem tudja ezt megtenni egy egyszerű szűrővel.

Ehhez kissé másképp kell beállítanunk az Excel lapot. Menjen előre, és illesszen be néhány sort az adatkészlet fölé, és másolja a fejléccímkéket pontosan az első sorba, az alább látható módon.

speciális szűrőbeállítás

Most itt van, hogyan működnek a fejlett szűrők. Először be kell gépelnie a kritériumokat a felső oszlopokba, majd az Adatok lapon a Rendezés és szűrés alatt kattintson a Speciális gombra.

fejlett szűrőszalag

Szóval mit pontosan beírhatunk ezekbe a cellákba? OK, szóval kezdjük a példánkkal. Csak New Orleans vagy Clarksville adatait szeretnénk látni, tehát írjuk be ezeket az E2 és E3 cellákba.

speciális szűrőváros

Ha különféle sorokra ír be értékeket, ez VAGY értéket jelent. Most azt akarjuk, hogy New Orleans családok több, mint két taggal rendelkezzenek, és Clarksville családok, amelyek több mint három tagúak. Ehhez írja be a> 2 értéket a C2-be és> 3 a C3-ba.

a fejlett szűrők kiemelkednek

Mivel a> 2 és a New Orleans ugyanabban a sorban helyezkedik el, és AND operátor lesz. Ugyanez vonatkozik a fenti 3. sorra. Végül azt akarjuk, hogy csak a .EDU végű e-mail címet használó családok legyenek. Ehhez írja be a * .edu parancsot mind a D2, mind a D3 elembe. A * szimbólum tetszőleges számú karaktert jelent.

kritériumok tartománya excel

Miután ezt megtette, kattintson az adatkészlet bárhová, majd kattintson a Speciális gombra. A Listatartomány mező automatikusan kitalálja az adatkészletet, mivel rákattintott azelőtt, hogy a Speciális gombra kattintott. Most kattintson a Kicsi gombra a Criteria range gomb jobb oldalán.

válassza ki a kritériumtartományt

Válasszon mindent A1-től E3-ig, majd kattintson ugyanazon a gombbal ismét az Advanced Filter párbeszédablakhoz való visszatéréshez. Kattintson az OK gombra, és az adatait most szűrni kell!

szűrje az eredményeket

Mint láthatja, most csak 3 eredményem van, amelyek megfelelnek az összes kritériumnak. Vegye figyelembe, hogy a kritériumtartomány címkéinek 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 is létrehozhat, tehát játsszon vele a kívánt eredmények elérése érdekében. Végül beszéljünk az összegzési függvényeknek a szűrt adatokra történő alkalmazásáról.

A szűrt adatok összefoglalása

Tegyük fel most, hogy össze szeretném mondani a kiszűrt adatokon a családtagok számát. Hogyan tudnék ezt megtenni? Nos, töröljük a szűrőnket a szalagon lévő Törlés gombra kattintva. Ne aggódjon, nagyon könnyű újra alkalmazni a speciális szűrőt, ha rákattint a Speciális gombra, majd ismét az OK gombra.

tiszta szűrő excelben

Az adatkészlet aljára adjunk hozzá egy Total nevű cellát, majd adjunk hozzá egy összegfüggvényt az összes családtag összegzéséhez. Példámban csak = SUM-ot (C7: C31) írtam.

összeg összes excel

Tehát ha az összes családot megnézem, akkor összesen 78 tagom van. Most menjünk tovább, és újból alkalmazzuk az Advanced szűrőt, és nézzük meg, mi történik.

rossz teljes szűrő

Hoppá! A helyes 11-es szám helyett még mindig látom, hogy a szám 78! Miert van az? Nos, a SUM funkció nem hagyja figyelmen kívül a rejtett sorokat, tehát továbbra is a számítást végzi az összes sor felhasználásával. Szerencsére van néhány olyan funkció, amellyel elrejtheti a rejtett sorokat.

Az első SUBTOTAL. Mielőtt felhasználnánk ezen speciális funkciók egyikét, töröljük a szűrőt, majd írjuk be a függvényt.

Miután törölte a szűrőt, lépjen tovább, és írja be = SUBTOTAL (és látnia kell, hogy egy legördülő doboz jelenik meg egy csomó opcióval. Ezzel a funkcióval először kiválasztja, hogy milyen számú összegzési funkciót szeretne használni.

Példánkban a SUM-t akarom használni, ezért beírnám a 9-es számot, vagy csak rá kell kattintanom a legördülő menüből. Ezután írjon be vesszőt, és válassza ki a cellák tartományát.

részösszeg funkció

Az Enter megnyomásakor látnia kell, hogy a 78 értéke megegyezik a korábbiakkal. Ha azonban újra alkalmazza a szűrőt, akkor 11-et fogunk látni!

részösszeg a szűrőn

Kiváló! Pontosan ezt akarjuk. Most beállíthatja a szűrőket, és az érték mindig csak a jelenleg megjelenő sorokat fogja tükrözni.

A második függvény, amely nagyjából pontosan megegyezik a SUBTOTAL funkcióval, az AGGREGATE. Az egyetlen különbség az, hogy van egy másik paraméter az AGGREGATE függvényben, ahol meg kell adnia, hogy figyelmen kívül hagyja a rejtett sorokat.

aggregált funkció

Az első paraméter a használni kívánt összegző függvény, és mint a SUBTOTAL esetében, a 9 jelöli a SUM funkciót. A második lehetőség az, ha be kell írnia az 5-öt a rejtett sorok figyelmen kívül hagyása érdekében. Az utolsó paraméter ugyanaz, és a cellatartományt tartalmazza.

Elolvashatja az összefoglaló funkciókról szóló cikket, amelyben részletesebben megismerheti az AGGREGATE funkció és más olyan funkciók használatát, mint a MODE, MEDIAN, AVERAGE stb.

Remélhetőleg ez a cikk jó kiindulópontot jelent a szűrők létrehozásához és használatához az Excel programban. Ha bármilyen kérdése van, nyugodtan írjon megjegyzést. Élvezd!