Esempi di documenti con tabelle Excel

La tabella pivot è forse lo strumento più utile in Excel. Con il suo aiuto, ci sono ampie opportunità per analizzare grandi quantità di dati e calcoli veloci.

Cosa sono le tabelle pivot in Excel? Tabelle pivot in Excel per manichini

Le tabelle pivot sono uno strumento di Excel per riepilogare e analizzare grandi quantità di dati.

Supponiamo di avere una tabella delle vendite di 1000 righe per cliente per l'anno:

La tabella mostra:

Date degli ordini;
La regione in cui si trova il cliente;
Tipo di cliente;
Cliente;
Numero di vendite;
Reddito;
Profitto.

Ora, immaginiamo che il nostro manager abbia impostato le attività da calcolare:

PRIMI cinque clienti per fatturato;

È possibile utilizzare varie funzioni e formule per trovare la risposta a queste domande. Ma cosa succede se non ci sono tre, ma trenta attività per questi dati? Ogni volta che devi cambiare formule e funzioni e regolare per ogni tipo di calcolo.

Questo è solo il caso in cui lo strumento Tabella pivot diventerà un assistente indispensabile per te. Con il suo aiuto, puoi rispondere a qualsiasi domanda in pochi secondi, secondo i dati della tabella.

Spero che tu abbia capito, nell'esempio sopra, quanto sia utile e necessaria una tabella pivot. Scopriamo come usarli.

Come creare una tabella pivot in Excel

Per creare una tabella pivot in Excel, segui questi passaggi:

Seleziona una qualsiasi cella della tabella con i dati in base ai quali vuoi creare una tabella pivot;
Fare clic sulla scheda "Inserisci" \u003d\u003e "Tabella pivot":

Nella finestra di dialogo a comparsa, il sistema determinerà automaticamente i limiti dei dati in base ai quali è possibile creare una tabella pivot. Questo metodo funziona di default nella maggior parte dei casi. Ti consiglio ogni volta che crei una tabella pivot, assicurati che il sistema abbia definito correttamente i parametri per la sua creazione:
- Tabella o intervallo: il sistema rileva automaticamente i limiti dei dati per creare una tabella pivot. Saranno corretti a condizione che non ci siano spazi nelle intestazioni e nelle righe nella tabella. È possibile regolare l'intervallo di dati secondo necessità.
Il sistema crea una tabella per impostazione predefinita in una nuova scheda del file Excel. Se vuoi crearlo in un punto specifico su un foglio specifico, puoi specificare i confini da creare nella colonna "Su foglio esistente".

Fare clic su "OK".

Dopo aver fatto clic sul pulsante "OK", verrà creata la tabella pivot.

Una volta creata la tabella pivot, non vedrai alcun dato sul foglio. Tutto ciò che sarà disponibile è il nome della tabella pivot e il menu per la selezione dei dati da visualizzare.

Ora, prima di iniziare ad analizzare i dati, propongo di capire cosa significano ogni campo e area della tabella pivot.

Aree della tabella pivot in Excel

Per utilizzare le tabelle pivot in modo efficace, è importante sapere a fondo come funzionano e in cosa consistono.

Scopri di più sulle aree seguenti:

Cache della tabella pivot
Area valori
Area archi
Area colonne
Area filtri

Cos'è la cache della tabella pivot

Quando si crea una tabella pivot, Excel crea una cache (un buffer di dati intermedio ad accesso rapido contenente le informazioni che è più probabile che vengano richieste) dei dati da cui verrà creata la tabella.

Quando si eseguono calcoli sulla tabella creata, Excel non fa riferimento ogni volta ai dati originali, ma utilizza le informazioni dalla cache. Questa funzione riduce in modo significativo la quantità di risorse di sistema spese per l'elaborazione e l'elaborazione dei dati.

È importante ricordare che la cache dei dati aumenta le dimensioni del file Excel.

Area valori

