La Funzione di matrice dinamica
PIVOTPER di Excel 365
Come ottenere i risultati di una
tabella Pivot applicando una
sola Funzione!
La funzione PIVOTPER di Excel
permette di raggruppare i dati
su più righe e colonne,
aggregando automaticamente i
valori in base a criteri
specifici di calcolo, come somma, media
o conteggio, etc.
Oltre
a questo, PIVOTPER permette
anche di ordinare e filtrare i
dati, consentendo una visione
più dettagliata e mirata delle
informazioni.
Uno
degli aspetti distintivi di
PIVOTPER è il fatto che si
tratta di una funzione matrice
dinamica. Questo significa che,
una volta applicata, i risultati
vengono espansi automaticamente
su tutte le celle necessarie per
contenere i dati elaborati,
senza dover copiare manualmente
la formula su più celle.
Il report che si ottiene con
PIVOTPER è simile a quello
generato con una tabella pivot
tradizionale.
Tuttavia,
a differenza delle tabelle
pivot, il risultato di PIVOTPER
non include una formattazione
predefinita e l'interfaccia
grafica tipica delle tabelle
pivot.
Questo permette di integrare i
risultati direttamente
all'interno del foglio di
calcolo senza la struttura
rigida delle tabelle pivot,
garantendo una maggiore
personalizzazione nella
successiva elaborazione o
formattazione dei dati.
Ulteriore elemento di interesse
è che a differenza di una
tabella pivot tradizionale,
il
risultato di PIVOTPER si
aggiorna automaticamente ogni
volta che vengono apportate
modifiche al set di dati
originale.
:: Nota. PIVOTPER è una funzione molto simile a RAGGRUPPAPER. La differenza fondamentale è che mentre RAGGRUPPAPER può raggruppare solo righe, PIVOTPER può raggruppare dati sia su righe sia su colonne.
In
questa sezione del sito vengono
illustrate le impostazioni di
base della funzione PIVOTPER,
con alcuni esempi pratici. In
futuro, verranno aggiunti
ulteriori esempi specifici per
esplorare in dettaglio le
funzionalità più avanzate di
questa
complessa funzione.
Per garantire una maggiore uniformità e una visione d'insieme più chiara delle informazioni e delle spiegazioni-esempi, si è scelto di mantenere la sezione unita, evitando suddivisioni in più pagine.
PIVOTPER
[1] - Sintassi delle Funzioni PIVOTPER
[2] - Primo esempio. Applicare PIVOTPER. Impostazione di base: Righe,
Colonne e Valori..
[3] - Calcolare i totali. Operatori di calcolo disponibili
nella Funzione PIVOTPER.
[4] - Considerazioni preliminari sulla differenza fra
l'utilizzo della Funzione PIVOTPER e l'impostazione di una tabella Pivot.
[5] - Secondo esempio. Visualizzare totali e subtotali.
[6] - Terzo esempio. Eseguire più calcoli sulla stessa
colonna. Nidificare la Funzione STACK.ORIZ
[7] - Quarto esempio. Ordinamento personalizzato dei dati..
[8] - Quinto esempio. Raggruppare le date anno per anno, oppure mese per mese con la Funzione PIVOTPER
Sintassi delle Funzioni PIVOTPER
=PIVOTPER(row_fields; col_fields;
values; functions: [field_headers];
[row_total_depth];
[row_sort_order];
[col_total_depth];
[col_sort_order]; [filter_array];
[relative_to])
Nella quale.
row_fields :
intervallo di valori da
raggruppare nelle righe.
col_fiels :
intervallo di valori da
raggruppare nelle colonne.
values:
rappresenta i valori da
aggregare.
functions:
l'argomento funzione permette di
calcolare i dati raggruppati,
attraverso un set di Funzioni:
SOMMA, MEDIA, CONTEGGIO, MIN,
MAX...
[field_headers]:
permette di specificare se il
set di dati ha una
intestazioni e se si vuole
includerla nei risultati.
Se omesso, le intestazioni non
vengono mostrate.
Le impostazione previste sono le
seguenti:
0 - Nessuna intestazione
1 - Sì, ma non mostrare le
intestazioni
2 - Nessuna intestazione, ma è
possibile generarla
3 - Sì, e mostra le intestazioni
[row_total_depth];
permette di definire se mostrare
totali delle righe.
Per
generare i subtotali,
l'argomento
row_fields
deve
avere almeno 2 colonne. Le
impostazioni previste per questo
argomento sono le seguenti:
0 - Nessun totale
1
- (impostazione predefinita)
Totale generale in basso
2
- Totali generali e subtotali in
basso
-1
- Totale generale in alto
-2 - Totali generali e subtotali
in alto
[row_sort_order]:
ordinamento per numero di indice
di colonna.
I
numeri corrispondono alle
numerazione delle colonne (da
sinistra a destra), seguita
dalla colonna dei valori
values.
I
numeri positivi organizzano i
dati in
ordine crescente, i numeri
negativi in ordine
decrescente. Per ordinare in
base a più colonne, fornire una
matrice unidimensionale di
numeri.
L'ordinamento impostato
come predefinito è quello
crescente.
[col_total_depth]:
analogamente all'argomento
row_total_depth determina in
questo caso se organizzare dei totali
sulle colonne. Le opzioni di
impostazione sono le seguenti:
0 - Nessun totale
1
- (impostazione predefinita)
Totale generale sul lato destro
2
- Totali generali e subtotali
sul lato destro
-1
- Totale generale sul lato
sinistro
-2
- Totali generali e subtotali
sul lato sinistro
[col_sort_order]:
valore numerico che indica come
ordinare le colonne. I numeri
corrispondono alle colonne inserite
in col_fields seguite dalle colonne
in values . Funziona con la
stessa logica dell'argomento
row_sort_order.
[Filter_array]:
Questo argomento permette
l'impostazione di un filtro per
determinate righe.
Può essere fornito come
espressione logica che genera
una matrice unidimensionale di
valori booleani corrispondenti
alle matrici row_fields e
col_fields.
[Relative_to]:
Quando nell'argomento functions viene applicata la
funzione PERCENTUALE.DI permette
di specificare come deve essere
eseguito il calcolo percentuale:
0 - Totali delle colonne
(predefinito)
1 - Totale riga
2 - Totale generale
3 - Totale colonna padre
4 - Totale riga padre
Applicare PIVOTPER. Impostazione di base: Righe, Colonne e Valori.
La tabella illustrata
nell'immagine sottostante
raccoglie un insieme di dati che
riguardano un centinaio di
operazioni di vendita effettuate
da quattro venditori. Le vendite sono organizzate per regione e cliente.
L'obiettivo dell'analisi è
quello di incrociare i venditori
con le regioni, disponendoli in
righe e colonne, così da
calcolare il fatturato totale
di ciascun venditore in
ogni specifica regione.
Questa impostazione permette di
ottenere una visione dettagliata
delle performance di vendita dei
singoli venditori nelle diverse
aree geografiche in cui operano.
Per ottenere questo risultato
nella cella G4 è stata inserita
la seguente Funzione:
=PIVOTPER(B1:B102;D1:D102;E1:E102;SOMMA)
Nella quale.
Primo argomento row_fields: è stata inserita la
matrice B1:B102 che rappresenta
la voce venditore. (Inclusa la
riga di intestazione).
Secondo argomento col_fields: è
stato inserito il riferimento ai
dati regionali D1:D102. (Inclusa
la riga di intestazione).
Terzo argomento
values: l'intervallo dei
valori che vanno calcolati
ovvero il fatturato. (Inclusa la
riga di intestazione).
Quarto argomento
functions; è stata definita la
funzione di calcolo da applicare
ai valori, nel caso
specifico dell'esempio: la Somma.
Calcolare i totali. Operatori di calcolo disponibili nella Funzione PIVOTPER.
Quando si imposta il quarto
argomento della funzione PIVOTPER, Excel visualizza automaticamente un elenco delle operazioni di calcolo disponibili.
Questo rende molto semplice
scegliere l'operazione di
calcolo desiderata, ad esempio:
SOMMA, PERCENTUALE.DI, MEDIA,
MEDIANA.....
Nell'immagine successiva, si può
vedere il menu a tendina che
appare quando si imposta l'argomento "function" di
PIVOTPER.
Considerazioni preliminari sulla differenza fra l'utilizzo della Funzione PIVOTPER e l'impostazione di una tabella Pivot.
Pur avendo uno scopo comune e risultati molto simili, è utile distinguere tra
l'uso della funzione PIVOTPER e la creazione di una tabella pivot in Excel.
Entrambi strumenti servono per aggregare e organizzare i dati, ma la differenza
principale risiede nella formattazione: la funzione PIVOTPER restituisce dati non
formattati, mentre la tabella pivot offre una presentazione immediatamente
strutturata e facilmente leggibile.
PIVOTPER permette di eseguire operazioni di pivot direttamente nella formula,
generando dati personalizzabili e automaticamente aggiornati se venisse
modificato il set di dati di origine, ideali per calcoli più complessi
senza dover utilizzare un’interfaccia separata.
La tabella pivot, invece, è uno strumento visivo e interattivo, perfetto per
creare report dettagliati con facilità, grazie al trascinamento dei campi.
Anche se offre meno flessibilità per personalizzare calcoli avanzati, la tabella
pivot è pratica per l'analisi rapida di grandi quantità di dati, grazie alla sua
interfaccia intuitiva e già formattata.
Nell'immagine sottostante, il set di dati presenti sul foglio di lavoro sono
stati analizzati sia attraverso la Funzione PIVOPER, sia attraverso una classica
tabella Pivot.
Il risultato restituito è ovviamente il medesimo.
A colpo d'occhio la differenza sostanziale risiede nella mancanza di
formattazione e di struttura della Funzione, rispetto alla classica Tabella Pivot.
Secondo esempio. Visualizzare totali e subtotali
Per includere totali generali e
subtotali nei risultati della
Funzione PIVOTPER, è necessario
impostare gli argomenti
row_total_depth e
col_total_depth.
Ad
esempio, se si desidera
visualizzare i totali e i
subtotali delle righe (nella
parte inferiore) e
nessun totale per le colonne, è
necessario impostare
row_total_depth su 2 e
col_total_depth su 0:
=PIVOTPER(B1:C102;D1:D102;E1:E102;SOMMA;;2;;0)
Questa impostazione produrrà una
tabella riepilogativa in cui
ogni gruppo di righe include un
subtotale nella parte inferiore,
seguito da una riga del totale
generale alla fine, mentre le
colonne non visualizzeranno
alcun totale.
Nell'esempio raffigurato nella
sottostante immagine, è stata
applicata la Funzione di cui
sopra.
Importante osservare che per
ottenere un subtotale di riga è
ovviamente fondamentale inserire
nel primo argomento la selezione
di due colonne (nel caso
specifico venditore e cliente).
Nei tre successivi argomenti sono
stati rispettivamente inseriti:
La colonna del cliente per la
colonne, il fatturato per il
campo valori ed infine la somma
come calcolo.
Per passare all'argomento
[row_total_depth]; che determina
se mostrare totali e subtotali, è
stato inserito due volte lo
stile separatore del punto e
virgola.
Infine,
nell'argomento [col_total_depth]
è stato impostato il valore
zero.
Per comodità si riepilogano i
codici accettati dall'argomento
[row_total_depth]:
0 - Nessun totale
1
- (predefinito) - Totale generale
in basso
2
- Totali generali e subtotali in
basso
-1
- Totale generale in alto
-2 - Totali generali e subtotali
in alto
:: NOTA. Quando si scrive una funzione con argomenti
facoltativi che non devono essere modificati o utilizzati, ma si
desidera passare direttamente a un argomento successivo, è possibile
lasciare vuoti quelli non necessari.
Tuttavia, è importante mantenere la corretta sintassi inserendo
comunque i separatori tra gli argomenti, come il punto e virgola.
Questo garantisce che ciascun argomento occupi la posizione corretta,
anche se alcuni rimangono non valorizzati.
Terzo esempio. Eseguire più calcoli sulla stessa colonna. Nidificare la Funzione STACK.ORIZ
Nell'esempio illustrato nell'immagine seguente,
l'obiettivo è calcolare, per ogni venditore e per ogni regione, sia la
somma totale che il valore medio delle fatture emesse.
Nello specifico: all'interno della Funzione PIVOTPER, nell'argomento
function è stata nidificata la Funzione STACK.ORIZ per calcolare diverse
funzioni, come MEDIA, SOMMA, CONTEGGIO etc.
Questo tipo di impostazione permette di effettuare contemporaneamente
più calcoli sulla voce fatturato.
Con questa impostazione è possibile ottenere sia la media delle vendite
che il numero totale di vendite su due distinte colonne.
Nello specifico la Funzione inserita è la seguente:
=PIVOTPER(B1:B102;D1:D102;E1:E102;STACK.ORIZ(SOMMA;MEDIA))
Nella quale.
Primo argomento row_fields: è stata inserita la
matrice B1:B102 che rappresenta
la voce venditore. (Inclusa la
riga di intestazione).
Secondo argomento col_fields: è
stato inserito il riferimento ai
dati regionali D1:D102. (Inclusa
la riga di intestazione).
Terzo argomento
values: l'intervallo dei
valori che vanno calcolati
ovvero il fatturato. E1:E102
(Inclusa la riga di intestazione).
Quarto argomento: per indicare l'inserimento di due distinte colonne di
calcolo è stata nidificata la funzione STACK.ORIZ i cui argomenti sono le
funzioni che specificatamente devo essere applicate alle due colonne.
:: NOTA. Per applicare diverse funzioni di aggregazione sulle
stesse colonne di valori, è necessario fornire un vettore di funzioni
lambda corrispondenti. Questo può essere ottenuto utilizzando la
funzione STACK.ORIZ
Per un approfondimento su questa funzione:
Le nuove funzioni di matrice dinamica di Excel STACK.VERT e STACK.ORIZ
Quarto esempio. Ordinamento personalizzato dei dati.
Per impostazione predefinita, la funzione PIVOTPER ordina
automaticamente le etichette di riga e colonna in ordine crescente,
dalla A alla Z.
Tuttavia, è possibile personalizzare questo ordinamento utilizzando gli
argomenti
row_sort_order e col_sort_order.
Nell'esempio seguente, è stato impostato un raggruppamento di righe a 2
livelli (per venditore e regione) e un raggruppamento di colonne a un
livello (per settore).
Per impostazione predefinita, tutto (righe e colonne) sarebbero ordinate
in ordine crescente.
Nello specifico, si è voluto impostare un ordinamento decrescente. Nel
caso delle righe la seconda colonna (a parità di valore della prima) è anch'essa
impostata come decrescente
La Funzione impostata nella cella G3 è la seguente:
=PIVOTPER(B1:C102;D1:D102;E1:E102;SOMMA;;0;{-1.-2};0;-1)
Sintassi un po' complessa per meglio chiarire procediamo punto
per punto.
Primo argomento row_fields: riferimento alla matrice relative alle due colonne (Venditore e
Regione) da disporre come intestazioni di righe.
Secondo argomento col_fiels: riferimento alla matrice relativa al settore (da disporre sulle
colonne).
Terzo argomento values: riferimento alla matrice con i valori da calcolare (Fatturato)
Quarto argomento functions: la Funzione da applicare ai valori inseriti nel terzo
argomento (Somma)
Quinto argomento [field_headers]: argomento omesso pertanto inserito solo il punto e
virgola per passare senza alcuna definizione al successivo.
Sesto argomento [row_total_depth]: è stato inserito il valore "0" per impostare nessun
totale
Settimo argomento [row_sort_order]: i numeri rappresentano le prime due colonne, il fatto
che siano negativi vuol dire valore decrescente. Per poter inserire due
definizioni all'interno di un solo argomento sono stati inseriti fra parentesi graffe (inseriti come matrice).
{-1.-2} Attenzione al punto come stile separatore all'interno della
matrice !
Ottavo argomento [col_total_depth]: è stato inserito il valore "0" per impostare nessun
totale
Nono argomento [col_sort_order]: è stato inserito il valore -1 per indicare
l'impostazione decrescente.
Quinto esempio. Raggruppare le date anno per anno, oppure mese per mese con la Funzione PIVOTPER
Nelle tabelle Pivot, quando si lavora con campi di tipo data, è
possibile sfruttare i comandi Raggruppa e Separa che permettono di
aggregare i dati secondo criteri temporali come l'anno, il trimestre o
il mese.
Se si desidera ottenere un risultato simile utilizzando la funzione
PIVOTPER, è possibile nidificare all'interno del secondo argomento le
Funzioni ANNO e MESE.
Queste due funzioni di Excel estraggono rispettivamente il mese e l'anno
da un insieme di date, permettendo così di raggruppare i dati in base a
tali intervalli temporali.
Per esempio, sulla base dei dati illustrati nella sottostante
immagine, supponiamo di voler raggruppare l'intervallo di date contenute
nella colonna A per anno rapportando il fatturato venditore per
venditore.
In questo caso, la sintassi da impostare con PIVOTPER sarebbe la
seguente:
=PIVOTPER(B2:B102;ANNO(A2:A102);D2:D102;SOMMA)
Nella quale:
Primo argomento row_fields: specifica il valore da raggruppare nelle
righe.
Secondo argomento col_fiels: la Funzione ANNO definisce
l'impostazione di aggregazione rispetto alla matrice A2:A102
Terzo argomento values: indica la colonna contenente il valore da calcolare
Quarto argomento functions: specifica la funzione da applicare al valore, ovvero la somma.
Il risultato della Funzione restituisce anno per anno (nelle colonne) il fatturato dei venditori. Alla fine della tabella viene calcolato sempre anno per anno il fatturato complessivo e nell'ultima colonna a destra il fatturato totale.
[Valter Borsato: 02/10/2024 | Aggiornamento: 19/10/24]