Elementi della tabella pivot. Cosa faremo con il materiale ricevuto. Mostra e nascondi i dati di dettaglio

Gli elementi principali delle tabelle pivot. Prima di procedere con questo lavoro, penso che non sarà superfluo dire di quali elementi sono costituite le tabelle pivot, e per questo si consideri l'esempio in Figura 2. Un campo pagina è un campo della lista o tabella originale, posto nella area di orientamento della pagina tabella pivot.

In questo esempio, Scope è un margine di pagina che può essere utilizzato per riepilogare per regione. Quando si specifica un diverso elemento del campo della pagina, la tabella pivot viene ricalcolata per visualizzare i totali associati a tale elemento. Valore del campo della pagina: gli elementi del campo della pagina combinano i record oi valori di un campo o di una colonna nell'elenco originale della tabella. In altre parole, questo avviene cambiando questo campo, infatti, ogni volta che c'è una transizione a una nuova origine dati per una nuova regione.

I campi riga sono campi nell'elenco o nella tabella originale che vengono inseriti nell'area riga della tabella pivot. In questo esempio, Prodotti e Fornitore sono campi stringa. I campi interni della riga, es. Venditore, corrispondono esattamente all'area dati, i campi esterni della riga, es. Prodotti raggruppano quelli interni. Pertanto, qui viene tracciata una certa regola, ogni elemento di un campo di livello superiore, per così dire, perfeziona tutti i campi di un livello inferiore, il parametro del prodotto limita tutti i dati sottostanti: venditori di prodotti, dati sulle vendite di prodotti da parte di questi venditori. Un campo colonna è un campo in un elenco o una tabella di origine posizionato nell'area della colonna.

In questo esempio, Quarters è un campo colonna che include due elementi di campo KB2 e KB3. I campi interni delle colonne contengono elementi, le aree dati corrispondenti sono i campi esterni delle colonne situate sopra i campi interni L'esempio mostra un solo campo colonna.

L'area dati è la parte della tabella pivot che contiene i dati di riepilogo. Le celle dell'area dati visualizzano i totali per gli elementi dei campi riga o colonna. I valori in ciascuna cella dell'area dati corrispondono ai dati originali. In questo esempio, la cella C6 riepiloga tutti i record di dati grezzi contenenti lo stesso nome di prodotto, distributore e un trimestre specifico Meat, Myastorg LLP e KB2. Gli elementi di campo sono sottocategorie di un campo di tabella pivot. In questo esempio, i valori Carne e Frutti di mare sono voci di campo nel campo Prodotti.

Gli elementi di campo rappresentano i record in un campo o una colonna nei dati di origine. Gli elementi dei campi vengono visualizzati come intestazioni di riga o di colonna e nell'elenco a discesa dei campi della pagina. Un campo dati è un campo in un elenco o una tabella di origine che contiene dati. In questo esempio, il campo Importo ordine è un campo dati che riepiloga i dati originali nel campo o nella colonna Importo ordine. Un campo dati in genere riepiloga un gruppo di numeri, come statistiche o quantità di vendita, sebbene i dati correnti possano anche essere testuali.

Per impostazione predefinita, una tabella pivot riepiloga i dati di testo utilizzando la funzione di riepilogo dei valori totali e i dati numerici utilizzando la funzione di riepilogo della somma. Modifica della struttura di una tabella pivot Aspetto Puoi modificare la tabella pivot direttamente sul foglio trascinando i nomi dei pulsanti dei campi o degli elementi dei campi. È inoltre possibile modificare l'ordine degli elementi nel campo, per il quale è necessario selezionare il nome dell'elemento, quindi impostare il puntatore sul bordo della cella.

Quando il puntatore si trasforma in una freccia, trascina la cella del campo in una nuova posizione. Per rimuovere un campo, trascina il pulsante del campo fuori dall'area dei dettagli. L'eliminazione di un campo nasconderà tutti i valori dipendenti nella tabella pivot, ma non influirà sui dati originali. Se è necessario utilizzare tutti gli strumenti forniti per strutturare la tabella pivot o se la tabella corrente non ha precedentemente incluso tutti i campi dei dati di origine, è necessario utilizzare la procedura guidata della tabella pivot.

Se la tabella pivot contiene un grande gruppo di campi della pagina, è possibile posizionarli in righe o colonne. Inoltre, è opportuno notare che la modifica della struttura della tabella pivot non influisce sui dati originali.

Fine del lavoro -

Questo argomento appartiene alla sezione:

