Sådan finder du og ændrer kildedataene til en Excel-pivottabel. For manglende kildedata skal du prøve at genskabe dem. Skift indstillinger for at gemme kildedata med pivottabelfil.

BEMÆRK: For makroer, der hjælper med at administrere kildedataene, skal du gå til siden for pivottabelkildedatamakroer. For at en makro kan frakoble kildedataene, skal du gå til Unpivot Excel-datamakro-siden

Find og rette kildedata

I denne video vil du se, hvordan du finder pivottabellen datakilde, og kontroller derefter datakilden for at sikre, at den inkluderer alle de rækker og kolonner, du har brug for. Juster om nødvendigt datakilden for at medtage nye rækker eller kolonner.

BEMÆRK: I lang tid term løsning, brug en navngivet Excel-tabel som en datakilde. Den justeres automatisk, hvis nye rækker tilføjes.

De skriftlige instruktioner er under videoen, og du kan downloade prøvefilen for at følge med med videoen.

Find kildedataene

Når en pivottabel er oprettet, vil du muligvis tilføje nye poster i kildedataene eller ændre de eksisterende poster. stor projektmappe, kan det være svært at finde den nøjagtige kilde til pivottabellen, hvis der er flere tabeller eller lister.

BEMÆRK: Hvis du har købt min Pivot Power Premium-tilføjelse, skal du klikke på Pivot Table Info, og klik derefter på Gå til kildedata.

Følg disse trin for at finde kildedataene til en pivottabel:

1. Vælg en hvilken som helst celle i pivottabellen.
2. På båndet, under fanen Pivottabelværktøjer skal du klikke på fanen Analyser (i Excel 2010 skal du klikke på fanen Indstillinger).
3. I datagruppen skal du klikke på det øverste afsnit af kommandoen Skift datakilde.

Dialogboksen Skift pivottabel datakilde åbnes, og du kan se kildetabellen eller området i boksen Tabel / rækkevidde. Dette kan være en henvisning til et ark og række celler, såsom

• Ordrer! $ A $ 1: $ H $ 9

eller et tabelnavn, f.eks.

• Sales_East

Bag dialogboksen kan du se kildeområdet på regnearket omgivet af en bevægende kant.

I nedenstående skærmbillede slutter kildedataområdet i række 9, og der er tilføjet en ny post i række 10. Denne post vises ikke i pivottabellen, medmindre datakilden er justeret.

Juster kildedataområdet

Hvis kildedataene til en pivottabel er en statisk reference til et bestemt ark og område, justeres det ikke automatisk, når nye data tilføjes. I ovenstående skærmbillede henviser datakildeområdet til ordrer! $ A $ 1: $ H $ 9, og det kan justeres manuelt for at inkludere række 10.

Manuel justering af det statiske kildeområde:

Du kan justere kildeområdet, når det er nødvendigt, så det inkluderer nye data, men en bedre løsning er at oprette en dynamisk kilde , og baser pivottabellen på det. Instruktionerne er nedenfor.

Skift kildedata

Lejlighedsvis skal du muligvis ændre en pivottabel, så den bruger en anden datakilde. I dette eksempel er der oprettet en ny tabel med kun data for East r egion. Pivottabellen ændres for at bruge den kilde, og derefter kan den sendes til en manager i det østlige område uden at afsløre data for den vestlige region.

BEMÆRK: Efter skift til en anden kildetabel , skal du muligvis omdøbe nogle felter eller tilføje forskellige felter til pivottabellen.

Hvis du vil ændre kildedataene til en Excel-pivottabel, skal du følge disse trin:

1. Vælg en hvilken som helst celle i pivottabellen.
2. På båndet under fanen Pivottabelværktøjer skal du klikke på fanen Analyser (i Excel 2010 skal du klikke på fanen Indstillinger).
3. I Data gruppe, klik på det øverste afsnit af kommandoen Skift datakilde.
4. Dialogboksen Skift pivottabel datakilde åbnes, og du kan se kildetabellen eller området i feltet Tabel / rækkevidde. I dette skærmbillede nedenfor er den aktuelle datakilde tabellen Sales_Data, der indeholder data fra begge regioner.

   

