Come si calcola la media delle celle o dell'elenco filtrati in Excel?

Quando si applica direttamente la funzione MEDIA a un set di dati filtrati, Excel calcola comunque la media di tutte le celle nell’intervallo specificato, incluse quelle nascoste dal filtro. Questo comportamento genera risultati imprecisi se l’obiettivo è calcolare la media esclusivamente delle righe visibili. Per ottenere il valore corretto sui dati filtrati (e quindi visibili), la funzione SUBTOTALE di Excel rappresenta la soluzione ideale: si adatta automaticamente ai filtri applicati ed esclude dal calcolo le righe nascoste, garantendo precisione e affidabilità.

Per calcolare la media solo dei risultati filtrati, segui questi passaggi:

• Identifica l'intervallo che include tutti i tuoi dati filtrati nella colonna di cui desideri calcolare la media (in questo esempio, supponiamo che i valori siano in)C12:C24 della colonna Importo).
• In una cella vuota inserisci la seguente formula:

=SUBTOTAL(1,C12:C24)

Questa formula calcola la media solo delle celle visibili (filtrate) nell'intervallo specificato ()C12:C24). Il parametro 1 indica a SUBTOTALE di utilizzare la funzione MEDIA, e SUBTOTALE ignora automaticamente tutte le righe nascoste dal filtro.

Dopo aver digitato la formula, premi Invio. Vedrai immediatamente visualizzato il valore medio delle sole righe visibili, come mostrato di seguito:

Somma/Conta/Media solo celle visibili, ignorando celle/righe/colonne nascoste o filtrate

Le normali funzioni SOMMA, CONTA.NUMERI o MEDIA di Excel calcolano utilizzando tutte le celle all'interno dell'intervallo specificato, indipendentemente dal fatto che siano visibili o nascoste a causa di filtri o nascondimenti manuali. Per gestire in modo più efficace questi casi, puoi contare su Kutools per Excel. Grazie alle sue funzioni dedicate SOMMAVISIBILI, CONTAVISIBILI e MEDIAVISIBILIPuoi calcolare facilmente totali, conteggi e medie solo per le celle effettivamente visibili in qualsiasi intervallo, escludendo sia quelle filtrate che quelle nascoste manualmente. Questa funzionalità ti aiuta a evitare errori nei fogli di lavoro complessi e ti fa risparmiare tempo rispetto all’uso di formule complesse o codice personalizzato.

Media di dati/elenco filtrati con la funzione AGGREGA

Se utilizzi Excel 2010 o versioni successive, la funzione AGGREGA offre ancora più flessibilità rispetto a SUBTOTALE per calcolare medie sui dati filtrati, con opzioni aggiuntive per gestire errori e righe nascoste. Ecco come applicarla:

• In una cella vuota inserisci la seguente formula (ipotizzando che i tuoi dati filtrati siano in C12:C24):

=AGGREGATE(1,5, C12:C24)

• Il primo argomento ()1) specifica la funzione MEDIA, esattamente come in SUBTOTALE.
• Il secondo argomento ()5) indica alla funzione AGGREGA di ignorare sia le righe nascoste (a causa del filtro) che gli errori.

Premi Invio dopo aver digitato la formula per visualizzare immediatamente la media delle righe visibili nel tuo intervallo filtrato. Se desideri adattare il calcolo a diversi metodi di nascondimento delle righe o ad altre funzioni di aggregazione, modifica semplicemente il secondo parametro di conseguenza.

Macro VBA per calcolare la media solo delle celle effettivamente visibili

Per esigenze più avanzate o personalizzate, puoi utilizzare una semplice macro VBA per calcolare la media solo delle celle visibili (non nascoste né filtrate) in un Seleziona intervallo. Questa soluzione è particolarmente utile nei fogli che contengono diversi metodi di nascondimento dei dati. Ecco come procedere:

1. Passa alla scheda Sviluppo di Excel e seleziona Visual Basic per aprire l'editor VBA. Nell'editor, fai clic su Inserisci > Modulo per creare un nuovo modulo.
2. Copia e incolla il seguente codice VBA nella finestra del modulo:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. Chiudi l'editor VBA. Torna al tuo foglio di lavoro, premi Alt+F8, seleziona AverageVisibleCells e fai clic su Esegui. Quando richiesto, seleziona il tuo intervallo dati di destinazione. La macro calcolerà e visualizzerà la media solo delle celle numeriche attualmente visibili (non filtrate e non nascoste).

Media ponderata

Calcolo della media/tasso di crescita annuo composto

Media mobile/scorrevole

Totale cumulativo/media cumulativa

Tasso medio di variazione