Elaborazione di informazioni tabulari utilizzando tabelle pivot utilizzando Microsoft Excel

Scambiando righe e colonne, è possibile creare nuovi totali dei dati originali visualizzando pagine diverse puoi filtrare i dati e ... In altre parole, queste tabelle ti consentono di combinare dati da diverse origini, ... Creazione di una tabella pivot Prima di creare una tabella pivot, devi prima impostare i dati per questa tabella, .. .

Se hai bisogno di materiale aggiuntivo su questo argomento, o non hai trovato quello che stavi cercando, ti consigliamo di utilizzare la ricerca nella nostra base di lavori:

Cosa faremo con il materiale ricevuto:

Se questo materiale si è rivelato utile per te, puoi salvarlo sulla tua pagina sui social network:

Tutti gli argomenti in questa sezione:

Creazione di una tabella pivot
Crea una tabella pivot. Prima di creare una tabella pivot, devi prima impostare i dati per questa tabella selezionando parte del libro, pagine e, se si tratta di un database o di un elenco, quindi n

Dettagli e azioni della tabella pivot
Dettagli dei dati della tabella pivot e delle azioni eseguite su di essa. I dati di dettaglio sono una sottocategoria di una tabella pivot. Questi elementi della tabella pivot sono elementi della tabella univoci o con

Mostra o nascondi i dati dettagliati della tabella pivot
Mostra o nascondi i dati dettagliati della tabella pivot. Per visualizzare i dati dettagliati precedentemente nascosti o nascondere i dati esistenti, è necessario selezionare l'elemento del campo, dati dettagliati

Mostra o nascondi gli elementi dei campi della tabella pivot
Mostra o nascondi gli elementi di un campo di tabella pivot. Se è necessario visualizzare o nascondere un elemento di campo, per questo è necessario prima selezionare il campo in cui è necessario visualizzare o

Visualizza gli elementi massimi o minimi in un campo di tabella pivot
Visualizza gli elementi massimi o minimi in un campo di tabella pivot. Per visualizzare gli elementi massimi o minimi è necessario selezionare il campo in cui verranno visualizzati gli elementi in futuro.

Disabilita l'accesso ai dettagli della tabella pivot
Disabilita l'accesso ai dettagli della tabella pivot. Se posizioni il puntatore su una cella nell'area dati di una tabella pivot e fai doppio clic, viene visualizzato un elenco di riepilogo dei dati di origine

Raggruppamento e separazione dei dati in una tabella pivot
Raggruppamento e separazione dei dati in una tabella pivot. In una tabella pivot puoi raggruppare date, orari, numeri e elementi selezionati. Ad esempio, puoi combinare i mesi per riassumere il sq.

Raggruppare e separare gli elementi in un campo di tabella pivot
Raggruppare e separare gli elementi in un campo di tabella pivot. Per eseguire il raggruppamento e la separazione degli elementi, è necessario selezionare gli elementi raggruppati. Quindi fare clic sul pulsante Gruppo sul pannello

Raggruppa e separa i numeri in una tabella pivot
Raggruppa e separa i numeri in una tabella pivot. Per raggruppare e separare i numeri, selezionare il numero nella casella in cui si desidera raggruppare gli elementi. Quindi premere il pulsante

Raggruppa e separa date o orari in una tabella pivot
Raggruppa e separa date o orari in una tabella pivot. Per raggruppare e separare una data o un'ora, selezionare la data o l'ora nel campo in cui deve essere raggruppato l'elemento.

Raggruppamento e separazione degli elementi dei campi della pagina della tabella pivot
Raggruppamento e separazione degli elementi di un campo della pagina di una tabella pivot. Se la tabella pivot è stata creata da un'origine dati esterna, controlla che il campo della pagina stia recuperando dati esterni

Aggiorna o modifica l'origine dati della tabella pivot. Nel processo di lavoro, a volte si verifica una situazione quando vengono visualizzati nuovi dati, che devono essere inseriti nel documento creato, e

Aggiornamento dei dati in una tabella pivot
Aggiornamento dei dati in una tabella pivot. Per aggiornare i dati nella tabella pivot, è necessario eseguire le seguenti operazioni: è necessario selezionare la cella nella tabella pivot, il cui contenuto è richiesto

Disabilita l'aggiornamento della tabella pivot all'apertura del file della cartella di lavoro
Disabilita l'aggiornamento della tabella pivot all'apertura di un file della cartella di lavoro. Per disabilitare l'aggiornamento della tabella pivot all'apertura di un file, selezionare una cella nella tabella pivot per la quale non si verificherà alcun aggiornamento

