Excel indbygget datasortering er fantastisk, men det er ikke dynamisk. Hvis du sorterer data og derefter tilføjer data til dem, skal du sortere dem igen.
Sorter data i alfabetisk rækkefølge
I dette indlæg vil jeg vise dig forskellige måder at sortere data i alfabetisk rækkefølge ved hjælp af formler. Det betyder, at du kan tilføje data, og det vil automatisk sortere dem for dig.
Når dataene alle er tekst uden dubletter
Antag, at du har en data som vist herunder:
I dette eksempel er alle data i tekstformat (ingen tal, emner eller dubletter). For at sortere dette vil jeg bruge en hjælperkolonne. I kolonnen ud for dataene skal du bruge følgende COUNTIF -formel:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Denne formel sammenligner en tekstværdi med alle de andre tekstværdier og returnerer dens relative rang. For eksempel returnerer den i celle B2 8, da der er 8 tekstværdier, der er lavere end eller lig med teksten 'US' (alfabetisk rækkefølge).
Brug nu følgende kombination af INDEX, MATCH og ROWS -funktioner til at sortere værdierne:
= INDEX ($ A $ 2: $ A $ 9, MATCH (Rækker ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Denne formel udtrækker simpelthen navnene i alfabetisk rækkefølge. I den første celle (C2) leder den efter det landnavn, der har det laveste tal (Australien har 1). I den anden celle returnerer den Canada (som har tallet 2) og så videre …
Allergisk over for hjælpekolonner ??
Her er en formel, der vil gøre det samme uden hjælperkolonnen.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Dette er en matrixformel, så brug Ctrl + Skift + Enter i stedet for Enter.
Jeg lader dig afkode.
Prøv det selv … Download eksempelfil
Denne formel fungerer godt, hvis du har tekst eller alfanumeriske værdier.
Men det fejler sørgeligt, hvis:
- Du har dubletter i dataene (prøv at sætte USA to gange).
- Der er blanke i dataene.
- Du har en blanding af tal og tekst (prøv at sætte 123 i en af cellerne).
Når data er en blanding af tal, tekst, dubletter og emner
Nu er denne lidt vanskelig. Jeg vil bruge 4 hjælperkolonner til at vise dig, hvordan det fungerer (og derefter give dig en enorm formel, der gør det uden hjælperkolonnerne). Antag, at du har en data som vist herunder:
Du kan se, at der er dublerede værdier, blank og tal. Så jeg vil bruge hjælperkolonner til at behandle hvert af disse spørgsmål.
Hjælperkolonne 1
Indtast følgende COUNTIF -formel i hjælperkolonne 1
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Denne formel gør følgende:
- Det returnerer 0 for emner.
- I tilfælde af dubletter returnerer det samme nummer.
- Tekst og tal behandles parallelt, og denne formel returnerer det samme tal for tekst og tal (f.eks. 123 og Indien får begge 1).
Hjælperkolonne 2
Indtast følgende IS -funktion i hjælperkolonne 2:
=-ISNUMBER (A2)
Hjælperkolonne 3
Indtast følgende formel i hjælperkolonne 3:
=-ISBLANK (A2)
Hjælperkolonne 4
Indtast følgende formel i Hjælperkolonne 4
= HVIS (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Ideen til denne formel er at adskille emner, tal og tekstværdier.
- Hvis cellen er tom, returnerer den værdien i celle B2 (som altid ville være 0) og tilføjer værdien i celle D10. I en nøddeskal returnerer det det samlede antal tomme celler i dataene
- Hvis cellen er en numerisk værdi, returnerer den den sammenlignende rang og tilføjer det samlede antal emner. For eksempel returnerer den for 123 2 (1 er rangen 123 i dataene, og der er 1 tom celle)
- Hvis det er tekst, returnerer den den sammenlignende rang og tilføjer det samlede antal numeriske værdier og emner. For eksempel tilføjer det i Indien tekstens sammenlignende rang i tekst (som er 1) og tilføjer antallet af tomme celler og antallet af numeriske værdier.
Slutresultat - Sorterede data
Nu vil vi bruge disse hjælperkolonner til at få den sorterede liste. Her er formlen:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMÅ ($ E $ 2: $ E $ 9, RÆKER ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Denne sorteringsmetode bliver nu fjolsikker. Jeg har vist dig metoden for 8 varer, men du kan udvide den til så mange emner, du vil.
Prøv det selv … Download eksempelfil
En formel til at sortere det hele (uden hjælpekolonner)
Hvis du kan håndtere ekstreme formler, er her en alt-i-en-formel, der vil sortere data i alfabetisk rækkefølge (uden hjælpekolonne).
Her er formlen:
= 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)))), ROWS ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), IKKE ($ 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))), 0)), "")
Indtast denne formel i en celle, og træk den ned for at få den sorterede liste. Da dette også er en matrixformel, skal du bruge Ctrl + Skift + Enter i stedet for Enter.
Denne formel har praktisk virkelighed. Hvad synes du? Jeg ville elske at lære af dig. Efterlad dine fodspor i kommentarfeltet!
