Hvordan finne og endre kildedataene for en Excel-pivottabell. For manglende kildedata, trinn for å prøve å gjenskape det. Endre alternativer for å lagre kildedata med pivottabellfilen.

MERKNAD: For makroer som hjelper med å administrere kildedataene, gå til siden for pivottabellens kildedatamakroer. For en makro for å fjerne kildedata, gå til Unpivot Excel Data Macro-siden

Finn og fikse kildedata

I denne videoen vil du se hvordan du finner pivottabellen datakilde, og kontroller deretter datakilden for å sikre at den inneholder alle radene og kolonnene du trenger. Juster om nødvendig datakilden for å inkludere nye rader eller kolonner.

MERK: I lang tid termløsning, bruk en navngitt Excel-tabell som datakilde. Den vil justeres automatisk hvis nye rader legges til.

De skriftlige instruksjonene er under videoen, og du kan laste ned eksempelfilen for å følge med med videoen.

Finn kildedataene

Etter at en pivottabell er opprettet, vil du kanskje legge til nye poster i kildedataene, eller endre de eksisterende postene. stor arbeidsbok, kan det være vanskelig å finne den eksakte kilden for pivottabellen, hvis det er flere tabeller eller lister.

MERK: Hvis du har kjøpt Pivot Power Premium-tillegget mitt, klikker du på Pivot Table Info, og klikk deretter Gå til kildedata.

Følg disse trinnene for å finne kildedataene til en pivottabell:

1. Velg hvilken som helst celle i pivottabellen.
2. På båndet, under kategorien Pivottabellverktøy, klikker du kategorien Analyser (i Excel 2010 klikker du kategorien Alternativer).
3. I datagruppen klikker du på den øverste delen av kommandoen Endre datakilde.

Dialogboksen Endre pivottabelldatakilde åpnes, og du kan se kildetabellen eller området i tabellen / rekkevidde-boksen. Dette kan være en referanse til et ark og rekke celler, for eksempel

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

eller et tabellnavn, slik som

• Sales_East

Bak dialogboksen kan du se kildene på regnearket, omgitt av en bevegelig kant.

I skjermbildet nedenfor ender kildedataområdet på rad 9, og en ny post er lagt til i rad 10. Denne posten vises ikke i pivottabellen, med mindre datakilden er justert.

Juster kildedataområdet

Hvis kildedataene til en pivottabell er en statisk referanse til et bestemt ark og område, justeres det ikke automatisk når nye data legges til. I skjermbildet ovenfor refererer datakildeområdet til ordrer! $ A $ 1: $ H $ 9, og det kan justeres manuelt for å inkludere rad 10.

For å justere det statiske kildeområdet manuelt:

Du kan justere kildeområdet når det er nødvendig, slik at det inkluderer nye data, men en bedre løsning er å opprette en dynamisk kilde , og baser pivottabellen på det. Instruksjonene er nedenfor.

Endre kildedataene

Noen ganger må du kanskje endre en pivottabell, så den bruker en annen datakilde. I dette eksemplet er det opprettet en ny tabell med bare data for øst r egion. Pivottabellen vil bli endret for å bruke den kilden, og deretter kan den sendes til en leder i Øst-regionen uten å avsløre dataene for den vestlige regionen.

MERKNAD: Etter å ha byttet til en annen kildetabell. , må du kanskje gi nytt navn til noen felt, eller legge til forskjellige felt i pivottabellen.

For å endre kildedataene for en Excel-pivottabell, følg disse trinnene:

1. Velg en hvilken som helst celle i pivottabellen.
2. På båndet, under kategorien Pivottabellverktøy, klikker du kategorien Analyser (i Excel 2010 klikker du på Alternativer-fanen).
3. I Data klikker du på den øverste delen av kommandoen Endre datakilde.
4. Endre pivottabell datakilde dialogboksen åpnes, og du kan se kildetabellen eller området i tabellen / området. I dette skjermbildet nedenfor er den nåværende datakilden Sales_Data-tabellen, som inneholder data fra begge regionene.

   

