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.

Artículos relacionados

• Las fórmulas matriciales en Excel
• Los 7 errores de fórmulas en Excel y cómo solucionarlos
• Trucos para crear fórmulas eficientes
• Guía práctica de funciones de Excel 

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.

Correspondencia de funciones y opciones

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

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

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

Las opciones

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.

Ejemplos con la función AGREGAR

Venga, vamos a ver algunos ejemplos prácticos.

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?