Utilizzo di tabelle pivot

In precedenza, ho già parlato del fatto che quando mi riferisco a una cella tabella pivot invece del solito collegamento, viene restituita la funzione GET.DATA.PUMP.TABLE (vedi). Se sei interessato a come per ovviare a questo inconveniente, consiglio di fare riferimento alla nota citata. Se ti stai chiedendo, perché è così che avviene, e anche quali sono gli aspetti positivi della funzione RICEVI I DATI DEL PANNELLO.TABELLA, quindi suggerisco un frammento del libro Jelen, Alexander. (capitolo 15). La tecnica in esame permetterà di far fronte a molti problemi che causano mal di testa agli utenti di tabelle pivot, in particolare:

Quando la tabella pivot viene aggiornata, la formattazione applicata in precedenza scompare. I formati dei numeri vengono persi. I risultati della regolazione della larghezza delle colonne scompaiono.
Non esiste modo semplice creare una tabella pivot asimmetrica. L'unica opzione è utilizzare set denominati, ma questo metodo è disponibile solo per coloro che utilizzano tabelle pivot del modello di dati e non normali tabelle pivot.
Excel non riesce a ricordare i modelli. Se è necessario ricreare le tabelle pivot più e più volte, sarà necessario raggruppare nuovamente, applicare campi e membri calcolati ed eseguire una serie di altre attività simili.

In effetti, tutto ciò che è descritto qui non è nuovo. Inoltre, tecniche simili sono state utilizzate a partire da Excel 2002. Tuttavia, la mia comunicazione con gli utenti mostra che meno dell'1% le conosce. L'unica domanda che gli utenti hanno è come disattivare la strana funzione GET DATA OF PANEL.TABLE. È un peccato…

Scarica una nota in formato o, esempi in formato

Bene, iniziamo in ordine.

Come abbandonare la problematica funzione GET DATA.PERSONAL TABLES

La funzione GET PIVOTTABLE DATA è stata a lungo un problema per molti utenti. All'improvviso, senza alcun preavviso, il comportamento delle tabelle pivot è cambiato in Excel 2002. Non appena inizi a creare formule al di fuori della tabella pivot che fanno riferimento ai suoi dati, questa funzione viene fuori dal nulla.

Supponiamo nella tabella pivot mostrata in Fig. 1, è necessario confrontare i dati per il 2015 e il 2014.

Figura: 1. Tabella pivot originale

Aggiungi l'intestazione "% Altezza" alla cella D3.
Copia il formato dalla cella C3 alla cella D3.
Immettere un segno di uguale nella cella D4.
Fare clic sulla cella C4.
Immettere il segno / (barra in avanti) per rappresentare l'operazione di divisione.
Fare clic sulla cella B4.
Immettere -1 e premere la combinazione di tasti rimanere nella stessa cella. Formatta il risultato come percentuale. Vedrai che la regione occidentale ha registrato un calo del 43,8% del reddito (Figura 2). Risultati non molto buoni.
Quando hai finito di inserire la tua prima formula, seleziona la cella D4.
Fare doppio clic sul quadratino situato nell'angolo inferiore destro della cella. Questo quadrato rappresenta un quadratino di riempimento che puoi utilizzare per copiare una formula per riempire un'intera colonna nel report.

Dopo aver finito di copiare la formula, guardando lo schermo, ti accorgerai che qualcosa non va: ogni regione ha mostrato un calo del 43,8% per l'anno (Fig. 3).

Figura: 3. Quando finisci di copiare la formula in tutte le celle della colonna, vedrai che ogni regione ha mostrato un calo del 43,8%

È improbabile che ciò accada nella vita reale. Chiunque ti dirà che dopo aver seguito i passaggi precedenti, Excel creerà la formula \u003d C4 / B4-1. Torna alla cella D4 e osserva la barra della formula (Figura 4). Solo un po 'di diavoleria! La semplice formula \u003d C4 / B4-1 non esiste più. Il programma sostituisce invece una costruzione complessa con la funzione GET.DATA.PUMP.TABLE. Perché questa formula fornisce risultati corretti nella cella D4, ma dopo la copia nelle celle sottostanti si rifiuta di funzionare?

La prima reazione di qualsiasi utente a quanto accaduto sarà la seguente: "Cos'è questa strana costruzione di GET.DATA.PERSONAL.TABLE che ha rovinato il mio rapporto?" La maggior parte degli utenti vorrà sbarazzarsi subito di questa funzione. Alcuni chiederanno: "Perché Microsoft ci ha fornito questa funzionalità?"

Non c'era niente di simile in tempi eccellenti 2000. Avendo iniziato a incontrare regolarmente la funzione GET.DATA.PERSONAL.TABLES, la odiavo. Quando in uno dei seminari qualcuno mi ha chiesto come potesse essere utilizzato per il bene della causa, sono rimasto sbalordito. Non mi sono mai posto una domanda del genere! A mio parere, e secondo l'opinione della maggior parte degli utenti di Excel, la funzione GET.DATA.PUMP.TABLE era un prodotto del male che non aveva nulla a che fare con le forze del bene. Fortunatamente, ci sono due modi per disabilitare questa funzione.

Blocco della funzione GET PIVOTTABLE DATA inserendo una formula.C'è un modo semplice per impedire la visualizzazione della funzione GET PIVOTTABLE. Per fare ciò, è necessario creare una formula senza utilizzare il mouse o i tasti cursore. Segui questi passaggi.

Vai alla cella D4 e digita \u003d (segno di uguale).
Immettere C4.
Immettere una / (barra in avanti per la divisione).
Immettere B4.
Immettere -1.
Clicca su accedere.

