photosdematures.com

Funció AGREGADA a Excel

Funció AGGREGATE en excel retorna l'agregat d'una taula de dades o llistes de dades donades, aquesta funció també té el primer argument com a número de funció i hi ha altres arguments per a un interval de conjunts de dades; s'ha de recordar el número de funció per saber quina funció s'ha d'utilitzar.

Sintaxi

Hi ha dues sintaxis per a la fórmula AGGREGATE:

1. Sintaxi de referència

= AGREGAT (nombre_funcions, opcions, ref1, ref2, ref [3], ...)

2. Sintaxi de matriu

= AGREGAT (nombre_funcions, opcions, matriu, [k])

Funció_num és un número que indica una funció específica que volem utilitzar, és un número de l'1 al 19

Opció: també és un valor numèric que oscil·la entre 0 i 7 i determina quins valors s'han d'ignorar durant els càlculs

Ref1, ref2, ref [3]: és l'argument mentre s'utilitza la sintaxi de referència, és el valor numèric o els valors sobre els quals volem realitzar el càlcul, es requereixen almenys dos arguments. Els arguments de descans són opcionals.

Matriu: és una matriu de valors sobre els quals volem realitzar l'operació, s'utilitza en la sintaxi de matriu de la funció AGGREGATE en excel

K: és un argument opcional i és un valor numèric, s'utilitza quan s'utilitzen funcions com LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC o QUARTILE.EXC a Excel.

Exemples

Podeu descarregar aquesta plantilla Excel de funció AGGREGATE aquí - Plantilla Excel de funció AGGREGATE

Exemple: # 1

Suposem que tenim una llista de nombres i calcularem la mitjana, recompte que és el nombre de cel·les que contenen un valor, nombre de cel·les que no estan buides, màxim, mínim, producte i suma dels valors numèrics donats. A continuació, es mostren els valors de la taula:

Calculem primer la mitjana de la fila 9, per a tots els valors donats. Per mitjà, la funció_núm és

A la columna C, es donen tots els valors i no haurem d’ignorar cap valor, de manera que seleccionarem l’Opció 4 (ignorar res)

I seleccionant l'interval de valors C1: C8 com a matriu de valors numèrics

Des dek ’ és un argument opcional i s’utilitza quan s’utilitza una funció com LARGE, SMALL a Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC o QUARTILE.EXC, però en aquest cas estem calculant la mitjana, de manera que ometrem el valor de k.

Per tant, el valor mitjà és

De la mateixa manera, per al rang D1: D8, tornarem a seleccionar l'opció 4.

Per al rang E1: E8, una cel·la E6 conté un valor d'error, si utilitzarem la mateixa fórmula AGGREGATE obtindrem un error, però quan s'utilitza una opció adequada, AGGREGATE a Excel dóna la mitjana dels valors restants sense tenir en compte l'error. valor a E6.

Per ignorar els valors d'error, tenim l'opció 6.

De la mateixa manera, per al rang G1: G8 utilitzarem l'opció 6 (ignorar els valors d'error)

Ara, per a l’interval H3 si posem un valor 64 i amaguem la tercera fila i fem servir l’opció 5, per ignorar la fila oculta, AGREGAR a Excel donarem el valor mitjà només per als valors numèrics visibles.

Sortida sense amagar la fila 3

Sortida després d’ocultar la fila 3

Aplicant la fórmula AGGREGATE per a altres operacions, tenim

Exemple: # 2

Suposem que tenim una taula sobre els ingressos generats en diferents dates dels diferents canals tal com es mostra a continuació

Ara volem comprovar els ingressos generats per diferents canals. Per tant, quan apliquem la funció suma obtenim els ingressos totals generats, però si volem comprovar els ingressos generats per al canal orgànic o el canal directe o qualsevol altre, quan apliquem filtres en excel per al mateix, la funció suma sempre serà doneu la suma total

Volem que quan filtrem el canal, obtinguem la suma dels valors que són visibles, de manera que, en lloc d’utilitzar la funció SUM, farem servir la funció AGREGAR per tal d’obtenir la suma dels valors que són visibles quan hi ha un filtre. aplicat.

Per tant, substituint la fórmula SUM per una funció AGREGAT pel codi d’opció 5 (ignorant les files i els valors ocults) que tenim,

Ara, quan aplicarem el filtre per a diferents canals, mostrarà els ingressos per a aquest canal només a mesura que s’amagui la resta de files.

Ingressos totals generats per al canal directe:

Ingressos totals generats per al canal orgànic:

Ingressos totals generats per al canal de pagament:

Per tant, podem veure que la funció AGGREGATE calcula els diferents valors de suma dels ingressos generats per a diferents canals un cop es filtren. Per tant, la funció AGGREGATE es pot utilitzar dinàmicament per substituir diferents funcions per diferents condicions sense utilitzar la fórmula condicional.

Suposem que per al mateix canal i ingressos de la taula, alguns dels nostres valors d’ingressos contenen un error, ara hem d’ignorar els errors i, al mateix temps, si volem aplicar un filtre, la funció AGREGAR també hauria d’ignorar els valors de fila ocults.

Quan fem servir l’opció 5, obtenim l’error de la SUMA dels ingressos totals, ara per ignorar els errors hem d’utilitzar l’opció 6

Utilitzant l’opció 6 obtenim la suma ignorant els valors d’error, però quan apliquem el filtre, per exemple, filtre per valor de canal Directe, obtenim la mateixa suma ignorant els errors, però al mateix temps també hem d’ignorar els valors ocults.

Per tant, en aquest cas, farem servir l’opció 7 que ignora els valors d’error i, alhora, les files amagades

Coses que cal recordar

• La funció AGREGAR no reconeix la funció _ num valor superior a 19 o inferior a 1 i de manera similar per a l'opció número no reconeix els valors superiors a 7 i inferior a 1, si proporcionem qualsevol altre valor, dóna un #VALUE ! Error
• Sempre accepta el valor numèric i sempre retorna un valor numèric com a sortida
• L’AGREGATE a Excel té una limitació; només ignora les files amagades, però no ignora les columnes amagades.