4 formule pentru a însuma valorile N de top în Excel

Dacă doriți să rezumați cele 3 valori în Excel, puteți face acest lucru prin simpla calculare a primelor 3 valori folosind o funcție LARGE și sumare.

Dar asta nu este singura cale. Puteți însuma N valori de top în Excel folosind mai multe formule. Toți vor folosi funcția LARGE ca funcție cheie. Vezi imaginea de mai sus. Să le explorăm pe toate.

Suma valorilor Top 3 sau Top N folosind funcția SUMIF

Formula generică

= SUMIF (interval, "> =" & LARGE (interval, n))

Gamă:Intervalul este intervalul din care doriți să însumați cele mai bune valori N.

n:Acesta este numărul de valori de top pe care doriți să le rezumați. Dacă doriți să însumați primele 3 valori atuncineste 3.

Deci, să vedem această formulă în acțiune.

Exemplul 1: Utilizarea SUMIF pentru a însuma cele mai bune 3 vânzări.

Avem date care conțin vânzările efectuate în diferite luni. Trebuie să rezumăm primele 3 vânzări. Folosim formula generică de mai sus.

The gamă este C2: C13 șin este 3. Apoi formula va fi:

= SUMIF (C2: C13, "> =" & LARGE (C2: C13,3))

Aceasta revine la 696020, care este corect. Dacă doriți să rezumați variabilele de sus N valori și N este scris în D2 atunci formula va fi:

= SUMIF (C2: C13, "> =" & LARGE (C2: C13, D2))

va însuma toate valorile care sunt mai mari sau egale cu valoarea celei mai mari N valori.

Cum funcționează asta?

Funcția LARGE returnează cea de-a N-a valoare superioară în intervalul dat. În formula de mai sus, funcția LARGE returnează a treia cea mai mare valoare din intervalC2: C13care este 212940. Acum formula este:

= SUMIF (C2: C13, "> =" & 212940)

Acum funcția SUMIF returnează SUMA valorilor mai mari sau egale cu 212940, care este 696020.

Dacă doriți să adăugați mai multe condiții pentru a suma primelor 3 sau N valori, utilizați funcția SUMIFS.

Suma valorilor Top 3 sau Top N folosind funcția SUM

Metoda de mai sus este destul de ușoară și rapidă de utilizat atunci când vine vorba de a rezuma primele 3 sau N valori din listă. Dar devine dificil atunci când vine vorba de însumarea valorilor N arbitrare de top, să zicem valoarea a doua, a patra și a șasea dintr-un interval Excel. Aici, funcția SUM sau SUMPRODUCT realizează munca.

Formula generică:

= SUM (LARGE (interval, {1,2,3}))

= SUMPRODUCT (LARGE (interval, {1,2,3}))

Gamă:Intervalul este intervalul din care doriți să rezumați cele mai bune N valori.

{1,2,3}: Acestea sunt valorile de top pe care doriți să le rezumați. Aici este 1, 2 și 3.

Deci, să folosim această formulă în exemplul de mai sus.

Exemplul 2: Sumați cele mai mari valori 1, 2 și 3 dintr-un interval Excel.

Deci, folosim același tabel pe care l-am folosit în exemplul de mai sus. The gamă este încă C2: C13 și primele 3 valori pe care dorim să le însumăm sunt 1, 2 și 3 cele mai mari valori din gamă.

= SUMPRODUCT (LARGE (C2: C13, {1,2,3}))

Returnează același rezultat ca înainte, care este 696020. Dacă doriți să însumați a doua și a patra valoare de sus, atunci formula va fi:

= SUMPRODUCT (LARGE (C2: C13, {2,4}))

Iar rezultatul va fi 425360.

Puteți înlocui funcția SUMPRODUCT cu funcția SUM în cazul de mai sus. Dar dacă doriți să faceți referire la valorile de top, atunci va trebui să utilizați CTRL + SHIF + ENTER pentru a face din aceasta o formulă matrice.

{= SUM (LARGE (C2: C13, E2: E4))}}

Cum functioneazã?

Funcția LARGE acceptă o serie de valori cheie și returnează o serie de valori de top. Aici funcția mare returnează valorile {241540; 183820; 38740} ca valori de top 2, 4 și 6. Acum formula devine:

= SUM ({241540; 183820; 38740})

= SUMPRODUCT ({241540; 183820; 38740})

Aceasta rezumă valorile returnate de funcția LARGE.

Sumând o gamă largă de valori de top în Excel

Să presupunem că doriți să însumați valorile de top 10-2o valori, all-inclusive. În acest caz, metodele menționate mai sus vor deveni agitate și mai lungi. Cel mai bun mod este să utilizați formula generică de mai jos pentru a rezuma valorile de top:

= SUMPRODUCT (LARGE (interval, ROW (INDIRECT ("start: end"))))

