Soms moet u de gegevens in Excel omdraaien, d.w.z. de volgorde van de gegevens ondersteboven in een verticale dataset en van links naar rechts in een horizontale dataset omkeren.
Nu, als je denkt dat er een ingebouwde functie moet zijn om dit in Excel te doen, ben ik bang dat je teleurgesteld zou zijn.
Hoewel er meerdere manieren zijn om de gegevens in Excel om te draaien, is er geen ingebouwde functie. Maar u kunt dit eenvoudig doen met behulp van een eenvoudige sorteertruc, formules of VBA.
In deze zelfstudie laat ik u zien hoe u de gegevens in rijen, kolommen en tabellen in Excel kunt omdraaien.
Dus laten we beginnen!
Gegevens omdraaien met SORTEREN en Helperkolom
Een van de gemakkelijkste manieren om de volgorde van de gegevens in Excel om te keren, is door een hulpkolom te gebruiken en vervolgens die hulpkolom te gebruiken om de gegevens te sorteren.
Draai de gegevens verticaal om (omgekeerde volgorde ondersteboven)
Stel dat u een gegevensset met namen in een kolom hebt, zoals hieronder weergegeven, en u wilt deze gegevens omdraaien:
Hieronder vindt u de stappen om de gegevens verticaal te spiegelen:
- Voer in de kolom hiernaast 'Helper' in als kop voor de kolom
- Voer in de helperkolom een reeks getallen in (1,2,3, enzovoort). U kunt de hier getoonde methoden gebruiken om dit snel te doen
- Selecteer de volledige dataset inclusief de helperkolom
- Klik op het tabblad Gegevens
- Klik op het pictogram Sorteren
- Selecteer in het dialoogvenster Sorteren 'Helper' in de vervolgkeuzelijst 'Sorteren op'
- Selecteer in de vervolgkeuzelijst Bestelling 'Grootste naar Kleinste'
- Klik OK
De bovenstaande stappen zouden de gegevens sorteren op basis van de waarden van de helperkolom, wat ook zou leiden tot het omkeren van de volgorde van de namen in de gegevens.
Als u klaar bent, kunt u de helperkolom verwijderen.
In dit voorbeeld heb ik je laten zien hoe je de gegevens kunt omdraaien als je maar één kolom hebt, maar je kunt dezelfde techniek ook gebruiken als je een hele tabel hebt. Zorg ervoor dat u de hele tabel selecteert en gebruik vervolgens de hulpkolom om de gegevens in aflopende volgorde te sorteren.
De gegevens horizontaal spiegelen
U kunt dezelfde methode ook volgen om de gegevens horizontaal om te draaien in Excel.
Excel heeft een optie om de gegevens horizontaal te sorteren met behulp van het dialoogvenster Sorteren (de functie 'Van links naar rechts sorteren').
Stel dat u een tabel heeft zoals hieronder weergegeven en u wilt deze gegevens horizontaal spiegelen.
Hieronder staan de stappen om dit te doen:
- Voer in de rij hieronder 'Helper' in als kop voor de rij
- Voer in de helperrij een reeks getallen in (1,2,3, enzovoort).
- Selecteer de volledige dataset inclusief de helperrij
- Klik op het tabblad Gegevens
- Klik op het pictogram Sorteren
- Klik in het dialoogvenster Sorteren op de knop Opties.
- Klik in het dialoogvenster dat wordt geopend op 'Sorteer van links naar rechts'
- Klik OK
- Selecteer in de vervolgkeuzelijst Sorteren op Rij 3 (of welke rij dan ook met uw hulpkolom)
- Selecteer in de vervolgkeuzelijst Bestelling 'Grootste naar Kleinste'
- Klik OK
De bovenstaande stappen zouden de hele tafel horizontaal omdraaien.
Als je klaar bent, kun je de hulprij verwijderen.
Gegevens spiegelen met formules
Microsoft 365 heeft een aantal nieuwe formules die het heel gemakkelijk maken om de volgorde van een kolom of tabel in Excel om te keren.
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 Microsoft 365 niet gebruikt)
De functie SORTEREN gebruiken (beschikbaar in Microsoft 365)
Stel dat u een tabel heeft zoals hieronder weergegeven en u wilt de gegevens in deze tabel omdraaien:
Om dit te doen, kopieert u eerst de kopteksten en plaatst u ze waar u de omgedraaide tabel wilt hebben
Gebruik nu de volgende formule onder de cel in de meest linkse koptekst:
=SORTEREN($A$2:$B$12,RIJ(A2:A12),-1)
De bovenstaande formule sorteert de gegevens en gebruikt het resultaat van de ROW-functie als basis om deze te sorteren.
De functie RIJ zou in dit geval een reeks getallen retourneren die de rijnummers tussen het opgegeven bereik vertegenwoordigt (wat in dit voorbeeld een reeks getallen zou zijn, zoals 2, 3, 4, enzovoort).
En aangezien het derde argument van deze formule -1 is, zou het de formule dwingen om de gegevens in aflopende volgorde te sorteren.
Het record met het hoogste rijnummer komt bovenaan en het record met het laagste regelnummer onderaan, waardoor de volgorde van de gegevens in wezen wordt omgekeerd.
Als u klaar bent, kunt u de formule converteren naar waarden om een statische tabel te krijgen.
De INDEX-functie gebruiken
Als u geen toegang heeft tot de SORTBY-functie, hoeft u zich geen zorgen te maken - u kunt de geweldige INDEX-functie gebruiken.
Stel dat u een dataset met namen hebt zoals hieronder weergegeven en u wilt deze gegevens omdraaien.
Hieronder 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 zou retourneren op basis van het getal dat is opgegeven in het tweede argument.
De echte magie gebeurt in het tweede argument waar ik de RIJEN-functie heb gebruikt.
Omdat ik het tweede deel van de verwijzing in de RIJEN-functie heb vergrendeld, in de eerste cel, zou het het aantal rijen tussen A2 en A12 retourneren, wat 11 zou zijn.
Maar als het door de rijen gaat, zou de eerste verwijzing veranderen in A3, en dan A4, enzovoort, terwijl de tweede verwijzing zou blijven zoals hij is omdat ik hem heb vergrendeld en absoluut heb gemaakt.
Als we de rijen naar beneden gaan, zou het resultaat van de RIJEN-functie afnemen met 1, van 11 naar 10 naar 9, enzovoort.
En aangezien de functie INDEX ons de waarde retourneert op basis van het getal in het tweede argument, zou dit ons uiteindelijk de gegevens in de omgekeerde volgorde opleveren.
U kunt dezelfde formule gebruiken, zelfs als u meerdere kolommen in de gegevensset hebt. u zult echter een tweede argument moeten specificeren dat het kolomnummer specificeert waaruit de gegevens moeten worden opgehaald.
Stel je hebt een dataset zoals hieronder weergegeven en je wilt de volgorde van de hele tabel omdraaien:
Hieronder vindt u de formule die dat voor u doet:
=INDEX($A$2:$B$12,RIJEN(A2:$A$12),KOLOMMEN ($A$2:A2))
Dit is een vergelijkbare formule waarbij ik ook een derde argument heb toegevoegd dat het kolomnummer specificeert waaruit de waarde moet worden opgehaald.
Om deze formule dynamisch te maken, heb ik de functie KOLOMMEN gebruikt die de kolomwaarde zou blijven veranderen van 1 naar 2 naar 3 terwijl je deze naar rechts kopieert.
Als u klaar bent, kunt u de formules converteren naar waarden om ervoor te zorgen dat u een statisch resultaat krijgt.
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 op de gesorteerde gegevens nodig hebt, kunt u deze handmatig toepassen of de opmaak van de oorspronkelijke gegevensset kopiëren en plakken naar de nieuwe gesorteerde gegevenssetGegevens omdraaien met VBA
Als het omdraaien van de gegevens in Excel iets is dat u vaak moet doen, kunt u ook de VBA-methode proberen.
Met een VBA-macrocode kunt u deze eenmaal in de werkmap in de VBA-editor kopiëren en plakken en deze vervolgens keer op keer opnieuw gebruiken in dezelfde werkmap.
U kunt de code ook opslaan in de persoonlijke macrowerkmap of als een Excel-invoegtoepassing en deze in elke werkmap op uw systeem gebruiken.
Hieronder staat de VBA-code die de geselecteerde gegevens verticaal in het werkblad zou omdraaien.
Sub FlipVerically() 'Code door Sumit Bansal van 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
Om deze code te gebruiken, moet u eerst de selectie maken van de dataset die u wilt omkeren (exclusief de headers) en vervolgens deze code uitvoeren.
Hoe werkt deze code?
De bovenstaande code telt eerst het totale aantal rijen in de dataset en wijst dat toe aan de variabele EndNum.
Het gebruikt vervolgens de Do While-lus waar het sorteren van de gegevens plaatsvindt.
De manier waarop het deze gegevens sorteert, is door de eerste en de laatste rij te nemen en deze om te wisselen. Het gaat dan naar de 2e rij in de voorlaatste rij en verwisselt deze vervolgens. Daarna gaat het naar de derde rij op de op twee na laatste rij, enzovoort.
De lus eindigt wanneer al het sorteren is voltooid.
Het gebruikt ook de eigenschap Application.ScreenUpdating en stelt deze in op FALSE tijdens het uitvoeren van de code, en zet deze vervolgens terug naar TRUE wanneer de code volledig is uitgevoerd.
Dit zorgt ervoor dat u de wijzigingen niet in realtime op uw scherm ziet gebeuren en versnelt ook het proces.
Hoe de code gebruiken?
Volg de onderstaande stappen om deze code te kopiëren en plakken in de VB Editor:
- Open het Excel-bestand waaraan u de VBA-code wilt toevoegen
- Houd de ALT-toets ingedrukt en druk op de F-11-toets (u kunt ook naar het tabblad Ontwikkelaar gaan en op het Visual Basic-pictogram klikken)
- In de Visual Basic Editor die wordt geopend, zou er een Project Explorer aan de linkerkant van de VBA-editor zijn. Als u het niet ziet, klikt u op het tabblad 'Bekijken' en vervolgens op 'Projectverkenner'
- Klik met de rechtermuisknop op een van de objecten voor de werkmap waaraan u de code wilt toevoegen
- Ga naar de optie Invoegen en klik vervolgens op Module. Dit zal een nieuwe module aan de werkmap toevoegen
- Dubbelklik op het modulepictogram in de Projectverkenner. Dit opent het codevenster voor die module
- 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 koppen).
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 vervolgens op de F5-toets
Dit zijn dus enkele van de methoden die u kunt gebruiken om de gegevens in Excel om te draaien (d.w.z. de volgorde van de gegevensset omkeren).
Alle methoden die ik in deze zelfstudie heb behandeld (formules, SORT-functie en VBA), kunnen worden gebruikt om de gegevens verticaal en horizontaal te spiegelen (je moet de formule en VBA-code dienovereenkomstig aanpassen voor horizontaal omdraaien van gegevens).
Ik hoop dat je deze tutorial nuttig vond.
