Come individuare e modificare i dati di origine per una tabella pivot di Excel. Per i dati di origine mancanti, passaggi per provare a ricrearli. Modifica le opzioni per salvare i dati di origine con il file della tabella pivot.

NOTA: per le macro che aiutano a gestire i dati di origine, vai alla pagina delle macro dei dati di origine della tabella pivot. Affinché una macro annulli il pivot dei dati di origine, vai alla pagina Unpivot Excel Data Macro

Individua e correggi i dati di origine

In questo video vedrai come individuare la tabella pivot origine dati, quindi controlla l’origine dati per assicurarti che includa tutte le righe e le colonne di cui hai bisogno. Se necessario, modifica l’origine dati per includere nuove righe o colonne.

NOTA: per un lungo soluzione del termine, utilizza una tabella Excel denominata, come origine dati. Verrà regolata automaticamente se vengono aggiunte nuove righe.

Le istruzioni scritte sono sotto il video e puoi scaricare il file di esempio da seguire con il video.

Individua i dati di origine

Dopo aver creato una tabella pivot, potresti voler aggiungere nuovi record nei dati di origine o modificare i record esistenti. In un cartella di lavoro di grandi dimensioni, può essere difficile individuare l’origine esatta della tabella pivot, se sono presenti più tabelle o elenchi.

NOTA: se hai acquistato il mio componente aggiuntivo Pivot Power Premium, fai clic su Tabella pivot Informazioni, quindi fare clic su Vai a Dati di origine.

Procedi nel seguente modo per trovare i dati di origine per una tabella pivot:

1. Seleziona qualsiasi cella nella tabella pivot.
2. Sulla barra multifunzione, nella scheda Strumenti tabella pivot, fare clic sulla scheda Analizza (in Excel 2010, fare clic sulla scheda Opzioni).
3. Nel gruppo Dati, fare clic sulla sezione superiore del comando Modifica origine dati.

Si apre la finestra di dialogo Cambia origine dati tabella pivot e puoi vedere la tabella o l’intervallo di origine in la casella Tabella / Intervallo. Può essere un riferimento a un foglio e a un intervallo di celle, ad esempio

• Ordini! $ A $ 1: $ H $ 9

o il nome di una tabella, come

• Sales_East

Dietro la finestra di dialogo, puoi vedere l’intervallo di origine nel foglio di lavoro, circondato da un bordo mobile.

Nella schermata seguente, l’intervallo di dati di origine termina alla riga 9 e un nuovo record è stato aggiunto nella riga 10. Tale record non verrà visualizzato nella tabella pivot, a meno che l’origine dati non venga modificata.

Modifica l’intervallo di dati di origine

Se i dati di origine di una tabella pivot sono un riferimento statico a un foglio e un intervallo specifici, non si regola automaticamente quando vengono aggiunti nuovi dati. Nella schermata sopra, l’intervallo dell’origine dati si riferisce agli ordini! $ A $ 1: $ H $ 9 e può essere regolato manualmente per includere la riga 10.

Per regolare manualmente l’intervallo della sorgente statica:

Puoi regolare l’intervallo della sorgente quando necessario, in modo che includa nuovi dati, ma una soluzione migliore è creare una sorgente dinamica e basare la tabella pivot su questo. Le istruzioni sono riportate di seguito.

Modifica i dati di origine

Occasionalmente, potrebbe essere necessario modificare una tabella pivot, in modo che utilizzi un’origine dati diversa. In questo esempio, è stata creata una nuova tabella, con solo i dati per l’East r egion. La tabella pivot verrà modificata per utilizzare quella sorgente, quindi potrà essere inviata a un gestore nella regione orientale, senza rivelare i dati della regione occidentale.

NOTA: dopo essere passati a una tabella di origine diversa , potresti dover rinominare alcuni campi o aggiungere campi diversi alla tabella pivot.

Per modificare i dati di origine per una tabella pivot di Excel, segui questi passaggi:

1. Selezionare una cella nella tabella pivot.
2. Sulla barra multifunzione, nella scheda Strumenti tabella pivot, fare clic sulla scheda Analizza (in Excel 2010, fare clic sulla scheda Opzioni).
3. Nella scheda Dati gruppo, fare clic sulla sezione superiore del comando Modifica origine dati.
4. Si apre la finestra di dialogo Modifica origine dati tabella pivot e si può vedere la tabella o l’intervallo di origine nella casella Tabella / intervallo. In questa schermata di seguito, l’origine dati corrente è la tabella Sales_Data, che contiene i dati di entrambe le regioni ..

   