5. Slet den eksisterende datakilde , og skriv et nyt tabelnavn, eller vælg en ny datakilde rækkevidde. Tabel / rækkevidde ændres til Sales_East, hvor kun de østlige data er angivet.

   

6. Klik på OK.

Dynamisk kilde til pivottabel

I stedet for at bruge et statisk interval som pivottabellens datakilde, er en bedre løsning at oprette et dynamisk område, der automatisk justeres i størrelse.

Opret dynamisk kilde – Excel-tabel

Dynamisk kilde – Navngivet område

Dynamisk kilde – Dynamisk matrix

Brug en dynamisk kilde

Dynamisk kilde – Excel-tabel

I stedet for at bruge et statisk område som pivottabellens datakilde, er en bedre løsning at oprette et dynamisk interval baseret på en navngivet Excel-tabel. Der er skriftlige instruktioner her, eller se videoen nedenfor.

Noter tabelnavnet, så du kan bruge det som pivottabelkilde

Dynamisk kilde – Navngivet rækkevidde

Hvis du ikke kan bruge en Excel-tabel som kildedata, opretter du et dynamisk navngivet interval, baseret på en INDEX- eller OFFSET-formel. Der er skriftlige instruktioner her, eller se videoen nedenfor.

Videoen viser, hvordan du opretter en dynamisk område med OFFSET-funktionen i Excel 2007, og trinnene er ens i senere versioner.

BEMÆRK: Husk områdets navn, så du kan bruge det som pivottabelkilde

Dynamisk kilde – Dynamisk matrix

Hvis du har en Excel-version, der understøtter de nye funktioner, såsom SORT eller UNIQUE, kan du bruge disse funktioner til at oprette dynamiske arrays. Denne teknik bruger kun formler – ingen makroer .

I afsnittet Download skal du hente den eksemplerede filfiltrerede kildedata. Den viser, hvordan man opsætter et navngivet område med kun de synlige rækker fra en navngivet Excel-tabel.

Her er filteret d data på et andet ark med kun de 2 reps og 3 kategorier fra de synlige rækker.

Derefter Du kan kun oprette en pivottabel baseret på de filtrerede data.

Brug dynamisk kilde til pivottabel

Når du opretter en dynamisk kilde, der indeholder pivottabellen data, følg disse trin for at bruge disse kildedata:

1. Vælg en hvilken som helst celle i pivottabellen.
2. På båndet under fanen Pivottabelværktøjer skal du klikke på Analyser fanen (i Excel 2010 skal du klikke på fanen Indstillinger).
3. I datagruppen skal du klikke på det øverste afsnit af kommandoen Skift datakilde.
4. I dialogboksen Skift pivottabel-datakilde skal du fjerne boksen Tabel / rækkevidde
5. I feltet Tabel / rækkevidde skal du skrive navnet på den tabel eller det navngivne område, du oprettede. I dette eksempel er den dynamiske kilde en navngivet tabel – tblOrders.
6. Klik på OK

Gem kildedata med pivottabel

Når du opretter en pivottabel i din projektmappe, gemmes kildedataoptegnelserne i et specielt hukommelsesområde – en pivotcache. Derefter, når du lukker filen, kan Excel gemme kildedataene i denne drejecache eller rydde den hukommelse.

Der er fordele og ulemper ved begge muligheder:

Hvis du gemmer kildedataene:

• Filen bliver større
• Fil åbner muligvis hurtigere

Hvis du ikke gemmer kildedataene:

• Udfyldningen bliver mindre
• Filen kan åbnes langsomt, mens pivotcachen genopbygges
• Pivottabel skal opdateres efter åbning af filen

Sådan ændres indstillingen

For at dreje Gem kildedataindstillingen til eller fra:

• Højreklik på en celle i pivottabellen, og klik på Pivottabelindstillinger.
• På fanen Data i afsnittet Pivottabeldata , tilføj eller fjern markeringen fra Gem kildedata med fil
• Klik på OK.

Opdater data, når du åbner

Hvis du vælger at dreje fra indstillingen Gem kildedata med fil, skal du slå Opdater data til, når du åbner filindstillingen. Denne indstilling er lige under afkrydsningsfeltet Gem kildedata.

