Formula generică
=SUMPRODUCT(LARGE(rng,(1,2,N)))
rezumat
Pentru a însuma valorile de sus într-un interval, puteți utiliza o formulă bazată pe funcția LARGE, înfășurată în funcția SUMPRODUCT. În forma generică a formulei (de mai sus), rng reprezintă un interval de celule care conțin valori numerice, iar N reprezintă ideea valorii a N-a.
În exemplu, celula activă conține această formulă:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Explicaţie
În forma sa cea mai simplă, LARGE va returna valoarea "A cea mai mare" dintr-un interval. De exemplu, formula:
=LARGE(B4:B13, 2)
va returna a doua cea mai mare valoare din intervalul B4: B13 care, în exemplul de mai sus, este numărul 9.
Cu toate acestea, dacă furnizați o "constantă de matrice" (de exemplu, o constantă în forma (1,2,3)) către LARGE ca al doilea argument, LARGE va returna o matrice de rezultate în loc de un singur rezultat. Deci, formula:
=LARGE(B4:B13,(1,2,3))
va returna prima, a doua și a treia cea mai mare valoare din intervalul B4: B13. În exemplul de mai sus, unde B4: B13 conține numerele 1-10, rezultatul din LARGE va fi matricea (8,9,10). SUMPRODUCT însumează apoi numerele din această matrice și returnează un total, care este 27.
SUM în loc de SUMPRODUCT
SUMPRODUCT este o funcție flexibilă care vă permite să utilizați referințe de celule pentru k în funcția LARGE.
Cu toate acestea, dacă utilizați o constantă de matrice codificată simplu, cum ar fi (1,2,3), puteți utiliza doar funcția SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Rețineți că trebuie să introduceți această formulă ca formulă de matrice dacă utilizați referințe de celulă și nu o constantă de matrice pentru k în LARGE.
Când N devine mare
Când N devine mare devine plictisitor să creați manual constanta matricei - Dacă doriți să însumați primele 20 sau 30 de valori dintr-o listă mare, tastarea unei constante matrice cu 20 sau 30 de articole va dura mult timp. În acest caz, puteți utiliza o comandă rapidă pentru a construi constanta matricei care utilizează funcțiile ROW și INDIRECT.
De exemplu, dacă doriți să SUMAȚI cele mai bune 20 de valori dintr-un interval numit „rng” puteți scrie o formulă de genul acesta:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabila N
Cu date insuficiente, un N fix poate provoca erori. În acest caz, puteți încerca o formulă de genul acesta:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Aici, folosim MIN cu COUNT pentru a însuma cele mai bune 3 valori sau numărul de valori, dacă este mai mic de 3.