L'area Valori contiene tutti i valori della tabella e può essere visualizzata come componente principale della tabella pivot. Immagina di voler visualizzare le vendite delle regioni per mese (dall'esempio all'inizio dell'articolo) utilizzando una tabella pivot. Valori ombreggiati giallo nell'immagine sotto e sono i valori che specifichiamo in tabella pivot nell'area Valori.

Nell'esempio sopra, abbiamo creato una tabella pivot che mostra i dati di vendita per regione, suddivisi per mese.

Area archi

Le intestazioni di tabella a sinistra dei valori sono righe. Nel nostro esempio, questi sono i nomi delle regioni. Nello screenshot qui sotto, le linee sono evidenziate in rosso:

Area colonne

Le intestazioni nella parte superiore dei valori della tabella sono chiamate "Colonne".

Nell'esempio sotto, i campi “Colonne” sono evidenziati in rosso, nel nostro caso questi sono i valori dei mesi.

Area filtri

L'area "Filtri" è facoltativa e consente di impostare il livello di dettaglio per i dati della tabella pivot. Ad esempio, possiamo specificare i dati "Tipo di cliente" - "Negozio di alimentari" come filtro ed Excel visualizzerà i dati nella tabella relativi solo ai negozi di alimentari.

Tabelle pivot in Excel. Esempi di

Negli esempi seguenti, vedremo come utilizzare le tabelle pivot per rispondere a tre domande dall'inizio di questo articolo:

Qual è il volume delle entrate per la regione del Nord nel 2017?;
PRIMI cinque clienti per fatturato;
Qual è la posizione del client IP Ludnikov nella regione di Vostok in termini di entrate?

Prima di analizzare i dati, è importante decidere come dovrebbero apparire i dati della tabella (quali dati contrassegnare in colonne, righe, valori, filtri). Ad esempio, se dobbiamo visualizzare i dati sulle vendite dei clienti per regione, dovremmo inserire i nomi delle regioni in righe, i mesi in colonne, i valori delle vendite nel campo "Valori". Una volta che hai un'idea di come vedi la tabella pivot finale, inizia a crearla.

La finestra "Campi tabella pivot" contiene aree e campi con valori per il posizionamento nella tabella pivot:

I campi vengono creati in base ai dati originali utilizzati per la tabella pivot. La sezione Aree è dove si posizionano i campi e, in base all'area in cui verrà posizionato il campo, i dati vengono aggiornati nella tabella pivot.

Lo spostamento dei campi da un'area all'altra è una comoda interfaccia in cui, mentre ci si sposta, i dati nella tabella pivot vengono aggiornati automaticamente.

Ora proviamo a rispondere alle domande del manager dall'inizio di questo articolo con gli esempi seguenti.

Esempio 1. Quante entrate ha la regione del Nord?

Per calcolare il volume delle vendite della regione Nord, suggerisco di inserire i dati di vendita di tutte le regioni nella tabella pivot. Per questo abbiamo bisogno di:

creare una tabella pivot e spostare il campo "Regione" nell'area "Righe";
posizionare il campo "Entrate" nell'area "Valori"

Otterremo la risposta: le vendite della regione del Nord sono 1 233 006 966 ₽:

Esempio 2. PRIMI cinque clienti per vendite

nella tabella pivot, spostare il campo “Cliente” nell'area “Righe”;
posizionare il campo “Fatturato” nell'area “Valori”;
imposta un formato numerico finanziario per eseguire il pivot delle celle della tabella con valori.

Otteniamo la seguente tabella pivot:

Per impostazione predefinita, Excel ordina i dati nella tabella in ordine alfabetico. Per ordinare i dati in base al volume delle vendite, segui questi passaggi:

vai al menu "Ordina" \u003d\u003e "Ordina decrescente":

Di conseguenza, otterremo un elenco ordinato di clienti in base alle entrate.

Esempio 3. Qual è il posto in termini di entrate del cliente Ludnikov IE nella regione di Vostok?

Per calcolare la posizione in base al volume delle entrate del cliente Ludnikov IP nella regione di Vostok, propongo di creare una tabella pivot che mostrerà i dati sulle entrate per regioni e clienti all'interno di questa regione.

Per questo:

