Kako uporabljati AGREGATE funkcijo v Excelu? | (s primeri)

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, je številka od 1-19

Možnost: to je tudi številčna vrednost, ki se giblje od 0 do 7 in določa, katere vrednosti bodo med izračuni prezrte

Ref1, ref2, ref [3]: je argument med uporabo referenčne sintakse, gre za številsko vrednost ali vrednosti, na katerih želimo opraviti izračun, vsaj dva argumenta sta potrebna, ostali argumenti niso obvezni.

Matrika: je niz vrednosti, na katerih želimo izvesti operacijo, uporablja se v sintaksi matrike funkcije AGGREGATE v Excelu

K: je neobvezen argument in je številska vrednost, uporablja se, kadar se v Excelu uporablja funkcija LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ali QUARTILE.EXC.

Primeri

To AGREGATE funkcijo Excel Predloga lahko prenesete tukaj - AGREGATE Function Excel Predloga

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č, najmanj, 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. Za povprečje je funkcija_številka

V stolpcu C so podane vse vrednosti in nobene vrednosti nam ne bo treba prezreti, zato 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 je uporabljena funkcija, kot je LARGE, SMALL v Excelu, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ali QUARTILE.EXC, vendar v tem primeru izračunamo povprečje, zato bo izpustil vrednost k.

Torej, povprečna vrednost je

Podobno bomo za obseg D1: D8 znova izbrali možnost 4.

Za obseg E1: E8 celica E6 vsebuje vrednost napake, če bomo uporabili isto formulo AGREGATE, bomo dobili napako, ko pa uporabimo ustrezno možnost, pa AGREGATE v Excelu poda povprečje preostalih vrednosti, ki napako zanemarjajo vrednost v E6.

Da bi prezrli 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 in uporabimo možnost 5, da prezremo skrito vrstico, bomo v Excelu AGREGATE 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

Recimo, da imamo tabelo prihodkov, ustvarjenih na različne datume iz različnih kanalov, kot je navedeno spodaj

Zdaj želimo preveriti ustvarjeni prihodek za različne kanale. Torej, ko uporabimo funkcijo vsote, dobimo skupni ustvarjeni prihodek, če pa želimo preveriti dohodek, ustvarjen za organski kanal ali neposredni kanal ali katerega koli drugega, ko za isti uporabimo filtre v excelu, bo funkcija vsote vedno navedite skupno vsoto

Želimo, da ko filtriramo kanal, dobimo vsoto vrednosti, ki so vidne, zato bomo namesto funkcije SUM uporabili funkcijo AGREGATE, da dobimo vsoto vrednosti, ki so vidne, ko je filter uporablja.

Torej, zamenjavo formule SUM s funkcijo AGREGATE s kodo možnosti 5 (ignoriranje skritih vrstic in vrednosti) imamo,

Zdaj, ko bomo uporabili filter za različne kanale, bo prikazal prihodek za ta kanal le, ko bodo ostale vrstice skrite.

Skupni prihodek, ustvarjen za neposredni kanal:

Skupni prihodek, ustvarjen za organski kanal:

Skupni prihodek od plačanega kanala:

Tako lahko vidimo, da funkcija AGREGATE izračuna različne vrednosti vsote za prihodek, ustvarjen za različne kanale, ko so filtrirani. Funkcijo AGGREGATE je torej mogoče dinamično uporabljati za nadomeščanje različnih funkcij za različne pogoje brez uporabe pogojne formule.

Recimo, da za isti kanal tabele in prihodek nekatere naše vrednosti prihodkov vsebujejo napako, zdaj pa moramo napake prezreti in hkrati, če želimo uporabiti filter, mora funkcija AGGREGATE prezreti tudi skrite vrednosti vrstic.

Ko uporabimo možnost 5, dobimo napako za vsoto celotnega prihodka, zato da bi prezrli napake, moramo uporabiti možnost 6

Z uporabo možnosti 6 dobimo vsoto, pri kateri se vrednosti napak ne upoštevajo, toda ko uporabimo filter, na primer filter po vrednosti kanala Direct, dobimo enako vsoto, če napake ignoriramo, hkrati pa moramo prezreti tudi skrite vrednosti.

Torej bomo v tem primeru uporabili možnost 7, ki prezre vrednosti napak in hkrati skrite vrstice

Stvari, ki si jih je treba zapomniti

Funkcija AGGREGATE ne prepozna funkcije _ num vrednost večja od 19 ali manjša od 1 in podobno za številko možnosti ne prepozna vrednosti, večje od 7 in manjše od 1, če navedemo katere koli druge vrednosti, da vrednost #VALUE ! Napaka
Vedno sprejme številčno vrednost in vedno vrne številčno vrednost kot izhod
AGREGATE v Excelu ima omejitev; ignorira samo skrite vrstice, ne pa tudi skritih stolpcev.