Formula SUBTOTAL() in Excel: guida completa ai calcoli

Chi usa Excel spesso ha bisogno di modi flessibili per sintetizzare i dati che rispondano a filtri o raggruppamenti. Le funzioni tradizionali come SUM(), AVERAGE() o COUNT() calcolano i risultati in base a tutte le celle in un intervallo, indipendentemente dal fatto che alcune righe siano nascoste o filtrate. La funzione SUBTOTAL() offre un’alternativa dinamica che si adatta automaticamente ai dati visibili nel foglio di lavoro.

Se sei alle prime armi con Excel e vuoi costruire basi solide prima di esplorare funzioni avanzate come SUBTOTAL(), il nostro corso Introduction to Excel copre competenze essenziali come navigazione nel foglio, formule di base e tecniche di formattazione dei dati che ti prepareranno a lavori più sofisticati in Excel.

Che cos’è la funzione SUBTOTAL() in Excel?

La funzione SUBTOTAL() calcola valori aggregati come somma, media, conteggio e altre operazioni statistiche su un set di dati. Ciò che la distingue dalle funzioni standard di Excel è il suo comportamento dinamico: cambia il risultato in base a ciò che è visibile o filtrato. La funzione può ignorare sia le righe nascoste manualmente sia quelle nascoste tramite filtri, a seconda del numero funzione specificato.

Quando applichi un filtro ai dati o nascondi manualmente alcune righe, SUBTOTAL() adatta automaticamente il calcolo includendo solo le celle visibili. Questo la rende un’ottima scelta per creare righe di riepilogo in set di dati filtrati, costruire dashboard interattivi o generare report che devono aggiornarsi in base alle selezioni dell’utente.

La funzione supporta le stesse operazioni statistiche delle funzioni standard di Excel—incluse somma, media, conteggio, massimo, minimo e molte altre—ma le racchiude in un’unica funzione contestuale che si adatta allo stato corrente dei dati.

Sintassi e operazioni supportate

La funzione SUBTOTAL() segue una sintassi semplice:

=SUBTOTAL(function_num, ref1, [ref2], ...)

Il primo parametro, function_num, determina sia il tipo di calcolo sia come la funzione gestisce le righe nascoste. I parametri restanti (ref1, ref2, ecc.) sono gli intervalli di celle da includere nel calcolo. Puoi specificare fino a 254 intervalli diversi.

I numeri funzione ricadono in due categorie distinte in base a come trattano le righe nascoste manualmente:

I numeri funzione da 1 a 11 includono nel calcolo le celle delle righe nascoste manualmente, mentre i numeri da 101 a 111 le escludono. Tuttavia, entrambi gli intervalli ignorano sempre le righe nascoste dai filtri.

Per esempio, SUBTOTAL(9, A2:A10) calcola la somma di A2:A10 includendo eventuali righe nascoste manualmente, mentre SUBTOTAL(109, A2:A10) esclude le righe nascoste manualmente dal calcolo della somma. In entrambi i casi, le righe filtrate sono automaticamente escluse dal risultato.

Esempio pratico della funzione SUBTOTAL()

Vediamo come funziona SUBTOTAL() con un esempio pratico usando dati di vendita di un negozio di elettronica e arredamento.

Dataset non filtrato. Immagine dell’autore. 

I nostri dati di esempio contengono sette prodotti in due categorie: quattro articoli di Elettronica (Laptop, Phone, Tablet, Monitor) e tre di Arredamento (Desk, Chair, Lamp). Senza filtri applicati, sia la formula SUBTOTAL(109,C2:C8) sia la formula SUM(C2:C8) mostrano lo stesso risultato: 3525 (il totale di tutte le vendite).

Dataset filtrato. Immagine dell’autore. 

Ora abbiamo applicato un filtro per mostrare solo i prodotti di Elettronica, nascondendo le tre righe di Arredamento. Nota la differenza chiave nei risultati del calcolo:

• Formula SUBTOTAL mostra 3000 - si adatta automaticamente sommando solo le righe visibili di Elettronica
• Formula SUM normale mostra ancora 3525 - continuando a includere gli articoli di Arredamento nascosti

Questo confronto affiancato dimostra il vantaggio principale di SUBTOTAL(): risponde in modo dinamico ai filtri e ai cambi di visibilità dei dati, mentre funzioni tradizionali come SUM() restano statiche. Quando filtri i dati o nascondi righe, SUBTOTAL() ricalcola all’istante riflettendo solo ciò che è visibile, diventando essenziale per dashboard reattivi e report interattivi.

Come SUBTOTAL() gestisce dati nascosti e filtrati

La funzione SUBTOTAL() gestisce in modi specifici i diversi tipi di dati nascosti; è importante comprenderli quando costruisci le formule.

SUBTOTAL() con righe filtrate

