Generisk formel
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Sammanfattning
För att dynamiskt sortera data med både siffror och text i alfabetisk ordning kan du använda en formel för att generera en numerisk rang i en hjälpkolumn och sedan använda INDEX och MATCH för att visa värden baserat på rang. I exemplet som visas är formeln i C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
där "data" är det namngivna området B5: B13.
Förklaring
Denna formel genererar först ett rangvärde med hjälp av ett uttryck baserat på COUNTIF:
=COUNTIF(data,"<="&B5)
vilket förklaras mer detaljerat här. Om data innehåller alla textvärden eller alla numeriska värden kommer rankningen att vara korrekt. Men om data innehåller både text och siffror, måste vi "flytta" rangordningen för alla textvärden för att ta hänsyn till de numeriska värdena. Detta görs med den andra delen av formeln här:
+(COUNT(data)*ISTEXT(B7))
Här använder vi COUNT-funktionen för att få ett antal numeriska värden i data och multiplicerar sedan resultatet med det logiska resultatet av ISTEXT, som testar om värdet är text och returnerar antingen SANT eller FALSK. Detta avbryter effektivt COUNT-resultatet när vi arbetar med ett nummer i den aktuella raden.
Hantering av dubbletter
Om data innehåller dubbletter kan formeln ändras enligt nedan för att tilldela en sekventiell rangordning till värden som visas mer än en gång:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Den här versionen justerar logiken för den ursprungliga COUNTIF-funktionen och lägger till ytterligare COUNTIF med en expanderande referens till stegvisa dubbletter.
Visa sorterade värden
För att hämta och visa värden sorterade i alfabetisk ordning med hjälp av det beräknade rangvärdet innehåller E5 följande INDEX- och MATCH-formel:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
där "data" är det namngivna området B5: B13, och "rang" är det namngivna området C5: C13.
Mer information om hur denna formel fungerar finns i exemplet här.
Hantera ämnen
Tomma celler genererar noll. Förutsatt att du vill ignorera tomma celler fungerar det bra eftersom INDEX- och MATCH-formeln ovan börjar vid 1. Du kommer dock att se # N / A-fel i slutet av sorterade värden, ett för varje tom cell. Ett enkelt sätt att hantera detta är att sätta INDEX- och MATCH-formeln så här i IFERROR:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
