Draai data onderstebo in Excel (draai die volgorde van data in kolomme/rye om)

Soms moet jy dalk data onderstebo in Excel draai, dit wil sê, die volgorde van die data in 'n vertikale datastel omkeer en die volgorde van die data van links na regs in 'n horisontale datastel omkeer.

Nou, as jy dink daar moet 'n ingeboude funksie in Excel wees om dit te doen, ek is bevrees jy sal teleurgesteld wees.

Alhoewel daar verskeie maniere is om data in Excel om te draai, is daar geen ingeboude funksionaliteit nie.Maar jy kan dit maklik doen met eenvoudige sorteer-truuks, formules of VBA.

In hierdie tutoriaal sal ek jou wys hoe om data in rye, kolomme en tabelle onderstebo in Excel om te draai.

So kom ons begin!

Draai data om deur SORT en hulpkolomme te gebruik

Een van die maklikste maniere om die volgorde van data in Excel om te keer, is om 'n helperkolom te gebruik en dan daardie helperkolom te gebruik om die data te sorteer.

Draai data vertikaal (omgekeerde volgorde)

Gestel jy het 'n kolom soos hieronderBenoem datastel, en jy wil hierdie data omdraai:

Hier is die stappe om die data vertikaal om te draai:

1. In die aangrensende kolom, voer "Helper" as die kolomopskrif in
2. Voer 'n reeks nommers in die hulpkolom in (1, 2, 3, ens.).Jy kan dit vinnig doen deur die metode wat hier gewys word
3. Kies die hele datastel, insluitend hulpkolomme
4. Klik op die Data-oortjie
5. Klik op die sorteer-ikoon
6. In die Sorteer dialoogkassie, kies Helper uit die Sorteer volgens aftreklys
7. Kies "Groot tot Klein" in die bestelling-aftreklys
8. Klik OK

Die bogenoemde stappe sal die data sorteer op grond van die hulpkolomwaardes, wat ook sal veroorsaak dat die name in die data in omgekeerde volgorde is.

Los gerus die hulpkolom wanneer jy klaar is.

In hierdie voorbeeld wys ek jou hoe om die data om te draai wanneer daar net een kolom is, maar jy kan dieselfde tegniek gebruik as jy die hele tabel het.Maak net seker dat jy die hele tabel kies, gebruik dan die helperkolom om die data in dalende volgorde te sorteer.

Draai data horisontaal om

Jy kan ook data horisontaal in Excel op dieselfde manier omdraai.

Excel het die opsie om data horisontaal te sorteer deur die Sorteer dialoogkassie (Sorteer links na regs funksie).

Gestel jy het 'n tabel soos hieronder getoon en jy wil hierdie data horisontaal omdraai.

Hier is die stappe om dit te doen:

1. In die ry hieronder, voer "Helper" in as die titel van die ry
2. Voer 'n reeks syfers in die helperry in (1, 2, 3, ens.).
3. Kies die hele datastel, insluitend hulprye
4. Klik op die Data-oortjie
5. Klik op die sorteer-ikoon
6. Klik in die Sorteer-dialoog op die Opsies-knoppie.
7. In die dialoog wat oopmaak, klik Sorteer links na regs
8. Klik OK
9. In die Sorteer volgens aftreklys, kies Ry 3 (of wat ook al jou helperkolom het)
10. Kies "Groot tot Klein" in die bestelling-aftreklys
11. Klik OK

Die bogenoemde stappe sal die hele tafel horisontaal omdraai.

As jy klaar is, kan jy die helperry uitvee.

Gebruik Excel om formules onderstebo te draaiflip data

Microsoft 365 het 'n paar nuwe formules wat dit baie maklik maak om die volgorde van 'n kolom of tabel in Excel om te keer.

In hierdie afdeling sal ek jou wys hoe om dit te doen deur die SORTBY-formule (as jy Microsoft 365 gebruik) of die INDEX-formule (as jy nie Microsoft 365 gebruik nie) te doen.

