Hur man hittar och ändrar källdata för en Excel-pivottabell. För saknade källdata, steg för att försöka återskapa det. Ändra alternativ för att spara källdata med pivottabellfilen.

OBS! För makron som hjälper till att hantera källdata går du till sidan för pivottabellens källdatamakron. För ett makro för att avaktivera källdata, gå till Unpivot Excel Data Macro-sidan

Leta upp och fixa källdata

I den här videon ser du hur du hittar pivottabellen datakälla och kontrollera sedan datakällan för att se till att den innehåller alla rader och kolumner som du behöver. Justera vid behov datakällan för att inkludera nya rader eller kolumner.

OBS: Under en lång tid termlösning, använd en namngiven Excel-tabell som datakälla. Den justeras automatiskt om nya rader läggs till.

De skriftliga instruktionerna finns under videon och du kan ladda ner exempelfilen för att följa med med videon.

Leta upp källdata

När en pivottabell har skapats kanske du vill lägga till nya poster i källdata eller ändra befintliga poster. stor arbetsbok kan det vara svårt att hitta den exakta källan för pivottabellen, om det finns flera tabeller eller listor.

OBS: Om du har köpt mitt Pivot Power Premium-tillägg, klicka på Pivottabell Info och klicka sedan på Gå till källdata.

Följ dessa steg för att hitta källdata för en pivottabell:

1. Välj vilken cell som helst i pivottabellen.
2. På menyfliksområdet, under fliken Pivottabellverktyg klickar du på fliken Analysera (i Excel 2010 klickar du på fliken Alternativ).
3. I datagruppen klickar du på den övre delen av kommandot Ändra datakälla.

Dialogrutan Ändra pivottabelldatakälla öppnas och du kan se källtabellen eller intervallet i rutan Tabell / intervall. Detta kan vara en referens till ett ark och cellintervall, till exempel

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

eller ett tabellnamn, till exempel

• Sales_East

Bakom dialogrutan kan du se källområdet i kalkylbladet, omgivet av en rörlig kant.

I skärmbilden nedan slutar källdataområdet vid rad 9 och en ny post har lagts till i rad 10. Den posten visas inte i pivottabellen, såvida inte datakällan justeras.

Justera källdataområdet

Om en pivottabells källdata är en statisk referens till ett visst ark och område, justeras det inte automatiskt när nya data läggs till. I skärmbilden ovan hänvisar datakällområdet till Order! $ A $ 1: $ H $ 9, och det kan justeras manuellt för att inkludera rad 10.

Så här justerar du det statiska källområdet manuellt:

Du kan justera källområdet vid behov så att det innehåller alla nya data, men en bättre lösning är att skapa en dynamisk källa och basera pivottabellen på det. Instruktionerna nedan.

Ändra källdata

Ibland kan du behöva ändra en pivottabell så att den använder en annan datakälla. I det här exemplet har en ny tabell skapats med endast data för East r egion. Pivottabellen kommer att ändras för att använda den källan, och sedan kan den skickas till en chef i östra regionen utan att avslöja West-regionens data.

OBS: Efter att ha bytt till en annan källtabell kan du behöva byta namn på vissa fält eller lägga till olika fält i pivottabellen.

För att ändra källdata för en Excel-pivottabell, följ dessa steg:

1. Välj vilken cell som helst i pivottabellen.
2. Klicka på fliken Analysera på menyfliksområdet under fliken Pivottabellverktyg (i Excel 2010, klicka på fliken Alternativ).
3. I Data klickar du på den övre delen av kommandot Ändra datakälla.
4. Dialogrutan Ändra pivottabelldatakälla öppnas och du kan se källtabellen eller intervallet i rutan Tabell / intervall. I det här skärmbilden nedan är den aktuella datakällan tabellen Sales_Data, som innehåller data från båda regionerna.

   

5. Ta bort befintlig datakälla och skriv ett nytt tabellnamn eller välj en ny datakälla räckvidd. Tabellen / intervallet ändras till Sales_East, där endast östdata visas.

   

6. Klicka på OK.

Dynamisk källa för pivottabell

I stället för att använda ett statiskt intervall som pivottabellens datakälla, är en bättre lösning att skapa ett dynamiskt intervall som automatiskt justeras i storlek.

Skapa dynamisk källa – Excel-tabell

Dynamisk källa – Namngivet intervall

Dynamisk källa – Dynamisk matris

Använd en dynamisk källa

Dynamisk källa – Excel-tabell

I stället för att använda ett statiskt intervall som pivottabellens datakälla är en bättre lösning att skapa ett dynamiskt intervall baserat på en namngiven Excel-tabell. Det finns skriftliga instruktioner här, eller titta på videon nedan.

Anteckna tabellnamnet så att du kan använda det som pivottabellkälla

Dynamisk källa – Namngivet intervall

Om du inte kan använda en Excel-tabell som källdata, skapar du ett dynamiskt namnområde, baserat på en INDEX- eller OFFSET-formel. Det finns skriftliga instruktioner här eller tittar på videon nedan.

Videon visar hur man skapar en dynamiskt omfång med OFFSET-funktionen, i Excel 2007, och stegen liknar i senare versioner.

OBS: Kom ihåg intervallnamnet så att du kan använda det som pivottabellkälla

Dynamisk källa – Dynamisk matris

Om du har en Excel-version som stöder de nya funktionerna, som SORT eller UNIK, kan du använda dessa funktioner för att skapa dynamiska matriser. Denna teknik använder endast formler – inga makron .

Skaffa exempelfilten för Filtrerad källdata i avsnittet Ladda ner. Den visar hur man ställer in ett namngivet intervall med endast de synliga raderna från en namngiven Excel-tabell.

