Kuidas kasutada funktsiooni Exceli koondamine -

Kokkuvõte

Funktsioon Excel AGGREGATE tagastab koondarvutuse, näiteks AVERAGE, COUNT, MAX jne, valikuliselt ignoreerides peidetud ridu ja vigu. Kokku on saadaval 19 toimingut, mis on esimeses argumendis määratud funktsiooni numbri järgi (vt valikute tabelit).

Eesmärk

Tagastab koondarvutuse

Tagastusväärtus

Sõltub määratud funktsioonist

Süntaks

= AGGREGATE (function_num, options, ref1, ref2)

Argumendid

• function_num - toiming sooritamiseks (1-19).
• Valikud - eiratavad väärtused (0–7).
• ref1 - esimene argument.
• ref2 - teine ​​argument (k).

Versioon

Excel 2010

Kasutusjuhised

Funktsioon AGGREGATE tagastab koondarvutuse tulemuse, näiteks AVERAGE, COUNT, MAX, MIN jne. Kokku on saadaval 19 toimingut ja teostatav toiming määratakse arvuna, mis kuvatakse funktsiooni esimese argumendina. Teine argument, valikud, kontrollib, kuidas AGGREGATE peidetud ridades vigu ja väärtusi käsitleb. Kõigi saadaolevate valikute kohta vaadake allolevaid tabeleid.

Näide 1

MAX väärtuse tagastamiseks vahemikus A1: A10, ignoreerides nii vigu kui peidetud ridu, määrake funktsiooni numbriks 4 ja valikute jaoks 7

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

MIN-väärtuse tagastamiseks samade valikutega muutke funktsiooni numbriks 5:

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

Näide 2

Eespool toodud näites on valem D5-s:

=AGGREGATE(4,6,values)

kus "väärtused" on nimega vahemik B5: B14. Funktsiooni number on 4, mis määrab MAX. Ainult vigade eiramiseks pakutakse valikuid 6.

Näide 3 - suuruselt n

D8: D10 valemid näitavad, kuidas tagastada n-nda suurima väärtuse:

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

Funktsiooni number on siin 14, mis käivitab funktsiooni LARGE. Kuna funktsioon SUUR nõuab ak argumenti, kuvatakse see viimase argumendina kolmes ülaltoodud valemis.

Näide # 4 - massiivi operatsioon

AGGREGATE muudab keerukamate valemite jaoks eriti kasulikuks see, et see saab massiive naturaalselt käsitseda, kui funktsiooninumber on 14–19. Näiteks esmaspäeviti MAX-väärtuse leidmiseks, kasutades andmeid, mis sisaldavad kuupäevi ja väärtusi, võite kasutada AGGREGATE-i järgmiselt:

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

Siin määrame funktsiooni (LARGE) jaoks 14 ja valiku jaoks 6 (ignoreerige vigu). Seejärel koostame funktsiooni TEXT abil loogilise avaldise, et kontrollida kõiki esmaspäevade kuupäevi. Selle toimingu tulemuseks on TRUE / FALSE väärtuste massiiv, millest saab algsete väärtuste nimetaja. FALSE hindab nulli ja viskab # DIV / 0! viga. TÕENE hindab väärtust 1 ja tagastab algse väärtuse. Lõplik väärtuste ja vigade massiiv toimib nagu filter. AGGREGATE ignoreerib kõiki vigu ja tagastab suurima (maksimaalse) säilinud väärtustest. Täielikum näide siin.

Funktsioonide numbrid

Allolevas tabelis on loetletud funktsioonile AGGREGATE saadaolevad funktsioonide numbrid koos seotud funktsiooni nimega. Kolmas veerg Ref2 tähistab teist argumenti väärtust, mida oodatakse vajadusel.

Funktsioon	Funktsioon	Viide 2
1	KESKMINE
2	COUNT
3	KUNST
4	MAX
5	MIN
6	TOOTE
7	STDEV.S
8	STDEV.P
9	SUM
10	VAR.S
11	VAR.P
12	KESKMINE
13	MODE.SNGL
14	SUUR	k
15	VÄIKE	k
16	PERCENTILE.INC	k
17	QUARTILE.INC	quart
18	PERCENTILE.EXC	k
19	QUARTILE.EXC	quart

Käitumisvalikud

Funktsioonil AGGREGATE on palju võimalusi vigade, peidetud ridade ja muude funktsioonide ignoreerimiseks. Võimalikud väärtused on 0–7, nagu on näidatud allolevas tabelis.

Variant	Käitumine
0	Ignoreeri funktsioone SUBTOTAL ja AGGREGATE
1	Ignoreeri peidetud ridu, funktsioone SUBTOTAL ja AGGREGATE
2	Ignoreeri veaväärtusi, funktsioone SUBTOTAL ja AGGREGATE
3	Eirake peidetud ridu, tõrke väärtusi, SUBTOTAL ja AGGREGATE funktsioone
4	Eirake midagi
5	Eirake varjatud ridu
6	Ignoreeri vea väärtusi
7	Eirake varjatud ridu ja veaväärtusi

Märkused

• AGGREGATE tagastab väärtuse #VALUE! viga, kui teine ​​funktsiooni argument on nõutav, kuid pole esitatud.
• 3D-viited ei tööta rakendusega AGGREGATE.
• Funktsioon AGGREGATE on kavandatud vertikaalsete, mitte horisontaalsete vahemike järgi.