Kā izmantot funkciju Excel AGGREGATE -

Kopsavilkums

Funkcija Excel AGGREGATE atgriež kopējo aprēķinu, piemēram, AVERAGE, COUNT, MAX utt., Pēc izvēles ignorējot slēptās rindas un kļūdas. Kopumā ir pieejamas 19 operācijas, kas norādītas pēc funkcijas numura pirmajā argumentā (opcijas skatiet tabulā).

Mērķis

Atgriezt kopējo aprēķinu

Atgriešanās vērtība

Atkarīgs no norādītās funkcijas

Sintakse

= AGGREGATE (function_num, options, ref1, ref2)

Argumenti

  • function_num - darbība, lai veiktu (1-19).
  • opcijas - ignorējamās vērtības (0–7).
  • ref1 - pirmais arguments.
  • ref2 - otrais arguments (k).

Versija

Excel 2010

Lietošanas piezīmes

Funkcija AGGREGATE atgriež apkopota aprēķina rezultātu, piemēram, AVERAGE, COUNT, MAX, MIN utt. Kopumā ir pieejamas 19 darbības, un veicamā darbība ir norādīta kā skaitlis, kas parādās kā pirmais arguments funkcijā. Otrais arguments, opcijas, kontrolē to, kā AGGREGATE apstrādā slēpto rindu kļūdas un vērtības. Skatiet visas pieejamās iespējas zemāk esošajās tabulās.

1. piemērs

Lai atgrieztu MAX vērtību diapazonā A1: A10, ignorējot gan kļūdas, gan slēptās rindas, norādiet 4 funkcijas numuram un 7 opcijām:

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

Lai atgrieztu MIN vērtību ar tām pašām opcijām, mainiet funkcijas numuru uz 5:

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

2. piemērs

Iepriekš parādītajā piemērā formula D5 ir:

=AGGREGATE(4,6,values)

kur "vērtības" ir nosauktais diapazons B5: B14. Funkcijas numurs ir 4, kas norāda MAX. Opcijas ir norādītas kā 6, lai ignorētu tikai kļūdas.

3. piemērs - n lielākais

Formulas D8: D10 parāda, kā atgriezt "n-tās lielākās" vērtības:

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

Funkcijas numurs šeit ir 14, kas palaiž funkciju LARGE. Tā kā funkcijai LARGE ir nepieciešams ak arguments, tas tiek parādīts kā pēdējais arguments iepriekš minētajās trīs formulās.

4. piemērs - masīva darbība

Īpaši noderīgu AGGREGATE sarežģītākām formulām padara tas, ka tā var ar masīviem rīkoties dabiski, ja funkcijas numurs ir 14-19. Piemēram, lai atrastu MAX vērtību pirmdienās, izmantojot datus, kas ietver datumus un vērtības, jūs varētu izmantot AGGREGATE šādi:

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

Šeit mēs norādām 14 funkcijai (LARGE) un 6 opcijai (ignorēt kļūdas). Pēc tam mēs izveidojam loģisku izteiksmi, izmantojot funkciju TEXT, lai pārbaudītu visus pirmdienu datumus. Šīs operācijas rezultāts ir TRUE / FALSE vērtību masīvs, kas kļūst par sākotnējo vērtību saucēju. FALSE tiek vērtēts kā nulle un iemet # DIV / 0! kļūda. Patiesība TRUE tiek novērtēta kā 1 un atgriež sākotnējo vērtību. Pēdējais vērtību un kļūdu masīvs darbojas kā filtrs. AGGREGATE ignorē visas kļūdas un atgriež lielāko (maksimālo) no saglabātajām vērtībām. Pilnīgāks piemērs šeit.

Funkciju numuri

Zemāk esošajā tabulā ir norādīti funkcijai AGGREGATE pieejamie funkciju numuri, kā arī saistītās funkcijas nosaukums. Trešajā kolonnā Ref2 ir norādīta otrā argumenta vērtība, kas tiek sagaidīta pēc nepieciešamības.

Funkcija Funkcija 2. atsauce
1 VIDĒJAIS
2 COUNT
3 KONTA
4 MAX
5 MIN
6 PRODUKTS
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 Vidusmēra
13 MODE.SNGL
14 LIELS k
15 MAZA k
16 PERCENTILE.INC k
17 QUARTILE.INC kvarts
18 PERCENTILE.EXC k
19 QUARTILE.EXC kvarts

Uzvedības iespējas

Funkcijai AGGREGATE ir daudz iespēju ignorēt kļūdas, slēptās rindas un citas funkcijas. Iespējamās vērtības ir 0-7, kā parādīts zemāk esošajā tabulā.

Opcija Uzvedība
0 Ignorēt funkcijas SUBTOTAL un AGGREGATE
1 Ignorēt slēptās rindas, SUBTOTAL un AGGREGATE funkcijas
2 Ignorēt kļūdu vērtības, SUBTOTAL un AGGREGATE funkcijas
3 Ignorēt slēptās rindas, kļūdu vērtības, SUBTOTAL un AGGREGATE funkcijas
4 Ignorēt neko
5 Ignorēt slēptās rindas
6 Ignorēt kļūdu vērtības
7 Ignorēt slēptās rindas un kļūdu vērtības

Piezīmes

  • AGGREGATE atgriež vērtību #VALUE! kļūda, ja ir nepieciešams otrs funkcijas arguments, bet tas nav norādīts.
  • 3D atsauces nedarbojas ar AGGREGATE.
  • Funkcija AGGREGATE ir paredzēta vertikāliem, nevis horizontāliem diapazoniem.

Interesanti raksti...