Kontingenční tabulka Top 10 Filter poskytuje celkem viditelných řádků
Kontingenční tabulky nabízejí filtr Top 10. To je bezva. Je flexibilní. Ale nenávidím to a řeknu vám proč.
Zde je kontingenční tabulka zobrazující tržby podle zákazníků. Celkové tržby jsou 6,7 milionu USD.
Co když má můj manažer pozornost zlaté rybky a chce vidět pouze pět nejlepších zákazníků?
Chcete-li začít, otevřete rozevírací nabídku v A3 a vyberte Filtry hodnot, Top 10.
Vysoce flexibilní dialogové okno Top 10 Filter umožňuje Top / Bottom. Může dělat 10, 5 nebo jiné číslo. Můžete požádat o prvních pět položek, nejlepších 80% nebo dostatek zákazníků, abyste se dostali na 5 milionů dolarů.
Ale tady je problém: Výsledná sestava zobrazuje pět zákazníků a celkem od těchto zákazníků místo celkových od všech.
Ale nejdříve několik důležitých slov o automatickém filtru
Uvědomuji si, že se to jeví jako otázka mimo zeď. Pokud chcete zapnout rozevírací nabídky Filtr v běžné sadě dat, jak to uděláte? Tady jsou tři opravdu běžné způsoby:
- Vyberte jednu buňku ve svých datech a klikněte na ikonu Filtr na kartě Data.
- Vyberte všechna svá data pomocí Ctrl + * a klikněte na ikonu Filtr na kartě Data.
- Stisknutím kláves Ctrl + T naformátujte data jako tabulku.
To jsou tři opravdu dobré způsoby. Pokud někoho z nich znáte, není nutné znát jiný způsob. Ale tady je neuvěřitelně temný, ale magický způsob, jak zapnout filtr:
- Přejít na řádek záhlaví, přejděte do buňky záhlaví zcela vpravo. Přesuňte jednu buňku doprava. Z nějakého neznámého důvodu, když jste v této buňce a kliknete na ikonu Filtr, Excel filtruje datovou sadu nalevo. Netuším, proč to funguje. Opravdu to nestojí za řeč, protože již existují tři opravdu dobré způsoby, jak zapnout rozbalovací nabídky Filtr. Tuto buňku říkám magická buňka.
A teď zpět na kontingenční tabulky …
Existuje tedy pravidlo, které říká, že nemůžete použít automatické filtry, když jste v kontingenční tabulce. Viz. níže? Ikona Filtr je zobrazena šedě, protože jsem v kontingenční tabulce vybral buňku.
Nikdy jsem neuvažoval, proč to Microsoft zašedne. Musí to být něco interního, co říká, že automatický filtr a kontingenční tabulka nemohou existovat společně. Takže v týmu aplikace Excel je někdo, kdo má na starosti šedou ikonu Filtr. Ten člověk nikdy neslyšel o magické cele. Vyberte buňku v kontingenční tabulce a filtr se zobrazí šedě. Klikněte mimo kontingenční tabulku a filtr je znovu povolen.
Ale počkej. A co magická buňka, o které jsem ti právě řekl? Pokud kliknete na buňku napravo od posledního záhlaví, Excel zapomene na šedou ikonu Filtr!
Excel jistě přidá rozevírací nabídky automatického filtru do horního řádku kontingenční tabulky. A automatický filtr funguje jinak než filtry kontingenční tabulky. Přejděte do rozbalovací nabídky Výnosy a vyberte Filtry čísel, Top 10…
V dialogovém okně Top 10 AutoFilter vyberte Top 6 Items. To není překlep … Pokud chcete pět zákazníků, vyberte 6. Pokud chcete 10 zákazníků, vyberte 11.
Pro automatický filtr je řádek celkového součtu největší položkou v datech. Prvních pět zákazníků obsazuje v datech pozice 2 až 6.
Pozor
Je zřejmé, že tímto trikem trháte díru ve struktuře aplikace Excel. Pokud později změníte podkladová data a obnovíte kontingenční tabulku, Excel filtr neobnoví, protože pokud Microsoft ví, neexistuje způsob, jak použít filtr na kontingenční tabulku!
Poznámka
Naším cílem je udržet toto tajemství před Microsoftem, protože je to docela skvělá funkce. Je to už nějakou dobu „rozbité“, takže existuje spousta lidí, kteří by se na to mohli nyní spoléhat.
Úplně legální řešení v aplikaci Excel 2013+Pokud chcete kontingenční tabulku zobrazující pět nejlepších zákazníků, ale celkový počet všech zákazníků, musíte přesunout svá data mimo Excel. Pokud máte Excel 2013 nebo 2016, můžete to udělat velmi pohodlným způsobem. Abych vám to ukázal, odstranil jsem původní kontingenční tabulku. Vyberte Vložit, Kontingenční tabulka. Než kliknete na OK, zaškrtněte políčko Přidat tato data do datového modelu.
Sestavte si kontingenční tabulku jako obvykle. Pomocí rozbalovací nabídky v A3 vyberte Filtry hodnot, Top 10 a požádejte o prvních pět zákazníků. S vybranou jednou buňkou v kontingenční tabulce přejděte na kartu Návrh na pásu karet a otevřete rozbalovací nabídku Mezisoučty. Konečnou volbou v rozbalovací nabídce je Zahrnout filtrované položky do součtu. Normálně je tato volba zobrazena šedě. Ale protože jsou data uložena v datovém modelu namísto normální pivotní mezipaměti, je tato možnost nyní k dispozici.
Vyberte možnost Zahrnout filtrované položky do součtu a váš celkový součet nyní obsahuje hvězdičku a součet všech dat.
Tento trik ke mně původně přišel od Dana na mém semináři ve Filadelfii. Děkujeme Miguelovi Caballerovi za navržení této funkce.
Sledovat video
- Kontingenční tabulka Top 10 Filter poskytuje celkem viditelných řádků
- Zahrnout filtrované položky do součtu je zašedlé
- Zvláštní způsob, jak vyvolat datový filtr z magické buňky
- Filtry dat nejsou v kontingenčních tabulkách povoleny
- Excel selže, aby šedý filtr dat z magické buňky
- Požádejte o nejlepších 6, abyste získali nejlepších 5 plus Grand Total
- Užitečné pro filtrování podle konkrétní kontingenční položky
- Excel 2013 nebo novější: Jiný způsob, jak získat skutečný součet
- Odesílejte svá data prostřednictvím datového modelu
- K dispozici budou zahrnout filtrované položky do součtu
- Získejte celkem s hvězdičkou
- Tento trik jsem se naučil před 10 a více lety od Dana ve Filadelfii
Přepis videa
Learn Excel for Podcast, Episode 1999 - Pivot Table True Top Five
Podcastuji celou tuto knihu. Existuje seznam skladeb. Kliknutím na I v pravém horním rohu tento seznam sledujete. Vítejte zpět na netcastu. Jsem Bill Jelen.
Dobře, takže vytvoříme kontingenční tabulku a chceme ukázat, nejen všechny zákazníky, ale pouze prvních pět zákazníků. VLOŽIT, kontingenční tabulka. Dobře, dám Zákazníka doleva a Příjmy. Dobře, takže tady je celý náš seznam zákazníků označený jako 6,7 milionu dolarů. Excel usnadňuje vytváření nejlepších pěti. Přejít na Řádkové štítky, Filtry hodnot, nahoře 10. Nemusí být nahoře. Může to být nahoře nebo dole. Nemusí to být pět. Může to být dvacet, čtyřicet, může to být cokoli. Prvních osmdesát procent, dej mi dostatek záznamů, abych se dostal na tři miliony dolarů nebo čtyři miliony dolarů, ale tady to máme. Prvních pět položek. Nyní si pamatujte 6,7 milionu dolarů, klikněte na OK a můj velký problém je, že celkový součet není 6,7 milionu. Když to dám viceprezidentovi pro prodej, vyděsí se a řekne, počkej chvíli,Vím, že jsem udělal více než 3,3 milionu dolarů. Správně, takže to vrátíme, vrátíme to a vrátíme se k původním datům.
Tento další trik jsem se naučil během jednoho ze svých seminářů Power Excel ve Filadelfii. Chlapík jménem Dan ve druhé řadě mi to ukázal. Bylo to před více než deseti lety, co mi tento trik ukázal, a nejprve si musíme promluvit o filtrech. Normálně tedy, pokud budete používat běžný filtr, tento filtr zde, vyberete libovolnou buňku v datové sadě a kliknete na ikonu filtru, nebo někteří lidé vyberou celou sadu dat, CONTROL * a kliknete na ikonu filtru, ale je tu ještě třetí cesta. Způsob, o který se nikdo nestará. Pokud přejdete na úplně poslední buňku záhlaví, v mém případě je to Cost v L1 a přejdete o jednu buňku doprava. Říkám tomu kouzelná buňka, netuším proč, ale z nějakého neznámého důvodu mohu z této buňky filtrovat sousední datovou sadu. Dobře, je to jako divný způsob a nikoho to nezajímá.
Správně, protože existují dva další opravdu dobré způsoby, jak vyvolat filtr, nikdo nemusí vědět o magické buňce, ale tady je ta věc, která je vidět uvnitř kontingenční tabulky, je zašedlá. Tyto filtry nesmíte používat. Je to v rozporu s pravidly. Teď, když sem přijdu, jsem více než vítán použít Filtr, ale uvnitř se klasifikují. Nevím, kdo je ten, kdo to zašedne, ale nikdy neslyšeli mé malé řeči o magické buňce, protože když půjdu do úplně poslední buňky záhlaví a půjdu o jednu buňku doprava, podívej se na to, zapomněli na šedý filtr a nyní jsem do kontingenční tabulky přidal staré automatické filtry. Přišel jsem sem, přejděte na Číselné filtry, které se liší od Hodnotových filtrů. Stále se tomu říká Top Ten. Trochu jiné, budu žádat o prvních pět, ne prvních šest.Prvních šest, protože k tomuto Filtru je Celkový součet jen dalším řádkem a Celkový součet je největší položkou a poté, když jsem požádán o položky 2 až 6, dostanu prvních pět položek.
Dobře, tak jsme tady. Skvělý hack na filtry, který nám dává pět nejlepších položek a skutečný součet všech. Dobře, pár věcí. Nezapomeňte na kouzelnou buňku. Dobře, není možné tento filtr vypnout, pokud se nevrátíte zpět do magické buňky. Dobře, takže si musíte pamatovat magickou buňku. Také pokud změníte podkladová data a obnovíte kontingenční tabulku, nebudou aktualizovat filtr, protože pokud Microsoft ví, nemáte povoleno mít filtr.
To je užitečné pro jiné věci. Někdy máme produkty, které jdou přes vrchol. Pojďme sem ve formě tabulky. Není to nutné, rád bych dostal skutečné nadpisy. Gizmo, Widget, Gadgety, Doodady. Dobře a možná jste manažerem Doodadů a musíte vidět jen zákazníky, kteří měli určitou hodnotu, a Doodady. Takže jdu do magické buňky, zapnu filtr a pak pod Doodadem mohu požádat o položky, které jsou větší než nula. Klikněte na OK. Dobře, tento typ filtrování by na běžné kontingenční tabulce nebyl možný, ale je to možné pomocí magické buňky.
Alright now let's undo the list. Let's turn off this Filter and remove the Pivot Table, and if you're in Excel 2013 or new, I'm going to show you a completely legal way to get the correct total at the bottom. Insert Pivot Table, down here at the bottom, starting in Excel 2013 this very innocuous box, doesn't sound very exciting, add this data to the Data Model. That sends the data, behind the scenes, to the Power Pivot Engine. Build the exact same report. Customers down the left-hand side. Revenue in the heart of the Pivot Table. Then, go to the regular Filters, the Value Filters top 10. Ask for the top five. Notice again we have 6.7 million dollars after I do this, 3.3 million dollars but here's the difference. When I go to the Design Tab, under Subtotals, this feature called Include Filtered Items in Totals, is no longer grayed out. At a regular Pivot Table is not available. We get a little asterisk there and it's the total of everything. Alright, now of course that only works in Excel 2013 or newer.
Alright it's going to take six weeks for me to get this entire book out here on YouTube. There's so many good tips here. Tips that could start to save you time, right away. Buy the entire book right now and you'll have access to all 40, It's actually a lot more than 40 tips. Excel shortcut keys. All kinds of great stuff in this book.
Alright, recap. So when we do a Pivot Table top 10 Filter, it gives us the total but only the visible rows, not the stuff that it filtered out. Yeah if we go to the second tab and look for Subtotals, Filtered Items and Totals, it's grayed out, but there is an odd way to invoke the Old Data Filter from the magic cell. The very last heading cell, go one cell to the right, you can't use Filters and Pivot Tables, but if you go to the magic cell they forget to gray it out. Now in the Number Filter, you ask for the top six to get the top five, plus the grand total. Also useful for filtering to a specific Pivot Item: Doodads, anything that had greater than 0 in Doodads or top 5 Doodads. Excel 2013 or newer, there's a different way to get the True Total. Check that box for the Data Model and then include Filtered Items in Totals will be available. You get the total with an asterisk. And thanks to Dan in Philadelphia who showed me at one of my Power Excel Seminars, more than ten years ago, and gave me this great little trick. A way for the Filter to sneak through the Club Pivot Table Wall. They normally don't allow that Auto Filter.
Hej, chci ti poděkovat, že jsi se zastavil. Uvidíme se příště, na dalším netcastu od MRExcel.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast1999.xlsx
