EXCEL - TRUCCHI E SEGRETI: Tabelle Pivot - Una pivot più fonti - I modelli di dati
Sommario:
- La differenza tra una singola tabella di file flat e tabelle multi-file correlate
- Tabelle multi-file correlate: creazione di relazioni
- Creare i report Tabella pivot
- Suggerimenti per la tabella pivot
La tabella pivot è uno strumento che Excel utilizza per creare rapporti personalizzati dai database del foglio di calcolo. Dopo aver selezionato la parte del foglio di lavoro che contiene i dati di destinazione, definirla come tabella e denominarla, diventa una tabella pivot, che è soggetta a tutti gli strumenti della tabella pivot.
Con questi strumenti, è possibile filtrare, ordinare, riorganizzare, calcolare e riepilogare una tabella di database o più tabelle. È possibile estrarre informazioni specifiche in un report personalizzato separato che visualizza solo le informazioni rilevanti richieste per il progetto corrente.
Questo articolo sulla creazione di report tabella pivot multi-file fa parte di una serie su tabelle pivot relazionali:
[ Ulteriori letture: il tuo nuovo PC ha bisogno di questi 15 programmi gratuiti eccellenti- Come creare tabelle relazionali
- Come creare e utilizzare i filtri per data, numero e campi di testo
- Come creare un singolo "file piatto" "Rapporti tabella pivot
Per semplificare la pratica delle attività che stiamo per descrivere, abbiamo creato una cartella di lavoro scaricabile di Excel con tutti i dati che utilizziamo in questo articolo.
La differenza tra una singola tabella di file flat e tabelle multi-file correlate
Iniziamo con un concetto di tabella pivot fondamentale. Una singola tabella "flat-file" viene creata da un singolo foglio di calcolo. Le tabelle multi-file correlate sono create da due o più fogli di calcolo collegati tra loro attraverso un campo chiave univoco.
Nel nostro foglio di calcolo di esempio, gli strumenti Tabella pivot consentono di estrarre i numeri di licenza e i nomi dei driver da una tabella (Master1) e i restanti dati da un'altra tabella (Violazioni). Il campo chiave univoco, in questo caso, è il numero di licenza, che deve esistere in tutte le tabelle con cui stai lavorando per questi rapporti.
Di conseguenza, le tabelle pivot attivano fogli di calcolo più piccoli (ovvero meno campi) ed eliminano dati ridondanti. I report sono più efficienti e più facili da compilare. Ad esempio, nelle versioni precedenti, dovevi inserire il nome, l'indirizzo, la città, lo stato, il codice postale e il numero di telefono del cliente nel foglio di calcolo, Foglio di calcolo prodotto, Foglio di calcolo vendite, Foglio di calcolo inventario e altro, in base a quanti dati era necessario tenere traccia di ciò che era correlato a quell'informazione. Con gli strumenti Tabella pivot, inserisci i dati una volta, quindi collega le tabelle insieme attraverso un campo unico comune. Excel fa il resto.
Ulteriore formazione di Excel
Se vuoi approfondire la padronanza di Excel, esistono un certo numero di corsi online per espandere le tue conoscenze. Ecco le nostre prime scelte per iniziare:
Coursera - Excel Skills for Business: Essentials
EDX - Analisi e visualizzazione dei dati con Excel
Lynda.com - Excel: Scenario Pianificazione e analisi
Udacity - Fondamenti di Excel
Tabelle multi-file correlate: creazione di relazioni
Inizieremo mostrando come si creano relazioni tra più fogli di calcolo per una tabella pivot. A partire dal foglio di calcolo di esempio:
Inserisci / Crea tabella pivot
1. Accedere alla tabella Violazioni.
2. Selezionare Inserisci > Tabella pivot .
3. Nella finestra di dialogo Crea tabella pivot , assicurarsi che la Intervallo tabella indichi Violazioni ; la posizione (scegliere dove posizionare questo rapporto) ha la casella del cerchio Nuovo foglio di lavoro selezionata; quindi selezionare l'ultima casella: Aggiungi questi dati al modello di dati ; e fare clic su OK.
Nota: Il motivo per cui abbiamo selezionato la tabella Violazioni anziché la tabella Master o Indirizzi è perché vogliamo analizzare e calcolare i dati in questa tabella.
4. Il pannello Campi pivot si apre a destra.
Il pannello Campi tabella pivot si apre a destra quando lavori in questa modalità.
5. Quindi, selezionare Dati > Relazioni e si apre la finestra di dialogo Gestisci relazioni . Fai clic sul pulsante Nuovo a destra e si apre la finestra Crea relazione
6. Fare clic sulla freccia giù accanto al campo Tabella e selezionare Master1 dall'elenco. Nella casella di immissione Colonna (Esterna) , fare clic sulla freccia e selezionare Numero di licenza dall'elenco.
7. Nella casella di input Tabella correlata , selezionare la tabella Violazioni. Sotto Colonna correlata (principale) , fare nuovamente clic sulla freccia e selezionare Numero di licenza di nuovo da quella lista.
Creare relazioni tra la tabella Master1 e le violazioni Tabella.
8. Riappare la finestra di dialogo Gestisci relazioni . Fare clic sul pulsante Nuovo a destra e si apre la finestra Crea relazione
9. Fare di nuovo clic sulla freccia giù accanto al campo Tabella e selezionare Master1 dall'elenco. Nella casella di immissione Colonna (Esterna) , fare clic sulla freccia e selezionare Numero di licenza dall'elenco.
La finestra Gestisci relazioni mostra due relazioni attive .
10. Nella casella di immissione Tabella correlata , selezionare la tabella Indirizzi . Sotto Colonna correlata (principale) , fare nuovamente clic sulla freccia e selezionare Numero di licenza di nuovo
11. Riappare la finestra di dialogo Gestisci relazioni . Nota che ora hai due relazioni attive: Master1 + Violazioni e Master1 + Indirizzi. Al termine, fare clic su OK e quindi su Chiudi .
Creare i report Tabella pivot
1. Nel pannello Campi pivot , fare clic sulla parola ALL in alto.
2. Fare clic sulla freccia del nome della tabella per visualizzare i campi in ciascuna tabella. Ad esempio, sotto la tabella Master1, fare clic sulle caselle Numero di licenza e Nome .
3. Sotto Tabella delle violazioni , fai clic sulle caselle Tipo di violazione e Tariffa di violazione . Il rapporto di tabella pivot viene visualizzato immediatamente a sinistra, in crescita e in espansione man mano che si aggiungono altri campi.
Selezionare i campi dalla tabella Master1 e i campi dalla tabella Violazioni per questo report.
Nota: Excel sa quale campo è il campo chiave, perché hai definito le relazioni utilizzando gli strumenti Tabella pivot nella finestra di dialogo Crea relazione. Quindi non è necessario includere il campo chiave (LicenseNumber) in entrambe le tabelle. Se lo fai, questo campo apparirà due volte (in due aree diverse) nel tuo rapporto. A meno che non sia necessario due volte nella relazione, non fare clic due volte su questo campo. Una volta nella tabella Master1 è sufficiente.
Immagina che ora il tuo capo desideri vedere se esiste una connessione tra le violazioni e le aree (codici ZIP) in cui vivono i conducenti colpevoli. Puoi farlo facilmente.
4. Innanzitutto, devi assicurarti che i tuoi CAP siano inseriti come campi di testo. Accedere alla tabella del foglio di calcolo Master1 ed evidenziare il campo Zip .
5. Seleziona Home > Formato > Formatta celle . Sotto la scheda Numero , seleziona Testo dall'elenco.
Verifica che il tuo campo CAP sia definito come campo di testo.
6 . Torna alla Tabella Master1 e fai clic sulla casella accanto al campo Zip.
7. Una casella gialla appare nel pannello Campi tabella pivot che dice: Le relazioni tra tabelle possono essere necessarie , seguite da un pulsante Crea . Se si fa clic su questo pulsante e si ridefiniscono le relazioni nella finestra di dialogo Crea relazione, in Excel viene visualizzato un messaggio che dice: Esiste già una relazione tra queste due colonne . Quindi fare clic su Annulla . Risparmia tempo e fai semplicemente clic sulla "X" nell'angolo in alto per chiudere la casella gialla.
8. Se Excel considera ancora il tuo codice postale come un numero e lo inserisce in una colonna Sum, fai clic con il pulsante destro del mouse sul campo Zip e scegli Sposta in etichette riga dall'elenco del menu a discesa o fino alla casella Valori (in basso a destra del pannello Campi tabella pivot), fare clic sulla freccia e selezionare Sposta in etichette riga dal elenco menu popup. E i codici ZIP si muovono sotto ogni violazione. Molto meglio.
Chiudi la casella gialla, quindi cambia il codice di avviamento postale in Etichette di riga.
Successivamente, aggiungeremo altri campi per migliorare il nostro rapporto.
9. Fare clic sulla casella di controllo Data di violazione nella tabella Violazioni nel pannello Campi tabella pivot . Si noti che Excel aggiunge prima le date alle colonne, che non funziona affatto. I dati sono dappertutto e troppo difficili da leggere.
10. Fare clic con il pulsante destro del mouse sul campo Data violazioni nel riquadro Campi tabella pivot (accanto alla casella di controllo) e selezionare Sposta etichette righe dall'elenco del menu a discesa. Si noti che la data è posizionata sotto il codice di avviamento postale e il tipo di violazione. Nota che il primo pilota della lista, Roger David Anderson, ha due inversioni a U illegali, una il 10 febbraio 2015 e una il 12 ottobre 2017.
11. Infine, nel riquadro Campi tabella pivot , fai clic sul campo Punti sotto la tabella Violazioni (accanto alla casella di controllo).
12. Excel aggiunge il campo al report e somma i punti in base al numero di licenza (per autista) e aggiunge anche un totale complessivo per tutti i punti. Si noti quanto sia facile aggiungere o rimuovere campi nei report utilizzando gli strumenti Tabella pivot.
Nota: I totali parziali e totali sono in grassetto e le violazioni sono di tipo normale. Ad esempio, i dati sui due inversioni a U illegali di Roger David Anderson sono di tipo regolare, mentre il totale parziale delle virate a U di $ 100 e 6 punti è in grassetto.
08 Aggiungi altri due campi del report: data e punti di violazione
Utilizzare la scheda Strumenti / struttura della tabella pivot (visibile solo quando il report è attivo) per aggiungere il colore e la formattazione della creatività al layout di tabella. Le tabelle sono più facili da leggere quando le righe alternano i colori e i totali dei campi sono in grassetto. Sperimenta e divertiti.
Suggerimenti per la tabella pivot
1. Assicurati di avere sempre più di un foglio di calcolo nella tua cartella di lavoro collegato / correlato a un altro foglio di calcolo attraverso un campo unico comune, come LicenseNumber per il nostro esempio.
2. In secondo luogo, assicurarsi che la tabella Master abbia solo UN numero di licenza per record, che può quindi connettersi a più tabelle slave che dispongono di più record LicenseNumber. Questa è chiamata una relazione uno-a-molti.
3. Assicurati di aver selezionato e definito tutti i fogli di lavoro della tua cartella di lavoro come tabelle e dati loro ciascun nome univoco.
4. Quando si impostano e si definiscono le relazioni tra tabelle, i formati di campo per entrambe le tabelle correlate devono essere uguali. Ad esempio, i formati di data devono corrispondere: non è possibile mescolare '12 / 25/2018 'e' Dec 25, 2018. '
Fogli Microsoft Excel vs Google: i 4 modi principali I fogli battono Excel
Il piccolo servizio di foglio elettronico di Google potrebbe effettivamente essere la scelta migliore se il tuo lavoro ha bisogno di giocare ai suoi punti di forza.
Fogli Microsoft Excel vs Google: i 5 modi in cui Excel batte beatamente Fogli
Fogli Google è migliorato, ma per molti foglio di calcolo, non c'è nient'altro che Microsoft Excel. Ecco perché fai leva su questo venerabile programma con mani fredde e morte.
