Come creare una top 10 su Excel

In questa guida vediamo come creare una top 10 su Microsoft Excel in tre diversi modi. A seconda della tua versione di Excel, potrai scegliere quale sia più congeniale per i tuoi file.

Chiaramente la logica vale per trovare anche la top 5, worst 10 e così via - saranno solo necessarie solo piccole modifiche alle formule, ma potrai replicare il ragionamento per tutti e 3 i metodi:

• Utilizzando le formule di Excel
• Utilizzando Power Query
• Utilizzando le tabelle pivot

Il nostro database di partenza ha le seguenti colonne, magari può esserti utile per sperimentare ulteriormente:

• CLIENTE: cognome del cliente
• PRODOTTO: brand acquistato
• DATA: data di acquisto
• PREZZO: prezzo di acquisto
• MERCATO: mercato di destinazione del prodotto venduto

In questo caso vogliamo fare una semplice top 10 dei clienti prendendo tutte le informazioni dalla tabella di partenza, quello che ci servirà sarà un modo per estrarre i singoli nomi dei clienti e sommare il prezzo per ognuno di loro. Puoi seguire la guida passo passo scaricando il file da qui:

Come creare una top 10 su Excel con le formule di Excel 365

Potendo utilizzare Excel 365, abbiamo delle formule veramente semplici per arrivare a questo risultato:

La formula completa è la seguente:

=LET(clienti;UNICI(TBL[CLIENTE]);
totale;SOMMA.PIÙ.SE(TBL[PREZZO];TBL[CLIENTE];clienti);
DATI.ORDINA(STACK.ORIZ(clienti;totale);2;-1))

Vediamola nel dettaglio:

=LET(clienti;UNICI(TBL[CLIENTE]);

Questa parte indica che stiamo dichiarando con il valore "clienti" l'elenco dei valori univoci dei nomi dei clienti

totale;SOMMA.PIÙ.SE(TBL[PREZZO];TBL[CLIENTE];clienti);

In questa parte abbiamo invece la somma dei singoli prezzi di vendita per singolo cliente univoco, infatti il valore "clienti" è richiamato per velocizzare la formula che altrimenti sarebbe:

totale;SOMMA.PIÙ.SE(TBL[PREZZO];TBL[CLIENTE];UNICI(TBL[CLIENTE]);

Tornando alla nostra formula:

DATI.ORDINA(STACK.ORIZ(clienti;totale);2;-1))

Le due parti con clienti e totale si uniscono, in modo che abbiamo in una sola riga (tramite STACK.ORIZ) il nome del cliente e il fatturato complessivo ottenuto. A questo punto possiamo utilizzare DATI.ORDINA che ordina per la seconda colonna (il valore 2, quindi il fatturato) il nostro risultato in ordine decrescente (-1, altrimenti potevamo inserire 1 o tenere vuoto per avere un ordine crescente).

Se avessimo voluto fare la statistica unicamente per un singolo brand (inserito nella cella P2, giusto per fare un esempio), la formula sarebbe stata leggermente diversa dato che avremmo dovuto aggiungerla nella parte relativa al SOMMA.PIÙ.SE come segue:

=LET(clienti;UNICI(TBL[CLIENTE]);
totale;SOMMA.PIÙ.SE(TBL[PREZZO];TBL[CLIENTE];clienti;TBL[PRODOTTO];P2);
DATI.ORDINA(STACK.ORIZ(clienti;totale);2;-1))

Questi però sono tutti i clienti - se volessimo trovare solo i primi 10 (o 25, 100), basterebbe fare così, considerato che abbiamo inserito il numero 10 nella cella M2:

=INCLUDI(LET(clienti;UNICI(TBL[CLIENTE]);
totale;SOMMA.PIÙ.SE(TBL[PREZZO];TBL[CLIENTE];clienti);
DATI.ORDINA(STACK.ORIZ(clienti;totale);2;-1));M2)

La funzione INCLUDI prende i valori dell'intera matrice e considera unicamente i primi 10 valori: avendo noi ordinato in ordine decrescente, questa formula comprenderà i nostri top 10 clienti escludendo gli altri.

In assenza di Excel 365, la situazione si complica un po' dato che dovremmo gestire anche il caso dei valori duplicati - magari puoi dare un'occhiata a questa guida dedicata che ha anche delle soluzioni compatibili con le versioni precedenti, anche se sicuramente sarà meno immediata come soluzione.

Ti suggerirei di guardare le soluzioni seguenti (Power Query e Pivot) anzichè cimentarti con le formule, sempre che tu non abbia una necessità molto specifica.

Come creare una top 10 con Power Query

Possiamo creare una classifica stile top 10 anche con Power Query, evitando di lavorare con le formule. In questo caso prendiamo i dati di partenza già presenti sul file, ma chiaramente potresti replicare il procedimento prendendo un qualsiasi file a tua disposizione.

I passaggi sono i seguenti:

• Cliccare nella tabella di partenza
• Caricare i dati cliccando su Dati > Recupera Dati > Da tabella / Intervallo. Questo perchè stiamo prendendo una tabella ma possiamo anche usare CSV, PDF e così via

• A questo punto, Power Query ci catapulta direttamente nella sua interfaccia e ha già modificato il tipo dei dati di partenza. Possiamo modificare il formato del prezzo inserendo la valuta sostituendo a type nullable number il valore Currency.Type nella parte di codice per il Prezzo

• Non ci resta che raggruppare per singolo cliente cliccando su Home > Raggruppa Per. Possiamo quindi raggruppare sommando i valori del prezzo, che andranno a comporre una nuova colonna chiamata TOTALE. Come vedi puoi fare tutti i passaggi direttamente a schermo, senza l'utilizzo di funzioni

• Power Query ci restituisce ora unicamente la colonna CLIENTE e la colonna TOTALE. Anche in questo caso, possiamo cambiare il tipo in Currency.Type direttamente da codice (o inserendo un passaggio intermedio cliccando sulla piccola icona con 123 accanto all'intestazione TOTALE)

• A questo punto, possiamo cliccare direttamente per ordinare da Z a A (in ordine decrescente) per la colonna TOTALE, trovando quindi i clienti tutti in ordine dal più importante al meno importante

• Clicchiamo su Chiudi e Carica, che troviamo in alto a sinistra. Facendo così, Power Query creerà la nostra tabella con tutte le informazioni che abbiamo riassunto nelle nostre operazioni in Power Query

In termini di codice, Power Query fa le seguenti operazioni:

let
Origine = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"CLIENTE", type text}, {"PRODOTTO", type text}, {"DATA", type datetime}, {"PREZZO", Currency.Type}, {"MERCATO", type text}}),
#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"CLIENTE"}, {{"TOTALE", each List.Sum([PREZZO]), Currency.Type}}),
#"Ordinate righe" = Table.Sort(#"Raggruppate righe",{{"TOTALE", Order.Descending}})
in
#"Ordinate righe"

