Rangordning

Rangordna tal kan ibland underlätta att analysera data. I tabellen nedan visas några grundläggande möjligheter för att rangordna tal inbördes.

 

Den första rangordningen är fallande och utan att rangordna tal som förekommer fler gånger. Formeln utgörs här av:

För att erhålla en fallande rangordning som beaktar tal som förekommer flera gånger måste en annorlunda formel användas:


För att rangordna stigande och utan att rangordna tal som förekommer flera gånger kan följande formeln användas:

 

För att rangordna tal som förekommer flera gånger kan följande formeln användas vid stigande rangordning:

 

Vill man kasta om rangordningen för tal som förekommer flera gånger kan det ske mha formeln:


Vill man skapa en formel för hela listan kan det lösas mha en matrisformel enligt nedanstående tabell:

 



Det kan ibland vara önskvärt att låta XL rangordna värden, som förekommer flera gånger, sekventiellt. I tabellen nedan demonstreras tre (komplexa) formeltekniska lösningar för det.

 

 

Rangordningen i "Rang 1" bygger på följande matrisformel:

Rangordningen i "Rang 2" sker i stigande ordning och utgår från matrisformeln:


Alternativt kan följande enklare formel användas:

 

Rangordningen i "Rang 3" är i fallande ordning och den formeltekniska lösning är:

Skillnaden mellan "Rang 2" och Rang 3" är att uttrycket ">" är ersatt i "Rang 3 " av uttrycket "<" !

Vill man rangordna rader istället för kolumner ersätts RAD-funktionen i formlerna ovan med KOLUMN-funktionen.

 

För de som håller på med diverse spel i XL kan följande vara av intresse. 

 

Rankningen i tabellen bygger på grundläggande formler enligt ovan. För att erhålla namnen enligt fallande rangordning används följande formel:

 

För att få fram en lista över namn stigande används följande formel:

 

Antag att du har en lista med enheter och deras utfall för en period. Du vill nu ha reda på hur dessa enheter rangordnas sinsemellan. Tabellen nedan visar förutsättningarna.

 

 

Lösningen är (tyvärr) en relativ komplex formel. Utöver att den kan rangordna enligt önskemål så kan den även hantera när t ex två enheter har samma resultatutfall.

 

Genom att lägga till 0,5 och utföra en multiplikation med 10 kan formeln hantera lika resultatutfall. Cellreferensen C2 i formeln ger rangordningstalet.

Se också Dynamisk sortering.

Vill man erhålla automatisk/dynamisk fallande rangordning för textlistor så kan det lösa mha följande formel:


Namnet "Område" avser det cellområde som håller den ursprungliga listan med text. Då det är en matrisformel så ska cellområdet som ska hålla den nya sorterade listan vara lika stort som den ursprungliga.

 

Vill man ha stigande sortering så ska MINSTA-funktionen i formeln ersättas med STÖRSTA-funktionen.

Se också Matriskalkylering.

Om man har en lista som består av två kolumner, namn och resultat, och man vill ha dynamisk sortering. Hur gör man då?

I tabellen nedan visas både den osorterade listan och den dynamiskt sorterade listan.

 

Hm, hur ser den tekniska lösningen ut då? Jo, för att skapa denna dynamiska lösning krävs att man markerar ett lika stort område som den ursprungliga listan omfattar och skriver in följande monstruösa matrisformel:

 

Namnet Tabell i formeln avser cellområdet A2:B8. Detta exempel finns ej att tillgå för hämtning.

Se också Dynamisk sortering.

Rangordna formler

Om vi antar att vi har formler i en lista, vilka vi vill rangordna, kan det inte ske med de inbyggda sorteringsfunktionerna.

För det krävs ett annat förfarande, vilket detta tips belyser.

Exemplet har sin utgångspunkt i följande uppställning:


I B-kolumnen i tabellen återfinns underliggande formler, vars värden ska rangordnas. Den innerhåller enkla SUMMA-funktioner, =SUMMA(A2:A3), vilken har kopierats nedåt i kolumnen.

Rangordningen sker mha en matrisformel och anges i lika många celler som antal formler i listan - Här i området C2:C11 för stigande sortering - samtidigt:

För stigande sortering används följande matrisformel:


För fallande sortering används matrisformeln:

 

Korrekt rangordning?

Här belyses hur vi kan lösa ett rangordningsproblem som kan uppstå när vi ska rangordna utifrån flera kolumner.

Följande tabell visar förutsättningarna:

 

För att erhålla de största värdena används följande formel som kopieras nedåt i kolumn E:

 

Om vi använder oss av samma formel för att erhålla avdelningsnamnen uppstår ett problem. Har vi, som i exemplet, flera namn med samma värden (de blå markerade raderna) erhålls inte alla namnen utan bara det första - "AA".

Följande matrisformel löser dock problemet och som kopieras nedåt i D-kolumnen:


Vill vi istället ha en omvänd rangordning räcker det med att sortera C-kolumnen i fallande ordning.


Rangordna kolumner

I detta tips visas hur vi kan rangordna värden i kolumner mha av en matrisformel, vilken knyter an till tipset ovan.

Exemplet i sin helhet:

 

För att erhålla poäng används följande formel, som skapas och kopieras nedåt i kolumn I:

 

För att erhålla vem som har de tre högsta poängen används följande matrisformel, som skapas och som därefter kopieras nedåt i kolumn H:

 

Rangordna inom subgrupper

Här belyses hur vi mha formler kan rangordna värden inom enskilda subgrupper. Den tekniska lösningen är såpass generell till sin karaktär att det inte finns någon begränsning när det gäller villkor.
 

Dessutom tillåter den att beräkningar av variabler kan göras direkt vid rangordningen, vilket inte den inbyggda funktionen RANG tillåter.

Exemplet i sin helhet visas i följande bild:

 

Följande namn används i exemplet:

 

I det första exemplet (Rang 1) sker rangordning för samtliga poster utan hänsyn till tillhörighet. Här används standardfunktionen RANG:


I det andra exemplet (Rang 2) sker rangordning utifrån subgruppen Månad:

 

I det tredje och sista exemplet (Rang 3) sker rangordning utifrån subgrupperna Avdelning och Månad

 

De två sistnämnda exemplen visar att vi kan bygga ut formeln nästan oändligt, dvs vi kan arbeta med stora datamängder och få den rangordnad mha denna formel!

 

Rangordna inom intervall

Här belyses hur vi kan bestämma inom vilket intervall en rangordning ska ske för i listor och hur denna rangordning ska numreras.

Bilden nedan visar exemplet i sin helhet:

 

I cellerna F1 och F2 anges lägsta respektive högsta värden som ska ingå i rangordningen.

I B-kolumnen används följande formel för att få fram vilka tal som kvalificerar sig för att ingå i rangordningen:

 

i C-kolumnen sker själva rangordningen mha följande formel:


Vill vi inte använda oss av flera interrimformler så kan vi använda oss av följande formel (och som visas i D-kolumnen ovan):

 

Vill vi ha en annan numrering för rangordningen ändras värdet 10 i formeln ovan.
 

Rangordna färger

Här demonstreras två tekniker för att rangordna dels utifrån cellers fyllningsfärg och dels på basis av teckenfärg. Lösningarna baseras på XL4-makron, vilket du kan läsa mer om här»

Bilden nedan visar det färdiga resultatet av att rangordna utifrån fyllningsfärg:

 

 

Skapa ett namn, i exemplet används "Färg", och referera till följande formel:


"-1" i formeln anger att formeln ska hämta fyllningsfärgen från cellen till vänster. "*1" har lagts till för att omräkning ska ske vid förändring av fyllningsfärgen. Ange namnet, =Färg, och kopiera den nedåt i kolumnen.


Bilden nedan visar det färdiga resultatet av att rangordna utifrån teckenfärg:
 

 

Skapa ett namn, i exemplet används Fontfärg, och referera till följande formel:

 

Ange namnet, =Fontfärg, och kopiera den nedåt i kolumnen.

OBS! Vid ändring av fyllningsfärg eller teckenfärg måste omräkning av arbetsbladet ske. Enklast är att trycka ned F9-knappen.