Urăști funcția GETPIVOTDATA din Excel? De ce apare? Cum o puteți preveni? Există o utilizare bună pentru GETPIVOTDATA?
Majoritatea oamenilor întâmpină întâi GETPIVOTDATA atunci când încearcă să construiască o formulă în afara unui tabel pivot care utilizează numere în tabelul pivot. De exemplu, acest procent de varianță nu se va copia în celelalte luni din cauza inserării funcțiilor GETPIVOTDATA în Excel.
Excel introduce GETPIVOTDATA de fiecare dată când utilizați mouse-ul sau tastele săgeată pentru a indica o celulă din tabelul pivot în timp ce construiți o formulă în afara tabelului pivot.
Apropo, dacă nu doriți să apară funcția GETPIVOTDATA, pur și simplu tastați o formulă precum = D5 / C5-1 fără a utiliza mouse-ul sau tastele săgeată pentru a indica spre celule. Această formulă se copiază fără probleme.
Iată un set de date care conține un număr de plan pe lună pe magazin. Există, de asemenea, vânzări reale pe lună pe magazin pentru lunile complete. Obiectivul dvs. este să creați un raport care să prezinte date reale pentru lunile finalizate și să planificați pentru lunile viitoare.
Construiți un tabel pivot cu Store în ROWS. Puneți Luna și Tastați în COLOANE. Veți primi raportul prezentat mai jos, cu Planul Ianuarie Actual, Planul Ianuarie și Planul Ianuarie Actual + Plan complet lipsit de sens.
Dacă selectați o celulă lunară și accesați Setări câmp, puteți schimba subtotalurile la Niciuna.
Aceasta elimină inutilul Plan Actual +. Dar trebuie totuși să scăpați de coloanele planului pentru ianuarie până în aprilie. Nu există o modalitate bună de a face acest lucru în interiorul tabelului pivot.
Deci, fluxul de lucru lunar devine:
- Adăugați datele reale pentru noua lună la setul de date.
- Construiți un nou tabel pivot de la zero.
- Copiați tabelul pivot și lipiți ca valori, astfel încât să nu mai fie un tabel pivot.
- Ștergeți coloanele de care nu aveți nevoie.
Există o modalitate mai bună de parcurs. Următoarea figură foarte mică arată o nouă foaie de lucru Excel adăugată în registrul de lucru. Acest lucru este doar Excel direct, fără tabele pivot. Singurul bit de magie este o funcție IF în rândul 4 care comută de la Actual la Plan pe baza datei din celula P1.
Prima celulă care trebuie completată este ianuarie, Actuals pentru Baybrook. Faceți clic în acea celulă și tastați un semn egal. Folosind mouse-ul, navigați înapoi la tabelul pivot. Găsiți celula pentru ianuarie Actuals pentru Baybrook. Faceți clic pe acea celulă și apăsați Enter. Ca de obicei, Excel construiește una dintre acele funcții enervante GETPIVOTDATA care nu pot fi copiate.
Dar astăzi, să studiem sintaxa GETPIVOTDATA.
Primul argument de mai jos este câmpul numeric „Vânzări”. Al doilea argument este celula în care se află tabelul pivot. Celelalte perechi de argumente sunt numele câmpului și valoarea. Vedeți ce a făcut formula generată automat? Numele magazinului a fost codificat „Baybrook”. De aceea nu puteți copia aceste formule GETPIVOTDATA generate automat. De fapt, denumesc coduri în formule. Chiar dacă nu puteți copia aceste formule, le puteți edita. În acest caz, ar fi mai bine dacă ați editat formula pentru a indica celula $ D6.
Iată formula după ce o editați. Au dispărut „Baybrook”, „Jan” și „Actual”. În schimb, indicați $ D6, E $ 3, E $ 4.
Copiați această formulă și apoi alegeți Lipire specială, formule în toate celelalte celule numerice.
Iată acum fluxul de lucru anual:
- Construiește un tabel pivot urât pe care nimeni nu îl va vedea vreodată.
- Configurați foaia de lucru a raportului.
În fiecare lună, trebuie să:
- Inserați noi date reale sub date.
- Reîmprospătați tabelul pivot urât.
-
Schimbați celula P1 din foaia de raport pentru a reflecta luna nouă. Toate numerele se actualizează.
Schimbați celula P1
Trebuie să recunoașteți că utilizarea unui raport simplu care extrage numerele dintr-un tabel pivot vă oferă cele mai bune din ambele lumi. Sunteți liber să formatați raportul în moduri în care nu puteți formata un tabel pivot. Rândurile goale sunt bine. Puteți avea simboluri valutare pe primul și ultimul rând, dar nu între ele. Veți obține, de asemenea, dubluri sublinieri sub totalurile mari.
Mulțumim @iTrainerMX pentru sugerarea acestei funcții.
Urmăriți videoclipul
- GetPivotData se întâmplă atunci când o formulă indică în interiorul unui tabel pivot
- Deși formula inițială este corectă, nu puteți copia formula
- Majoritatea oamenilor urăsc getpivotdata și vor să o prevină
- Metoda 1: Construiți o formulă fără mouse sau tastele săgeată
- Metoda 2: dezactivați GetPivotData permanent folosind meniul derulant de lângă opțiuni
- Dar există o utilizare pentru GetPivotData
- Managerul dvs. dorește un raport cu Actualități pentru ultimele luni și buget pentru viitor
- Fluxul normal de lucru vă va face să creați un tabel pivot, să îl convertiți în valori, să ștergeți coloane
- Eliminarea subtotalurilor pentru a preveni Planul real + ianuarie folosind Setările câmpului
- În schimb, creați un tabel pivot cu date „prea multe”
- Utilizați o foaie de lucru de raport frumos formatată
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Din prima celulă de date din foaia de lucru, construiți o formulă cu mouse-ul
- Permiteți GetPivotData să se întâmple
- Examinați sintaxa GetPivotData (câmp pentru returnare, locație pivot, perechi)
- Modificați valoarea codificată pentru a indica o celulă
- Apăsarea F4 de trei ori blochează doar coloana
- Apăsând F4 de două ori se blochează doar rândul
- Lipiți formule speciale
- Fluxul de lucru luna viitoare: adăugați date, reîmprospătați tabelul pivot, modificați data
- Ultra atent să ai grijă de magazinele noi
Transcrierea videoclipului
Aflați Excel din podcast, episodul 2013 - GetPivotData poate să nu fie complet rău!
Voi transmite podcast-ul întregii cărți, faceți clic pe „i” din colțul din dreapta sus pentru a vă abona.
În regulă, în episodul 1998 am vorbit pe scurt despre această problemă GetPivotData. Dacă calculăm o varianță% și suntem în afara tabelului pivot îndreptat spre interior și folosesc mouse-ul sau tasta săgeată, deci 2019 / 2018-1. Acest răspuns pe care îl vom obține aici este corect pentru ianuarie, dar când facem dublu clic pentru a copia acest lucru, formula nu se copiază, obținem răspunsul din ianuarie până la capăt. Și când ne uităm la el, primim GetPivotData, nu am tastat GetPivotData, am indicat doar acele celule și acest lucru a început să se întâmple în Excel 2002 fără niciun avertisment. Și în acel moment am spus că modalitatea de a evita acest lucru este să tastați formula C5 / B5-1 și veți obține o formulă pe care o puteți copia. Sau dacă pur și simplu urăști GetPivotData, dacă este „complet rău”, accesează fila Analize, nuDeschideți butonul Opțiuni apropo. Reveniți la tabelul pivot, accesați fila Analize, deschideți meniul derulant de lângă Opțiuni, debifați această casetă, este o setare globală. Odată ce îl opriți, va fi oprit pentru totdeauna, bine.
De cele mai multe ori întrebările pe care le primesc sunt „Cum dezactivez GetPivotData?” dar din când în când voi primi pe cineva care iubește GetPivotData. Și luam masa cu Rob Collie când era încă la Microsoft și mi-a spus „Ei bine, clienții noștri interni adoră GetPivotData”. Am spus „Ce? Nu, toată lumea urăște GetPivotData! ” Rob spune „Ai dreptate, în afara Microsoft, absolut, ei urăsc GetPivotData”. Vorbesc despre contabilii din Microsoft și mai târziu am întâlnit unul care lucrează acum pentru echipa Excel, Carlos, iar Carlos a fost unul dintre contabilii care folosesc această metodă.
Bine, deci iată ce trebuie să facem. Avem raportul nostru, un set de date aici că pentru fiecare lună avem planul pentru fiecare magazin, iar apoi în partea de jos acumulăm date reale. Bine, deci avem date reale pentru ianuarie până în decembrie, dar avem date reale doar pentru câteva luni, lunile care au trecut. Și ceea ce vrea managerul nostru să facem este să construim un raport cu magazinele din partea stângă, doar magazinele din Texas, desigur, pentru a face viața mai dificilă. Și apoi trecând peste, avem luni și, dacă avem un efectiv pentru luna respectivă, afișăm efectivul, deci efectivul din ianuarie, efectivul din februarie, efectivul din martie, aprilie real. Dar apoi pentru lunile în care nu avem date reale, trecem și afișăm bugetul, deci bugetează până în decembrie și apoi un total care totalizează totul, bine. Ei bine, când încercați să creați acest tabel pivot, da,nu merge.
Așadar, introduceți tabelul pivot, Foaie de lucru nouă, puneți Magazin în partea stângă, partea care este frumoasă, puneți lunile deasupra, puneți tipul deasupra, puneți vânzările aici, bine. Deci, iată ce obținem cu care trebuie să începem să lucrăm, așa că avem planul real din ianuarie, planul din ianuarie și apoi planul complet plus inutil din ianuarie. Nimeni nu va folosi acest lucru vreodată, dar pot scăpa de aceste coloane gri, ceea ce este destul de ușor, unele aici în această celulă, accesați Setări de câmp și schimbați subtotalele la Niciuna Dar nu există absolut nicio modalitate de a elimina planul din ianuarie care nu va elimina nici planul din aprilie mai iunie iulie, bine, nu există nicio modalitate de a scăpa de acest lucru. Deci, în acest moment, în fiecare lună, rămân blocat selectând întregul tabel pivot, merg la Copiere și apoi Lipire, Lipire valori. Nu mai este un tabel pivot,și apoi încep să șterg manual coloanele care nu apar în raport.
Bine, aceasta este metoda normală, dar contabilii de la Microsoft au adăugat un pas suplimentar în ianuarie, durează 15 minute, iar acest pas permite acestui tabel pivot să trăiască pentru totdeauna, nu? Eu numesc acest lucru cel mai urât tabel pivot din lume, iar contabilii de la Microsoft acceptă că acesta este cel mai urât tabel pivot din lume, dar nimeni nu va vedea niciodată acest raport, cu excepția lor. Ceea ce fac, este să vină aici la o nouă foaie și să construiască raportul pe care managerul lor îl dorește. Bine, așa că iată magazinele din partea stângă, chiar le-am grupat în Houston, Dallas și altele, este un raport frumos formatat. Am evidențiat totalurile, veți vedea că atunci când primim câteva numere, există monedă pe primul rând, dar nu aceste rânduri ulterioare, rânduri goale. Ooh, rânduri goale în tabelul pivot.Și un pic de logică aici, unde pot pune data de trecere în celula P1, și apoi am o formulă aici care analizează că DACĂ luna datei de trecere este> această coloană și apoi pun cuvântul Actual, altfel pune cuvântul Plan, bine. Deci, tot ce trebuie să fac este să schimb această dată și apoi cuvântul Actual să se răstoarne în plan, bine.
Acum, iată ce facem, ne vom permite să fim GetPivotData'd, nu? Nu sunt sigur că acesta este un verb, dar vom permite Microsoft să GetPivotData. Așa că încep să construiesc o formulă cu un =, apuc mouse-ul și voi căuta actualul Baybrook din ianuarie! Așa că mă întorc în cel mai urât tabel din pivot din lume, găsesc Baybrook, găsesc ianuarie, găsesc actual și fac clic pe Enter și las-o să mi-o facă, bine, iată-ne, avem acum o formulă GetPivotData. Îmi amintesc ziua în care am făcut asta, a fost ca, știi, după ce Rob mi-a explicat ce fac, iar eu m-am întors și am încercat-o. Acum, dintr-o dată, toată viața mea, am scăpat de GetPivotData, nu am îmbrățișat niciodată GetPivotData. Deci, ceea ce este, este primul element este ceea ce căutăm, acolo 'un câmp numit Vânzări, de aici începe tabelul pivot și poate fi orice celulă din tabelul pivot, utilizează mâna din stânga sus.
Bine, acesta este un nume de câmp „Magazin”, iar apoi au codat „Baybrook”, acesta este un nume de câmp „Lună”, au codificat „Ianuarie”, acesta este un nume de câmp „Tip”, iar ei „ Am codificat „Actual”. Acesta este motivul pentru care nu îl puteți copia, pentru că au codificat valorile. Dar contabilii de la Microsoft, Carlos și colegii săi își dau seama: „Ei, așteaptă o secundă, avem cuvântul Baybrook aici, avem ianuarie aici, avem Actual aici. Trebuie doar să schimbăm această formulă pentru a indica celulele reale din raport, în loc să fim codificate în mod dur. ” Bine, așa că numesc acest parametrizare GetPivotData.
Eliminați cuvântul Baybrook, veniți aici și faceți clic pe celula D6. Acum, trebuie să blochez asta în coloană, bine, așa că apăs tasta F4 de 3 ori, primesc un singur $ înainte de D, bine. Pentru luna ianuarie am eliminat codul hard ianuarie, fac clic pe celula E3, voi apăsa de două ori F4 pentru a o bloca pe rând, E $ 3. Tastați Actual, eliminați cuvântul Actual, faceți clic pe E4, din nou F4 de două ori, bine, și am o formulă care acum atrage aceste date înapoi. Voi copia asta, apoi Lipesc special, aleg Formate, alt = "" ESF, vezi F este subliniat acolo, ESF Enter, și acum, am făcut asta, voi repeta doar cu F4, F4 este o refacere, iar F4. Bine, așa că acum avem un raport frumos, are spații goale, are formatare, are un singur subliniat contabil sub fiecare secțiune,în partea de jos are sublinierea contabilității duble.
Corect, nu primiți niciodată aceste lucruri într-un tabel pivot, este imposibil, dar acest raport este generat de tabelul pivot. Deci, ce facem atunci când primim actualitatea din mai, revenim aici, lipiți-le în, reîmprospătați cel mai urât tabel din pivot din lume și apoi, aici, în raport, schimbați doar data de trecere de la 30 aprilie la 31 aprilie. Și ceea ce face asta este că face ca această formulă să treacă de la cuvântul Plan la Actual, care merge și extrage realele din raport, în loc de plan, bine. Acum, iată că este un lucru grozav, nu? Înțeleg unde aș face asta mult dacă aș mai lucra în contabilitate.
Lucrul la care trebuie să fii foarte atent este dacă își construiesc un magazin nou, trebuie să știi să-l adaugi manual, corect, datele vor apărea în tabelul pivot, dar le-ai adăuga manual. Acum acesta este un subset al tuturor magazinelor, dacă ar raporta toate magazinele, probabil că aici, în afara gamei de imprimare, aș avea ceva care a scos marele total din tabelul pivot. Și atunci aș ști, dacă acest total nu se potrivește cu totalul total din tabelul pivot, că ceva nu este în regulă și că are o funcție IF aici, spunând „Hei există, știi, date noi care au fost adăugate, fii foarte atent. ” Au un fel de mecanism pentru a detecta că există date noi. Dar îl înțeleg, este o utilizare minunată. Așadar, deși GetPivotData de cele mai multe ori ne înnebunește, de fapt poate fi folosit. Bine,deci acesta este sfatul # 21 din 40 din carte, cumpărați cartea chiar acum, comandați online, dați clic pe „i” din colțul din dreapta sus.
Recapitulare lungă, lungă astăzi, bine: GetPivotData se întâmplă atunci când o formulă indică în interiorul unui tabel pivot, o formulă din afara tabelului pivot indică în interior. Deși formula inițială este corectă, nu va copia. Majoritatea oamenilor urăsc GetPivotData și vor să o prevină. Deci, puteți crea o formulă fără mouse sau tastele săgeată, pur și simplu tastați formula sau dezactivați definitiv GetPivotData, ah, dar există o utilizare, bine. Deci, trebuie să construim un raport cu date reale pentru luna trecută, buget pentru viitor. Flux de lucru normal, creați un tabel pivot, convertiți în valori, Ștergeți coloane. Există o modalitate de a elimina subtotalurile folosind Setări câmp, scăpând de acel plan real plus din ianuarie. În schimb, vom crea cel mai urât tabel pivot din lume cu prea multe date.
Construiți o foaie de lucru a unui raport frumos formatat, simplu, cu un pic de logică pentru a schimba cuvântul Actual în Plan. Și apoi din prima celulă de raport, primul loc în care vor fi numerele în acel raport, tastați un =, mergeți la tabelul pivot și permiteți GetPivotData să se întâmple. Examinăm sintaxa GetPivotData, deci este câmpul de returnat, Vânzări, unde locuiește tabelul Pivot și apoi perechi de criterii, numele câmpului și valoarea. Vom elimina valoarea codificată hard și vom indica o celulă, apăsând F4 de 3 ori blochează doar coloana, apăsând F4 de 2 ori blochează doar rândul, copiați formula respectivă, lipiți formule speciale. Am aruncat un sfat suplimentar acolo că F4 este o refacere, așa că a trebuit să merg la dialogul Lipire specială o singură dată, apoi pentru următoarea Lipire Formule Speciale tocmai am folosit F4. Luna viitoare adăugați datele,reîmprospătați tabelul pivot, modificați data finală. Asigurați-vă că nu au construit niciun magazin nou, știți, au un fel de mecanism, fie manual, fie o formulă de verificare, verificați-l. Mulțumim iTrainerMX pe Twitter, care a sugerat GetPivotData, de asemenea, Carlos și Rob de la Microsoft, Rob acum de la Power Pivot Pro. Carlos pentru că a folosit acest lucru și Rob pentru că mi-a spus că Carlos îl folosește, l-am întâlnit mai târziu pe Carlos și mi-a confirmat că da, a fost unul dintre contabilii care au folosit asta tot timpul la Microsoft, bine, iată-te.și Rob, pentru că mi-a spus că Carlos îl folosește, l-am întâlnit pe Carlos mai târziu și mi-a confirmat că da, a fost unul dintre contabilii care au folosit asta tot timpul la Microsoft, bine, iată-te.și Rob, pentru că mi-a spus că Carlos îl folosește, l-am întâlnit pe Carlos mai târziu și mi-a confirmat că da, a fost unul dintre contabilii care au folosit asta tot timpul la Microsoft, bine, iată-te.
Ei bine, vreau să-ți mulțumesc că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2013.xlsx
