Función de Excel AGREGADO

Función AGREGAR en Excel

La función AGREGAR en Excel devuelve el agregado de una tabla de datos o listas de datos dadas, esta función también tiene el primer argumento como número de función y los argumentos adicionales son para un rango de conjuntos de datos, el número de función debe recordarse para saber qué función usar .

Sintaxis

Hay dos sintaxis para la fórmula AGREGADA:

1. Sintaxis de referencia

= AGREGADO (núm_función, opciones, ref1, ref2, ref [3],…)

2. Sintaxis de matriz

= AGREGADO (número_función, opciones, matriz, [k])

Function_num es un número que denota una función específica que queremos usar, es un número del 1 al 19

Opción: también es un valor numérico que va de 0 a 7 y determina qué valores deben ignorarse durante los cálculos

Ref1, ref2, ref [3]:  es el argumento mientras se usa la sintaxis de referencia, es el valor numérico o valores sobre los que queremos realizar el cálculo, se requieren al menos dos argumentos, los demás argumentos son opcionales.

Matriz: es una matriz de valores sobre la que queremos realizar la operación, se usa en la sintaxis de matriz de la función AGREGAR en excel

K: es un argumento opcional y es un valor numérico, se usa cuando se usa la función LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC o QUARTILE.EXC en Excel.

Ejemplos

Puede descargar esta plantilla de Excel de función AGREGADA aquí - Plantilla de Excel de función AGREGADA

Ejemplo 1

Supongamos que tenemos una lista de números y calcularemos el promedio, el recuento que es el número de celdas que contienen un valor, el recuento de celdas que no están vacías, el máximo, el mínimo, el producto y la suma de los valores numéricos dados. Los valores se dan a continuación en la tabla:

Primero calculemos el promedio en la fila 9, para todos los valores dados. Para promedio, la función_ num es

En la Columna C, se dan todos los valores y no tendremos que ignorar ningún valor, por lo que seleccionaremos la Opción 4 (no ignorar nada)

Y seleccionando el rango de valores C1: C8 como una matriz de valores numéricos

Dado que ' k' es un argumento opcional y se usa cuando se usa una función como LARGE, SMALL en Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC o QUARTILE.EXC, pero en este caso, estamos calculando el Promedio, por lo que omitirá el valor de k.

Entonces, el valor promedio es

De manera similar, para el rango D1: D8, nuevamente seleccionaremos la opción 4.

Para el rango E1: E8, una celda E6 contiene un valor de error, si usamos la misma fórmula AGREGADO obtendremos un error, pero cuando se usa una opción apropiada, el AGREGADO en Excel da el promedio de los valores restantes descuidando el error valor en E6.

Para ignorar los valores de error, tenemos la opción 6.

Del mismo modo, para el rango G1: G8 usaremos la opción 6 (ignorar los valores de error)

Ahora, para el rango H3 si ponemos un valor 64, y ocultamos la tercera fila y usamos la opción 5, para ignorar la fila oculta, el AGREGAR en Excel le daremos el valor promedio solo para los valores numéricos visibles.

Salida sin ocultar la fila 3

Salida después de ocultar la fila 3

Aplicando la fórmula AGREGADA para otras operaciones, tenemos

Ejemplo - # 2

Supongamos que tenemos una tabla para los ingresos generados en diferentes fechas de los diferentes canales como se indica a continuación.

Ahora, queremos comprobar los ingresos generados por diferentes canales. Entonces, cuando aplicamos la función de suma obtenemos los ingresos totales generados pero en caso de que queramos verificar los ingresos generados por canal orgánico o canal directo o cualquier otro, cuando aplicamos filtros en excel para el mismo, la función de suma siempre será dar la suma total

Queremos que cuando filtremos el canal, obtengamos la suma de los valores que son visibles, por lo que en lugar de usar la función SUMA, usaremos la función AGREGAR para obtener la suma de los valores que son visibles cuando un filtro es aplicado.

Entonces, reemplazando la fórmula SUM con una función AGREGAR con el código de opción 5 (ignorando las filas y valores ocultos) tenemos,

Ahora, cuando apliquemos el filtro para diferentes canales, mostrará los ingresos para ese canal solo cuando el resto de las filas se oculten.

Ingresos totales generados por canal directo:

Ingresos totales generados para el canal orgánico:

Ingresos totales generados para el canal pago:

Entonces, podemos ver que la función AGREGAR calcula los diferentes valores de Suma para los ingresos generados por diferentes canales una vez que se filtran. Entonces, la función AGREGAR se puede usar dinámicamente para el reemplazo de diferentes funciones para diferentes condiciones sin usar la fórmula condicional.

Supongamos que para el mismo canal de tabla e ingresos, algunos de nuestros valores de ingresos contienen un error, ahora debemos ignorar los errores y, al mismo tiempo, si queremos aplicar un filtro, la función AGREGAR también debe ignorar los valores de fila ocultos.

Cuando usamos la opción 5, obtenemos el error de la SUMA de los ingresos totales, ahora para ignorar los errores tenemos que usar la opción 6

Usando la opción 6 obtenemos la suma ignorando los valores de error, pero cuando aplicamos el filtro, por ejemplo, filtrar por valor de canal Directo obtenemos la misma suma ignorando los errores pero al mismo tiempo tenemos que ignorar los valores ocultos también.

Entonces, en este caso, usaremos la opción 7 que ignora los valores de error y al mismo tiempo las filas ocultas

Cosas para recordar

• La función AGREGAR no reconoce la función _ num valor mayor que 19 o menor que 1 y de manera similar para el número de Opción no reconoce los valores mayores que 7 y menores que 1, si proporcionamos cualquier otro valor da un #VALOR ! Error
• Siempre acepta el valor numérico y siempre devuelve un valor numérico como salida
• El AGREGADO en Excel tiene una limitación; solo ignora las filas ocultas pero no ignora las columnas ocultas.