Come utilizzare RAGGRUPPAPER in Excel

La funzione RAGGRUPPAPER ci permette di raggruppare dati in Excel secondo alcuni parametri definiti dall'utente. Diciamo che è un'alternativa assai valida a utilizzare tabelle pivot o a raggruppare i dati da Power Query, che rimangono comunque opzioni percorribili a seconda dei singoli casi.

Questa funzione è compatibile solo con Excel 365, quindi se hai una versione desktop installata precedente a 365, puoi seguire la guida utilizzando la versione (gratuita) di Excel per web.

La funzione RAGGRUPPAPER è disponibile solo da Excel 365 ed è ora sul canale corrente. Se non la vedi ancora sul tuo Excel, è solo un tema di aggiornamenti - prova ad aggiornare e controlla nuovamente, altrimenti parla con il reparto IT aziendale. Insieme alla funzione RAGGRUPPAPER, dovresti avere anche altre due funzioni: PIVOTPER e PERCENTUALE.DI.

Che cosa fa la funzione RAGGRUPPAPER in Excel

Come molte funzioni recenti, RAGGRUPPAPER ha, al suo interno, parecchi argomenti possibili per compiere azioni diverse in Excel. La scelta di Microsoft è quella di offrire funzioni che sembrano dei coltellini svizzeri con moltissime opzioni al loro interno. Mi ritrovo d'accordo perchè ci permette di sperimentare molto più velocemente, ma a volte risultano un attimo complesse da digerire:

RAGGRUPPAPER(row_fields;values;function;[field_headers];[total_depth];[sort_order];[filter_array];[field_relationship])

Vediamo quindi i singoli parametri:

• Row_fields determina i campi delle righe. Stiamo quindi definendo i dati che vogliamo restituiti sulle singole righe ed è l'impalcatura cruciale per il resto della funzione
• Values sono i valori che utilizziamo per la parte di calcolo successiva, similarmente a come inseriresti un campo all'interno di una tabella pivot
• Function è un elenco predeterminato di una serie di 15 funzioni che possiamo utilizzare, fra le quali SOMMA
• Field_headers sono le intestazioni e possiamo definire se non ci sono nell'intervallo di partenza e quindi di non visualizzarle (0), se i dati di partenza hanno intestazioni che non vanno mostrate (1), se i dati non hanno intestazioni ma vanno generate (2) e se i dati hanno intestazioni da visualizzare (3)
• Total_depth ci indica la profondità alla quale vogliamo arrivare relativamente ai totali, ovvero se non vogliamo totali (0), se vogliamo i totali complessivi (1), se vogliamo totali complessivi e subtotali (2), se vogliamo i totali complessivi in alto (-1) e se vogliamo totali complessivi e i subtotali in alto (-2)
• Sort_order è l'ordine di presentazione dei dati. In questo caso un valore positivo sarà crescente e uno negativo decrescente. Selezioniamo la colonna che ci interessa e possiamo poi decidere se mettere in dati in ordine crescente per la terza colonna (3) o in ordine decrescente per la quinta (-5) e così via. Rispetto a funzioni come DATI.ORDINA o DATI.ORDINA.PER, qui c'è un passaggio solo
• Filter_array ci permette di filtrare i dati di partenza per raggruppare solo i dati di un sottoinsieme secondo una logica booleana. Puoi quindi indicare TBL_VENDITE[REGIONE]="Umbria" per prendere solo i dati della regione Umbria, ad esempio
• Field_relationship indica la relazione fra i campi, se sono più di uno in row_fields. Inserendo 0 indichiamo che i campi fanno parte di una gerarchia e visualizziamo i subtotali, mentre con 1 indichiamo che i campi non fanno parte di una gerarchia (vediamo infatti Tabella) e non possiamo visualizzare i subtotali. Questo campo aiuta a fare un ordinamento più preciso se hai vari campi in row_fields, poichè il caso 1 considera come indipendenti tutte le combinazioni - mentre il caso 0 non fa altro che ordinare per il primo campo, ignorando eventuali combinazioni che abbiano valori più grandi