Quello che vedi è il codice M, creato automaticamente da Excel quando lavoriamo su Power Query. Non è necessario imparare a usarlo nel dettaglio, ma potrebbe tornare utile per alcune funzioni particolari. Vediamolo nel dettaglio:

let
Origine = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],

L'origine del nostro file è sullo stesso file (Excel.CurrentWorkbook) ed è la tabella chiamata TBL.

#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"CLIENTE", type text}, {"PRODOTTO", type text}, {"DATA", type datetime}, {"PREZZO", Currency.Type}, {"MERCATO", type text}}),

Power Query cambia automaticamente i tipi di dati per tutte le colonne della tabella che incontra utilizzando la funzione Table.TransformColumnTypes - quindi inserisce come testo le colonne CLIENTE, PRODOTTO e MERCATO, mentre la colonna DATA ha un formato di data/ora e la colonna PREZZO ha il formato di valuta. Come indicato in precedenza, questo è stato l'unico che ho modificato da M, dato che in origine Power Query lo aveva solo inteso come valore numerico (type nullable number), comunque è un dettaglio dato che potevamo tranquillamente modificarlo anche in seconda battuta

#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"CLIENTE"}, {{"TOTALE", each List.Sum([PREZZO]), Currency.Type}}),

Come vediamo, M richiama sempre la voce precedente (Modificato tipo), dato che lavoriamo a step. In questo caso noi stiamo raggruppando i valori (Table.Group) ottenendo, per ogni singolo CLIENTE, il totale della colonna PREZZO. Questa colonna si dovrà chiamare TOTALE e sarà anch'esso in formato di valuta.

#"Ordinate righe" = Table.Sort(#"Raggruppate righe",{{"TOTALE", Order.Descending}})

in

#"Ordinate righe"

