De ingebouwde gegevenssortering van Excel is geweldig, maar niet dynamisch. Als u gegevens sorteert en er vervolgens gegevens aan toevoegt, moet u deze opnieuw sorteren.
Sorteer gegevens in alfabetische volgorde
In dit bericht laat ik je verschillende manieren zien om gegevens in alfabetische volgorde te sorteren met behulp van formules. Dit betekent dat u gegevens kunt toevoegen en deze automatisch voor u zal sorteren.
Wanneer de gegevens allemaal tekst zijn zonder duplicaten
Stel dat u een gegevens heeft zoals hieronder weergegeven:
In dit voorbeeld zijn alle gegevens in tekstformaat (geen cijfers, spaties of duplicaten). Om dit te sorteren, zal ik een hulpkolom gebruiken. Gebruik in de kolom naast de gegevens de volgende AANTAL.ALS-formule:
=AANTAL.ALS($A$2:$A$9,"<="&A2)
Deze formule vergelijkt een tekstwaarde met alle andere tekstwaarden en retourneert de relatieve rangorde. In cel B2 wordt bijvoorbeeld 8 geretourneerd, omdat er 8 tekstwaarden zijn die lager zijn dan of gelijk zijn aan de tekst 'VS' (alfabetische volgorde).
Om de waarden nu te sorteren, gebruikt u de volgende combinatie van INDEX-, MATCH- en ROWS-functies:
=INDEX($A$2:$A$9,MATCH(ROWS($B$2:B2),$B$2:$B$9,0))
Deze formule extraheert eenvoudig de namen in alfabetische volgorde. In de eerste cel (C2) wordt gezocht naar de landnaam met het laagste nummer (Australië heeft 1). In de tweede cel retourneert het Canada (met nummer 2) enzovoort …
Allergisch voor helperkolommen??
Hier is een formule die hetzelfde zal doen zonder de helperkolom.
=INDEX($A$2:$A$9,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),0))
Dit is een matrixformule, dus gebruik Control + Shift + Enter in plaats van Enter.
Ik laat het aan jou over om te decoderen.
Probeer het zelf… Voorbeeldbestand downloaden
Deze formule werkt goed als u tekst- of alfanumerieke waarden hebt.
Maar het mislukt jammerlijk als:
- U heeft dubbele gegevens in de gegevens (probeer twee keer VS in te voeren).
- Er zijn lege plekken in de gegevens.
- Je hebt een combinatie van cijfers en tekst (probeer 123 in een van de cellen te plaatsen).
Wanneer gegevens een combinatie zijn van cijfers, tekst, duplicaten en blanco's
Nu is deze een beetje lastig. Ik zal 4 helperkolommen gebruiken om je te laten zien hoe het werkt (en je dan een enorme formule geven die het zal doen zonder de helperkolommen). Stel dat u een gegevens heeft zoals hieronder weergegeven:
U kunt zien dat er dubbele waarden, blanco en getallen zijn. Dus ik zal helperkolommen gebruiken om elk van deze problemen aan te pakken.
Helper Kolom 1
Voer de volgende AANTAL.ALS-formule in Helperkolom 1 in
=AANTAL.ALS($A$2:$A$9,"<="&A2)
Deze formule doet het volgende:
- Het retourneert 0 voor spaties.
- In het geval van duplicaten wordt hetzelfde getal geretourneerd.
- Tekst en getallen worden parallel verwerkt en deze formule retourneert hetzelfde getal voor tekst en getal (bijvoorbeeld 123 en India krijgen beide 1).
Helperkolom 2
Voer de volgende IS-functie in Helperkolom 2 in:
=--ISGETAL(A2)
Helperkolom 3
Voer de volgende formule in Helperkolom 3 in:
=--ISBLANK (A2)
Helperkolom 4
Voer de volgende formule in Helperkolom 4 in
=ALS(ISGETAL(A2),B2,ALS(ISBLANK(A2),B2,B2+$C$10))+$D$10
Het idee voor deze formule is om spaties, getallen en tekstwaarden te scheiden.
- Als de cel leeg is, wordt de waarde in cel B2 geretourneerd (die altijd 0 zou zijn) en wordt de waarde in cel D10 opgeteld. In een notendop, het zal het totale aantal lege cellen in de gegevens retourneren
- Als de cel een numerieke waarde is, retourneert deze de vergelijkende rang en wordt het totale aantal blanco's opgeteld. Voor 123 retourneert het bijvoorbeeld 2 (1 is de rangorde van 123 in de gegevens en er is 1 lege cel)
- Als het tekst is, retourneert het de vergelijkende rang en voegt het het totale aantal numerieke waarden en spaties toe. Voor India voegt het bijvoorbeeld de vergelijkende positie van de tekst in tekst toe (dit is 1) en voegt het het aantal lege cellen en het aantal numerieke waarden toe.
Eindresultaat - Gesorteerde gegevens
Nu zullen we deze hulpkolommen gebruiken om de gesorteerde lijst te krijgen. Hier is de formule:
=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL($E$2:$E$9,ROWS($F$2:F2)+$D$10),$E$2:$E$9,0)) "")
Deze manier van sorteren wordt nu onfeilbaar. Ik heb je de methode voor 8 items laten zien, maar je kunt het uitbreiden naar zoveel items als je wilt.
Probeer het zelf… Voorbeeldbestand downloaden
Eén formule om alles te sorteren (zonder helperkolommen)
Als je extreme formules aankunt, is hier een alles-in-één formule die gegevens in alfabetische volgorde sorteert (zonder hulpkolom).
Hier is de formule:
=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(NOT($A$2:$A$9="")*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2: $A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(--ISNUMBER($A $2:$A$9))),RIJEN($A$2:A2)+SUM(--ISBLANK($A$2:$A$9))),NIET($A$2:$A$9="")*IF (ISNUMMER ($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<=" &$A$2:$A$9)+SUM(--ISNUMBER($A$2:$A$9))),0)),"")
Voer deze formule in een cel in en sleep deze naar beneden om de gesorteerde lijst te krijgen. Gebruik ook, aangezien dit een matrixformule is, Control + Shift + Enter in plaats van Enter.
Deze formule heeft een praktisch nut. Wat denk je? Ik leer graag van je. Laat je voetafdruk achter in de comments!
