Cum se utilizează funcția Excel AGGREGATE -

Cuprins

rezumat

Funcția Excel AGGREGATE returnează un calcul agregat, cum ar fi AVERAGE, COUNT, MAX etc., ignorând opțional rândurile ascunse și erorile. Sunt disponibile un total de 19 operații, specificate de numărul funcției în primul argument (a se vedea tabelul pentru opțiuni).

Scop

Returnează calculul agregat

Valoare returnată

Depinde de funcția specificată

Sintaxă

= AGGREGATE (funcție_num, opțiuni, ref1, ref2)

Argumente

  • function_num - Operațiune de efectuat (1-19).
  • opțiuni - Valori de ignorat (0-7).
  • ref1 - Primul argument.
  • ref2 - Al doilea argument (k).

Versiune

Excel 2010

Note de utilizare

Funcția AGREGARE returnează rezultatul unui calcul agregat, cum ar fi MEDIE, COUNT, MAX, MIN etc. Sunt disponibile în total 19 operațiuni, iar operația de efectuat este specificată ca număr, care apare ca primul argument din funcție. Al doilea argument, opțiuni, controlează modul în care AGGREGATE tratează erorile și valorile din rândurile ascunse. Vedeți tabelele de mai jos pentru toate opțiunile disponibile.

Exemplul nr. 1

Pentru a returna valoarea MAX în intervalul A1: A10, ignorând atât erorile, cât și rândurile ascunse, furnizați 4 pentru numărul funcției și 7 pentru opțiuni:

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

Pentru a returna valoarea MIN cu aceleași opțiuni, schimbați numărul funcției la 5:

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

Exemplul nr. 2

În exemplul prezentat mai sus, formula din D5 este:

=AGGREGATE(4,6,values)

unde „valori” este intervalul numit B5: B14. Numărul funcției este 4, care specifică MAX. Opțiunile sunt furnizate ca 6, pentru a ignora numai erorile.

Exemplul # 3 - al nouălea ca mărime

Formulele din D8: D10 demonstrează cum se returnează valorile "a nea cea mai mare":

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

Numărul funcției aici este 14, care rulează funcția LARGE. Deoarece funcția LARGE necesită argument ak, apare ca ultim argument în cele trei formule de mai sus.

Exemplul # 4 - operațiune matrice

Ceea ce face AGGREGATE util în special pentru formule mai complexe este că poate gestiona matrici în mod nativ atunci când numărul funcției este 14-19. De exemplu, pentru a găsi valoarea MAX luni, cu date care includ date și valori, puteți utiliza AGGREGATE astfel:

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

Aici specificăm 14 pentru funcție (LARGE) și 6 pentru opțiune (ignorați erorile). Apoi construim o expresie logică folosind funcția TEXT pentru a verifica toate datele pentru luni. Rezultatul acestei operații este o serie de valori ADEVĂRATE / FALSE, care devin numitorul valorilor originale. FALSE evaluează ca zero și aruncă un # DIV / 0! eroare. TRUE evaluează 1 și returnează valoarea inițială. Matricea finală de valori și erori acționează ca un filtru. AGGREGATE ignoră toate erorile și returnează cea mai mare (maximă) dintre valorile supraviețuitoare. Un exemplu mai complet aici.

Numerele funcției

Tabelul de mai jos prezintă numerele de funcții disponibile funcției AGGREGATE, împreună cu numele funcției asociate. Cea de-a treia coloană, Ref2, indică a doua valoare a argumentului așteptată atunci când este necesar.

Funcţie Funcţie Ref2
1 IN MEDIE
2 NUMARA
3 COUNTA
4 MAX
5 MIN
6 PRODUS
7 STDEV.S
8 STDEV.P
9 SUMĂ
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 MARE k
15 MIC k
16 PERCENTILE.INC k
17 QUARTILE.INC quart
18 PERCENTILE.EXC k
19 QUARTILE.EXC quart

Opțiuni de comportament

Funcția AGREGARE are multe opțiuni pentru a ignora erorile, rândurile ascunse și alte funcții. Valorile posibile sunt 0-7, așa cum se arată în tabelul de mai jos.

Opțiune Comportament
0 Ignorați funcțiile SUBTOTAL și AGREGATE
1 Ignorați rândurile ascunse, funcțiile SUBTOTAL și AGREGATE
2 Ignorați valorile de eroare, funcțiile SUBTOTAL și AGREGATE
3 Ignorați rândurile ascunse, valorile erorilor, funcțiile SUBTOTAL și AGGREGATE
4 Nu ignora nimic
5 Ignorați rândurile ascunse
6 Ignorați valorile de eroare
7 Ignorați rândurile ascunse și valorile erorilor

Note

  • AGGREGATE returnează un #VALUE! eroare dacă este necesar un al doilea argument funcțional, dar nu este furnizat.
  • Referințele 3D nu funcționează cu AGGREGATE.
  • Funcția AGREGAT este proiectată pe intervale verticale, nu pe orizontale.

Articole interesante...