Als u een dataset heeft en deze wilt transponeren in Excel (Dit betekent rijen converteren naar kolommen en kolommen naar rijen), handmatige uitvoering is volledig onmogelijk!
Inhoud
Gegevens transponeren met Plakken speciaal
Plakken speciaal kan veel verbazingwekkende dingen, waaronder het transponeren van gegevens in Excel.
Stel dat u een dataset heeft die er als volgt uitziet:
De gegevens hebben regio's in kolommen en kwartalen in rijen.
Om de een of andere reden, als u deze gegevens moet transponeren, kunt u plakken speciaal op de volgende manier gebruiken:
Hiermee worden de gegevens onmiddellijk gekopieerd en geplakt, maar op een manier die is getransponeerd.Hieronder vindt u een demo die het hele proces laat zien.
De bovenstaande stappen kopiëren de waarden, formules (indien aanwezig) en opmaak.Als u alleen de waarde wilt kopiëren, selecteert u "Waarde" in het speciale plakdialoogvenster.
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 u deze getransponeerde cellen aan de originele cellen wilt koppelen, kunt u de kracht van Zoeken en vervangen combineren met Plakken speciaal.
Transponeer gegevens met behulp van speciaal plakken en zoeken en vervangen
Alleen plakken speciaal geeft u statische gegevens.Dit betekent dat als uw originele gegevens veranderen en de gegevens die u wilt transponeren ook worden bijgewerkt, u opnieuw plakken speciaal moet gebruiken om deze te transponeren.
Dit is een coole truc die je kunt gebruiken om de gegevens te transponeren en toch te koppelen aan de originele cellen.
Stel dat u een dataset heeft die er als volgt uitziet:
Hier zijn de stappen om de gegevens te transponeren, maar de link intact te houden:
- Selecteer de dataset (A1:E5).
- Dupliceer het (Control + C, of klik met de rechtermuisknop en kies Dupliceren).
- Nu kunt u de getransponeerde gegevens op de nieuwe locatie plakken.In dit voorbeeld wil ik G1:K5 kopiëren, dus klik met de rechtermuisknop op cel G1 en kies 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 (bijv. G1 is gekoppeld aan A1, G2 is gekoppeld aan A2, enz.).
- Met deze nieuw gekopieerde gegevens geselecteerd, drukt u op Control + H (of ga naar Home -> Bewerken -> Zoeken en selecteren -> Vervangen).Dit opent het dialoogvenster Zoeken en vervangen.
- Gebruik in het dialoogvenster Zoeken en vervangen de volgende opdrachten:
- In Zoek wat:=
- Vervang door: !@# (merk op dat ik !@# heb gebruikt omdat het een unieke combinatie van tekens is en waarschijnlijk geen deel uitmaakt van uw gegevens. U kunt elke dergelijke unieke tekenset gebruiken).
- Klik op Alles vervangen.Dit vervangt de gelijken in 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 gegevens waar ze zijn gemaakt.
- Open nu het dialoogvenster Zoeken en vervangen opnieuw en vervang !@# door =.
Dit geeft u de getransponeerde gekoppelde gegevens.De getransponeerde gegevens worden automatisch bijgewerkt als u wijzigingen aanbrengt in de oorspronkelijke gegevensset.
Opmerking: aangezien A1 in onze oorspronkelijke gegevens leeg is, moet u de 1 in G0 handmatig verwijderen.De 0 verschijnt wanneer we de link plakken, omdat een link naar een lege cel nog steeds 0 retourneert.U moet ook de nieuwe dataset opmaken (u kunt de opmaak van de originele dataset gewoon kopiëren en plakken).
Gegevens transponeren met de Excel TRANSPOSE-functie
De Excel TRANSPOSE-functie kan - zoals de naam al doet vermoeden - worden gebruikt om gegevens in Excel te transponeren.
Stel dat u een dataset heeft die er als volgt uitziet:
Hier zijn de stappen om het te transponeren:
- Selecteer de cellen waarin u de gegevensset wilt transponeren.Merk op dat u het exacte aantal cellen als onbewerkte gegevens moet selecteren.Als u bijvoorbeeld 2 rijen en 3 kolommen heeft, moet u de 3 rijen en 2 kolommen met cellen selecteren waar u de gegevens wilt transponeren.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 zijn geselecteerd en druk op Control Shift Enter.
Hiermee wordt de dataset getransponeerd.
Dit is wat u moet weten over de TRANSPOSE-functie:
- Het is een array-functie, dus je moet Control-Shift-Enter gebruiken in plaats van alleen Enter.
- U kunt een deel van het resultaat niet verwijderen.U moet de volledige reeks waarden verwijderen die door de functie TRANSPOSE worden geretourneerd.
- De transponeerfunctie kopieert alleen de waarde, niet het formaat.
Gegevens transponeren met Power Query
Power Query is een krachtig hulpmiddel waarmee u snel gegevens in Excel kunt transponeren.
Power Query JaExcel 2016("Ophalen en transformeren" op het tabblad "Gegevens"), maar als u gebruikmaakt vanExcel 2013 of 2010, moet u het als een add-on installeren.
Stel dat u een dataset heeft die er als volgt uitziet:
Dit zijn de stappen om deze gegevens te transponeren met Power Query:
in Excel 2016
Merk op dat aangezien de cel linksboven in onze dataset leeg is, deze een generieke naam Kolom1 krijgt (hieronder weergegeven).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 hierDownload de plug-in en ontvang installatie-instructies.
Ga na het installeren van Power Query naar Power Query -> Excel-gegevens -> Van tabel.
Dit opent het dialoogvenster Tabel maken.Volg nu dezelfde stappen als in Excel 2016.
Misschien vind je de volgende Excel-zelfstudies ook leuk:
- Vermenigvuldigen in Excel met Plakken speciaal.
- Voeg Excel-bereiken samen (met en zonder scheidingstekens).
