AGGREGATE funkcija „Excel“
„AGGREGATE“ funkcija „Excel“ pateikia nurodytos duomenų lentelės arba duomenų sąrašų agregatą. Ši funkcija taip pat turi pirmąjį argumentą, nes funkcijos numeris ir kiti argumentai yra duomenų rinkinių diapazonui. Funkcijos numerį reikia prisiminti, kad žinotumėte, kurią funkciją naudoti .
Sintaksė
Yra dvi AGGREGATE formulės sintaksės:
- Nuorodos sintaksė
= SUMATYTI (function_num, options, ref1, ref2, ref (3),…)
- Masyvo sintaksė
= AGGREGATE (function_num, options, masyvas, (k))
Funkcijos_num yra skaičius, žymintis konkrečią funkciją, kurią norime naudoti; tai skaičius nuo 1-19
Variantas: ji taip pat yra skaitinė vertė, svyruojanti nuo 0 iki 7, ir nustato, kurių verčių nepaisoma atliekant skaičiavimus
Ref1, ref2, ref (3): yra argumentas naudojant nuorodos sintaksę; tai yra skaitinė vertė arba reikšmės, kuriomis norime atlikti skaičiavimą, reikalingi bent du argumentai. Poilsio argumentai yra neprivalomi.
Masyvas : tai reikšmių masyvas, kurį mes norime valdyti; jis naudojamas funkcijos „AGGREGATE“ masyvo sintaksėje.
K: yra pasirinktinis argumentas ir skaitinė reikšmė; jis naudojamas, kai „Excel“ naudojama tokia funkcija kaip LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC arba QUARTILE.EXC.
Pavyzdžiai
Pavyzdys - # 1
Tarkime, kad turime skaičių sąrašą ir apskaičiuosime vidurkį, skaičių, kuris yra langelių, kuriuose yra reikšmė, skaičių, Counta-ląstelių, kurios nėra tuščios, skaičių, maksimalų, minimalų, sandaugą ir nurodytų skaitinių verčių sumą . Vertės pateikiamos žemiau lentelėje:
Pirmiausia apskaičiuokime visų nurodytų verčių vidurkį 9 eilutėje. Vidutiniškai funkcija_ skaičius yra 1
C stulpelyje pateikiamos visos vertės ir mums nereikės ignoruoti jokių verčių, kad pasirinktume 4 variantą (nieko nepaisyti)
Ir pasirinkdami C1: C8 reikšmių diapazoną kaip skaitinių verčių masyvą
Kadangi „ k“ yra neprivalomas argumentas ir naudojamas, kai naudojama tokia funkcija kaip LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC arba QUARTILE.EXC, tačiau šiuo atveju mes skaičiuojame vidurkį praleisti k reikšmę.
Taigi, vidutinė vertė yra
Panašiai ir D1: D8 diapazone vėl pasirinksime 4 variantą.
E1: E8 diapazone langelyje E6 yra klaidos reikšmė. Jei naudosime tą pačią AGGREGATE formulę, gausime klaidą. Vis dėlto, kai naudojama tinkama parinktis, „AGGREGATE“ programoje „Excel“ pateikia likusių verčių vidurkį, nepaisydami klaidos vertės E6.
Norėdami ignoruoti klaidos reikšmes, turime 6 variantą.
Panašiai ir diapazone G1: G8 naudosime 6 parinktį (nepaisysime klaidų verčių)
Dabar H3 diapazonui, jei mes nustatysime 64 reikšmę ir paslėpsime trečią eilutę ir naudosime 5 parinktį, ignoruosime paslėptą eilutę „AGGREGATE“ programoje „Excel“, mes pateiksime tik matomų skaitinių verčių vidutinę vertę.
Rezultatas neslepiant 3 eilutės
Rezultatas paslėpus 3 eilutę
Taikydami AGGREGATE formulę kitoms operacijoms, mes turime
Pavyzdys - # 2
Tarkime, kad turime lentelę, kurioje pateikiamos pajamos, gautos skirtingomis datomis iš skirtingų kanalų, kaip nurodyta toliau
Dabar norime patikrinti pajamas, gautas už skirtingus kanalus. Taigi, taikydami sumos funkciją, gauname visas gautas pajamas, tačiau tuo atveju, jei norime patikrinti pajamas, gautas už natūralų kanalą, tiesioginį kanalą ar bet kurį kitą, kai taikome filtrus „Excel“ dėl tos pačios sumos funkcijos visada pateiks visą sumą
Mes norime, kad filtruodami kanalą gautume matomų verčių sumą, taigi, užuot naudoję funkciją SUM, naudosime funkciją AGGREGATE, kad gautume reikšmių, matomų pritaikius filtrą, sumą.
Taigi, pakeisdami SUM formulę funkcija AGGREGATE su parinkties kodu 5 (nepaisydami paslėptų eilučių ir reikšmių),
Dabar, kai pritaikysime filtrą skirtingiems kanalams, jo pajamos bus rodomos tik tada, kai likusios eilutės bus paslėptos.
Bendros tiesioginio kanalo pajamos:
Bendros pajamos iš natūralaus kanalo:
Bendros pajamos iš mokamo kanalo:
Matome, kad funkcija AGGREGATE apskaičiuoja skirtingas skirtingų kanalų pajamų sumas, kai tik jos filtruojamos. Taigi funkcija AGGREGATE gali būti dinamiškai naudojama norint pakeisti skirtingas funkcijas skirtingomis sąlygomis, nenaudojant sąlyginės formulės.
Tarkime, kad toje pačioje lentelėje kai kuriose mūsų pajamų reikšmėse yra klaida, dabar turime nepaisyti klaidų, o tuo pačiu metu, jei norime pritaikyti filtrą, funkcija AGGREGATE turėtų ignoruoti ir paslėptų eilučių vertes.
Naudodami 5 variantą, gauname klaidą dėl visų pajamų SUM. Norėdami nepaisyti klaidų, turime naudoti 6 variantą
Naudodami 6 variantą, gauname sumą, nepaisydami klaidų verčių. Vis dėlto, kai pritaikome filtrą, pavyzdžiui, filtruojame pagal tiesioginę kanalo vertę, gauname tą pačią sumą, nepaisydami klaidų, tačiau tuo pat metu turime nepaisyti ir paslėptų verčių.
Taigi šiuo atveju naudosime 7 variantą, kuris nepaisys klaidų reikšmių ir tuo pačiu paslėptų eilučių.
Ką reikia atsiminti
- AGGREGATE funkcija neatpažįsta funkcijos _ num reikšmės, didesnės nei 19 arba mažesnės nei 1. Panašiai ir „Option number“ ji nenustato didesnių nei 7 ir mažesnių nei 1 verčių; jei pateiksime kitas vertes, tai suteiks #VALUE! Klaida
- Jis visada priima skaitinę vertę ir visada grąžina skaitinę vertę kaip išvestį
- „AGGREGATE“ programoje „Excel“ yra apribojimas; jis tik nepaiso paslėptų eilučių, bet neignoruoja paslėptų stulpelių.