Quando applichi un filtro al dataset, SUBTOTAL() esclude sempre le righe filtrate dai suoi calcoli, indipendentemente dal numero funzione utilizzato. Questo comportamento è automatico e coerente per tutti i numeri funzione (sia 1-11 sia 101-111).

Ad esempio, se hai dati di vendita da gennaio a dicembre e applichi un filtro per mostrare solo i mesi del Q1 (gennaio, febbraio, marzo), SUBTOTAL(9, B2:B13) calcolerà la somma solo di quei tre mesi visibili, ignorando i dati filtrati di Q2-Q4.

SUBTOTAL() con righe nascoste manualmente

Le righe nascoste manualmente sono gestite in modo diverso a seconda del numero funzione scelto. Quando fai clic destro sui numeri di riga e selezioni "Nascondi", stai nascondendo manualmente quelle righe.

Usando i numeri funzione 1-11, queste righe nascoste manualmente sono incluse nei calcoli. Usando i numeri 101-111, vengono escluse. Questo ti dà controllo sul fatto che i dati nascosti debbano influire o meno sui risultati.

SUBTOTAL() con intervalli orizzontali e verticali

La funzione SUBTOTAL() lavora al meglio con intervalli verticali (colonne di dati). Sebbene possa gestire intervalli orizzontali, il suo comportamento rispetto al nascondimento è ottimizzato per operazioni basate sulle righe, poiché le funzionalità di filtro e nascondi di Excel lavorano verticalmente.

Quando annidi formule SUBTOTAL() all’interno dello stesso intervallo, la funzione salta automaticamente gli altri risultati di SUBTOTAL() per evitare doppi conteggi. Ciò è utile quando crei riepiloghi gerarchici o subtotali all’interno di gruppi, garantendo che i totali riflettano correttamente ciascun raggruppamento distinto senza duplicazioni.

Usi avanzati della funzione SUBTOTAL()

Creare un riepilogo dinamico con menu a discesa usando SUBTOTAL()

Puoi combinare SUBTOTAL() con la funzione IF() per creare riepiloghi interattivi che cambiano in base alla selezione dell’utente. Questo approccio usa un elenco a discesa per permettere all’utente di passare tra diversi tipi di calcolo.

Per creare un riepilogo dinamico, imposta innanzitutto un menu a discesa nella cella D1 con opzioni come "Sum", "Average", "Count" e "Max".

Poi usa la seguente formula in un’altra cella (ad es. D2) per adeguare dinamicamente il calcolo in base alla scelta dell’utente:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

Questa formula valuta la selezione del menu a discesa e applica la corrispondente funzione SUBTOTAL(). Gli utenti possono passare tra i diversi tipi di calcolo senza modificare le formule, rendendo i report più interattivi e facili da usare.

SUBTOTAL() nelle tabelle e nei livelli struttura di Excel

Le Tabelle di Excel usano automaticamente SUBTOTAL() nella riga Totale. Quando converti l’intervallo dati in Tabella (Ctrl+T) e abiliti la riga Totale, Excel inserisce di default SUBTOTAL(109, …) per i calcoli di somma. Questa riga Totale si aggiorna dinamicamente quando le righe vengono filtrate, adeguando all’istante i risultati ai soli dati visibili.

Puoi cambiare il tipo di calcolo facendo clic sulla freccia del menu a discesa in una cella della riga Totale e selezionando opzioni come Conteggio, Media, Max o Min. Excel aggiorna automaticamente il numero funzione mantenendo la struttura SUBTOTAL().

La funzionalità Struttura (Dati > Subtotale) si basa anch’essa sulle formule SUBTOTAL(). Quando raggruppi i dati e inserisci subtotali, Excel posiziona automaticamente funzioni SUBTOTAL() a ogni interruzione di gruppo. Questo crea riepiloghi gerarchici che rispondono correttamente quando espandi o comprimi i livelli di struttura, poiché la funzione ignora i risultati dei subtotali annidati.

Insidie comuni e limitazioni

Capire gli errori comuni e le limitazioni di SUBTOTAL() ti aiuta a evitare sbagli di calcolo e a scegliere l’approccio giusto per le tue esigenze di analisi.

• Errore #VALUE! con numeri funzione non validi: l’errore più frequente si verifica usando un numero funzione non valido. SUBTOTAL() accetta solo i numeri funzione 1-11 e 101-111. Usare numeri come 12, 50 o 200 restituisce un errore #VALUE!. Verifica sempre che il numero funzione corrisponda a un’operazione valida tra quelle supportate.

• Comportamento colonne nascoste vs righe nascoste: mentre SUBTOTAL() risponde alle righe nascoste in base al numero funzione scelto, non ha lo stesso comportamento con le colonne nascoste. La funzione include i dati delle colonne nascoste in tutti i calcoli, indipendentemente dal fatto che usi i numeri 1-11 o 101-111. Questa limitazione richiede approcci alternativi quando la visibilità delle colonne influisce sulla tua analisi.