In sintesi: RAGGRUPPAPER diventa lo standard di riferimento per creare array complessi dove l'obiettivo è ridurre il numero di dati visualizzati dall'utente, andando da una visione estremamente granulare a una più riassuntiva. Ci vorrà del tempo per vederla adottata nei file aziendali, ma è veramente una funzione da non sottovalutare.

Quali sono le funzioni utilizzabili con RAGGRUPPAPER in Excel

Un aspetto interessante, comune a RAGGRUPPAPER e PIVOTPER, è la presenza di 15 funzioni che possiamo utilizzare come default nei calcoli (parametro function). Se hai mai utilizzato funzioni come SUBTOTALE o AGGREGA, che presentano al loro interno una scelta di comandi, ti ritroverai nella logica - ma qui, il vantaggio è che possiamo direttamente inserire il nome delle funzioni anzichè numeri che le identificano (9 per SUBTOTALE è SOMMA, qui puoi inserire direttamente SOMMA).

Vediamole singolarmente, penso molte le conoscerai già:

• SOMMA effettua la somma dei valori
• PERCENTUALE.DI calcola la percentuale sul totale. Quindi se abbiamo tre valori pari a 15, 27 e 58, essendo il totale 100, avremo 15%, 27% e 58% come risultato
• MEDIA calcola la media
• MEDIANA calcola la mediana
• CONTA.NUMERI conta i numeri presenti
• CONTA.VALORI conta i valori presenti
• MAX calcola il valore massimo
• MIN calcola il valore minimo
• PRODOTTO moltiplica i dati per singola colonna / intervallo. Non è uguale a SOMMA o MATR.SOMMA.PRODOTTO, attenzione, perchè agisce sempre sullo stesso intervallo
• MATRICE.A.TESTO riassume i dati da una matrice a un testo
• CONCAT concatena i valori presenti
• DEV.ST.C calcola la deviazione standard basandosi su un campione
• DEV.ST.P calcola la deviazione standard basandosi sull'intera popolazione
• VAR.C stima la varianza basandosi su un campione
• VAR.P stima la varianza basandosi sull'intera popolazione
• MODA.SNGL restituisce il valore più presente all'interno di un intervallo
• LAMBDA permette di creare una funzione LAMBDA (personalizzata)

La comodità di queste funzioni è evidente: anzichè scrivere formule lunghe e complesse cui dare degli argomenti, possiamo richiamarle in automatico senza passaggi aggiuntivi.

Un caso pratico per utilizzare RAGGRUPPAPER in Excel

Il nostro database di partenza si chiama TBL_VIDEOGAMES ed è una rivisitazione di questo database liberamente scaricabile da Kaggle. Abbiamo tenuto solo alcune colonne, per semplicità:

• Title: i titoli dei videogiochi
• Total_sales: i dati di vendita
• Console: la console di gioco
• Genre: il genere di gioco
• Publisher: chi segue la distribuzione del videogioco, "editore" o "publisher"

Le domande cui risponderemo saranno:

• Qual è il totale di fatturato per singolo videogioco?
• Ordina i videogiochi per totale di fatturato (dal più venduto al meno venduto)
• Ordina le console e relativi generi per totale di fatturato (dal più venduto al meno venduto) con e senza subtotali
• Raggruppa console e generi filtrando esclusivamente per una console specifica, ordinando il fatturato (Z-A)
• Raggruppa console e generi filtrando esclusivamente per una console specifica e per un genere specifico, ordinando il fatturato (Z-A)
• Raggruppa console e generi filtrando esclusivamente per una console specifica, ordinando il fatturato (Z-A) e calcolando sia la somma di fatturato sia la mediana
• Raggruppa console e generi, indicando il numero di generi per console, quali sono e ordinando il totale per numero di generi (Z-A)

Cominciamo!

Come raggruppare i dati in modo semplice con RAGGRUPPAPER in Excel

Vediamo un caso semplice di raggruppamento dati con RAGGRUPPAPER in Excel:

=RAGGRUPPAPER(TBL_VIDEOGAMES[title];
TBL_VIDEOGAMES[total_sales];
SOMMA)

In questo caso abbiamo definito che:

• I titoli dei videogiochi sono gli elementi che vogliamo visualizzare nelle righe
• Il fatturato è ciò che vogliamo utilizzare per i calcoli
• Il calcolo sarà quello della somma
• Ci interessa vedere il totale, alla fine del raggruppamento (questo è un aspetto da non sottovalutare)

Una breve variazione sul tema, per evitare di vedere il totale finale, è questa:

=RAGGRUPPAPER(TBL_VIDEOGAMES[title];
TBL_VIDEOGAMES[total_sales];
SOMMA;0;0)

Per riassumere: recupero i singoli videogiochi come se usassi UNICI (senza duplicati) per poi sommare il fatturato per ognuno di loro. Sembra semplice, ma creare questo recap senza questa formula sarebbe stato decisamente più complesso, diciamo un qualcosa tipo:

=STACK.ORIZ(UNICI(TBL_VIDEOGAMES[TITOLO]);SOMMA.PIÙ.SE(TBL_VIDEOGAMES[FATTURATO];TBL_VIDEOGAMES[TITOLO];UNICI(TBL_VIDEOGAMES[TITOLO])))

Qui, RAGGRUPPAPER vince a mani basse. Alziamo però la complessità del raggruppamento.

Come raggruppare e ordinare i dati con RAGGRUPPAPER in Excel

RAGGRUPPAPER permette di ordinare i risultati, abbastanza facilmente. Vediamo un caso semplice dove c'è un ordinamento dei dati, che pesca dall'argomento chiamato Sort_order:

=RAGGRUPPAPER(TBL_VIDEOGAMES[title];
TBL_VIDEOGAMES[total_sales];
SOMMA;
0;0;-2)

Lo so, sembra molto più complessa della precedente, ma in realtà i primi 3 passaggi sono esattamente come i precedenti. La parte invece con ;0;0;-2 si traduce così:

• Il primo 0 sono i Field_headers: non ci interessa visualizzare le intestazioni
• Il secondo è il livello di profondità dei totali, Total_depth. Non ci interessano, quindi pure in questo caso mettiamo 0
• Il -2 vuol dire ordinare per la seconda colonna (2) in ordine decrescente (-).

Quindi prendiamo i videogiochi, sommiamo il fatturato per ognuno, ordinandoli poi da quello che generato maggiore fatturato a quello che ne ha generato di meno.

Se hai presente come utilizzare funzioni come DATI.ORDINA, considera che sarebbe una soluzione del tipo DATI.ORDINA(intervallo;2;-1), mentre qui c'è semplicemente il -2 (seconda colonna, decrescente).

Come raggruppare e ordinare dati in più colonne con RAGGRUPPAPER in Excel

Vediamo ora come raggruppare i dati inserendo console e genere, trovando poi il fatturato totale e ordinando i dati proprio per il fatturato finale

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
SOMMA;
0;0;-3)

La funzione STACK.ORIZ è come se considerasse tutte le combinazioni fra console e genere, raggruppando poi i dati e creando dei calcoli per ogni coppia. A questo punto, sommiamo il fatturato e ordiniamo poi per la terza colonna finale del nostro array. Da notare come non solo X360 sia la console più venduta, ma i dati sono anche ordinati per ogni singola console. Quindi vediamo per prima X360, ma all'interno del sottinsieme di X360, i generi sono tutti ordinati.

Se volessimo vedere anche il subtotale, ci basterebbe cambiare solo uno dei parametri precedenti:

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
SOMMA;
0;2;-3)

Inserendo 2, avremo i totali per i singoli dati, che appaiono alla fine di ogni sottinsieme. Vedremo quindi X360 dal genere più venduto a quello meno venduto, concludendo poi con il totale complessivo proprio per X360 prima di passare alla console successiva, in questo caso PS3.

Come raggruppare e ordinare dati in più colonne con RAGGRUPPAPER in Excel, filtrando i dati

Come facciamo a filtrare i nostri risultati, in RAGGRUPPAPER? Vediamo come pescare esclusivamente la console inserita nella cella B2:

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
SOMMA;
0;1;-3;
TBL_VIDEOGAMES[console]=B2)

