GetPivotData în Excel - Sfaturi Excel

Tocmai ați construit un tabel pivot în Excel. Faceți clic în afara tabelului pivot. Construiți o formulă Excel. Copiați această formulă în jos pe toate rândurile din tabelul pivot. Calculul raportează răspunsul greșit pentru toate, cu excepția primului rând al tabelului pivot. Tocmai ați fost înțepenit de „caracteristica” Generați GetPivotData. Să aruncăm o privire la ce este aceasta și cum să o prevenim.

Preveniți doar problema GetPivotData - Metoda rapidă

Cel mai rapid mod de a împiedica Excel să genereze GetPivotData este să tastați formula fără a utiliza mouse-ul sau tastele săgeți. Dacă tastați pur și simplu =E5/D5, atunci Excel va crea o formulă relativă "normală" care poate fi copiată în jos pe toate rândurile adiacente tabelului pivot.

Prevenirea problemei GetPivotData - Metoda permanentă

Există o setare ascunsă pentru a opri Excel să genereze GetPivotData. Este mai ascuns în Excel 2003 decât în ​​Excel 2007.

În Excel 2007, urmați acești pași:

• Creați un tabel pivot în Excel 2007
• Asigurați-vă că celula activă se află în interiorul tabelului pivot
• Uită-te în partea stângă a filei Opțiuni Instrumente tabel pivot din panglică. Există un buton Opțiuni. Nu faceți clic pe butonul Opțiuni! În partea dreaptă a butonului Opțiuni este o săgeată drop-down. Faceți clic pe săgeata drop-down. Debifați Generare GetPivotData.
• Acum puteți introduce formule folosind mouse-ul, tastele săgeată sau tastând.

În Excel 2003 și versiunile anterioare, urmați acești pași:

• Alege Instrumente, Personalizează
• Există trei file în dialogul Personalizare. Alegeți fila Comenzi din centru.
• În caseta de listă din stânga, alegeți al 7-lea element, Date
• În caseta de listă din dreapta, derulați aproape până la capăt. A 8-a pictogramă de la sfârșitul listei este Generate GetPivotData. Trageți această pictogramă din caseta listă și plasați-o în mijlocul oricărei bare de instrumente existente.
• Faceți clic pe butonul Închidere pentru a închide dialogul.
• Faceți clic pe pictograma pe care tocmai ați adăugat-o în bara de instrumente Excel. Aceasta va dezactiva caracteristica. Acum puteți introduce formule folosind mouse-ul fără a genera funcții GetPivotData.

Iată un sfat bonus: obișnuiam să disprețuiesc această caracteristică și pur și simplu doream să fie oprită tot timpul. Am adăugat pictograma la bara mea de instrumente, am oprit pictograma, apoi am eliminat-o din bara de instrumente. Acum … m-am ușurat puțin. Văd că pictograma are ocazional câteva utilizări bune. Deci, am construit un tabel pivot în Excel 2003 și m-am asigurat că indicatorul de celulă se află în tabelul pivot. Apoi am folosit caseta de dialog de personalizare pentru a trage pictograma Generați GetPivotData în bara mea de instrumente Pivot Table. Acum - când mă aflu într-un tabel pivot, pot alege să activez sau să dezactivați funcția.

De ce a făcut Microsoft acest lucru? Există vreo utilizare bună pentru GetPivotData?

Ce ar trebui să facă GetPivotData? În mod clar, Microsoft îi place această funcție, deoarece a forțat-o pe milioane de oameni nebănuitori care folosesc tabele pivot.

GetPivotData va returna orice celulă vizibilă dintr-un tabel pivot. În loc să indicați o adresă de celulă, veți descrie valoarea ca o intersecție a diferitelor valori ale câmpului.

Pe măsură ce se modifică tabelul pivot, GetPivotData va continua să returneze aceleași date logice din tabelul pivot, cu condiția ca datele să fie încă vizibile în tabelul pivot. Acest lucru poate fi important dacă schimbați de la lună la lună în câmpul de pagină al unui tabel pivot și doriți să returnați întotdeauna vânzările pentru un anumit client. Pe măsură ce lista de clienți se schimbă în fiecare lună, poziția clientului se va schimba. Prin utilizare =GETPIVOTDATA, vă puteți asigura că returnați valoarea corectă din tabelul pivot.

=GETPIVOTDATAîncepe întotdeauna cu două argumente particulare. Apoi are opțional perechi suplimentare de argumente.

Iată cele două argumente necesare:

1. Numele unui câmp de date. Aceasta poate fi „Suma veniturilor” sau pur și simplu „Venituri”.
2. Orice celulă care se află „în interiorul” tabelului pivot. Știați că tabelul pivot are un nume? Probabil că nu știați acest lucru, deoarece nu puteți descoperi numele în interfața Excel. Ar trebui să mergeți la VBA pentru a afla numele tabelului pivot. Deci - a fost mai ușor pentru Microsoft să vă permită să identificați tabelul pivot arătând către orice celulă din tabelul pivot. Deși puteți alege orice celulă, este cel mai sigur să alegeți celula din colțul din stânga sus. Există șansa ca tabelul pivot să se micșoreze pentru o anumită combinație de câmpuri de pagină. În acest caz, utilizarea celulei din colțul din stânga sus vă va asigura că continuați să indicați în interiorul tabelului pivot.

Apoi, continuați funcția cu perechi suplimentare de argumente. Fiecare pereche include un nume de câmp și o valoare.

GetPivotData poate returna doar valorile vizibile în tabelul pivot

După ce vedeți câteva funcții GetPivotData funcționând, ați putea crede că sunt mai puternice decât sunt de fapt. De fapt, funcția va funcționa numai dacă valoarea particulară este vizibilă în tabelul pivot. Luați în considerare imaginea de mai jos.

• În celula A2, GETPIVOTDATA returnează vânzările de ABC în ianuarie 2004 în regiunea centrală. Aceasta captează 80003 din celula G19.
• Cu toate acestea, formula din A6 eșuează. Solicită vânzări de ABC în toate regiunile. Tabelul pivot arată ABC total pentru Central, ABC total pentru Est, ABC total pentru Vest, dar nu afișează niciodată ABC total pentru toate regiunile, deci rezultatul este un #REF! eroare.
• Dacă ați pivota câmpul regiunii până la zona paginii, formula din A6 ar începe să funcționeze, dar formula din A2 și A4 ar începe să returneze #REF !. Dacă alegeți Central din meniul derulant Region, toate cele patru formule ar funcționa.
• Dezactivarea Totalului total în dialogul Opțiuni pentru tabelul pivot va determina ca multe funcții GetPivotData să înceapă să returneze #REF! erori.