Excel-zelfstudie: n-de waarden krijgen met SMALL en LARGE

Hoe u de n-de waarden krijgt met SMALL en LARGE

In deze video bekijken we hoe we de n-de kleinste of grootste waarden in een bereik kunnen berekenen met de functies SMALL en LARGE. Dit zijn bijvoorbeeld de 1e, 2e en 3e kleinste of grootste waarde.

Op dit eerste blad hebben we een lijst met studenten met 5 testscores. Laten we de LARGE-functie gebruiken om de beste 3 scores voor elke student te extraheren.

Gebruik LARGE om topwaarden te krijgen. De LARGE-functie heeft een array nodig en iets genaamd "k", wat je kunt zien als "nth".

Dus in dit geval wijs ik gewoon naar de 5 scores voor array en voer ik 1 in voor K. Dit geeft ons de hoogste score voor de eerste student.

Om de formule nu te kopiëren, moet ik eerst de kolomverwijzingen voor het bereik vergrendelen, zodat ze niet veranderen.

Dan kan ik de formule kopiëren en zo nodig terugkomen om k te wijzigen.

Als ik alle 3 de formules kopieer, krijgen we de beste score voor elke student in de lijst.

Dus, laat me je een paar trucs laten zien met het omgaan met formules k in deze situatie …

Omdat we al een nummer in de kolomkop hebben, zou ik dat gewoon kunnen gebruiken in de formule … rij 5 vergrendelen.

Dat laat me overal dezelfde formule kopiëren.

Ik zou ook de COLUMN-functie kunnen gebruiken om k dynamisch te berekenen. COLUMN laat ons zien dat we in de kolommen 9,10 en 11 staan.

Dus door 1 af te trekken, kunnen we de getallen 1, 2 en 3 genereren.

Dan kan ik deze constructie overal direct in de formule gebruiken.

Laten we nu eens kijken naar de SMALL-functie.

Hier hebben we een lijst met raceresultaten en ik wil de beste 3 keer overall halen. De beste tijden zijn de kortste tijden, dus dit is een klus voor de functie KLEIN.

Ik heb al een rang in kolom E met behulp van de rangschikkingsfunctie, die ik ter referentie zal achterlaten

Ik heb ook bereiken genoemd voor "namen" en "tijden", om formules gemakkelijker leesbaar te maken.

Klein werkt net als LARGE, dus ik gebruik "tijden" voor array, en pak dan k uit de kolom aan de linkerkant.

Als ik de formule kopieer, krijgen we de 3 kortste of beste tijden.

Dus je vraagt je misschien af hoe we voor elk van deze tijden een naam kunnen krijgen?

Daarvoor kunnen we, ervan uitgaande dat er geen duplicaten zijn, INDEX en MATCH gebruiken.

WEDSTRIJD geeft ons de positie elke keer in de lijst.

Dan kan ik die formule gewoon nemen en deze rechtstreeks in INDEX gebruiken om de naam op te halen.