posizionare il campo della tabella pivot “Regione” nell'area “Righe”;
mettere il campo “Cliente” nell'area “Stringhe” sotto il campo “Regione”;
imposta il formato del numero finanziario sulle celle con valori.

Non appena si posizionano i campi “Regione” e “Cliente” uno sotto l'altro nell'area “Righe”, il sistema capirà come si desidera visualizzare i dati e suggerirà l'opzione appropriata.

il campo “Fatturato” verrà posizionato nell'area “Valori”.

Di conseguenza, abbiamo ottenuto una tabella pivot, che riflette i dati delle entrate dei clienti all'interno di ciascuna regione.

Per ordinare i dati, segui questi passaggi:

fare clic con il pulsante destro del mouse su una qualsiasi delle righe di dati sulle entrate nella tabella pivot;
vai al menu "Ordina" \u003d\u003e "Ordina decrescente":

Nella tabella risultante, possiamo determinare quale posto occupa il client di Ludnikov IP tra tutti i client della regione di Vostok.

Esistono diverse opzioni per risolvere questo problema. È possibile spostare il campo "Regione" nell'area "Filtri" e inserire i dati di vendita dei clienti nelle righe, riflettendo così i dati sui ricavi solo per i clienti della regione Est.

Se non hai mai utilizzato un foglio di calcolo per creare documenti prima, ti consigliamo di leggere la nostra guida di Excel per i manichini.

Quindi puoi creare il tuo primo foglio di calcolo con tabelle, grafici, formule matematiche e formattazione.

Informazioni dettagliate sulle funzioni e capacità di base processore da tavolo MS Excel. Descrizione degli elementi principali del documento e istruzioni per lavorare con loro nel nostro materiale.

Lavorare con le cellule. Padding e formattazione

Prima di procedere con azioni specifiche, è necessario capire elemento di base qualsiasi documento in Excel. Un file Excel è costituito da uno o più fogli, suddivisi in piccole celle.

Una cella è un componente di base di qualsiasi report, foglio di calcolo o grafico di Excel. Ogni cella contiene un blocco di informazioni. Può essere un numero, una data, una valuta, un'unità di misura o un altro formato di dati.

Per riempire una cella, è sufficiente fare clic su di essa con il puntatore e inserire le informazioni richieste. Per modificare una cella precedentemente riempita, fare doppio clic su di essa.

Figura: 1 - un esempio di riempimento delle celle

Ogni cella del foglio ha il proprio indirizzo univoco. Pertanto, puoi eseguire calcoli o altre operazioni con esso. Quando fai clic su una cella, un campo con il suo indirizzo, nome e formula apparirà nella parte superiore della finestra (se la cella è coinvolta in un calcolo).

Selezioniamo la cella "Quota di azioni". L'indirizzo della sua posizione è A3. Questa informazione è indicata nel pannello delle proprietà che si apre. Possiamo anche vedere il contenuto. Questa cella non ha formule, quindi non vengono visualizzate.

Altre proprietà e funzioni della cella che possono essere utilizzate in relazione ad essa sono disponibili nel menu contestuale. Fare clic sulla cella con il tasto destro del manipolatore. Si aprirà un menu con il quale puoi formattare la cella, analizzare il contenuto, assegnare un valore diverso e altre azioni.

Figura: 2 - menu contestuale di una cella e sue proprietà principali

Ordinamento dei dati

Spesso gli utenti si trovano ad affrontare il compito di ordinare i dati su un foglio in Excel. Questa funzione consente di selezionare e visualizzare rapidamente solo i dati desiderati dall'intera tabella.