5. Elimina l’origine dati esistente e digita un nuovo nome per la tabella o seleziona una nuova origine dati e gamma. La tabella / intervallo viene modificata in Sales_East, dove sono elencati solo i dati East.

   

6. Fai clic su OK.

Sorgente dinamica per tabella pivot

Invece di utilizzare un intervallo statico come origine dati della tabella pivot, una soluzione migliore è creare un intervallo dinamico, che si adatterà automaticamente alle dimensioni.

Crea sorgente dinamica – Tabella Excel

Sorgente dinamica – Intervallo denominato

Sorgente dinamica – Array dinamico

Utilizza una sorgente dinamica

Sorgente dinamica – Tabella Excel

Invece di utilizzare un intervallo statico come origine dati della tabella pivot, una soluzione migliore è creare un intervallo dinamico, basato su una tabella Excel denominata. Ci sono istruzioni scritte qui, o guarda il video qui sotto.

Prendi nota del nome della tabella, in modo da poterlo utilizzare come sorgente della tabella pivot

Sorgente dinamica – Intervallo denominato

Se non puoi utilizzare una tabella Excel come dati di origine, crei un intervallo denominato dinamico, basato su una formula INDICE o OFFSET. Ci sono istruzioni scritte qui o guarda il video di seguito.

Il video mostra come creare un intervallo dinamico con la funzione OFFSET, in Excel 2007, e i passaggi sono simili nelle versioni successive.

NOTA: ricorda il nome dell’intervallo, in modo da poterlo utilizzare come origine della tabella pivot

Dynamic Source – Dynamic Array

Se disponi di una versione di Excel che supporta le nuove funzioni, come SORT o UNIQUE, puoi utilizzare tali funzioni per creare array dinamici. Questa tecnica utilizza solo formule, nessuna macro .

Nella sezione Download, ottieni il file di esempio Filtered Source Data. Mostra come impostare un intervallo denominato con solo le righe visibili da una tabella Excel denominata.

Ecco il filtro d dati, su un foglio diverso, con solo 2 ripetizioni e 3 categorie dalle righe visibili.

Quindi, puoi creare una tabella pivot basata solo su quei dati filtrati.

Usa sorgente dinamica per tabella pivot

Dopo aver creato una sorgente dinamica che contiene la tabella pivot dati, segui questi passaggi per utilizzare i dati di origine:

1. Seleziona una cella qualsiasi nella tabella pivot.
2. Sulla barra multifunzione, nella scheda Strumenti tabella pivot, fai clic su Analizza scheda (in Excel 2010, fare clic sulla scheda Opzioni).
3. Nel gruppo Dati, fare clic sulla sezione superiore del comando Modifica origine dati.
4. Nella finestra di dialogo Modifica origine dati tabella pivot, deselezionare la casella Tabella / intervallo
5. Nella casella Tabella / intervallo, digitare il nome della tabella o dell’intervallo denominato creato. In questo esempio, l’origine dinamica è una tabella denominata – tblOrders.
6. Fai clic su OK

Salva dati di origine con tabella pivot

Quando crei una tabella pivot nella cartella di lavoro, i record di dati di origine vengono salvati in un’area di memoria speciale, una cache pivot. Quindi, quando chiudi il file, Excel può salvare i dati di origine in questa cache pivot o cancellare quella memoria.

Ci sono vantaggi e svantaggi per entrambe le opzioni:

Se salvi i dati di origine:

• Il file sarà più grande
• Il file potrebbe aprirsi più rapidamente

Se non salvi i dati di origine:

• Il riempimento sarà più piccolo
• Il file potrebbe aprirsi lentamente mentre la cache pivot viene ricostruita
• La tabella pivot deve essere aggiornata dopo aver aperto il file

Come modificare l’impostazione

Per attivare il salvataggio l’attivazione o la disattivazione dell’impostazione Dati di origine:

• Fare clic con il pulsante destro del mouse su una cella nella tabella pivot e fare clic su Opzioni tabella pivot.
• Nella scheda Dati, nella sezione Dati tabella pivot , aggiungi o rimuovi il segno di spunta da Salva dati sorgente con file
• Fai clic su OK.

Aggiorna dati all’apertura

