Vend data i Excel - Omvendt rekkefølge av data i kolonne/rad

Noen ganger kan du ha behov for å snu dataene i Excel, dvs. å reversere rekkefølgen på dataene opp ned i et vertikalt datasett og fra venstre til høyre i et horisontalt datasett.

Hvis du tenker at det må være en innebygd funksjon for å gjøre dette i Excel, er jeg redd du vil bli skuffet.

Selv om det er flere måter du kan snu dataene i Excel, er det ingen innebygd funksjon. Men du kan enkelt gjøre dette ved å bruke et enkelt sorteringstrick, formler eller VBA.

I denne opplæringen vil jeg vise deg hvordan du snu dataene i rader, kolonner og tabeller i Excel.

Så la oss komme i gang!

Vend data ved å bruke SORT og hjelperkolonne

En av de enkleste måtene å reversere rekkefølgen på dataene i Excel er å bruke en hjelperkolonne og deretter bruke den hjelperkolonnen til å sortere dataene.

Vend dataene vertikalt (omvendt rekkefølge opp ned)

Anta at du har et datasett med navn i en kolonne som vist nedenfor, og du vil snu disse dataene:

Nedenfor er trinnene for å snu dataene vertikalt:

1. I den tilstøtende kolonnen skriver du inn ‘Helper’ som overskrift for kolonnen
2. I hjelpekolonnen skriver du inn en rekke tall (1,2,3 og så videre). Du kan bruke metodene som vises her for å gjøre dette raskt
3. Velg hele datasettet inkludert hjelperkolonnen
4. Klikk kategorien Data
5. Klikk på Sorter -ikonet
6. I dialogboksen Sorter velger du 'Hjelper' i rullegardinmenyen 'Sorter etter'
7. I rullegardinlisten Bestilling velger du "Størst til minste"
8. Klikk OK

De ovennevnte trinnene ville sortere dataene basert på verdiene for hjelperkolonnen, noe som også ville føre til å reversere rekkefølgen på navnene i dataene.

Når du er ferdig, kan du slette hjelperkolonnen.

I dette eksemplet har jeg vist deg hvordan du snu dataene når du bare har én kolonne, men du kan også bruke samme teknikk hvis du har en hel tabell. Bare sørg for at du velger hele tabellen og deretter bruker hjelpekolonnen til å sortere dataene i synkende rekkefølge.

Vend dataene horisontalt

Du kan også følge den samme metoden for å snu dataene horisontalt i Excel.

Excel har et alternativ for å sortere dataene horisontalt ved hjelp av dialogboksen Sorter (funksjonen "Sorter fra venstre til høyre").

Anta at du har en tabell som vist nedenfor, og du vil snu disse dataene horisontalt.

Nedenfor er trinnene for å gjøre dette:

1. I raden nedenfor skriver du inn ‘Helper’ som overskrift for raden
2. Skriv inn en rekke tall (1,2,3, og så videre) i hjelperaden.
3. Velg hele datasettet, inkludert hjelperaden
4. Klikk kategorien Data
5. Klikk på Sorter -ikonet
6. Klikk på Alternativer -knappen i dialogboksen Sorter.
7. I dialogboksen som åpnes, klikker du på "Sorter fra venstre til høyre"
8. Klikk OK
9. Velg Rad 3 i rullegardinlisten Sorter etter (eller hvilken rad som har din hjelpekolonne)
10. I rullegardinlisten Bestilling velger du "Størst til minste"
11. Klikk OK

Trinnene ovenfor vil snu hele bordet horisontalt.

Når du er ferdig, kan du fjerne hjelperaden.

Vend data ved hjelp av formler

Microsoft 365 har fått noen nye formler som gjør det veldig enkelt å reversere rekkefølgen på en kolonne eller en tabell i Excel.

I denne delen viser jeg deg hvordan du gjør dette ved å bruke SORTBY -formelen (hvis du bruker Microsoft 365), eller INDEX -formelen (hvis du ikke bruker Microsoft 365)

Bruke SORTBY -funksjonen (tilgjengelig i Microsoft 365)

Anta at du har en tabell som vist nedenfor, og du vil snu dataene i denne tabellen:

For å gjøre dette må du først kopiere overskriftene og plassere dem der du vil ha det vendte bordet

Bruk nå følgende formel under cellen i toppteksten til venstre:

= SORTBY ($ A $ 2: $ B $ 12, RAD (A2: A12),-1)

Formelen ovenfor sorterer dataene og bruker resultatet av ROW -funksjonen som grunnlag for å sortere dem.

ROW -funksjonen i dette tilfellet vil returnere en rekke tall som representerer radnummerene mellom det angitte området (som i dette eksemplet ville være en serie med tall som 2, 3, 4 og så videre).

Og siden det tredje argumentet i denne formelen er -1, ville det tvinge formelen til å sortere dataene i synkende rekkefølge.

Rekorden som har det høyeste radnummeret vil komme øverst, og den som har det laveste regelnummeret vil gå nederst, i hovedsak reversere rekkefølgen på dataene.

Når du er ferdig, kan du konvertere formelen til verdier for å få et statisk bord.

Bruke INDEX -funksjonen

Hvis du ikke har tilgang til SORTBY -funksjonen, ikke bekymre deg - du kan bruke den fantastiske INDEX -funksjonen.

Anta at du har et datasett med navn som vist nedenfor, og du vil snu disse dataene.

Nedenfor er formelen for å gjøre dette:

= INDEKS ($ A $ 2: $ A $ 12, RADER (A2: $ A $ 12))

Hvordan fungerer denne formelen?

Formelen ovenfor bruker INDEX -funksjonen som returnerer verdien fra cellen basert på tallet som er angitt i det andre argumentet.