Ellers vil du se en besked, når du prøver at filtrere dataene eller foretage andre layoutændringer.

• “Pivottabellen blev gemt uden de underliggende data. Brug kommandoen Opdater data til at opdatere rapporten. “

Hvis du ser denne meddelelse, skal du klikke på OK og derefter opdatere pivottabellen manuelt.

Og for at undgå den irriterende besked, skal du aktivere Opdater data, når du åbner filindstillingen.

Gendan pivottabelkildedata

Hvis du ved et uheld sletter regnearket, der indeholder kildedataene til din pivottabel, eller hvis du modtog en fil uden pivottabellens data, kan du muligvis bruge pivottabellen Vis detaljer-funktion til at genskabe den.

BEMÆRK: Dette fungerer ikke for alle pivottabeller, men er værd at prøve, for at gendanne kildedataene.

Hvis du vil prøve at genskabe kildedataene til en pivottabel, skal du følge disse trin for at bruge funktionen Vis detaljer:

1. Sørg for at ingen af elementerne i pivottabelfelterne er skjult – ryd alle filtre og udskæringer, der er anvendt.
2. Pivottabellen behøver ikke at indeholde alle felterne – bare sørg for at der er mindst et felt i Val ues-område.
3. Vis de samlede totaler for rækker og kolonner. Hvis totalerne ikke er synlige, skal du vælge en celle i pivottabellen, og på båndet under pivottabelværktøjer skal du klikke på fanen Analyser. I gruppen Layout skal du klikke på Stortotaler og derefter klikke på Til for rækker og kolonner.
4. Dobbeltklik på den samlede totalcelle nederst til højre i pivottabellen. Dette skal oprette et nyt ark med de relaterede poster fra de originale kildedata.

Fix det udpakkede Data

Hvis du gendanner kildedataene, skal du muligvis foretage nogle yderligere ændringer for at gendanne dem til deres oprindelige tilstand.

• Omdøb tabellen ved hjælp af navnet på de originale kildedata, hvis den er kendt.
• Hvis de originale kildedata indeholdt formler, skal du gendanne dem igen, fordi funktionen Vis detaljer eksporterer kun dataene.
• Kolonnerne i de udpakkede data vil være i samme rækkefølge som de var i de originale kildedata.
• De udpakkede data vil være formateret med projektmappens standardtabelformat. Du kan anvende et andet tabelformat eller anvende din egen formatering.
• Bemærk: Hvis du havde foretaget ændringer i kildedataene og ikke opdateret pivottabellen, vil disse ændringer ikke være i de udpakkede data.

Opret forbindelse til de udpakkede data

Hvis du omdøber den tabel, der blev oprettet under Vis detaljer-processen, og brug det samme navn som den tabel, der oprindeligt indeholdt kilden data, kan pivottabellen muligvis automatisk oprette forbindelse til de nye kildedata.

Hvis ikke, kan du oprette forbindelse til de genskabte kildedata – følg trinnene i afsnittet ovenfor: Brug dynamisk kilde til pivottabel – brug det tabelnavn, du gav til den nye tabel.

Download prøvefilen

Pivotkilde Eksempel: For at følge vejledningen sammen kan du downloade DateAmt.zip-filen. Den zip-fil er i xlsx-format og indeholder ikke makroer.

Filtrerede kildedata Eksempel: Dette eksempel er til Excel-versioner, der har de nye funktioner, såsom SORT og UNIQUE. Dow nload denne prøvefil for at se, at en pivottabel oprettes kun fra de synlige rækker i en filtreet Excel-tabel. Den zip-fil er i xlsx-format og indeholder ikke makroer.

Pivottabelværktøjer

For at spare tid, når du bygger, formaterer og ændrer dine pivottabeller, skal du bruge værktøjerne i min Pivot Power Premium-tilføjelsesprogram. Med blot et par klik kan du:

• kopiere formateringen fra en pivottabel og anvende den på en anden pivottabel.
• skift alle værdier fra Count til Sum
• fjern “Sum of” fra alle overskrifter

og meget mere!

Gå ikke glip af vores Excel-tip