La maggior parte delle persone incontra GETPIVOTDATA per la prima volta quando tenta di creare una formula all'esterno di una tabella pivot che utilizza numeri nella tabella pivot. Ad esempio, questa percentuale di varianza non verrà copiata negli altri mesi a causa dell'inserimento di funzioni GETPIVOTDATA da parte di Excel.
Excel inserisce GETPIVOTDATA ogni volta che utilizzi il mouse o i tasti freccia per puntare a una cella all'interno della tabella pivot durante la creazione di una formula all'esterno della tabella pivot.
A proposito, se non vuoi che appaia la funzione GETPIVOTDATA, digita semplicemente una formula come =D5/C5-1senza usare il mouse o i tasti freccia per puntare alle celle. Quella formula copia senza problemi.
Ecco un set di dati che contiene un numero di piano al mese per negozio. Ci sono anche vendite effettive al mese per negozio per i mesi che sono completi. Il tuo obiettivo è creare un rapporto che mostri i valori effettivi per i mesi completati e pianifichi per i mesi futuri.
Crea una tabella pivot con Store in Rows. Metti mese e digita nelle colonne. Si ottiene il rapporto mostrato di seguito, con gennaio effettivo, piano di gennaio e il piano gennaio Actual + completamente privo di senso.
Se selezioni una cella del mese e vai a Impostazioni campo, puoi modificare i totali parziali su Nessuno.
Questo rimuove l'inutile Piano Actual +. Ma devi ancora sbarazzarti delle colonne del piano da gennaio ad aprile. Non c'è un buon modo per farlo all'interno della tabella pivot.
Quindi, il tuo flusso di lavoro mensile diventa:
- Aggiungi i valori effettivi per il nuovo mese al set di dati.
- Costruisci una nuova tabella pivot da zero.
- Copia la tabella pivot e incolla come valori in modo che non sia più una tabella pivot.
- Elimina le colonne che non ti servono.
C'è un modo migliore per andare. La seguente figura molto compressa mostra un nuovo foglio di lavoro Excel aggiunto alla cartella di lavoro. Questo è tutto solo Excel semplice, niente tabelle pivot. L'unico tocco magico è una funzione IF nella riga 4 che passa da Actual a Plan, in base alla data nella cella P1.
La prima cella che deve essere compilata è Gennaio Actual per Baybrook. Fare clic in quella cella e digitare un segno di uguale.
Usando il mouse, torna alla tabella pivot. Trova la cella per Gennaio effettivo per Baybrook. Fare clic su quella cella e premere Invio. Come al solito, Excel crea una di quelle fastidiose funzioni GETPIVOTDATA che non possono essere copiate.
Ma oggi, studiamo la sintassi di GETPIVOTDATA.
Il primo argomento di seguito è il campo numerico "Vendite". Il secondo argomento è la cella in cui risiede la tabella pivot. Le restanti coppie di argomenti sono il nome e il valore del campo. Vedi cosa ha fatto la formula generata automaticamente? È hardcoded "Baybrook" come nome del negozio. Ecco perché non puoi copiare queste formule GETPIVOTDATA generate automaticamente. In realtà codificano i nomi in formule. Anche se non puoi copiare queste formule, puoi modificarle. In questo caso, sarebbe meglio se modifichi la formula in modo che punti alla cella $ D6.
La figura seguente mostra la formula dopo averla modificata. Sono finiti "Baybrook", "Jan" e "Actual". Invece, stai indicando $ D6, E $ 3 e E $ 4.
Copia questa formula e quindi scegli Incolla speciale, Formule in tutte le altre celle numeriche.
Ora ecco il tuo flusso di lavoro mensile:
- Costruisci una brutta tabella pivot che nessuno vedrà mai.
- Imposta il foglio di lavoro del rapporto.
Ogni mese devi:
- Incolla i nuovi valori effettivi sotto i dati.
- Aggiorna la brutta tabella pivot.
-
Modificare la cella P1 nel foglio del report per riflettere il nuovo mese. Tutti i numeri si aggiornano.
Devi ammettere che l'utilizzo di un rapporto che estrae i numeri da una tabella pivot ti offre il meglio di entrambi i mondi. Sei libero di formattare il rapporto in modi in cui non puoi formattare una tabella pivot. Le righe vuote vanno bene. Puoi avere simboli di valuta sulla prima e sull'ultima riga ma non nel mezzo. Ottieni anche doppie sottolineature sotto i totali generali.
Grazie a @iTrainerMX per aver suggerito questa funzione.
