Generieke formule
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Samenvatting
Om de hoogste n-waarden in een bereik dat overeenkomt met criteria bij elkaar op te tellen, kunt u een formule gebruiken die is gebaseerd op de LARGE-functie, verpakt in de SUMPRODUCT-functie. In de generieke vorm van de formule (hierboven) vertegenwoordigt bereik een celbereik dat wordt vergeleken met criteria , vertegenwoordigen waarden numerieke waarden waaruit de hoogste waarden worden opgehaald en vertegenwoordigt N het idee van de N- de waarde.
In het voorbeeld bevat de actieve cel deze formule:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Waarbij kleur het benoemde bereik B5: B12 is en waarde het benoemde bereik C5: C12.
Uitleg
In de eenvoudigste vorm retourneert LARGE de "N-de grootste" waarde in een bereik met deze constructie:
=LARGE (range,N)
Dus bijvoorbeeld:
=LARGE (C5:C12,2)
retourneert de op een na grootste waarde in het bereik C5: C12, wat 12 is in het getoonde voorbeeld.
Als u echter een "matrixconstante" (bijv. Een constante in de vorm (1,2,3)) opgeeft aan LARGE als tweede argument, retourneert LARGE een matrix met resultaten in plaats van een enkel resultaat. Dus de formule:
=LARGE (C5:C12, (1,2,3))
retourneert de 1e, 2e en 3e grootste waarde C5: C12 in een array als deze: (12,12,10)
De truc hier is dus om de waarden te filteren op basis van kleur voordat LARGE wordt uitgevoerd. Dit doen we met de uitdrukking:
(color=E5)
Wat resulteert in een array van WAAR / ONWAAR waarden. Tijdens de vermenigvuldiging worden deze waarden omgezet in enen en nullen:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Het uiteindelijke resultaat is dus dat alleen waarden die zijn gekoppeld aan de kleur "rood" de bewerking overleven:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
en de andere waarden worden naar nul gedwongen.
Opmerking: deze formule verwerkt geen tekst in het waardebereik. Zie hieronder.
Omgaan met tekst in waarden
Als u ergens in de waardebereiken tekst heeft, genereert de LARGE-functie een #VALUE-fout en stopt de formule met werken.
Om tekst in het waardebereik af te handelen, kunt u de IFERROR-functie als volgt toevoegen:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Hier vangen we fouten op van LARGE die worden veroorzaakt door tekstwaarden en vervangen door nul. Als u IF binnen LARGE gebruikt, moet de formule worden ingevoerd met control + shift + enter, dus schakelen we over naar SOM in plaats van SOMPRODUCT.
Opmerking: ik kwam deze formule tegen die werd gepost door de geweldige Barry Houdini op stackoverflow.
