Funzione SUBTOTALE: formula e come utilizzare la funzione SUBTOTALE in Excel

Cos'è la funzione SUBTOTALE in Excel ? Qual è la formula SUBTOTALE in Excel ? Scopriamolo con LuckyTemplates.com!

Microsoft Excel dispone di numerose funzioni utilizzate per calcolare i totali e SUBTOTALE è una di queste. Tuttavia, il totale parziale è diverso dalla funzione SOMMA. Non è una somma normale. Viene invece utilizzato per calcolare i totali parziali in elenchi o database, calcolare le medie e altro ancora.

Contenuto principale sulla funzione SUBTOTALE

• Cos'è la funzione SUBTOTALE e a cosa serve?
• Formula SUBTOTALE in Excel
• Come utilizzare la funzione SUBTOTALE in Excel
  • 1. Calcola il totale delle righe filtrate 
  • 2. Contare le celle filtrate che non sono vuote
  • 3. Ignora i valori nelle formule di subtotale nidificate
• Cose da considerare prima di utilizzare la funzione SUBTOTALE in Excel
• Come si verifica un errore durante l'esecuzione della funzione SUBTOTALE in Excel

Cos'è la funzione SUBTOTALE e a cosa serve?

A prima vista potresti chiederti: qual è lo scopo della funzione SUBTOTALE? Dopotutto, è facile trovare un totale parziale da un intervallo di celle utilizzando funzioni esistenti come SOMMA.SE o MEDIA .

Anche se è vero che non è necessariamente necessario utilizzare SUBTOTALE, ciò rende molto più semplice l'analisi dei dati mirati. È possibile inserire una formula utilizzando SUBTOTALE in un intervallo di celle senza influire sul totale complessivo, poiché SUBTOTALE ignora le altre celle che contengono una formula SUBTOTALE.

SUBTOTALE funziona con altre funzionalità di Excel, come il filtraggio delle celle. Se filtri una tabella in base a un valore, la formula SUBTOTALE si aggiorna, riconosce il filtro ed esclude le celle corrispondenti. SUBTOTALE è utile anche per ignorare i valori nascosti, cosa che altre funzioni (come SOMMA) non possono fare.

Fortunatamente, puoi utilizzare altre funzioni come parte di una formula SUBTOTALE. Sono disponibili 11 diversi metodi di totale parziale, ciascuno adatto a una funzione Excel esistente. Se desideri una formula SOMMA che funzioni con le celle filtrate e ignori le altre formule SUBTOTALE, è meglio utilizzare SUBTOTALE, sebbene una tabella pivot possa funzionare allo stesso modo.

Formula SUBTOTALE in Excel

La funzione SUBTOTALE funziona calcolando il valore totale di un intervallo di celle, in base a un'altra funzione matematica. SUBTOTALE utilizza fino a 11 diverse funzioni matematiche (da MEDIA a VAR.P) per trovare il valore totale appropriato, lavorando all'interno dei parametri della funzione (ovvero ignorando altre formule SUBTOTALE).

La funzione SUBTOTALE ha la sintassi =SUBTOTALE(function_num, ref1, [ref2],…) . Lì dentro:

• Function_num: i numeri da 1 a 11 e da 101 a 111 specificano quale funzione verrà utilizzata per calcolare SUBTOTALE.
• Rif1, Rif2, ...: 1 o più celle o intervallo di celle per calcolare i totali parziali, massimo 254.

Nota:

• La funzione SUBTOTALE è progettata per calcolare colonne verticali di dati.
• Se gli argomenti rif1, rif2,... contengono la funzione SUBTOTALE, verranno ignorati per evitare un doppio conteggio.
• Se function_num è compreso tra 1 e 11, la funzione SUBTOTALE calcola includendo i valori nascosti nel set di dati perché la riga contenente tale intervallo è nascosta. Se function_num è compreso tra 101 e 111, la funzione SUBTOTALE calcola solo i valori non nascosti nel set di dati (senza contare i valori nelle righe nascoste).
• Per le aree dati nascoste dal filtro, il SUBTOTALE verrà ignorato.

Come utilizzare la funzione SUBTOTALE in Excel

1. Calcola il totale delle righe filtrate 

Per comprendere meglio questo tipo di somme vi rimandiamo all'articolo Calcolare la somma dei valori delle liste filtrate in Excel . Fondamentalmente la funzione SUBTOTALE in questo caso sarà:

=SUBTOTALE(9,_intervallo)

pham_range ecco l'area che vuoi sommare dopo aver filtrato i dati.

2. Contare le celle filtrate che non sono vuote

Utilizzeremo SUBTOTALE 3 o SUBTOTALE 103. Tuttavia, nel caso in cui siano presenti righe nascoste, è necessario utilizzare SUBTOTALE 103 per contare con precisione le celle visibili non vuote.

Ad esempio, la tabella dati sopra nasconderà 2 righe 4 e 5. Quando si utilizza SUBTOTALE 3 o SUBTOTALE 103, produrrà 2 risultati diversi.

Inseriamo la formula e poi Excel mostrerà automaticamente la funzione impostata tra cui scegliere senza doverla ricordare.

Il risultato quando si utilizza SUBTOTALE 3 sarà 3, comprese le celle nascoste nella riga.

Per quanto riguarda SUBTOTALE 103, mostrerà solo le celle non vuote che vediamo, ignorando le righe nascoste.

3. Ignora i valori nelle formule di subtotale nidificate

