Funzioni excel: il subtotale

Spesso si ha a che fare con liste di importi che è comodo sommare in base a raggruppamenti; ad esempio: budget, rendiconti di spese, resoconti di vendita, hanno in comune il fatto di presentare tanti importi suddivisi su varie voci ed è comodo poterli riepilogare. Per questo fra le funzioni excel troviamo il SUBTOTALE(), che ci permette di visualizzare il totale delle singole voci di budget, piuttosto che il venduto per agente e per provincia, ed ogni altra combinazione utile quando  si vuole analizzare voci con importi e riepilogare o totalizzare i numeri.

 

 

In questo articolo andremo a esplorare con qualche esempio questa interessante funzione Excel: SUBTOTALE(), che si può inserire in due modi:

• Attraverso il menu standard delle funzioni Excel, scegliendo: SUBTOTALE()
• Con la procedura guidata nel comando Subtotale della scheda Dati

Vedremo entrambi con due esempi:

• Come calcolare la somma di un elenco di voci in modo che siano considerate solo le righe visibili;
• Come dare una struttura a un resoconto di vendite totalizzando le voci omogenee;

Anatomia del subtotale

E’ una funzione di riepilogo disponibile nel raggruppamento “Matematiche e trigonometriche” della scheda formule che totalizza più valori in modi diversi; anche se l’operazione più utilizzata è la somma, ne sono disponibili altre:

• SOMMA
• PRODOTTO
• MEDIA
• MAX
• MIN
• CONTA.NUMERI
• CONTA.VALORI
• DEV.ST
• DEV.ST.POP
• VAR
• VAR.POP

A prima vista verrebbe da dire:

1. SOMMA(), MEDIA() ecc. sono già disponibili come funzioni a se stanti, perché un doppione?
2. Scorrendo la lista degli argomenti proposti quando inseriamo SUBTOTALE() in una cella ognuno compare due volte: perché?

 

Chiariremo il funzionamento con degli esempi focalizzati sulla SOMMA(), le altre operazioni funzionano in modo analogo. La sintassi è: SUBTOTALE(Num_funzione;Rif1;[Rif2];...]) dove:

• Num_funzione serve a indicare a Excel il calcolo da fare (somme piuttosto che conteggi, ecc.);
• Rif serve a indicare quali sono le celle da elaborare;

Quindi ad esempio scrivendo: SUBTOTALE(9;AM4:AM170) stiamo dicendo ad Excel di applicare la funzione 9 (Somma) all’intervallo di celle AM4:AM170, cioè di sommare le celle da AM4 a AM170.

 

Funzioni excel per gli elenchi: come sommare solo le righe filtrate

Supponendo di analizzare un elenco di ordinativi come quello in figura, per far il totale di tutti gli ordini sicuramente la prima opzione che viene in mente è inserire la funzione somma, ad esempio in AM2

 

 

Se questo punto si va a filtrare l’elenco mostrando solo gli ordinativi dell’agente “Verdi”, il totale non cambia:

Se invece inseriamo il subtotale con l’opzione “somma” il risultato tiene conto solo delle celle incluse dal filtro (cioè solo degli ordinativi riferiti all’agente Verdi).

Come tutte le funzioni excel, naturalmente anche il subtotale si aggiorna in tempo reale quando si fa qualunque cambiamento sul foglio, compreso impostare un altro filtro, tipo mostrare solo gli ordini originati dall’agente Rossi nella provincia di Bologna:

Questa è la prima parte della risposta alla domanda se questa funzione excel è un doppione di altre. Un’altra cosa che la differenzia e la rende molto comoda in tante applicazioni è la possibilità di inserire subtotali annidati. Tornando al solito esempio prendiamo un elenco più ridotto come quello in figura:

Su una lista di questo genere sarebbe molto comodo visualizzare, per esempio, il totale di ogni agente sotto la lista dei suoi ordini, possiamo cominciare  da Gialli, ma se utilizziamo la funzione somma() non tornano più i totali:

Se invece si inserisce un subtotale() notiamo che somma() continua a dare un risultato falsato, mentre il subtotale in AM2 da il numero giusto: la funzione subtotale() è studiata appositamente per ignorare altri subtotali nella colonna:

 

Grazie a questa funzione excel si possono ottenere facilmente riepiloghi come quello in figura:

Come sempre la funzione è dinamica, e cambiando il valore di una qualsiasi delle righe in elenco si aggiorneranno di conseguenza i subtotali.

Funzioni excel per gli elenchi: come inserire un subtotale automatico

Un altro modo per usare questa funzione è la procedura guidata disponibile sulla scheda “Dati”:

Questo comando è attivo su elenchi costruiti con le modalità tradizionali, se per caso abbiamo usato il comando inserisci tabella, bisogna riconvertire in intervallo dati con il comando “Converti in intervallo”, che comunque lascerà intatta la formattazione e il contenuto (non saranno però disponibili i comandi specifici delle tabelle excel).

Un’altra cosa da fare è controllare che l’elenco abbia etichette di intestazione per tutte le colonne, come nell’esempio visto. In pratica è sufficiente che la prima riga contenga i “titoli” delle colonne e abbia un formato diverso dalle altre.

