AGREGATE Funkcija v Excelu
AGREGATE Funkcija v Excelu vrne agregat dane podatkovne tabele ali seznamov podatkov, ta funkcija ima tudi prvi argument kot številko funkcije, nadaljnji argumenti pa so za obseg naborov podatkov, številko funkcije je treba zapomniti, da vemo, katero funkcijo uporabiti .
Sintaksa
Za AGREGATNO formulo obstajata dve sintaksi:
- Referenčna sintaksa
= AGREGATE (funkcija_številka, možnosti, ref1, ref2, ref (3),…)
- Sintaksa matrike
= AGREGATE (funkcija_številka, možnosti, matrika, (k))
Function_num je številka, ki označuje določeno funkcijo, ki jo želimo uporabiti; gre za številko od 1-19
Možnost: gre tudi za številsko vrednost od 0 do 7 in določa, katere vrednosti se med izračuni prezrejo
Ref1, ref2, ref (3): je argument med uporabo referenčne sintakse; gre za številsko vrednost ali vrednosti, na katerih želimo izvesti izračun, sta potrebna vsaj dva argumenta. Argumenti počitka niso obvezni.
Matrika: je niz vrednosti, na katerih želimo delovati; uporablja se v sintaksi matrike funkcije AGGREGATE v Excelu.
K: je neobvezen argument in številska vrednost; uporablja se, kadar je v Excelu uporabljena funkcija LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ali QUARTILE.EXC.
Primeri
Primer - # 1
Recimo, da imamo seznam števil in bomo izračunali povprečje, štetje, ki je število celic, ki vsebujejo vrednost, števec celic, ki niso prazne, največje, najmanjše, zmnožek in vsota danih številskih vrednosti . Vrednosti so podane spodaj v tabeli:
Najprej izračunamo povprečje v 9. vrstici za vse dane vrednosti. V povprečju je funkcija_številka 1
V stolpcu C so podane vse vrednosti in nobene vrednosti nam ne bo treba prezreti, da bomo izbrali možnost 4 (ničesar ne prezri)
In izbira obsega vrednosti C1: C8 kot polja številskih vrednosti
Ker je ' k' neobvezen argument in se uporablja, kadar se uporablja funkcija, kot je LARGE, SMALL v Excelu, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ali QUARTILE.EXC, vendar v tem primeru izračunamo povprečje na izpusti vrednost k.
Torej, povprečna vrednost je
Podobno bomo za območje D1: D8 znova izbrali možnost 4.
Za obseg E1: E8 celica E6 vsebuje vrednost napake. Če uporabimo isto formulo AGREGATE, bomo dobili napako. Kljub temu, ko je uporabljena ustrezna možnost, AGREGATE v Excelu poda povprečje preostalih vrednosti, pri čemer zanemari vrednost napake v E6.
Če želite prezreti vrednosti napak, imamo možnost 6.
Podobno bomo za obseg G1: G8 uporabili možnost 6 (prezreti vrednosti napak)
Zdaj za obseg H3, če damo vrednost 64 in skrijemo tretjo vrstico ter uporabimo možnost 5, da prezremo skrito vrstico, AGREGATE v Excelu, bomo podali povprečno vrednost samo za vidne številske vrednosti.
Izhod brez skrivne vrstice 3
Izhod po skrivanju vrstice 3
Uporabljamo formulo AGGREGATE za druge operacije
Primer - # 2
Suppose we have a table for the revenue generated on different dates from the different channels as given below
Now, we want to check the revenue generated for different channels. So, when we apply the sum function, we get the total revenue generated, but in case if we want to check the revenue generated for Organic channel or direct channel or any other, when we apply filters in excel for the same, the sum function will always give the total sum
We want that when we filter the channel, we get the sum of the visible values, so instead of using the SUM function, we will use the AGGREGATE function to get the sum of the values that are visible when a filter is applied.
So, replacing the SUM formula with an AGGREGATE function with option code 5 (ignoring the hidden rows and values), we have,
Now, when we apply the filter for different channels it will show the revenue for that channel only as the rest of the rows gets hidden.
Total Revenue Generated for Direct Channel:
Total Revenue Generated for Organic Channel:
Total Revenue Generated for Paid Channel:
We can see the AGGREGATE function calculates the different Sum values for the revenue generated for different channels once they are filtered. So, the AGGREGATE function can be dynamically used to replace different functions for different conditions without using the conditional formula.
Suppose for the same table, some of our revenue values contains an error, now we need to ignore the errors, and at the same time, if we want to apply a filter, the AGGREGATE function should ignore the hidden row values also.
When we use option 5, we get the error for the SUM of the total revenue. To ignore the errors, we have to use option 6
Using option 6, we get the sum ignoring the error values. Still, when we apply the filter, for example, filter by channel value Direct, we get the same sum ignoring the errors, but at the same time, we have to ignore the hidden values also.
So, in this case, we will use option 7 that ignores the error values and, at the same time, the hidden rows.
Things to Remember
- The AGGREGATE function doesn’t recognize the function _ num value greater than 19 or less than 1. Similarly, for Option number, it doesn’t identify the values greater than 7 and less than 1; if we provide any other values, it gives a #VALUE! Error
- It always accepts the numeric value and always returns a numeric value as an output
- AGREGATE v Excelu ima omejitev; ignorira samo skrite vrstice, ne pa tudi skritih stolpcev.
