Nedávno môj priateľ uvažoval o tlačidlách Prispôsobiť a Prispôsobiť na karte Nástroje kontingenčnej tabuľky na páse s nástrojmi. Prečo sú neustále sivé? V Ribbone zaberajú veľa miesta. Ako ich má niekto používať?
Po nejakom výskume existuje spôsob, ako ich použiť, ale na vytvorenie hierarchie musíte použiť dátový model a použiť zobrazenie diagramu Power Pivot. Ak na páse s nástrojmi nemáte kartu Power Pivot, budete musieť nájsť spolupracovníka, ktorý má dané tlačidlo, aby sa vytvorila hierarchia. (Alebo ak si chcete iba vyskúšať túto funkciu, stiahnite si súbor Excel, ktorý som vytvoril: Hierarchy.xlsx)
Prvý krok - konvertujte zdrojovú dátovú sadu kontingenčných súborov na tabuľku pomocou položky Domov - Formátovať ako tabuľku alebo Ctrl + T. Uistite sa, že je vybratá možnosť Moja tabuľka obsahuje hlavičky.
Použite Vložiť - kontingenčná tabuľka. V dialógovom okne Vytvorenie kontingenčnej tabuľky začiarknite políčko Pridať tieto údaje do údajového modelu.
Pred vytvorením hierarchie sú tu polia kontingenčnej tabuľky.
Kliknite na ikonu Spravovať na karte Power Pivot na páse s nástrojmi. (Veľa inštancií programu Excel 2013 a 2016 nemá túto kartu. Na počítačoch Mac sa nezobrazuje.)
V okne Power Pivot pre Excel kliknite na ikonu Zobrazenie diagramu. Nachádza sa v pravej časti karty Domov.
Pomocou rukoväti na zmenu veľkosti v pravom dolnom rohu tabuľky1 tabuľku zväčšíte, aby ste videli všetky svoje polia. Kliknite na prvú položku vo vašej hierarchii (v mojom príklade kontinent). Shift-kliknite na poslednú položku v hierarchii (v mojom príklade mesto). Môžete tiež kliknúť na jednu položku a Ctrl-kliknúť na ďalšie, ak polia hierarchie nesusedia. Po vybratí polí kliknite pravým tlačidlom myši na ktorékoľvek z polí a vyberte príkaz Vytvoriť hierarchiu.
Hierarchy1 je vytvorená a čaká na zadanie nového názvu. Svoju hierarchiu pomenujem Geografia. Ak kliknete ďalej od doplnku Power Pivot, hierarchia1 už nebude v režime premenovania. Pravým tlačidlom myši kliknite na Hierachy1 a vyberte príkaz Premenovať.
Zatvorte Power Pivot a vráťte sa do Excelu. Polia kontingenčnej tabuľky teraz zobrazujú geografickú hierarchiu a ďalšie polia. Vaše pole predaja je skryté pod položkou Viac polí. Trochu chápem, prečo skrývajú Kontinent, Krajina, Región, Územie, Mesto pod Viac polí. Ale nerozumiem, prečo skrývajú Predaj pod Viac polí.
Ak chcete zostaviť kontingenčnú tabuľku, začiarknite políčko pre geografickú hierarchiu. Kliknutím na trojuholník vedľa neho otvoríte ďalšie polia. Vyberte možnosť Predaj.
Na obrázku vyššie je veľa čo si všimnúť. Keď spočiatku vytvárate kontingenčnú tabuľku, aktívna bunka je na A3 a ikona Hĺbková analýza je sivá. Ak však presuniete ukazovateľ bunky do A4 v Severnej Amerike, uvidíte, že je povolené hĺbkové testovanie.
S ukazovateľom bunky v Severnej Amerike kliknite na možnosť Podrobný rozpis a kontinent bude nahradený krajinou.
S bunkovým ukazovateľom na Kanadu kliknite na položku Prerobiť a uvidíte východnú Kanadu a západnú Kanadu. Upozorňujeme, že v tomto okamihu sú povolené tlačidlá Posunúť aj Posunúť nahor.
Klikol som na možnosť Vyvŕtať a vrátim sa do krajiny. Vyberte USA. Trikrát rozvaľkajte a skončím v mestách v regióne Carolinas. V tomto okamihu je tlačidlo Drill Down sivé.
Upozorňujeme, že na úrovni kontinentu môžete kliknutím na položku Rozbaliť pole zobraziť kontinenty a krajiny. Potom z prvej krajiny vyberte Rozbaliť pole a odhalte Regióny. V prvom regióne použite Rozbaliť pole na zobrazenie Územia. Na prvom území kliknite na Rozbaliť pole, aby ste odhalili Mesto.
Všetky vyššie uvedené snímky obrazovky zobrazujú kontingenčnú tabuľku v mojom predvolenom zobrazení Zobraziť v tabuľkovej podobe. Ak sú vaše kontingenčné tabuľky vytvorené v kompaktnom formáte, uvidíte nasledujúce zobrazenie. (Ak sa chcete dozvedieť, ako majú všetky vaše budúce kontingenčné tabuľky začínať v tabuľkovej podobe, pozrite si toto video.)
Aká je výhoda hierarchie? Skúšal som vytvoriť bežnú kontingenčnú tabuľku bez hierarchie. Stále mám možnosť rozbaľovať a zbaliť polia. Ak však chcem zobraziť iba oblasti v Kanade, musel by som pridať rezač alebo filter prehľadov.
Pozeraj video
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2196: Prejdite hore a dole v kontingenčných tabuľkách.
Ahoj, vitaj späť na netcastu, som Bill Jelen. V kontingenčných tabuľkách je tajomstvo. Ak sem vložím kontingenčnú tabuľku, uvidíte, že máme polia Drill Up a Drill Down, ale nikdy sa nerozsvietia. Čo sa deje? Prečo ich máme? Ako ich prinútime fungovať? Dobre, je to skvelá, skvelá otázka a bohužiaľ sa z toho cítim zle. Snažím sa robiť celý svoj život v Exceli, nikdy nepoužívam kartu Power Pivot. Nechcem, aby ste museli platiť ďalšie 2 doláre mesačne za verziu Pro Plus Office 365, ale toto je jedna - táto je jedna - kde musíme minúť ďalšie 2 doláre mesačne alebo nájsť niekoho, kto má ďalšie 2 doláre mesačne na jeho nastavenie.
Tento údajový formát budem brať ako tabuľku. Nezáleží na tom, aký formát si vyberiem, formát nie je dôležitý; už len dostať stôl je dôležitá súčasť. Power Pivot, pridáme túto tabuľku do nášho dátového modelu a potom klikneme na možnosť Spravovať. Dobre, takže tu je naša tabuľka v dátovom modeli. Musíme ísť do zobrazenia diagramu, teraz ho trochu rozšírime, aby sme videli všetky polia. Idem zvoliť kontinent; Idem na Shift + klik na City. Teraz je to moja hierarchia Drill Down, Drill Up. A potom klikneme pravým tlačidlom myši a povieme Create Heirarchy. A dajú nám meno - napíšem napríklad „geografia“ pre moju hierarchiu. Skvelé, teraz s touto zmenou vložíme kontingenčnú tabuľku - a bude to kontingenčná tabuľka dátového modelu - a uvidíte, že môžeme pridať geografiu ako svoju vlastnú hierarchiu.
Jedna vec, ktorá sa mi na tom zvlášť nepáči, je, že všetko ostatné sa presúva do More Fields. Dobre? Vyberieme teda Geografiu a tá letí na ľavú stranu. A aj keď je to skvelé, musím si tiež zvoliť Revenue a oni vzali polia, ktoré neboli súčasťou Hierarchie, a presunuli ich do More Fields. Takže je to ako, chápem, pokúšajú sa skryť polia, ktoré som si nemal vybrať, ale v priebehu toho skryli aj Viac polí - Výnosy alebo Predaj tu. Dobre. Takže trochu frustrujúce musíme ísť na ďalšie polia, aby sme dostali polia, ktoré nie sú súčasťou Geografie, ale tak to chodí.
Dobre. Takže, teraz, keď máme, poďme sa pozrieť na to, čo tu funguje. Sedím na kontinente, idem na kartu Analyzovať a nič sa nerozsvieti, nefungovalo to. Strieľajte! Nie, fungovalo to, stačí prísť do Severnej Ameriky a potom môžem vykonať Drill Down a nahradí kontinent kontinentom Country. A potom z Kanady môžem prejsť podrobnosťou a získať východnú Kanadu a západnú Kanadu. Z východnej Kanady rozbaliť, mám Ontario a Quebec. Ontario, mám tie mestá, môžem to rozcvičiť, Drill Up, Drill Up a zvoliť USA; Vŕtačka dole, vŕtačka dole, vŕtačka dole. Dobre, tak to funguje.
Vyskúšajte to, musíte mať kartu Power Pivot alebo nájsť niekoho, kto má kartu Power Pivot. Ak si to chcete len vyskúšať, pozrite sa do popisu YouTube, kde bude odkaz na webovú stránku a na webovej stránke je miesto, kde si môžete tento súbor stiahnuť, a hierarchiu by ste mali používať, aj keď nemáte kartu Power Pivot. Ak ste v programe Excel 2016 alebo Office 365, malo by to fungovať.
Teraz, viete, myslím, že tým, o čom si nie som istý, že som jeho fanúšikom, je skutočnosť, že sa zbavujú ostatných informácií, na rozdiel od použitia ikony Rozbaliť, ktorá by sa potom rozšírila na ďalšia skupina a ďalšia skupina a ďalšia skupina. Vždy sme mali ikonu Expand, ale aj tak to funguje trochu inak. Tu, ak by som chcel, môžem v skutočnosti sedieť v Severnej Amerike a rozširovať jednu úroveň po druhej bez toho, aby som musel vyberať každú ďalšiu z dátového modelu. Vyzerá to, že ukazovateľ bunky musíme posúvať po bitoch.
Dobre, teraz, tento tip bol skutočne len, trochu, objavený. Programy MVP programu Excel viedli rozhovor s tímom programu Excel o týchto tlačidlách, takže táto kniha sa nimi nezaoberá. Ale veľa ďalších skvelých tipov, ktoré obsahuje LIVe, 54 najlepších tipov všetkých čias.
Stručné zhrnutie pre dnešok: Prečo je postup Drill Up and Drill Down neustále šedý? Musíte si vytvoriť hierarchiu. Ak chcete vytvoriť hierarchiu, musíte prejsť do doplnku Power Pivot; do zobrazenia diagramu; vyberte polia pre heirarchiu; a potom kliknite pravým tlačidlom myši; a Vytvorte hierarchiu.
Chcem vám poďakovať, že ste sa zastavili, uvidíme sa nabudúce pri ďalšom netcaste z.
