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, která zobrazuje tržby podle zákazníků. Celkové tržby jsou 6,7 milionu USD. Všimněte si, že největší zákazník, Roto-Rooter, je 9% z celkových výnosů.
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. Roto-Rooter, který byl dříve 9% z celkového počtu, je 23% z nového celkového počtu.
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ď. Chcete-li zapnout rozbalovací 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 nebo stiskněte Ctrl + Shift + L.
- 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řejděte do řady záhlaví a poté 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 pravidlo, které říká, že nemůžete použít automatický filtr, když jste v kontingenční tabulce. Viz. níže? Ikona Filtr je zobrazena šedě, protože jsem v kontingenční tabulce vybral buňku.
Nevím, 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 kouzelná 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ž filtr 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 žádný 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 data mimo Excel. Pokud máte v systému Windows spuštěný Excel 2013 nebo novější, 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. Před kliknutím 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, jak je uvedeno níže.
Tento trik s magickými buňkami ke mně původně přišel od Dana na mém semináři ve Filadelfii a o 15 let později ho zopakoval jiný Dan z mého semináře v Cincinnati. Děkujeme Miguelovi Caballerovi za navržení této funkce.
