Zet gegevens ondersteboven in Excel (keer de volgorde van gegevens in kolommen/rijen om)

Soms moet u gegevens in Excel ondersteboven draaien, dat wil zeggen: de volgorde van de gegevens in een verticale gegevensset omkeren, en de volgorde van de gegevens van links naar rechts in een horizontale gegevensset.

Nu, als je denkt dat er een ingebouwde functie in Excel moet zijn om dit te doen, ben ik bang dat je teleurgesteld zult zijn.

Hoewel er verschillende manieren zijn om gegevens in Excel om te draaien, is er geen ingebouwde functionaliteit.Maar je kunt het gemakkelijk doen met eenvoudige sorteertrucs, formules of VBA.

In deze zelfstudie laat ik u zien hoe u gegevens in rijen, kolommen en tabellen ondersteboven kunt spiegelen in Excel.

Dus laten we beginnen!

Gegevens spiegelen met SORTEREN en hulpkolommen

Een van de gemakkelijkste manieren om de volgorde van gegevens in Excel om te keren, is door een hulpkolom te gebruiken en vervolgens die hulpkolom te gebruiken om de gegevens te sorteren.

Gegevens verticaal spiegelen (omgekeerde volgorde)

Stel dat u een kolom heeft zoals hieronderNaam dataset, en je wilt deze gegevens omdraaien:

Dit zijn de stappen om de gegevens verticaal om te draaien:

1. Voer in de aangrenzende kolom "Helper" in als de kolomkop
2. Voer in de helpkolom een ​​reeks cijfers in (1, 2, 3, enz.).U kunt dit snel doen met behulp van de hier getoonde methode
3. Selecteer de volledige dataset, inclusief hulpkolommen
4. Klik op het tabblad Gegevens
5. Klik op het sorteerpictogram
6. Selecteer in het dialoogvenster Sorteren Helper in het vervolgkeuzemenu Sorteren op
7. Selecteer in het vervolgkeuzemenu van de bestelling "Groot naar klein"
8. Klik OK

Met de bovenstaande stappen worden de gegevens gesorteerd op basis van de helperkolomwaarden, waardoor de namen in de gegevens ook in omgekeerde volgorde worden weergegeven.

Voel je vrij om de help-kolom te laten vallen als je klaar bent.

In dit voorbeeld laat ik je zien hoe je de gegevens omdraait als er maar één kolom is, maar je kunt dezelfde techniek gebruiken als je de hele tabel hebt.Zorg ervoor dat u de hele tabel selecteert en gebruik vervolgens de hulpkolom om de gegevens in aflopende volgorde te sorteren.

Gegevens horizontaal spiegelen

Op dezelfde manier kunt u gegevens in Excel ook horizontaal spiegelen.

Excel heeft de mogelijkheid om gegevens horizontaal te sorteren met behulp van het dialoogvenster Sorteren (functie van links naar rechts sorteren).

Stel dat u een tabel heeft zoals hieronder weergegeven en u wilt deze gegevens horizontaal spiegelen.

Hier zijn de stappen om dit te doen:

1. Voer in de rij hieronder "Helper" in als de titel van de rij
2. Voer in de helperrij een reeks getallen in (1, 2, 3, enz.).
3. Selecteer de volledige dataset, inclusief hulprijen
4. Klik op het tabblad Gegevens
5. Klik op het sorteerpictogram
6. Klik in het dialoogvenster Sorteren op de knop Opties.
7. Klik in het dialoogvenster dat wordt geopend op Van links naar rechts sorteren
8. Klik OK
9. Selecteer in de vervolgkeuzelijst Sorteren op Rij 3 (of wat dan ook uw helperkolom heeft)
10. Selecteer in het vervolgkeuzemenu van de bestelling "Groot naar klein"
11. Klik OK

Met de bovenstaande stappen wordt de hele tafel horizontaal omgedraaid.

Als u klaar bent, kunt u de helperrij verwijderen.

Gebruik Excel om formules op hun kop te zettengegevens omdraaien

Microsoft 365 heeft enkele nieuwe formules die het heel eenvoudig maken om de volgorde van een kolom of tabel in Excel om te draaien.

