Formula generică
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
rezumat
Pentru a însuma primele n valori într-un interval de potrivire a criteriilor, puteți utiliza o formulă bazată pe funcția LARGE, înfășurată în funcția SUMPRODUCT. În forma generică a formulei (de mai sus), intervalul reprezintă o gamă de celule care sunt comparate cu criteriile , valorile reprezintă valori numerice din care sunt recuperate valorile superioare, iar N reprezintă ideea valorii a N-a.
În exemplu, celula activă conține această formulă:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Unde culoarea este gama numită B5: B12 și valoarea este gama numită C5: C12.
Explicaţie
În forma sa cea mai simplă, LARGE returnează valoarea "A cea mai mare" dintr-un interval cu această construcție:
=LARGE (range,N)
Deci, de exemplu:
=LARGE (C5:C12,2)
va returna cea de-a doua cea mai mare valoare din intervalul C5: C12, care este 12 în exemplul prezentat.
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 (C5:C12, (1,2,3))
va returna prima, a doua și a treia cea mai mare valoare C5: C12 într-o matrice ca aceasta: (12,12,10)
Deci, trucul de aici este să filtrați valorile pe baza culorii înainte de a rula LARGE. Facem acest lucru cu expresia:
(color=E5)
Ceea ce rezultă într-o serie de valori ADEVĂRATE / FALSE. În timpul operației de multiplicare, aceste valori sunt constrânse în unii și zerouri:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Deci, rezultatul final este că numai valorile asociate cu culoarea „roșie” supraviețuiesc operației:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
iar celelalte valori sunt forțate la zero.
Notă: această formulă nu va gestiona textul în intervalul de valori. Vezi mai jos.
Gestionarea textului în valori
Dacă aveți text oriunde în intervalele de valori, funcția LARGE va arunca o eroare #VALUE și va opri formula să funcționeze.
Pentru a gestiona textul în intervalul de valori, puteți adăuga funcția IFERROR astfel:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Aici, captăm erori din LARGE cauzate de valorile textului și le înlocuim cu zero. Utilizarea IF în interiorul LARGE necesită introducerea formulei cu control + shift + enter, deci trecem la SUM în loc de SUMPRODUCT.
Notă: am întâlnit această formulă postată de uimitorul Barry Houdini pe stackoverflow.
