Come sommare i valori escludendo o senza subtotali in Excel?
Quando si lavora con set di dati in Excel, è comune organizzare e analizzare i numeri calcolando i subtotali per diversi gruppi o categorie. Tuttavia, dopo aver aggiunto questi subtotali, potresti voler calcolare un totale generale che esclude tali valori parziali, evitando qualsiasi doppio conteggio nel totale finale. La sfida è che, quando si sommano direttamente tutti i numeri, i subtotali vengono inclusi nel totale complessivo, risultando in una somma totale più grande del previsto. Per evitarlo, devi trovare un modo per sommare i dati originali escludendo le righe dei subtotali. In questo articolo, presentiamo diversi metodi pratici per risolvere questo problema, così puoi calcolare risultati accurati in modo efficiente in diversi scenari.
Sommare i valori senza subtotali con la funzione Somma in Excel
Un approccio ampiamente adottato per calcolare i subtotali di gruppo è inserire la funzione SOMMA all'interno o sotto ciascun gruppo pertinente. Tuttavia, quando desideri calcolare il totale generale per l'intera lista ignorando queste righe di subtotali di gruppo, è spesso necessario un passaggio aggiuntivo. Ecco un metodo che puoi utilizzare per raggiungere questo obiettivo:
1. Utilizza la funzione SOMMA all'interno di ciascun gruppo per generare i rispettivi subtotali, come mostrato in questo screenshot:
2. Successivamente, per trovare il totale generale escludendo quelle righe di subtotali, inserisci la seguente formula in una cella vuota dove desideri che appaia il risultato:
=SOMMA(B2:B21)/2
Premi Invio per ottenere il totale corretto. Questa formula funziona perché presuppone che tu abbia inserito i subtotali con la funzione SOMMA immediatamente dopo i gruppi pertinenti, raddoppiando efficacemente la somma dei dati grezzi. Dividendo per 2 si rimuove il totale duplicato. Nota che questo metodo è più adatto quando il set di dati è strutturato in modo tale che la somma dei valori originali e dei subtotali formi esattamente il doppio della somma dei dati originali.
Se la struttura dei tuoi dati è più complessa o il numero di righe di subtotali varia, i metodi alternativi descritti di seguito possono offrire una soluzione più accurata e flessibile.
Somma valori senza subtotali con la funzione Subtotale in Excel
La funzione SUBTOTALE in Excel fornisce un modo integrato per eseguire calcoli solo sulle righe filtrate o visibili. Questo la rende particolarmente utile quando si lavora con elenchi contenenti subtotali, poiché le formule SUBTOTALE possono essere configurate per ignorare altri risultati SUBTOTALE all'interno dell'intervallo, prevenendo qualsiasi doppio conteggio dei valori.
Per utilizzare la funzione SUBTOTALE in questo contesto, segui questi passaggi:
1. Inserisci la funzione SUBTOTALE per ciascun gruppo come segue:
=SUBTOTALE(9;B2:B10)
Questo applica l'operazione SOMMA (numero di funzione 9) all'intervallo specificato ed è spesso usato come parte della funzione incorporata Dati > Subtotale di Excel.
2. Per il totale generale che esclude i subtotali interni, inserisci la seguente formula nella cella scelta:
=SUBTOTALE(9;B2:B21)
Premi Invio per sommare automaticamente i valori dei dati, ignorando qualsiasi funzione SUBTOTALE nidificata all'interno dell'intervallo. Questo approccio è adatto per elenchi che utilizzano la funzione SUBTOTALE per riassunti di gruppo ed elimina il rischio di sommare più volte i subtotali.
Tieni presente che la funzione SUBTOTALE funziona meglio quando le celle dei subtotali sono state generate utilizzando SUBTOTALE piuttosto che SOMMA. Inoltre, se filtri o nascondi righe, SUBTOTALE può essere impostato per sommare solo i dati visibili (non nascosti), il che è utile in situazioni di report dinamici.
Codice VBA - Sommare i valori escludendo le righe dei subtotali
Se la struttura dei tuoi dati è complessa o desideri un metodo automatizzato per sommare solo le righe dei dati grezzi (escludendo i subtotali), puoi utilizzare una semplice macro VBA. Questo approccio ti consente di identificare e sommare programmaticamente solo quelle righe che soddisfano i tuoi criteri, come sulla base di una formattazione specifica, la presenza di formule o altre caratteristiche distintive.
Ad esempio, se le righe dei subtotali sono calcolate con formule (come SOMMA o SUBTOTALE) e le righe dei dati grezzi contengono solo valori statici, puoi impostare il codice VBA per sommare solo quelle celle in una colonna specifica che non contengono formule. Ecco come puoi farlo:
1. Clicca Strumenti di sviluppo > Visual Basic per aprire la finestra Microsoft Visual Basic for Applications.
2. Nella nuova finestra, clicca Inserisci > Modulo e incolla il seguente codice nel modulo:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Clicca il pulsante 
Esegui per eseguire il codice. Apparirà un prompt per selezionare l'intervallo contenente i tuoi dati. La macro sommerà quindi solo le celle della tua selezione che non contengono formule, saltando efficacemente le tipiche righe dei subtotali.
Filtraggio - Usa filtro e SUBTOTALE per sommare solo le righe visibili (non subtotali)
Se il tuo set di dati ti permette di filtrare le righe dei subtotali (ad esempio, c'è un'etichetta, una parola chiave o un modello per identificarle), puoi utilizzare la funzione Filtro incorporata di Excel insieme alla funzione SUBTOTALE per aggiungere solo le righe visibili (non filtrate). Questa tecnica è pratica nei casi in cui i subtotali abbiano un indicatore o un'etichetta coerente.
1. Clicca ovunque nel tuo set di dati, poi vai alla scheda Dati e clicca Filtro per abilitare i filtri per le colonne.
2. Clicca il menu a discesa nella tua colonna ausiliaria, deseleziona "Subtotale" per nascondere le righe dei subtotali e visualizzare solo i dati originali.
3. In una cella separata, inserisci la seguente formula per sommare solo le righe visibili (filtrate):
=SUBTOTAL(9,B2:B21)La funzione SUBTOTALE (con numero di funzione 9 per SOMMA) ignorerà tutte le righe nascoste, restituendo il totale solo delle righe attualmente visibili (cioè, le righe non-subtotale che hai mantenuto dopo il filtraggio). Questa soluzione è rapida e dinamica – se riapplichi i filtri o espandi il tuo set di dati, la formula si aggiornerà di conseguenza.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in