Generisk formel
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Sammendrag
For å dynamisk sortere data med både tall og tekst i alfabetisk rekkefølge, kan du bruke en formel til å generere en numerisk rangering i en hjelpekolonne, og deretter bruke INDEX og MATCH for å vise verdier basert på rangering. I eksemplet vist er formelen i C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
der "data" er det navngitte området B5: B13.
Forklaring
Denne formelen genererer først en rangverdi ved hjelp av et uttrykk basert på COUNTIF:
=COUNTIF(data,"<="&B5)
som er forklart mer detaljert her. Hvis dataene inneholder alle tekstverdier, eller alle numeriske verdier, vil rangeringen være riktig. Imidlertid, hvis dataene inneholder både tekst og tall, må vi "skifte" rangeringen av alle tekstverdier for å ta hensyn til de numeriske verdiene. Dette gjøres med den andre delen av formelen her:
+(COUNT(data)*ISTEXT(B7))
Her bruker vi COUNT-funksjonen for å få et antall numeriske verdier i dataene, og multipliser deretter resultatet med det logiske resultatet av ISTEXT, som tester om verdien er tekst og returnerer enten SANT eller FALSK. Dette opphever effektivt COUNT-resultatet når vi jobber med et tall i den nåværende raden.
Håndtering av duplikater
Hvis data inneholder duplikater, kan formelen endres som vist nedenfor for å tilordne en sekvensiell rangering til verdier som vises mer enn en gang:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Denne versjonen justerer logikken til den opprinnelige COUNTIF-funksjonen, og legger til en annen COUNTIF med en utvidende referanse til økende duplikater.
Vis sorterte verdier
For å hente og vise verdier sorterte verdier i alfabetisk rekkefølge ved hjelp av den beregnede rangverdien, inneholder E5 følgende INDEKS- og MATCH-formel:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
der "data" er det navngitte området B5: B13, og "rang" er det navngitte området C5: C13.
For mer informasjon om hvordan denne formelen fungerer, se eksemplet her.
Håndterer blanke
Tomme celler genererer en rangering på null. Forutsatt at du vil ignorere tomme celler, fungerer dette bra fordi INDEKS- og MATCH-formelen ovenfor begynner med 1. Du vil imidlertid se # N / A-feil på slutten av sorterte verdier, en for hver tomme celle. En enkel måte å håndtere dette på er å pakke INDEX- og MATCH-formelen i IFERROR slik:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