A questo punto, non ci resta che ordinare il risultato (Table.Sort) in ordine decrescente (Order.Descending). Avremo quindi una tabella di uscita che sarà ordinata per il totale del fatturato per ogni singolo cliente e Power Query ce lo restituirà come valore finale (in).

Anche in questo caso, se volessimo mantenere esclusivamente le prime 10 righe dovremmo fare un passaggio ulteriore (non dinamico, a differenza delle formule):

let
Origine = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"CLIENTE", type text}, {"PRODOTTO", type text}, {"DATA", type datetime}, {"PREZZO", Currency.Type}, {"MERCATO", type text}}),
#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"CLIENTE"}, {{"TOTALE", each List.Sum([PREZZO]), Currency.Type}}),
#"Ordinate righe" = Table.Sort(#"Raggruppate righe",{{"TOTALE", Order.Descending}}),
#"Mantenute prime righe" = Table.FirstN(#"Ordinate righe",10)
in
#"Mantenute prime righe"

Stiamo tenendo solamente le prime 10 righe (Table.FirstN), ma potevamo chiaramente scegliere il valore che più ci era congeniale. Tutto il resto del processo rimane, fino a quel momento, assolutamente identico al precedente.

Come best practice, avremmo dovuto rinominare i singoli passaggi in maniera più descrittiva, ad esempio anzichè "Raggruppate Righe" avremmo potuto scrivere "Raggruppamento per Cliente" e così via. Questa operazione può essere fatta cliccando sul tasto destro nelle singole query sul pannello di destra chiamato Impostazioni query.

Come creare una top 10 su Excel utilizzando le tabelle pivot

Possiamo anche evitare di utilizzare formule o Power Query, creando molto più semplicemente una tabella pivot che ci permette di trovare i primi 10 clienti del nostro database.

Le operazioni per arrivare a trovare la nosta top 10 sono le seguenti:

• Clicchiamo all'interno della tabella per far aprire il menu contestuale chiamato Struttura tabella. Da qui, possiamo cliccare su tabella pivot. In linea di massima non ti consiglio di utilizzare le tabelle pivot consigliate, dato che arrivano direttamente a un risultato ma non sempre sono in linea con quello che stiamo cercando

• A questo punto possiamo spostare il campo CLIENTE fra le righe e il campo PREZZO fra i valori. Excel riconosce in automatico che i valori di PREZZO sono numerici, quindi li somma

• Otteniamo una tabella pivot con i risultati per ogni singolo cliente. L'intestazione Somma di PREZZO può essere modificata sia cliccando sulla cella C3 (nell'esempio) sia utilizzando il menu contestuale nella sezione valori. Io ho modificato poi il nome da Somma di PREZZO a TOTALE, ma la logica rimane inalterata

• A questo punto possiamo selezionare CLIENTE per poi cliccare su Altre opzioni di ordinamento

• Da qui, possiamo scegliere di utilizzare l'ordinamento decrescente per il TOTALE. Vedremo quindi prima i valori più grandi di TOTALE e via via quelli inferiori - quindi partiremo dai clienti più importanti fino ai più piccoli

• Et voilà! Abbiamo la nostra pivot con tutti i dati ordinati. Da qui possiamo vedere a colpo d'occhio i clienti più importanti, quali Caruso, Grasso e Moretti.

Per arrivare alla top 10 ci manca solo un passaggio ulteriore, se necessario:

• Cliccando sempre sulla colonna CLIENTE, possiamo andare su Filtri per valori e poi selezionare Primi 10. Questa voce è un po' nascosta, ma la trovi come ultima dell'elenco proposto:

• Possiamo scegliere ora quanti siano i clienti che ci interessano. In questo caso rimaniamo a 10, dato che stiamo considerando i 10 elementi più grandi per il TOTALE_PREZZO (che è sempre la colonna del TOTALE, ma con un nome diverso)

• La pivot ora è completa, dato che vediamo unicamente i primi 10 clienti e tutti gli altri sono stati filtrati!

Eccoci arrivati alla conclusione della guida! La strategia per arrivare al risultato cambia molto ma penso sia interessante vedere questi metodi dato che in alcuni casi ci risulterà più semplice avere una pivot (ad esempio se i dati di partenza sono molti), Power Query (se dobbiamo integrare i dati e poi avere soltanto un output stringato senza possibilità di filtri) o le formule (per soluzioni completamente dinamiche).

Se vuoi scaricare il file che completa la guida, puoi cliccare qui: