Come utilizzare PIVOTPER in Excel

La funzione PIVOTPER ci permette di creare una tabella pivot in Excel con una sola formula, raggruppando i dati sia per righe sia per colonne. È la "sorella" di RAGGRUPPAPER, che raggruppa solo per righe ed è un'alternativa davvero interessante alle tabelle pivot classiche.

Questa funzione è compatibile solo con Excel 365, quindi se hai una versione desktop precedente puoi seguire la guida utilizzando la versione (gratuita) di Excel per il web. Insieme a PIVOTPER sono arrivate anche RAGGRUPPAPER e PERCENTUALE.DI, che useremo più avanti.

Diciamo che PIVOTPER è una funzione particolare perchè unisce una delle funzionalità più conosciute di Excel in assoluto, cioè le tabelle pivot, alla facilità di utilizzo delle matrici dinamiche. Se un tempo avevamo bisogno di funzioni veramente complesse per pescare informazioni da una pivot (come INFO.DATI.TAB.PIVOT o le funzioni cubo), adesso possiamo gestirle esattamente come tutte le altre formule in Excel.

Fra PIVOTPER e RAGGRUPPAPER personalmente preferisco RAGGRUPPAPER perchè è più semplice gestire file "lunghi", con tante righe ma poche colonne, rispetto a file "larghi" con tante colonne di dati. Riconosco però che PIVOTPER possa essere molto comoda, soprattutto per visualizzazioni rapide.

Che cosa fa la funzione PIVOTPER in Excel

Come RAGGRUPPAPER, anche PIVOTPER è una funzione stile "coltellino svizzero", con parecchi argomenti al suo interno. Gli argomenti obbligatori sono solo quattro, gli altri servono per personalizzare totali, ordinamento e filtri:

=PIVOTPER(campi_riga;campi_col;valori;funzione;[intestazioni_campo];[totale_profondità_riga];[ordinamento_riga];[totale_profondità_col];[ordinamento_col];[matrice_filtro];[relativo_a])

Vediamo quindi i singoli parametri:

• campi_riga sono i campi delle righe, cioè i dati che vogliamo vedere sulle singole righe, come nell'area Righe di una tabella pivot
• campi_col sono i campi delle colonne ed è la vera differenza rispetto a RAGGRUPPAPER: i dati inseriti qui diventano le intestazioni delle colonne
• valori sono i valori da utilizzare per i calcoli, come faresti in pivot
• funzione è il calcolo da fare, scelto tra le stesse funzioni di RAGGRUPPAPER (SOMMA, MEDIA, CONTA.VALORI, PERCENTUALE.DI e così via) oppure una LAMBDA personalizzata
• intestazioni_campo indica se i dati hanno intestazioni e se mostrarle: 0 = no, 1 = sì ma non visualizzarle, 2 = no ma creale, 3 = sì e visualizzale. Se lo lasci vuoto, Excel prova a capirlo da solo
• totale_profondità_riga indica i totali delle righe: 0 = nessun totale, 1 = totale complessivo, 2 = totale complessivo e subtotali, -1 e -2 per averli in alto
• ordinamento_riga è l'ordinamento delle righe. Il numero indica la colonna su cui ordinare, contando prima le colonne di campi_riga e poi quelle di valori; se è negativo, l'ordine è decrescente
• totale_profondità_col e ordinamento_col funzionano allo stesso modo, ma per le colonne
• matrice_filtro permette di filtrare i dati di partenza con una condizione, come faremmo con FILTRO
• relativo_a serve solo con funzioni come PERCENTUALE.DI e indica rispetto a quale totale calcolare: 0 = totali delle colonne (predefinito), 1 = totali delle righe, 2 = totale complessivo, 3 e 4 = totali della colonna o della riga "padre"

Le funzioni disponibili sono le stesse 15 di RAGGRUPPAPER, che trovi elencate nella guida dedicata. Il vantaggio è sempre lo stesso: scriviamo direttamente il nome della funzione (SOMMA) anziché un numero come succedeva con SUBTOTALE o AGGREGA.

Attenzione anche a una tentazione tipica di PIVOTPER - se usi questa funzione, non può diventare automaticamente una tabella (visto che è una matrice dinamica) e neanche una pivot, sono proprio dei temi separati.

Come logica, in genere io in tabelle pivot ho l'ordine righe > valori > colonne > filtri, qui è un attimo ribaltato ma ti abitui abbastanza velocemente - devi solo impostare prima le colonne.

Un caso pratico per utilizzare PIVOTPER in Excel

