Susie ha un foglio di lavoro che contiene un bel po ‘di dati. Non è insolito per lei nascondere le colonne all’interno del foglio di lavoro. Ha bisogno di una formula che restituisca la media di un intervallo di celle, tutte nella stessa riga, ignorando le celle nascoste. Ad esempio, se Susie seleziona B7: G7, vorrebbe una media delle sole celle in quell’intervallo che sono visibili (le colonne D ed E, in questo caso, sono nascoste).

Non esiste una funzione intrinseca in Excel per calcolare tale media. Se stavi cercando di trovare la media di un intervallo di celle in una colonna, potresti usare la funzione SUBTOTALE, in questo modo:

=SUBTOTAL(101,A7:A12)

Tuttavia, la funzione SUBTOTALE non restituirà un valore accurato quando l’intervallo fornito è all’interno di una riga. Potresti, se lo desideri, utilizzare alcune celle di supporto per eseguire il calcolo. Metti semplicemente quanto segue nella cella B8:

=(CELL("width",B7)>0)+0

Copia la cella B8 nell’intervallo C8: G8. Il risultato è che ogni cella nell’intervallo B8: G8 conterrà uno 0 o un 1 a seconda che la colonna sia nascosta o meno. È quindi possibile utilizzare la seguente formula per determinare la media:

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

Calcola la media solo per quelle colonne in cui le celle in B8: G8 contengono un valore di 1 (non sono nascoste).

Ovviamente, potresti non essere in grado di utilizzare le celle helper e potresti trovare più vantaggioso creare una funzione definita dall’utente per calcolare la media. Il seguente funziona molto rapidamente:

Function AverageVisible(rng As Range)

Dim rCell As Range     Dim iCount As Integer     Dim dTtl As Double

iCount = 0     dTtl = 0     For Each rCell In rng         If rCell.ColumnWidth > 0 _           And rCell.RowHeight > 0 _           And Not IsEmpty(rCell) _           And IsNumeric(rCell.Value) Then               dTtl = dTtl + rCell               iCount = iCount + 1         End If     Next     If iCount > 0 Then         AverageVisible = dTtl / iCount     Else         AverageVisible = 0     End If End Function

Per utilizzare la funzione è sufficiente inserire questa formula nella cella in cui si vuole contenere la media:

=AverageVisible(B7:G7)

La funzione controlla ogni cella dell’intervallo (il che significa che puoi usarla su righe, colonne o, in effetti, su qualsiasi intervallo) per assicurarti che non sia nascosta e non vuota. Se la cella contiene un valore numerico, viene utilizzata per calcolare la media. Se l’intervallo specificato non contiene valori che possono essere mediati, la funzione restituisce un valore 0.

La funzione viene eseguita automaticamente ogni volta che il foglio di lavoro viene ricalcolato. Se modifichi le colonne nascoste, Excel non ricalcola automaticamente. Pertanto, dovrai forzare il ricalcolo dopo aver nascosto o mostrato le colonne.

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (13262) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.