GetPivotData i Excel - Excel-tips

Du har precis byggt en pivottabell i Excel. Du klickar utanför pivottabellen. Du bygger en Excel-formel. Du kopierar den här formeln till alla raderna i pivottabellen. Beräkningen rapporterar fel svar för alla utom den första raden i pivottabellen. Du har precis stungits av funktionen "Generera GetPivotData". Låt oss ta en titt på vad detta är och hur man kan förhindra det.

Förhindra bara GetPivotData-problemet - den snabba metoden

Det snabbaste sättet att förhindra att Excel genererar GetPivotData är att skriva din formel utan att använda musen eller några piltangenter. Om du bara skriver =E5/D5så skapar Excel en "normal" relativ formel som kan kopieras ner till alla rader intill pivottabellen.

Förhindra GetPivotData-problemet - den permanenta metoden

Det finns en dold inställning som förhindrar att Excel genererar GetPivotData. Det är mer dolt i Excel 2003 än i Excel 2007.

Följ dessa steg i Excel 2007:

• Skapa en pivottabell i Excel 2007
• Se till att den aktiva cellen finns i pivottabellen
• Titta till vänster på fliken Alternativ för pivottabellverktyg i menyfliksområdet. Det finns en alternativknapp. Klicka inte på alternativknappen! Till höger om alternativknappen finns en nedrullningspil. Klicka på rullgardinspilen. Avmarkera Skapa GetPivotData.
• Du kan nu ange formler med musen, piltangenterna eller genom att skriva.

Följ dessa steg i Excel 2003 och tidigare:

• Välj Verktyg, Anpassa
• Det finns tre flikar i dialogrutan Anpassa. Välj fliken Kommandon i mitten.
• I det vänstra listrutan väljer du det sjunde objektet, Data
• I den högra listrutan, bläddra nästan hela vägen till botten. Den åttonde ikonen från slutet av listan är Generate GetPivotData. Dra den här ikonen från listrutan och släpp den mitt i ett befintligt verktygsfält.
• Klicka på knappen Stäng för att stänga dialogrutan.
• Klicka på ikonen du just lagt till i Excel-verktygsfältet. Detta stänger av funktionen. Du kan nu ange formler med musen utan att generera GetPivotData-funktioner.

Här är ett bonustips: Jag brukade förakta den här funktionen och ville helt enkelt stänga av den hela tiden. Jag lade till ikonen i mitt verktygsfält, stängde av ikonen och tog sedan bort den från verktygsfältet. Nu … Jag har lättat upp lite. Jag kan se att ikonen ibland har några bra användningsområden. Så jag byggde en pivottabell i Excel 2003 och såg till att cellpekaren var i pivottabellen. Jag använde sedan dialogrutan Anpassa för att dra ikonen Skapa GetPivotData till mitt pivottabellverktygsfält. Nu - när jag befinner mig i en pivottabell kan jag välja att aktivera eller inaktivera funktionen.

Varför gjorde Microsoft detta? Finns det någon bra användning för GetPivotData?

Vad ska GetPivotData göra? Det är uppenbart att Microsoft gillar funktionen eftersom de tvingade den på miljoner intet ont anande människor som använder pivottabeller.

GetPivotData returnerar alla synliga celler från en pivottabell. Istället för att peka på en celladress får du dock beskriva värdet som en skärningspunkt mellan olika fältvärden.

När pivottabellen ändras kommer GetPivotData att fortsätta att returnera samma logiska data från pivottabellen, förutsatt att data fortfarande syns i pivottabellen. Detta kan vara viktigt om du byter från månad till månad i sidfältet i en pivottabell och alltid vill returnera försäljningen för en viss kund. När kundlistan ändras varje månad kommer kundens position att ändras. Genom att använda =GETPIVOTDATAkan du se till att du returnerar rätt värde från pivottabellen.

=GETPIVOTDATAbörjar alltid med två särskilda argument. Det har sedan valfritt ytterligare par argument.

Här är de två nödvändiga argumenten:

1. Namnet på ett datafält. Detta kan vara "Summa av intäkter" eller helt enkelt "Intäkter".
2. Alla celler som finns "inuti" pivottabellen. Visste du att din pivottabell har ett namn? Du visste förmodligen inte detta eftersom du inte kan hitta namnet i Excel-gränssnittet. Du måste gå till VBA för att lära dig namnet på pivottabellen. Så - det var lättare för Microsoft att låta dig identifiera pivottabellen genom att peka på vilken cell som helst i pivottabellen. Medan du kan välja vilken cell som helst är det säkrast att välja cellen i det övre vänstra hörnet. Det finns en chans att din pivottabell kan krympa för en viss kombination av sidfält. I så fall ser du till att du fortsätter att peka inuti pivottabellen genom att använda cellen uppe till vänster.

Sedan fortsätter du funktionen med ytterligare par argument. Varje par innehåller ett fältnamn och ett värde.

GetPivotData kan bara returnera värden som är synliga i pivottabellen

När du ser några GetPivotData-funktioner fungerar kanske du tror att de är mer kraftfulla än de faktiskt är. Faktum är att funktionen bara fungerar om det specifika värdet syns i pivottabellen. Tänk på bilden nedan.

• I cell A2 returnerar GETPIVOTDATA försäljningen av ABC i januari 2004 i Centralregionen. Detta tar 80003 från cell G19.
• Formeln i A6 misslyckas dock. Det ber om försäljning av ABC i alla regioner. Pivottabellen visar totalt ABC för centralt, totalt ABC för öst, totalt ABC för väst, men det visar aldrig totalt ABC för alla regioner, så resultatet är ett #REF! fel.
• Om du skulle svänga regionfältet upp till sidområdet skulle formeln i A6 börja fungera, men formeln i A2 och A4 skulle börja returnera #REF !. Om du väljer Central från rullgardinsmenyn Region, fungerar alla fyra formlerna.
• Om du stänger av totalsummor i dialogrutan Pivottabellalternativ kommer många GetPivotData-funktioner att börja returnera #REF! fel.