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 introduceți o formulă, cum ar fi =D5/C5-1fără a utiliza mouse-ul sau tastele săgeată pentru a indica spre celule. Această formulă 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 in 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 subtotale 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 comprimată 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 din rândul 4 care comută de la Actual la Plan, pe baza datei din celula P1.
Prima celulă care trebuie completată este ianuarie actuală 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 Actual 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.
Figura de mai jos arată formula după ce o editați. Au dispărut „Baybrook”, „Jan” și „Actual”. În schimb, indicați $ D6, E $ 3 și E $ 4.
Copiați această formulă și apoi alegeți Lipire specială, formule în toate celelalte celule numerice.
Iată acum fluxul de lucru lunar:
- 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ă.
Trebuie să recunoașteți că utilizarea unui raport care extrage cifre dintr-un tabel pivot vă oferă tot ce este mai bun 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.