gamă:Intervalul este intervalul din care doriți să însumați cele mai bune valori N.

„start: end”:Este textul gamei de valori de top pe care doriți să îl rezumați. Dacă doriți câteva valori între 3 și 6, atunci începeți: sfârșitul va fi „3: 6”.

Să vedem această formulă în acțiune:

Exemplul 3: Sumați primele 3 până la 6 valori din vânzări:

Deci avem același tabel ca exemplul de mai sus. Singura interogare este diferită. Folosind formula generică de mai sus, scriem această formulă:

= SUMPRODUCT (LARGE (C2: C13, ROW (INDIRECT ("3: 6")))))

Aceasta returnează valoarea 534040. Care este suma a 3-a, a 4-a, a 5-a și a 6-a cea mai mare valoare din intervalul C2: C13.

Cum funcționează asta?

În primul rând, funcția INDIRECT transformă textul „3: 6” în intervalul real 3: 6. În continuare, funcția ROW returnează o serie de numere de rând în intervalul 3: 6, care va fi {3,4,5,6}. Apoi, funcția LARGE returnează a 3-a, a 4-a, a 5-a și a 6-a cea mai mare valoare din intervalul C2: C13. În cele din urmă, funcția SUMPRODUCT rezumă aceste valori și obținem rezultatul nostru.

= SUMPRODUCT (LARGE (C2: C13, ROW (INDIRECT ("3: 6")))))

=SUMPRODUCT (LARGE (C2: C13,{3,4,5,6}))

= SUMPRODUCT ({212940;183820;98540;38740})

= 534040

Bonus: Sumați valorile de top N folosind funcția SEQUENCE

Funcția SEQUENCE este introdusă în Excel 2019 și 365. Returnează o succesiune de numere. Îl putem folosi pentru a obține cele mai bune valori N și apoi pentru a le rezuma.

Formula generică:

= SUMPRODUCT (LARGE (interval, SEQUENCE (num_values ,, [start_num], [step])))))

gamă:Intervalul este intervalul din care doriți să însumați cele mai bune valori N.

num_values: Este numărul valorilor de top pe care doriți să le însumați.

[start_num]:Este numărul inițial al seriei. Este opțional. Dacă omiteți acest lucru, seria va începe de la 1.

[pași]: Este diferența dintre numărul următor și numărul curent. În mod implicit, este 1.

Dacă folosim această formulă generică pentru a obține același rezultat ca în exemplul anterior, formula va fi:

= SUM (LARGE (C2: C13, SECVENȚA (4,, 3)))

Se va returna valoarea 534040.

Cum functioneazã?

Este simplu. Funcția SEQUENCE returnează o serie de 4 valori care încep cu 3 cu intervalul 1. Returnează matricea {3; 4; 5; 6}. Acum funcția LARGE returnează cele mai mari valori ale numerelor corespunzătoare. În cele din urmă, funcția SUM rezumă aceste valori și obținem rezultatul ca 534040.

= SUMĂ (LARGE (C2: C13, SECVENȚĂ (4,,3)))

=SUMĂ (MARE (C2: C13,{3;4;5;6}))

=SUMĂ({212940;183820;98540;38740})

=534040

Deci, băieți, așa puteți rezuma primele 3, 5, 10, … N valori în Excel. Am încercat să fiu explicativ. Sper să te ajute. Dacă aveți îndoieli cu privire la acest subiect sau la orice alt subiect Excel VBA, întrebați în secțiunea de comentarii de mai jos. Răspund la întrebări frecvent.

Suma dacă este mai mare de 0 în Excel | Pentru a însuma doar valori pozitive, trebuie să sumăm valori mai mari de 0. Pentru a face acest lucru, vom folosi funcția SUMIF a Excel.

Cum se sumează vânzările din ultimele 30 de zile în Excel Uneori este rezumat și pe intervalul standard de 30 de zile. În acest caz, să aveți o formulă care să arate dinamic suma ultimelor 30 de zile va fi o idee bună. În acest articol, vom învăța cum să însumăm valorile din ultimele 30 de zile în Excel.

Cum să însumați ultimele n coloane în Excel | Uneori este dată o condiție, adică atunci când trebuie să obținem suma ultimelor 5 sau n numere din coloană. Puteți efectua cu ușurință soluția la această problemă utilizând funcțiile Excel.

Cum se face suma prin potrivirea rândului și a coloanei în Excel | Cum sumăm valori atunci când trebuie să potrivim coloana și rândul. În acest articol, vom învăța cum să facem suma rândurilor și coloanelor potrivite.

Articole populare:

50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.

Cum se utilizează funcția Excel VLOOKUP| Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.

Cum se folosește Excel Funcția COUNTIF| Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra o anumită valoare. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.

Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.

Articole populare:

50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.

Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.