Come sommare solo le celle visibili in base a criteri specifici in Excel?
In Excel, gli utenti possono generalmente sommare le celle in base a criteri specifici utilizzando la funzione SOMMA.PIÙ.SE. Tuttavia, quando si lavora con dati filtrati, applicare semplicemente SOMMA.PIÙ.SE includerà sia le celle visibili che quelle nascoste nel calcolo. Ciò spesso porta a risultati errati se è necessario sommare solo le celle visibili (cioè non filtrate) che corrispondono a determinati criteri, come mostrato nella schermata sottostante.
Sommare solo le celle visibili basandosi su uno o più criteri con una colonna di supporto
Sommare solo le celle visibili basandosi su uno o più criteri con una formula
Sommare solo le celle visibili basandosi su criteri utilizzando il codice VBA
È una necessità comune nei flussi di lavoro di reportistica quotidiana e analisi dei dati aggregare accuratamente i dati nelle tabelle filtrate, come quando si calcolano gli importi delle vendite per un particolare prodotto o categoria dopo aver applicato alcuni filtri. Fare ciò in modo errato può portare a totali che includono dati non intenzionali, quindi è importante utilizzare tecniche che sommino solo i dati visibili che si vedono sullo schermo.
Questo articolo introduce diversi metodi pratici adatti a vari scenari e livelli di competenza, ognuno con i suoi vantaggi e possibili limitazioni. È possibile selezionare una soluzione che meglio si adatta alla dimensione del foglio di lavoro, alla struttura dei dati e alle abitudini operative. Di seguito vengono forniti i passaggi dettagliati per ogni soluzione, insieme a spiegazioni degli errori potenziali e modi per ottimizzare il processo di calcolo per risultati più affidabili.
Sommare solo le celle visibili basandosi su uno o più criteri con una colonna di supporto
Uno degli approcci più intuitivi e stabili per sommare le celle visibili in base a criteri specifici è utilizzare una colonna di supporto che restituisca valori solo per le righe visibili, e poi sfruttare la funzione SOMMA.PIÙ.SE con le condizioni desiderate. Questo è particolarmente efficace se il set di dati viene filtrato frequentemente in vari modi o se è necessario impostare calcoli che i colleghi possono facilmente comprendere o modificare.
Vantaggi: Semplice da configurare; tutta la logica e i calcoli rimangono visibili nel foglio di lavoro; ideale per tabelle piccole o medie; robusto quando è necessario regolare o verificare le formule.
Limitazioni: Crea colonne aggiuntive; potrebbe essere necessario aggiornare le formule se cambia il layout delle righe; l'uso estensivo potrebbe diventare ingombrante su set di dati molto grandi.
Ad esempio, per sommare solo i valori degli ordini per il prodotto "Felpa" in un intervallo filtrato:
1. Inserisci o copia la seguente formula in una colonna vuota accanto al tuo set di dati (ad esempio, nella cella E2, assumendo che D sia la tua colonna dei valori):
Trascina la maniglia di riempimento verso il basso per riempire questa formula attraverso tutte le righe nell'intervallo dei dati. Questa formula restituirà il valore dalla colonna D se la riga è visibile e 0 se la riga è nascosta dal filtro.
2. Dopo aver generato i valori di supporto nella colonna E, utilizza una funzione SOMMA.PIÙ.SE per sommare solo i valori visibili in base ai tuoi criteri. Ad esempio, per sommare per "Felpa" nella colonna A:
Puoi aggiungere più criteri estendendo gli argomenti di SOMMA.PIÙ.SE nel formato =SOMMA.PIÙ.SE(intervallo_somma, intervallo_criteri1, criterio1, [intervallo_criteri2, criterio2], [intervallo_criteri3, criterio3], ...). Controlla sempre i tuoi intervalli per assicurarti un allineamento corretto e risultati attesi.
Attenzione: Se riorganizzi, inserisci o elimini righe dopo aver impostato le tue formule, verifica attentamente che tutti i riferimenti corrispondano ancora alla tua struttura dati. A volte gli errori possono derivare da intervalli non allineati o dall'aver dimenticato di aggiornare le celle dei criteri.
Sommare solo le celle visibili basandosi sui criteri con una formula
Se preferisci una soluzione basata su formule che non richieda l'aggiunta di colonne di supporto, puoi utilizzare una combinazione di SOMMA.PRODOTTO, SUBTOTALE, SCARTO, RIF.RIGA e MIN per sommare le celle visibili secondo criteri specifici. Questo approccio è migliore per gli utenti esperti di Excel a proprio agio con le formule matriciali ed è particolarmente utile quando si desidera mantenere il foglio ordinato senza colonne extra.
Vantaggi: Non è necessario aggiungere colonne extra al foglio di lavoro; flessibile e dinamico; la formula si aggiorna istantaneamente mentre si filtra o si modificano i criteri.
Limitazioni: Le formule possono essere complesse da leggere o correggere, specialmente per chi non è familiare con le funzioni matriciali; prestazioni che potrebbero rallentare in tabelle molto grandi.
Copia o inserisci la seguente formula in una cella vuota (ad esempio, per sommare le celle visibili per "Felpa" in A2:A12, con i valori effettivi in D2:D12 e i criteri in A17):
Dopo aver inserito la formula, premi Invio per ottenere il risultato desiderato, come mostrato di seguito:
Attenzione: Questo approccio è sensibile agli intervalli specificati - intervalli non corrispondenti o sovrapposti possono causare errori o risultati inaspettati. Testa i casi limite, specialmente quando il filtro cambia il numero o la posizione delle righe visibili.
Sommare solo le celle visibili basandosi su criteri utilizzando il codice VBA
Per gli utenti avanzati, l'uso di VBA offre un modo flessibile per sommare solo le celle visibili per criteri specifici, specialmente quando si gestiscono scenari complessi o grandi set di dati dove le formule standard possono soffrire di colli di bottiglia prestazionali o dove il conteggio dei criteri include logiche multi-condizione difficili da esprimere in una singola formula. VBA può iterare attraverso ogni riga visibile, testare le tue condizioni e calcolare la somma in modo efficiente. Questo è particolarmente adatto per attività di reportistica ripetitive o quando si automatizzano calcoli di sintesi.
Vantaggi: Può gestire facilmente grandi set di dati, criteri multipli o dinamici e logiche complesse; il processo si esegue rapidamente anche con migliaia di righe; riduce il rischio di errori derivanti da modifiche manuali alle formule.
Limitazioni: Richiede l'abilitazione delle macro; alcuni utenti potrebbero non essere familiari con VBA o non avere permessi adeguati; le modifiche richiedono l'accesso all'Editor di Macro. Fai sempre un backup prima di eseguire VBA su set di dati importanti.
1. Per iniziare, apri l'Editor VBA cliccando Strumenti di Sviluppo > Visual Basic. Nella finestra che appare, vai su Inserisci > Modulo, e incolla il seguente codice nel nuovo modulo:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Clicca il 
pulsante "Esegui" (o premi F5) per eseguire il codice. Una finestra di dialogo ti chiederà di selezionare sia l'intervallo dei criteri (come i nomi dei prodotti), l'intervallo di valori da sommare e quale valore desideri come filtro (ad esempio, "Hoodie"). La macro sommerà solo quelle righe visibili in cui i tuoi criteri sono soddisfatti e mostrerà il risultato in un messaggio pop-up.
Consigli pratici: Usa questo codice VBA quando hai spesso bisogno di ricalcolare le somme dopo aver cambiato i tuoi dati o filtri. Puoi espandere ulteriormente il codice VBA per funzionare con più criteri aggiungendo altre richieste di input o condizioni logiche.
Risoluzione dei problemi: Assicurati sempre che gli intervalli selezionati per i criteri e i valori abbiano lo stesso numero di righe e appartengano alle stesse colonne dei tuoi dati filtrati. Se il codice segnala un errore o non restituisce la somma attesa, verifica nuovamente le impostazioni del filtro e la selezione attiva.
Suggerimenti riassuntivi: Per analisi dati che richiedono calcoli ripetuti solo sulle celle visibili, salvare questa macro nel Tuo Libro Macro Personale può velocizzare la tua reportistica quotidiana. Se una finestra di dialogo non appare, verifica le impostazioni macro e i permessi di sicurezza.
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