Il nostro database di partenza si chiama TBL_NOLEGGI_FILM e contiene i noleggi di un anno di una piattaforma di streaming di film: ogni riga indica quante volte un film è stato noleggiato in un giorno, in una certa qualità e da clienti di una certa città. Le colonne sono:

• DATA: la data del noleggio
• FILM, ANNO e GENERE: il titolo del film, l'anno di uscita e il genere
• QUALITÀ: HD, Full HD o 4K
• CITTÀ: la città del cliente (Milano, Roma, Torino o Napoli)
• NOLEGGI e IMPORTO: il numero di noleggi e l'importo incassato
• TRIMESTRE: una colonna calcolata che ricava il trimestre dalla data

I film sono reali, i noleggi invece sono inventati per l'esempio. La colonna TRIMESTRE ci servirà più avanti e usa questa formula:

="T"&ARROTONDA.PER.ECC(MESE([@DATA])/3;0)

Praticamente la formula aggiunge T e poi un numero variabile fra 1 e 4, generato arrotondando per eccesso il mese ricavato dalle singole date.

Le domande cui risponderemo saranno:

• Qual è l'incasso per genere e qualità?
• Come togliere i totali e come ordinare i generi da quello che incassa di più a quello che incassa di meno?
• Quali sono gli incassi per genere e film, con i subtotali per genere?
• Quanto pesa ogni genere sugli incassi di ogni città?
• Quanti noleggi ha ogni film in ogni trimestre?
• Come filtrare i risultati per città, per città e trimestre insieme?
• Come calcolare somma e media nella stessa formula?
• Quali film vengono noleggiati in ogni città, per ogni genere?

Cominciamo!

Come creare una tabella pivot con PIVOTPER in Excel

Vediamo il caso più semplice: l'incasso per genere (sulle righe) e per qualità (sulle colonne).

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];
TBL_NOLEGGI_FILM[QUALITÀ];
TBL_NOLEGGI_FILM[IMPORTO];
SOMMA)

In questo caso abbiamo definito che:

• i generi sono gli elementi che vogliamo visualizzare nelle righe
• le qualità di riproduzione diventano le colonne
• l'importo è il valore da usare per i calcoli
• il calcolo è la somma

Senza altri argomenti, PIVOTPER aggiunge in automatico la colonna e la riga dei totali. Il risultato è identico a una tabella pivot con GENERE nelle righe, QUALITÀ nelle colonne e la somma di IMPORTO nei valori: 7.015,80 € in totale, con Sci-Fi come genere che incassa di più in assoluto.

Occhio ad allineare le intestazioni, visto che in questo caso si nota come siano testi (allineati a sinistra) mentre i numeri sono allineati, correttamente, a destra.

Come togliere i totali con PIVOTPER in Excel

Se non ci interessano i totali, basta indicarlo nei due argomenti dedicati, uno per le righe e uno per le colonne:

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[QUALITÀ];TBL_NOLEGGI_FILM[IMPORTO];SOMMA;0;0;;0)

Il primo 0 è field_headers (niente intestazioni), il secondo è row_total_depth (niente totale delle righe). Poi lasciamo vuoto row_sort_order e mettiamo 0 anche in col_total_depth, così sparisce anche la colonna dei totali.

In genere i totali vengono tolti se devi fare operazioni che richiamano questi numeri, perchè semplificano ciò che stiamo realizzando ed evitano di creare nuove righe o colonne.

Come ordinare i dati con PIVOTPER in Excel

Di default i generi sono in ordine alfabetico.

E se invece li volessimo da quello che incassa di più a quello che incassa di meno?

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[QUALITÀ];TBL_NOLEGGI_FILM[IMPORTO];SOMMA;0;1;-2)

La parte ;0;1;-2 si traduce così:

• 0 sono i field_headers: non ci interessa visualizzare le intestazioni
• 1 è row_total_depth: vogliamo solo il totale complessivo. Caso tipico questo, per evitare troppe sovrapposizioni
• -2 vuol dire ordinare per la seconda colonna in ordine decrescente. La prima colonna è GENERE (row_fields), la seconda è IMPORTO (values), quindi ordiniamo per incasso totale

Sci-Fi passa in cima con 1.540,98 €, seguito da Action e Crime. Rispetto a una tabella pivot non dobbiamo aprire nessun menu - l'ordinamento è scritto nella formula e resta valido anche quando arrivano nuovi dati.

Come creare subtotali con PIVOTPER in Excel

Alziamo la complessità: vogliamo vedere genere e film sulle righe, con il subtotale di ogni genere.

