Hoe de Excel AGGREGATE-functie - te gebruiken

Samenvatting

De Excel AGGREGATE-functie retourneert een geaggregeerde berekening zoals GEMIDDELDE, COUNT, MAX, etc., waarbij optioneel verborgen rijen en fouten worden genegeerd. Er zijn in totaal 19 bewerkingen beschikbaar, gespecificeerd door functienummer in het eerste argument (zie tabel voor opties).

Doel

Retourneer de totale berekening

Winstwaarde

Hangt af van de gespecificeerde functie

Syntaxis

= AGGREGATE (functie_getal, opties, ref1, ref2)

Argumenten

  • function_num - Uit te voeren bewerking (1-19).
  • options - Waarden om te negeren (0-7).
  • ref1 - Eerste argument.
  • ref2 - Tweede argument (k).

Versie

Excel 2010

Gebruiksopmerkingen

De functie AGGREGATE retourneert het resultaat van een geaggregeerde berekening zoals GEMIDDELDE, AANTAL, MAX, MIN, enz. Er zijn in totaal 19 bewerkingen beschikbaar en de uit te voeren bewerking wordt gespecificeerd als een getal, dat verschijnt als het eerste argument in de functie. Het tweede argument, options, bepaalt hoe AGGREGATE fouten en waarden in verborgen rijen verwerkt. Zie onderstaande tabellen voor alle beschikbare opties.

Voorbeeld 1

Om de MAX-waarde in het bereik A1: A10 te retourneren, waarbij zowel fouten als verborgen rijen worden genegeerd, geeft u 4 op voor functienummer en 7 voor opties:

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

Om de MIN-waarde met dezelfde opties te retourneren, wijzigt u het functienummer in 5:

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

Voorbeeld 2

In het bovenstaande voorbeeld is de formule in D5:

=AGGREGATE(4,6,values)

waarbij "waarden" het benoemde bereik B5: B14 is. Het functienummer is 4, wat MAX aangeeft. De opties zijn beschikbaar als 6, om alleen fouten te negeren.

Voorbeeld # 3 - n-de grootste

De formules in D8: D10 laten zien hoe u "n-de grootste" waarden retourneert:

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

Het functienummer hier is 14, waarmee de LARGE-functie wordt uitgevoerd. Omdat de LARGE-functie een ak-argument vereist, verschijnt het als het laatste argument in de drie bovenstaande formules.

Voorbeeld # 4 - array-bewerking

Wat AGGREGATE vooral handig maakt voor complexere formules, is dat het native arrays kan verwerken wanneer het functienummer 14-19 is. Als u bijvoorbeeld de MAX-waarde op maandag wilt vinden, met gegevens die datums en waarden bevatten, kunt u AGGREGATE als volgt gebruiken:

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

Hier specificeren we 14 voor functie (LARGE) en 6 voor optie (negeer fouten). Vervolgens bouwen we een logische uitdrukking op met de functie TEXT om alle datums voor maandag te controleren. Het resultaat van deze bewerking is een reeks TRUE / FALSE-waarden, die de noemer worden van de oorspronkelijke waarden. FALSE evalueert als nul, en gooit een # DIV / 0! fout. WAAR evalueert als 1 en retourneert de oorspronkelijke waarde. De laatste reeks waarden en fouten werkt als een filter. AGGREGATE negeert alle fouten en retourneert de grootste (maximum) van de overgebleven waarden. Compleet voorbeeld hier.

Functienummers

De onderstaande tabel bevat de functienummers die beschikbaar zijn voor de AGGREGATE-functie, samen met de naam van de bijbehorende functie. De derde kolom, Ref2, geeft de tweede argumentwaarde aan die wordt verwacht wanneer dat nodig is.

Functie Functie Ref2
1 GEMIDDELDE
2 AANTAL
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SOM
10 VAR.S
11 VAR.P
12 MEDIAAN
13 MODE.SNGL
14 GROOT k
15 KLEIN k
16 PERCENTILE.INC k
17 QUARTILE.INC kwart
18 PERCENTIEL.EXC k
19 QUARTILE.EXC kwart

Gedragsopties

De AGGREGATE-functie heeft veel opties om fouten, verborgen rijen en andere functies te negeren. Mogelijke waarden zijn 0-7, zoals weergegeven in de onderstaande tabel.

Keuze Gedrag
0 Negeer de functies SUBTOTAAL en AGGREGATIE
1 Negeer verborgen rijen, SUBTOTAAL en AGGREGATE functies
2 Negeer foutwaarden, SUBTOTAAL en AGGREGATE functies
3 Negeer verborgen rijen, foutwaarden, SUBTOTAAL en AGGREGATE functies
4 Negeer niets
5 Negeer verborgen rijen
6 Negeer foutwaarden
7 Negeer verborgen rijen en foutwaarden

Opmerkingen

  • AGGREGATE retourneert een #VALUE! fout als een tweede functieargument vereist is, maar niet is opgegeven.
  • 3D-verwijzingen werken niet met AGGREGATE.
  • De AGGREGATE-functie is ontworpen voor verticale bereiken, niet voor horizontale bereiken.

Interessante artikelen...