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.
| 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
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?
2 comentarios en “AGREGAR, la superfunción que casi nadie usa”
Excelente ejercio nunca había usado la función agregar espero sacarle provecho
Excelente, Sergio. ¿Alguna posibilidad de explicar esta función y otras explicaciones que nos brindas mediante videos subidos a Youtube? Es que visualizando y escuchando, el aprendizaje es mucho mejor. De todas formas, estoy agradecido contigo por estos tips.
Los comentarios están cerrados.