✅ Snel gegevens transponeren in Excel (stapsgewijze handleiding)

Inhoudsopgave

Gegevens transponeren met Plakken speciaal
Gegevens transponeren met Plakken speciaal en Zoeken en vervangen
Gegevens transponeren met behulp van Excel TRANSPOSE-functie
Gegevens transponeren met Power Query

Bekijk video - Gegevens transponeren in Excel

Als u een dataset heeft en deze in Excel wilt transponeren (wat betekent dat u rijen in kolommen en kolommen in rijen moet converteren), is het een complete NEE!

Gegevens transponeren met Plakken speciaal

Plakken speciaal kan veel verbazingwekkende dingen doen, en een daarvan is om gegevens in Excel te transponeren.

Stel dat u een dataset heeft zoals hieronder weergegeven:

Deze gegevens hebben de regio's in een kolom en kwartalen op een rij.

Om de een of andere reden, als u deze gegevens moet transponeren, kunt u dit als volgt doen met speciaal plakken:

Selecteer de dataset (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.
Vink in het dialoogvenster Plakken speciaal de optie voor transponeren rechtsonder aan.
Klik OK.

Dit zou de gegevens onmiddellijk kopiëren en plakken, maar op zo'n manier dat ze zijn getransponeerd. Hieronder vindt u een demo die het hele proces laat zien.

De hierboven getoonde stappen kopiëren de waarde, de formule (indien aanwezig), evenals het formaat. Als u alleen de waarde wilt kopiëren, selecteert u 'waarde' in het speciale dialoogvenster plakken.

Houd er rekening mee dat de gekopieerde gegevens statisch zijn en dat als u wijzigingen aanbrengt in de oorspronkelijke gegevensset, deze wijzigingen niet worden weergegeven in de getransponeerde gegevens.

Als je wilt dat deze getransponeerde cellen aan de originele cellen worden gekoppeld, kun je de kracht van Zoeken & Vervangen combineren met Plakken speciaal.

Gegevens transponeren met Plakken speciaal en Zoeken en vervangen

Als u alleen Paste Special gebruikt, krijgt u statische gegevens. Dit betekent dat als uw originele gegevens veranderen en u wilt dat de getransponeerde gegevens ook worden bijgewerkt, u Paste Special opnieuw moet gebruiken om ze te transponeren.

Hier is een coole truc die je kunt gebruiken om de gegevens te transponeren en toch aan de originele cellen te koppelen.

Stel dat u een dataset heeft zoals hieronder weergegeven:

Hier zijn de stappen om de gegevens te transponeren, maar de links intact te houden:

Selecteer de dataset (A1:E5).
Kopieer het (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.
Klik in het dialoogvenster Plakken speciaal op de knop Koppeling plakken. Dit geeft je dezelfde dataset, maar hier zijn de cellen gekoppeld aan de originele dataset (bijvoorbeeld G1 is gekoppeld aan A1 en G2 is gekoppeld aan A2 enzovoort).
Met deze nieuwe gekopieerde gegevens geselecteerd, druk je op Control + H (of ga je naar Home -> Bewerken -> Zoeken en selecteren -> Vervangen). Hiermee wordt het dialoogvenster Zoeken en vervangen geopend.
Gebruik het volgende in het dialoogvenster Zoeken en vervangen:
- In Zoek wat: =
- In Vervangen door: !@# (merk op dat ik !@# gebruik omdat het een unieke combinatie van tekens is die waarschijnlijk geen deel uitmaakt van uw gegevens. U kunt elke dergelijke unieke reeks tekens gebruiken).
Klik op Alles vervangen. Dit vervangt het gelijk aan van de formule en je hebt !@# gevolgd door de celverwijzing in elke cel.
Klik met de rechtermuisknop en kopieer de dataset (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. Je kunt het overal plakken waar je maar wilt.
Selecteer in het dialoogvenster Plakken speciaal de optie Transponeren en klik op OK.
Kopieer en plak deze nieuw gecreëerde getransponeerde data naar degene van waaruit het is gemaakt.
Open nu het dialoogvenster Zoeken en vervangen opnieuw en vervang !@# door =.

Dit geeft u gekoppelde gegevens die zijn getransponeerd. Als u wijzigingen aanbrengt in de oorspronkelijke gegevensset, worden de getransponeerde gegevens automatisch bijgewerkt.

Opmerking: aangezien onze oorspronkelijke gegevens A1 als leeg hebben, moet u de 0 in G1 handmatig verwijderen. De 0 verschijnt wanneer we koppelingen plakken, omdat een koppeling naar een lege cel nog steeds een 0 zou opleveren. Ook zou u de nieuwe dataset moeten formatteren (u kunt de opmaak eenvoudig kopiëren en plakken vanuit de originele dataset).

Gegevens transponeren met behulp van Excel TRANSPOSE-functie

Excel TRANSPOSE-functie - zoals de naam al doet vermoeden - kan worden gebruikt om gegevens in Excel te transponeren.

Stel dat u een dataset heeft zoals hieronder weergegeven:

Hier zijn de stappen om het te transponeren:

Selecteer de cellen waar u de gegevensset wilt transponeren. Merk op dat u het exacte aantal cellen moet selecteren als de originele gegevens. Dus als u bijvoorbeeld 2 rijen en 3 kolommen heeft, moet u 3 rijen en 2 kolommen met cellen selecteren waar 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.
Voer =TRANSPOSE(A1:E5) in de actieve cel in (dit moet de cel linksboven in 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 je moet Control-Shift-Enter gebruiken en niet alleen Enter.
U kunt een deel van het resultaat niet verwijderen. U moet de volledige reeks waarden verwijderen die de functie TRANSPOSE retourneert.
De transponeerfunctie kopieert alleen de waarden, niet de opmaak.

Gegevens transponeren met Power Query

Power Query is een krachtig hulpmiddel waarmee u snel gegevens in Excel kunt transponeren.

Power Query maakt deel uit van Excel 2016 (Ophalen en transformeren op het tabblad Gegevens), maar als u Excel 2013 of 2010 gebruikt, moet u het als invoegtoepassing installeren.

Stel je hebt de dataset zoals hieronder weergegeven:

Dit zijn de stappen om deze gegevens te transponeren met Power Query:

In Excel 2016

Selecteer de data en ga naar Data -> Get & Transform -> From Table.
Controleer in het dialoogvenster Tabel maken of het bereik correct is en klik op OK. Hierdoor wordt het dialoogvenster Query-editor geopend.
Selecteer in het dialoogvenster Query-editor het tabblad 'Transformeren'.
Ga op het tabblad Transformeren naar Tabel -> Eerste rij gebruiken als kopteksten -> Kopteksten gebruiken als eerste rij. Dit zorgt ervoor dat de eerste rij (die koppen bevat: Q1, Q2, Q3 en Q4) ook als gegevens wordt behandeld en getransponeerd.
Klik op Transponeren. Dit zou de gegevens onmiddellijk omzetten.
Klik op Eerste rij als koptekst gebruiken. Dit zou nu de eerste rij van de getransponeerde gegevens als koppen maken.
Ga naar Bestand -> Sluiten en laden. Hierdoor wordt het Power Editor-venster gesloten en wordt een nieuw blad gemaakt dat de getransponeerde gegevens bevat.

Merk op dat aangezien de cel linksboven in onze dataset leeg was, deze een generieke naam Kolom1 krijgt (zoals hieronder weergegeven). U kunt deze cel uit de getransponeerde gegevens verwijderen.