Kontingenčná tabuľka Top 10 Filter poskytuje celkový počet viditeľných riadkov
Kontingenčné tabuľky ponúkajú filter Top 10. Je to studené. Je flexibilný. Ale nenávidím to a poviem vám prečo.
Tu je kontingenčná tabuľka zobrazujúca výnosy podľa zákazníka. Celkové tržby sú 6,7 milióna dolárov.
Čo ak má môj manažér pozornosť zlatých rybiek a chce vidieť iba päť najlepších zákazníkov?
Ak chcete začať, otvorte rozbaľovaciu ponuku v A3 a vyberte položku Filtre hodnôt, prvých 10.
Superflexibilné dialógové okno Top 10 Filter umožňuje nastavenie Hore / Dole. Môže robiť 10, 5 alebo akékoľvek iné číslo. Môžete požiadať o päť najlepších položiek, 80% najlepších alebo dostatok zákazníkov, aby ste dosiahli 5 miliónov dolárov.
Ale tu je problém: Výsledná správa zobrazuje päť zákazníkov a celkový počet od týchto zákazníkov namiesto celkových od všetkých.
Ale najskôr niekoľko dôležitých slov o automatickom filtri
Uvedomujem si, že sa to javí ako všeobecná otázka. Ak chcete zapnúť rozbaľovacie ponuky Filtrovať na bežnej množine údajov, ako to urobíte? Tu sú tri skutočne bežné spôsoby:
- Vyberte jednu bunku vo svojich údajoch a kliknite na ikonu Filter na karte Údaje.
- Vyberte všetky svoje údaje pomocou kombinácie klávesov Ctrl + * a kliknite na ikonu Filter na karte Údaje.
- Stlačením kombinácie klávesov Ctrl + T naformátujte údaje ako tabuľku.
Toto sú tri skutočne dobré spôsoby. Pokiaľ niektorého z nich poznáte, nie je potrebné vedieť inak. Ale tu je neuveriteľne nejasný, ale magický spôsob, ako zapnúť filter:
- Prejdite do riadku s hlavičkami a prejdite do bunky nadpisu úplne vpravo. Presunúť o jednu bunku doprava. Z nejakého neznámeho dôvodu, keď sa nachádzate v tejto bunke a kliknete na ikonu Filter, program Excel filtruje množinu údajov po vašej ľavej strane. Netuším, prečo to funguje. Naozaj to nestojí za reč, pretože už existujú tri skutočne dobré spôsoby, ako zapnúť rozbaľovacie ponuky Filter. Túto bunku nazývam Kúzelná bunka.
A teraz späť na kontingenčné tabuľky …
Existuje teda pravidlo, ktoré hovorí, že automatické filtre nemôžete používať, keď ste v kontingenčnej tabuľke. Pozri nižšie? Ikona filtra je sivá, pretože som vybral bunku v kontingenčnej tabuľke.
Nikdy som skutočne neuvažoval nad tým, prečo to spoločnosť Microsoft zobrazuje šedo. Musí to byť niečo interné, čo hovorí, že automatický filter a kontingenčná tabuľka nemôžu existovať súčasne. Takže v tíme Excel je niekto, kto má na starosti šedivenie ikony Filter. Táto osoba nikdy nepočula o čarovnej cele. Vyberte bunku v kontingenčnej tabuľke a filter bude sivý. Kliknite mimo kontingenčnej tabuľky a filter je znova povolený.
Ale počkaj. A čo kúzelná bunka, o ktorej som ti práve hovoril? Ak kliknete do bunky napravo od posledného nadpisu, program Excel zabudne sivú ikonu filtra!
Program Excel samozrejme pridá do horného riadku vašej kontingenčnej tabuľky rozbaľovacie ponuky automatických filtrov. A automatický filter funguje inak ako filtre kontingenčných tabuliek. Prejdite do rozbaľovacej ponuky Výnosy a vyberte položku Filtre čísel, Top 10 …
V dialógovom okne Prvých 10 automatických filtrov vyberte Prvých 6 položiek. To nie je preklep … Ak chcete päť zákazníkov, vyberte 6. Ak chcete 10 zákazníkov, vyberte 11.
Pre automatický filter je riadok celkového súčtu najväčšou položkou v dátach. Prvých päť zákazníkov obsadzuje v údajoch pozície 2 až 6.
Pozor
Je zrejmé, že týmto trikom trháte dieru v tkanive Excelu. Ak neskôr zmeníte podkladové údaje a obnovíte svoju kontingenčnú tabuľku, program Excel neobnoví filter, pretože pokiaľ vie spoločnosť Microsoft, neexistuje žiadny spôsob, ako použiť filter na kontingenčnú tabuľku!
Poznámka
Naším cieľom je udržať pred Microsoftom tajomstvo, pretože je to celkom skvelá funkcia. Je to už nejaký čas „zlomené“, takže je veľa ľudí, ktorí sa na to teraz môžu spoliehať.
Úplne legálne riešenie v programe Excel 2013+Ak chcete, aby sa vám v kontingenčnej tabuľke zobrazilo päť najlepších zákazníkov, ale celkový počet všetkých zákazníkov, musíte presunúť svoje údaje mimo programu Excel. Ak máte program Excel 2013 alebo 2016, môžete to urobiť veľmi pohodlným spôsobom. Aby som vám to ukázal, odstránil som pôvodnú kontingenčnú tabuľku. Vyberte Vložiť, Kontingenčná tabuľka. Pred kliknutím na tlačidlo OK začiarknite políčko Pridať tieto údaje do dátového modelu.
Zostavte si svoj kontingenčný stôl ako obvykle. Pomocou rozbaľovacej ponuky v A3 vyberte Value Filters, Top 10, a požiadajte o päť najlepších zákazníkov. Keď je v kontingenčnej tabuľke vybratá jedna bunka, na páse s nástrojmi prejdite na kartu Návrh a otvorte rozbaľovaciu ponuku Medzisúčty. Poslednou voľbou v rozbaľovacej ponuke je Zahrnúť filtrované položky do súčtov. Za normálnych okolností je táto voľba sivá. Ale pretože sú údaje uložené v dátovom modeli namiesto normálnej pivotnej pamäte cache, je táto možnosť teraz k dispozícii.
Vyberte možnosť Zahrnúť filtrované položky do celkového súčtu a váš celkový súčet teraz obsahuje hviezdičku a súhrn všetkých údajov.
Tento trik ku mne pôvodne prišiel od Dana na mojom seminári vo Philadelphii. Ďakujem Miguelovi Caballerovi za navrhnutie tejto funkcie.
Pozeraj video
- Kontingenčná tabuľka Top 10 Filter poskytuje celkový počet viditeľných riadkov
- Zahrnutie filtrovaných položiek do súčtov je sivé
- Zvláštny spôsob vyvolania dátového filtra z magickej bunky
- V kontingenčných tabuľkách nie sú povolené dátové filtre
- Program Excel nedokáže šedý dátový filter z magickej bunky
- Požiadajte o najlepších 6, aby ste získali najlepších 5 plus Grand Total
- Užitočné na filtrovanie podľa konkrétnej otočnej položky
- Excel 2013 alebo novší: Iný spôsob získania skutočného súčtu
- Pošlite svoje údaje prostredníctvom dátového modelu
- K dispozícii bude zahrnutie filtrovaných položiek do súčtov
- Získajte celkom s hviezdičkou
- Tento trik som sa naučil pred 10 a viac rokmi od Dana vo Philadelphii
Prepis videa
Learn Excel for Podcast, Episode 1999 - Pivot Table True Top Five
Podcastujem celú túto knihu. Existuje zoznam skladieb. Kliknutím na písmeno I v pravom hornom rohu tento zoznam sledujete. Vitajte späť na netcaste. Som Bill Jelen.
Dobre, takže vytvoríme kontingenčnú tabuľku a chceme ukázať, nie všetkých zákazníkov, ale iba prvých päť zákazníkov. INSERT, Pivot Table. Dobre, položím Zákazníka na ľavú stranu a Príjmy. Dobre, takže tu je celý náš zoznam zákazníkov označený ako 6,7 milióna dolárov. Vďaka Excel je ľahké urobiť päť najlepších. Prejdite do časti Štítky riadkov, Filtre hodnôt, horná 10. Nemusí byť horná. Môže to byť zhora alebo zdola. Nemusí byť päť. Môže to byť dvadsať, štyridsať, môže to byť čokoľvek. Najvyšších osemdesiat percent, dajte mi dostatok záznamov, aby som sa dostal k trom miliónom dolárov alebo štyrom miliónom dolárov, ale sme tu. Najvyšších päť položiek. Teraz si zapamätajte 6,7 milióna dolárov, kliknite na OK a mojím veľkým problémom je, že celkový súčet nie je 6,7 milióna. Keď to dám viceprezidentovi pre predaj, zblázni sa a povie, počkaj chvíľu,Viem, že som urobil viac ako 3,3 milióna dolárov. Správne, takže to vrátime späť, vrátime to späť a vrátime sa k pôvodným údajom.
Tento ďalší trik som sa teraz naučil počas jedného z mojich seminárov Power Excel vo Philadelphii. Toto mi ukázal chlapík menom Dan v druhom rade. Bolo to pred viac ako desiatimi rokmi, čo mi ukázal tento trik, a najskôr musíme hovoriť o filtroch. Takže ak chcete použiť bežný filter, tento filter tu, vyberiete ktorúkoľvek bunku v množine údajov a kliknete na ikonu filtra, alebo niektorí vyberú celú množinu údajov, CONTROL * a kliknú na ikonu filtra, ale je tu ešte tretia cesta. Spôsob, ktorý nikoho nezaujíma. Ak pôjdete do úplne poslednej bunky nadpisu, v mojom prípade je to Cost v L1 a pôjdete o jednu bunku doprava. Tomu hovorím čarovná bunka, netuším prečo, ale z nejakého neznámeho dôvodu môžem z tejto bunky filtrovať susedný súbor údajov. Dobre, je to ako divný spôsob a nikoho to nezaujíma.
Správne, pretože existujú dva ďalšie skutočne dobré spôsoby vyvolania filtra, nikto nemusí vedieť o magickej bunke, ale tu je vec, ktorá je vnútri kontingenčnej tabuľky šedá. Tieto filtre nemôžete používať. Je to v rozpore s pravidlami. Teraz, keď sem vyjdem, som viac než vítaný, že použijem filter, ale vo vnútri gradujú. Neviem, kto to je, kto to zašedne, ale nikdy nepočuli moje rozprávanie o čarovnej cele, pretože ak idem do úplne poslednej bunky nadpisu a pôjdem o jednu bunku doprava, pozri sa na to zabudli na sivú farbu filtra a teraz som do kontingenčnej tabuľky pridal staré automatické filtre. Takže prídem sem, prejdem k číselným filtrom, ktoré sú iné ako hodnotové filtre. Stále sa volá Top Ten. Trochu inak, poprosím prvých päť, nie prvých šesť.Prvých šesť, pretože k tomuto Filtru je Celkový súčet iba ďalším riadkom a Celkový súčet je najväčšou položkou. Potom, keď sa zobrazí výzva na položky 2 až 6, dostanem prvých päť položiek.
Dobre, tak sme tu. Skvelý hack filtra, ktorý nám dáva päť najlepších položiek a skutočný počet všetkých. Teraz v poriadku, pár vecí. Nezabudnite na čarovnú bunku. Dobre, tento filter nie je možné vypnúť, pokiaľ sa nevrátite späť do čarovnej bunky. Dobre, takže si musíte pamätať magickú bunku. Ak tiež zmeníte základné údaje a obnovíte kontingenčnú tabuľku, nebude to obnovovať filter, pretože pokiaľ spoločnosť Microsoft vie, nemáte povolené mať filter.
To je užitočné pre iné veci. Niekedy máme výrobky, ktoré idú cez vrchol. Poďme sem vo forme tabuľky. Nie je to potrebné, rád by som získal skutočné nadpisy. Gizmo, Widget, Gadgety, Doodady. V poriadku a možno ste manažérom Doodadov a musíte vidieť iba zákazníkov, ktorí mali konkrétnu hodnotu, a Doodadov. Idem teda do čarovnej bunky, zapnem Filter a potom pod Doodadmi môžem požiadať o položky, ktoré sú väčšie ako nula. Kliknite na tlačidlo OK. Dobre, tento typ filtrovania by nebol možný na bežnej kontingenčnej tabuľke, ale je to možné pomocou magickej bunky.
Dobre, vráťme zoznam späť. Vypneme tento filter a odstránime kontingenčnú tabuľku. Ak ste v programe Excel 2013 alebo novšom, ukážem vám úplne legálny spôsob, ako získať správny súčet v dolnej časti. Vložte kontingenčnú tabuľku dole dole, počnúc programom Excel 2013 toto veľmi neškodné políčko, neznie to nijako vzrušujúco, pridajte tieto údaje do dátového modelu. To odosiela údaje zo zákulisia do motora Power Pivot Engine. Vytvorte presne ten istý prehľad. Zákazníci dole na ľavej strane. Príjmy v srdci kontingenčnej tabuľky. Potom choďte na bežné filtre, hodnotové filtre hore 10. Požiadajte o prvých päť. Všimnite si znova, že po tom, čo to urobím, máme 6,7 milióna dolárov, 3,3 milióna dolárov, ale tu je rozdiel. Keď prejdem na kartu Dizajn, v časti Medzisúčty sa táto funkcia volá Zahrnúť filtrované položky do súčtov,už nie je sivé. Kontingenčná tabuľka nie je k dispozícii. Dostaneme tam malú hviezdičku a je to súhrn všetkého. Dobre, teraz samozrejme funguje iba v programe Excel 2013 alebo novšom.
Dobre, bude trvať šesť týždňov, kým sa celá táto kniha dostane na YouTube. Je tu toľko dobrých tipov. Tipy, ktoré by vám mohli začať okamžite šetriť čas. Kúpte si celú knihu hneď teraz a získate prístup ku všetkým 40, v skutočnosti je to oveľa viac ako 40 tipov. Klávesové skratky programu Excel. V tejto knihe sú všetky druhy vynikajúcich vecí.
Dobre, rekapitulácia. Takže keď urobíme 10 najlepších filtrov v kontingenčnej tabuľke, dá nám to celé, ale iba viditeľné riadky, nie tie, ktoré to odfiltrovalo. Áno, ak prejdeme na druhú kartu a vyhľadáme medzisúčty, filtrované položky a súčty, je sivá, ale existuje zvláštny spôsob, ako vyvolať filter starých údajov z magickej bunky. Úplne posledná bunka nadpisu, choďte o jednu bunku doprava, nemôžete používať filtre a kontingenčné tabuľky, ale ak prejdete do čarovnej bunky, zabudnú ju vyšednúť. Teraz vo filtri čísel požiadate o najlepších šesť, aby ste dostali prvých päť plus celkový súčet. Užitočné tiež na filtrovanie podľa konkrétnej otočnej položky: Doodady, všetko, čo malo v Doodadoch viac ako 0 alebo najlepších 5 Doodadov. Excel 2013 alebo novší, existuje iný spôsob, ako získať True Total.Začiarknite toto políčko pre údajový model a potom zahrňte filtrované položky do súčtov. Celkovú sumu získate hviezdičkou. A vďaka Danovi vo Philadelphii, ktorý ma pred viac ako desiatimi rokmi ukázal na jednom z mojich seminárov Power Excel a dal mi tento skvelý malý trik. Spôsob, ako sa filter môže vplížiť cez stenu otočného stola Club. Normálne nepovoľujú tento automatický filter.
Hej, chcem sa ti poďakovať, že si sa zastavil. Uvidíme sa nabudúce, pri ďalšom netcaste od MRExcel.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast1999.xlsx