Come per RAGGRUPPAPER, usiamo STACK.ORIZ per passare due colonne come row_fields:

=PIVOTPER(STACK.ORIZ(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[FILM]);TBL_NOLEGGI_FILM[QUALITÀ];TBL_NOLEGGI_FILM[IMPORTO];SOMMA;0;2;-3)

Le differenze rispetto a prima sono due:

• row_total_depth vale 2, quindi oltre al totale complessivo abbiamo i subtotali di ogni genere
• l'ordinamento è -3, perché ora le colonne di row_fields sono due (GENERE e FILM) e IMPORTO è la terza

Il risultato è ordinato su due livelli: prima i generi per incasso, poi i film all'interno di ogni genere. Sci-Fi è in cima e al suo interno Alien (674,61 €) è davanti a Interstellar e Inception. La riga con il genere e senza film è il subtotale, mentre l'ultima riga è il totale complessivo.

Attenzione in particolare a lasciare -2 anzichè -3: i dati sarebbero in ordine per il nome del film in ordine decrescente, non ha molto senso.

Come analizzare i dati con PIVOTPER in Excel

Passiamo ora a due analisi tipiche dei report: il peso percentuale di ogni genere e l'andamento dei noleggi nel tempo.

Come calcolare le percentuali con PIVOTPER in Excel

Una delle novità arrivate con PIVOTPER e RAGGRUPPAPER è la funzione PERCENTUALE.DI, che calcola il peso di ogni valore sul totale.

Vogliamo sapere quanto pesa ogni genere sugli incassi di ogni città:

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[CITTÀ];TBL_NOLEGGI_FILM[IMPORTO];PERCENTUALE.DI)

Senza indicare relative_to, le percentuali sono calcolate sul totale di ogni colonna: ogni città somma al 100%. Vediamo così che a Torino Animation pesa il 25,9% degli incassi, mentre a Milano il genere più forte è Action con il 30,0%. Se volessimo invece il peso di ogni città all'interno di un genere, basterebbe usare 1 come ultimo argomento (totali delle righe).

Ricordati di formattare le celle come percentuale: PIVOTPER restituisce numeri decimali - la formattazione resta una scelta nostra.

Come analizzare i dati per trimestre con PIVOTPER in Excel

Passiamo ai noleggi: quante volte è stato noleggiato ogni film in ogni trimestre?

Qui ci torna utile la colonna TRIMESTRE che abbiamo calcolato nella tabella:

=PIVOTPER(TBL_NOLEGGI_FILM[FILM];TBL_NOLEGGI_FILM[TRIMESTRE];TBL_NOLEGGI_FILM[NOLEGGI];SOMMA;0;1;-2)

I film sono ordinati per noleggi totali, con The Dark Knight in testa (169 noleggi). Nota le celle vuote: Titanic e Inception non hanno noleggi nel T4. PIVOTPER lascia la cella vuota invece di scrivere 0, proprio come una tabella pivot.

Ti consiglio di preparare colonne come TRIMESTRE direttamente nella tabella di partenza: la formula PIVOTPER resta più leggibile e la stessa colonna può servire anche per altre analisi.

Come filtrare i dati con PIVOTPER in Excel

Come facciamo a vedere solo i dati di una città? Usiamo filter_array, inserendo la città nella cella C2:

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[QUALITÀ];TBL_NOLEGGI_FILM[IMPORTO];SOMMA;0;1;-2;1;;TBL_NOLEGGI_FILM[CITTÀ]=C2)

Gli argomenti dopo SOMMA sono:

• nessuna intestazione (0)
• totale complessivo delle righe (1)
• ordinamento decrescente per incasso (-2)
• totale delle colonne (1)
• nessun ordinamento particolare per le colonne (vuoto)
• la condizione TBL_NOLEGGI_FILM[CITTÀ]=C2

Per Milano l'incasso totale è 1.390,46 €, con Action al primo posto. Cambiando la città nella cella gialla, tutta la tabella si aggiorna.

Come filtrare con più condizioni in PIVOTPER

E se volessimo filtrare per città e trimestre insieme?

Le condizioni vanno tra parentesi e moltiplicate, come con FILTRO:

=PIVOTPER(TBL_NOLEGGI_FILM[FILM];TBL_NOLEGGI_FILM[QUALITÀ];TBL_NOLEGGI_FILM[IMPORTO];SOMMA;0;1;-2;1;;(TBL_NOLEGGI_FILM[CITTÀ]=C2)*(TBL_NOLEGGI_FILM[TRIMESTRE]=C3))

