Usar la función AGREGAR para aplicar funciones omitiendo filas ocultas y valores de error en Excel

Photo by Julia Sabiniarz on Unsplash

En días pasados un compañero me presentó un archivo de Excel, pero estaba consternado porque había hecho una fórmula de suma en su archivo de ventas, pero la suma no correspondía al valor que deseaba obtener. Al mirar el archivo lo primero que me percaté fue que tenía filas ocultas y estaba usando la función SUMA para devolver el total de ventas de un trimestre.

Al tener filas ocultas, la función SUMA le devolvía la suma de todas las celdas incluyendo las ocultas. Par este caso tuvimos que usar la función AGREGAR para sumar los valores sin tomar en cuenta las filas ocultas.

Función AGREGAR en Excel

Según la definición en la ayuda de Microsoft Office, la función AGREGAR devuelve un agregado de una lista o base datos. Entendamos agregado como un conjunto o un subtotal de valores aplicando diferentes funciones de agregación.

Lo mejor de esta función es que nos permite aplicar funciones con la posibilidad de omitir filas ocultas o celdas con valores que contengan un error. Así solo vamos a poder aplicar la función en celdas visibles y celdas que no contengan error.

Cómo trabaja la función SUMA en una rango con celdas ocultas

Tenemos un rango de celdas de un reporte de ventas. Noten en la Figura 1 que la fila 16 se brinca hasta la fila 104, lo que significa que las celdas 17 a las 103 están ocultas.

Figura 1. Reporte de ventas con celdas ocultas.

Ahora bien, si aplicamos la función SUMA a las celdas de la fila D obtendremos el valor de 3’018,506, pero si elegimos el rango, en la barra de estado veremos la suma 408,287. Lo anterior quiere decir que la función SUMA hace una suma de todas las celdas incluidas las ocultas.

Figura 2. La función SUMA toma en cuenta todas las celdas incluso las ocultas.

Ver video La función AGREGAR en EXCEL

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Función AGREGAR y su sintaxis

La sintaxis de la función AGREGAR que usaremos en este ejemplo es la Forma de referencia, aunque existe también la Forma matricial.

AGREGAR(núm_función, opciones, ref1, …)

El parámetro núm_función nos da la oportunidad de elegir hasta 19 funciones de agregación.

El parámetro opciones nos dará la oportunidad de elegir de la opción 0 a la opción 7 y dónde especificaremos los valores que se omitirán en el agregado.

El parámetro ref1 es el rango en el cual deseamos aplicar la función de agregación, como la SUMA. Podemos tener más rangos incluido en el parámetro ref2 y así sucesivamente.

Cabe aclarar que la función AGREGAR solo funciona para columnas o rangos verticales, no para filas o rangos horizontales.

La función AGREGAR aplicada a nuestro ejemplo

Como deseamos aplicar una suma a nuestro rango de la columna D, haremos uso de las siguientes fórmulas.

Figura 3. La función AGREGAR en Microsoft Excel.

Descarga el archivo de ejemplo

Función AGREGAR en Excel.zip

Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.