Funkcija AGGREGATE programmā Excel
AGGREGATE Funkcija excel atgriež norādītās datu tabulas vai datu sarakstu kopumu, šai funkcijai ir arī pirmais arguments kā funkcijas numurs un citi argumenti ir datu kopu diapazonam. Funkcijas numurs jāatceras, lai zinātu, kuru funkciju izmantot .
Sintakse
Formulai AGGREGATE ir divas sintakse:
- Atsauces sintakse
= AGGREGATE (function_num, options, ref1, ref2, ref (3),…)
- Masīva sintakse
= AGGREGATE (function_num, options, masīvs, (k))
Funkcijas_numurs ir skaitlis, kas apzīmē noteiktu funkciju, kuru mēs vēlamies izmantot; tas ir skaitlis no 1-19
Variants: tā ir arī skaitliskā vērtība, kas svārstās no 0 līdz 7, un nosaka, kuras vērtības aprēķinos jāignorē
Ref1, ref2, ref (3): ir arguments, lietojot atsauces sintaksi; tā ir skaitliskā vērtība vai vērtības, kurām mēs vēlamies veikt aprēķinu, ir nepieciešami vismaz divi argumenti. Pārējie argumenti nav obligāti.
Masīvs: ir vērtību masīvs, ar kuru mēs vēlamies darboties; tas tiek izmantots funkcijas AGGREGATE masīva sintaksē Excel.
K: ir izvēles arguments un skaitliskā vērtība; to lieto, ja programmā Excel tiek izmantota tāda funkcija kā LARGE, MAZA, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC vai QUARTILE.EXC
Piemēri
Piemērs - # 1
Pieņemsim, ka mums ir skaitļu saraksts, un mēs aprēķināsim vidējo vērtību, skaitu, kas ir šūnu skaits, kas satur vērtību, Counta-šūnu skaits, kas nav tukšs, maksimālais, minimālais, produkts un norādīto skaitlisko vērtību summa . Vērtības ir norādītas zemāk tabulā:
Vispirms aprēķināsim vidējo vērtību 9. rindā visām norādītajām vērtībām. Vidēji funkcija_numurs ir 1
C slejā visas vērtības ir norādītas, un mums nevajadzēs ignorēt nevienu vērtību, lai mēs izvēlētos 4. opciju (neko neņemt vērā)
Atlasot vērtību diapazonu C1: C8 kā skaitlisko vērtību masīvu
Tā kā “ k” ir neobligāts arguments un tiek izmantots, ja tiek izmantota tāda funkcija kā LARGE, SMALL Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC vai QUARTILE.EXC, taču šajā gadījumā mēs aprēķinām vidējo izlaist k vērtību.
Tātad vidējā vērtība ir
Līdzīgi diapazonam D1: D8 atkal izvēlēsimies 4. opciju.
Diapazonam E1: E8 šūnā E6 ir kļūdas vērtība. Ja izmantosim to pašu AGGREGATE formulu, tiks parādīta kļūda. Tomēr, ja tiek izmantota atbilstoša opcija, programmā AGGREGATE programmā Excel tiek parādīts atlikušo vērtību vidējais rādītājs, atstājot novārtā kļūdas vērtību E6.
Lai ignorētu kļūdu vērtības, mums ir 6. opcija.
Līdzīgi diapazonam G1: G8 mēs izmantosim opciju 6 (ignorēt kļūdu vērtības)
Tagad diapazonam H3, ja mēs ieliekam vērtību 64 un paslēpjam trešo rindu un izmantojam 5. opciju, lai ignorētu slēpto rindu, AGGREGATE programmā Excel, mēs norādīsim vidējo vērtību tikai redzamām skaitliskām vērtībām.
Rezultāts, neslēpjot 3. rindu
Rezultāts pēc 3. rindas paslēpšanas
Piemērojot AGGREGATE formulu citām operācijām, mēs esam
Piemērs - # 2
Pieņemsim, ka mums ir tabula par ieņēmumiem, kas gūti dažādos datumos no dažādiem kanāliem, kā norādīts zemāk
Tagad mēs vēlamies pārbaudīt ieņēmumus, kas gūti dažādiem kanāliem. Tātad, lietojot summas funkciju, mēs iegūstam kopējos gūtos ieņēmumus, bet gadījumā, ja mēs vēlamies pārbaudīt ieņēmumus par dabisko kanālu vai tiešo kanālu vai jebkuru citu, tad, kad mēs Excel izmantojam filtrus tam pašam, summa funkcija vienmēr norādīs kopējo summu
Mēs vēlamies, lai, filtrējot kanālu, mēs iegūtu redzamo vērtību summu, tāpēc tā vietā, lai izmantotu funkciju SUM, mēs izmantosim funkciju AGGREGATE, lai iegūtu vērtību vērtību, kas ir redzama, kad tiek piemērots filtrs.
Tātad, aizstājot SUM formulu ar funkciju AGGREGATE ar opcijas kodu 5 (ignorējot slēptās rindas un vērtības),
Tagad, kad filtru lietosim dažādiem kanāliem, šī kanāla ieņēmumi tiks rādīti tikai tad, kad pārējās rindas tiks paslēptas.
Kopējie tiešo kanālu ieņēmumi:
Kopējie ieņēmumi par dabisko kanālu:
Kopējie ieņēmumi no apmaksātā kanāla:
Mēs varam redzēt, ka funkcija AGGREGATE aprēķina dažādas summas ienākumiem, kas iegūti dažādiem kanāliem, tiklīdz tie tiek filtrēti. Tātad funkciju AGGREGATE var dinamiski izmantot, lai aizstātu dažādas funkcijas dažādiem apstākļiem, neizmantojot nosacīto formulu.
Pieņemsim, ka šai pašai tabulai dažās mūsu ieņēmumu vērtībās ir kļūda, tagad mums ir jāignorē kļūdas, un tajā pašā laikā, ja mēs vēlamies lietot filtru, funkcijai AGGREGATE būtu jāignorē arī slēptās rindas vērtības.
Izmantojot 5. opciju, tiek parādīta kļūda par kopējo ieņēmumu SUM. Lai ignorētu kļūdas, mums jāizmanto 6. opcija
Izmantojot 6. opciju, mēs iegūstam summu, ignorējot kļūdas vērtības. Tomēr, lietojot filtru, piemēram, filtrējot pēc kanāla vērtības Direct, mēs saņemam to pašu summu, ignorējot kļūdas, bet tajā pašā laikā mums ir jāignorē arī slēptās vērtības.
Tātad šajā gadījumā mēs izmantosim 7. opciju, kas ignorē kļūdu vērtības un tajā pašā laikā slēptās rindas.
Atceramās lietas
- Funkcija AGGREGATE neatpazīst funkcijas _ num vērtību, kas lielāka par 19 vai mazāka par 1. Tāpat opcijas numuram tā nenosaka vērtības, kas lielākas par 7 un mazākas par 1; ja mēs sniedzam citas vērtības, tas dod #VALUE! Kļūda
- Tas vienmēr pieņem skaitlisko vērtību un vienmēr kā izvadi atgriež skaitlisko vērtību
- PAPILDINĀJUMAM programmā Excel ir ierobežojums; tas tikai ignorē slēptās rindas, bet neignorē slēptās kolonnas.
