Nylig lurte en venn av meg på knappene Drill-Down og Drill-Up i Pivot Table Tools-fanen på båndet. Hvorfor blir disse stadig gråtonet? De tar mye plass i båndet. Hvordan skal noen bruke dem?
Etter litt undersøkelser er det en måte å bruke dem på, men du må bruke datamodellen og bruke Power Pivot-diagramvisningen for å lage et hierarki. Hvis du ikke har Power Pivot-fanen i båndet, må du finne en medarbeider som har knappen for å opprette hierarkiet. (Eller hvis du bare vil prøve funksjonen, kan du laste ned Excel-filen jeg opprettet: Hierarchy.xlsx)
Første trinn - konverter pivotkildedatasettet til en tabell ved hjelp av enten Hjem - Format som tabell eller Ctrl + T. Sørg for at alternativet for Mitt bord har overskrifter er valgt.
Bruk Insert - Pivot Table. I dialogboksen Opprett pivottabell velger du boksen for Legg til disse dataene i datamodellen.
Her er pivottabellfeltene før du oppretter hierarkiet.
Klikk på Manage-ikonet i Power Pivot-fanen i båndet. (Mange tilfeller av Excel 2013 og 2016 har ikke denne kategorien. Den vises ikke på Mac.)
I Power Pivot for Excel-vinduet klikker du på Diagram View-ikonet. Det er nær høyre side av Hjem-fanen.
Bruk størrelse på håndtaket i nedre høyre hjørne av Table1 for å forstørre Table1 slik at du kan se alle feltene dine. Klikk på det første elementet i hierarkiet ditt (Kontinent i mitt eksempel). Skift-Klikk på det siste elementet i hierarkiet (By i mitt eksempel). Du kan også klikke på ett element, og Ctrl-klikke på andre hvis hierarkifeltene ikke ligger ved siden av. Når du har valgt feltene, høyreklikker du på et av feltene og velger Opprett hierarki.
Hierarki1 er opprettet og venter på at du skal skrive inn et nytt navn. Jeg vil gi navnet mitt hierarki Geografi. Hvis du klikker bort fra Power Pivot, er Hierarchy1 ikke lenger i Endre navn-modus. Høyreklikk Hierachy1 og velg Gi nytt navn.
Lukk Power Pivot og gå tilbake til Excel. Pivottabellfeltene viser nå Geografihierarkiet og Flere felt. Salgsfeltet ditt er skjult under Flere felt. Jeg forstår noe hvorfor de gjemmer kontinent, land, region, territorium, by under flere felt. Men jeg forstår ikke hvorfor de gjemmer Salg under Flere felt.
Hvis du vil bygge pivottabellen, merker du av for Geografihierarkiet. Åpne flere felt ved å klikke trekanten ved siden av den. Velg Salg.
Det er mye å merke seg i bildet over. Når du oppretter pivottabellen, er den aktive cellen på A3 og Drill Down-ikonet er gråtonet. Imidlertid, hvis du flytter cellepekeren til Nord-Amerika i A4, vil du se at Drill Down er aktivert.
Med cellepekeren i Nord-Amerika klikker du på Bore ned og kontinentet erstattes av land.
Med cellepekeren på Canada klikker du Drill Down og du ser Øst-Canada og Vest-Canada. Merk på dette punktet at både Drill Down og Drill Up-knappene er aktivert.
Jeg klikket Drill Up for å gå tilbake til Country. Velg USA. Bor ned tre ganger, og jeg havner i byene i Carolinas-regionen. På dette punktet er Drill Down-knappen gråtonet.
Merk at fra kontinentnivå kan du klikke på Utvid felt for å vise kontinenter og land. Velg deretter Utvid felt fra det første landet for å avsløre regioner. Fra den første regionen, bruk Utvid felt for å vise territorier. Fra det første territoriet klikker du på Utvid felt for å avsløre byen.
Alle skjermbildene ovenfor viser pivottabellen i min standardvisning av Vis i tabellform. Hvis pivottabellene dine er opprettet i kompakt skjema, vil du se utsikten nedenfor. (For å lære hvordan alle fremtidige pivottabeller skal starte i tabellform, se denne videoen).
Hva er fordelen med hierarkiet? Jeg prøvde å lage en vanlig pivottabell uten hierarki. Jeg har fortsatt muligheten til å utvide og skjule felt. Men hvis jeg bare vil vise regionene i Canada, må jeg legge til en skiver eller Rapportfilter.
Se på video
Videoutskrift
Lær Excel fra Podcast, episode 2196: Drill Up and Drill Down in Pivot Tables.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Det er et mysterium i pivottabeller. Hvis jeg setter inn en pivottabell her, ser du at vi har Drill Up og Drill Down-felt, men de lyser aldri. Hva skjer med dette? Hvorfor har vi disse? Hvordan får vi dem til å fungere? OK, dette er et flott, flott spørsmål, og dessverre føler jeg meg dårlig om dette. Jeg prøver å gjøre hele mitt liv i Excel, ikke med Power Pivot-fanen. Jeg vil ikke at du må betale ekstra $ 2 i måneden for Pro Plus-versjonen av Office 365, men dette er en - dette er en - der vi må bruke ekstra $ 2 i måneden eller finne noen som har ekstra $ 2 i måneden for å sette opp dette.
Jeg tar dette dataformatet som en tabell. Det spiller ingen rolle hvilket format jeg velger, formatet er ikke viktig; bare å skaffe oss et bord er den viktige delen. Power Pivot, vi skal legge til denne tabellen i datamodellen vår, og klikk deretter Administrer. OK, så her er vårt bord i datamodellen. Vi må gå til Diagram View, nå skal vi gjøre dette litt bredere slik at vi kan se alle feltene. Jeg skal velge Kontinent; Jeg skal Shift + klikke på City. Nå utgjør dette Drill Down, Drill Up, hierarkiet. Og så høyreklikker vi og sier Create Heirarchy. Og de gir oss et navn - Jeg skal skrive "Geografi" for Hierarkiet mitt, slik. Flott, nå, med den ene endringen, setter vi inn en pivottabell - og dette vil være en datamodell pivottabell - og du ser at vi kan legge til geografi som sitt eget hierarki.
Nå, den ene tingen jeg ikke liker spesielt godt med dette, er at alt annet beveger seg til More Fields. Ok? Så vi velger Geografi, og den flyr til venstre. Og selv om det er flott, må jeg også velge Inntekt, og de tok feltene som ikke var en del av hierarkiet og flyttet dem til Flere felt. Så det er som, jeg skjønner, de prøver å skjule feltene jeg ikke skal velge, men i ferd med å gjøre det skjulte de også Flere felt - inntektene eller salget her nede. Ok. Så litt frustrerende må vi gå til flere felt for å få feltene som ikke er en del av geografien, men det er slik det går.
Ok. Så nå, nå som vi har det, la oss ta en titt på hva som fungerer her. Jeg sitter på kontinentet, jeg går til Analyze-fanen og ingenting lyser, det fungerte ikke. Skyte! Nei, det fungerte, du må bare komme til Nord-Amerika, og så kan jeg bore ned, og det erstatter kontinentet med land. Og så fra Canada kan jeg bore ned og hente Øst-Canada og Vest-Canada. Fra det borte Øst-Canada får jeg Ontario og Quebec. Ontario, jeg får de byene, jeg kan bore opp Drill Up, Drill Up, og velge USA; Drill Down, Drill Down, Drill Down. Ok, så det fungerer slik.
Prøv det, du må ha Power Pivot-fanen eller finne noen med Power Pivot-fanen. Hvis du bare vil prøve det, se i YouTube-beskrivelsen, det vil være en lenke til websiden, og det er et sted der på websiden der du kan laste ned denne filen, og du bør kunne bruke hierarkiet selv om du ikke har Power Pivot-fanen. Hvis du er i Excel 2016 eller Office 365, skal det fungere.
Nå, du vet, skjønner, jeg antar at det jeg ikke er sikker på at jeg er fan av, er det faktum at de blir kvitt den andre informasjonen, i motsetning til å bruke Utvid-ikonet, som deretter utvides til neste gruppe, og neste gruppe, og neste gruppe. Vi har alltid hatt Utvid-ikonet, men selv da fungerer det litt annerledes. Her, hvis jeg ville, kan jeg faktisk sitte der i Nord-Amerika og utvide ett nivå om gangen uten å måtte velge hver ekstra fra datamodellen. Det ser ut til at vi må flytte cellepekeren over, en og en om gangen.
Ok, nå ble dette tipset egentlig bare oppdaget. Excel MVP-ene hadde en samtale med Excel-teamet om disse knappene, så ikke dekket i denne boken. Men mange andre gode tips dekket i LIVe, de 54 største tipsene gjennom tidene.
Avslutning for i dag: Hvorfor blir Drill Up and Drill Down stadig nedtonet? Du må lage et hierarki. For å skape et hierarki, må du gå inn i Power Pivot; inn i Diagramvisningen; velg feltene for arvingen; og høyreklikk deretter; og skape hierarki.
Jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra.