Prima che tu sia una tabella già completata (scopriremo come crearla più avanti nell'articolo). Immagina di voler ordinare i dati di gennaio in ordine crescente. Come lo faresti? Una banale riscrittura di un tavolo è un lavoro in più, inoltre, se è voluminoso, nessuno lo farà.

Excel ha una funzione speciale per l'ordinamento. L'utente è tenuto solo a:

Seleziona una tabella o un blocco di informazioni;
Apri la scheda "Dati";
Fare clic sull'icona "Ordina";

Figura: 3 - Scheda "Dati"

Nella finestra che si apre, seleziona la colonna della tabella, sulla quale effettueremo le azioni (Gennaio).
Successivamente, il tipo di ordinamento (stiamo raggruppando per valore) e, infine, l'ordine è crescente.
Conferma l'azione facendo clic su "OK".

Figura: 4 - impostazione dei parametri di ordinamento

I dati verranno ordinati automaticamente:

Figura: 5 - il risultato dell'ordinamento delle cifre nella colonna "gennaio"

Allo stesso modo, puoi ordinare per colore, carattere e altri parametri.

Calcoli matematici

Il vantaggio principale di Excel è la capacità di eseguire automaticamente i calcoli nel processo di compilazione della tabella. Ad esempio, abbiamo due celle con valori 2 e 17. Come inserire il loro risultato nella terza cella senza eseguire i calcoli da soli?

Per fare ciò, è necessario fare clic sulla terza cella in cui verrà inserito il risultato finale dei calcoli. Quindi fare clic sull'icona della funzione f (x) come mostrato nella figura sotto. Nella finestra che si apre, seleziona l'azione che desideri applicare. SUM è la somma, MEDIA è la media e così via. Lista completa le funzioni ei loro nomi nell'editor di Excel possono essere trovati sul sito Web ufficiale di Microsoft.

Dobbiamo trovare la somma di due celle, quindi facciamo clic su "SUM".

Figura: 6 - selezione della funzione "SOMMA"

La finestra degli argomenti della funzione ha due campi: "Numero 1" e "Numero 2". Seleziona il primo campo e fai clic sulla cella con il numero "2". Il suo indirizzo verrà scritto nella stringa dell'argomento. Fare clic su "Numero 2" e fare clic sulla cella con il numero "17". Quindi confermare l'azione e chiudere la finestra. Se devi eseguire calcoli con tre o più caselle, continua a inserire i valori degli argomenti nelle caselle Numero 3, Numero 4 e così via.

Se in futuro il valore delle celle sommate cambia, la loro somma verrà aggiornata automaticamente.

Figura: 7 - il risultato dei calcoli

Creazione di tabelle

Tutti i dati possono essere memorizzati nelle tabelle di Excel. Con l'aiuto della funzione di configurazione e formattazione rapida, è molto facile organizzare un sistema di controllo del budget personale, un elenco di spese, dati digitali per la creazione di report e così via nell'editor.

Le tabelle in Excel hanno la precedenza sull'opzione simile in Word e in altri programmi per ufficio. Qui hai l'opportunità di creare una tabella di qualsiasi dimensione. I dati vengono inseriti facilmente. C'è un pannello delle funzioni per la modifica dei contenuti. Inoltre, tavolo pronto può essere integrato in un file docx utilizzando la consueta funzione copia-incolla.

Per creare una tabella, segui le istruzioni:

Fare clic sulla scheda Inserisci. Sul lato sinistro del riquadro delle opzioni, seleziona Tabella. Se hai bisogno di riepilogare dei dati, seleziona la voce "Tabella pivot";
Usando il mouse, seleziona un posto sul foglio che verrà assegnato per il tavolo. Inoltre puoi inserire la posizione dei dati nella finestra di creazione dell'elemento;
Fare clic su OK per confermare l'azione.

Figura: 8 - creazione di una tabella standard

Per formattare aspetto della lastra risultante, apri il contenuto del costruttore e nel campo "Stile" clicca sul template che preferisci. Se lo desideri, puoi creare il tuo look con una combinazione di colori e una selezione di celle diverse.

Figura: 9 - formattazione della tabella

Il risultato del riempimento della tabella con i dati:

Figura: 10 - tavolo pieno

Per ogni cella della tabella è inoltre possibile personalizzare il tipo di dati, la formattazione e la modalità di visualizzazione delle informazioni. La finestra del designer contiene tutte le opzioni necessarie per un'ulteriore configurazione della piastra, in base alle vostre esigenze.

Aggiunta di grafici / grafici

Per costruire un grafico o un grafico, è necessaria una piastra già pronta, perché i dati grafici saranno basati proprio su informazioni tratte da singole linee o celle.

Per creare un grafico / grafico, hai bisogno di:

Seleziona completamente la tabella. Se è necessario creare un elemento grafico solo per la visualizzazione dei dati alcune cellule, selezionali solo;
Apri la scheda Inserisci;
Nella casella dei grafici consigliati, seleziona l'icona che ritieni possa descrivere meglio visivamente le informazioni tabulari. Nel nostro caso, questo è un grafico a torta 3-D. Spostare il puntatore sull'icona e selezionare l'aspetto dell'elemento;

Allo stesso modo, puoi creare grafici a dispersione, grafici a linee e dipendenze da elementi di tabella. Tutti gli elementi grafici ricevuti possono anche essere aggiunti a documenti di testo Parola.

Ci sono molte altre funzioni nell'editor di fogli di calcolo Excel, tuttavia, le tecniche descritte in questo articolo saranno sufficienti per il lavoro iniziale. Nel processo di creazione di un documento, molti utenti padroneggiano autonomamente opzioni più avanzate. Ciò è dovuto a un'interfaccia user-friendly e intuitiva. ultime versioni programmi.

Video tematici:

La tabella pivot in Excel è un potente strumento di analisi dei dati che ti aiuterà rapidamente:

Preparare i dati per i rapporti;
Calcola vari indicatori;
Dati di gruppo;
Filtra e analizza le metriche di interesse.

E ti fa anche risparmiare un sacco di tempo.

In questo articolo imparerai:

Come creare una tabella pivot;
Come utilizzare una tabella pivot serie temporali di gruppo e valutare i dati in dinamica per anni, trimestri, mesi, giorni ...
Come calcolare una previsione utilizzando una tabella pivot e Forecast4AC PRO;

Per prima cosa, impariamo come creare tabelle pivot.

Per creare una tabella pivot, dobbiamo creare i dati sotto forma di una semplice tabella. Ogni colonna deve avere 1 parametro analizzato. Ad esempio, abbiamo 3 colonne:

Prodotto
Vendite in rubli

E in ogni linea 3 ° parametri sono correlati, ad es. ad esempio, il 02/01/2010, il prodotto 1 è stato venduto per 422 656 rubli.

Dopo aver preparato i dati per la tabella pivot, impostare il cursore alla prima colonna alla prima cella di una semplice tabella, quindi vai al menu "Inserisci" e premere il pulsante "Tabella pivot"

Apparirà una finestra di dialogo in cui:

puoi premere immediatamente il pulsante "OK", e la tabella pivot verrà visualizzata in un foglio separato.
oppure puoi configurare i parametri dell'output dei dati della tabella pivot:

Intervallo con i dati da visualizzare nella tabella pivot;
Dove visualizzare la tabella pivot (su un nuovo foglio o su uno esistente (se si seleziona uno esistente, sarà necessario specificare la cella in cui si desidera posizionare la tabella pivot)).

Fare clic su "OK", la tabella pivot è pronta e visualizzata in un nuovo foglio. Chiamiamo il foglio Pivot.

Sul lato destro del foglio, vedrai i campi e le aree con cui puoi lavorare. È possibile trascinare i campi nelle aree e verranno visualizzati nella tabella pivot sul foglio.
Sul lato sinistro del foglio c'è una tabella pivot.

Ora, tieni premuto il campo "Prodotto" con il pulsante sinistro del mouse e trascinalo su "Nomi righe", "Vendite in rubli". - in "Valori" nella tabella pivot. Pertanto, abbiamo ricevuto il totale delle vendite di merci per l'intero periodo:

Raggruppamento e filtraggio di serie temporali in una tabella pivot

Ora, se vogliamo analizzare e confrontare le vendite dei prodotti per anni, trimestri, mesi, giorni, allora abbiamo bisogno aggiungere i campi pertinenti alla tabella pivot.

Per fare ciò, vai alla scheda "Dati" e dopo la data inserisci 3 colonne vuote. Seleziona la colonna "Prodotto" e fai clic su "Inserisci".

Importantein modo che le colonne appena aggiunte siano all'interno dell'intervallo di una tabella esistente con i dati, quindi non avremo bisogno di rifare il pivot per aggiungere nuovi campi, sarà sufficiente aggiornarlo.

Le colonne inserite sono chiamate "Anno", "Mese", "Anno-Mese".

Ora, in ciascuna di queste colonne, aggiungi la formula appropriata per ottenere il parametro temporale di interesse:

Aggiungi la formula alla colonna "Anno" \u003d ANNO (con riferimento alla data);
Aggiungi la formula alla colonna "Mese" \u003d MESE (con riferimento alla data);
Aggiungi la formula alla colonna "Anno - Mese" \u003d CONCATENATE (collegamento all'anno; ""; collegamento al mese).

Otteniamo 3 colonne con anno, mese e anno e mese:

Ora vai alla scheda "Riepilogo", posiziona il cursore sulla tabella pivot, fai clic con il pulsante destro del mouse sul menu e premere il pulsante "Aggiorna"... Dopo l'aggiornamento nell'elenco dei campi, abbiamo nuovi campi della tabella pivot "Anno", "Mese", "Anno - mese", a cui abbiamo aggiunto tavolo semplice con dati:

Ora analizziamo le vendite per anno.

Per fare ciò, trasciniamo il campo "Anno" nei "nomi delle colonne" della tabella pivot. Otteniamo una tabella con le vendite per prodotto per anno:

Ora vogliamo "scendere" ancora più in profondità al livello dei mesi e analizzare le vendite per anni e per mesi. A tale scopo, trascina il campo del mese sotto l'anno nei "nomi delle colonne":

Analizzare le dinamiche di mese per anno, possiamo spostare i mesi nell'area pivot "Nomi riga" e ottenere la seguente visualizzazione della tabella pivot:

In questa visualizzazione tabella pivot, vediamo:

vendite per ogni prodotto nella quantità per l'intero anno (riga con il nome del prodotto);
più in dettaglio le vendite per ogni prodotto in ogni mese in dinamica per 4 anni.

Prossima sfida, noi vogliamo rimuovere dall'analisi le vendite per un mese (ad esempio, ottobre 2012), perché non disponiamo ancora di dati sulle vendite per un intero mese.
A tale scopo, trascina "Anno - mese" nell'area pivot "Filtro rapporto"

Fare clic sul filtro di riepilogo visualizzato sopra e metti un segno di spunta "Seleziona più elementi"... Quindi, nell'elenco con anni e numeri di mesi, deseleziona 2012 10 e fai clic su OK.

Pertanto, puoi aggiungere nuovi parametri per modificare l'ora e analizzare quei periodi di tempo che ti interessano e nella forma in cui ne hai bisogno. La tabella pivot calcolerà gli indicatori per quei campi e filtri impostati e li aggiungerà come campo di interesse.

Calcolo di Pronoz utilizzando la tabella pivot e Forecast4AC PRO

Costruiamo le vendite utilizzando una tabella pivot per prodotto, per anno e per mese. Disattiveremo anche i totali generali in modo che non siano inclusi nel calcolo.

Per disattivare i totali nella tabella pivot, posizionare il cursore sulla colonna "Totale" e fare clic sul pulsante "Elimina totale". Il totale scompare dal riepilogo.

e premere il pulsante "Grafico modello di previsione" nel menu Forecast4AC PRO

Otteniamo il calcolo della previsione per 12 mesi e un bel grafico con l'analisi del modello previsionale (trend, stagionalità e modello) relativo ai dati effettivi. Forecast4AC PRO può calcolare previsioni, tassi di stagionalità, trend e altri indicatori per te e costruire grafici basati sui dati visualizzati nella tabella pivot.

La tabella pivot in Excel è un potente strumento di analisi dei dati che consente di calcolare rapidamente le metriche e creare dati nella forma che ti interessa in modo rapido e semplice.