A Top 10 Filter forgótábla összesen a látható sorokat adja meg
A forgatótáblák a Top 10 szűrőt kínálják. Menő. Rugalmas. De utálom, és elmondom, miért.
Itt van egy forgatótábla, amely az ügyfelek szerinti bevételt mutatja. A bevétel összesen 6,7 millió dollár.
Mi van akkor, ha a menedzserem figyeli az aranyhalat, és csak az első öt ügyfelet akarja látni?
A kezdéshez nyissa meg az A3 legördülő menüt, és válassza az Értékszűrők, Top 10 elemet.
A rendkívül rugalmas Top 10 Filter párbeszédpanel lehetővé teszi a Top / alsó lehetőséget. 10, 5 vagy bármely más számot megtehet. Kérheti az első öt tételt, a 80% -ot, vagy annyi ügyfelet, hogy elérje az 5 millió dollárt.
De itt van a probléma: Az eredményül kapott jelentés öt ügyfelet és az összes vásárló számát mutatja, nem pedig a mindenkinek.
Először is, néhány fontos szó az AutoFilterről
Tudomásul veszem, hogy ez falon kívüli kérdésnek tűnik. Ha be akarja kapcsolni a Szűrő legördülő menüt egy szokásos adathalmazon, hogyan csinálja? Íme három igazán gyakori módszer:
- Válasszon ki egy cellát az adataiból, és kattintson az Adatok fül Szűrő ikonjára.
- Jelölje ki az összes adatot a Ctrl + * gombbal, majd kattintson az Adat fül Szűrő ikonjára.
- Az adatok táblázatos formázásához nyomja meg a Ctrl + T billentyűkombinációt.
Ez három nagyon jó módszer. Amíg ismeri bármelyiket, abszolút nincs szükség más mód megismerésére. De itt van egy hihetetlenül homályos, de varázslatos mód a szűrő bekapcsolására:
- Ugrás a fejlécsorra, a jobb szélső fejléc cellára. Mozgasson egy cellát jobbra. Valami ismeretlen okból, amikor ebben a cellában van, és a Szűrő ikonra kattint, az Excel kiszűri a bal oldalon lévő adatkészletet. Fogalmam sincs, miért működik ez. Valójában nem érdemes erről beszélni, mert már három nagyon jó módja van a Szűrő legördülő menü bekapcsolásának. Varázscellának hívom ezt a cellát.
És most, Vissza a forgó táblákhoz …
Tehát van egy szabály, amely azt mondja, hogy nem használhatja az automatikus szűrőket pivot-táblázatban. Lásd lejjebb? A Szűrő ikon szürkén jelenik meg, mert kiválasztottam egy cellát a kimutató táblázatban.
Soha nem gondoltam arra, hogy a Microsoft miért szürkíti ezt. Valami belsőnek kell lennie, amely szerint az AutoFilter és a Pivot Table nem létezhet együtt. Tehát az Excel csapatban van valaki, aki a Szűrő ikon szürkeségéért felel. Ez a személy még soha nem hallott a Magic celláról. Jelöljön ki egy cellát az elfordulási táblázatban, és a szűrő szürkévé válik. Kattintson a pivot táblázaton kívülre, és a Szűrő újra engedélyezve van.
De várj. Mi a helyzet a varázscellával, amiről most meséltem? Ha rákattint az utolsó fejléc jobb oldalán található cellára, az Excel elfelejti szürkíteni a Szűrő ikont!
Valóban, az Excel hozzáadja az AutoFilter legördülő listákat a forgatótábla felső sorához. Az AutoFilter pedig másképp működik, mint a forgóasztal-szűrők. Nyissa meg a Bevétel legördülő menüt, és válassza a Számszűrők, a legjobb 10 lehetőséget.
A Top 10 AutoFilter párbeszédpanelen válassza a Top 6 elemet. Ez nem elírás … Ha öt ügyfelet szeretne, válassza a 6. Ha 10 ügyfelet szeretne, válassza a 11 lehetőséget.
Az AutoFilter számára a végösszeg a legnagyobb elem az adatokban. Az első öt ügyfél az adatok 2–6 pozícióját foglalja el.
Vigyázat
Nyilvánvaló, hogy ezzel a trükkel lyukat tép az Excel szövetébe. Ha később megváltoztatja az alapul szolgáló adatokat, és frissíti a kimutatótáblát, akkor az Excel nem frissíti a szűrőt, mert a Microsoft tudomása szerint nincs mód szűrő alkalmazására egy kimutató táblára!
jegyzet
Célunk, hogy ezt titokban tartsuk a Microsoft előtt, mert ez egy nagyon jó funkció. Jó ideje „törött”, ezért nagyon sokan vannak, akik mára már támaszkodhatnak rá.
Teljesen legális megoldás az Excel 2013+ alkalmazásbanHa olyan kimutató táblázatot szeretne, amely az első öt, de az összes ügyfél összesített számát mutatja, akkor az adatait az Excelen kívülre kell vinnie. Ha Excel 2013 vagy 2016 rendszert futtat, akkor ennek nagyon kényelmes módja van. Ennek bemutatásához töröltem az eredeti pivot táblázatot. Válassza az Insert, Pivot Table lehetőséget. Mielőtt az OK gombra kattintana, jelölje be az Adatok hozzáadása az adatmodellhez mezőt.
Készítse el forgóasztalát a szokásos módon. Az A3 legördülő menüben válassza az Értékszűrők, a Top 10 lehetőséget, és kérje az első öt ügyfelet. A pivot tábla egyik cellájának kiválasztásával lépjen a szalag Tervezés fülére, és nyissa meg a Részösszegek legördülő menüt. A legördülő menüben a végső választás a Szűrt tételek szerepeltetése összesen. Normális esetben ez a választás szürkén jelenik meg. De mivel az adatokat az adatmodell tárolja normál pivot gyorsítótár helyett, ez a lehetőség már elérhető.
Válassza a Szűrt elemek belefoglalása az összesbe opciót, és a Főösszeg mostantól csillagot és az összes adatot tartalmazza.
Ez a trükk eredetileg Dantől származott nekem a Philadelphiában tartott szemináriumon. Köszönet Miguel Caballerónak, hogy ezt a funkciót javasolta.
Videót nézni
- A Top 10 Filter forgótábla összesen a látható sorokat adja meg
- A Szűrt tételek szerepeltetése az összesítésben szürke színű
- Furcsa mód az adatszűrő meghívására a mágikus cellából
- Az adatszűrők nem engedélyezettek a kimutató táblákban
- Az Excel nem szürkíti ki az adatszűrőt a varázslatos cellából
- Kérje meg a legjobb 6-ot, hogy bejusson az első 5-be, valamint a teljes összeg
- Hasznos egy adott pivot elem alapján történő szűréshez
- Excel 2013 vagy újabb: A True Total megszerzésének más módja
- Küldje el adatait az adatmodellen keresztül
- A Szűrt elemek teljes összegbe foglalása elérhető lesz
- Szerezd meg a Total-ot a csillaggal
- Tíz évvel ezelőtt tanultam meg ezt a trükköt Dan-tól Philadelphiában
Videó átirat
Ismerje meg az Excel for Podcast alkalmazást, 1999. epizód - Pivot Table True Top Five
Ezt az egész könyvet podcastolom. Van egy lejátszási lista, kattintson a jobb felső sarokban található I gombra, hogy kövesse ezt a lejátszási listát. Üdvözöljük a netcaston. Bill Jelen vagyok.
Rendben, ezért létrehozunk egy kimutató táblázatot, és nem az összes, hanem csak az első öt ügyfelet szeretnénk megmutatni. INSERT, forgóasztal. Oké, leteszem az Ügyfelet a bal oldalon és a Bevételt. Rendben, így itt van a teljes ügyfelek listája, amelyet 6,7 millió dollárként jegyeztek. Az Excel megkönnyíti az első öt elvégzését. Menjen a Sorcímkék, Értékszűrők 10. oldalra. Nem feltétlenül kell lennie a tetején. Lehet felső vagy alsó. Nem kell ötévesnek lennie. Lehet húsz, negyven, lehet bármi. A legjobb nyolcvan százalék, adj nekem elég lemezt hárommillió vagy négymillió dollár eléréséhez, de tessék. Az első öt elem. Emlékezzen most 6,7 millió dollárra, kattintson az OK gombra, és itt a nagy problémám az, hogy ez a teljes összeg nem a 6,7 millió. Amikor ezt adom az értékesítésért felelős alelnöknek, akkor kiborul, mondván, várjon egy kicsit,Tudom, hogy több mint 3,3 millió dollárt tettem meg. Helyes, tehát visszavonjuk, visszavonjuk és visszatérünk az eredeti adatokhoz.
Most ezt a következő trükköt tanultam az egyik Power Excel szemináriumon Philadelphiában. Egy Dan nevű srác a második sorban megmutatta ezt. Több mint tíz évvel ezelőtt mutatta meg nekem ezt a trükköt, és először a Szűrőkről kell beszélnünk. Tehát normális esetben, ha a szokásos szűrőt, ezt a Szűrőt fogja itt használni, akkor az adatkészlet bármelyik celláját kiválasztja, majd a Szűrő ikonra kattint, vagy egyesek a teljes Adatkészletet, a CONTROL * elemet választják, majd a Szűrő ikonra kattintanak, de van egy harmadik út is. Olyan módon, amely senkit nem érdekel. Ha az utolsó fejlécbe megy, az én esetemben ez a költség az L1-ben, és egy cellával jobbra megy. Ezt varázscellának hívom, fogalmam sincs, miért, de valamilyen ismeretlen okból, ebből a cellából kiszűrhetem a szomszédos Adathalmazt. Rendben, ez olyan furcsa mód, és ez senkit nem érdekel.
Helyes, mert két másik nagyon jó módja van a Szűrő meghívásának, senkinek sem kell tudnia a mágikus celláról, de itt van a lényeg: lásd a Pivot-tábla belsejét, ez szürke. Nem használhatja ezeket a szűrőket. Ez ellentétes a szabályokkal. Most, ha kijövök ide, szívesen használom a Szűrőt, de belül besorolják. Nem tudom, ki az, aki ezt szürkíti, de még soha nem hallották a varázssejtemről szóló kis beszédemet, mert ha a legutolsó Fejléc cellába megyek, és egy cellával jobbra megyek, elfelejtik szürkíteni a szűrőt, és most hozzáadtam a régi automatikus szűrőket a kimutató táblához. Tehát idejövök, elmegyek a Számszűrőkhöz, ez más, mint az Értékszűrők. Még mindig a Top Ten-nek hívják. Kicsit másképp, az első ötöt fogom kérni, nem az első hatot.Az első hat, mert ehhez a szűrőhöz a végösszeg csak egy újabb sor, és a végösszeg a legnagyobb tétel, majd amikor a 2–6. Tételt kérik, megkapom az első öt elemet.
Rendben, szóval ott vagyunk. Hűvös Filter hack, amely megadja nekünk az első öt elemet, és mindenki valós számát. Rendben, pár dolog. Ne feledkezzünk meg a mágikus celláról. Rendben, ezt a szűrőt nem lehet kikapcsolni, hacsak nem megy vissza a mágikus cellába. Rendben, így emlékeznie kell a mágikus cellára. Továbbá, ha megváltoztatja az alapul szolgáló adatokat, és frissíti a kimutatási táblázatot, akkor nem fogják frissíteni a Szűrőt, mert a Microsoft tudtával nem engedélyezett a Szűrő használata.
Ez más dolgokhoz is hasznos. Néha vannak olyan termékeink, amelyek a tetején vannak. Menjünk ide táblázatos formában. Nem szükséges, csak szeretek valódi címsorokat kapni. Gizmo, Widget, Gadgets, Doodads. Rendben, talán te vagy a Doodads menedzsere, és csak azokat az ügyfeleket kell látnod, akiknek különleges értéke volt, és a Doodads-t. Tehát elmegyek a mágikus cellába, bekapcsolom a Szűrőt, majd a Doodads alatt kérhetek nullánál nagyobb tételeket. Kattintson az OK gombra. Rendben, ez a típusú szűrés nem lenne lehetséges egy szokásos kimutató táblán, de a mágikus cellával lehetséges.
Rendben, most vonjuk vissza a listát. Kapcsoljuk ki ezt a szűrőt, és távolítsuk el a kimutató táblázatot, és ha az Excel 2013-as vagy új verzióját használja, akkor egy teljesen legális módszert fogok bemutatni Önnek, hogy az alján a helyes összeg kerüljön. Helyezze be a pivot-táblázatot ide, alulra, az Excel 2013-tól kezdődően ez a nagyon ártalmatlan doboz nem hangzik túl izgalmasnak, adja hozzá ezeket az adatokat az adatmodellhez. Ez az adatokat a kulisszák mögött elküldi a Power Pivot Engine-be. Készítse el pontosan ugyanazt a jelentést. Az ügyfelek a bal oldalon. Bevétel a Pivot-tábla szívében. Ezután lépjen a szokásos szűrőkhöz, az Értékszűrők top 10-re. Kérje meg az első ötet. Ismét vegye észre, hogy 6,7 millió dollárunk van, miután ezt megtettem, 3,3 millió dollár, de itt van a különbség. Amikor a Tervezés fülre lépek, az Részösszegek alatt, ez a funkció a Szűrt elemek belefoglalása az összesbe,már nem szürkés. A szokásos Pivot-táblázat nem érhető el. Van egy kis csillagunk ott, és ez minden. Rendben, most természetesen ez csak az Excel 2013 vagy újabb verzióban működik.
Rendben, hat hétbe telik, mire ezt az egész könyvet kijuttatom a YouTube-ra. Olyan sok jó tipp van itt. Tippek, amelyek azonnal időt takaríthatnak meg Önnek. Most vásárolja meg a teljes könyvet, és hozzáférhet mind a 40-hez. Valójában sokkal több, mint 40 tipp. Excel gyorsbillentyűk. Mindenféle nagyszerű dolog ebben a könyvben.
Rendben, összefoglalom. Tehát amikor egy Pivot Table top 10 szűrőt készítünk, akkor az összes, de csak a látható sorokat megadja nekünk, nem pedig azokat a dolgokat, amelyeket kiszűrt. Ja, ha a második fülre megyünk, és megkeressük a Részösszegeket, a Szűrt elemeket és az Összegeket, akkor ez szürkén jelenik meg, de van egy furcsa mód arra, hogy a varázscellából lehívjuk a Régi adatszűrőt. A legutolsó fejléccella, menjen egy cellával jobbra, nem használhatja a Szűrőket és a Táblázatokat, de ha a varázscellába megy, elfelejti szürkíteni. Most a Számszűrőben a legjobb hatot kéri az első öt megszerzéséhez, plusz a végösszeget. Hasznos egy adott pivot elemre történő szűréshez is: Doodadok, bármi, ami 0-nál nagyobb volt a Doodads-ban vagy az első 5 Doodad-ban. Az Excel 2013 vagy újabb, a True Total megszerzésének más módja van.Jelölje be az Adatmodell jelölőnégyzetét, majd a Szűrt elemeket foglalja össze az Összesen lehetőség lesz. Csillaggal kapja meg az összeget. És köszönet Dannak Philadelphiában, aki több mint tíz éve megmutatta az egyik Power Excel szemináriumomon, és megadta ezt a nagyszerű trükköt. A szűrő bejutásának módja a Club Pivot asztal falán. Általában nem engedélyezik azt az automatikus szűrőt.
Hé, szeretném megköszönni, hogy betértél. Legközelebb találkozunk, még egy netes közvetítésért a MRExcel-től.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast1999.xlsx
