La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Función Agregar en Excel.

Función Agregar en Excel. Para qué sirve.


La función Agregar en Excel permite realizar operaciones matemáticas y estadísticas sobre un subconjunto de datos en una tabla de Excel que tenga datos erróneos,  datos filtrados o datos ocultos. La función pertenece a la familia de funciones matemáticas aunque también tiene sentido dentro de la familia de funciones estadísticas. Las operaciones que se pueden realizar sobre los datos son 19. Y el comportamiento en el tratamiento de los datos se puede elegir de una lista de hasta 7 posibilidades.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Función Agregar en Excel. Argumentos.


La función Agregar admite 3 argumentos obligatorios aunque podemos ir añadiendo rangos al final de la función con lo que el número de parámetros puede aumentar.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

  • Núm_función: Valor numérico del 1 al 19 se corresponde con el listado de funciones
  • Opciones: Es el comportamiento se aplicará sobre los datos calculados con el Num_función. Es un valor numérico de 0 a 7 que se corresponde con el listado de comportamientos.
  • Ref1: Los datos que se operarán con la función elegida normalmente será un rango.
  • Ref2: Se pueden elegir rangos adicionales.

La función Agregar también puede escribirse en formato matricial. En tal caso los argumentos que admite son 3 obligatorios y un último argumento “K” que solo será utilizado con 6 funciones de las 19 disponibles. Estás seis funciones son:

  • K.ESIMO.MAYOR(matriz;k)
  • K.ESIMO.MENOR(matriz;k)
  • PERCENTIL.INC(matriz;k)
  • CUARTIL.INC(matriz;cuart)
  • PERCENTIL.EXC(matriz;cuart)
  • CUARTIL.EXC(matriz;cuart)

Ejemplo con Subtotales.


A continuación se presentan varios ejemplos de uso de la función agregar. Son simplificaciones de listados para conseguir un efecto visual y didáctico.

En el primer listado que se presenta tenemos aplicada una agrupación por zonas en la que se han calculado sumas parciales. Así tendremos los acumulados de las zonas centro, este, islas, norte, oeste y sur.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

¿Qué ocurrirá si en la celda F35 hacemos una suma automática del rango F5:F31? La respuesta es que nos sumará también los subtotales parciales. El resultado final estará multiplicado por dos y en consecuencia es erróneo. Como se ve en la imagen nos mantiene dentro del rango de suma las celdas con los totales de zona, la celda F13, F16, F20…

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Aplicaremos entonces la suma mediante la función Agregar. Para este caso si acudimos a las tablas de funciones y comportamientos localizamos la función SUMA en la opción 9. Vemos en la opción 0 de comportamientos que evitaremos que se sumen los Subtotales. También sirve la opción 1, 2 y 3. Con la opción 4 tendríamos un valor desvirtuado ya que sería la suma con los subtotales. Las opciones 5, 6 y 7 tampoco nos servirán en este ejemplo ya que están dirigidas a filas ocultas y errores que no es nuestro caso.

Luego entonces en la celda F36  escribiremos = AGREGAR (9; 0; F5:F31). Y el resultado final será:

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Como vemos es el mismo resultado que habíamos conseguido mediante la agrupación de subtotales.

Ejemplo con Errores.


En este ejemplo seguimos trabajando con el mismo listado. Esta vez vamos a convertir la fila 18 en un error. Para ello El valor 716€ de la celda F18 lo dividiremos entre “0” para conseguir un fallo.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Este error se propaga en los cálculos que ya teníamos en nuestra hoja puesto que la suma de cualquier valor con #DIV/0 necesariamente nos devuelve un error.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Para resolver este problema añadiremos una nueva fila al final donde escribiremos la función Agregar pero esta vez teniendo en cuenta al seleccionar el argumento de comportamiento el error existente en nuestro listado. En este caso concreto podemos usar el argumento con valor 2.

=AGREGAR (9; 2; $F$5:$F$31) hemos fijado el rango de entrada con referencias absolutas para demostrar el funcionamiento del procedimiento.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

El resultado final 172.454,00€ difiere en el anterior resultado obtenido 173.170,00€ en los 716€ de la fila 18 que había anotado Manuel Corbacho pero que arrojaron un error del tipo #DIV/0!

Ejemplo con filas ocultas.


Para este ejemplo vamos a ocultar dos filas la fila 11 y la fila 12. La suma de ambas transacciones es de 30.423,00€.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

El resultado que aparezca tras aplicar la función Agregar deberá llevar descontados esos 30.423,00€.

Para este ejemplo tendremos entonces filas ocultas, subtotales de zonas y errores. Si buscamos el comportamiento en la tabla de comportamientos vemos que se ajusta a la opción 3. De nuevo fijamos el rango de suma para comprobar el buen funcionamiento de Agregar.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

El resultado final de esta operación es de 142.031,00€ como habíamos previsto inicialmente 30.423,00€ inferior al resultado anterior.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Ejemplo con filtros.


El caso de los filtros es exactamente igual a filas o columnas ocultas. El tratamiento es el mismo y lo podemos comprobar fácilmente aplicando un filtro al fichero que hemos utilizado en los ejemplos.

La función Agregar realiza operaciones matemáticas o estadísticas sobre una tabla de Excel que tenga datos erróneos, datos filtrados o datos ocultos.

Función Agregar en Inglés.


La función Agregaren en inglés se denomina AGGREGATE. Su estructura es la siguiente:

  • En inglés:
    • AGGREGATE(function_num; options; ref1;…)
    • AGGREGATE(function_num; options; array;[k])

Descargar ejemplos vistos en el post.

#DIV/0!, aggregate, aggregate function, agregar, fallos, función agregar, sumar con errores


Iván Antona

Especialista formación Microsoft Excel en Madrid para empresas.

Cursos Excel Empresas Tecnoexcel

Contactar

Calle de Gloria Fuertes, 10

28701 San Sebastián de los Reyes

Madrid, España

+34 911 23 52 43

ivan.antona@tecnoexcel.es


TecnoExcel | Cursos Excel para empresas