Oggi vediamo una delle funzioni matriciali dinamiche più recenti e interessanti: la funzione Raggruppaper, in grado di creare una tabella riassuntiva dinamica con una sola formula.
Le funzioni di Excel
Le formule sono strumenti di calcolo che oltre alle operazioni matematiche, usano funzioni per elaborare valori in celle e tabelle. Le funzioni sono scorciatoie, automazione, programmi che elaborano i parametri inseriti per restituire in modo veloce un risultato complesso.
Le funzioni matriciali elaborano intere matrici di dati per ottenere il risultato che stiamo cercando e possono restituire singoli valori come intere matrici di valori.
Negli ultimi anni in Excel 365 sono state introdotte nuove funzioni matriciali, diverse però dalle formule matriciali classiche.
Le funzioni matriciali dinamiche di Excel
Le funzioni matriciali dinamiche sono riconoscibili per le seguenti caratteristiche:
- espansione automatica: se il risultato della formula o funzione è una matrice, questa verrà estesa in automatico nelle celle necessarie, in basso e destra della cella in cui abbiamo inserito la formula; se nell’intervallo di celle previsto sono presenti celle con valori o formule, la formula restituirà un errore #Espansione e attenderà di essere spostata o che vengano rimosse o spostare le celle che ostacolano la visualizzazione della matrice.
- riferimenti a matrici di dati: i parametri delle funzioni di solito si riferiscono non a singole celle, ma a interi campi, intere tabelle o parti di esse.
- criteri multipli: in presenza di parametri che indicano condizioni, di solito permettono l’impostazione anche di criteri multipli, attraverso l’uso di operatori logici come * (AND) e + (OR), come le formule matriciali classiche.
La Funzione RAGGRUPPAPER in Excel
In questo post parliamo della funzione Raggruppaper, Groupby in inglese, una funzione utile, avanzata e meno complessa di quello che può apparire.
Come dice il nome la funzione Raggruppaper serve ad elaborare e raggruppare dati per categorie o periodi allo scopo di realizzare report dinamici.
Cosa intendo con “raggruppare”?
Intendo mostrare una tabella che mostra l’elenco di una categoria, come per esempio la lista dei clienti, con a fianco un campo calcolato come l’importo delle fatture. Quindi il risultato sarà una tabella dinamica che nella prima colonna mostra l’elenco univoco della categoria o periodo prescelto e nella secondo un valore numerico ottenuto tramite somma, conteggio, eccetera di un campo della basedati di origine a cui abbiamo collegato la formula.
Naturalmente i campi possono essere più di uno e le possibilità di personalizzare la tabella sono diversi. Vediamolo insieme.
Sintassi della funzione raggruppaper
La sintassi di RAGGRUPPAPER() è la seguente:
=RAGGRUPPAPER(row_fields;valori;funzione;[field_headers];[total_depth];[sort_order];[filter_array];[field_relationship])
Come puoi vedere la sintassi prevede numerosi parametri che a prima vista non sembrano semplici. Ma solo in apparenza.
row_fields (obbligatorio)
Matrice o colonna che contiene categorie o periodi usati per raggruppare le righe e generare intestazioni di riga. La matrice può contenere più colonne e in tal caso la tabella risultante avrà più “campi colonna”, cioè elenchi nella o nelle prime colonne sui cui valori verranno raggruppati i valori.
Nel caso tu voglia usare più campi, questi saranno collegati gerarchicamente e i valori verranno mostrati di conseguenza.
In realtà le matrici non devono necessariamente essere verticali, ma possono essere anche orizzontali. Naturalmente i parametri dovranno rispettare tutti lo stesso orientamento oltre che la stessa grandezza. O verrà restituito un errore.
valori (obbligatorio)
Matrice o colonne di dati da aggregare, di solito valori numerici, o in caso contrario di testi o altri che di solito verranno conteggiati. Anche in questo caso la matrice può contenere più colonne e in tal caso la tabella risultante avrà più “campi calcolati”, cioè elenchi nella o nelle ultime colonne a destra.
funzione (obbligatorio)
Stringa che indica l’operazione da applicare al campo calcolato (somma, % di, media, mediana, conta numeri, conta valori, ecc) per aggregare i valori. L’elenco delle operazioni offre molte scelte, tra cui perfino una funzione lamba, cioè un’operazione che puoi impostare tu come preferisci. E’ possibile usare più funzioni? In teoria no, ma con l’uso di altre funzioni (stack.vert, stack.oriz, lamba, ecc) è possibile visualizzare altri calcoli.
field_headers (opzionale)
Richiede un numero che specifica se devono essere visualizzati anche i titoli dei campi. Questo naturalmente se i titoli sono presenti nelle matrici indicate nei parametri precedenti. I valori possibili sono:
0: No
1: Sì e non mostrare
2: No ma generare
3: Sì e mostra
Se non viene indicato il parametro, verrà restituita l’impostazione predefinita, cioè nessun titolo.
total_depth (opzionale)
Richiede un numero che specifica se verrà aggiunta anche una riga dei totali e/o eventuali subtotali e dove. I valori possibili sono:
0: Nessun totale
1: Totali complessivi
2: Totali complessivi e subtotali
-1: Totali complessivi nella parte superiore
-2: Complessivi e Subtotali in alto
Se non viene indicato il parametro, verrà restituita l’impostazione predefinita, cioè i totali in basso.
Per visualizzare i subtotali, devono essere presenti almeno 2 “campi riga” cioè ci devono essere almeno 2 categorie o periodi.
sort_order (opzionale)
Richiede un numero positivo o negativo che indica come verrà ordinata la tabella. I numeri corrispondono alle colonne della tabella (es: 1 la prima colonna), se è positivo l’ordinamento è crescente, altrimenti le righe vengono ordinate in ordine decrescente.
filter_array (opzionale)
Richiede l’inserimento di un’espressione o una funzione che devono restituire vero o falso, o meglio una matrice di vero o falso con cui è possibile filtrare i record della tabella con i dati.
Per esempio: B1:B1000=”Attivo” farà verificare alla funzione il contenuto di ogni cella dell’intervallo B1:B1000 e se trova “Attivo” restuirà VERO, altrimenti FALSO e di conseguenza la funzione nella tabella risultante mostrerà solo i record con VERO. Una nota importante: l’intervallo indicato deve sempre avere la stessa dimensione della matrice “row_field”.
Questo parametro permette di impostare anche condizioni multiple anche complesse, in modo simile alla funzione Filtro, cioè racchiudendo ogni singola condizione tra parentesi e collegandole con operatori logici: “*” (AND) e “+” (OR).
field_relationship (opzionale)
Richiede un numero che indica la relazione tra i “campi riga” quando sono più di 1. I valori possibili sono:
0: Gerarchia (impostazione predefinita)
1: Tabella
Con Gerarchia (0), l’ordinamento delle colonne di campo successive tiene in considerazione la gerarchia delle colonne precedenti. Con Tabella (1), l’ordinamento di ogni colonna di campo viene eseguito in modo indipendente. I subtotali non sono supportati perché si basano sui dati che hanno una gerarchia.
Esempi di utilizzo della funzione Raggruppaper
Adesso vediamo qualche esempio d’uso della funzione raggruppaper. Vista la specificità della funzione, gli esempi saranno piuttosto simili, in primo luogo perché l’output è sempre una tabella che mostra dati raggruppati.
Esempio 1 – l’uso base della funzione raggruppaper
= RAGGRUPPAPER(TabellaFatture[Cliente]; TabellaFatture[Importo]; SOMMA)
In questo semplice esempio la funzione restituisce una tabella a 2 colonne, la prima mostrerà l’elenco dei clienti presenti nella tabella delle fatture e la seconda mostrerà la somma del fatturato per cliente. Questo con la semplice formula qui sopra.
Devo sottolineare che la tabella non presenterà titoli, ma presenterà una riga dei totali in fondo. Sarebbe sempre opportuno visualizzare i titoli e mostrare i totali nella riga sotto i titoli.
Per farlo è sufficiente modificare la formula in questo modo:
=RAGGRUPPAPER(TabellaFatture[[#Tutti];[Cliente]];TabellaFatture[[#Tutti];[Importo]]; SOMMA; 3; -1)
Come puoi vedere, ho ampliato gli intervalli del campo calcolato e del campo riga per comprendere anche i titoli, ho impostato il parametro “field_headers” per mostrare i titoli e il parametro per mostrare il totale in alto. Questo 5 parametri sono quelli più usati e ti invito a usarli sempre.
Esempio 2 – come dovresti usarla
=RAGGRUPPAPER(TabellaVendite[[#Tutti];[Mese]:[Anno]]; TabellaVendite [[#Tutti];[Importo]]; SOMMA; 3; -2)
In questo esempio la funzione restituisce una tabella a 3 colonne, la prima mostrerà l’elenco degli anni, la seconda l’elenco dei mesi e la terza mostrerà l’importo delle vendite per anno e mese.
La tabella quindi mostrerà l’andamento per anno e mese delle vendite in ordine crescente e mostrerà titoli e totali in alto e i subtotali per anno.
Devi usare proprio i riferimenti delle tabelle Excel?
No, dipende dalla basedati a cui colleghi la formula. Se i dati non sono in un tabella excel, ma in una matrica di dati la formula diventa:
=RAGGRUPPAPER(O6:R829; N6:N829; SOMMA; 3; -2)
Ma se la tabella non ha celle con valori sotto e sopra la tabella e le celle sono sempre piene, in Excel 365 potresti scriverla così:
=RAGGRUPPAPER(O.:.R; N.:.N; SOMMA; 3; -2)
Il punto prima e dopo la tabella indica a excel di rimuovere le celle vuote dall’intervallo.
Se invece i dati sono restituiti da una funzione matriciale, per esempio filtro che abbiamo usato per estrarre un periodo preciso dalla basedati, allora la formula potrebbe essere questa
=RAGGRUPPAPER(Scegli.Col(A6#;17;18); Scegli.Col(A6#;14); SOMMA; 3; -2)
Il cancelletto indica a excel tutta la matrice restituita dalla formula presente nella cella A6, mentre la funzione Scegli.Col() permette di indicare solo colonne precise della matrice.
Esempio 3 – filtra la tabella ottenuta
= RAGGRUPPAPER(TabellaFatture[[#Tutti];[Cliente]]; TabellaFatture[[#Tutti];[Importo]]; SOMMA; 3; -2; TabellaVendite[[#Tutti];[Anno]]=$C$4)
In questo esempio la funzione restituisce una tabella a 2 colonne, la prima mostrerà l’elenco dei clienti, la seconda mostrerà il fatturato, ma questa volta la tabella sarà filtrata secondo la condizioni impostata: TabellaVendite[[#Tutti];[Anno]]=$C$4.
La tabella mostrerà la distribuzione del fatturato per cliente dell’anno indicato nella cella C4, ordinato in modo decrescente per i valori e mostrerà titoli e totali in alto. Ti faccio notare che collegando la funzione a una o più celle diverse si può rendere la formula dinamica offrendo agli utenti la possibilità di scegliere da elenchi preimpostati o altri controlli.
Come accennato in precedenza, le condizioni possono essere multiple attraverso l’uso preciso di parentesi e operatori logici (“*” (AND) e “+” (OR)) con cui concatenare le condizioni.
Come dovresti usare la funzione Raggruppaper
In sintesi dovresti usarla sempre almeno come nell’esempio 2 visto più sopra. Quindi dovresti usare i primi 5 parametri sia per ottenere la tabella che stai cercando, sia per impostarne struttura e visualizzazione al meglio per renderla più facilmente leggibile e anche, perché no, gradevole.
Prendi l’abitudine a usare sempre anche i parametri 4 e 5 anche se sono opzionale e in breve tempo ti verrà naturale usarli e in questo modo predisporrai tabelle di maggior efficacia.
Le tabelle pivot e la funzione Raggruppaper
Se conosci le tabelle pivot ti sarai immediatamente accorto che raggruppaper riproduce parte delle funzioni delle pivot: restituisce una tabella strutturata, impostando uno o più campi riga e campi calcolati.
Quindi la funzione è migliore delle pivot, o viceversa?
In realtà i 2 strumenti hanno vantaggi e svantaggi:
- la funzione raggruppaper è più veloce da impostare, è dinamica e si aggiorna in automatico, attinge più facilmente da altre funzioni matriciali, offre più calcoli possibili, anche di personalizzarli.
- le pivot sono affidabili, equipaggiate con layout predisposti, filtri su pagina, grafici pivot, campi calcolati, possono essere collegate a matrici di dati e molto altro.
Anche se in apparenza possono essere confrontabili, in realtà le pivot sono preferibili per molti validi motivi, per esempio perché possono impostare un layout professionale in pochi secondi. Quindi quando è opportuno usare la funzione raggruppaper.
Quando usare la funzione Raggruppaper
La funzione raggruppaper vale la pena usarla:
- se usi abitualmente le funzioni matriciali e se devi collegarti a matrici restituite da altre funzioni (es: filtro),
- nelle analisi preliminari o se hai bisogno di un report veloce,
- se hai bisogno di aggiornare in automatico e non puoi usare le macro,
- se vuoi creare visualizzazione interattive veloci e semplici.
Come gestire il layout della tabella
E se vuoi usare la tabella restituita dalla funzione raggruppaper in un report o in una dashboard?
In tal caso devi curarne l’aspetto, quindi dovrai evidenziare l’intestazione con colore, grassetto e dimensioni, aggiungere bordi dover servono e via dicendo. Se il bisogno è una tantum nessun problema, ma se il report verrà usato ogni settimana per anni, allora devi impostare un layout adeguato usando la formattazione condizionale, in modo che se i record aumentano o diminuiscono, il layout sia in grado di seguirli.
Se conosci la formattazione condizionale non avrai difficoltà a impostare bordi, colori nelle righe pari, eccetera, se non la conosci ti invito a iniziare da questo post e da questo tutorial.
Uso avanzato della funzione
La funzione raggruppaper non ha usi avanzati spettacolari come per esempio la funzione filtro. Dopotutto il risultato è quello: una tabella di solito a 2-3 colonne, e non ha senso aggiungerne altre perché ne ridurrebbero la leggibilità.
Quando si parla di uso avanzato per la funzione raggruppaper di solito ci si riferisce a:
- l’uso di filtri multicondizione,
- l’uso di altre funzioni, come scegli.col(), stack.oriz() o stack.vert(), filtro() e altro ancora, utili nella preparazione dei parametri o di una basedati intermedia o altro ancora,
- l’uso della funzione collegata a controlli su pagina, come elenchi della convalida, nei report o nelle dashboard per renderle più dinamiche,
- l’ampliamento dei campi calcolati visualizzati con la stessa funzione e con altre funzioni matriciali (es: stack.oriz(), stack.vert())
Comunque vedremo l’uso avanzato di raggruppaper insieme all’uso avanzato della funzione pivotper, altra funzione utile che vedremo in un altro post.
Conclusioni
La funzione raggruppaper è una delle funzioni di Excel 365 che meritano di essere apprese e usate soprattutto se lavori abitualmente con le funzioni matriciali dinamiche o se hai bisogno di un report veloce.
Ti permette in qualche secondo di impostare un andamento o una distribuzione dell’indicatore che vuoi analizzare e con qualche secondo in più ti permette di posizionarsi i filtri in cella della pagina per renderla interattiva.
Se invece conosci bene le pivot probabilmente non ne avrai bisogno. Ma ti invito lo stesso a provarla per qualche giorno, per comprenderne i punti di forza e le debolezze.
Come anticipato approfondiremo l’uso avanzato della funzione in un altro post, mentre nei prossimi vedremo altre funzioni dinamiche matriciali che ti semplificano la vita e che dovresti conoscere. A presto.