Ogni condizione genera una matrice di VERO e FALSO; moltiplicandole otteniamo 1 solo dove entrambe sono vere. Qui abbiamo messo i film sulle righe: a Roma, nel quarto trimestre, il film che incassa di più è Gladiator con 43,89 €: chiaramente qui compaiono solo i cinque film che hanno avuto almeno un noleggio.

Ricordati che VERO = 1 e FALSO = 0 in Excel, quindi restituisce solo casi nei quali abbiamo VERO per città = C2 e VERO per trimestre = C3. Tutte le altre combinazioni non sono considerate.

Come fare calcoli personalizzati con PIVOTPER in Excel

Come fare più calcoli con PIVOTPER in Excel

Vogliamo vedere, per ogni genere e qualità, sia l'incasso totale sia l'importo medio per riga del database.

Come in RAGGRUPPAPER, usiamo STACK.ORIZ anche per le funzioni:

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[QUALITÀ];TBL_NOLEGGI_FILM[IMPORTO];STACK.ORIZ(SOMMA;MEDIA);0;1;;0)

Per ogni qualità avremo due colonne, SOMMA e MEDIA, con una seconda riga di intestazione che indica il calcolo. Abbiamo messo 0 in col_total_depth per evitare le colonne dei totali, che con due calcoli diventerebbero altre due colonne: la tabella si allarga in fretta, quindi conviene tenere solo quello che serve davvero.

Come elencare i valori di ogni gruppo con PIVOTPER in Excel

Chiudiamo con un caso che le tabelle pivot classiche non sanno risolvere: invece di un numero, vogliamo vedere in ogni cella quali film sono stati noleggiati, per ogni genere e città. Qui serve una LAMBDA come calcolo:

=PIVOTPER(TBL_NOLEGGI_FILM[GENERE];TBL_NOLEGGI_FILM[CITTÀ];TBL_NOLEGGI_FILM[FILM];LAMBDA(x;TESTO.UNISCI(", ";VERO;DATI.ORDINA(UNICI(x))));0;0;;0)

Vediamo come funziona:

• values è la colonna FILM, quindi la LAMBDA riceve l'elenco dei film noleggiati in ogni combinazione di genere e città, con un elemento per ogni riga del database. Questi sono chiaramente dei valori testuali
• UNICI(x) toglie i duplicati, perché lo stesso film compare in tante righe
• DATI.ORDINA mette i titoli in ordine alfabetico
• TESTO.UNISCI li unisce in un'unica cella, separati da virgola e spazio
• gli ultimi argomenti (0;0;;0) tolgono intestazioni e totali, che con un elenco di testi non avrebbero senso

Il risultato si legge a colpo d'occhio: a Milano, Napoli e Torino sono stati noleggiati tutti i film, mentre a Roma mancano The Godfather tra i Crime e Inception tra i Sci-Fi. Con una tabella pivot classica avremmo dovuto mettere i film nelle righe e cercare le celle vuote - qui invece l'informazione è già scritta.

PIVOTPER o tabella pivot: quale usare

Le due soluzioni convivono, dipende molto da cosa devi fare:

• PIVOTPER si aggiorna da sola quando cambiano i dati, senza dover cliccare su Aggiorna
• la logica è tutta scritta nella formula, quindi è facile da controllare, copiare e riutilizzare
• filtri, ordinamenti e calcoli personalizzati con LAMBDA sono più flessibili
• la tabella pivot resta più comoda per esplorare i dati trascinando i campi, per usare i filtri dei dati e per lavorare con il modello dati
• PIVOTPER funziona solo con Excel 365: se il file deve essere aperto con versioni precedenti, la tabella pivot è ancora la scelta più sicura

Per utenti meno esperti, consiglio assolutamente le tabelle pivot: veloci, rapide e semplici da realizzare. Per chi invece vuole avere nuovi strumenti a disposizione ed evitare il proliferare di pivot (belle ma, ahimè, spesso confusionarie) esplorare PIVOTPER può essere un'ulteriore freccia al tuo arco.

Conclusioni

Abbiamo visto come PIVOTPER permetta di creare vere e proprie tabelle pivot con una formula, aggiungendo totali, subtotali, percentuali, filtri e calcoli personalizzati senza passaggi manuali.

Se usi già RAGGRUPPAPER, il passaggio a PIVOTPER è immediato - non ti resta che scaricare il file, cambiare città e trimestre nelle celle indicate e provare a ricreare con PIVOTPER una delle tabelle pivot che usi ogni giorno!