Gebruik die SORTBY-funksie (beskikbaar in Microsoft 365)

Gestel jy het 'n tabel soos die volgende, en jy wil die data in daardie tabel omdraai:

Om dit te doen, kopieer eers die opskrifte en plaas dit waar jy die tabel wil omdraai

Gebruik nou die volgende formule onder die kopsel heel links:

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

Die bogenoemde formule sorteer die data en sorteer op grond van die resultaat van die ROW-funksie.

In hierdie geval sal die ROW-funksie 'n reeks getalle terugstuur wat rynommers tussen die gespesifiseerde reeks verteenwoordig (in hierdie voorbeeld, 'n reeks getalle, soos 2, 3, 4, ens.).

Aangesien die derde parameter van hierdie formule -1 is, dwing dit die formule om die data in dalende volgorde te sorteer.

Die rekord met die hoogste rynommer sal bo-aan wees en die rekord met die laagste reëlnommer sal onderaan wees, basies omkeer die volgorde van die data.

Sodra jy klaar is, kan jy die formule omskakel na 'n waarde om 'n statiese tabel te kry.

Gebruik indeksfunksie

As jy nie die SORTBY-funksie kan gebruik nie, moenie bekommerd wees nie - jy kan die wonderlike INDEX-funksie gebruik.

Gestel jy het 'n datastel name soos hieronder getoon, en jy wil hierdie data omdraai.

Hier is die formule om dit te doen:

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

 Hoe werk hierdie formule?

Die formule hierbo gebruik die INDEX-funksie, wat die waarde van die sel sal terugstuur op grond van die getal wat in die tweede parameter gespesifiseer word.

Die ware magie gebeur wanneer ek die tweede parameter van die ROWS-funksie gebruik.

Aangesien ek die tweede deel van die verwysing in die RYE-funksie gesluit het, sal dit in die eerste sel die aantal rye tussen A2 en A12, wat 11 is, terugstuur.

Maar soos dit in die ry afgaan, sal die eerste verwysing verander na A3, dan A4, ensovoorts, terwyl die tweede verwysing sal bly soos dit is omdat ek dit gesluit het en dit absoluut gemaak het.

Soos ons afgaan, sal die resultaat van die RYE-funksie verminder met 1, van 11 tot 10 tot 9, ensovoorts.

Aangesien die INDEX-funksie 'n waarde op grond van die getal in die tweede argument terugstuur, gee dit uiteindelik vir ons die data in omgekeerde volgorde.

Jy kan dieselfde formule gebruik selfs al is daar veelvuldige kolomme in die datastel.U moet egter 'n tweede parameter spesifiseer, wat die kolomnommer sal spesifiseer waaruit data gehaal moet word.

Gestel jy het 'n datastel wat so lyk, en jy wil die volgorde van die hele tabel omkeer:

Hier is die formule wat dit vir jou sal doen:

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

Hier is 'n soortgelyke formule waar ek ook 'n derde parameter bygevoeg het wat die kolomnommer spesifiseer waaruit die waarde geneem moet word.

Om hierdie formule dinamies te maak, het ek die KOLOMME-funksie gebruik, wat aanhou om die kolomwaarde van 1 na 2 na 3 te verander soos jy dit na regs kopieer.

Sodra jy klaar is, kan jy die formule omskakel na 'n waarde om statiese resultate te verseker.

Let wel: Wanneer jy 'n formule gebruik om die volgorde van 'n datastel in Excel om te keer, behou dit nie die oorspronklike formatering nie.As jy ook die oorspronklike formatering van die gesorteerde data nodig het, kan jy dit handmatig toepas of die formatering vanaf die oorspronklike datastel in die nuwe gesorteerde datastel kopieer en plak

Blaai data met behulp van VBA

As die blaai van data in Excel iets is wat jy gereeld moet doen, kan jy ook die VBA-metode probeer.

