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.
núm_función | Función |
1 | PROMEDIO |
2 | CONTAR |
3 | CONTARA |
4 | MAX |
5 | MIN |
6 | PRODUCTO |
7 | DESVEST.M |
8 | DESVEST.P |
9 | SUMA |
10 | VAR.M |
11 | VAR.P |
12 | MEDIANA |
13 | MODA.UNO |
14 | K.ESIMO.MAYOR |
15 | K.ESIMO.MENOR |
16 | PERCENTIL.INC |
17 | CUARTIL.INC |
18 | PERCENTIL.EXC |
19 | QUARTILCUARTIL.EXC |
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.
Opción | Lo que se omitirá |
0 u omitido | Omitir funciones AGREGAR y SUBTOTALES anidadas |
1 | Omitir filas ocultas y funciones AGREGAR y SUBTOTALES anidadas |
2 | Omitir valores de error y funciones AGREGAR y SUBTOTALES anidadas |
3 | Omitir filas ocultas, valores de error y funciones AGREGAR y SUBTOTALES anidadas |
4 | No omitir nada |
5 | Omitir filas ocultas |
6 | Omitir valores de error |
7 | Omitir filas ocultas y valores de error |
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.
Fórmula | Lo que omitimos | Resultado |
=AGREGAR(9,4,D11:D110) | No omitir nada. Devuelve error ya que no podemos sumar valores de error. | #¡DIV/0! |
=AGREGAR(9,5,D11:D110) | Omitir filas ocultas. Devuelve error ya que las celdas visibles contienen un error. | #¡DIV/0! |
=AGREGAR(9,6,D11:D110) | Omitir valores de error. Omite valores de error y suma las celdas visibles y ocultas. | $ 2,969,285 |
=AGREGAR(9,7,D11:D110) | Omitir filas ocultas y valores de error. Suma solo las celdas visibles. | $ 359,066 |
Descarga el archivo de ejemplo
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.