Come se stessimo usando FILTRO, possiamo inserire, in filter_array, la nostra condizione. In questo caso, TBL_VIDEOGAMES[console]=B2 riduce il nostro campo d'azione e avremo esclusivamente i titoli collegati con un certo genere. Come nelle altre formule, chiaramente dobbiamo avere tutti i dati nella stessa tabella (TBL_VIDEOGAMES) per essere certi che la formula restituisca poi le informazioni che desideriamo.

E se fossero più condizioni? Inseriamo anche il caso del publisher, come se volessimo restringere il campo a un solo publisher:

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
SOMMA;
0;0;-3;
(TBL_VIDEOGAMES[console]=F2)*(TBL_VIDEOGAMES[publisher]=G2))

Dato che quando parliamo di FILTRO dobbiamo ragionare in ottica di algebra booleana, le due condizioni vanno messe fra parentesi perchè generano array del tipo (VERO\VERO\VERO) * (FALSO\VERO\VERO). Solo quando entrambe le condizioni sono verificate (VERO*VERO) avremo allora un risultato.

Un'alternativa è anche quella di inserire il filtro direttamente come secondo argomento della funzione, un trucco che ho scoperto da questo post LinkedIn di Jessica S:

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
(TBL_VIDEOGAMES[total_sales])*
(TBL_VIDEOGAMES[console]=B2)*
(TBL_VIDEOGAMES[publisher]=C2);
SOMMA;
0;0;-3)

In sostanza, anzichè usare filter_array, inseriamo le condizioni nella sezione dei valori. La differenza sostanziale è che, in questo caso, avremo tutte le combinazioni possibili di console e genere sotto quelle che hanno effettivamente il fatturato, come puoi notare dall'immagine.

Come raggruppare e ordinare dati in più colonne con RAGGRUPPAPER in Excel, filtrando i dati e compiendo più calcoli

Alziamo ulteriormente la complessità, dato che vogliamo sapere la somma e la mediana di fatturato per tutte le console e relativi generi, filtrando poi per una sola console. Vediamo la formula complessiva:

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
STACK.ORIZ(SOMMA;MEDIANA);
0;1;-3;
TBL_VIDEOGAMES[console]=B2)

In questo caso utilizziamo nuovamente il "trucco" di STACK.ORIZ, ma, anzichè farlo per i dati iniziali del nostro array, lo sfruttiamo per i calcoli. Avremo quindi, come risultato, quattro diverse colonne: console | genere | somma_fatturato | mediana_fatturato. Puoi inserirne quante preferisci!

Certo, non ci fa impazzire avere la scritta SOMMA e MEDIANA, quindi possiamo semplicemente rimuovere la prima riga:

=ESCLUDI(RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
STACK.ORIZ(SOMMA;MEDIANA);
0;1;-3;
TBL_VIDEOGAMES[console]=B2);1)

ESCLUDI rimuove la prima riga, semplificando anche la nostra visualizzazione finale. In genere, preferisco mettere io le intestazioni e manovrare i dati in maniera indipendente, evitando di inserire le intestazioni perchè poi complicano la vita quando fai calcoli - ma, chiaramente, qui andiamo sulle scelte personali. Fai solo attenzione perchè, se tieni anche le intestazioni, eventuali calcoli tipo somma e conteggi potrebbero essere sballati, avendo le intestazioni nell'array e non fuori.

E se volessimo vederli in verticale, questi risultati? Il ragionamento è molto simile al precedente, ma stavolta lavoreremo con STACK.VERT:

=RAGGRUPPAPER(STACK.ORIZ(TBL_VIDEOGAMES[console];TBL_VIDEOGAMES[genre]);
TBL_VIDEOGAMES[total_sales];
STACK.VERT(SOMMA;MEDIANA);
0;0;-3;
TBL_VIDEOGAMES[console]=G2)

Dipende sempre dalle esigenze delle singole analisi, perchè in questo caso l'esplosione dei dati, simile a un unpivot, ci aiuta se dobbiamo poi fare ulteriori calcoli - tuttavia, le righe saranno parecchie di più.