Den virkelige magien skjer i det andre argumentet der jeg har brukt ROWS -funksjonen.

Siden jeg har låst den andre delen av referansen i ROWS -funksjonen, i den første cellen, vil den returnere antall rader mellom A2 og A12, som ville være 11.

Men når den går nedover radene, vil den første referansen endres til A3, og deretter A4, og så videre, mens den andre referansen forblir som den er fordi jeg har låst den og gjort den absolutt.

Når vi går nedover radene, vil resultatet av ROWS -funksjonen reduseres med 1, fra 11 til 10 til 9, og så videre.

Og siden INDEX -funksjonen returnerer verdien til oss basert på tallet i det andre argumentet, vil dette til slutt gi oss dataene i motsatt rekkefølge.

Du kan bruke den samme formelen selv om du har flere kolonner i datasettet. Du må imidlertid spesifisere et andre argument som spesifiserer kolonnummeret som dataene må hentes fra.

Anta at du har et datasett som vist nedenfor, og du vil reversere rekkefølgen på hele tabellen:

Nedenfor er formelen som vil gjøre det for deg:

= INDEKS ($ A $ 2: $ B $ 12, RADER (A2: $ A $ 12), KOLONNER ($ A $ 2: A2))

Dette er en lignende formel der jeg også har lagt til et tredje argument som spesifiserer kolonnetallet som verdien skal hentes fra.

For å gjøre denne formelen dynamisk, har jeg brukt COLUMNS -funksjonen som fortsetter å endre kolonneverdien fra 1 til 2 til 3 mens du kopierer den til høyre.

Når du er ferdig, kan du konvertere formlene til verdier for å sikre at du har et statisk resultat.

Merk: Når du bruker en formel for å reversere rekkefølgen på et datasett i Excel, beholder den ikke den opprinnelige formateringen. Hvis du også trenger den opprinnelige formateringen på de sorterte dataene, kan du enten bruke den manuelt eller kopiere og lime inn formateringen fra det originale datasettet til det nye sorterte datasettet

Vend data ved hjelp av VBA

Hvis blending av dataene i Excel er noe du må gjøre ganske ofte, kan du også prøve VBA -metoden.

Med en VBA -makrokode kan du kopiere og lime den inn én gang i arbeidsboken i VBA -editoren, og deretter bruke den igjen og igjen i den samme arbeidsboken.

Du kan også lagre koden i Personal Macro Workbook eller som et Excel-tillegg og kunne bruke den i en hvilken som helst arbeidsbok på systemet ditt.

Nedenfor er VBA -koden som vil snu de valgte dataene vertikalt i regnearket.

Sub FlipVerically () 'Code by Sumit Bansal from TrumpExcel.com Dim TopRow As variant Dim LastRow As variant Dim StartNum As Integer Dim EndNum As Integer Application.ScreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Do While StartNum <EndNum TopRow = Selection.Rows (StartNum) LastRow = Selection.Rows (EndNum) Selection.Rows (EndNum) = TopRow Selection.Rows (StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - 1 Loop Application.ScreenUpdating = True End Sub

For å bruke denne koden må du først velge datasettet du vil reversere (unntatt overskriftene), og deretter kjøre denne koden.

Hvordan fungerer denne koden?

Koden ovenfor teller først det totale antallet rader i datasettet og tildeler variabelen EndNum det.

Den bruker deretter Do While -løkken der sorteringen av dataene skjer.

Måten den sorterer disse dataene på er ved å ta den første og den siste raden og bytte disse. Den flytter deretter til 2. rad i den nest siste raden og bytter deretter disse. Deretter går den til den tredje raden i den tredje siste raden og så videre.

Sløyfen slutter når all sorteringen er ferdig.

Den bruker også egenskapen Application.ScreenUpdating og setter den til FALSE mens du kjører koden, og slår den deretter tilbake til TRUE når koden er fullført.

Dette sikrer at du ikke ser endringene som skjer i sanntid på skjermen din, det gjør også prosessen raskere.

Hvordan bruke koden?

Følg trinnene nedenfor for å kopiere og lime inn denne koden i VB Editor:

1. Åpne Excel -filen der du vil legge til VBA -koden
2. Hold inne ALT-tasten og trykk på F-11-tasten (du kan også gå til kategorien Utvikler og klikke på Visual Basic-ikonet)
3. I Visual Basic Editor som åpnes, vil det være en Project Explorer i venstre del av VBA -editoren. Hvis du ikke ser det, klikker du på "View" -fanen og klikker deretter på "Project Explorer"
4. Høyreklikk på et av objektene for arbeidsboken der du vil legge til koden
5. Gå til alternativet Sett inn, og klikk deretter på Modul. Dette vil legge til en ny modul i arbeidsboken
6. Dobbeltklikk på modulikonet i Project Explorer. Dette åpner kodevinduet for den modulen
7. Kopier og lim inn ovennevnte VBA -kode i kodevinduet

For å kjøre VBA -makrokoden, må du først velge datasettet du vil snu (unntatt overskriftene).

Med dataene valgt, gå til VB Editor og klikk på den grønne avspillingsknappen på verktøylinjen, eller velg hvilken som helst linje i koden og trykk deretter på F5 -tasten

Så dette er noen av metodene du kan bruke til å snu dataene i Excel (dvs. reversere rekkefølgen på datasettet).

Alle metodene jeg har dekket i denne opplæringen (formler, SORT -funksjon og VBA), kan brukes til å snu dataene vertikalt og horisontalt (du må justere formelen og VBA -koden tilsvarende for horisontal datavending).

Jeg håper du synes denne opplæringen var nyttig.