Formula generică
=SUMPRODUCT(SMALL(rng,(1,2,n)))
rezumat
Pentru a însuma cele mai mici n valori dintr-un interval, puteți utiliza o formulă bazată pe funcția SMALL și 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ă numărul celor mai mici valori de sumat. În exemplul prezentat, E5 conține această formulă:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
care returnează suma celor trei valori mai mici din B5: B14, 60
Explicaţie
În forma sa cea mai simplă, SMALL va returna cea de-a "-a cea mai mică valoare" dintr-un interval. De exemplu:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Cu toate acestea, dacă furnizați o constantă de matrice (de exemplu, o constantă în forma (1,2,3)) către SMALL ca al doilea argument, SMALL va returna o matrice de rezultate în loc de un singur rezultat. De exemplu:
=SMALL(A1:A10,(1,2,3))
va returna prima, a doua și a treia cea mai mică valoare din intervalul A1: A10.
Lucrând din interior spre exterior în exemplul prezentat, SMALL returnează cele mai mici 3 valori din intervalul B5: B14:
=SMALL(B4:B14,(1,2,3))
Rezultatul este o matrice ca aceasta:
(10,20,30)
Această matrice este returnată direct la funcția SUMPRODUCT, care însumează numerele și returnează totalul:
SUMPRODUCT((10,20,30)) // returns 60
Formula matricei cu SUM
Este obișnuit să utilizați SUMPRODUCT ca mai sus, deoarece poate gestiona matrici în mod nativ fără a intra ca formulă matrice. Cu toate acestea, puteți scrie și o formulă matrice cu funcția SUM astfel:
(=SUM(SMALL(B4:B13,(1,2,3))))
Aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Când n devine mare
Când n devine mare, devine plictisitor să creezi manual constanta matricei - introducerea 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, pentru a SUMA cele mai mici 20 de valori dintr-un interval numit „rng” puteți scrie o formulă de genul acesta:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Aici, INDIRECT convertește șirul „1:20” în intervalul 1:20, care este returnat direct la SMALL.
Variabila n
Pentru a configura formula a în care n este o variabilă într-o altă celulă, puteți concatena în INDIRECT. De exemplu, dacă A1 conține N, puteți utiliza:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Aceasta permite utilizatorului să schimbe valoarea lui n direct pe foaia de lucru.
