Cum se folosește funcția AGGREGATE în Excel? | (cu exemple)

Funcția AGREGAT în Excel

Funcția AGGREGATE în Excel returnează agregatul unui anumit tabel de date sau liste de date, această funcție are și primul argument ca număr de funcție și alte argumente sunt pentru o serie de seturi de date, numărul funcției ar trebui să fie amintit pentru a ști ce funcție să utilizeze .

Sintaxă

Există două sintaxi pentru formula AGGREGATE:

  1. Sintaxă de referință

= AGGREGATE (funcție_num, opțiuni, ref1, ref2, ref [3], ...)

  1. Sintaxa matricei

= AGGREGATE (număr_funcție, opțiuni, matrice, [k])

Function_num este un număr care denotă o funcție specifică pe care dorim să o folosim, este un număr de la 1-19

Opțiune: este, de asemenea, o valoare numerică cuprinsă între 0 și 7 și determină ce valori trebuie ignorate în timpul calculelor

Ref1, ref2, ref [3]:  este argumentul în timpul utilizării sintaxei de referință, este valoarea numerică sau valorile pe care dorim să efectuăm calculul, cel puțin două argumente sunt necesare argumentele de rest sunt opționale.

Array: este o matrice de valori pe care dorim să efectuăm operația, este utilizată în sintaxa matrice a funcției AGGREGATE în excel

K: este un argument opțional și este o valoare numerică, este utilizat atunci când este utilizată funcția ca LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC sau QUARTILE.EXC în Excel.

Exemple

Puteți descărca acest șablon Excel funcție AGGREGATE aici - Șablon Excel funcție AGGREGATE

Exemplu - # 1

Să presupunem că avem o listă de numere și vom calcula Media, Numărul care este numărul de celule care conțin o valoare, Contul - număr de celule care nu sunt goale, Maxim, Minim, produs și suma valorilor numerice date. Valorile sunt date mai jos în tabel:

Să calculăm mai întâi Media din rândul 9, pentru toate valorile date. Pentru medie funcția_număr este

În Coloana C, toate valorile sunt date și nu va trebui să ignorăm nici o valoare, așa că vom selecta Opțiunea 4 (nu ignora nimic)

Și selectând gama de valori C1: C8 ca o serie de valori numerice

Deoarece „ k” este un argument opțional și este utilizat atunci când se utilizează o funcție precum LARGE, SMALL în Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC sau QUARTILE.EXC, dar în acest caz, calculăm Media, astfel încât să va omite valoarea lui k.

Deci, valoarea medie este

În mod similar, pentru intervalul D1: D8, din nou vom selecta opțiunea 4.

Pentru intervalul E1: E8, o celulă E6 conține o valoare de eroare, dacă vom folosi aceeași formulă AGGREGATE vom primi o eroare, dar atunci când este utilizată o opțiune adecvată, AGGREGATE în Excel dă media valorilor rămase neglijând eroarea. valoare în E6.

Pentru a ignora valorile erorii, avem opțiunea 6.

În mod similar, pentru intervalul G1: G8 vom folosi opțiunea 6 (ignorăm valorile erorii)

Acum, pentru intervalul H3 dacă punem o valoare 64 și ascundem al treilea rând și folosim opțiunea 5, pentru a ignora rândul ascuns, AGREGARE în Excel vom da valoarea medie numai pentru valorile numerice vizibile.

Ieșire fără ascunderea rândului 3

Ieșire după ascunderea rândului 3

Aplicând formula AGREGAT pentru alte operațiuni, avem

Exemplu - # 2

Să presupunem că avem un tabel pentru veniturile generate la diferite date de la diferite canale, după cum se arată mai jos

Acum, dorim să verificăm veniturile generate pentru diferite canale. Deci, atunci când aplicăm funcția sumă, obținem venitul total generat, dar în cazul în care dorim să verificăm veniturile generate pentru canalul organic sau canalul direct sau oricare altul, atunci când aplicăm filtre în excel pentru același lucru, funcția sumă va fi întotdeauna da suma totală

Vrem ca atunci când filtrăm canalul, să obținem suma valorilor vizibile, așa că, în loc să folosim funcția SUM, vom folosi funcția AGREGARE pentru a obține suma valorilor care sunt vizibile atunci când un filtru este aplicat.

Deci, înlocuind formula SUMĂ cu o funcție AGGREGATE cu codul de opțiune 5 (ignorând rândurile și valorile ascunse) pe care le avem,

Acum, când vom aplica filtrul pentru diferite canale, acesta va afișa veniturile pentru acel canal numai pe măsură ce restul rândurilor se ascund.

Venit total generat pentru canalul direct:

Venit total generat pentru canalul organic:

Venit total generat pentru canalul plătit:

Deci, putem vedea că funcția AGGREGATE calculează diferitele valori de sumă pentru veniturile generate pentru diferite canale odată ce acestea sunt filtrate. Deci, funcția AGGREGATE poate fi utilizată dinamic pentru înlocuirea diferitelor funcții pentru condiții diferite, fără a utiliza formula condițională.

Să presupunem că pentru același canal de tabel și venituri, unele dintre valorile veniturilor noastre conțin o eroare, acum trebuie să ignorăm erorile și, în același timp, dacă dorim să aplicăm un filtru, funcția AGREGARE ar trebui să ignore și valorile rândurilor ascunse.

Când folosim opțiunea 5, obținem eroarea pentru SUMA venitului total, acum pentru a ignora erorile trebuie să folosim opțiunea 6

Folosind opțiunea 6 obținem suma ignorând valorile erorii, dar atunci când aplicăm filtrul, de exemplu, filtrează după valoarea canalului, obținem aceeași sumă ignorând erorile, dar în același timp trebuie să ignorăm și valorile ascunse.

Deci, în acest caz, vom folosi opțiunea 7 care ignoră valorile erorii și, în același timp, rândurile ascunse

Lucruri de amintit

  • Funcția AGGREGATE nu recunoaște funcția _ num valoare mai mare de 19 sau mai mică de 1 și în mod similar pentru numărul Opțiunii nu recunoaște valorile mai mari de 7 și mai mici de 1, dacă oferim orice alte valori, dă un #VALUE ! Eroare
  • Acceptă întotdeauna valoarea numerică și returnează întotdeauna o valoare numerică ca ieșire
  • AGREGATE în Excel are o limitare; ignoră doar rândurile ascunse, dar nu ignoră coloanele ascunse.