Formula generică
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
rezumat
Pentru a însuma cele mai mici valori n într-un interval de potrivire a criteriilor, puteți utiliza o formulă matrice bazată pe funcția SMALL, înfășurată în funcția SUM. În forma generică a formulei (de mai sus), intervalul 1 reprezintă intervalul de celule comparativ cu criteriile , intervalul 2 conține valori numerice din care sunt recuperate valorile inferioare, iar N reprezintă „al n-lea”.
În exemplu, celula activă conține această formulă:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Unde culoarea este gama numită B5: B12 și valoarea este gama numită C5: C12.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
În forma sa cea mai simplă, SMALL returnează valoarea „A cea mai mică” dintr-un interval cu această construcție:
=SMALL (range,N)
Deci, de exemplu:
=SMALL (C5:C12,2)
va returna a 2-a cea mai mică valoare din intervalul C5: C12, care este 5 în exemplul prezentat.
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. Deci, formula:
=SMALL (C5:C12, (1,2,3))
va returna prima, a doua și a treia cea mai mică valoare C5: C12 într-o matrice ca aceasta: (4,5,7).
Deci, trucul de aici este să filtrați valorile pe baza culorii înainte de a rula SMALL. Facem acest lucru cu o expresie bazată pe funcția IF:
IF(color=E5,value)
Aceasta creează matricea de valori introduse în SMALL. În esență, numai valorile asociate cu culoarea roșie intră în matrice. În cazul în care culoarea este egală cu „roșu”, matricea conține un număr, iar în cazul în care culoarea nu este roșie, matricea conține FALS:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Funcția SMALL ignoră valorile FALSE și returnează cele mai mici 3 valori din matrice: (4,8,10). Funcția SUM returnează rezultatul final, 22.