Come faremmo, però, se volessimo calcolare il totale di fatturato per ogni publisher, oltre che i generi, contandoli in maniera univoca? Anche qui, sono combinazioni di quanto già visto:

=ESCLUDI(RAGGRUPPAPER(TBL_VIDEOGAMES[publisher];
STACK.ORIZ(TBL_VIDEOGAMES[total_sales];TBL_VIDEOGAMES[genre]);
STACK.ORIZ(SOMMA;LAMBDA(x;CONTA.VALORI(UNICI(x))));
0;1;-2;
TBL_VIDEOGAMES[console]=B2);
1)

In sostanza, gli STACK.ORIZ si "pareggiano", quindi faremo la somma per il fatturato, mentre per i generi avremo il conteggio dei valori univoci. In questo modo puoi sfruttare numerose colonne per i tuoi calcoli, non solo una!

Veniamo ora al "mostro finale":

=LET(riepilogo;RAGGRUPPAPER(TBL_VIDEOGAMES[console];
TBL_VIDEOGAMES[genre];
LAMBDA(cons;TESTO.UNISCI(" | "; VERO; DATI.ORDINA(UNICI(cons))));;0);
console;SCEGLI.COL(riepilogo;1);
generi;SCEGLI.COL(riepilogo;2);
generi_singoli;MAP(generi;
LAMBDA(gen;CONTA.VALORI(DIVIDI.TESTO(TESTO.UNISCI(",";VERO;gen);" | ";","))));
risultato;STACK.ORIZ(console;generi_singoli;generi);
risultato_ordinato;DATI.ORDINA(risultato;{2\1};{-1\1});
risultato_ordinato)

In questi casi LET è una vera salvezza, perchè ci permette di spezzare la formula per gradi:

Riepilogo: stiamo raggruppando le console con relativi generi. Uniamo tutti i generi univoci ordinati in un'unica stringa separata da " | ". Quindi, se una console ha RPG, FPS e Puzzle, diventerà: FPS | Puzzle | RPG (in una sola cella). Puoi vedere quale sia il risultato di questa sezione sostituendo l'ultimo argomento "risultato_ordinato", nella formula, con "riepilogo". Purtroppo, non è molto leggibile nè possiamo ordinare i dati, quindi va un attimo sistemato. DATI.ORDINA(UNICI( si rende necessario altrimenti non prende i valori univoci e crea un array con risultati poco coerenti

Console e generi: stiamo selezionando, con console, i valori univoci delle console precedenti. Generi è invece l'elenco dei generi per singola console. Fin qui, tutto normale, potevamo usare anche INDICE, nel caso

Generi_singoli: dato che vogliamo contare i generi per console. Usiamo MAP per considerare ognuno dei valori di generi, che sono le celle precedenti con diversi generi in una singola cella. Uniamo (TESTO.UNISCI) i dati usando la virgola, per poi separare tutto il risultato prima per la barra precedente "|" e poi per la virgola "," (DIVIDI.TESTO). In questo modo stiamo spezzando ognuno dei valori in diverse colonne, a seconda del numero di generi che troviamo - li contiamo (CONTA.VALORI) e recuperiamo quindi il totale per ognuno dei raggruppamenti

Risultato: non ci resta che unire tutto, inserendo prima le console, poi il numero di generi singoli e alla fine l'elenco dei generi stessi.

Risultato_ordinato: ordiniamo i dati, inserendo delle condizioni più complesse del solito. DATI.ORDINA ci dice che darà la priorità alla seconda colonna del nostro array (il numero di generi singoli) e poi al nome della console ({2\1}). Il numero di generi singoli sarà poi ordinato Z-A e il nome della console in ordine crescente ({-1\1})

Conclusioni

Abbiamo visto come RAGGRUPPAPER sia una funzione versatile e comoda per lavorare con Excel. Se devi analizzare un database, questa funzione rappresenta un punto di partenza ideale per creare analisi complesse, evitando di perdersi in "accrocchi" di vario tipo con tante funzioni difficili da riutilizzare.

Nel mio flusso di lavoro RAGGRUPPAPER rappresenta una costante e spero che, dopo questa guida, entri a far parte del tuo arsenale personale di funzioni in Excel!