SUBTOTALE in Excel: totali che saltano subtotali e filtri

=SUBTOTALE(9;C2:C8) somma i numeri di C2:C8, come SOMMA, con due differenze: salta le altre formule SUBTOTALE dentro l'intervallo e salta le righe nascoste da un filtro. Il primo argomento, 9, dice quale calcolo fare. SUBTOTALE si chiama SUBTOTAL in Excel inglese, ed è così che la tabella mostra le formule; puoi però scriverle anche in italiano, con il punto e virgola: =SUBTOTALE(9;C2:C7).

FormuleRipristina

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =SUBTOTALE(9;C2:C7)

Il totale generale in C8 copre tutta la colonna, righe dei subtotali comprese, e mostra comunque 445: SUBTOTALE lascia fuori C4 e C7 perché contengono formule SUBTOTALE. F2 fa lo stesso con SOMMA (in inglese SUM) e mostra 890, ogni vendita contata due volte. Con SUBTOTALE su ogni riga di totale puoi aggiungere o spostare gruppi senza riscrivere il totale generale.

Numeri di funzione di SUBTOTALE

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

Quando scrivi =SUBTOTALE(, Excel mostra questo elenco, quindi non devi impararlo a memoria. 9 e 109 (SOMMA), 1 (MEDIA) e 103 (contare le righe visibili) sono i più usati.

FormuleRipristina

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =SUBTOTALE(1;C2:C7)

Qui non c'è nulla di nascosto, quindi ogni riga è uguale alla funzione normale: una media di 88.33, 6 righe, un MAX di 200, un MIN di 30 e una SOMMA di 530. La differenza si vede solo quando ci sono righe nascoste, ed è l'argomento della sezione successiva.

SUBTOTALE 9 o 109, e le righe filtrate

Attiva un filtro con Dati > Filtro (Ctrl+Maiusc+L, Cmd+Maiusc+F su Mac), poi scegli North nel menu a discesa di Region. Le righe delle altre regioni vengono nascoste:

• =SOMMA(C2:C7) somma ancora tutte e sei le righe.
• =SUBTOTALE(9;C2:C7) e =SUBTOTALE(109;C2:C7) sommano solo le righe North visibili.
• =SUBTOTALE(103;A2:A7) conta le righe rimaste visibili: 2, lo stesso numero di "2 di 6 record trovati" nella barra di stato.

Le due famiglie differiscono solo per le righe che nascondi a mano (seleziona le righe, clic destro > Nascondi). 9 le somma ancora; 109 no. Se il totale deve sempre corrispondere a ciò che vedi, usa 109. Se nascondi le righe solo per ordinare la vista e le vuoi comunque nel totale, usa 9.

SUBTOTALE lavora solo sulle righe. Le colonne nascoste sono sempre incluse, quindi =SUBTOTALE(109;B2:G2) lungo una riga somma anche le colonne nascoste.

Il modo più veloce per ottenere un SUBTOTALE è il pulsante Somma automatica con un filtro attivo: Excel scrive =SUBTOTALE(9;...) invece di SOMMA. Dati > Subtotale va oltre: su un elenco ordinato per una colonna, inserisce una riga di totale sotto ogni gruppo e un totale generale, tutti con SUBTOTALE, più i pulsanti di struttura per comprimere i gruppi.

AGGREGA: un SUBTOTALE che sa saltare gli errori

Se una cella dell'intervallo contiene un errore, SOMMA e SUBTOTALE restituiscono quell'errore. AGGREGA (in inglese AGGREGATE, da Excel 2010) è SUBTOTALE con un argomento di opzioni in più; l'opzione 6 ignora i valori di errore.

FormuleRipristina

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =AGGREGA(9;6;C2:C7)

C3 contiene #N/A (in Excel italiano #N/D; la tabella mostra i nomi inglesi degli errori), quindi anche F2 mostra #N/A. F3 lo ignora e somma gli altri cinque: 450. Il suo primo argomento usa gli stessi numeri di SUBTOTALE (9 è SOMMA, 4 è MAX). Altre opzioni: 5 ignora le righe nascoste, 7 ignora le righe nascoste e gli errori, 3 ignora le righe nascoste, gli errori e le formule SUBTOTALE e AGGREGA annidate. Sostituisci C3 con un numero e F2 mostra lo stesso totale di F3.

Esercizio: un totale generale sopra i subtotali

FormuleRipristina

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.

Tocca a te: L'elenco ha un subtotale sotto ogni regione. Metti in C9 un totale generale che copra C2:C8 senza contare due volte le righe dei subtotali.

VerificaMostra la risposta

Perché un totale con SUBTOTALE è ancora sbagliato

• I totali dei gruppi usano SOMMA. SUBTOTALE salta le altre formule SUBTOTALE dentro il suo intervallo, non le formule SOMMA. Un totale di gruppo scritto come =SOMMA(C2:C3) viene contato di nuovo. Trasforma ogni riga di totale in SUBTOTALE.
• Le righe sono state nascoste a mano e il numero di funzione è 9. Usa 109.
• I dati sono in colonne, non in righe. Le colonne nascoste non vengono mai saltate.
• Ti serve una condizione, non un filtro. SUBTOTALE segue ciò che il filtro nasconde. Per totalizzare North senza filtrare, usa SOMMA.SE. Per un riepilogo di tutti i gruppi in una volta, una tabella pivot lo fa senza righe di totale nei dati.