Excel-handledning: Hur får man n: e värden med SMALL och LARGE

Hur får man n: e värden med SMALL och LARGE

I den här videon tittar vi på hur man beräknar de n: a minsta eller största värdena i ett intervall med hjälp av SMALL- och STOR-funktionerna. Detta skulle till exempel vara 1: a, 2: a och 3: e minsta eller största värden.

I det här första bladet har vi en lista över studenter med 5 testresultat. Låter oss använda LARGE-funktionen för att extrahera de bästa tre poängen för varje elev.

Använd LARGE för att få toppvärden. Den STORA funktionen behöver en matris och något som kallas "k", som du kan tänka på som "nth".

Så i det här fallet pekar jag bara på de 5 poängen för array och anger 1 för K. Detta ger oss toppresultatet för den första eleven.

För att kopiera formeln måste jag först låsa kolumnreferenser för intervallet så att de inte ändras.

Sedan kan jag kopiera formeln och komma tillbaka för att ändra k efter behov.

När jag kopierar alla 3 formlerna får vi bästa poäng för varje elev i listan.

Så, låt mig visa dig ett par tricks formelhantering k i denna situation …

Eftersom vi redan har ett nummer i kolumnrubriken kan jag bara använda det i formeln … låser rad 5.

Det låter mig kopiera samma formel överallt.

Jag kunde också använda COLUMN-funktionen för att dynamiskt beräkna k. KOLONN visar oss att vi är i kolumnerna 9,10 och 11.

Så genom att subtrahera 1 kan vi generera siffrorna 1, 2 och 3.

Då kan jag använda den här konstruktionen direkt i formeln överallt.

Låt oss nu titta på SMALL-funktionen.

Här har vi en lista med tävlingsresultat, och jag vill ta ut de bästa tre gånger totalt. De bästa tiderna är de kortaste tiderna, så det här är ett jobb för funktionen SMALL.

Jag har redan fått en rang i kolumn E med hjälp av rangfunktionen, som jag lämnar som referens

Jag har också nämnt intervall för "namn" och "tider" för att göra formler lättare att läsa.

Små fungerar precis som STORA, så jag använder "tider" för array, sedan plockar jag bara upp k från kolumnen till vänster.

När jag kopierar formeln ner får vi de tre kortaste eller bästa tiderna.

Så du kanske undrar hur vi kan få ett namn för var och en av dessa tider?

För det, förutsatt att det inte finns några dubbletter, kan vi använda INDEX och MATCH.

MATCH ger oss positionen bort varje gång i listan.

Då kan jag bara ta den formeln och använda den direkt inuti INDEX för att hämta namnet.