5. Slett den eksisterende datakilden , og skriv inn et nytt tabellnavn, eller velg en ny datakilde rekkevidde. Tabellen / området endres til Sales_East, der bare østdataene er oppført.

   

6. Klikk OK.

Dynamisk kilde for pivottabell

I stedet for å bruke et statisk område som pivottabellens datakilde, er en bedre løsning å opprette et dynamisk område som automatisk justeres i størrelse.

Opprett dynamisk kilde – Excel-tabell

Dynamisk kilde – Navngitt område

Dynamisk kilde – Dynamisk matrise

Bruk en dynamisk kilde

Dynamisk kilde – Excel-tabell

I stedet for å bruke et statisk område som pivottabellens datakilde, er en bedre løsning å lage et dynamisk område, basert på en navngitt Excel-tabell. Det er skriftlige instruksjoner her, eller se videoen nedenfor.

Legg merke til tabellnavnet, slik at du kan bruke det som pivottabellkilde

Dynamisk kilde – Navngitt område

Hvis du ikke kan bruke en Excel-tabell som kildedata, lager du et dynamisk navngitt område, basert på en INDEX- eller OFFSET-formel. Det er skriftlige instruksjoner her, eller se videoen nedenfor.

Videoen viser hvordan du lager en dynamisk område med OFFSET-funksjonen i Excel 2007, og trinnene er like i senere versjoner.

MERK: Husk områdets navn, slik at du kan bruke det som kildetabellkilde

Dynamic Source – Dynamic Array

Hvis du har en Excel-versjon som støtter de nye funksjonene, for eksempel SORT, eller UNIQUE, kan du bruke disse funksjonene til å lage dynamiske arrays. Denne teknikken bruker bare formler – ingen makroer .

I nedlastingsseksjonen får du Filtered Source Data-eksempelfilen. Den viser hvordan du setter opp et navngitt område med bare de synlige radene fra en navngitt Excel-tabell.

Her er filteret d data, på et annet ark, med bare to reps og 3 kategorier fra de synlige radene.

Deretter, du kan opprette en pivottabell bare basert på de filtrerte dataene.

Bruk dynamisk kilde til pivottabell

Etter at du har opprettet en dynamisk kilde som inneholder pivottabellen data, følg disse trinnene for å bruke kildedataene:

1. Velg hvilken som helst celle i pivottabellen.
2. På båndet under kategorien Pivottabellverktøy klikker du på Analyser -fanen (i Excel 2010, klikk på Alternativer-fanen).
3. I datagruppen klikker du på den øverste delen av kommandoen Endre datakilde.
4. I dialogboksen Endre pivottabelldatakilde fjerner du tabellen / rekkevidden
5. I tabellen / området skriver du inn navnet på tabellen eller det navngitte området du opprettet. I dette eksemplet er den dynamiske kilden en navngitt tabell – tblOrders.
6. Klikk OK

Lagre kildedata med pivottabell

Når du oppretter en pivottabell i arbeidsboken din, blir kildedatapostene lagret i et spesielt minneområde – en pivotbuffer. Når du lukker filen, kan Excel lagre kildedataene i denne svingbufferen, eller tømme minnet.

Det er fordeler og ulemper ved begge alternativene:

Hvis du lagrer kildedataene:

• Filen blir større
• Filen kan åpne raskere

Hvis du ikke lagrer kildedataene:

• Fyllingen blir mindre
• Filen kan åpne sakte mens pivotbufferen gjenoppbygges
• Pivottabellen må oppdateres etter at filen er åpnet

Slik endrer du innstillingen

For å snu Lagre kildedatainnstillingen på eller av:

• Høyreklikk på en celle i pivottabellen, og klikk deretter Alternativer for pivottabell.
• I kategorien Data i delen Pivottabelldata , legg til eller fjern haken fra Lagre kildedata med fil
• Klikk OK.

Oppdater data når du åpner

Hvis du velger å slå av alternativet Lagre kildedata med fil, bør du slå på Oppdater data når du åpner filalternativet. Denne innstillingen er rett under avmerkingsboksen Lagre kildedata.