Ordinamento dei dati della tabella pivot
Ordinamento dei dati della tabella pivot. Gli elementi dei campi della tabella pivot vengono ordinati automaticamente in ordine crescente in base al loro nome. L'ordinamento degli elementi per nome consente di ripristinare l'originale

Riepilogo ed elaborazione dei dati della tabella pivot
Riassumendo ed elaborando i dati della tabella pivot. I campi dati della tabella pivot possono essere configurati per calcoli diversi da quelli predefiniti nella funzione di riepilogo. Per eseguire complessi

Utilizzo di totali complessivi e totali parziali in una tabella pivot
Utilizzo di totali complessivi e totali parziali in una tabella pivot. Una tabella pivot può essere configurata per eseguire calcoli utilizzando una funzione di riepilogo Una funzione di riepilogo è il tipo di calcolo eseguito da

Inserisci o rimuovi i subtotali in una tabella pivot
Inserisci o rimuovi i subtotali in una tabella pivot. Per inserire o eliminare i subtotali, è necessario posizionare il puntatore sul pulsante del campo in cui si desidera

Crea un grafico per una tabella pivot
Crea un grafico per una tabella pivot. Per creare un grafico per una tabella pivot, sulla barra degli strumenti Tabelle pivot selezionare il comando Seleziona dal menu Tabella pivot. Controllalo

Questo post presenta una raccolta di strumenti di tabella pivot semplici e ingegnosi in Excel. Ciò che viene chiamato suggerimenti e trucchi in inglese. Prenditi un po 'di tempo e leggi i suggerimenti qui. Chissà, forse troverai finalmente la risposta alla domanda che ti tormenta da tempo?

Suggerimento 1. Aggiornamento automatico delle tabelle pivot

A volte vuoi che le tabelle pivot si aggiornino automaticamente. Supponiamo che tu abbia creato una tabella pivot per un manager. È improbabile che tu possa aggiornarlo regolarmente, a meno che il manager non ti permetta di accedere al suo laptop. Puoi abilitare aggiornamento automatico tabella pivot da eseguire ogni volta che viene aperta la cartella di lavoro:

1. Fare clic con il tasto destro sulla tabella pivot e selezionare Opzioni tabella pivot.
2. Nella finestra di dialogo che appare Opzioni tabella pivot seleziona scheda Dati.
3. Selezionare la casella Aggiorna all'apertura del file.

Figura. 1. Abilita l'opzione Aggiorna all'apertura del file

Casella di controllo Aggiorna all'apertura del file dovrebbe essere installato separatamente per ogni tabella pivot.

Scarica la nota nel formato o, esempi nel formato (il file contiene il codice VBA).

Suggerimento 2: aggiorna contemporaneamente tutte le tabelle pivot in una cartella di lavoro

Se una cartella di lavoro contiene più tabelle pivot, aggiornarle contemporaneamente può essere problematico. Esistono diversi modi per superare queste difficoltà:

Metodo 1. Per ogni tabella pivot inclusa nella cartella di lavoro, è possibile selezionare un'impostazione che si aggiorna automaticamente all'apertura della cartella di lavoro (per ulteriori dettagli, vedere Suggerimento 1).

Metodo 3: utilizzare il codice VBA per aggiornare su richiesta tutte le tabelle pivot nella cartella di lavoro. Questo approccio utilizza il metodo RefreshAll dell'oggetto Workbook. Per utilizzare questa tecnica, crea un nuovo modulo e inserisci il seguente codice:

Sub Refresh_All ()

ThisWorkbook.RefreshAll

Suggerimento 3. Ordina gli elementi in ordine casuale

Nella fig. La Figura 2 mostra l'ordine predefinito in cui le aree vengono visualizzate in una tabella pivot. Le regioni sono ordinate alfabeticamente: Ovest, Nord, Midwest e Sud. Se le regole aziendali richiedono di visualizzare prima la regione Ovest e poi le regioni Midwest, Nord e Sud, eseguire un ordinamento manuale. Basta entrare nel Midwest nella cella C4 e premere il tasto accedere... L'ordinamento delle regioni cambierà.

Suggerimento 4: converti una tabella pivot in valori hardcoded

Lo scopo della creazione di una tabella pivot è riepilogare e visualizzare i dati in formato formato adatto... I dati non elaborati per la tabella pivot vengono archiviati separatamente, il che introduce un po 'di "overhead". La conversione della tabella pivot in valori ti consentirà di utilizzare i risultati ottenuti in essa senza accedere ai dati originali o alla cache della tabella pivot. Il modo in cui la tabella pivot viene convertita dipende dal fatto che l'intera tabella sia interessata o solo una parte di essa.

