A pivot 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 pivot-tábla, amely az ügyfelek szerinti bevételt mutatja. A bevétel összesen 6,7 millió dollár. Vegye figyelembe, hogy a legnagyobb ügyfél, a Roto-Rooter a teljes bevétel 9% -át teszi ki.
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. Roto-Rooter, aki korábban a teljes összeg 9% -a volt, az új teljes összeg 23% -a.
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:
- Jelöljön ki egy cellát az adataiban, és kattintson az Adat fül Szűrő ikonjára, vagy nyomja meg a Ctrl + Shift + L billentyűkombinációt.
- 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:
- Menjen a fejlécsorra, majd a jobb szélső fejléc cellába. 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. Ezt a cellát varázscellának hívom.
És most, Vissza a forgó táblákhoz
Van egy szabály, amely szerint nem használhatja az AutoFilter funkciót, ha pivot táblában van. Lásd lejjebb? A Szűrő ikon szürkén jelenik meg, mert kiválasztottam egy cellát a kimutató táblázatban.
Nem tudom, miért szürkíti ezt a Microsoft. Valami belsőnek kell lennie, amely azt mondja, hogy az AutoFilter és a pivot tábla 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 varázssejtrő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ő ismét 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ő. Lépjen a Bevétel legördülő menübe, é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. lehetőséget. 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 a 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ásban
Ha 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 az Excel 2013 vagy újabb fut a Windows rendszerben, akkor erre 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 jelölőnégyzetet.
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 kijelölésével lépjen a Szalag Tervezés lapjára, é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, az alábbiak szerint.
Ezt a varázssejt-trükköt eredetileg Dan kapta meg nekem a filadelfiai szemináriumon, és 15 évvel később egy másik Dan megismételte, mint a cincinnati szeminárium. Köszönet Miguel Caballerónak, hogy ezt a funkciót javasolta.
