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:
- Sintaksa reference
= AGREGATE (funkcija_broj, opcije, ref1, ref2, ref (3),…)
- 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 tijekom korištenja referentne sintakse; to je numerička vrijednost ili vrijednosti na kojima želimo izvršiti proračun, potrebna su najmanje dva argumenta. Argumenti za odmor nisu obvezni.
Niz : je niz vrijednosti na kojima želimo djelovati; koristi se u sintaksi niza funkcije AGGREGATE u excelu.
K: je neobavezni argument i numerička vrijednost; koristi se kada se koristi funkcija poput LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ili QUARTILE.EXC u Excelu.
Primjeri
Primjer - # 1
Pretpostavimo da imamo popis brojeva i izračunat ćemo 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. U prosjeku je funkcija_broj 1
U stupcu C date su sve vrijednosti i nećemo morati zanemariti nijednu vrijednost tako da ć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 na izostaviti 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 upotrijebimo istu AGREGATE formulu, dobit ćemo pogrešku. Ipak, kada se koristi odgovarajuća opcija, AGREGATE u Excelu daje prosjek preostalih vrijednosti zanemarujući vrijednost pogreške 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 ćemo za raspon H3, ako stavimo vrijednost 64 i sakrijemo treći redak i upotrijebimo opciju 5, zanemariti skriveni redak, AGREGAT u Excelu, dati 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 ukupni ostvareni prihod, ali u slučaju da želimo provjeriti prihod ostvaren 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 vidljivih vrijednosti, pa ćemo umjesto funkcije SUM koristiti funkciju AGREGATE da bismo dobili zbroj vrijednosti koje su vidljive kada se primijeni filtar.
Dakle, zamjenjujući formulu SUM s AGREGATE funkcijom s kodom opcije 5 (zanemarujući skrivene retke i vrijednosti), imamo,
Sada, kada primijenimo filtar za različite kanale, on će prikazati 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:
Možemo vidjeti da funkcija AGGREGATE izračunava različite vrijednosti zbroja za prihod ostvaren 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 istu tablicu neke od naših 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 upotrijebimo opciju 5, dobit ćemo pogrešku za SUM ukupnog prihoda. Da bismo ignorirali pogreške, moramo upotrijebiti opciju 6
Koristeći opciju 6, dobivamo zbroj zanemarujući vrijednosti pogreške. Ipak, 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šaka i istovremeno skrivene retke.
Stvari koje treba zapamtiti
- Funkcija AGGREGATE ne prepoznaje funkciju _ num vrijednost veću od 19 ili manju od 1. Slično tome, za broj opcije ne prepoznaje vrijednosti veće od 7 i manje od 1; ako pružimo bilo koje druge vrijednosti, to 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.