Per convertire una parte di una tabella pivot, segui questi passaggi:

1. Seleziona i dati della tabella pivot copiati, fai clic con il pulsante destro del mouse e seleziona copia (o digita Ctrl + C sulla tastiera).
2. Fare clic con il pulsante destro del mouse in un punto qualsiasi del foglio di lavoro e selezionare il comando Incolla (o digita Ctrl + V).

Se è necessario convertire l'intera tabella pivot, attenersi alla seguente procedura:

1. Seleziona l'intera tabella pivot, fai clic con il tasto destro e seleziona copia... Se la tabella pivot non contiene l'area FILTRI, è possibile utilizzare la scorciatoia da tastiera Ctrl + Maiusc + * per selezionare l'area della tabella pivot.
2. Fare clic con il pulsante destro del mouse in un punto qualsiasi del foglio e selezionare un'opzione dal menu di scelta rapida Incolla speciale.
3. Seleziona un'opzione I valori e fare clic ok.

È una buona idea eliminare i totali parziali prima di convertire la tabella pivot perché non sono molto utili nel set di dati offline. Per eliminare tutti i subtotali, vai al menu Design -\u003e Subtotals -\u003e Non mostrare subtotali. Per eliminare i totali parziali specifici, fare clic con il pulsante destro del mouse sulla cella in cui vengono calcolati i totali. Seleziona l'elemento nel menu contestuale Parametri di campo e nella finestra di dialogo Parametri di campo nella sezione Risultati selezionare il pulsante di opzione Non... Dopo aver fatto clic sul pulsante ok i totali parziali verranno eliminati.

Suggerimento 5. Riempire le celle vuote nei campi LINE

Dopo aver convertito la tabella pivot, il foglio visualizza non solo i valori, ma l'intera struttura dati della tabella pivot. Ad esempio, i dati mostrati in Fig. 3 sono stati derivati \u200b\u200bda una tabella pivot con un layout tabulare.

Figura. 3. È problematico utilizzare questa tabella pivot convertita senza riempire le celle vuote sul lato sinistro

Nota che i campi Regione e Mercato di vendita mantiene la stessa struttura di righe di quando questi dati si trovano nell'area RIGHE di una tabella pivot. Excel 2013 ha modo veloce riempiendo le celle nell'area LINEA con i valori. Fare clic nell'area della tabella pivot, quindi scorrere il menu Costruttore -> Layout del report -> (fig.4). Quindi puoi convertire la tabella pivot in valori, che ti darà una tabella di dati senza spazi.

Figura. 4. Dopo aver applicato il comando Ripeti tutte le etichette degli elementi tutte le celle vuote vengono riempite

Suggerimento 6: classificazione dei campi numerici in una tabella pivot

Quando si ordinano e classificano i campi che contengono un numero elevato di elementi di dati, non è sempre facile determinare il rango numerico dell'elemento di dati analizzato. Inoltre, se la tabella pivot viene convertita in valori, assegnare a ciascun elemento di dati un rango numerico visualizzato in un campo intero faciliterà notevolmente l'analisi del set di dati generato. Aprire una tabella pivot come quella mostrata in Fig. 5. Si prega di notare che lo stesso indicatore - Importo per il campo Volume delle vendite - visualizzato due volte. Fare clic con il pulsante destro del mouse sulla seconda istanza della metrica e selezionare Calcoli aggiuntivi -> Ordinamento dal massimo al minimo (figura 6.)

Dopo aver creato una classifica, è possibile personalizzare le etichette dei campi e la formattazione (Figura 14.9). Il risultato sarà un bel rapporto classificato.

Suggerimento 7: ridurre le dimensioni di un rapporto di tabella pivot

Quando si genera un rapporto di tabella pivot, Excel acquisisce un'istantanea dei dati e la archivia nella cache della tabella pivot. La cache della tabella pivot è un'area speciale di memoria che archivia una copia dell'origine dati per velocizzare l'accesso. In altre parole, Excel crea una copia dei dati e quindi la archivia in una cache associata alla cartella di lavoro. La cache della tabella pivot fornisce l'ottimizzazione del flusso di lavoro. Eventuali modifiche apportate alla tabella pivot, ad esempio la modifica del layout dei campi, l'aggiunta di nuovi campi o l'occultamento di elementi, sono più veloci ei requisiti delle risorse di sistema sono molto più modesti. Lo svantaggio principale della cache della tabella pivot è che quasi raddoppia la dimensione del file della cartella di lavoro ogni volta che viene creata una tabella pivot da zero.

