Watch Video – Hoe kan ik gegevens transponeren in Excel
Als u een dataset hebt en u wilt deze transponeren in Excel (d.w.z. rijen omzetten in kolommen en kolommen in rijen), dan is handmatig transponeren een complete NEE!
Deze handleiding behandelt:
Gegevens transponeren met behulp van Plakken speciaal
Plakken speciaal kan een heleboel verbazingwekkende dingen doen, en een van die dingen is het transponeren van gegevens in Excel.
Voorstel dat u een dataset hebt zoals hieronder is weergegeven:
Deze gegevens hebben de regio’s in een kolom en de kwartalen in een rij.
Nu moet u om de een of andere reden deze gegevens transponeren, en wel als volgt:
- Selecteer de gegevensverzameling (in dit geval A1:E5).
- Kopieer de dataset (Control + C) of klik met de rechtermuisknop en selecteer kopiëren.
- Nu kunt u de getransponeerde gegevens op een nieuwe locatie plakken. In dit voorbeeld wil ik kopiëren in G1:K5, dus klik met de rechtermuisknop op cel G1 en selecteer plakken speciaal.
- In het dialoogvenster plakken speciaal, vinkt u rechtsonder de optie omzetten aan.
- Klik op OK.
Dit zou direct de gegevens kopiëren en plakken, maar op een zodanige manier dat ze zijn getransponeerd. Hieronder staat een demo die het hele proces laat zien.
De hierboven getoonde stappen kopiëren zowel de waarde, de formule (indien aanwezig), als de opmaak. Als u alleen de waarde wilt kopiëren, selecteer dan ‘waarde’ in het dialoogvenster ‘speciaal plakken’.
Merk op dat de gekopieerde gegevens statisch zijn, en als u wijzigingen aanbrengt in de oorspronkelijke gegevensverzameling, deze wijzigingen niet worden weerspiegeld in de getransponeerde gegevens.
Als u wilt dat deze getransponeerde cellen worden gekoppeld aan de oorspronkelijke cellen, kunt u de kracht van Zoeken & Vervangen combineren met Plakken speciaal.
Gegevens transponeren met Plakken speciaal en Zoeken & Vervangen
Als u alleen Plakken speciaal gebruikt, krijgt u statische gegevens. Dit betekent dat als je oorspronkelijke gegevens veranderen en je wilt dat de getransponeerde gegevens ook worden bijgewerkt, je opnieuw Speciaal Plakken moet gebruiken om ze te transponeren.
Hier is een coole truc die je kunt gebruiken om de gegevens te transponeren en ze toch te koppelen aan de oorspronkelijke cellen.
Voorstel dat u een dataset hebt zoals hieronder afgebeeld:
Hier volgen de stappen om de gegevens te transponeren maar de koppelingen intact te houden:
- Selecteer de dataset (A1:E5).
- Kopieer deze (Control + C, of klik met de rechtermuisknop en selecteer kopiëren).
- Nu kunt u de getransponeerde gegevens op een nieuwe locatie plakken. In dit voorbeeld wil ik kopiëren in G1:K5, dus klik met de rechtermuisknop op cel G1 en selecteer plakken speciaal.
- In het dialoogvenster Plakken speciaal klikt u op de knop Koppeling plakken. Hierdoor krijgt u dezelfde gegevensverzameling, maar hier zijn de cellen gekoppeld aan de oorspronkelijke gegevensverzameling (bijvoorbeeld G1 is gekoppeld aan A1, en G2 is gekoppeld aan A2, enzovoort).
- Met deze nieuwe gekopieerde gegevens geselecteerd, Druk op Control + H (of ga naar Home -> Bewerken -> Zoeken & Selecteren ->Vervangen). Hierdoor wordt het dialoogvenster Zoeken & Vervangen geopend.
- In het dialoogvenster Zoeken & Vervangen, gebruikt u het volgende:
- In Zoek wat: =
- In Vervangen door: !@# (merk op dat ik !@# gebruik omdat het een unieke combinatie van tekens is waarvan het onwaarschijnlijk is dat ze deel uitmaken van uw gegevens. U kunt elke dergelijke unieke reeks tekens gebruiken).
- Rechter muisklik en kopieer de gegevensreeks (of gebruik Control + C).
- Selecteer een nieuwe locatie, klik met de rechtermuisknop en selecteer Plakken speciaal. In dit voorbeeld plak ik het in cel G7. U kunt het overal plakken waar u maar wilt.
- In het dialoogvenster Plakken speciaal selecteert u Transponeren en klikt u op OK.
- Open nu het dialoogvenster Zoeken en vervangen opnieuw en vervang !@# door =.
Klik op Alles vervangen. Dit zal de gelijk aan uit de formule vervangen en u zult !@# gevolgd door de celverwijzing in elke cel hebben.
Kopieer plakken van deze nieuw gemaakte getransponeerde gegevens naar de gegevens waaruit ze zijn gemaakt.
Dit levert u een gekoppeld gegeven op dat is getransponeerd. Als u wijzigingen aanbrengt in de oorspronkelijke dataset, worden de getransponeerde gegevens automatisch bijgewerkt.
Note: aangezien A1 in onze oorspronkelijke gegevens leeg is, moet u de 0 in G1 handmatig verwijderen. De 0 verschijnt wanneer we links plakken, aangezien een link naar een lege cel nog steeds een 0 zou retourneren. Ook moet u de nieuwe dataset opmaken (u kunt gewoon de opmaak kopiëren en plakken vanuit de oorspronkelijke dataset).
Gegevens transponeren met de TRANSPOSE-functie van Excel
Excel TRANSPOSE-functie – zoals de naam al doet vermoeden – kan worden gebruikt om gegevens in Excel te transponeren.
Voorstel dat u een dataset hebt zoals hieronder is weergegeven:
Hier volgen de stappen om de dataset te transponeren:
- Selecteer de cellen waarin u de dataset wilt transponeren. Let erop dat u exact hetzelfde aantal cellen selecteert als de oorspronkelijke gegevens. Dus als u bijvoorbeeld 2 rijen en 3 kolommen hebt, moet u 3 rijen en 2 kolommen cellen selecteren waarin u de getransponeerde gegevens wilt hebben. In dit geval, aangezien er 5 rijen en 5 kolommen zijn, moet u 5 rijen en 5 kolommen selecteren.
- Enter =TRANSPOSE(A1:E5) in de actieve cel (dat moet de cel linksboven van de selectie zijn en druk op Control Shift Enter.
Dit zou de dataset transponeren.
Hier zijn een paar dingen die u moet weten over de TRANSPOSE-functie:
- Het is een array-functie, dus u moet Control-Shift-Enter gebruiken en niet alleen Enter.
- Je kunt niet een deel van het resultaat verwijderen. U moet de hele array van waarden verwijderen die de TRANSPOSE-functie retourneert.
- Transpose-functie kopieert alleen de waarden, niet de opmaak.
Gegevens transponeren met Power Query
Power Query is een krachtig hulpmiddel waarmee u snel gegevens kunt transponeren in Excel.
Power Query is een onderdeel van Excel 2016 (haal & Transform in het tabblad Gegevens), maar als je Excel 2013 of 2010 gebruikt, dan moet je het als een add-in installeren.
Voorstel dat u de gegevensreeks hebt zoals hieronder weergegeven:
Hier volgen de stappen om deze gegevens om te zetten met Power Query:
In Excel 2016
- Selecteer de gegevens en ga naar Gegevens -> Haal & Transformeer -> Uit tabel.
- In het dialoogvenster Tabel maken, zorg ervoor dat het bereik correct is en klik op OK. Hierdoor wordt het dialoogvenster Query-editor geopend.
- In het dialoogvenster Query-editor selecteert u het tabblad ‘Transformeren’.
- In het tabblad Transformeren, ga naar Tabel -> Gebruik eerste rij als Koppen -> Gebruik Koppen als eerste rij. Dit zorgt ervoor dat de eerste rij (die de koppen bevat: Q1, Q2, Q3, en Q4) ook worden behandeld als gegevens en worden getransponeerd.
- Klik op Transponeren. Dit zou onmiddellijk de gegevens omzetten.
- Klik op Eerste rij als koptekst gebruiken. Dit zou nu de eerste rij van de getransponeerde gegevens als kopteksten maken.
- Ga naar Bestand ->Sluiten en laden. Hiermee sluit u het Power Editor venster en maakt u een nieuw blad aan dat de getransponeerde gegevens bevat.
Merk op dat aangezien de cel linksboven van onze dataset leeg was, deze een algemene naam krijgt Kolom1 (zoals hieronder getoond). U kunt deze cel uit de getransponeerde gegevens verwijderen.
In Excel 2013/2010
In Excel 2013/10 moet u Power Query als invoegtoepassing installeren.
Klik hier om de invoegtoepassing te downloaden en installatie-instructies te krijgen.
Als u Power Query hebt geïnstalleerd, gaat u naar Power Query -> Excel Data -> Uit tabel.
Hiermee wordt het dialoogvenster Tabel maken geopend. Volg nu dezelfde stappen als getoond voor Excel 2016.
Je vindt de volgende Excel-tutorials misschien ook leuk:
- Vermenigvuldigen in Excel met behulp van Plakken speciaal.
- CONCONCATENATE Excel Range (met en zonder scheidingsteken).
- Schoon gegevens in Excel-spreadsheets.
- Vergelijk kolommen in Excel
- Excel tekst naar kolommen
- Excel TRIM-functie
- Tabellen samenvoegen in Excel