• Riferimenti 3D non supportati: a differenza di molte funzioni di Excel, SUBTOTAL() non può fare riferimento a intervalli su più fogli di lavoro. Formule come SUBTOTAL(109, Sheet1:Sheet3!A1:A10) restituiscono errori. Come soluzione, puoi prima calcolare somme o altri aggregati separatamente su ciascun foglio con funzioni standard come SUM(), quindi consolidare questi risultati intermedi con un unico SUBTOTAL in un foglio di riepilogo.

• I valori di errore restano nei calcoli: SUBTOTAL() non ignora le celle che contengono valori di errore come #N/A, #DIV/0! o #VALUE!. Questi errori si propagano nei calcoli del subtotale, potenzialmente invalidando l’intero risultato. Pulisci i dati dagli errori prima di applicare SUBTOTAL() o considera l’uso della funzione AGGREGATE(), che può saltare i valori di errore.

• L’orientamento verticale dei dati funziona meglio: sebbene SUBTOTAL() possa gestire intervalli orizzontali, è progettata per strutture di dati verticali. Le funzionalità di filtro, ordinamento e nascondi di Excel lavorano riga per riga, rendendo gli schemi verticali più compatibili con il comportamento previsto della funzione.

Subtotal() vs. AGGREGATE()

La funzione AGGREGATE() è un’alternativa avanzata a SUBTOTAL() con capacità aggiuntive per la gestione degli errori e più operazioni statistiche. Mentre SUBTOTAL() offre 11 operazioni di base, AGGREGATE() fornisce 19 funzioni diverse tra cui percentili, quartili e calcoli della mediana.

Il vantaggio principale di AGGREGATE() risiede nelle capacità di gestione degli errori. A differenza di SUBTOTAL(), che include i valori di errore nei calcoli, AGGREGATE() può saltare automaticamente le celle contenenti errori come #N/A, #DIV/0! o #VALUE!. Ciò la rende particolarmente utile con set di dati incompleti o problematici.

AGGREGATE() offre anche un controllo più granulare su cosa ignorare. Puoi configurarla per saltare righe nascoste, subtotali annidati, valori di errore o qualsiasi combinazione di questi elementi tramite il parametro delle opzioni.

Consigli pratici per usare SUBTOTAL()

Ordina sempre prima

Quando usi la funzione integrata Dati > Subtotale di Excel, ordina prima i dati per la colonna di raggruppamento. In questo modo tutti i record correlati appaiono insieme, creando interruzioni di gruppo pulite per i calcoli dei subtotali. Dati non ordinati producono subtotali sparsi che non offrono riepiloghi significativi.

Usa etichette nella prima riga delle colonne

Inserisci intestazioni descrittive nella prima riga dell’intervallo dati prima di applicare i subtotali. Excel usa queste etichette per creare descrizioni significative dei subtotali e rende i report più facili da interpretare. Intestazioni chiare aiutano anche nella selezione degli intervalli per formule SUBTOTAL() manuali.

Attenzione al posizionamento del riepilogo (sopra o sotto)

Lo strumento Subtotale di Excel posiziona di default le righe di riepilogo sotto ogni gruppo, ma puoi scegliere di metterle sopra. Considera l’uso previsto del report quando fai questa scelta. Le righe di riepilogo sopra i gruppi funzionano bene per dashboard direzionali, mentre quelle sotto i gruppi sono in linea con i formati contabili tradizionali.

Nascondi vs filtra righe per un controllo preciso

Comprendi la differenza tra filtrare e nascondere manualmente le righe per ottenere i risultati attesi. Usa i filtri quando vuoi che SUBTOTAL() ignori temporaneamente alcuni dati. Usa il nascondimento manuale (clic destro > Nascondi) quando vuoi esclusioni più permanenti, quindi scegli i numeri funzione 101-111 per rispettare lo stato di nascosto.

Questo approccio ti dà un controllo a più livelli: filtra per viste temporanee dei dati, nascondi manualmente per esclusioni semi-permanenti e seleziona i numeri funzione appropriati per rispettare le tue intenzioni di nascondimento.

Conclusione

Imparare a usare SUBTOTAL() in modo efficace trasforma il tuo approccio all’analisi dei dati in Excel. Invece di creare calcoli statici che si rompono quando i dati cambiano, puoi costruire formule robuste che si adattano automaticamente alle azioni degli utenti e alle modifiche dei dati. Questa abilità diventa particolarmente preziosa quando lavori con grandi set di dati o crei report che più utenti filtreranno e manipoleranno nel tempo.

Per costruire una competenza completa in Excel oltre le singole funzioni come SUBTOTAL(), il nostro percorso di competenze Excel Fundamentals offre un itinerario strutturato di 16 ore che ti porta dalla preparazione dei dati fino ad analisi avanzate e tecniche di visualizzazione. Per chi è pronto a esplorare subito applicazioni analitiche, il nostro corso Data Analysis in Excel insegna l’uso avanzato delle tabelle pivot e funzioni logiche per insight più approfonditi.