Elimina i dati originali.Se la cartella di lavoro contiene un set di dati originale e una tabella pivot, la dimensione del file viene raddoppiata. Pertanto, puoi eliminare in sicurezza i dati originali e ciò non influirà affatto sulla funzionalità della tua tabella pivot. Dopo aver eliminato i dati originali, ricordarsi di salvare la versione compressa del file della cartella di lavoro. Dopo aver eliminato i dati originali, puoi utilizzare la tabella pivot come al solito. L'unico problema è l'impossibilità di aggiornare la tabella pivot a causa della mancanza di dati originali. Se sono necessari dati in background, fare doppio clic sull'intersezione di una riga e una colonna nell'area dei totali generali (nella Figura 7, questa è la cella B18). In tal modo, Excel scarica il contenuto della cache della tabella pivot in un nuovo foglio di lavoro.

Suggerimento 8: crea un intervallo di dati espandibile automaticamente

Sicuramente hai riscontrato ripetutamente situazioni in cui dovevi aggiornare i rapporti di tabella pivot su base giornaliera. Ciò è molto spesso necessario quando vengono aggiunti costantemente nuovi record all'origine dati. In questi casi, sarà necessario ridefinire l'intervallo utilizzato in precedenza prima che i nuovi record vengano aggiunti alla nuova tabella pivot. Ridefinire l'intervallo di dati originale per la tabella pivot è facile, ma quando lo fai spesso diventa piuttosto noioso.

La soluzione al problema è convertire l'intervallo di dati originale in una tabella anche prima che venga creata la tabella pivot. Grazie a fogli di calcolo Excel è possibile creare un intervallo denominato che può espandersi o contrarsi automaticamente a seconda della quantità di dati in esso contenuti. Puoi anche collegare qualsiasi componente, grafico, tabella pivot o formula a un intervallo, in modo da poter tenere traccia delle modifiche nel set di dati.

Per implementare la tecnica descritta, selezionare i dati di origine, quindi fare clic sull'icona della tabella situata nella scheda Inserire (fig.8) o premere Ctrl + T (inglese T). Clic ok nella finestra che si apre. Tieni presente che, sebbene non sia necessario ridefinire l'intervallo dei dati di origine nella tabella pivot, dovrai comunque fare clic sul pulsante quando aggiungi i dati di origine all'intervallo nella tabella pivot. ricaricare.

Suggerimento 9: confronta le tabelle normali con una tabella pivot

Se stai confrontando due tabelle diverse, è conveniente utilizzare una tabella pivot, che ti farà risparmiare molto tempo. Supponiamo di avere due tabelle che visualizzano i dettagli del cliente per il 2011 e il 2012 (Figura 9). Le piccole dimensioni di queste tabelle sono qui fornite solo come esempi. In pratica vengono utilizzate tabelle molto più grandi.

Il confronto crea una singola tabella da cui viene creata una tabella pivot. Assicurati di avere un modo per taggare i dati associati a queste tabelle. In questo esempio, utilizza la colonna Anno fiscale (fig.10). Dopo aver combinato le due tabelle, utilizzare il set di dati combinato risultante per creare una nuova tabella pivot. Formattare la tabella pivot in modo che l'area della colonna della tabella pivot venga utilizzata come tag della tabella (identificatore che indica l'origine della tabella). Come mostrato in fig. 11, gli anni sono nell'area della colonna e i dettagli del cliente sono nell'area della riga. L'area dati contiene i volumi di vendita per ogni cliente.

Suggerimento 10: filtro automatico di una tabella pivot

Come sapete, non è possibile utilizzare filtri automatici nelle tabelle pivot. Tuttavia, esiste un trucco per abilitare i filtri automatici in una tabella pivot. Il principio alla base di questa tecnica è posizionare il puntatore del mouse a destra dell'ultima intestazione della tabella pivot (cella D3 nella Figura 12), quindi andare sulla barra multifunzione e selezionare il comando Dati -> Filtro... D'ora in poi, nella tua tabella pivot apparirà un filtro automatico! Ad esempio, puoi selezionare tutti i clienti con un tasso di transazione superiore alla media. I filtri automatici aggiungono un ulteriore livello di analisi alla tabella pivot.