Deur VBA-makrokode te gebruik, kan jy dit een keer in 'n werkboek in die VBA-redigeerder kopieer en plak, en dit dan oor en oor in dieselfde werkboek hergebruik.

Jy kan ook die kode in 'n persoonlike makro-werkboek of as 'n Excel-byvoeging stoor en dit in enige werkboek op jou stelsel kan gebruik.

Hieronder is die VBA-kode om die geselekteerde data vertikaal in die werkblad om te draai.

Sub FlipVerically()

Dim TopRow as variant
Dim LastRow as variant
Dim StartNum As Heelgetal
Verduister Eindgetal as heelgetal

Application.ScreenUpdating = Onwaar

BeginNum = 1
EndNum = Seleksie. Rye. Tel

Doen terwyl StartNum < EndNum
TopRow = Seleksie. Rye(StartNum)
Laaste Ry = Selection.Rows(Eindnommer)
Selection.Rows(EndNum) = TopRow
Selection.Rows(StartNum) = Laaste Ry
BeginNum = BeginNum + 1
EndNum = EindNum - 1
Loop

Application.ScreenUpdating = Waar
End Sub

Om hierdie kode te gebruik, moet jy eers die datastel kies wat jy wil omkeer (opskrifte uitgesluit), en dan hierdie kode laat loop.

Hoe werk hierdie kode?

Bogenoemde kode tel eers die totale aantal rye in die datastel en ken dit toe aan die veranderlike EndNum.

Dan gebruik dit 'n Do While-lus om die data te sorteer.

Die manier waarop dit hierdie data sorteer, is deur die eerste en laaste ry te neem en dit om te ruil.Dan skuif dit na die tweede ry van die voorlaaste ry en ruil hulle dan om.Dan beweeg dit na die derde ry van die derde na laaste ry, ensovoorts.

Die lus eindig wanneer alle sortering voltooi is.

Dit gebruik ook die Application.ScreenUpdating-eienskap en stel dit op ONWAAR wanneer die kode loop, en draai dit dan terug na WAAR wanneer die kode klaar loop.

Dit verseker dat jy nie veranderinge in reële tyd op die skerm sien gebeur nie, en versnel ook die proses.

Hoe om die kode te gebruik?

Kopieer en plak hierdie kode in die VB-redigeerder soos volg:

1. Maak die Excel-lêer oop waar jy die VBA-kode wil byvoeg
2. Hou ALT en druk F-11 (jy kan ook na die ontwikkelaaroortjie gaan en op die Visual Basic-ikoon klik)
3. In die Visual Basic-redigeerder wat oopmaak, sal daar 'n Project Explorer aan die linkerkant van die VBA-redigeerder wees.As jy dit nie sien nie, klik die View-oortjie en klik dan Project Explorer
4. Regskliek op enige voorwerp van die werkboek waar jy kode wil byvoeg
5. Gaan na Insert opsie en klik op Module.Dit sal 'n nuwe module by die werkboek voeg
6. Dubbelklik op die module-ikoon in die Project Explorer.Dit sal die kodevenster vir daardie module oopmaak
7. Kopieer en plak die bogenoemde VBA-kode in die kodevenster

Om die VBA-makrokode te laat loop, kies eers die datastel om te draai (uitgesluit die kopskrif).

Met die data gekies, gaan na die VB-redigeerder en klik op die groen speelknoppie in die nutsbalk, of kies enige reël in die kode en druk F5

So, dit is 'n paar van die maniere waarop jy data in Excel kan omdraai (dit wil sê, die volgorde van 'n datastel omkeer).

Al die metodes wat ek in hierdie tutoriaal behandel het (formules, SORT-funksies en VBA) kan gebruik word om data vertikaal en horisontaal om te draai (jy moet die formules en VBA-kode dienooreenkomstig aanpas vir horisontale data-omdraai).

Ek hoop jy het hierdie tutoriaal nuttig gevind.