Kaip naudotis „Excel AGGREGATE“ funkcija -

Turinys

Santrauka

„Excel AGGREGATE“ funkcija pateikia bendrą skaičiavimą, pvz., AVERAGE, COUNT, MAX ir kt., Pasirinktinai ignoruodama paslėptas eilutes ir klaidas. Iš viso yra 19 operacijų, nurodytų funkcijos numeriu pirmajame argumente (parinkčių lentelę žr.).

Tikslas

Grąžinti suvestinį skaičiavimą

Grąžinimo vertė

Priklauso nuo nurodytos funkcijos

Sintaksė

= AGGREGATE (function_num, options, ref1, ref2)

Argumentai

  • function_num - operacija atlikti (1-19).
  • parinktys - ignoruojamos vertės (0–7).
  • ref1 - Pirmasis argumentas.
  • ref2 - Antrasis argumentas (k).

Versija

„Excel 2010“

Naudojimo užrašai

AGGREGATE funkcija pateikia bendrą skaičiavimo rezultatą, pvz., AVERAGE, COUNT, MAX, MIN ir kt. Iš viso yra 19 operacijų, o atliekama operacija nurodoma kaip skaičius, kuris rodomas kaip pirmasis funkcijos argumentas. Antrasis argumentas, parinktys, kontroliuoja, kaip AGGREGATE tvarko klaidas ir reikšmes paslėptose eilutėse. Žemiau pateiktose lentelėse rasite visas galimas parinktis.

1 pavyzdys

Norėdami grąžinti MAX vertę diapazone A1: A10, nepaisydami klaidų ir paslėptų eilučių, funkcijos numeriui nurodykite 4, o parinktims - 7:

=AGGREGATE(4,7,A1:A10) // max value

Norėdami grąžinti MIN reikšmę su tomis pačiomis parinktimis, pakeiskite funkcijos numerį į 5:

=AGGREGATE(5,7,A1:A10) // min value

2 pavyzdys

Aukščiau pateiktame pavyzdyje formulė D5 yra:

=AGGREGATE(4,6,values)

kur „reikšmės“ yra įvardytas diapazonas B5: B14. Funkcijos numeris yra 4, nurodantis MAX. Parinktys pateikiamos kaip 6, kad nepaisytumėte tik klaidų.

3 pavyzdys - n-tas pagal dydį

D8: D10 formulės parodo, kaip grąžinti „n-tas didžiausias“ reikšmes:

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Funkcijos numeris čia yra 14, kuris paleidžia LARGE funkciją. Kadangi funkcijai DIDELIS reikalingas argumentas „ak“, jis rodomas kaip paskutinis argumentas iš trijų aukščiau pateiktų formulių.

4 pavyzdys - masyvo operacija

AGGREGATE ypač naudinga sudėtingesnėms formulėms yra ta, kad ji gali natūraliai tvarkyti masyvus, kai funkcijos skaičius yra 14-19. Pvz., Norėdami rasti maksimalią vertę pirmadieniais, naudodami duomenis, kurie apima datas ir vertes, galite naudoti AGGREGATE taip:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Čia nurodome 14 funkcijos (DIDELIS) ​​ir 6 - parinkties (nepaisyti klaidų). Tada mes sukuriame loginę išraišką naudodami TEXT funkciją, kad patikrintume visas pirmadienių datas. Šios operacijos rezultatas yra TRUE / FALSE reikšmių masyvas, kuris tampa pradinių verčių vardikliu. FALSE vertinamas kaip nulis ir išmeta # DIV / 0! klaida. TRUE vertina kaip 1 ir grąžina pradinę vertę. Galutinis reikšmių ir klaidų masyvas veikia kaip filtras. AGGREGATE nepaiso visų klaidų ir pateikia didžiausią (maksimalią) iš išlikusių verčių. Išsamesnis pavyzdys čia.

Funkcijų numeriai

Žemiau esančioje lentelėje pateikiami funkcijos AGGREGATE funkcijų numeriai kartu su susietos funkcijos pavadinimu. Trečiame stulpelyje Ref2 nurodoma antroji argumento vertė, kurios tikimasi prireikus.

Funkcija Funkcija Ref2
1 VIDUTINIS
2 COUNT
3 COUNTA
4 MAKS
5 MIN
6 PREKĖ
7 STDEV.S
8 STDEV.P
9 SUMA
10 VAR.S
11 VAR.P
12 Vidurinis
13 MODE.SNGL
14 DIDELIS k
15 MAŽAS k
16 PERCENTILE.INC k
17 QUARTILE.INC kv
18 PERCENTILE.EXC k
19 QUARTILE.EXC kv

Elgesio galimybės

AGGREGATE funkcija turi daugybę klaidų, paslėptų eilučių ir kitų funkcijų ignoravimo galimybių. Galimos vertės yra 0–7, kaip parodyta toliau pateiktoje lentelėje.

Variantas Elgesys
0 Nepaisykite SUBTOTAL ir AGGREGATE funkcijų
1 Nepaisykite paslėptų eilučių, SUBTOTAL ir AGGREGATE funkcijų
2 Nepaisyti klaidų reikšmių, SUBTOTAL ir AGGREGATE funkcijų
3 Nepaisykite paslėptų eilučių, klaidų reikšmių, SUBTOTAL ir AGGREGATE funkcijų
4 Nieko nepaisykite
5 Nepaisykite paslėptų eilučių
6 Nepaisyti klaidų verčių
7 Nepaisykite paslėptų eilučių ir klaidų verčių

Pastabos

  • AGGREGATE pateikia #VALUE! klaida, jei reikalingas antras funkcijos argumentas, bet nepateiktas.
  • 3D nuorodos neveikia su AGGREGATE.
  • AGGREGATE funkcija yra skirta vertikaliems, o ne horizontaliems diapazonams.

Įdomios straipsniai...