Excel innebygd datasortering er fantastisk, men den er ikke dynamisk. Hvis du sorterer data og deretter legger til data i dem, må du sortere dem igjen.
Sorter data i alfabetisk rekkefølge
I dette innlegget vil jeg vise deg forskjellige måter å sortere data i alfabetisk rekkefølge ved å bruke formler. Dette betyr at du kan legge til data, og det vil automatisk sortere det for deg.
Når dataene er alle tekst uten duplikater
Anta at du har data som vist nedenfor:
I dette eksemplet er alle dataene i tekstformat (ingen tall, mellomrom eller dubletter). For å sortere dette, vil jeg bruke en hjelperkolonne. I kolonnen ved siden av dataene bruker du følgende COUNTIF -formel:
= TELLING ($ A $ 2: $ A $ 9, "<=" og A2)
Denne formelen sammenligner en tekstverdi med alle andre tekstverdier og returnerer den relative rangeringen. For eksempel returnerer den i celle B2 8, da det er 8 tekstverdier som er lavere enn eller lik teksten ‘US’ (alfabetisk rekkefølge).
Nå for å sortere verdiene, bruk følgende kombinasjon av INDEX, MATCH og ROWS -funksjoner:
= INDEKS ($ A $ 2: $ A $ 9, MATCH (RADER ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Denne formelen trekker ganske enkelt ut navnene i alfabetisk rekkefølge. I den første cellen (C2) ser den etter landnavnet som har det laveste tallet (Australia har 1). I den andre cellen returnerer den Canada (som har tallet 2) og så videre …
Allergisk mot hjelperkolonner ??
Her er en formel som vil gjøre det samme uten hjelperkolonnen.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Dette er en matriseformel, så bruk Ctrl + Shift + Enter i stedet for Enter.
Jeg lar deg avkode.
Prøv det selv … Last ned eksempelfil
Denne formelen fungerer bra hvis du har tekst eller alfanumeriske verdier.
Men det mislykkes elendig hvis:
- Du har duplikater i dataene (prøv å sette USA to ganger).
- Det er blanke i dataene.
- Du har en blanding av tall og tekst (prøv å sette 123 i en av cellene).
Når data er en blanding av tall, tekst, dubletter og mellomrom
Nå er denne litt vanskelig. Jeg vil bruke fire hjelperkolonner for å vise deg hvordan det fungerer (og deretter gi deg en enorm formel som gjør det uten hjelperkolonnene). Anta at du har data som vist nedenfor:
Du kan se at det er like verdier, tomme og tall. Så jeg vil bruke hjelperkolonner for å ta opp hvert av disse problemene.
Hjelperkolonne 1
Skriv inn følgende COUNTIF -formel i hjelperkolonne 1
= TELLING ($ A $ 2: $ A $ 9, "<=" og A2)
Denne formelen gjør følgende:
- Den returnerer 0 for blanke.
- Når det gjelder duplikater, returnerer det samme nummer.
- Tekst og tall behandles parallelt, og denne formelen returnerer samme nummer for tekst og tall (for eksempel 123 og India får begge 1).
Hjelperkolonne 2
Skriv inn følgende IS -funksjon i hjelperkolonne 2:
=-ISNUMBER (A2)
Hjelperkolonne 3
Skriv inn følgende formel i hjelperkolonne 3:
=-ISBLANK (A2)
Hjelperkolonne 4
Skriv inn følgende formel i hjelperkolonne 4
= HVIS (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Ideen med denne formelen er å skille mellomrom, tall og tekstverdier.
- Hvis cellen er tom, returnerer den verdien i celle B2 (som alltid vil være 0) og legger til verdien i celle D10. I et nøtteskall vil det returnere det totale antallet tomme celler i dataene
- Hvis cellen er en numerisk verdi, vil den returnere den sammenlignende rangen og legge til det totale antallet emner. For eksempel returnerer den for 123 2 (1 er rangeringen 123 i dataene, og det er 1 tom celle)
- Hvis det er tekst, returnerer den den sammenlignende rangeringen og legger til det totale antallet numeriske verdier og tomrom. For eksempel, for India, legger den til tekstens komparative rangering i tekst (som er 1) og legger til antall tomme celler og antall numeriske verdier.
Sluttresultat - Sorterte data
Nå skal vi bruke disse hjelperkolonnene for å få den sorterte listen. Her er formelen:
= IFERROR (INDEKS ($ A $ 2: $ A $ 9, MATCH (LITEN ($ E $ 2: $ E $ 9, RADER ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Denne sorteringsmetoden blir nå idiotsikker. Jeg har vist deg metoden for åtte elementer, men du kan utvide den til så mange elementer du vil.
Prøv det selv … Last ned eksempelfil
En formel for å sortere alt (uten hjelperkolonner)
Hvis du kan håndtere ekstreme formler, her er en alt-i-ett-formel som vil sortere data i alfabetisk rekkefølge (uten hjelpekolonne).
Her er formelen:
= 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)), "")
Skriv inn denne formelen i en celle og dra den ned for å få den sorterte listen. Siden dette er en matriseformel, bruker du også Ctrl + Shift + Enter i stedet for Enter.
Denne formelen har virkelighet. Hva tror du? Jeg vil gjerne lære av deg. Legg igjen dine fotavtrykk i kommentarfeltet!