Suggerimento 11: converti i set di dati visualizzati nelle tabelle pivot

Il layout migliore per i dati originali convertiti in una tabella pivot è il layout tabulare. Questo tipo di layout ha le seguenti caratteristiche: non ci sono righe o colonne vuote, ogni colonna ha un'intestazione, ogni campo ha un valore in ogni riga e le colonne non contengono gruppi ripetuti di dati. In pratica, ci sono spesso set di dati che assomigliano a quello mostrato in Fig. 13. Come puoi vedere, i nomi dei mesi vengono visualizzati in una riga lungo la parte superiore della tabella, svolgendo la duplice funzione di etichette di colonna e dati effettivi. In una tabella pivot creata da una tabella di questo tipo, ciò comporterebbe 12 campi da gestire, ciascuno dei quali rappresenta un mese separato.

Per eliminare questo problema, puoi utilizzare una tabella pivot con diversi intervalli consolidati come passaggio intermedio (vedi dettagli). Per convertire un set di dati con uno stile di matrice in un set di dati più adatto per la creazione di tabelle pivot, segui questi passaggi.

Passaggio 1. Combina tutti i campi non a colonne in una colonna.Per creare tabelle pivot con più intervalli consolidati, creare una singola colonna di dimensione. In questo esempio, tutto ciò che non è correlato al campo del mese viene considerato come una dimensione. Quindi i campi Mercato di vendita e Descrizione del servizio dovrebbe essere unito in una colonna. Per combinare i campi in una colonna, inserisci semplicemente una formula che concatena i due campi utilizzando un punto e virgola come separatore. Assegna un nome alla nuova colonna. La formula inserita viene visualizzata nella barra della formula (Fig. 14).

Figura. 14. Risultato della concatenazione di colonne Mercato di vendita e Descrizione del servizio

Dopo aver creato la colonna concatenata, converti le formule in valori. Per fare ciò, seleziona la colonna appena creata, premi Ctrl + C, quindi esegui il comando Incolla -> Incolla speciale -> I valori... Le colonne possono ora essere rimosse Mercato di vendita e Descrizione del servizio (fig.15).

Figura. 15. Colonne rimosse Mercato di vendita e Descrizione del servizio

Passaggio 2. Creare una tabella pivot con più intervalli di consolidamento.Ora è necessario chiamare la Creazione guidata tabella pivot e grafico, familiare a molti utenti dalle versioni precedenti di Excel. Premi Alt + D + P per richiamare questa procedura guidata. Sfortunatamente, questa scorciatoia da tastiera è per la versione inglese di Excel 2013. Nella versione russa, corrisponde alla scorciatoia da tastiera Alt + D + N. Ma per ragioni a me sconosciute, non funziona. Tuttavia, puoi portare la buona vecchia procedura guidata della tabella pivot alla barra degli strumenti di accesso rapido, vedere. Dopo aver avviato la procedura guidata, seleziona il pulsante di opzione In più intervalli di consolidamento... Clic Ulteriore... Imposta l'interruttore Crea campi pagina e fare clic Ulteriore... Determina l'intervallo di lavoro e fai clic Fatto (vedi i dettagli). Creerai una tabella pivot (Figura 16).

Passaggio 3. Fare doppio clic sull'intersezione di una riga e di una colonna nella riga del totale generale.A questo punto, avrai una tabella pivot (Figura 16) che include diversi intervalli di consolidamento, il che è quasi inutile. Seleziona la cella che si trova all'intersezione della riga e della colonna del totale generale e fai doppio clic su di essa (nel nostro esempio, questa è la cella N88). Otterrai un nuovo foglio, la cui struttura ricorda la struttura mostrata in Fig. 17. In realtà, questo foglio è una versione trasposta dei dati originali.

Passaggio 4. Suddivisione della colonna Riga in campi separati.Resta da dividere la colonna Linea in campi separati (ritorno alla struttura originale). Aggiungi una colonna vuota immediatamente dopo la colonna Linea... Evidenzia la colonna A e quindi vai alla scheda della barra multifunzione Dati e fare clic sul pulsante Testo della colonna... Sullo schermo apparirà una finestra di dialogo. Procedura guidata di distribuzione del testo... Nel primo passaggio, seleziona il pulsante di opzione Con separatorie fare clic sul pulsante Avanti. Nel passaggio successivo, seleziona il pulsante di opzione punto e virgola e fare clic Fatto... Formatta il testo, aggiungi un titolo e trasforma i dati originali in una tabella premendo Ctrl + T (Figura 18).