Ad esempio, calcoleremo la media dei chilogrammi totali di tessuto nei magazzini A1 e A2.

La formula media per il magazzino A2= SUBTOTALE(1,C2:C4) e restituisce il risultato 19.

La formula media per il magazzino A1 = SUBTOTALE(1,C5:C7) ha come risultato 38.

Tuttavia, quando si calcola la media del numero totale di tessuti in 2 magazzini, i risultati medi nei 2 magazzini verranno ignorati. Abbiamo la formula =SUBTOTALE(1,C2:C9) e il risultato rimuoverà automaticamente il risultato medio calcolato in precedenza.

Cose da considerare prima di utilizzare la funzione SUBTOTALE in Excel

Sebbene SUBTOTALE abbia i suoi vantaggi, ci sono alcune cose che dovresti considerare prima di iniziare a utilizzare questa funzione, tra cui quanto segue:

• Come accennato, l'utilizzo di 1-11 per l'argomento function_num garantirà che SUBTOTAL includa valori nascosti, mentre 101-111 li ignori.
• L'utilizzo di un valore diverso da 1-11 o 101-111 farà sì che Excel restituisca l'errore #VALORE!. Ciò accadrà anche per i riferimenti di cella 3D (dove si fa riferimento alla stessa cella, su più fogli, in un intervallo).
• Quando vengono utilizzati intervalli di celle orizzontali come A1:D1, i valori nascosti vengono inclusi automaticamente (indipendentemente dal valore dell'argomento function_num). Questa è una limitazione della funzione SUBTOTALE e non può essere superata.
• L'utilizzo di SUBTOTALE sui dati filtrati garantirà che i valori nascosti vengano sempre ignorati, indipendentemente dall'argomento function_num utilizzato.
• SUBTOTALE può essere utilizzato come funzione nidificata come parte di altre funzioni, incluse le formule che contengono una funzione SE.
• Se un'altra formula SUBTOTALE rientra nell'intervallo dati rif1, SUBTOTALE la ignora e la esclude dal calcolo complessivo.
• Excel consente di utilizzare fino a 254 intervalli di celle in una formula SUBTOTALE, sebbene sia necessario solo 1 intervallo di celle affinché la formula funzioni.

Come si verifica un errore durante l'esecuzione della funzione SUBTOTALE in Excel

Quando calcoli la funzione SUBTOTALE in Excel, incontrerai spesso alcuni errori di base:

• #VALORE!: l'identificatore della funzione non è compreso nell'intervallo 1-11 o 101-111 o ha un riferimento 3D.
• #DIV/0!: si verifica quando una somma specifica deve essere divisa per 0 (ad esempio, calcolando la media o la deviazione standard di un intervallo di celle che non contiene valori numerici).
• #NOME? Il nome della funzione SUBTOTALE è scritto in modo errato.

Alcuni suggerimenti su come utilizzare la funzione SUBTOTALE in Microsoft Excel che potresti non conoscere

Suggerimento 1: supponiamo che tu voglia assicurarti che tutti i colori delle magliette siano disponibili in ogni taglia in entrambi i magazzini. Puoi seguire questi passaggi:

Passaggio 1: fare clic su Totale parziale . Ricorda che questo passaggio consiste nell'aggiungere più criteri ai dati del totale parziale esistenti.

Passaggio 2: seleziona COUNT dal menu a discesa e Dimensioni dal campo " Aggiungi campo subtotale a ". Quindi, deseleziona Sostituisci i totali parziali correnti . Quando fai clic su OK , riceverai la seguente tabella di dati:

Ciò aiuta a garantire di disporre di un numero sufficiente di dimensioni diverse e di poter classificare i dati in modo da non richiedere operazioni ripetute.

Suggerimento 2 : è sempre utile ordinare i dati per colonna.

Suggerimento 3: ti aiuta a includere un'etichetta nella prima riga.

Suggerimento 4: se desideri riepilogare i dati, deseleziona la casella " Riepilogo sotto i dati quando si inserisce il totale parziale ". - Riepilogo sotto i dati quando si inserisce la funzione SUBTOTALE.

In breve, qual è la funzione Subtotale in Excel?

Come suggerisce il nome, la funzione SUBTOTALE in Excel viene utilizzata per calcolare i subtotali. La funzione SUBTOTALE di Excel restituisce risultati aggregati per i valori forniti. SUBTOTALE può calcolare somma, media, conteggio, massimo... Questo Excel può anche includere o escludere valori nelle righe nascoste.

• Scopo: Calcola il totale parziale in un elenco o database.
• Valore restituito: un numero che rappresenta un tipo di totale parziale.
• Discussione:
  • function_num- Un numero che specifica quale funzione utilizzare per calcolare i totali parziali in un elenco.
  • ref1- Un intervallo denominato o un riferimento a un totale parziale.
  • ref2- [facoltativo] Un intervallo denominato o un riferimento a un totale parziale.

L'utilizzo della funzione SUBTOTALE può aiutarti ad analizzare rapidamente un set di dati senza fare affidamento su tabelle pivot più complesse. Se rimani bloccato, puoi anche utilizzare la funzione Subtotale, disponibile nella scheda Dati sulla barra multifunzione, per creare automaticamente una formula SUBTOTALE che puoi quindi modificare o copiare altrove.

Vedi altro:

• Funzione SUMPRODUCT in Excel: calcola il prodotto totale dei valori corrispondenti
• Come combinare 2 colonne Nome completo e Nome in Excel senza perdere contenuto