1

AGREGAR, la superfunción que casi nadie usa

Antes de nada, déjame hacerte una pregunta:

¿Cómo sumarías los valores de una columna en la que existen errores?

  • Si tu respuesta pasa por eliminar los errores para que la función SUMA pueda hacer su trabajo… mal vamos. Tardarías mucho tiempo en caso de tener muchos datos.
  • Si tratas  de evitar estos errores con la función SI.ERROR… ya me va gustando más.
  • Pero si en tu solución utilizas la función AGREGAR… ¡enhorabuena!¡eres un Exceler!

El artículo de hoy está dedicado a una función tan potente como olvidada. Es la función AGREGAR. Una superfunción tan potente… ¡que vale por 19!

A simple vista, AGREGAR se parece mucho a SUBTOTALES. Ambas sirven para hacer diferentes cálculos con datos ignorando las filas ocultas. Sin embargo, AGREGAR ofrece mucha más potencia como te mostraré en los ejemplos.

AGREGAR tiene dos sintaxis diferentes, determinadas por la función que quieras utilizar en cada momento.

Sintaxis de referencias

=AGREGAR(núm_función;opciones; ref1;[ref2];…)

  • Núm_función: Es un número del 1 al 13 que determina la función que se va a utilizar. Puedes ver más abajo la tabla con las funciones correspondientes.
  • Opciones: Es un número que indica qué datos se deben incluir o excluir del cálculo. También puedes ver la correspondencia más abajo.
    Ref1;[ref2]…: son las referencias a las celdas o rangos de celdas que contienen los valores a calcular.

Sintaxis matricial

=AGREGAR(núm_función;opciones; matriz;k)

  • Núm_función: Es un número del 14 al 19 que determina la función que se va a utilizar. Puedes ver más abajo la correspondencia.
  • Opciones: Es un número que indica qué datos se deben incluir o excluir del cálculo. También puedes ver la correspondencia más abajo.
  • Matriz: Es una matriz o rango de datos o una fórmula matricial que da como resultado una matriz y cuyos datos se van a utilizar en el cálculo.
  • k: Es un número que, dependiendo del núm_función elegida, determina el resultado a devolver.
Función Significado de k
K.ESIMO.MAYOR El enésimo valor más alto que contenga la matriz.
K.ESIMO.MENOR El enésimo valor más bajo que contenga la matriz.
PERCENTIL.INC El porcentaje, que debe ser un valor entre 0 y 1.
PERCENTIL.EXC El porcentaje, que debe ser un valor entre 0 y 1.
CUARTIL.INC El cuartil, que debe ser un valor entre 0 y 4.
CUARTIL.EXC El cuartil, que debe ser un valor entre 0 y 4.

Correspondencia de funciones y opciones

Las funciones que se pueden utilizar con AGREGAR están numeradas del 1 al 19:

Núm_función Función Descripción Sintaxis
1 PROMEDIO Devuelve la media aritmética (promedio) de los argumentos. Referencias
2 CONTAR Cuenta el número de celdas de un rango que contienen números. Referencias
3 CONTARA Cuenta el número de celdas no vacías de un rango. Referencias
4 MAX Devuelve el valor máximo de una lista. Referencias
5 MIN Devuelve el valor mínimo de una lista. Referencias
6 PRODUCTO Devuelve el producto de todos los números introducidos como argumentos. Referencias
7 DESVEST.M Calcula la desviación estándar en función de una muestra. Referencias
8 DESVEST.P Calcula la desviación estándar de un total de población. Referencias
9 SUMA Suma todos los valores de los rangos introducidos como argumentos. Referencias
10 VAR.S Calcula la varianza en función de una muestra. Referencias
11 VAR.P Calcula la varianza en función de la población total. Referencias
12 MEDIANA Devuelve la mediana o el número central de un rango de datos. Referencias
13 MODA.UNO Devuelve el valor que más veces se repite en un rango. Referencias
14 K.ESIMO.MAYOR Devuelve el enésimo valor más alto de un rango. Matricial
15 K.ESIMO.MENOR Devuelve el enésimo valor más bajo de un rango. Matricial
16 PERCENTIL.INC Devuelve el enésimo percentil de un rango donde n está en el rango 0 y 1 incluidos. Matricial
17 CUARTIL.INC Devuelve el cuartil en función del percentil entre 0 y 1 incluidos. Matricial
18 PERCENTIL.EXC Devuelve el enésimo percentil de un rango donde n está en el rango 0 y 1 excluidos. Matricial
19 CUARTIL.EXC Devuelve el cuartil en función del percentil entre 0 y 1 excluidos. Matricial

