GetPivotData i Excel - Excel-tips

Du har nettopp laget en pivottabell i Excel. Du klikker utenfor pivottabellen. Du bygger en Excel-formel. Du kopierer denne formelen til alle radene i pivottabellen. Beregningen rapporterer feil svar for alle bortsett fra første rad i pivottabellen. Du har nettopp blitt stukket av Generate GetPivotData "-funksjonen". La oss ta en titt på hva dette er, og hvordan vi kan forhindre det.

Bare forhindre GetPivotData-problemet - den raske metoden

Den raskeste måten å forhindre at Excel genererer GetPivotData, er å skrive formelen uten å bruke musen eller piltastene. Hvis du bare skriver =E5/D5, vil Excel opprette en "normal" relativ formel som kan kopieres ned til alle radene ved siden av pivottabellen.

Forhindre GetPivotData-problemet - den permanente metoden

Det er en skjult innstilling for å forhindre at Excel genererer GetPivotData. Det er mer skjult i Excel 2003 enn i Excel 2007.

Følg disse trinnene i Excel 2007:

• Opprett en pivottabell i Excel 2007
• Forsikre deg om at den aktive cellen er inne i pivottabellen
• Se på venstre side av kategorien Alternativer for pivottabellverktøy på båndet. Det er en alternativknapp. Ikke klikk på Alternativer-knappen! På høyre side av alternativknappen er det en rullegardinpil. Klikk på rullegardinpilen. Fjern merket for Generer GetPivotData.
• Du kan nå angi formler ved hjelp av musen, piltastene eller skriver.

Følg disse trinnene i Excel 2003 og tidligere:

• Velg Verktøy, Tilpass
• Det er tre faner i dialogboksen Tilpass. Velg kommandofanen i midten.
• I det venstre listeboksen velger du det 7. elementet, Data
• I høyre listeboks blar du nesten helt til bunnen. Det åttende ikonet fra slutten av listen er Generer GetPivotData. Dra dette ikonet fra listeboksen og slipp det midt i en eksisterende verktøylinje.
• Klikk på Lukk-knappen for å lukke dialogboksen.
• Klikk på ikonet du nettopp la til i Excel-verktøylinjen. Dette vil slå av funksjonen. Du kan nå angi formler ved hjelp av musen uten å generere GetPivotData-funksjoner.

Her er et bonustips: Jeg pleide å forakte denne funksjonen og ville bare ha den slått av hele tiden. Jeg la til ikonet i verktøylinjen min, slått av ikonet og fjernet det fra verktøylinjen. Nå … Jeg har lett meg litt. Jeg kan se at ikonet noen ganger har noen gode bruksområder. Så jeg bygde en pivottabell i Excel 2003 og sørget for at cellepekeren var i pivottabellen. Jeg brukte deretter tilpasse dialogboksen til å dra Generer GetPivotData-ikonet til pivottabellverktøylinjen. Nå - når jeg er i et pivottabell, kan jeg velge å slå funksjonen på eller av.

Hvorfor gjorde Microsoft dette? Er det god bruk for GetPivotData?

Hva skal GetPivotData gjøre? Tydeligvis liker Microsoft funksjonen siden de tvang den til millioner av intetanende mennesker som bruker pivottabeller.

GetPivotData returnerer en hvilken som helst synlig celle fra en pivottabell. I stedet for å peke på en celleadresse, skjønner du imidlertid verdien som et skjæringspunkt mellom forskjellige feltverdier.

Når pivottabellen endres, vil GetPivotData fortsette å returnere de samme logiske dataene fra pivottabellen, forutsatt at data fremdeles er synlige i pivottabellen. Dette kan være viktig hvis du endrer fra måned til måned i sidefeltet i en pivottabell, og du alltid vil returnere salget til en bestemt kunde. Når kundelisten endres hver måned, vil kundens stilling endres. Ved å bruke =GETPIVOTDATAkan du sikre at du returnerer riktig verdi fra pivottabellen.

=GETPIVOTDATAstarter alltid med to spesielle argumenter. Det har deretter valgfrie flere par argumenter.

Her er de to nødvendige argumentene:

1. Navnet på et datafelt. Dette kan være "Sum of Revenue" eller bare "Revenue".
2. Enhver celle som ligger "inne" i pivottabellen. Visste du at pivottabellen din har et navn? Du visste sannsynligvis ikke dette fordi du ikke kan oppdage navnet i Excel-grensesnittet. Du må gå til VBA for å lære navnet på pivottabellen. Så - det var lettere for Microsoft å tillate deg å identifisere pivottabellen ved å peke på en hvilken som helst celle i pivottabellen. Mens du kan velge hvilken som helst celle, er det tryggest å velge cellen øverst til venstre. Det er en sjanse for at pivottabellen kan krympe for en bestemt kombinasjon av sidefelt. I så fall vil du ved å bruke cellen øverst til venstre sørge for at du fortsetter å peke inne i pivottabellen.

Deretter fortsetter du funksjonen med flere par argumenter. Hvert par inkluderer et feltnavn og en verdi.

GetPivotData kan bare returnere verdier som er synlige i pivottabellen

Når du ser noen GetPivotData-funksjoner fungerer, kan du tro at de er kraftigere enn de faktisk er. Faktisk vil funksjonen bare fungere hvis den spesielle verdien er synlig i pivottabellen. Tenk på bildet nedenfor.

• I celle A2 returnerer GETPIVOTDATA salg i januar 2004 av ABC i den sentrale regionen. Dette tar 80003 fra celle G19.
• Formelen i A6 mislykkes imidlertid. Det ber om salg av ABC i alle regioner. Pivottabellen viser total ABC for sentral, total ABC for øst, total ABC for vest, men den viser aldri total ABC for alle regioner, så resultatet er en #REF! feil.
• Hvis du vil svinge regionfeltet opp til sideområdet, vil formelen i A6 begynne å fungere, men formelen i A2 og A4 vil begynne å returnere #REF !. Hvis du valgte Sentralt fra rullegardinmenyen Region, fungerer alle fire formlene.
• Hvis du slår av totaler i dialogboksen Pivottabellalternativer, vil mange GetPivotData-funksjoner begynne å returnere #REF! feil.