Kako koristiti AGREGATE funkciju u Excelu? | (s primjerima)

AGREGATE funkcija u Excelu

AGREGATE Funkcija u excelu vraća agregat zadane tablice podataka ili popisa podataka, ova funkcija također ima prvi argument kao broj funkcije, a daljnji argumenti su za niz skupova podataka, treba zapamtiti broj funkcije da bismo znali koju funkciju koristiti .

Sintaksa

Postoje dvije sintakse za AGREGATE Formulu:

  1. Sintaksa reference

= AGREGATE (funkcija_broj, opcije, ref1, ref2, ref [3],…)

  1. Sintaksa niza

= AGREGATE (funkcija_broj, opcije, niz, [k])

Function_num je broj koji označava određenu funkciju koju želimo koristiti, to je broj od 1-19

Opcija: to je također numerička vrijednost u rasponu od 0 do 7 i određuje koje vrijednosti treba zanemariti tijekom izračuna

Ref1, ref2, ref [3]:  je argument dok se koristi referentna sintaksa, to je numerička vrijednost ili vrijednosti na kojima želimo izvršiti izračunavanje, potrebna su najmanje dva argumenta, a ostali argumenti nisu obvezni.

Niz : je niz vrijednosti na kojima želimo izvršiti operaciju, koristi se u sintaksi niza funkcije AGGREGATE u excelu

K: je neobavezni argument i numerička je vrijednost, koristi se kada se u Excelu koristi funkcija poput LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ili QUARTILE.EXC.

Primjeri

Ovu AGREGATE funkciju Excel Predložak možete preuzeti ovdje - AGREGATE Function Excel Predložak

Primjer - # 1

Pretpostavimo da imamo popis brojeva i da ćemo izračunati Prosjek, Brojanje koji je broj ćelija koje sadrže vrijednost, Brojanje -broj ćelija koje nisu prazne, Maksimum, Minimum, proizvod i zbroj zadanih numeričkih vrijednosti. Vrijednosti su dane u nastavku u tablici:

Prvo izračunajmo Prosjek u retku 9, za sve zadane vrijednosti. Prosječno je function_ num

U stupcu C date su sve vrijednosti i nećemo morati zanemariti nijednu vrijednost, pa ćemo odabrati opciju 4 (ništa ne zanemariti)

I odabir raspona vrijednosti C1: C8 kao niza numeričkih vrijednosti

Budući da je ' k' neobavezan argument i koristi se kada se koristi funkcija poput LARGE, SMALL u programu Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ili QUARTILE.EXC, ali u ovom slučaju izračunavamo Prosjek pa izostavit će vrijednost k.

Dakle, prosječna vrijednost je

Slično tome, za raspon D1: D8, opet ćemo odabrati opciju 4.

Za raspon E1: E8, ćelija E6 sadrži vrijednost pogreške, ako ćemo koristiti istu AGREGATE formulu dobit ćemo pogrešku, ali kada se koristi odgovarajuća opcija, AGREGATE u Excelu daje prosjek preostalih vrijednosti zanemarujući pogrešku vrijednost u E6.

Da bismo zanemarili vrijednosti pogreške, imamo opciju 6.

Slično tome, za raspon G1: G8 upotrijebit ćemo opciju 6 (zanemariti vrijednosti pogreške)

Sada, za raspon H3 ako stavimo vrijednost 64, a treći redak sakrijemo i upotrijebimo opciju 5, da zanemarimo skriveni redak, AGREGATE u Excelu dat ćemo prosječnu vrijednost samo za vidljive numeričke vrijednosti.

Izlaz bez skrivanja retka 3

Izlaz nakon skrivanja retka 3

Primjenjujući formulu AGGREGATE za druge operacije, imamo

Primjer - # 2

Pretpostavimo da imamo tablicu prihoda ostvarenog na različite datume s različitih kanala kako je dano u nastavku

Sada želimo provjeriti prihod ostvaren za različite kanale. Dakle, kada primijenimo funkciju zbroja, dobivamo ukupan generirani prihod, ali u slučaju da želimo provjeriti prihod generiran za organski kanal ili izravni kanal ili bilo koji drugi, kada za isti primijenimo filtre u excelu, funkcija zbroja uvijek će dati ukupni zbroj

Želimo da kada filtriramo kanal dobijemo zbroj vrijednosti koje su vidljive, pa ćemo umjesto funkcije SUM koristiti funkciju AGREGATE kako bismo dobili zbroj vrijednosti koje su vidljive kad je filtar primijenjena.

Dakle, zamjenjujući formulu SUM funkcijom AGREGATE s kodom opcije 5 (zanemarujući skrivene retke i vrijednosti) imamo,

Sada, kada ćemo primijeniti filtar za različite kanale, on će prikazivati ​​prihod za taj kanal samo kad se ostatak redaka sakrije.

Ukupni prihod ostvaren za izravni kanal:

Ukupni prihod ostvaren za organski kanal:

Ukupni prihod ostvaren za plaćeni kanal:

Dakle, možemo vidjeti kako AGREGATE funkcija izračunava različite vrijednosti zbroja za prihod generiran za različite kanale nakon što se filtriraju. Dakle, AGREGATE funkcija može se dinamički koristiti za zamjenu različitih funkcija za različite uvjete bez upotrebe uvjetne formule.

Pretpostavimo da za isti kanal tablice i prihod neke naše vrijednosti prihoda sadrže pogrešku, sada trebamo zanemariti pogreške, a istodobno, ako želimo primijeniti filtar, funkcija AGGREGATE treba zanemariti i vrijednosti skrivenih redaka.

Kada koristimo opciju 5, dobivamo pogrešku za ZBOR ukupnog prihoda, sada da bismo ignorirali pogreške moramo koristiti opciju 6

Korištenjem opcije 6 dobivamo zbroj koji zanemaruje vrijednosti pogreške, ali kada primijenimo filtar, na primjer, filtriramo prema vrijednosti kanala Direct, dobivamo isti zbroj zanemarujući pogreške, ali istovremeno moramo zanemariti i skrivene vrijednosti.

Dakle, u ovom ćemo slučaju koristiti opciju 7 koja zanemaruje vrijednosti pogreške i istodobno skrivene retke

Stvari koje treba zapamtiti

  • Funkcija AGGREGATE ne prepoznaje funkciju _ num vrijednost veća od 19 ili manja od 1, a slično kao i za broj opcije ne prepoznaje vrijednosti veće od 7 i manje od 1, ako pružimo bilo koje druge vrijednosti daje #Vrijednost ! Pogreška
  • Uvijek prihvaća numeričku vrijednost i uvijek vraća numeričku vrijednost kao izlaz
  • AGREGAT u Excelu ima ograničenje; samo ignorira skrivene retke, ali ne zanemaruje skrivene stupce.