Generieke formule
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Samenvatting
Om gegevens dynamisch te sorteren met zowel cijfers als tekst in alfabetische volgorde, kunt u een formule gebruiken om een numerieke rangorde in een hulpkolom te genereren, en vervolgens INDEX en MATCH gebruiken om waarden weer te geven op basis van rangorde. In het getoonde voorbeeld is de formule in C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
waarbij "data" het benoemde bereik B5: B13 is.
Uitleg
Deze formule genereert eerst een rangwaarde met behulp van een uitdrukking op basis van AANTAL.ALS:
=COUNTIF(data,"<="&B5)
die hier in meer detail wordt uitgelegd. Als de gegevens alle tekstwaarden of alle numerieke waarden bevatten, is de rangorde correct. Als de gegevens echter zowel tekst als cijfers bevatten, moeten we de rangschikking van alle tekstwaarden "verschuiven" om rekening te houden met de numerieke waarden. Dit doe je met het tweede deel van de formule hier:
+(COUNT(data)*ISTEXT(B7))
Hier gebruiken we de COUNT-functie om een telling van numerieke waarden in de gegevens te krijgen, en vervolgens het resultaat te vermenigvuldigen met het logische resultaat van ISTEXT, dat test of de waarde tekst is en WAAR of ONWAAR retourneert. Dit annuleert effectief het COUNT-resultaat wanneer we werken met een getal in de huidige rij.
Omgaan met duplicaten
Als gegevens duplicaten bevatten, kan de formule worden gewijzigd zoals hieronder wordt weergegeven om een opeenvolgende rangorde toe te wijzen aan waarden die meer dan eens voorkomen:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Deze versie past de logica van de initiële AANTAL.ALS-functie aan en voegt nog een AANTAL.ALS toe met een uitbreidende verwijzing om duplicaten te verhogen.
Gesorteerde waarden weergeven
Om gesorteerde waarden in alfabetische volgorde op te halen en weer te geven met behulp van de berekende rangwaarde, bevat E5 de volgende INDEX- en MATCH-formule:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
waar "data" het benoemde bereik B5: B13 is, en "rang" het benoemde bereik C5: C13 is.
Bekijk het voorbeeld hier voor meer informatie over hoe deze formule werkt.
Omgaan met lege plekken
Lege cellen genereren een rangschikking van nul. Ervan uitgaande dat u lege cellen wilt negeren, werkt dit prima omdat de INDEX- en MATCH-formule hierboven begint bij 1. U ziet echter # N / A-fouten aan het einde van gesorteerde waarden, één voor elke lege cel. Een gemakkelijke manier om hiermee om te gaan, is door de INDEX- en MATCH-formule als volgt in IFERROR in te pakken:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