Figura. 18. Questo set di dati è ideale per creare una tabella pivot (confronta con la Fig. 13)

Suggerimento 12: includi due formati numerici in una tabella pivot

Consideriamo ora una situazione in cui un set di dati normalizzato rende difficile costruire una tabella pivot di facile analisi. Un esempio è mostrato in Fig. 19 è una tabella che comprende due diversi indicatori per ciascuna area di vendita. Notare la colonna D, che identifica la metrica.

Sebbene questa tabella sia un esempio di una formattazione abbastanza buona, non è poi così buona. Tieni presente che alcune metriche dovrebbero essere visualizzate in formato numerico e altre in percentuale. Ma nel database originale, il campo Valore è di tipo Double. Quando si crea una tabella pivot da un set di dati, non è possibile assegnare due diversi formati numerici allo stesso campo Valore... C'è una semplice regola pratica qui: un campo corrisponde a un formato numerico. Se si tenta di assegnare un formato numerico a un campo a cui è stato assegnato un formato percentuale, i valori percentuali si trasformeranno in numeri regolari che terminano con un segno di percentuale (Figura 20).

Per risolvere questo problema, viene utilizzato un formato numerico personalizzato, che formatta qualsiasi valore maggiore di 1,5 come numero. Se il valore è inferiore a 1,5, viene formattato come percentuale. Nella finestra di dialogo Formato cella seleziona scheda (tutti i formati) e sul campo Un tipo inserisci la seguente stringa di formattazione (fig. 21): [\u003e \u003d 1,5] $ # ## 0; [<1,5]0,0%

Figura. 21. Applicare un formato numerico personalizzato in cui tutti i numeri inferiori a 1,5 vengono formattati come percentuali

Il risultato è mostrato in Fig. 22. Come puoi vedere, ora ogni indicatore è formattato correttamente. Ovviamente, la ricetta in questo suggerimento non è universale. Piuttosto, indica la direzione in cui sperimentare.

Suggerimento 13: crea un'allocazione di frequenza per una tabella pivot

Se hai mai generato distribuzioni di frequenza utilizzando la funzione Excel Frequenza, probabilmente sai che questo è un compito molto difficile. Inoltre, dopo aver modificato gli intervalli di dati, tutto deve ricominciare da capo. In questa sezione imparerai come creare semplici distribuzioni di frequenza utilizzando una semplice tabella pivot. Innanzitutto, crea una tabella pivot che contenga i dati nell'area delle righe. Prestare attenzione alla fig. 23, dove il campo si trova nell'area della riga Volume i saldi.

Fare clic con il tasto destro su qualsiasi valore nell'area Righe e selezionare l'opzione dal menu contestuale Gruppo... Nella finestra di dialogo Raggruppamento (Fig.24) determinano i valori dei parametri che determinano l'inizio, la fine e il passo della distribuzione di frequenza. Fare clic su OK.

Figura. 24. Nella finestra di dialogo Raggruppamento regolare i parametri di distribuzione della frequenza

Se aggiungi un campo alla tabella pivot Cliente (Fig.25), otteniamo la distribuzione della frequenza delle transazioni dei clienti rispetto alla dimensione degli ordini (in dollari).

Figura. 25. Ora hai a tua disposizione la distribuzione delle transazioni dei clienti in base alla dimensione degli ordini (in dollari)

Il vantaggio di questa tecnica è che il filtro del rapporto di tabella pivot può essere utilizzato per filtrare in modo interattivo i dati in base ad altre colonne, ad esempio Regione e Mercato di vendita... L'utente ha anche la possibilità di regolare rapidamente gli intervalli di distribuzione della frequenza facendo clic con il pulsante destro del mouse su qualsiasi numero nell'area della riga e quindi selezionando un'opzione. Gruppo... Per chiarezza di presentazione, è possibile aggiungere un grafico pivot (Fig. 26).

Suggerimento 14. Utilizzo di una tabella pivot per distribuire un set di dati tra i fogli di una cartella di lavoro

Gli analisti spesso devono creare diversi rapporti di tabella pivot per ogni regione, area di vendita, manager, ecc. Questa attività in genere comporta un lungo processo di copia della tabella pivot in un nuovo foglio e quindi la modifica del campo del filtro per riflettere la regione e il gestore appropriati. Questo processo viene eseguito manualmente e viene ripetuto per ogni tipo di analisi. Ma in realtà, puoi esternalizzare la creazione di singole tabelle pivot in Excel. Come risultato dell'applicazione del parametro una tabella pivot separata viene creata automaticamente per ogni elemento nell'area dei campi del filtro. Per utilizzare questa funzione, è sufficiente creare una tabella pivot che includa un campo filtro (Figura 27). Posiziona il cursore in un punto qualsiasi della tabella pivot e della scheda Analisi nel gruppo delle squadre Tabella pivot fare clic sul menu a discesa Opzioni (fig.28). Quindi fare clic sul pulsante Mostra pagine filtro report.

