Nyligen undrade en vän till mig om knapparna Drill-Down och Drill-Up på fliken Pivot Table Tools i menyfliksområdet. Varför är dessa ständigt nedtonade? De tar mycket plats i menyfliksområdet. Hur ska någon använda dem?
Efter lite forskning finns det ett sätt att använda dem, men du måste använda datamodellen och använda Power Pivot-diagramvyn för att skapa en hierarki. Om du inte har Power Pivot-fliken i menyfliksområdet måste du hitta en medarbetare som har knappen för att skapa hierarkin. (Eller, om du bara vill prova funktionen, ladda ner Excel-filen som jag skapade: Hierarchy.xlsx)
Första steget - konvertera din pivotkällsats till en tabell med antingen Hem - Format som tabell eller Ctrl + T. Se till att alternativet för Mitt bord har rubriker är valt.
Använd Insert - Pivot Table. I rutan Skapa pivottabell väljer du rutan Lägg till dessa data i datamodellen.
Här är pivottabellfälten innan du skapar hierarkin.
Klicka på ikonen Hantera på fliken Power Pivot i menyfliksområdet. (Många fall av Excel 2013 och 2016 har inte den här fliken. Den visas inte på Mac.)
Klicka på ikonen Diagramvy i Power Pivot for Excel-fönstret. Det ligger nära höger sida av fliken Hem.
Använd resizehandtaget i det nedre högra hörnet av Table1 för att förstora Table1 så att du kan se alla dina fält. Klicka på det första objektet i din hierarki (Kontinent i mitt exempel). Skift-Klicka på det sista objektet i hierarkin (Stad i mitt exempel). Du kan också klicka på ett objekt och Ctrl-klicka på andra om hierarkifälten inte ligger intill varandra. När du har valt fälten högerklickar du på något av fälten och väljer Skapa hierarki.
Hierarki1 skapas och väntar på att du skriver ett nytt namn. Jag kommer att namnge min hierarki Geografi. Om du klickar bort från Power Pivot är Hierarchy1 inte längre i Byt namn-läge. Högerklicka på Hierachy1 och välj Byt namn.
Stäng Power Pivot och återgå till Excel. Pivottabellfälten visar nu Geografihierarkin och Fler fält. Ditt försäljningsfält är dolt under Fler fält. Jag förstår något varför de gömmer kontinent, land, region, territorium, stad under fler fält. Men jag förstår inte varför de döljer försäljningen under fler fält.
För att bygga pivottabellen markerar du kryssrutan för Geografihierarki. Öppna fler fält genom att klicka på triangeln bredvid den. Välj Försäljning.
Det finns mycket att märka i bilden ovan. När du först skapar pivottabellen är den aktiva cellen på A3 och Drill Down-ikonen är gråtonad. Men om du flyttar cellpekaren till Nordamerika i A4 ser du att Drill Down är aktiverat.
Med cellpekaren i Nordamerika klickar du på Borra ner och kontinent ersätts av land.
Med cellpekaren på Kanada klickar du på Drill Down så visas Östra Kanada och Västra Kanada. Observera vid denna tidpunkt, både knapparna Drill Down och Drill Up är aktiverade.
Jag klickade på Drill Up för att återvända till Country. Välj USA. Borra ner tre gånger och jag hamnar i städerna i Carolinas-regionen. Vid denna punkt är Drill Down-knappen gråtonad.
Observera att från kontinentnivån kan du klicka på Expandera fält för att visa kontinenter och länder. Välj sedan Expand Field från det första landet för att avslöja regioner. Från den första regionen använder du Expand Field för att visa territorier. Från det första territoriet klickar du på Expandera fält för att avslöja stad.
Alla skärmdumpar ovan visar pivottabellen i min standardvy av Visa i tabellform. Om dina pivottabeller skapas i kompakt form ser du vyn nedan. (För att lära dig hur alla dina framtida pivottabeller startar i tabellform, se den här videon).
Vilken är fördelen med hierarkin? Jag försökte skapa en vanlig pivottabell utan en hierarki. Jag har fortfarande förmågan att expandera och dölja fält. Men om jag bara vill visa regionerna i Kanada måste jag lägga till en skivare eller ett rapportfilter.
Kolla på video
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2196: Drill Up and Drill Down in Pivot Tables.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Det finns ett mysterium i pivottabeller. Om jag infogar en pivottabell här ser du att vi har Drill Up och Drill Down-fält, men de tänds aldrig. Vad händer med det här? Varför har vi dessa? Hur får vi dem att fungera? Okej, det här är en fantastisk, bra fråga och tyvärr känner jag mig dålig om detta. Jag försöker göra hela mitt liv i Excel och använder aldrig fliken Power Pivot. Jag vill inte att du måste betala extra $ 2 per månad för Pro Plus-versionen av Office 365, men det här är en - det här är en - där vi måste spendera extra $ 2 per månad eller hitta någon som har extra $ 2 per månad för att ställa in detta.
Jag tar detta dataformat som en tabell. Det spelar ingen roll vilket format jag väljer, formatet är inte viktigt; bara att skaffa oss ett bord är den viktiga delen. Power Pivot, vi ska lägga till den här tabellen i vår datamodell och klicka sedan på Hantera. Okej, så här är vår tabell i datamodellen. Vi måste gå till Diagram View, nu ska vi göra det lite bredare så att vi kan se alla fälten. Jag ska välja kontinent; Jag ska Skift + klicka på City. Nu utgör det min Drill Down, Drill Up, hierarkin. Och sedan högerklickar vi och säger Create Heirarchy. Och de ger oss ett namn - Jag ska skriva "Geografi" för min hierarki, så här. Bra, nu, med den här ändringen, infogar vi en pivottabell - och detta kommer att vara en pivottabell för datamodell - och du ser att vi kan lägga till geografi som sin egen hierarki.
Nu, det enda jag inte gillar särskilt med det här är att allt annat flyttar till More Fields. OK? Så vi väljer geografi och det flyger till vänster. Och även om det är fantastiskt måste jag också välja Intäkter, och de tog fälten som inte var en del av hierarkin och flyttade dem till Fler fält. Så det är som att jag förstår att de försöker dölja de fält som jag inte ska välja, men i färd med att göra det gömde de också fler fält - intäkterna eller försäljningen här nere. OK. Så lite frustrerande måste vi gå till fler fält för att få de fält som inte ingår i geografin, men det är så det går.
OK. Så nu, nu när vi har det, låt oss ta en titt på vad som fungerar här. Jag sitter på kontinenten, jag går till fliken Analysera och ingenting tänds, det fungerade inte. Skjuta! Nej, det fungerade, du måste bara komma till Nordamerika och då kan jag borra ner och det ersätter kontinent med land. Och sedan från Kanada kan jag borra ner och hämta östra Kanada och västra Kanada. Från östra Kanada borra ner får jag Ontario och Quebec. Ontario, jag får de städerna, jag kan borra upp Drill Up, Drill Up och välja USA; Borra ner, borra ner, borra ner. Okej, så det är så det fungerar.
Prova, du måste ha fliken Power Pivot eller hitta någon med en Power Pivot-flik. Om du bara vill prova, titta i YouTube-beskrivningen, det kommer att finnas en länk till webbsidan och det finns en plats där på webbsidan där du kan ladda ner den här filen, och du borde kunna använda hierarkin även om du har inte fliken Power Pivot. Om du är i Excel 2016 eller Office 365 borde det fungera.
Nu vet du, jag antar att den sak som jag inte är säker på att jag är ett fan av är att de blir av med den andra informationen, i motsats till att använda Expand-ikonen, som sedan skulle expandera till nästa grupp och nästa grupp och nästa grupp. Vi har alltid haft Expand-ikonen, men även då fungerar det lite annorlunda. Här, om jag skulle, kan jag faktiskt sitta där i Nordamerika och utöka en nivå åt gången utan att behöva välja ytterligare en från datamodellen. Det ser ut som att vi måste flytta cellpekaren, en i taget.
Okej, nu upptäcktes detta tips egentligen bara, typ av. Excel MVP: erna hade en konversation med Excel-teamet om dessa knappar, så de täcks inte av den här boken. Men många andra bra tips som behandlas i LIVe, de 54 bästa tipsen genom tiderna.
Avslutning för idag: Varför är Drill Up and Drill Down ständigt nedtonad? Du måste skapa en hierarki. För att skapa en hierarki måste du gå in i Power Pivot; in i diagrammet; välj fälten för arvet; och högerklicka sedan; och skapa hierarki.
Jag vill tacka dig för att du stannade, vi ses nästa gång för en ny netcast från.