Las opciones o reglas de inclusión o exclusión de datos también vienen dadas por un número:

Opción Descripción
0 o en blanco Ignora las funciones SUBTOTALES y AGREGAR anidadas.
1 Ignora las filas ocultas y las funciones SUBTOTALES y AGREGAR anidadas.
2 Ignora los errores y las funciones SUBTOTALES y AGREGAR anidadas.
3 Ignora las filas ocultas, los errores y las funciones SUBTOTALES y AGREGAR anidadas.
4 No ignora nada.
5 Ignora las filas ocultas
6 Ignora los valores de error.
7 Ignora las filas ocultas y los errores.

No te preocupes por todos estos datos. A medida que se van introduciendo los argumentos, aparecen todas las opciones disponibles para cada uno.

Los números de función

funcion número agregar excel

Las opciones

agregar número opción excel

Si te es más fácil también puedes utilizar el botón Insertar función, con lo que aparecerá el cuadro de diálogo de AGREGAR. En este caso no tendrás la misma ayuda visual para seleccionar la función ni la opción.

insertar excel agregar función

argumentos excel funcion agregar

 

Ejemplos con la función AGREGAR

Venga, vamos a ver algunos ejemplos prácticos.

ejemplo funcion agregar Excel

Observa los ejemplos de arriba. Te los voy detallando uno por uno:

Ejemplo 1: Varias referencias

La fórmula de la celda I4

=AGREGAR(1;7;C2:C17;D2:D17;E2:E17;F2:F17)

calcula el promedio  (núm_función=1) de los rangos de celdas C2:C17;D2:D17;E2:E17;F2:F17 omitiendo las filas ocultas y los errores (opción=7).

En este ejemplo también es posible incluir un solo rango (C2:F17), pero este ejemplo muestra cómo también funciona con varios rangos.

Ejemplo 2: K.ESIMO.MAYOR

La fórmula de la celda I5

=AGREGAR(14;5;C2:C17;1)

devuelve el primer valor mayor (núm_función=14) del rango C2:C29, omitiendo las filas ocultas.

Ejemplo 3: K.ESIMO.MAYOR en fórmula matricial

Este ejemplo es algo más complicado que los anteriores. La fórmula de la celda I6

=AGREGAR(14;5;C2:C17*(B2:B17=”Herra. Manual”);2)

La fórmula devuelve el segundo valor más alto del rango C2:C29 y cuya categoría es “Tornillería”.

Sería posible conseguir el mismo resultado utilizando únicamente K.ESIMO.MAYOR, pero para introducirla habría que pulsar Ctrl + Mayús + Intro, ya que se trata de una fórmula matricial. Al usar AGREGAR, ya está implícito que se trata de una matricial.

Ejemplo 4: Sin omitir errores

El último ejemplo de la imagen de arriba corresponde a una suma en la que ni se ignoran las filas ocultas ni los errores:

=AGREGAR(9;5;D2:D17)

Al no omitir nada, la fórmula da un error ya que no se pueden sumar valores de error.

… pero no todo es tan bueno

Sí, parece que agregar es la navaja suiza  de las funciones de Excel, pero no siempre es así. ¿Por qué?

  • Porque es más complicado utilizarla que sus equivalentes. Por ejemplo, es más fácil utilizar SUMA que AGREGAR.
  • Porque tienes que introducir más argumentos.
  • Porque tiene muchas opciones para recordar.
  • Porque muchas personas no conocen la función y, en caso de querer modificarla, resultaría más tedioso.
  • Sólo funciona en versiones a partir de 2007 (aunque no sé de nadie que utilice 2003 y antiguas).

Ventajas de utilizar AGREGAR

Si te he convencido para utilizar AGREGAR, seguro que después de leer la siguiente lista, la utilizarás sí o sí:

  • Con AGREGAR puedes hacer 512 cálculos diferentes (19 funciones X 8 opciones).
  • No tienes que preocuparte de los posibles errores que se encuentren en los rangos a utilizar.
  • Puedes hacer cálculos con las celdas visibles de listas con filtro.
  • No necesitas entender cómo funcionan las fórmulas matriciales.

Resumen

Como has visto, AGREGAR es junto con SUBTOTALES y SUMAPRODUCTO, una de las funciones más versátiles y útiles con las que cuenta Excel.

¿Tú ya la utilizas?¿o lo harás a partir de ahora?

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 1 comentarios
Luis Tovar - 08/01/2019

Excelente ejercio nunca había usado la función agregar espero sacarle provecho

Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software