Odiate la funzione GETPIVOTDATA di Excel? Perché appare? Come puoi prevenirlo? C'è un buon uso per GETPIVOTDATA?
La maggior parte delle persone incontra GETPIVOTDATA per la prima volta quando tenta di creare una formula al di fuori di una tabella pivot che utilizza i 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-1 senza 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 tipo 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 su 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 figura molto piccola seguente mostra un nuovo foglio di lavoro Excel aggiunto alla cartella di lavoro. Questo è tutto solo Excel semplice, niente tabelle pivot. L'unico tocco di magia è 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, Actuals for Baybrook. Fare clic in quella cella e digitare un segno di uguale. Usando il mouse, torna alla tabella pivot. Trova la cella per Actual di gennaio 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.
Ecco la formula dopo averla modificata. Sono finiti "Baybrook", "Jan" e "Actual". Invece, stai indicando $ D6, E $ 3, E $ 4.
Copia questa formula e quindi scegli Incolla speciale, Formule in tutte le altre celle numeriche.
Ora ecco il tuo flusso di lavoro annuale:
- 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.
Cambia cella P1
Devi ammettere che l'utilizzo di un rapporto semplice che estrae i numeri da una tabella pivot ti dà 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.
Guarda un video
- GetPivotData si verifica quando una formula punta all'interno di una tabella pivot
- Sebbene la formula iniziale sia corretta, non è possibile copiare la formula
- La maggior parte delle persone odia getpivotdata e vuole prevenirlo
- Metodo 1: crea una formula senza il mouse o i tasti freccia
- Metodo 2: disattivare GetPivotData in modo permanente utilizzando il menu a discesa accanto alle opzioni
- Ma c'è un uso per GetPivotData
- Il tuo manager desidera un report con Actual per i mesi passati e budget per il futuro
- Il normale flusso di lavoro prevede la creazione di una tabella pivot, la conversione in valori, l'eliminazione di colonne
- Rimozione dei totali parziali per evitare il piano effettivo + gennaio utilizzando le impostazioni in loco
- Crea invece una tabella pivot con "troppi" dati
- Utilizzare un foglio di lavoro del report ben formattato
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Dalla prima cella di dati del foglio di lavoro, crea una formula con il mouse
- Consenti a GetPivotData di verificarsi
- Esamina la sintassi di GetPivotData (campo da restituire, posizione pivot, coppie)
- Modificare il valore hardcoded in modo che punti a una cella
- Premendo F4 tre volte si blocca solo la colonna
- Premendo F4 due volte si blocca solo la riga
- Incolla formule speciali
- Flusso di lavoro il mese prossimo: aggiunta di dati, aggiornamento della tabella pivot, modifica della data
- Ultra attento a guardare fuori per nuovi negozi
Trascrizione del video
Impara Excel dal podcast, episodio 2013 - GetPivotData potrebbe non essere del tutto malvagio!
Metterò in podcast l'intero libro, fai clic sulla "i" nell'angolo in alto a destra per iscriverti.
Bene, nell'episodio 1998 ho parlato brevemente di questo problema di GetPivotData. Se calcoliamo una varianza% e siamo fuori dalla tabella pivot che punta all'interno e io uso il mouse o il tasto freccia, quindi 2019 / 2018-1. Questa risposta che otterremo qui è corretta per gennaio, ma quando facciamo doppio clic per copiarla, la formula non viene copiata, otteniamo la risposta di gennaio fino in fondo. E quando lo guardiamo, stiamo ottenendo GetPivotData, non ho digitato GetPivotData, ho solo indicato quelle celle, e questo ha iniziato a succedere in Excel 2002 senza alcun preavviso. E a quel punto ho detto che il modo per evitarlo è digitare la formula C5 / B5-1 e otterrai una formula che puoi copiare. Oppure, se odi solo GetPivotData, se è "completamente malvagio", vai alla scheda Analizza, nont aprire il pulsante Opzioni a proposito. Torna alla tabella pivot, vai alla scheda Analizza, apri il menu a discesa accanto a Opzioni, deseleziona questa casella, è un'impostazione globale. Una volta spento, sarà spento per sempre, va bene.
La maggior parte delle volte le domande che ricevo sono "Come faccio a disattivare GetPivotData?" ma ogni tanto troverò qualcuno che ama GetPivotData. E stavo pranzando con Rob Collie quando era ancora in Microsoft, e lui ha detto "Beh, i nostri clienti interni adorano GetPivotData". Ho detto "Cosa? No, tutti odiano GetPivotData! " Rob dice "Hai ragione, al di fuori di Microsoft, assolutamente, odiano GetPivotData". Sto parlando dei contabili all'interno di Microsoft, e in seguito ne ho incontrato uno che ora lavora per il team di Excel, Carlos, e Carlos era uno dei contabili che usa questo metodo.
Va bene, quindi ecco cosa dobbiamo fare. Abbiamo il nostro report, un set di dati qui che per ogni mese abbiamo il piano per ogni negozio, e poi in fondo stiamo accumulando effettivi. OK, quindi abbiamo valori effettivi da gennaio a dicembre, ma solo pochi mesi, i mesi trascorsi. E quello che il nostro manager vuole che facciamo è creare un rapporto con i negozi sul lato sinistro, solo i negozi del Texas, ovviamente, per rendere la vita più difficile. E poi abbiamo mesi, e se abbiamo un effettivo per quel mese, mostriamo l'attuale, quindi gennaio effettivo, febbraio effettivo, marzo effettivo, aprile effettivo. Ma poi per i mesi in cui non abbiamo effettivi cambiamo e mostriamo il budget, quindi budget fino a dicembre, e poi un totale che totalizza tutto, va bene. Bene, quando provi a creare questa tabella pivot, sì,non funziona.
Quindi inserisci Tabella pivot, Nuovo foglio di lavoro, metti Store in basso a sinistra, lato che è bello, metti Mesi in alto, metti Tipo in alto, metti Vendite qui, va bene. Quindi ecco cosa otteniamo con cui iniziare a lavorare, quindi abbiamo gennaio effettivo, piano di gennaio e poi il piano più effettivo di gennaio completamente inutile. Nessuno lo userà mai, ma posso sbarazzarmi di queste colonne grigie, è abbastanza facile, alcune qui in questa cella, vai su Impostazioni campo e cambia i totali parziali su Nessuno. Ma non c'è assolutamente alcun modo per me di rimuovere il piano di gennaio che non rimuoverà anche il piano di aprile maggio giugno luglio, va bene, non c'è modo di sbarazzarsi di questo. Quindi, a questo punto ogni mese, sono bloccato a selezionare l'intera tabella pivot, andare su Copia, quindi Incolla, Incolla valori. Non è più una tabella pivot,e poi inizio a eliminare manualmente le colonne che non compaiono nel rapporto.
Va bene, questo è il metodo normale, ma i contabili di Microsoft hanno aggiunto un passaggio in più a gennaio, ci vogliono 15 minuti e questo passaggio consente a questa tabella pivot di vivere per sempre, giusto? Io la chiamo la tabella pivot più brutta del mondo ei contabili di Microsoft accettano che questa sia la tabella pivot più brutta del mondo, ma nessuno vedrà mai questo rapporto tranne loro. Quello che fanno è che arrivano qui su un nuovo foglio e creano il rapporto che il loro manager desidera. Bene, quindi ecco i negozi sul lato sinistro, li ho anche raggruppati in Houston, Dallas e altri, è un rapporto ben formattato. Ho evidenziato i totali, vedrai che quando inseriamo alcuni numeri, c'è la valuta sulla prima riga, ma non su queste righe successive, righe vuote. Ooh, righe vuote nella tabella pivot.E un po 'di logica qui, dove posso inserire la data di scadenza nella cella P1, e poi ho una formula qui che analizza che SE il mese della data di scadenza è> questa colonna, e quindi inserire la parola Actual, altrimenti metti la parola Piano, va bene. Quindi tutto quello che devo fare è cambiare questa data e poi la parola Actual capovolgere per pianificare, Va bene.
Ora, ecco cosa facciamo, ci permetteremo di essere GetPivotData'd, giusto? Non sono sicuro che sia un verbo, ma consentiremo a Microsoft di GetPivotData. Quindi inizio a creare una formula con un =, prendo il mouse e vado a cercare il Baybrook di gennaio! Quindi torno nella tabella pivot più brutta del mondo, trovo Baybrook, trovo gennaio, trovo reale e faccio clic su Invio, e lascio che me lo facciano, va bene, eccoci, ora abbiamo una formula GetPivotData. Ricordo il giorno in cui l'ho fatto, è stato come, sai, dopo che Rob mi ha spiegato cosa stavano facendo, e sono tornato indietro e l'ho provato. Ora, all'improvviso, per tutta la vita, mi sono sbarazzato di GetPivotData, non ho mai abbracciato GetPivotData. Quindi quello che è, è il primo elemento è quello che stiamo cercando, lì 'un campo chiamato Vendite, è qui che inizia la tabella pivot e può essere qualsiasi cella nella tabella pivot, usano la mano in alto a sinistra.
OK, questo è un nome di campo "Store", quindi hanno codificato "Baybrook", questo è un nome di campo "Mese", hanno codificato "gennaio", questo è un nome di campo "Tipo" e " ho hardcoded "Actual". Ecco perché non puoi copiarlo, perché hanno codificato i valori. Ma i contabili di Microsoft, Carlos e i suoi colleghi realizzano "Whoa, aspetta un attimo, abbiamo la parola Baybrook qui, abbiamo gennaio qui, abbiamo Actual qui. Dobbiamo solo modificare questa formula in modo che punti alle celle effettive nel rapporto invece di essere codificata ". Va bene, quindi lo chiamano parametrizzazione GetPivotData.
Rimuovi la parola Baybrook, vieni qui e fai clic sulla cella D6. Ora, devo bloccarlo nella colonna, va bene, quindi premo il tasto F4 3 volte, prendo un solo $ prima della D, va bene. Per il mese di gennaio rimuovo il gennaio hardcoded, clicco sulla cella E3, premo due volte F4 per bloccarlo sulla riga E $ 3. Digita Actual, rimuovi la parola Actual, fai clic su E4, di nuovo F4 due volte, va bene, e ottengo una formula che ora recupera quei dati. Lo copierò, quindi Incolla speciale, scegli Formati, alt = "" ESF, vedo la F è sottolineata lì, ESF Invio, e poi ora, l'ho fatto, ripeterò semplicemente con F4, F4 è una ripetizione e F4. Va bene, quindi ora abbiamo un bel rapporto, ha spazi vuoti, ha una formattazione, ha la singola sottolineatura contabile sotto ogni sezione,in fondo ha la doppia sottolineatura contabile.
Bene, non ottieni mai queste cose in una tabella pivot, è impossibile, ma questo rapporto è guidato dalla tabella pivot. Quindi cosa facciamo quando otteniamo i valori effettivi di maggio, torna qui, incollali, aggiorna la tabella pivot più brutta del mondo e poi qui sul rapporto cambia semplicemente la data di scadenza da 4/30 a 5/31. E ciò che fa è che questa formula passa dalla parola Piano a Effettivo, che va e estrae i valori effettivi dal rapporto, invece del piano, ok. Ora, ecco la cosa che … questo è fantastico, giusto? Posso vedere dove lo farei spesso se ancora, sai, lavorassi in contabilità.
La cosa a cui devi stare davvero attento è che se costruiscono un nuovo negozio, devi sapere di aggiungerlo manualmente, giusto, i dati verranno visualizzati nella tabella pivot, ma li aggiungeresti manualmente. Ora questo è un sottoinsieme di tutti i negozi, se riportasse tutti i negozi, probabilmente qui fuori, al di fuori dell'intervallo di stampa, avrei qualcosa che estrae il totale dalla tabella pivot. E poi saprei, se questo totale non corrisponde al totale generale della tabella pivot, che qualcosa non va, e ho una funzione IF quaggiù Dicendo "Ehi, sai, nuovi dati che sono stati aggiunti, stai molto attento. " Hanno una sorta di meccanismo per rilevare la presenza di nuovi dati. Ma ho capito, è un ottimo uso. Quindi, mentre la maggior parte delle volte GetPivotData ci fa impazzire, in realtà può esserci un uso per questo. Tutto a posto,quindi questo è il suggerimento n. 21 su 40 nel libro, acquista il libro subito, ordina online, fai clic sulla "i" nell'angolo in alto a destra.
Riepilogo lungo, lungo oggi, va bene: GetPivotData si verifica quando una formula punta all'interno di una tabella pivot, una formula all'esterno della tabella pivot punta all'interno. Sebbene la formula iniziale sia corretta, non verrà copiata. La maggior parte delle persone odia GetPivotData e vuole impedirlo. Quindi puoi costruire una formula senza il mouse o i tasti freccia, basta digitare la formula o disattivare GetPivotData in modo permanente, ah, ma c'è un uso, va bene. Quindi dobbiamo creare un rapporto con i valori effettivi per il mese scorso e il budget per il futuro. Flusso di lavoro normale, creare una tabella pivot, convertire in valori, eliminare colonne. C'è un modo per rimuovere i totali parziali utilizzando Impostazioni campo, eliminando quel piano Plus effettivo di gennaio. Invece creeremo solo la tabella pivot più brutta del mondo con troppi dati.
Costruisci un vecchio foglio di lavoro del report ben formattato, semplicemente con forse un po 'di logica per cambiare la parola Actual in Plan. Quindi dalla prima cella del rapporto, il primo posto in cui saranno i numeri in quel rapporto, digita un =, vai alla tabella pivot e consenti a GetPivotData di accadere. Esaminiamo la sintassi di GetPivotData, quindi è il campo da restituire, Sales, dove risiede la tabella pivot, e quindi le coppie di criteri, il nome del campo e il valore. Rimuoveremo il valore hardcoded e puntiamo a una cella, premendo F4 3 volte si blocca solo la colonna, premendo F4 2 volte si blocca solo la riga, copia quella formula, Incolla formule speciali. Ho aggiunto un suggerimento in più che F4 è un rifacimento, quindi dovevo andare alla finestra di dialogo Incolla speciale una volta, e poi per le successive formule speciali Incolla ho appena usato F4. Il prossimo mese aggiungi i dati,aggiorna la tabella pivot, modifica la data di scadenza. Assicurati che non abbiano costruito nuovi negozi, sai, hanno una sorta di meccanismo, manuale o una formula di controllo, dai un'occhiata. Grazie a iTrainerMX su Twitter, che ha suggerito GetPivotData, anche Carlos e Rob di Microsoft, Rob ora di Power Pivot Pro. Carlos per averlo usato, e Rob per avermi detto che Carlos lo stava usando, ho incontrato Carlos più tardi, e lui ha confermato di sì, era uno dei contabili che lo usava tutto il tempo in Microsoft, va bene, ecco qua.e Rob per avermi detto che Carlos lo stava usando, ho incontrato Carlos più tardi, e lui ha confermato di sì, era uno dei contabili che lo usava tutto il tempo in Microsoft, va bene, ecco qua.e Rob per avermi detto che Carlos lo stava usando, ho incontrato Carlos più tardi, e lui ha confermato di sì, era uno dei contabili che lo usava tutto il tempo in Microsoft, va bene, ecco qua.
Ehi, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2013.xlsx