Här är filtret d data, på ett annat ark, med endast de 2 reps och 3 kategorier från de synliga raderna.

Sedan, Du kan bara skapa en pivottabell baserat på den filtrerade informationen.

Använd dynamisk källa för pivottabell

När du har skapat en dynamisk källa som innehåller pivottabellen data, följ dessa steg för att använda källdata:

1. Välj vilken cell som helst i pivottabellen.
2. Klicka på Analysera på menyfliksområdet under fliken Pivottabellverktyg fliken (i Excel 2010, klicka på fliken Alternativ).
3. I datagruppen klickar du på det översta avsnittet i kommandot Ändra datakälla.
4. I dialogrutan Ändra pivottabelldatakälla rensar du rutan Tabell / intervall
5. I rutan Tabell / intervall skriver du namnet på tabellen eller namngivna intervall som du skapade. I det här exemplet är den dynamiska källan en namngiven tabell – tblOrders.
6. Klicka på OK

Spara källdata med pivottabell

När du skapar en pivottabell i din arbetsbok sparas källdataposterna i ett särskilt minnesområde – en pivotcache. När du stänger filen kan Excel sedan spara källdata i denna pivotcache eller rensa minnet.

Det finns fördelar och nackdelar med båda alternativen:

Om du sparar källdata:

• Filen blir större
• Filen kan öppnas snabbare

Om du inte sparar källdata:

• Fyllningen blir mindre
• Filen kan öppnas långsamt medan pivotcachen byggs om
• Pivottabellen måste uppdateras efter att filen har öppnats

Så här ändrar du inställningen

För att vrida spara inställningen för källdata på eller av:

• Högerklicka på en cell i pivottabellen och klicka på Alternativ för pivottabell.
• På fliken Data i avsnittet Pivottabelldata , lägg till eller ta bort bocken från Spara källdata med fil
• Klicka på OK.

Uppdatera data när du öppnar

Om du väljer att vända av alternativet Spara källdata med fil bör du aktivera Uppdatera data när du öppnar filalternativet. Den inställningen ligger strax under kryssrutan Spara källdata.

Annars ser du ett meddelande när du försöker filtrera data eller göra andra layoutändringar.

• ”Pivottabellrapporten sparades utan underliggande data. Använd kommandot Uppdatera data för att uppdatera rapporten. ”

Om meddelandet visas klickar du på OK och uppdaterar sedan pivottabellen manuellt.

Och för att undvika det irriterande meddelande, aktivera Uppdatera data när du öppnar filalternativet.

Återskapa källdata för pivottabell

Om du av misstag tar bort kalkylbladet som har källdata för din pivottabell, eller du fick en fil utan pivottabellens data, kanske du kan använda pivottabellens Visa detaljer-funktion för att återskapa den.

OBS: Detta fungerar inte för alla pivottabeller, men är värt att försöka, för att återställa källdata.

För att försöka återskapa källdata för en pivottabell, följ dessa steg för att använda funktionen Visa detaljer:

1. Se till att att inget av objekten i pivottabellfälten är dolda – rensa alla filter och skivor som har tillämpats.
2. Pivottabellen behöver inte innehålla alla fält – se bara till att det finns är minst ett fält i Val område.
3. Visa totalsummorna för rader och kolumner. Om totalen inte är synliga väljer du en cell i pivottabellen och på menyfliksområdet under Pivottabellverktyg klickar du på fliken Analysera. I gruppen Layout klickar du på Totalsummor och sedan på På för rader och kolumner.
4. Dubbelklicka på den totala totala cellen längst ner till höger i pivottabellen. Detta bör skapa ett nytt ark med relaterade poster från den ursprungliga källinformationen.

Fixa det extraherade Data

Om du återställer källdata kan du behöva göra några ytterligare ändringar för att återställa den till sitt ursprungliga skick.

• Byt namn på tabellen med originalkällans namn, om det är känt.
• Om den ursprungliga källinformationen innehöll formler måste du skapa dem på nytt, eftersom funktionen Visa detaljer exporterar endast data.
• Kolumnerna i de extraherade uppgifterna kommer att vara i samma ordning som de var i den ursprungliga källan.
• De extraherade uppgifterna kommer att vara formaterad med arbetsbokens standardtabellformat. Du kan använda ett annat tabellformat eller tillämpa din egen formatering.
• Obs! Om du hade gjort ändringar i källdata och inte uppdaterat pivottabellen finns de ändringarna inte i de extraherade data.

Anslut till extraherad data

Om du byter namn på tabellen som skapades under processen Visa detaljer och använder samma namn som tabellen som ursprungligen innehöll källan data, kan pivottabellen automatiskt ansluta till den nya källdata.

Om inte, kan du ansluta till den återskapade källdata – följ stegen i avsnittet ovan: Använd dynamisk källa för pivottabell – använd tabellnamnet som du gav till den nya tabellen.

Ladda ner provfilen

Pivotkälla Exempel: För att följa med självstudierna kan du ladda ner DateAmt.zip-filen. Den zippade filen är i xlsx-format och innehåller inte makron.

Filtrerad källdata Exempel: Detta exempel är för Excel-versioner som har de nya funktionerna, som SORT och UNIK. Dow ladda ner denna exempelfil för att se att en pivottabell skapas från endast de synliga raderna i en filtrerad Excel-tabell. Den zippade filen är i xlsx-format och innehåller inte makron.

Pivottabellverktyg

För att spara tid när du bygger, formaterar och ändrar dina pivottabeller använder du verktygen i min Pivot Power Premium-tillägg. Med bara några få klick kan du:

• kopiera formateringen från en pivottabell och tillämpa den på en annan pivottabell.
• ändra alla värden från Count till Sum
• ta bort ”Summan av” från alla rubriker

och mycket mer!

Missa inte våra Excel-tips