Supponendo di voler totalizzare l’ordinato per agente, la prima cosa da fare è mettere in ordine l’elenco in base alla colonna “Agente”:  click destro su una cella qualsiasi della prima colonna, poi selezionare ordina -> ordina dalla A alla Z.

A questo punto la lista è pronta per accogliere i subtotali, che inseriremo con i passi seguenti:

• Selezionare una casella qualunque dell’elenco;
• Premere il pulsante “Subtotale” della scheda dati;

• Questo attiva la scheda di impostazione che richiede i parametri principali;

• Ad ogni cambiamento In: serve per selezionare rispetto a quale colonna eseguire il raggruppamento, nel nostro caso volendo l’ordinato per agente va selezionata la colonna “Agente” come in figura;
• Usa la funzione: richiama l’elenco delle funzioni disponibili nel subtotale, cioè ripropone l’elenco a discesa delle funzioni già viste in precedenza quando facevamo l’inserimento manuale;
• Aggiungi subtotali a: dobbiamo specificare in quale colonna fare le somme; in questo caso impostare “TotOrdine”;
• A questo punto si può dare l’ok lasciando invariate tutte le altre impostazioni, ottenendo questo risultato:

Ricapitolando, excel ha fatto automaticamente queste operazioni:

• Aggiungere una riga di riepilogo con il subtotale ogni volta che trova un cambiamento nella colonna “Agente” : per questo è importante mettere in ordine alfabetico la colonna rispetto alla quale raggruppare prima di far partire la procedura;
• Aggiungere una riga di totale complessivo in fondo all’elenco;
• Creare una struttura per il nostro elenco: so noti i pulsanti + e – che compaiono nell’area a sinistra del foglio, cliccandoli si può espandere o nascondere i raggruppamenti, permettendoci si scegliere se mostrare tutto il dettaglio o solamente i totali;

Funzioni excel per gli elenchi : come inserire subtotali su più livelli

Tenendo come esempio la tabella vista sopra ipotizziamo di voler riepilogare il venduto per agente, e in più per ogni agente il venduto in ogni provincia, i passi da seguire sono questi:

• Togliere il subtotale dall’elenco;
• Mettere la lista in ordine alfabetico per cliente e per provincia;
• Applicare i subtotali di primo livello (somma per agente);
• Applicare i subtotali di secondo livello (somma per provincia);

Per eliminare una struttura già applicata è sufficiente posizionarsi su una cella interna alla tabella, premere nuovamente “Subtotale” dalla scheda Dati, e nella maschera che appare premere “rimuovi tutti”.

Il passo seguente parte sempre dalla scheda dati:

• Scegliere una cella interna all’elenco;
• Dati -> Ordina;

• Compare la finestra di dialogo per gli ordinamenti;
• Scegliere “Aggiungi Livello” e inserire “Agente” ordinato dalla A alla Z;
• Ancora “Aggiungi Livello” e inserire “Provincia” ordinato dalla a alla Z;
• Poi confermare con “Ok”;

Il risultato di questa operazione è una lista in ordine alfabetico per client e all’interno di ogni cliente in ordine alfabetico di provincia, A questo punto la procedura è analog a a prima:

• Dati à subtotali da compilare come in figura:

• Questo ricrea la struttura per agente, poi di nuovo: Dati à Subtotali;
• Ad Ogni cambiamento in: “Provincia”;
• Usa funzione: “Somma”;
• Aggiungi subtotali a: “TotOrdine”;
• Poi va tolta la spunta dall’opzione “Sostituisci subtotali correnti”;

Il risultato che si ottiene è un livello in più nella struttura che riporta il totale di ogni provincia all’interno del totale per agente:

Naturalmente i pulsanti sulla parte sinistra dello schermo permettono di espandere o chiudere il dettaglio delle varie voci esattamente come prima.

Funzioni excel per gli elenchi: una somma che ignora le righe nascoste

A questo punto ci resta da rispondere all’ultima domanda: perché la funzione somma appare due volte nella lista di argomenti proposti dal subtotale?

La risposta è che la prima considera anche le righe nascoste, mentre la seconda le ignora. E’ importante non confondersi fra righe filtrate e righe nascoste: questa opzione riguarda quelle nascoste dall’utente con il comando “nascondi” sulla riga, o dai pulsanti di struttura che appaiono a sinistra; chiariamo con un esempio:

• Nel foglio appena creato selezioniamo il pulsante 2 in alto a sinistra in modo da visualizzare solo i totali;
• In AM2 digitiamo =SUBTOTALE(9;AM4:AM500)
• In AM3 =SUBTOTALE(109;AM4:AM500)
• Notiamo che in AM3 il risultato è zero, in quanto abbiamo usato l’opzione di sommare ignorando le celle nascoste;

Se si va ad espandere  le categorie, invece, man mano che le righe diventano visibili sono aggiunte alla somma, ad esempio nella figura sotto vediamo che in AM3 appare la somma del venduto a Foggia e del venduto a Perugia dell’agente gialli.