Cómo Usar la Nueva Función AGREGAR

Estaba leyendo una discusión en uno de los foros de Excel sobre el uso de la combinación de funciones =SI(ESERROR(fórmula1),fórmula2,fórmula1) que opuesto a la nueva función (desde Excel 2007) SI.ERROR(fórmula1,fórmula2).

La primera forma es muy, pero muy, ineficiente ya que primero tiene que calcular fórmula1, luego evaluar si da error y si no da error volver a calcularla.
SI.ERROR resuelve este problema, pero sólo para quien haya avanzado a Excel 2007 posteriores.

Personalmente trato de evitar el uso de SI.ERROR ya que en ciertas ocasiones puede ocultar errores que si quisiéramos detectar (por ejemplo: #REF!).

Una situación similar es cuando queremos hacer operaciones con un rango de valores que contiene errores. Pero a partir de Excel 2010 tenemos una nueva herramienta: la función AGREGAR.

Esta nueva función es una especie de SUBTOTALES potenciada. AGREGAR viene provisto con 19 funciones (como puede verse en la ayuda en línea de Excel)

Siete opciones (qué omitir y qué no)

Esta función viene en dos formas: forma de referencia y forma matricial. Un detalle a tener en cuenta es que esta función no está diseñada para agregar rangos horizontales.

Sintaxis

Forma de referencia

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

Forma matricial

AGREGAR(núm_función, opciones, matriz, [k])

La sintaxis de la función AGREGAR tiene los siguientes argumentos:

• Núm_función   Obligatorio. Un número de 1 a 19 que especifica la función que se usará.

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

• Opciones   Obligatorio. Un valor numérico que determina qué valores del rango de evaluación de la función se omitirán.

Opción Comportamiento

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

• Ref1   Obligatorio. El primer argumento numérico para las funciones que tienen varios argumentos numéricos de los que desea obtener el valor agregado.
• Ref2;…   Opcional. Argumentos numéricos 2 a 253 cuyo valor agregado desea obtener.

En el caso de funciones que aceptan matrices, ref1 es una matriz, una fórmula matricial o una referencia a un rango de celdas cuyo valor agregado se desea. Ref2 es un segundo argumento que es obligatorio para algunas funciones. Las funciones siguientes exigen un argumento ref2:

Función

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)

Observaciones

Núm_función:

• En cuanto escriba el argumento núm_función al escribir la función AGREGAR en una celda de la hoja de cálculo, verá una lista de todas las funciones que puede usar como argumentos.

Errores:

• Si un segundo argumento de referencia es obligatorio pero no se incluye, AGREGAR devolverá el valor de error #¡VALOR!.
• Si algunas de las referencias son referencias 3D, AGREGAR devolverá el valor de error #¡VALOR!.

Agregados anidados:

• Si hay otros AGREGADOS dentro de ref1; ref2; … (o AGREGADOS anidados), éstos se pasarán por alto para no repetir los cálculos.
• Si hay SUBTOTALES dentro de los argumentos de referencia de la función AGREGAR, estos SUBTOTALES se omiten.
• Si hay AGREGADOS anidados en la función SUBTOTALES, estos AGREGADOS se omiten.

Tipo de rango:

• La función AGREGAR está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni para rangos horizontales. Por ejemplo, cuando se calcula el subtotal de un rango horizontal mediante la opción 1, como en AGREGAR(1; 1; ref1), ocultar una columna no afecta al valor de la suma agregada. En cambio, ocultar una fila en un rango vertical sí afecta al valor de la suma agregada.

Ejemplos

Ejemplo 1

El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

¿Cómo copio un ejemplo?

A B

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

#¡DIV/0! 82 72 65 30 95 #¡NÚM! 63 31 53 96 71 32 55 81 83 33 100 53 91 34 89 Fórmula Descripción (resultado) =AGREGAR(4; 6; A1:A11) Calcula el valor máximo e ignora los valores erróneos dentro del rango (96) =AGREGAR(14; 6; A1:A11; 3) Calcula el tercer valor máximo e ignora los valores erróneos dentro del rango (72) =AGREGAR(15; 6; A1:A11) Devolverá el valor de error #¡VALOR!. Esto se debe a que AGREGAR espera un segundo argumento de referencia, ya que la función (K.ESIMO.MENOR) pide ese argumento. =AGREGAR(12; 6; A1:A11; B1:B11) Calcula la mediana e ignora los valores erróneos dentro del rango (68) =MAX(A1:A2) Devolverá un valor de error, porque en el rango de evaluación hay valores de error (#¡DIV/0!)

Ejemplo 2

El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

¿Cómo copio un ejemplo?

A B

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

x y 96 82 72 65 30 95 56 63 31 53 98 71 32 55 81 83 33 100 53 91 34 89 Fórmula Descripción (resultado) =AGREGAR(4; 5; A1:A11) Calcula el valor máximo e ignora los valores erróneos dentro del rango (98) =AGREGAR(14; 5; A1:A11; 3) Calcula el tercer valor máximo e ignora los valores erróneos dentro del rango (81) =AGREGAR(18; 5; A1:A11) Devolverá el valor de error #¡VALOR!. Esto se debe a que AGREGAR espera un segundo argumento de referencia, ya que la función (PERCENTIL) pide ese argumento. =AGREGAR(12; 5; A1:A11; B1:B11) Calcula la mediana e ignora los valores erróneos dentro del rango (68)