AGGREGATE-functie in Excel
AGGREGATE Functie in Excel retourneert de aggregatie van een gegeven gegevenstabel of gegevenslijsten, deze functie heeft ook het eerste argument als functienummer en verdere argumenten zijn voor een bereik van de gegevenssets, het functienummer moet worden onthouden om te weten welke functie moet worden gebruikt .
Syntaxis
Er zijn twee syntaxis voor de AGGREGATE-formule:
- Referentie syntaxis
= AGGREGATE (functie_getal, opties, ref1, ref2, ref (3), …)
- Array-syntaxis
= AGGREGATE (functie_getal, opties, matrix, (k))
Functie_getal is een getal dat een specifieke functie aangeeft die we willen gebruiken; het is een nummer van 1-19
Optie: het is ook een numerieke waarde van 0 tot 7 en bepaalt welke waarden bij berekeningen moeten worden genegeerd
Ref1, ref2, ref (3): is het argument tijdens het gebruik van de referentiesyntaxis; het is een numerieke waarde of waarden waarop we de berekening willen uitvoeren, er zijn ten minste twee argumenten vereist. Restargumenten zijn optioneel.
Array: is een reeks waarden waarop we willen werken; het wordt gebruikt in de array-syntaxis van de AGGREGATE-functie in Excel.
K: is een optioneel argument en numerieke waarde; het wordt gebruikt wanneer de functie zoals LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC of QUARTILE.EXC in Excel wordt gebruikt.
Voorbeelden
Voorbeeld 1
Stel dat we een lijst met getallen hebben, en we zullen het gemiddelde berekenen, het aantal dat is het aantal cellen dat een waarde bevat, het aantal cellen dat niet leeg is, het maximum, het minimum, het product en de som van de opgegeven numerieke waarden . Waarden worden hieronder in de tabel gegeven:
Laten we eerst het gemiddelde in rij 9 berekenen voor alle gegeven waarden. Voor gemiddelde is de function_ num 1
In kolom C worden alle waarden gegeven en hoeven we geen waarden te negeren, zodat we optie 4 selecteren (negeer niets)
En het bereik van waarden C1: C8 selecteren als een reeks numerieke waarden
Omdat ' k' een optioneel argument is en wordt gebruikt wanneer een functie zoals LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC of QUARTILE.EXC wordt gebruikt, maar in dit geval berekenen we het gemiddelde tot laat de waarde van k weg.
Dus de gemiddelde waarde is
Evenzo zullen we voor bereik D1: D8 opnieuw optie 4 selecteren.
Voor bereik E1: E8 bevat cel E6 een foutwaarde. Als we dezelfde AGGREGATE-formule gebruiken, krijgen we een foutmelding. Maar als een geschikte optie wordt gebruikt, geeft de AGGREGATE in Excel het gemiddelde van de resterende waarden, waarbij de foutwaarde in E6 wordt verwaarloosd.
Om de foutwaarden te negeren, hebben we optie 6.
Evenzo zullen we voor bereik G1: G8 de optie 6 gebruiken (negeer de foutwaarden)
Als we nu voor bereik H3 een waarde 64 plaatsen en de derde rij verbergen en optie 5 gebruiken om de verborgen rij, de AGGREGATE in Excel, te negeren, geven we de gemiddelde waarde alleen voor zichtbare numerieke waarden.
Uitvoer zonder rij te verbergen 3
Uitvoer na het verbergen van rij 3
We passen de AGGREGATE-formule toe op andere bewerkingen
Voorbeeld - # 2
Stel dat we een tabel hebben met de inkomsten die op verschillende datums via de verschillende kanalen zijn gegenereerd, zoals hieronder weergegeven
Nu willen we de gegenereerde inkomsten voor verschillende kanalen bekijken. Dus wanneer we de somfunctie toepassen, krijgen we de totale gegenereerde inkomsten, maar als we de gegenereerde inkomsten voor organisch kanaal of direct kanaal of een ander willen controleren, wanneer we filters in Excel toepassen voor hetzelfde, de somfunctie geeft altijd het totale bedrag
We willen dat wanneer we het kanaal filteren, we de som van de zichtbare waarden krijgen, dus in plaats van de SOM-functie te gebruiken, zullen we de AGGREGATE-functie gebruiken om de som te krijgen van de waarden die zichtbaar zijn wanneer een filter wordt toegepast.
Dus als we de SOM-formule vervangen door een AGGREGATE-functie met optiecode 5 (waarbij de verborgen rijen en waarden worden genegeerd), hebben we,
Als we het filter nu toepassen op verschillende kanalen, worden de inkomsten voor dat kanaal alleen weergegeven als de rest van de rijen wordt verborgen.
Totale gegenereerde inkomsten voor direct kanaal:
Totale gegenereerde inkomsten voor organisch kanaal:
Totale gegenereerde inkomsten voor betaald kanaal:
We kunnen zien dat de AGGREGATE-functie de verschillende Sum-waarden berekent voor de inkomsten die voor verschillende kanalen zijn gegenereerd nadat ze zijn gefilterd. De functie AGGREGATE kan dus dynamisch worden gebruikt om verschillende functies voor verschillende voorwaarden te vervangen zonder de voorwaardelijke formule te gebruiken.
Stel dat voor dezelfde tabel sommige van onze inkomstenwaarden een fout bevatten, nu moeten we de fouten negeren, en tegelijkertijd, als we een filter willen toepassen, zou de AGGREGATE-functie de verborgen rijwaarden ook moeten negeren.
Als we optie 5 gebruiken, krijgen we de fout voor de SOM van de totale inkomsten. Om de fouten te negeren, moeten we optie 6 gebruiken
Door optie 6 te gebruiken, krijgen we de som die de foutwaarden negeert. Maar als we het filter toepassen, bijvoorbeeld filteren op kanaalwaarde Direct, krijgen we dezelfde som zonder de fouten te negeren, maar tegelijkertijd moeten we ook de verborgen waarden negeren.
Dus in dit geval zullen we optie 7 gebruiken die de foutwaarden negeert en tegelijkertijd de verborgen rijen.
Dingen om te onthouden
- De functie AGGREGATE herkent de functie _ num waarde groter dan 19 of kleiner dan 1 niet. Evenzo identificeert het voor Option nummer niet de waarden groter dan 7 en kleiner dan 1; als we andere waarden opgeven, geeft dit een #VALUE! Fout
- Het accepteert altijd de numerieke waarde en retourneert altijd een numerieke waarde als uitvoer
- De AGGREGATE in Excel heeft een beperking; het negeert alleen de verborgen rijen, maar niet de verborgen kolommen.