Ora hai creato un normale file formula di Excel, che può essere copiato nelle celle sotto la colonna e con cui è possibile ottenere i risultati corretti (Fig.5). Come puoi vedere, puoi creare formule in aree esterne alla tabella pivot che fanno riferimento ai dati all'interno della tabella pivot. E coloro che non credono che ciò sia possibile, lascia che eseguano le azioni descritte da soli.

Figura: 5. Basta inserire \u003d C4 / B4-1 dalla tastiera e la formula funzionerà come necessario

Alcuni utenti si sentiranno a disagio con il solito ordine di immissione delle formule. Inoltre, l'opzione proposta è più laboriosa. Se sei uno di questi utenti, il secondo modo è per te ...

Disabilitare la funzione GET PANEL.TABLE DATA.È possibile disabilitare in modo permanente la funzione GET PANEL.TABLE DATA. Fare clic sulla barra multifunzione del menu File → Opzioni... Nella finestra aperta OpzioniEccellere vai al contributo Formule e deseleziona la casella accanto all'opzione Usa la funzioneGetPivotData per collegamenti a tabelle pivot... Clicca su Ok.

Opzione alternativa. Fare clic sulla tabella pivot e nella scheda contestuale che appare Analisi fare clic sull'elenco a discesa accanto al pulsante Opzioni... Deseleziona la casella accanto a Crea GetPivotData (fig.7). La casella di controllo è attiva per impostazione predefinita.

Perché Microsoft ci ha offerto la funzione GET PANEL.TABLES.Se questa funzione è così orribile, perché Microsoft l'ha abilitata per impostazione predefinita? Perché si preoccupano di mantenere il supporto per questa funzionalità nelle versioni più recenti di Excel? Sono a conoscenza del sentimento degli utenti? E passiamo alla parte divertente ...

Utilizzo di GET PIVOTTABLE DATA per migliorare le tabelle pivot

Le tabelle pivot sono una grande invenzione dell'umanità. La tabella pivot viene creata con pochi clic, eliminando la necessità di applicare filtri avanzati, funzione BDSUMM e tabelle di dati. Con le tabelle pivot, puoi creare rapporti di una pagina da enormi quantità di dati. Questi vantaggi mettono in ombra alcuni degli svantaggi delle tabelle pivot, come la formattazione poco brillante e la necessità di convertire le tabelle pivot in valori per una personalizzazione aggiuntiva. Nella fig. La Figura 8 mostra un tipico processo per la creazione di una tabella pivot. In questo caso, tutto inizia con i dati iniziali. Creiamo una tabella pivot e utilizziamo tutte le tecniche possibili per personalizzarla e migliorarla. A volte convertiamo la tabella pivot in valori ed eseguiamo una formattazione finale.

La nuova metodologia di tabella pivot proposta da Rob Colley (sviluppatore di Microsoft) e discussa in seguito è il risultato di miglioramenti al processo sopra descritto. In questo caso, viene creata prima una tabella pivot primitiva. Non è necessario formattare questa tabella. Viene quindi seguito un processo in un unico passaggio, relativamente dispendioso in termini di tempo, per creare una shell ben formattata che ospiterà il report finale. Successivamente, la funzione GET DATA.PUMP.TABLE viene utilizzata per riempire rapidamente il report che si trova nella shell con i dati. Dopo aver ricevuto i nuovi dati, è possibile metterli sul foglio, aggiornare la tabella pivot primitiva e stampare il report che si trova nella shell (Figura 9). Questa tecnica ha una serie di vantaggi innegabili. Ad esempio, non devi preoccuparti di formattare il rapporto subito dopo averlo creato. Il processo di creazione delle tabelle pivot diventa quasi completamente automatizzato.

Le sezioni seguenti illustrano come creare un report dinamico che mostri le cifre effettive per i mesi passati e gli obiettivi per i mesi futuri.

Crea una tabella pivot primitiva.I dati iniziali (Fig. 10) sono presentati sotto forma di transazioni contenenti informazioni sugli indicatori pianificati ed effettivi per ciascuna regione in cui sono presenti filiali dell'azienda. I dati pianificati sono dettagliati a livello di mese, mentre i dati effettivi sono dettagliati a livello di singoli giorni. Gli indicatori pianificati vengono creati per l'anno successivo e gli indicatori effettivi vengono creati per i mesi passati. Poiché il rapporto verrà aggiornato ogni mese, questo processo risulta notevolmente semplificato se l'origine dati della tabella pivot aumenta di dimensioni man mano che vengono aggiunti nuovi dati in fondo. In versioni obsolete Creazione di Excel un'origine dati simile è stata implementata utilizzando un intervallo dinamico denominato utilizzando la funzione OFFSET (vedere i dettagli). Quando lavori in Excel 2013, seleziona semplicemente una delle celle di dati e premi la combinazione di tasti Ctrl + T (crea una tabella). Questo creerà un set di dati denominato che si espande automaticamente man mano che vengono aggiunte nuove righe e colonne.

Ora creiamo una tabella pivot. La funzione GET PIVOTTABLE DATA è sufficientemente potente, ma può restituire solo i valori visualizzati nella tabella pivot corrente. Questa funzione non è in grado di eseguire una scansione della cache per calcolare gli elementi che non sono nella tabella pivot.

Crea una tabella pivot:

