Generisk formel
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Resumé
For at dynamisk sortere data med både tal og tekst i alfabetisk rækkefølge kan du bruge en formel til at generere en numerisk rang i en hjælpekolonne og derefter bruge INDEX og MATCH til at vise værdier baseret på rang. I det viste eksempel er formlen i C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
hvor "data" er det navngivne område B5: B13.
Forklaring
Denne formel genererer først en rangværdi ved hjælp af et udtryk baseret på COUNTIF:
=COUNTIF(data,"<="&B5)
hvilket forklares mere detaljeret her. Hvis dataene indeholder alle tekstværdier eller alle numeriske værdier, vil rangen være korrekt. Men hvis dataene inkluderer både tekst og tal, er vi nødt til at "skifte" rangeringen af alle tekstværdier for at tage højde for de numeriske værdier. Dette gøres med den anden del af formlen her:
+(COUNT(data)*ISTEXT(B7))
Her bruger vi COUNT-funktionen til at få et antal numeriske værdier i dataene, gang derefter resultatet med det logiske resultat af ISTEXT, som tester om værdien er tekst og returnerer enten SAND eller FALSK. Dette annullerer effektivt COUNT-resultatet, når vi arbejder med et nummer i den aktuelle række.
Håndtering af dubletter
Hvis data indeholder dubletter, kan formlen ændres som vist nedenfor for at tildele en sekventiel rang til værdier, der vises mere end én gang:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Denne version tilpasser logikken for den oprindelige COUNTIF-funktion og tilføjer en anden COUNTIF med en udvidet reference til stigende duplikater.
Vis sorterede værdier
For at hente og vise værdier sorterede værdier i alfabetisk rækkefølge ved hjælp af den beregnede rangværdi, indeholder E5 følgende INDEX- og MATCH-formel:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
hvor "data" er det navngivne område B5: B13, og "rang" er det navngivne område C5: C13.
For mere information om, hvordan denne formel fungerer, se eksemplet her.
Håndtering af emner
Tomme celler genererer en rang på nul. Forudsat at du vil ignorere tomme celler, fungerer dette fint, fordi INDEX- og MATCH-formlen ovenfor begynder med 1. Du vil dog se # N / A-fejl i slutningen af sorterede værdier, en for hver tom celle. En nem måde at håndtere dette på er at indpakke INDEX- og MATCH-formlen i IFERROR således:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
