Quando un foglio Excel contiene errori, righe filtrate o valori nascosti, una semplice SOMMA può restituire un risultato poco utile. La funzione AGGREGA di Excel risolve il problema scegliendo il tipo di calcolo e gli elementi da escludere, con esempi pratici per somme, medie, mediane e classifiche.
Un unico strumento per calcolare dati puliti e selezionati
- 19 funzioni disponibili, tra cui SOMMA, MEDIA, MAX, MEDIANA, GRANDE e PICCOLO.
- Le opzioni permettono di ignorare righe nascoste, errori e formule annidate.
- La sintassi italiana usa normalmente il punto e virgola come separatore.
- La formula è particolarmente utile su elenchi filtrati e dati incompleti.
- Esistono due modalità, quella con riferimenti e quella con matrici o array calcolati.
Che cosa fa davvero la funzione AGGREGA
AGGREGA combina in una sola formula molte operazioni che normalmente richiederebbero funzioni diverse. Io la considero una sorta di livello di controllo aggiuntivo sopra SOMMA, MEDIA o MAX, perché permette di decidere quali dati devono entrare nel calcolo.
Il vantaggio si nota subito quando un intervallo contiene valori come #N/D, #DIV/0! o #VALORE!. Con una formula tradizionale, l’errore può propagarsi al risultato; con AGGREGA, invece, si può scegliere di ignorare i valori problematici e ottenere comunque un numero utilizzabile.
La funzione è disponibile nelle versioni moderne di Excel, comprese Microsoft 365, Excel 2024, Excel 2021, Excel 2019, Excel 2016 ed Excel per il Web. I casi d’uso più frequenti sono tre:
- calcolare solo i valori visibili dopo un filtro;
- ignorare errori presenti in una colonna;
- trovare valori massimo, minimo, mediano o percentili senza ripulire prima tutto l’intervallo.
Non la userei, però, per ogni somma ordinaria. Se i dati sono già puliti e non ci sono righe filtrate, SOMMA resta più semplice da leggere. AGGREGA dà il meglio quando il foglio deve resistere a dati reali, modifiche e situazioni non perfette.
La sintassi e le opzioni da scegliere
La funzione ha due forme principali. La prima lavora con riferimenti a celle:
=AGGREGA(num_funzione; opzioni; rif1; [rif2]; ...)
La seconda lavora con una matrice o un array:
=AGGREGA(num_funzione; opzioni; matrice; [k])
Il primo argomento indica l’operazione. I codici più utili nella pratica sono:
| Codice | Operazione | Esempio d’uso |
|---|---|---|
| 1 | MEDIA | Media dei valori |
| 4 | MAX | Valore più alto |
| 5 | MIN | Valore più basso |
| 9 | SOMMA | Totale dell’intervallo |
| 12 | MEDIANA | Valore centrale |
| 14 | GRANDE | Primo, secondo o terzo valore più alto |
| 15 | PICCOLO | Primo, secondo o terzo valore più basso |
Il secondo argomento è quello che rende AGGREGA più flessibile. Le opzioni da ricordare sono soprattutto 6 e 7, perché risolvono i problemi più comuni.
| Opzione | Ignora | Quando usarla |
|---|---|---|
| 0 | SUBTOTALE e AGGREGA annidate | Quando gli errori non sono un problema |
| 1 | Righe nascoste e formule annidate | Per analizzare solo i dati visibili |
| 2 | Errori e formule annidate | Quando le righe nascoste devono restare incluse |
| 3 | Righe nascoste, errori e formule annidate | Per un’elaborazione molto selettiva |
| 6 | Valori di errore | Per mantenere anche le righe nascoste nel calcolo |
| 7 | Righe nascoste e valori di errore | Per ottenere il totale dei dati visibili e validi |
Le opzioni da 0 a 3 ignorano anche i SUBTOTALE e le AGGREGA presenti nell’intervallo. Quelle da 4 a 7 non escludono automaticamente le formule annidate. Questa differenza sembra piccola, ma può cambiare il totale in un report costruito su più livelli.
[search_image]funzione AGGREGA Excel opzioni righe nascoste errori esempio formula]
Esempi pratici per lavorare su dati reali
Sommare solo i valori visibili e senza errori
Immaginiamo una colonna C con importi di vendita, alcune righe filtrate e qualche errore dovuto a una formula di ricerca. Per sommare soltanto i valori visibili e ignorare gli errori, uso:
=AGGREGA(9;7;C2:C100)
Il codice 9 richiama SOMMA, mentre l’opzione 7 esclude sia le righe nascoste sia gli errori. È una formula molto adatta ai report commerciali, perché il risultato cambia quando si applica un filtro per agente, area o mese.
Calcolare una media senza farsi bloccare dagli errori
Per calcolare la media degli importi validi nella colonna D:
=AGGREGA(1;6;D2:D100)
In questo caso AGGREGA ignora gli errori, ma considera anche le righe nascoste. Io preferisco questa impostazione quando le righe sono state nascoste solo per motivi di visualizzazione e devono comunque contribuire all’analisi.
Trovare il terzo valore più alto
Per individuare il terzo importo più alto, anche se nell’intervallo sono presenti errori:
=AGGREGA(14;6;E2:E100;3)
Il codice 14 corrisponde a GRANDE e l’ultimo argomento, 3, significa “terzo elemento”. La stessa logica permette di creare classifiche rapide senza ordinare fisicamente la tabella.
Calcolare la mediana di una colonna sporca
La mediana è spesso più rappresentativa della media quando pochi valori molto alti alterano il risultato. Per calcolarla ignorando gli errori:
=AGGREGA(12;6;B2:B50)
Il codice 12 richiama MEDIANA. In un’analisi di stipendi, tempi di consegna o valori delle transazioni, questo approccio aiuta a leggere il comportamento tipico senza farsi condizionare troppo dagli estremi.
Usare un criterio dentro una matrice
AGGREGA può anche lavorare su un array calcolato. Per ottenere il valore massimo della colonna A solo quando nella colonna B compare “Nord”:
=AGGREGA(14;6;A2:A100/(B2:B100="Nord");1)
Le righe che non rispettano il criterio generano un errore di divisione, che l’opzione 6 esclude. È una tecnica potente, ma la formula diventa meno intuitiva: per un foglio condiviso con molti utenti, una combinazione di FILTRO, MAX e SE può risultare più leggibile nelle versioni recenti di Excel.
AGGREGA o SUBTOTALE quale scegliere
Le due funzioni vengono spesso confuse perché entrambe possono lavorare con elenchi filtrati. La differenza principale è che AGGREGA offre più operazioni e un controllo migliore sugli errori, mentre SUBTOTALE è più immediata per totali e conteggi di base.
| Esigenza | Funzione più adatta | Motivo |
|---|---|---|
| Somma semplice su righe filtrate | SUBTOTALE | Formula breve e facile da comprendere |
| Somma con errori nell’intervallo | AGGREGA | Può ignorare i valori di errore |
| Mediana o percentile sui dati visibili | AGGREGA | SUBTOTALE non offre queste operazioni |
| Report con formule annidate | AGGREGA | Consente di escludere risultati già aggregati |
Per esempio, =SUBTOTALE(9;C2:C100) può essere sufficiente per una somma filtrata. Se però nella colonna ci sono errori, il risultato può diventare inutilizzabile. In quel caso preferisco =AGGREGA(9;7;C2:C100), purché sia chiaro a chi leggerà il file che le righe nascoste e gli errori vengono esclusi.
La scelta non è quindi una gara tra funzioni. Conta soprattutto la trasparenza del modello. Una formula più potente non è automaticamente migliore se nessuno riesce a capire quali dati sta scartando.
Errori comuni e limiti da conoscere
Confondere il codice della funzione con l’opzione
In =AGGREGA(9;7;C2:C100), il 9 indica SOMMA e il 7 indica cosa ignorare. Invertire i due numeri produce un risultato errato o un messaggio di errore. Quando scrivo formule complesse, aggiungo sempre una breve nota accanto alla cella per documentare questa scelta.
Dimenticare l’argomento k
GRANDE, PICCOLO, INC.PERCENTILE, INC.QUARTILE, ESC.PERCENTILE ed ESC.QUARTILE richiedono un argomento aggiuntivo. Per esempio, =AGGREGA(15;6;A2:A20;2) restituisce il secondo valore più piccolo. Se k manca, Excel restituisce #VALORE!.
Aspettarsi che le righe nascoste vengano sempre ignorate
L’opzione 6 ignora gli errori, ma non le righe nascoste. Per entrambe le esclusioni serve l’opzione 7. Inoltre, quando l’argomento è una matrice ottenuta da un calcolo, come A2:A100/(B2:B100="Nord"), AGGREGA non riesce a riconoscere allo stesso modo le righe nascoste del riferimento originale.
Usare intervalli orizzontali o riferimenti 3D
AGGREGA è pensata soprattutto per colonne e intervalli verticali. Nascondere una colonna in un intervallo orizzontale può non modificare il risultato come ci si aspetta. Anche i riferimenti 3D, cioè riferimenti a intervalli distribuiti su più fogli, possono restituire #VALORE!.
Leggi anche: Testo in colonne Excel, guida a separatori e Power Query
Ignorare il significato degli errori
Eliminare #N/D o #DIV/0! dal calcolo non significa che il problema alla fonte sia risolto. Io uso l’opzione 6 o 7 per rendere leggibile un report, ma controllo comunque perché gli errori sono comparsi. Un totale pulito può nascondere dati mancanti, formule sbagliate o collegamenti interrotti.
La regola pratica per usare AGGREGA senza perdere controllo
Prima di scrivere la formula, chiarisco due cose: quale operazione devo eseguire e quali elementi devono essere esclusi. Solo dopo scelgo i codici, perché la maggior parte degli errori nasce dall’uso automatico dell’opzione 7 senza sapere che cosa sta filtrando.
- Usa 6 se devi ignorare soltanto gli errori.
- Usa 7 se vuoi anche escludere le righe nascoste.
- Usa la forma con matrice solo quando ti serve applicare un criterio dentro il calcolo.
- Preferisci SUBTOTALE per un totale filtrato molto semplice e leggibile.
- Documenta sempre l’opzione scelta quando il file viene usato da altre persone.
La funzione diventa davvero utile quando trasforma un foglio fragile in un report più resistente a filtri, errori e dati incompleti. Non sostituisce la pulizia delle informazioni, ma permette di separare il calcolo dalla gestione delle eccezioni, che è spesso la differenza tra una formula solo funzionante e un modello Excel affidabile.
