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ă utilizați .
Sintaxă
Există două sintaxi pentru formula AGGREGATE:
- Sintaxă de referință
= AGGREGATE (funcție_num, opțiuni, ref1, ref2, ref (3), …)
- 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 sau valorile numerice pe care dorim să efectuăm calculul, sunt necesare cel puțin două argumente. Argumentele de rest sunt opționale.
Array: este o serie de valori pe care dorim să operăm; este utilizat în sintaxa matrice a funcției AGGREGATE în excel.
K: este un argument opțional și o valoare numerică; este utilizat atunci când se utilizează funcția ca LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC sau QUARTILE.EXC în Excel.
Exemple
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. În medie, funcția_număr este 1
În Coloana C, sunt date toate valorile și nu va trebui să ignorăm nicio valoare, astfel încât să selectăm 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 la omiteți 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ă folosim aceeași formulă AGGREGATE, vom primi o eroare. Totuși, atunci când este utilizată o opțiune adecvată, AGGREGATE în Excel dă media valorilor rămase neglijând valoarea erorii în E6.
Pentru a ignora valorile erorii, avem opțiunea 6.
În mod similar, pentru intervalul G1: G8, vom folosi opțiunea 6 (ignorați 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, AGREGAT î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, funcția sumă va da întotdeauna suma totală
Vrem ca atunci când filtrăm canalul, să obținem suma valorilor vizibile, deci, în loc să folosim funcția SUM, vom folosi funcția AGREGARE pentru a obține suma valorilor care sunt vizibile atunci când este aplicat un filtru.
Deci, înlocuind formula SUM cu o funcție AGGREGATE cu codul de opțiune 5 (ignorând rândurile și valorile ascunse), avem,
Acum, când aplicăm 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:
Putem vedea că funcția AGGREGATE calculează diferitele valori ale sumelor pentru veniturile generate pentru diferite canale odată ce acestea sunt filtrate. Deci, funcția AGGREGATE poate fi utilizată dinamic pentru a înlocui diferite funcții pentru condiții diferite, fără a utiliza formula condițională.
Să presupunem că pentru același tabel, unele dintre valorile noastre de venituri 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, primim eroarea pentru SUMA venitului total. Pentru a ignora erorile, trebuie să folosim opțiunea 6
Folosind opțiunea 6, obținem suma ignorând valorile erorii. Totuși, când aplicăm filtrul, de exemplu, filtrează după valoarea canalului Direct, 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 AGREGARE nu recunoaște funcția _ num valoare mai mare de 19 sau mai mică de 1. În mod similar, pentru Opțiunea număr, nu identifică valorile mai mari de 7 și mai mici de 1; dacă oferim 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.