Selezionare squadra Inserire → Tabella pivote poi nella finestra di dialogo Creazione di una tabella pivotclic ok.
Nell'elenco dei campi della tabella pivot selezionare un campo Data... Apparirà un elenco di date sul lato sinistro della tabella pivot (Fig. 11).
Seleziona qualsiasi cella della data, ad esempio A4. Nella scheda contestuale Analisisituato nella serie di schede contestuali Lavorare con le tabelle pivot, fare clic sul pulsante Raggruppamento per campo (vedi i dettagli). Nella finestra di dialogo Raggruppamento seleziona un'opzione Mesi (fig.12). Clic ok... I nomi dei mesi appariranno sul lato sinistro della tabella pivot (Fig.13).
Trascina il campo Data nell'area Colonne della tabella pivot.
Trascina il campo Indice all'area Colonne dell'elenco dei campi della tabella pivot.
Seleziona un campo Regioneda visualizzare nella colonna di sinistra della tabella pivot.
Seleziona un campo Redditoche viene visualizzato nell'area Valori tabella pivot.

Figura: 11. Inizia raggruppando per campo Data

A questo punto, la nostra tabella pivot sembra piuttosto primitiva (Figura 14). Non mi piacciono davvero le lettere Nomi delle linee e Nomi delle colonne... Non è pratico visualizzare i totali per Jan Plan e Jan Fact nella colonna D, ecc. Ma non preoccuparti aspetto questa tabella pivot, perché, tranne te, nessun altro la vedrà. Da questo punto in poi, creeremo un wrapper di report, la cui origine dati sarà la tabella pivot appena creata.

Creazione della shell di report.Inserisci un foglio bianco nella cartella di lavoro. Mettiamo da parte gli strumenti della tabella pivot per un po 'e passiamo ai normali strumenti di Excel. Il nostro compito è utilizzare formule e formattazioni per creare un bel report che non si vergogna di mostrare al gestore.

Procedere come segue (fig.15).