Figura. 28. Fare clic sul pulsante Mostra pagine filtro report

Nella finestra di dialogo visualizzata (Figura 29), è possibile selezionare un campo filtro per il quale verranno create tabelle pivot separate. Seleziona il campo del filtro appropriato e fai clic su ok.

Figura. 29. Finestra di dialogo Visualizzazione delle pagine dei filtri dei report

Per ogni elemento del campo filtro verrà creata una tabella pivot, posta su un foglio separato (Fig.30). Tieni presente che le schede del foglio hanno lo stesso nome degli elementi nel campo del filtro. Notare che il parametro Mostra le pagine dei filtri può essere applicato per filtrare i campi uno per uno.

Suggerimento 15: utilizzo di una tabella pivot per distribuire un set di dati a singole cartelle di lavoro

Nel suggerimento 14, abbiamo utilizzato un'opzione speciale per dividere tabelle riassuntive dai mercati di vendita su diversi fogli della cartella di lavoro. Se hai bisogno di dividere dati iniziali su diversi mercati di vendita in libri separati, è possibile utilizzare un piccolo codice VBA. Innanzitutto, posizionare il campo su cui si desidera filtrare nell'area dei campi del filtro. Posiziona il campo Volume delle vendite nell'intervallo di valori (Fig.31). Il codice VBA suggerito seleziona a turno ogni elemento FILTER e chiama la funzione Mostra dettaglicreazione di una nuova scheda tecnica. Questo foglio viene quindi salvato in una nuova cartella di lavoro

Il codice VBA.

Sotto ExplodeTable ()

Dim PvtItem As PivotItem

Dim PvtTable come tabella pivot

Dim strfield As PivotField

'Modifica le variabili in base allo script

ConststrFieldName \u003d "Marketplace" "<—Изменение имени поля

Const strTriggerRange \u003d "A4" '<—Изменение диапазона триггера

'Modifica il nome della tabella pivot (se necessario)

SetPvtTable \u003d ActiveSheet.PivotTables ("PivotTable1") '<—Изменение названия сводной

"Passa attraverso ogni elemento del campo selezionato

Per ogni oggetto Pvt in PvtTable.PivotFields (strFieldName) .PivotItems

PvtTable.PivotFields (strFieldName) .CurrentPage \u003d PvtItem.Name

Intervallo (strTriggerRange). ShowDetail \u003d True

'Assegnazione di un nome a un foglio temporaneo

ActiveSheet.Name \u003d "TempSheet"

'Copia dei dati in una nuova cartella di lavoro ed eliminazione di un foglio temporaneo

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts \u003d False

ActiveWorkbook.SaveAs _

Nome file: \u003d ThisWorkbook.Path & "\\" & PvtItem.Name & ".xlsx"

ActiveWorkbook.Close

Fogli ("Tempsheet") .Elimina

Application.DisplayAlerts \u003d True

Inserisci questo codice in un nuovo modulo VBA. Verificare i valori delle seguenti costanti e variabili e modificarli se necessario:

• Const strFieldName. Il nome del campo utilizzato per separare i dati. In altre parole, è un campo che si inserisce nell'area filtro / pagine della tabella pivot.
• Const strTriggerRange. Una cella trigger che archivia un singolo numero dall'area dati della tabella pivot. Nel nostro caso, la cella trigger è A4 (vedi Fig.31).

Come risultato dell'esecuzione del codice VBA, i dati per ciascuna area di vendita verranno salvati in una cartella di lavoro separata.

Nota basata sul libro di Jelen, Alexander. ... Capitolo 14.

Consigliato da leggere

Programmi di Office

Perché i muri imbiancati sognano

Compresse

Perché hai sognato di imprecare in un sogno?

Smartphone

Pietra dello zodiaco Scorpione

Istruzioni di Windows

Imperatore: significato in una situazione e in una domanda

Programmi di Office

Interpretazione da sogno di ciò che sogna il braccialetto sulla mano

Istruzioni per Android

Interpretazione del sogno della guaritrice Akulina Cosa significa Straw in un sogno