Sommen.als/4 hoogste scores

Bekijk bijlage Voorbeeldbestand Helpmij.xlsxHallo,

Ik heb een Excel werkblad met in tabblad 1 in kolom A unieke waarden van bedrijven, in kolom B het jaar en in kolom C scores. Een bedrijf kan in een bepaald jaar meerdere scores hebben (allemaal op een aparte regel). In tabblad 2 heb ik in kolom A de unieke waarden van elk bedrijf, met in de kolom B het jaar. Hetzelfde bedrijf kan op meerdere regels voorkomen, maar dan elke keer met een ander jaar in kolom B. Nu wil ik in kolom C van tabblad 2 per regel de scores van de waarnemingen in tabblad 1 optellen, maar als er in tabblad 1 voor een specifiek bedrijf in een specifiek jaar meer dan 4 scores zijn, dan alleen de 4 hoogste scores. Dus als bedrijf A in 2015 de volgende scores heeft: 12, 9, 9, 8, 6 dan wil ik dat in tab 2 een score komt van 38.

Ik heb zelf al verschillende dingen geprobeerd, maar ik kom er niet uit!

maar ik kom er niet uit!

Klik om te vergroten...

Wij hier ook niet zonder representatief (excel) voorbeeldbestand!

graag een voorbeeld bestandje

Ik heb een voorbeeld bestand toegevoegd!

Wat dacht je van deze:
Let op: dit is een matrixfunctie, d.w.z. afsluiten met Control+Shift+Enter na invoeren/wijzigen.

Hey AlexCEL,

Dit lijkt te werken. Hartelijk dank!

Ik begrijp de formule niet helemaal, dus voor de zekerheid nog twee kleine vragen:
1. Houdt de formule er rekening mee als het het 4e en 5e getal allebei even groot zijn, bijvoorbeeld allebei 8? Dus telt hij dan één keer 8 en niet twee keer?
2. Als ik de formule naar beneden trek dan blijft het laatste deel van de formule altijd naar dezelfde rijen verwijzen (Rij($1:$4)). Dit lijkt goed te gaan, maar klopt dat?

Kleine uitleg:

• een matrixfunctie heeft niet i.t.t. een gewone formule een reeks als uitkomst, niet een enkele waarde.
• de eerste ALS vergelijkt de bedrijfsnummers, als ze overeenkomen zet deze functie een 1 in de reeks, zo niet een 0. Uitkomst is dan zoiets als 1,1,0,0,1,1,0,0,... etc.
• Idem voor de tweede ALS die kijkt naar jaartallen.
• Het product van deze 2 ALS functies geeft geeft weer een reeks, waarin een 1 betekend dat aan beide voorwaarden wordt voldaan. Door deze te vermenigvuldigen met de waarde uit kolom C krijg je dus een reeks met scores die voldoen aan bijbehorende randvoorwaarden.
• De functie GROOTSTE kan de grootste (k=RIJ(1)=1), de op 1-na grootste (k=RIJ(2)=2), de op 2-na grootste (k=RIJ(3)=3) en de op 3-na grootste (k=RIJ(4)=4) uit de reeks halen. Ook dit is in de matrixvorm gegoten, dus excel evalueert deze RIJ-functies één voor één en bepaalt de 4 grootste waardes. De SOM-functie telt deze nog op.

Antwoorden op je vragen :
1. Probeer het eens... maar ja, zou goed moeten gaan.
2. Ja dat klopt, omdat de RIJ()-functie dus input is voor de functie GROOTSTE.