Nella cella A1, inserisci il nome del report - Indicatori pianificati ed effettivi per regione.
Vai alla scheda casa, fare clic sul pulsante Stili di cella seleziona il formato Intestazione 1.
Nella cella A2, inserisci la formula \u003d EON MONTHS (TODAY (); 0). Questa funzione restituisce l'ultimo giorno del mese corrente. Ad esempio, se leggi queste righe il 14 agosto 2014, la cella A2 visualizzerà la data 31 agosto 2014.
Seleziona la cella A2. Premere la combinazione di tasti Ctrl + 1 per visualizzare la finestra di dialogo Formato cella... Nella scheda Numero fare clic sull'elemento Tutti i formati... Immettere un formato numero personalizzato come "Dal mese" Indicatori pianificati MMMM "" (fig.16). Di conseguenza, la data calcolata apparirà come testo.
Nella cella A5 inserisci un titolo Regione.
Immettere le intestazioni delle regioni nelle celle rimanenti nella colonna A. Le intestazioni delle regioni devono corrispondere ai nomi delle regioni mostrati nella tabella pivot.
Aggiungi etichette alla colonna per i totali per reparto, se necessario.
In fondo al rapporto aggiungi la riga Totale per l'azienda.
Nella cella B4, inserisci la formula \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1). Questa formula restituisce le date 01/01/2014, 01/02/2104, ecc., I primi giorni di tutti i 12 mesi dell'anno corrente.
Seleziona la cella B4. Premere la combinazione di tasti Ctrl + 1 per aprire la finestra Formato cella... Nella scheda Numero nella sezione Tutti i formati immettere un formato numero personalizzato Mmm... Questo formato visualizza il nome del mese di tre lettere. Allinea il testo a destra della cella.
Copia il contenuto della cella B4 nell'intervallo C4: M4. Nella parte superiore della tabella pivot viene visualizzata una riga con i nomi dei mesi.
Nella cella B5, inserisci la formula \u003d IF (MONTH (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fatto, e per il presente e il futuro - Piano.
Aggiungi un titolo alla cella N5 Risultato... Alla cella O4 - Risultato, O5 - Piano, Р5 - % deviazione.
Immettere le solite formule di Excel utilizzate per calcolare i totali del reparto, la riga del totale dell'azienda, la colonna del totale generale e la colonna della varianza%:
1. nella cella B8, inserisci la formula \u003d SUM (B6: B7) e copiala in altre celle della riga;
2. nella cella N6, inserisci la formula \u003d SUM (B6: M6) e copiala in altre celle nella colonna;
3. nella cella P6, inserisci la formula \u003d SE.ERRORE ((N6 / O10) -1; 0) e copiala in altre celle della colonna;
4. nella cella B13, inserisci la formula \u003d SUM (B10: B12) e copiala in altre celle della riga;
5. nella cella B17 inserisci la formula \u003d SUM (B15: B16) e copialo in altre celle della riga;
6. nella cella B19 inserisci la formula \u003d SUM (B6: B18) / 2 e copiala nelle altre celle della riga.
Applicare lo stile Titolo 4 alle etichette nella colonna A e alle intestazioni nelle righe 4 e 5.
Per l'intervallo di celle B6: O19, seleziona il formato numerico # ## 0.
Per le celle nella colonna P, seleziona il formato numerico 0,0%.

Quindi, abbiamo completato la creazione della shell di report mostrata in Fig. 15. Questo rapporto include tutta la formattazione richiesta. La sezione successiva mostra come utilizzare la funzione GET PIVOTTABLE DATA per completare un report.

Figura: 15. Report wrapper prima di aggiungere le formule GET.DATA.PERSONAL.TABLES

Utilizzo della funzione GET PIVOTTABLE DATA per popolare la shell del report con i dati.D'ora in poi potrai sperimentare tutti i vantaggi dell'utilizzo della funzione GET PANEL.TABLE DATA. Se è stata deselezionata la casella di controllo abilitando questa funzione, tornare all'impostazione corrispondente e restituire la casella di controllo (vedere la descrizione in Fig. 6 o 7).

Seleziona la cella B6 della shell del report. Questa cella corrisponde alla regione nord-orientale e ai dati effettivi di gennaio.

Immettere \u003d (segno di uguale) per iniziare a inserire la formula.
Vai al foglio dalla tabella pivot e fai clic sulla cella che corrisponde alla regione nord-est e ai dati effettivi per gennaio - C12 (Fig.17).
Premi il tasto accedereper terminare di inserire la formula e tornare alla shell del report. Di conseguenza, Excel aggiungerà la funzione OTTIENI DATI PIVOTTABLE nella cella B6. La cella visualizzerà il valore di $ 277.435.

Ricorda questo numero, poiché sarà richiesto durante il confronto con i risultati della formula, che modificherai in seguito. La formula generata dal programma ha la seguente forma: \u003d GET.DATA.PERSONAL.TABLE ("Reddito"; 'Fig. 11-14 ′! $ A $ 3; "Regione"; "Nord-Est"; "Data" ; 1; "Indicatore"; "Fatto"). Se finora hai ignorato la funzione GET PIVOTTABLE DATA, è tempo di dare un'occhiata più da vicino. Nella fig. 18 questa formula viene mostrata in modalità di modifica insieme a un suggerimento.

Argomenti della funzione:

Campo dati. Campo dall'area Valori tabella pivot Nota: in questo caso viene utilizzato il campo Reddito, ma no Importo nel campo Reddito.
Tabella pivot. Con questo parametro, Microsoft ti chiede: "Quale tabella pivot desideri utilizzare?" È sufficiente specificare una delle celle della tabella pivot. La voce 'Fig. 11-14 '! $ A $ 3 si riferisce alla prima cella della tabella pivot in cui vengono inseriti i dati. Poiché nel nostro caso è possibile specificare qualsiasi cella relativa alla tabella pivot, lasciare invariato l'argomento. L'indirizzo di cella $ A $ 3 è adatto sotto tutti gli aspetti.
Campo 1; elemento 1. Nella formula generata automaticamente, il nome del campo è selezionato Regionee come valore del campo - Nordest... È qui che risiede la ragione dei problemi che sorgono quando si lavora con la funzione GET DATA.PUMP.TABLE. I valori selezionati automaticamente non possono essere copiati perché sono hardcoded. Pertanto, se si copiano le formule nell'intera area del report, sarà necessario modificarle manualmente. Sostituisci Northeast con un riferimento di cella nella forma $ A6. Posizionando un segno di dollaro davanti alla colonna A, si determina se la parte di riga del riferimento può essere modificata quando si copia la formula nelle celle della colonna.
Campo 2; elemento 2. Questa coppia di argomenti definisce il campo Data con un valore di 1. Se la tabella pivot originale era raggruppata in base al mese, il campo del mese mantiene il nome del campo originale Data... Il valore numerico del mese è 1, che corrisponde a gennaio. Non è consigliabile utilizzare un tale valore quando si creano formule enormi impostate in dozzine o addirittura centinaia di celle di report. Meglio utilizzare una formula che calcoli i valori dei campi Data, come la formula nella cella B4. Invece di 1 in questo caso, puoi usare la formula MESE (In $ 4). Il segno del dollaro che precede il 4 indica che la formula può assegnare valori al campo Data in base ad altri mesi poiché la formula viene copiata nelle celle della riga.
Campo 3; elemento 3. In questo caso, il nome del campo viene assegnato automaticamente Indice e il valore del campo Fatto... Questi valori sono corretti per gennaio, ma per i mesi successivi il valore del campo dovrà essere modificato in Plan. Modificare il valore hardcoded del campo Fatto al collegamento B $ 5.
Campo 4; Elemento 4. Questi argomenti non vengono utilizzati perché i campi sono finiti.

La nuova formula è mostrata in Fig. 19. In un minuto, invece di una formula hardcoded progettata per funzionare con un singolo valore, è stata creata una formula flessibile che può essere copiata in tutte le celle del set di dati. Premi il tasto accederee otterrai lo stesso risultato di prima di modificare la formula. La formula modificata assume la forma seguente: \u003d GET.PUMP.TABLE DATA ("Income"; 'Fig. 11-14 ′! $ A $ 3; "Regione"; $ A6; "Data"; MESE (B $ 4) ; "Indicatore"; B $ 5)

Figura: 19. Al termine della modifica, la formula GET.DATA.PERSONAL.TABLE è adatta per la copia in tutte le celle dell'intervallo

Copia la formula in qualsiasi cella vuota nelle colonne B: M in cui vengono calcolati i risultati. Ora che il report contiene numeri reali, è possibile apportare le modifiche finali alle larghezze delle colonne.

Nella fase successiva, imposteremo la formula GET.PUMP.TABLE per calcolare gli obiettivi finali. Se copi semplicemente la formula nella cella O6, il messaggio di errore #REF! La ragione di questo errore è che la parola Risultato nella cella O4 non è il nome del mese. Per garantire il corretto funzionamento della funzione GET PIVOTTABLE DATA, il valore richiesto deve essere nella tabella pivot. Ma poiché nella tabella pivot originale, il campo Indice è il secondo campo nell'area della colonna, la colonna dei dati Riepilogo del piano effettivamente assente. Sposta il campo Indice in modo che diventi il \u200b\u200bprimo nell'area della colonna (Figura 20).

Figura: 20. Regolare il layout dei campi nell'area della colonna in modo che venga visualizzata la colonna Piano di massima

Confronta con la fig. 14. Lì, nell'area della COLONNA, il campo era il primo Data, che ha portato al fatto che inizialmente le colonne erano raggruppate per data, e all'interno di ogni mese per piano / fatto. Ora il primo è il campo Indicatore e nel riepilogo le colonne sono le prime Piano, ordinati all'interno per mese e poi per tutte le colonne Fatto.

Ritornando al foglio della busta del report, posizionarsi nella cella O6, digitare \u003d (segno di uguale) e fare riferimento alla cella N12 sul foglio della tabella pivot, corrispondente ai risultati pianificati della regione Nordest. Clicca su accedere... Ottieni la formula \u003d GET.DATA.PERSONAL.TABLES ("Reddito"; 'Fig. 11-14'! $ A $ 3; "Regione"; "Nord-Est"; "Indicatore"; "Piano"). Modificalo: \u003d GET. DATA.SUMMARY.TABLES ("Reddito"; 'Fig. 11-14 ′! $ A $ 3; "Regione"; $ A6; "Indicatore"; O $ 5). Copia questa formula in altre celle nella colonna O (Figura 21). Notare che anche se si spostano aree diverse del rapporto di tabella pivot, la shell funziona correttamente. Ovviamente, se rendi inattivi alcuni campi pivot, la shell non farà fronte a questo ...

Figura: 21. Rapporto finale che può essere presentato al manager

Si dispone ora di un wrapper di report ben formattato che utilizza i valori di una tabella pivot dinamica. Sebbene la creazione iniziale del rapporto abbia richiesto molto tempo, l'aggiornamento ha richiesto solo pochi minuti.

Aggiorna il rapporto.Per aggiornare il rapporto con i dati per i mesi futuri, segui questi passaggi.

Inserisci le cifre effettive sotto il set di dati originale. Poiché i dati di origine sono in formato tabella, la formattazione della tabella viene automaticamente propagata alle nuove righe di dati. Espande anche la definizione della tabella pivot originale (nel file Excel ho già aggiunto le cifre effettive per l'intero anno).
Vai alla tabella pivot. Fare clic con il tasto destro e selezionare ricaricare... La tabella pivot cambierà, ma va bene.
Vai alla shell del rapporto. In linea di principio, è già stato fatto tutto per aggiornare il report, ma non fa male testare i risultati. Cambia la formula nella cella A2, ad esempio, in questo: \u003d EON MONTHS (TODAY () +31 ; 0) e guarda cosa succede.

Aggiungendo nuovi dati sulle vendite effettive ogni mese, non devi preoccuparti di ricreare formati, formule, ecc. Il processo descritto di aggiornamento del rapporto è così semplice che dimenticherai per sempre i problemi sorti durante la preparazione dei rapporti mensili. L'unico problema può sorgere se l'azienda viene riorganizzata, a seguito della quale nuove regioni potrebbero apparire nella tabella pivot. Per assicurarti che le formule funzionino correttamente, verifica che il totale complessivo nel rapporto corrisponda al totale nella tabella pivot. Quando viene visualizzata una nuova regione, aggiungila al foglio avvolto e trascina le formule appropriate.

Non pensavo che avrei mai detto quanto segue: “La funzione GET.DATA.PERSONAL.TABLE è la più grande benedizione. Come abbiamo fatto a esistere senza di lei prima? "

Nell'originale, i dati iniziali di Jelena erano disposti in modo che ulteriori formule funzionassero correttamente solo a luglio 2015. Nel file Excel allegato a questa nota, ho modificato i dati originali, così come alcune formule in modo che tutto funzionasse, indipendentemente dalla data in cui sperimenterai con il file Excel allegato. Sfortunatamente, le formule dovevano essere complicate.

In precedenza ho già parlato del fatto che quando si fa riferimento a una cella di una tabella pivot, invece di un normale collegamento, viene restituita la funzione GET.PIVOTTABLE DATA (vedi). Se sei interessato a come per ovviare a questo inconveniente, consiglio di fare riferimento alla nota citata. Se ti stai chiedendo, perché è così che avviene, e anche quali sono gli aspetti positivi della funzione RICEVI I DATI DEL PANNELLO.TABELLA, quindi suggerisco un frammento del libro Jelen, Alexander. (capitolo 15). La tecnica in esame permetterà di far fronte a molti problemi che causano mal di testa agli utenti di tabelle pivot, in particolare:

Quando la tabella pivot viene aggiornata, la formattazione applicata in precedenza scompare. I formati dei numeri vengono persi. I risultati della regolazione della larghezza delle colonne scompaiono.
Non esiste un modo semplice per creare una tabella pivot asimmetrica. L'unica opzione è utilizzare set denominati, ma questo metodo è disponibile solo per coloro che utilizzano tabelle pivot del modello di dati e non normali tabelle pivot.
Excel non riesce a ricordare i modelli. Se è necessario ricreare le tabelle pivot più e più volte, sarà necessario raggruppare nuovamente, applicare campi e membri calcolati ed eseguire una serie di altre attività simili.

Scarica una nota in formato o, esempi in formato

Bene, iniziamo in ordine.

Come abbandonare la problematica funzione GET DATA.PERSONAL TABLES

Supponiamo nella tabella pivot mostrata in Fig. 1, è necessario confrontare i dati per il 2015 e il 2014.

Figura: 1. Tabella pivot originale

Aggiungi l'intestazione "% Altezza" alla cella D3.
Copia il formato dalla cella C3 alla cella D3.
Immettere un segno di uguale nella cella D4.
Fare clic sulla cella C4.
Immettere il segno / (barra in avanti) per rappresentare l'operazione di divisione.
Fare clic sulla cella B4.
Immettere -1 e premere la combinazione di tasti rimanere nella stessa cella. Formatta il risultato come percentuale. Vedrai che la regione occidentale ha registrato un calo del 43,8% del reddito (Figura 2). Risultati non molto buoni.
Quando hai finito di inserire la tua prima formula, seleziona la cella D4.
Fare doppio clic sul quadratino situato nell'angolo inferiore destro della cella. Questo quadrato rappresenta un quadratino di riempimento che puoi utilizzare per copiare una formula per riempire un'intera colonna nel report.

Dopo aver finito di copiare la formula, guardando lo schermo, ti accorgerai che qualcosa non va: ogni regione ha mostrato un calo del 43,8% per l'anno (Fig. 3).

Figura: 3. Quando finisci di copiare la formula in tutte le celle della colonna, vedrai che ogni regione ha mostrato un calo del 43,8%

Niente di simile è accaduto ai tempi di Excel 2000. Quando ho iniziato a incontrare regolarmente la funzione GETDATA.PERSONAL.TABLES, la odiavo. Quando in uno dei seminari qualcuno mi ha chiesto come potesse essere utilizzato per il bene della causa, sono rimasto sbalordito. Non mi sono mai posto una domanda del genere! A mio parere, e secondo l'opinione della maggior parte degli utenti di Excel, la funzione GET.DATA.PUMP.TABLE era un prodotto del male che non aveva nulla a che fare con le forze del bene. Fortunatamente, ci sono due modi per disabilitare questa funzione.

Vai alla cella D4 e digita \u003d (segno di uguale).
Immettere C4.
Immettere una / (barra in avanti per la divisione).
Immettere B4.
Immettere -1.
Clicca su accedere.

Ora hai creato una normale formula di Excel che puoi copiare nelle celle sotto la colonna e con la quale puoi ottenere i risultati corretti (Figura 5). Come puoi vedere, puoi creare formule in aree esterne alla tabella pivot che fanno riferimento ai dati all'interno della tabella pivot. E coloro che non credono che ciò sia possibile, lascia che eseguano le azioni descritte da soli.

Figura: 5. Basta inserire \u003d C4 / B4-1 dalla tastiera e la formula funzionerà come necessario

Alcuni utenti si sentiranno a disagio con il solito ordine di immissione delle formule. Inoltre, l'opzione proposta è più laboriosa. Se sei uno di questi utenti, il secondo modo è per te ...

Utilizzo di GET PIVOTTABLE DATA per migliorare le tabelle pivot

Le sezioni seguenti illustrano come creare un report dinamico che mostri le cifre effettive per i mesi passati e gli obiettivi per i mesi futuri.

Crea una tabella pivot primitiva.I dati iniziali (Fig. 10) sono presentati sotto forma di transazioni contenenti informazioni sugli indicatori pianificati ed effettivi per ciascuna regione in cui sono presenti filiali dell'azienda. Le cifre pianificate sono dettagliate a livello di mese e le cifre effettive a livello di singoli giorni. Gli indicatori pianificati vengono creati per l'anno successivo e gli indicatori effettivi vengono creati per i mesi passati. Poiché il rapporto verrà aggiornato ogni mese, questo processo risulta notevolmente semplificato se l'origine dati della tabella pivot aumenta di dimensioni man mano che vengono aggiunti nuovi dati in fondo. Nelle versioni precedenti di Excel, tale origine dati è stata creata utilizzando un intervallo dinamico denominato utilizzando la funzione OFFSET (vedere i dettagli). Quando lavori in Excel 2013, seleziona semplicemente una delle celle di dati e premi la combinazione di tasti Ctrl + T (crea una tabella). Questo creerà un set di dati denominato che si espande automaticamente man mano che vengono aggiunte nuove righe e colonne.

Crea una tabella pivot:

Selezionare squadra Inserire → Tabella pivote poi nella finestra di dialogo Creazione di una tabella pivotclic ok.
Nell'elenco dei campi della tabella pivot selezionare un campo Data... Apparirà un elenco di date sul lato sinistro della tabella pivot (Fig. 11).
Seleziona qualsiasi cella della data, ad esempio A4. Nella scheda contestuale Analisisituato nella serie di schede contestuali Lavorare con le tabelle pivot, fare clic sul pulsante Raggruppamento per campo (vedi i dettagli). Nella finestra di dialogo Raggruppamento seleziona un'opzione Mesi (fig.12). Clic ok... I nomi dei mesi appariranno sul lato sinistro della tabella pivot (Fig.13).
Trascina il campo Data nell'area Colonne della tabella pivot.
Trascina il campo Indice all'area Colonne dell'elenco dei campi della tabella pivot.
Seleziona un campo Regioneda visualizzare nella colonna di sinistra della tabella pivot.
Seleziona un campo Redditoche viene visualizzato nell'area Valori tabella pivot.

Figura: 11. Inizia raggruppando per campo Data

A questo punto, la nostra tabella pivot sembra piuttosto primitiva (Figura 14). Non mi piacciono davvero le lettere Nomi delle linee e Nomi delle colonne... Non è pratico visualizzare i totali per Jan Plan e Jan Fact nella colonna D, ecc. Ma non preoccuparti dell'aspetto di questa tabella pivot, perché nessun altro la vedrà tranne te. Da questo punto in poi, creeremo un wrapper di report, la cui origine dati sarà la tabella pivot appena creata.

Procedere come segue (fig.15).

Nella cella A1, inserisci il nome del report - Indicatori pianificati ed effettivi per regione.
Vai alla scheda casa, fare clic sul pulsante Stili di cella seleziona il formato Intestazione 1.
Nella cella A2, inserisci la formula \u003d EON MONTHS (TODAY (); 0). Questa funzione restituisce l'ultimo giorno del mese corrente. Ad esempio, se leggi queste righe il 14 agosto 2014, la cella A2 visualizzerà la data 31 agosto 2014.
Seleziona la cella A2. Premere la combinazione di tasti Ctrl + 1 per visualizzare la finestra di dialogo Formato cella... Nella scheda Numero fare clic sull'elemento Tutti i formati... Immettere un formato numero personalizzato come "Dal mese" Indicatori pianificati MMMM "" (fig.16). Di conseguenza, la data calcolata apparirà come testo.
Nella cella A5 inserisci un titolo Regione.
Immettere le intestazioni delle regioni nelle celle rimanenti nella colonna A. Le intestazioni delle regioni devono corrispondere ai nomi delle regioni mostrati nella tabella pivot.
Aggiungi etichette alla colonna per i totali per reparto, se necessario.
In fondo al rapporto aggiungi la riga Totale per l'azienda.
Nella cella B4, inserisci la formula \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1). Questa formula restituisce le date 01/01/2014, 01/02/2104, ecc., I primi giorni di tutti i 12 mesi dell'anno corrente.
Seleziona la cella B4. Premere la combinazione di tasti Ctrl + 1 per aprire la finestra Formato cella... Nella scheda Numero nella sezione Tutti i formati immettere un formato numero personalizzato Mmm... Questo formato visualizza il nome del mese di tre lettere. Allinea il testo a destra della cella.
Copia il contenuto della cella B4 nell'intervallo C4: M4. Nella parte superiore della tabella pivot viene visualizzata una riga con i nomi dei mesi.
Nella cella B5, inserisci la formula \u003d IF (MONTH (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fatto, e per il presente e il futuro - Piano.
Aggiungi un titolo alla cella N5 Risultato... Alla cella O4 - Risultato, O5 - Piano, Р5 - % deviazione.
Immettere le solite formule di Excel utilizzate per calcolare i totali del reparto, la riga del totale dell'azienda, la colonna del totale generale e la colonna della varianza%:
1. nella cella B8, inserisci la formula \u003d SUM (B6: B7) e copiala in altre celle della riga;
2. nella cella N6, inserisci la formula \u003d SUM (B6: M6) e copiala in altre celle nella colonna;
3. nella cella P6, inserisci la formula \u003d SE.ERRORE ((N6 / O10) -1; 0) e copiala in altre celle della colonna;
4. nella cella B13, inserisci la formula \u003d SUM (B10: B12) e copiala in altre celle della riga;
5. nella cella B17 inserisci la formula \u003d SUM (B15: B16) e copialo in altre celle della riga;
6. nella cella B19 inserisci la formula \u003d SUM (B6: B18) / 2 e copiala nelle altre celle della riga.
Applicare lo stile Titolo 4 alle etichette nella colonna A e alle intestazioni nelle righe 4 e 5.
Per l'intervallo di celle B6: O19, seleziona il formato numerico # ## 0.
Per le celle nella colonna P, seleziona il formato numerico 0,0%.

Figura: 15. Report wrapper prima di aggiungere le formule GET.DATA.PERSONAL.TABLES

Seleziona la cella B6 della shell del report. Questa cella corrisponde alla regione nord-orientale e ai dati effettivi di gennaio.

Immettere \u003d (segno di uguale) per iniziare a inserire la formula.
Vai al foglio dalla tabella pivot e fai clic sulla cella che corrisponde alla regione nord-est e ai dati effettivi per gennaio - C12 (Fig.17).
Premi il tasto accedereper terminare di inserire la formula e tornare alla shell del report. Di conseguenza, Excel aggiungerà la funzione OTTIENI DATI PIVOTTABLE nella cella B6. La cella visualizzerà il valore di $ 277.435.

Argomenti della funzione:

Campo dati. Campo dall'area Valori tabella pivot Nota: in questo caso viene utilizzato il campo Reddito, ma no Importo nel campo Reddito.
Tabella pivot. Con questo parametro, Microsoft ti chiede: "Quale tabella pivot desideri utilizzare?" È sufficiente specificare una delle celle della tabella pivot. La voce 'Fig. 11-14 '! $ A $ 3 si riferisce alla prima cella della tabella pivot in cui vengono inseriti i dati. Poiché nel nostro caso è possibile specificare qualsiasi cella relativa alla tabella pivot, lasciare invariato l'argomento. L'indirizzo di cella $ A $ 3 è adatto sotto tutti gli aspetti.
Campo 1; elemento 1. Nella formula generata automaticamente, il nome del campo è selezionato Regionee come valore del campo - Nordest... È qui che risiede la ragione dei problemi che sorgono quando si lavora con la funzione GET DATA.PUMP.TABLE. I valori selezionati automaticamente non possono essere copiati perché sono hardcoded. Pertanto, se si copiano le formule nell'intera area del report, sarà necessario modificarle manualmente. Sostituisci Northeast con un riferimento di cella nella forma $ A6. Posizionando un segno di dollaro davanti alla colonna A, si determina se la parte di riga del riferimento può essere modificata quando si copia la formula nelle celle della colonna.
Campo 2; elemento 2. Questa coppia di argomenti definisce il campo Data con un valore di 1. Se la tabella pivot originale era raggruppata in base al mese, il campo del mese mantiene il nome del campo originale Data... Il valore numerico del mese è 1, che corrisponde a gennaio. Non è consigliabile utilizzare un tale valore quando si creano formule enormi impostate in dozzine o addirittura centinaia di celle di report. Meglio utilizzare una formula che calcoli i valori dei campi Data, come la formula nella cella B4. Invece di 1 in questo caso, puoi usare la formula MESE (In $ 4). Il segno del dollaro che precede il 4 indica che la formula può assegnare valori al campo Data in base ad altri mesi poiché la formula viene copiata nelle celle della riga.
Campo 3; elemento 3. In questo caso, il nome del campo viene assegnato automaticamente Indice e il valore del campo Fatto... Questi valori sono corretti per gennaio, ma per i mesi successivi il valore del campo dovrà essere modificato in Plan. Modificare il valore hardcoded del campo Fatto al collegamento B $ 5.
Campo 4; Elemento 4. Questi argomenti non vengono utilizzati perché i campi sono finiti.

Figura: 19. Al termine della modifica, la formula GET.DATA.PERSONAL.TABLE è adatta per la copia in tutte le celle dell'intervallo

Figura: 20. Regolare il layout dei campi nell'area della colonna in modo che venga visualizzata la colonna Piano di massima

Figura: 21. Rapporto finale che può essere presentato al manager

Aggiorna il rapporto.Per aggiornare il rapporto con i dati per i mesi futuri, segui questi passaggi.

Inserisci le cifre effettive sotto il set di dati originale. Poiché i dati di origine sono in formato tabella, la formattazione della tabella viene automaticamente propagata alle nuove righe di dati. Espande anche la definizione della tabella pivot originale (nel file Excel ho già aggiunto le cifre effettive per l'intero anno).
Vai alla tabella pivot. Fare clic con il tasto destro e selezionare ricaricare... La tabella pivot cambierà, ma va bene.
Vai alla shell del rapporto. In linea di principio, è già stato fatto tutto per aggiornare il report, ma non fa male testare i risultati. Cambia la formula nella cella A2, ad esempio, in questo: \u003d EON MONTHS (TODAY () +31 ; 0) e guarda cosa succede.

Non pensavo che avrei mai detto quanto segue: “La funzione GET.DATA.PERSONAL.TABLE è la più grande benedizione. Come abbiamo fatto a esistere senza di lei prima? "

Per le tabelle pivot, la funzione GET.DATA.PIVOTTABLE è una funzione che restituisce i dati archiviati nel rapporto tabella pivot.

Per accedere rapidamente alla funzione, è necessario inserire un segno di uguale nella cella (\u003d) e selezionare la cella richiesta nella tabella pivot. Excel genererà automaticamente la funzione GET PIVOTTABLE DATA.

Disabilita la creazione di GetPivotData

Per disattivare la generazione automatica della funzione GET PIVOTTABLE, seleziona una cella qualsiasi nella tabella pivot, fai clic sulla scheda Lavorare con le tabelle pivot -\u003e Opzionial gruppo Tabella pivot.Fare clic sulla freccia in giù accanto alla scheda Opzioni.Nel menu a discesa, deseleziona la casella Creare unGetPivotData.

Utilizzo dei riferimenti di cella nella funzione GET DATA.PUMP.TABLE

Invece di specificare i nomi degli elementi o dei campi nella funzione GET.DATA.PUMP.TABLE, puoi fare riferimento alle celle del foglio di lavoro. Nell'esempio seguente, la cella E3 contiene il nome del prodotto e la formula nella cella E4 fa riferimento ad esso. Di conseguenza, verrà restituito il volume totale per le torte.

Utilizzo dei riferimenti ai campi della tabella pivot

Non ci sono domande su come funzionano i collegamenti agli elementi della tabella pivot; sorgono problemi se vogliamo fare riferimento a un campo dati.

Nell'esempio, la cella E3 contiene il nome del campo dati "Quantità" e sarebbe opportuno fare riferimento a questa cella nella funzione, invece di avere il nome del campo nella formula GET.DATA.PUMP.TABLE.

Tuttavia, se cambiamo il primo argomento campo datial riferimento alla cella E3, Excel ci restituirà l'errore #REF!

GET.PERSONAL.TABLE DATA (E3; $ A $ 3)

Il problema viene risolto semplicemente aggiungendo una stringa vuota ("") all'inizio o alla fine del riferimento di cella.

OTTIENI DATI.PERSONAL.TABLE (E3 & ""; $ A $ 3)

Una semplice correzione alla formula restituirà il valore corretto.

Utilizzo delle date nella funzione GET DATA.PUMP.TABLE

Se si utilizzano le date nella funzione GET PIVOTTABLE DATA, potrebbero verificarsi problemi anche se la data viene visualizzata nella tabella pivot. Ad esempio, la formula seguente è la data "21/04/2013" e la tabella pivot contiene un campo con le date di vendita. Tuttavia, la formula nella cella E4 restituisce un errore.

GET.DATA.SUMMARY.TABLE ("Volume"; $ A $ 3; "Data"; "21/04/2013")

Per evitare errori relativi alle date, puoi utilizzare uno dei seguenti metodi:

Confronta i formati di data nella formula e nella tabella pivot
Usa la funzione DATEVALUE
Usa la funzione DATA
Fare riferimento alla cella con la data corretta

Confronta i formati di data nella formula e nella tabella pivot.

Per ottenere il risultato corretto, quando si utilizza la funzione GET PIVOTTABLE DATA, assicurarsi che i formati della data dell'argomento della formula e della tabella pivot siano gli stessi.

Nella cella E4, la formula utilizza il formato della data "GG.MM.AAAA" e, di conseguenza, sono state restituite le informazioni corrette.

Utilizzando la funzione DATEVALUE

Invece di inserire manualmente la data nella formula, puoi aggiungere la funzione DATEVALUE per restituire la data.

Nella cella E4, la data viene immessa utilizzando la funzione DATA.VALORE ed Excel restituisce le informazioni richieste.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Data"; DATEVALUE ("04.21.2013"))

Utilizzo della funzione DATA

Invece di inserire manualmente la data nella formula, è possibile utilizzare la funzione DATA, che restituirà correttamente le informazioni necessarie.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Data"; DATE (2013; 4; 21))

Riferimento cella data

Invece di inserire manualmente una data in una formula, puoi fare riferimento a una cella che contiene una data (in qualsiasi formato in cui Excel interpreta i dati come date). Nell'esempio nella cella E4, la formula si riferisce alla cella E3 ed Excel restituisce i dati corretti.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Data"; E3)