In deze sectie laat ik je zien hoe je dit doet met behulp van de SORTBY-formule (als je Microsoft 365 gebruikt) of de INDEX-formule (als je geen Microsoft 365 gebruikt)

Gebruik de functie SORTEREN (beschikbaar in Microsoft 365)

Stel dat u een tabel heeft zoals de volgende, en u wilt de gegevens in die tabel omdraaien:

Om dit te doen, kopieert u eerst de koppen en plaatst u ze waar u de tabel wilt omdraaien

Gebruik nu de volgende formule onder de meest linkse koptekstcel:

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

De bovenstaande formule sorteert de gegevens en sorteert op basis van het resultaat van de ROW-functie.

In dit geval retourneert de functie RIJ een reeks getallen die rijnummers vertegenwoordigen tussen het opgegeven bereik (in dit voorbeeld een reeks getallen, zoals 2, 3, 4, enz.).

Aangezien de derde parameter van deze formule -1 is, dwingt het de formule om de gegevens in aflopende volgorde te sorteren.

Het record met het hoogste rijnummer staat bovenaan en het record met het laagste regelnummer staat onderaan, waardoor de volgorde van de gegevens in feite wordt omgekeerd.

Als u klaar bent, kunt u de formule converteren naar een waarde om een ​​statische tabel te krijgen.

Indexfunctie gebruiken

Als u de SORTBY-functie niet kunt gebruiken, hoeft u zich geen zorgen te maken - u kunt de geweldige INDEX-functie gebruiken.

Stel dat u een dataset met namen heeft, zoals hieronder weergegeven, en u wilt deze gegevens omdraaien.

Hier is de formule om dit te doen:

= INDEX ($ A $ 2: $ A $ 12, RIJEN (A2: $ A $ 12))

 Hoe werkt deze formule?

De bovenstaande formule gebruikt de INDEX-functie, die de waarde uit de cel retourneert op basis van het getal dat is opgegeven in de tweede parameter.

De echte magie gebeurt wanneer ik de tweede parameter van de ROWS-functie gebruik.

Omdat ik het tweede deel van de verwijzing in de RIJEN-functie heb vergrendeld, wordt in de eerste cel het aantal rijen tussen A2 en A12 geretourneerd, dat is 11.

Maar als het door de rij gaat, verandert de eerste verwijzing naar A3, dan A4, enzovoort, terwijl de tweede verwijzing blijft zoals hij is omdat ik hem heb vergrendeld en absoluut heb gemaakt.

Naarmate we naar beneden gaan, neemt het resultaat van de RIJEN-functie af met 1, van 11 naar 10 naar 9, enzovoort.

Omdat de INDEX-functie een waarde retourneert op basis van het getal in het tweede argument, geeft dit ons de gegevens in omgekeerde volgorde.

U kunt dezelfde formule gebruiken, zelfs als de gegevensset meerdere kolommen bevat.U moet echter een tweede parameter opgeven, die het kolomnummer specificeert waaruit gegevens moeten worden opgehaald.

Stel dat u een dataset heeft die er als volgt uitziet, en u wilt de volgorde van de hele tabel omkeren:

Hier is de formule die dit voor u zal doen:

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

Hier is een vergelijkbare formule waarbij ik ook een derde parameter heb toegevoegd die het kolomnummer specificeert waaruit de waarde moet worden gehaald.

Om deze formule dynamisch te maken, heb ik de functie KOLOMMEN gebruikt, die de kolomwaarde steeds verandert van 1 in 2 in 3 terwijl je deze naar rechts kopieert.

Als u klaar bent, kunt u de formule converteren naar een waarde om statische resultaten te garanderen.

Opmerking: wanneer u een formule gebruikt om de volgorde van een gegevensset in Excel om te keren, behoudt deze de oorspronkelijke opmaak niet.Als u ook de originele opmaak van de gesorteerde gegevens nodig heeft, kunt u deze handmatig toepassen of de opmaak van de oorspronkelijke gegevensset kopiëren en plakken in de nieuwe gesorteerde gegevensset

