Generisk formel
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Sammanfattning
För att dynamiskt sortera data som bara innehåller numeriska värden kan du använda en hjälpkolumn och en formel som skapats med RANK- och COUNTIF-funktionerna. I exemplet som visas är formeln i D5:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
där "försäljning" är det namngivna intervallet C5: C11.
Förklaring
Obs: den här formeln är inställningen för en formel som kan extrahera och visa data med hjälp av en fördefinierad sorteringsordning i en hjälpkolumn. Ett exempel här.
Kärnan i denna formel är RANK-funktionen, som används för att generera en rang av försäljningsvärden, där det högsta antalet rankas som nummer 1:
=RANK(C5,sales)
Här använder RANK det angivna sortimentet "försäljning" (C5: C11) för enkelhetens skull. Som standard kommer RANK att tilldela 1 till det högsta värdet, 2 till det näst högsta värdet och så vidare. Detta fungerar perfekt så länge numeriska värden är unika. För att hantera numeriska värden som innehåller dubbletter måste vi dock använda COUNTIF-funktionen för att bryta band. Detta görs genom att lägga till resultatet av detta utdrag till värdet som RANK returnerar:
COUNTIF($C$5:C5,C5)-1
Observera att intervallet anges som en blandad referens som kommer att expandera när formeln kopieras ner i tabellen. Som skrivet kommer denna referens att inkludera den aktuella raden, så vi subtraherar 1 till "noll ut" den första förekomsten. Detta betyder att uttrycket returnerar noll för varje numeriskt värde tills en duplikat påträffas. Vid den andra instansen kommer uttrycket att returnera 1, vid det tredje exemplet kommer det att returnera 2, och så vidare. Detta bryter effektivt band och gör det möjligt för formeln att generera en sekventiell lista med nummer utan luckor.
När formeln är på plats kan data sorteras efter hjälpkolumnen. Det kan också hämtas med INDEX med hjälp av värdena i hjälpkolumnen.
Obs: Denna formel är anpassad från ett exempel i den utmärkta boken Control + Shift + Enter, av Mike Girvin.
