Report in stile tabella pivot con le formule

Una tabella pivot organizza i dati in una tabella a doppia entrata: le righe rappresentano una categoria, le colonne un'altra e i totali riempiono la griglia. Un esempio classico prevede le Regioni sul lato sinistro, i Trimestri in alto e le Vendite in ogni cella.

Le tabelle pivot sono ottime, ma richiedono un aggiornamento manuale e occupano un blocco fisso. Una tabella pivot basata su formule si ricostruisce in tempo reale ogni volta che cambiano i dati.

In questa lezione disporrà le intestazioni di riga, le intestazioni di colonna e un corpo di formule SUMIFS che calcoleranno automaticamente ogni intersezione.

Utilizzeremo un foglio denominato Sales con queste colonne: Regione in A, Trimestre in B e Importo in C, nelle righe dalla 2 alla 500.

Il report che vogliamo creare è strutturato così:

• Etichette di riga: ogni Regione distinta nella colonna E.
• Etichette di colonna: Q1, Q2, Q3, Q4 nella riga 1, da F a I.
• Corpo: l'Importo totale per ogni coppia Regione-Trimestre.

Ogni cella del corpo risponde a una domanda: quanto ha venduto questa regione in questo trimestre?

Le intestazioni di riga sono le regioni distinte. Usi UNIQUE insieme a SORT per espanderle verso il basso nella colonna E e mantenerle ordinate.

Inserisca questa formula in E2:

Le regioni ora riempiono autonomamente E2 e le celle sottostanti. Come nelle tabelle riepilogative, questo elenco è il punto di riferimento dell'intera griglia.

Le intestazioni di colonna sono i trimestri distribuiti lungo una riga. Può inserire manualmente Q1, Q2, Q3, Q4 oppure disporli orizzontalmente usando TRANSPOSE insieme a UNIQUE.

Inserita in F1, questa formula dispone i trimestri distinti lungo la parte superiore:

TRANSPOSE trasforma un elenco verticale in uno orizzontale, quindi una colonna di trimestri diventa una riga di intestazioni. Ora entrambi gli assi della griglia sono pronti.

Ora riempia il corpo della tabella. Ogni cella deve contenere il totale relativo alla regione della propria riga e al trimestre della propria colonna. SUMIFS gestisce facilmente due condizioni.

Nella prima cella del corpo, F2, scriva:

Questa formula legge l'Importo quando Regione è uguale all'etichetta a sinistra e Trimestre è uguale all'intestazione in alto. Rappresenta una singola intersezione della tabella pivot.

I simboli del dollaro permettono di copiare una sola formula nell'intera griglia. Esamini i riferimenti misti:

• $E2 blocca la colonna E ma lascia variare la riga, così ogni riga legge la propria regione.
• F$1 blocca la riga 1 ma lascia variare la colonna, così ogni colonna legge il proprio trimestre.
• $C$2:$C$500 è completamente bloccato perché l'intervallo dei dati non deve spostarsi.

Copi F2 in tutte le colonne dei trimestri e in tutte le righe delle regioni: ogni cella si adatterà autonomamente in modo corretto.

Dopo aver scritto correttamente F2, la selezioni e trascini il quadratino di riempimento verso destra, attraverso le colonne dei trimestri, quindi verso il basso, attraverso le righe delle regioni. Excel riscriverà automaticamente le parti relative.

• La cella G2 diventa Regione $E2 e Trimestre G$1.
• La cella F3 diventa Regione $E3 e Trimestre F$1.

Il risultato è una tabella a doppia entrata completa, con il totale di ogni intersezione. Non serve alcuna procedura guidata per le tabelle pivot e il risultato si ricalcola non appena cambiano i dati di Sales.

Una vera tabella pivot mostra i totali complessivi. Aggiunga una colonna Totale a destra e una riga Totale in fondo, usando una semplice SUM per ogni riga o colonna.

Per il totale di riga della prima regione, inserisca questa formula nella colonna dopo l'ultimo trimestre:

Per ottenere un totale di colonna, sommi le celle del corpo relative a quel trimestre lungo le righe. Questi totali ai margini completano il report e permettono ai lettori di verificare rapidamente la coerenza dei numeri.

Se lo strumento che utilizza lo supporta, può evitare di copiare la formula inserendo direttamente i riferimenti agli intervalli espansi in SUMIFS. Usi le intestazioni espanse come criteri.

Questa singola formula calcola il totale di ogni intersezione tra regione e trimestre:

Qui E2# è l'elenco verticale delle regioni e F1# è l'elenco orizzontale dei trimestri. Excel li combina in una griglia completa in un'unica operazione. Il metodo con trascinamento è più compatibile, ma questa è l'elegante versione moderna.

I report diventano più significativi quando mostrano la percentuale, non soltanto gli importi. Aggiunga una colonna che esprima il totale di ogni regione come percentuale del totale complessivo.

Se il totale della riga della regione è in J2 e il totale complessivo è in J10, scriva:

Bloccando il totale complessivo con $J$10, potrà copiare la formula verso il basso per tutte le regioni, dividendolo sempre per lo stesso denominatore. Formatti la colonna come percentuale per mostrare immediatamente ai lettori quali regioni sono predominanti.

Alcune buone abitudini mantengono affidabile una tabella pivot basata su formule:

• Faccia riferimento a intervalli ampi e completi, come le righe dalla 2 alla 500, in modo da includere le nuove righe.
• Blocchi gli intervalli dei dati con ancoraggi $ completi; solo i riferimenti alle intestazioni devono variare.
• Lasci spazio vuoto sotto e a destra, così le intestazioni e i totali espansi avranno spazio sufficiente.

Se realizzato correttamente, questo report non richiede alcuna manutenzione. Inserisca nuove vendite e la griglia, i totali e le etichette si aggiorneranno autonomamente.

Verifichi la sua comprensione dei riferimenti misti alla base di una tabella pivot basata su formule.

Ha ricreato una tabella pivot usando soltanto formule:

• UNIQUE insieme a SORT ha creato le intestazioni di riga in una colonna espansa.
• TRANSPOSE ha distribuito le intestazioni di colonna lungo una riga.
• SUMIFS con i riferimenti misti $E2 e F$1 ha riempito ogni intersezione, trascinando la formula oppure usando riferimenti agli intervalli espansi come E2# e F1#.
• SUM ha aggiunto i totali complessivi ai margini.

L'intera griglia si ricalcola in tempo reale. Nella prossima lezione renderà interattivo il dashboard con elenchi a discesa che controlleranno le metriche.