Gegevens omdraaien met VBA

Als het omdraaien van gegevens in Excel iets is dat u vaak moet doen, kunt u ook de VBA-methode proberen.

Met behulp van VBA-macrocode kunt u deze één keer kopiëren en plakken in een werkmap in de VBA-editor en deze vervolgens steeds opnieuw gebruiken in dezelfde werkmap.

U kunt de code ook opslaan in een persoonlijke macro-werkmap of als een Excel-invoegtoepassing en deze in elke werkmap op uw systeem gebruiken.

Hieronder staat de VBA-code om de geselecteerde gegevens verticaal in het werkblad om te draaien.

Sub FlipVerisch()

Dim TopRow als variant
Dim LastRow als variant
Dim StartGetal als geheel getal
Dim EndNum als geheel getal

Application.ScreenUpdating = False

StartGetal = 1
EndNum = Selectie.Rijen.Aantal

Doen terwijl StartNum < EndNum
TopRow = Selectie.Rijen (StartNum)
Laatste Rij = Selectie. Rijen (EndNum)
Selection.Rows(EndNum) = TopRij
Selection.Rows(StartNum) = LaatsteRij
StartNum = StartNum + 1
EndNum = EndNum - 1
Ringleiding

Application.ScreenUpdating = True
End Sub

Om deze code te gebruiken, moet u eerst de dataset selecteren die u wilt omkeren (exclusief headers), en vervolgens deze code uitvoeren.

Hoe werkt deze code?

De bovenstaande code telt eerst het totale aantal rijen in de dataset en wijst dit toe aan de variabele EndNum.

Vervolgens gebruikt het een Do While-lus om de gegevens te sorteren.

De manier waarop het deze gegevens sorteert, is door de eerste en laatste rij te nemen en deze om te wisselen.Dan gaat het naar de tweede rij van de voorlaatste rij en verwisselt ze dan.Daarna gaat het naar de derde rij van de derde naar de laatste rij, enzovoort.

De lus eindigt wanneer alle sortering is voltooid.

Het gebruikt ook de eigenschap Application.ScreenUpdating en stelt deze in op FALSE wanneer de code wordt uitgevoerd, en zet deze vervolgens terug naar TRUE wanneer de code is voltooid.

Dit zorgt ervoor dat je veranderingen niet in realtime op het scherm ziet gebeuren en versnelt ook het proces.

Hoe de code gebruiken?

Kopieer en plak deze code als volgt in de VB-editor:

1. Open het Excel-bestand waaraan u de VBA-code wilt toevoegen
2. Houd ALT ingedrukt en druk op F-11 (je kunt ook naar het ontwikkelaarstabblad gaan en op het Visual Basic-pictogram klikken)
3. In de Visual Basic-editor die wordt geopend, bevindt zich een Projectverkenner aan de linkerkant van de VBA-editor.Als u het niet ziet, klikt u op het tabblad Weergave en vervolgens op Projectverkenner
4. Klik met de rechtermuisknop op een object van de werkmap waaraan u code wilt toevoegen
5. Ga naar de optie Invoegen en klik op Module.Dit zal een nieuwe module aan de werkmap toevoegen
6. Dubbelklik op het modulepictogram in de Projectverkenner.Dit opent het codevenster voor die module
7. Kopieer en plak de bovenstaande VBA-code in het codevenster

Om de VBA-macrocode uit te voeren, selecteert u eerst de gegevensset die u wilt omdraaien (exclusief de koptekst).

Ga met de gegevens geselecteerd naar de VB-editor en klik op de groene afspeelknop in de werkbalk, of selecteer een regel in de code en druk op F5

Dit zijn dus enkele manieren waarop u gegevens in Excel kunt omdraaien (dwz de volgorde van een gegevensset omkeren).

Alle methoden die ik in deze tutorial heb behandeld (formules, SORT-functies en VBA) kunnen worden gebruikt om gegevens verticaal en horizontaal om te draaien (je moet de formules en VBA-code dienovereenkomstig aanpassen voor horizontaal omdraaien van gegevens).

Ik hoop dat je deze tutorial nuttig vond.