For nylig undrede en af mine venner sig over knapperne Drill-Down og Drill-Up i fanen Pivot Table Tools på båndet. Hvorfor er disse vedvarende nedtonede? De optager meget plads i båndet. Hvordan skal nogen bruge dem?
Efter nogle undersøgelser er der en måde at bruge dem på, men du skal bruge datamodellen og bruge Power Pivot-diagrammet til at oprette et hierarki. Hvis du ikke har fanen Power Pivot i dit bånd, bliver du nødt til at finde en medarbejder, der har knappen for at oprette hierarkiet. (Eller hvis du bare vil prøve funktionen, skal du downloade den Excel-fil, jeg oprettede: Hierarchy.xlsx)
Første trin - konverter dit pivotkildedatasæt til en tabel ved hjælp af enten Hjem - Format som tabel eller Ctrl + T. Sørg for, at indstillingen til Min tabel har overskrifter er valgt.
Brug Insert - Pivot Table. I dialogboksen Opret pivottabel skal du vælge feltet Tilføj disse data til datamodellen.
Her er pivottabelfelterne, inden du opretter hierarkiet.
Klik på ikonet Administrer på fanen Power Pivot i båndet. (Mange forekomster af Excel 2013 og 2016 har ikke denne fane. Den vises ikke på Mac.)
Klik på ikonet Diagramvisning i vinduet Power Pivot for Excel. Det er tæt på højre side af fanen Hjem.
Brug størrelse på håndtaget i nederste højre hjørne af tabel1 for at forstørre tabel1, så du kan se alle dine felter. Klik på det første element i dit hierarki (Kontinent i mit eksempel). Skift-Klik på det sidste element i hierarkiet (By i mit eksempel). Du kan også klikke på et element og Ctrl-klikke på andre, hvis hierarkifelterne ikke er tilstødende. Når du har valgt markerne, skal du højreklikke på et af felterne og vælge Opret hierarki.
Hierarki1 oprettes og venter på, at du skriver et nyt navn. Jeg vil navngive mit hierarki Geografi. Hvis du klikker væk fra Power Pivot, er Hierarchy1 ikke længere i Omdøbstilstand. Højreklik på Hierachy1, og vælg Omdøb.
Luk Power Pivot, og vend tilbage til Excel. Pivottabelfelterne viser nu Geografihierarkiet og Flere felter. Dit salgsfelt er skjult under Flere felter. Jeg forstår lidt, hvorfor de skjuler kontinent, land, region, territorium, by under flere felter. Men jeg forstår ikke, hvorfor de skjuler salg under flere felter.
For at oprette drejetabellen skal du markere afkrydsningsfeltet for Geografihierarkiet. Åbn flere felter ved at klikke på trekanten ved siden af den. Vælg salg.
Der er meget at bemærke i billedet ovenfor. Når du oprindeligt opretter pivottabellen, er den aktive celle på A3, og ikonet Drill Down er nedtonet. Men hvis du flytter cellemarkøren til Nordamerika i A4, vil du se, at Drill Down er aktiveret.
Med cellemarkøren i Nordamerika skal du klikke på Bore ned, og kontinentet erstattes af land.
Med cellemarkøren på Canada skal du klikke på Drill Down og du vil se det østlige Canada og det vestlige Canada. Bemærk på dette tidspunkt, både knapperne Drill Down og Drill Up er aktiveret.
Jeg klikkede på Drill Up for at vende tilbage til land. Vælg USA. Bor ned tre gange, og jeg ender i byerne i Carolinas-regionen. På dette tidspunkt er knappen Drill Down nedtonet.
Bemærk, at du fra kontinentniveau kan klikke på Udvid felt for at vise kontinenter og lande. Vælg derefter Udvid felt fra det første land for at afsløre regioner. Fra den første region skal du bruge Udvid felt til at vise territorier. Fra det første område skal du klikke på Udvid felt for at afsløre by.
Alle ovenstående skærmbilleder viser pivottabellen i min standardvisning af Vis i tabelform. Hvis dine drejetabeller oprettes i kompakt form, kan du se udsigten nedenfor. (For at lære at få alle dine fremtidige drejetabeller til at starte i tabelform, se denne video).
Hvad er fordelen ved hierarkiet? Jeg forsøgte at oprette en almindelig pivottabel uden et hierarki. Jeg har stadig evnen til at udvide og skjule felter. Men hvis jeg kun vil vise regionerne i Canada, bliver jeg nødt til at tilføje et udsnit eller Rapportfilter.
Se video
Videoudskrift
Lær Excel fra Podcast, afsnit 2196: Drill Up and Drill Down in Pivot Tables.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Der er et mysterium i pivottabeller. Hvis jeg indsætter en pivottabel her, ser du, at vi har Drill Up og Drill Down-felter, men de lyser aldrig op. Hvad sker der med dette? Hvorfor har vi disse? Hvordan får vi dem til at fungere? Okay, dette er et godt, godt spørgsmål, og desværre har jeg det dårligt med dette. Jeg forsøger at gøre hele mit liv i Excel ikke nogensinde ved hjælp af fanen Power Pivot. Jeg vil ikke have, at du skal betale de ekstra $ 2 om måneden for Pro Plus-versionen af Office 365, men dette er en - dette er en - hvor vi skal bruge de ekstra $ 2 om måneden eller finde nogen, der har de ekstra $ 2 om måneden for at indstille dette.
Jeg tager dette dataformat som en tabel. Det betyder ikke noget, hvilket format jeg vælger, formatet er ikke vigtigt; bare at skaffe os et bord er den vigtige del. Power Pivot, vi vil føje denne tabel til vores datamodel og derefter klikke på Administrer. Okay, så her er vores tabel i datamodellen. Vi er nødt til at gå til Diagram View, nu skal vi gøre dette lidt bredere, så vi kan se alle felterne. Jeg vælger kontinent; Jeg går til Skift + klik på By. Nu udgør det min Drill Down, Drill Up, hierarkiet. Og så højreklikker vi og siger Opret heirarki. Og de giver os et navn - Jeg vil skrive "Geografi" for mit hierarki, sådan. Fantastisk, nu med den ene ændring indsætter vi en pivottabel - og dette vil være en datamodel pivottabel - og du ser, at vi kan tilføje geografi som sit eget hierarki.
Nu er den ene ting, som jeg ikke særlig godt kan lide ved dette, alt andet flytter til Flere felter. I orden? Så vi vælger geografi, og den flyver til venstre side. Og selvom det er fantastisk, skal jeg også vælge indtægter, og de tog de felter, der ikke var en del af hierarkiet, og flyttede dem til Flere felter. Så det er som, jeg forstår det, de prøver at skjule de felter, som jeg ikke skal vælge, men i færd med at gøre det skjulte de også Flere felter - indtægterne eller salget hernede. I orden. Så lidt frustrerende er vi nødt til at gå til flere felter for at få de felter, der ikke er en del af geografien, men det er sådan, det går.
I orden. Så nu, nu hvor vi har det, lad os se på, hvad der fungerer her. Jeg sidder på kontinentet, jeg går til fanen Analyser, og intet lyser op, det fungerede ikke. Skyde! Nej, det fungerede, du skal bare komme til Nordamerika, og så kan jeg bore ned, og det erstatter kontinentet med land. Og så fra Canada kan jeg bore ned og hente det østlige Canada og det vestlige Canada. Fra det østlige Canada går jeg ned Ontario og Quebec. Ontario, jeg får disse byer, jeg kan bore Drill Up, Drill Up og vælge USA; Bor ned, bor ned, bor ned. Okay, så det fungerer sådan.
Prøv det, du skal have Power Pivot-fanen eller finde nogen med en Power Pivot-fane. Hvis du bare vil prøve det, skal du se i YouTube-beskrivelsen, der vil være et link til websiden, og der er et sted der på websiden, hvor du kan downloade denne fil, og du bør kunne bruge hierarkiet, selvom du ikke har Power Pivot-fanen. Hvis du er i Excel 2016 eller Office 365, skal det fungere.
Nu ved du, se, jeg antager, at den ting, som jeg ikke er sikker på, at jeg er fan af, er det faktum, at de slipper af med de andre oplysninger i modsætning til at bruge Udvid-ikonet, som derefter udvides til den næste gruppe og den næste gruppe og den næste gruppe. Vi har altid haft udvidelsesikonet, men selv da fungerer det lidt anderledes. Her, hvis jeg ville, kan jeg faktisk sidde der i Nordamerika og udvide et niveau ad gangen uden at skulle vælge hver yderligere fra datamodellen. Det ser ud til, at vi er nødt til at flytte cellemarkøren en ad gangen.
Okay, nu blev dette tip virkelig bare opdaget. Excel MVP'erne havde en samtale med Excel-teamet om disse knapper, så de er ikke dækket af denne bog. Men mange andre gode tip dækket af LIVe, de 54 største tip nogensinde.
Afslutning i dag: Hvorfor er Drill Up and Drill Down konstant nedtonet? Du skal oprette et hierarki. For at skabe et hierarki skal du gå ind i Power Pivot; ind i diagrammet; vælg felterne til arvingen; og højreklik derefter på; og Opret hierarki.
Jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra.