Ellers vil du se en melding når du prøver å filtrere dataene, eller foreta andre layoutendringer.

• «Pivottabellrapporten ble lagret uten de underliggende dataene. Bruk kommandoen Oppdater data for å oppdatere rapporten. «

Hvis du ser denne meldingen, klikker du OK og deretter oppdaterer du pivottabellen manuelt.

Og for å unngå den irriterende meldingen, slå på Oppdater data når du åpner filalternativet.

Gjenopprett pivottabellkildedata

Hvis du ved et uhell sletter regnearket som har kildedataene for pivottabellen, eller hvis du mottok en fil uten pivottabellens data, kan du kanskje bruke pivottabellens Vis detaljer-funksjon for å gjenskape den.

MERK: Dette fungerer ikke for alle pivottabeller, men er verdt å prøve, for å gjenopprette kildedataene.

For å prøve å gjenskape kildedataene for en pivottabell, følg disse trinnene for å bruke funksjonen Vis detaljer:

1. Forsikre deg om at ingen av elementene i pivottabellfeltene er skjult – fjern alle filtrene og skivene som er brukt.
2. Pivottabellen trenger ikke å inneholde alle feltene – bare sørg for at det er minst ett felt i Val ues-området.
3. Vis totalsummene for rader og kolonner. Hvis totalene ikke er synlige, velger du en celle i pivottabellen, og på båndet, under Pivottabellverktøy, klikker du på Analyser-fanen. I Layout-gruppen klikker du på Totalsummer, og deretter klikker du på for rader og kolonner.
4. Dobbeltklikk på den totale cellen nederst til høyre i pivottabellen. Dette skal opprette et nytt ark med relaterte poster fra de opprinnelige kildedataene.

Fikse det ekstraherte Data

Hvis du gjenoppretter kildedataene, kan det hende du må gjøre noen ekstra endringer for å gjenopprette den til sin opprinnelige tilstand.

• Endre navn på tabellen ved å bruke navnet på den opprinnelige kildedata, hvis kjent.
• Hvis de opprinnelige kildedataene inneholdt formler, må du opprette dem på nytt, fordi funksjonen Vis detaljer eksporterer bare dataene.
• Kolonnene i de ekstraherte dataene vil være i samme rekkefølge som de var i de opprinnelige kildedataene.
• De ekstraherte dataene vil være formatert med arbeidsbokens standard tabellformat. Du kan bruke et annet tabellformat, eller bruke din egen formatering.
• Merk: Hvis du hadde gjort endringer i kildedataene og ikke oppdatert pivottabellen, vil ikke disse endringene være i de ekstraherte dataene.

Koble til de ekstraherte dataene

Hvis du gir nytt navn til tabellen som ble opprettet under prosessen Vis detaljer, og bruker samme navn som tabellen som opprinnelig inneholdt kilden data, kan pivottabellen automatisk koble til de nye kildedataene.

Hvis ikke, kan du koble til de gjenopprettede kildedataene – følg trinnene i avsnittet ovenfor: Bruk dynamisk kilde til pivottabell – bruk tabellnavnet du ga til den nye tabellen.

Last ned prøvefilen

Pivotkilde Eksempel: For å følge opp veiledningene kan du laste ned DateAmt.zip-filen. Den zippede filen er i xlsx-format og inneholder ikke makroer.

Filtrerte kildedata Eksempel: Dette eksemplet er for Excel-versjoner som har de nye funksjonene, for eksempel SORT og UNIK. Dow Last ned denne eksempelfilen for å se at en pivottabell bare er opprettet fra de synlige radene, i en filtreert Excel-tabell. Den zippede filen er i xlsx-format og inneholder ikke makroer.

Pivottabellverktøy

For å spare tid når du bygger, formaterer og endrer pivottabellene, bruker du verktøyene i Pivot. Power Premium-tillegg. Med bare noen få klikk kan du:

• kopiere formateringen fra en pivottabell, og bruke den på en annen pivottabell.
• endre alle verdiene fra Count til Sum
• fjern «Sum of» fra alle overskriftene

og mye mer!

Ikke gå glipp av våre Excel-tips