Susie tiene una hoja de trabajo que contiene bastantes datos. No es inusual que esconda columnas dentro de la hoja de trabajo. Necesita una fórmula que devuelva el promedio de un rango de celdas, todas en la misma fila, ignorando las celdas ocultas. Por ejemplo, si Susie selecciona B7: G7, le gustaría un promedio de solo las celdas en ese rango que son visibles (las columnas D y E, en este caso, están ocultas).

No existe una función intrínseca en Excel para calcular dicho promedio. Si intentaba encontrar el promedio de un rango de celdas en una columna, entonces podría usar la función SUBTOTAL, de esta manera:

=SUBTOTAL(101,A7:A12)

Sin embargo, la función SUBTOTAL no devolverá un valor exacto cuando el rango proporcionado esté dentro de una fila. Si lo desea, puede utilizar algunas celdas auxiliares para realizar el cálculo. Simplemente ingrese lo siguiente en la celda B8:

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

Copie la celda B8 en el rango C8: G8. El resultado es que cada celda en el rango B8: G8 contendrá un 0 o un 1 en función de si la columna está oculta o no. A continuación, puede utilizar la siguiente fórmula para determinar el promedio:

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

Calcula el promedio solo para aquellas columnas en las que las celdas en B8: G8 contienen un valor de 1 (no están ocultas).

Por supuesto, es posible que no pueda utilizar las celdas auxiliares y que le resulte más beneficioso crear una función definida por el usuario para calcular el promedio. Lo siguiente funciona muy rápidamente:

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

Para usar la función, simplemente coloque esta fórmula en la celda en la que desea contener el promedio:

=AverageVisible(B7:G7)

La función verifica cada celda en el rango (lo que significa que puede usarla en filas, columnas o, de hecho, en cualquier rango) para asegurarse de que no esté oculta ni vacía. Si la celda contiene un valor numérico, se usa para calcular el promedio. Si el rango que especifica no contiene valores que se puedan promediar, la función devuelve un valor de 0.

La función se ejecuta automáticamente cada vez que se recalcula la hoja de trabajo. Si cambia las columnas que están ocultas, Excel no recalcula automáticamente. Por lo tanto, deberá forzar el recálculo después de ocultar o mostrar columnas.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13262) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.