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, ktorá zobrazuje príjmy podľa zákazníka. Celkové tržby sú 6,7 milióna dolárov. Všimnite si, že najväčší zákazník, Roto-Rooter, predstavuje 9% z celkových výnosov.
Č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. Roto-Rooter, ktorý predtým predstavoval 9% z celkového počtu, je 23% z nového celkového počtu.
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 filtra na karte Údaje alebo stlačte kombináciu klávesov Ctrl + Shift + L.
- 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 potom 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 čarovná bunka.
A teraz späť na kontingenčné tabuľky
Existuje pravidlo, ktoré hovorí, že keď ste v kontingenčnej tabuľke, nemôžete použiť automatický filter. Pozri nižšie? Ikona filtra je sivá, pretože som vybral bunku v kontingenčnej tabuľke.
Neviem, prečo to spoločnosť Microsoft zašedne. 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čnú tabuľku a filter je znova povolený.
Ale počkaj. A čo magická 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 filter kontingenčnej tabuľky. Prejdite do rozbaľovacej ponuky Výnosy a vyberte položku Filtre čísel, Prvých 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, zvoľte 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 základné ú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 toto 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 kontingenčnú tabuľku zobrazujúcu päť najlepších zákazníkov, ale celkový počet všetkých zákazníkov, musíte svoje údaje presunúť mimo Excel. Ak máte v systéme Windows spustený program Excel 2013 alebo novší, existuje veľmi pohodlný spôsob, ako to urobiť. 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. Po vybratí jednej bunky v kontingenčnej tabuľke prejdite na kartu Návrh na páse s nástrojmi 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 súčtov a váš celkový súčet teraz obsahuje hviezdičku a súhrn všetkých údajov, ako je uvedené nižšie.
Tento trik s kúzelnými bunkami ku mne pôvodne prišiel od Dana na mojom seminári vo Philadelphii a o 15 rokov neskôr ho zopakoval iný Dan ako môj seminár v Cincinnati. Ďakujem Miguelovi Caballerovi za navrhnutie tejto funkcie.