Se scegli di attivare dall’opzione Salva dati di origine con file, è necessario attivare l’opzione Aggiorna dati all’apertura del file. Questa impostazione è appena sotto la casella di controllo Salva dati di origine.

In caso contrario, verrà visualizzato un messaggio quando si tenta di filtrare i dati o di apportare altre modifiche al layout.

• “Il rapporto di tabella pivot è stato salvato senza i dati sottostanti. Utilizza il comando Aggiorna dati per aggiornare il rapporto. “

Se vedi quel messaggio, fai clic su OK, quindi aggiorna manualmente la tabella pivot.

E per evitare il fastidioso messaggio, attiva l’opzione Aggiorna dati all’apertura del file.

Ricrea i dati di origine della tabella pivot

Se elimini accidentalmente il foglio di lavoro che contiene i dati di origine per la tabella pivot, o se hai ricevuto un file senza i dati della tabella pivot, potresti essere in grado di utilizzare la funzione Mostra dettagli della tabella pivot per ricrearlo.

NOTA: non funzionerà per tutte le tabelle pivot, ma è vale la pena provare, per recuperare i dati di origine.

Per provare a ricreare i dati di origine per una tabella pivot, segui questi passaggi per utilizzare la funzione Mostra dettagli:

1. Assicurati che nessuno degli elementi nei campi della tabella pivot sia nascosto: cancella tutti i filtri e i filtri dei dati applicati.
2. La tabella pivot non deve contenere tutti i campi, assicurati solo che è almeno un campo in Val ues area.
3. Mostra i totali complessivi per righe e colonne. Se i totali non sono visibili, selezionare una cella nella tabella pivot e sulla barra multifunzione, in Strumenti tabella pivot, fare clic sulla scheda Analizza. Nel gruppo Layout, fare clic su Totali complessivi, quindi fare clic su Attivo per Righe e colonne.
4. Fai doppio clic sulla cella del totale generale in basso a destra della tabella pivot. In questo modo dovresti creare un nuovo foglio con i record correlati dai dati di origine originali.

Correggi i dati estratti Dati

Se si ripristinano i dati di origine, potrebbe essere necessario apportare alcune modifiche aggiuntive per ripristinarli alla loro condizione originale.

• Rinomina la tabella, utilizzando il nome dei dati di origine originale, se noto.
• Se i dati di origine originali contenevano formule, dovrai ricrearli, perché la funzione Mostra dettagli esporta solo i dati.
• Le colonne nei dati estratti saranno nello stesso ordine in cui erano nei dati di origine originali.
• I dati estratti saranno formattato con il formato Tabella predefinito della cartella di lavoro. Puoi applicare un formato di tabella diverso o una formattazione personalizzata.
• Nota: se hai apportato modifiche ai dati di origine e non hai aggiornato la tabella pivot, tali modifiche non saranno nei dati estratti.

Connetti ai dati estratti

Se rinomini la tabella che è stata creata durante il processo Mostra dettagli e utilizzi lo stesso nome della tabella che originariamente conteneva la fonte dati, la tabella pivot potrebbe connettersi automaticamente ai nuovi dati di origine.

In caso contrario, puoi connetterti ai dati di origine ricreati – segui i passaggi nella sezione precedente: Usa sorgente dinamica per tabella pivot – usa il nome della tabella che hai assegnato alla nuova tabella.

Scarica il file di esempio

Esempio di sorgente pivot: per seguire i tutorial, puoi scaricare il file DateAmt.zip. Il file zippato è in formato xlsx e non contiene macro.

Esempio di dati di origine filtrati: questo esempio è per le versioni di Excel che hanno le nuove funzioni, come SORT e UNIQUE. Dow Carica questo file di esempio per vedere una tabella pivot creata solo dalle righe visibili, in una tabella Excel filtrata. Il file zippato è in formato xlsx e non contiene macro.

Strumenti per tabelle pivot

Per risparmiare tempo durante la creazione, la formattazione e la modifica delle tabelle pivot, utilizza gli strumenti nel mio Pivot Componente aggiuntivo Power Premium. Con pochi clic puoi:

• copiare la formattazione da una tabella pivot e applicarla a un’altra tabella pivot.
• cambia tutti i valori da Count a Sum
• rimuovi la “Sum of” da tutte le intestazioni

e molto altro!

Non perdere i nostri suggerimenti su Excel