5 manieren om gegevens om te zetten in Excel (stapsgewijze zelfstudie)
Het transponeren van gegevens betekent het wijzigen van de oriëntatie van een array door de rijen ervan om te zetten in kolommen of vice versa. In deze tutorial zullen we vijf verschillende methoden met u delen om de oriëntatie van een array te veranderen en het gewenste resultaat te bereiken.
Video: gegevens omzetten in Excel
Schakel kolommen naar rijen met Plakken speciaal
Stap 1: Kopieer het bereik dat moet worden getransponeerd
Selecteer het celbereik waar u van rij en kolom wilt wisselen en druk vervolgens op CTRL + C om het bereik te kopiëren.
Stap 2: Selecteer de optie Transponeren plakken
Klik met de rechtermuisknop op de eerste cel van het doelbereik en klik vervolgens op de transponeren icon (Onder Plak opties) vanuit het rechtsklikmenu.
Resultaat
Voila! Het bereik is in een mum van tijd getransponeerd!
(AD) Tabelafmetingen eenvoudig transponeren met Kutools
Het omzetten van een kruistabel (tweedimensionale tabel) naar een platte lijst (eendimensionale lijst) of andersom in Excel kost altijd veel tijd en moeite. Als Kutools voor Excel echter is geïnstalleerd, is het Transponeer tafelafmetingen tool helpt u de conversie snel en gemakkelijk uit te voeren.
Kutools for Excel - Bevat meer dan 300 handige functies die uw werk veel gemakkelijker maken. Ontvang nu een volledige gratis proefperiode van 30 dagen!
Transponeer en koppel gegevens aan de bron
Als u de richting van een bereik dynamisch wilt wijzigen (kolommen naar rijen wisselen en vice versa) en het geschakelde resultaat wilt koppelen aan de oorspronkelijke gegevensset, kunt u een van de onderstaande benaderingen gebruiken:
- TRANSPONEREN functie (Gemakkelijk te gebruiken, maar de resultaten kunnen niet worden bewerkt)
- INDIRECT, ADRES, KOLOM en RIJ functies (Grote formule maar maakt het mogelijk om wijzigingen in de resultaten aan te brengen)
- Plakken Speciaal en Zoeken & Vervangen (Complex maar gemakkelijk te begrijpen)
- Power Query (Eenvoudig met resultaten opgemaakt in een tabel)
Wijzig rijen in kolommen met de functie TRANSPONEREN
Om rijen om te zetten in kolommen en vice versa met de TRANSPONEREN functie, doe dan als volgt.
Stap 1: Selecteer hetzelfde aantal lege cellen als de originele set cellen, maar in de andere richting
Tip: Sla deze stap over als u Excel 365 of Excel 2021 gebruikt.
Stel dat uw originele tabel binnen bereik is A1: C4, wat betekent dat de tabel 4 rijen en 3 kolommen heeft. De getransponeerde tabel heeft dus 3 rijen en 4 kolommen. Dit betekent dat u 3 rijen en 4 kolommen met lege cellen moet selecteren.
Stap 2: Voer de TRANSPOSE-formule in
Typ de onderstaande formule in de formulebalk en druk vervolgens op Ctrl + Shift + Enter om het resultaat te krijgen.
Tip: Media Enter als u Excel 365- of Excel 2021-gebruikers bent.
=TRANSPOSE(A1:C4)
- Je zou moeten veranderen A1: C4 naar uw daadwerkelijke bronbereik dat u wilt transponeren.
- Als er lege cellen in het oorspronkelijke bereik zijn, wordt het TRANSPONEREN functie zou de lege cellen omzetten in 0s (nullen). Om de nulresultaten kwijt te raken en de lege cellen te behouden tijdens het transponeren, moet u profiteren van de IF functie:
-
=TRANSPOSE(IF(A1:C4="","",A1:C4))
Resultaat
De rijen worden omgezet in kolommen en de kolommen worden omgezet in rijen.
Roteer gegevens met de functies INDIRECT, ADRES, KOLOM en RIJ
Hoewel de bovenstaande formule vrij eenvoudig te begrijpen en te gebruiken is, is het nadeel dat u geen cellen in de geroteerde tabel kunt bewerken of verwijderen. Dus ik zal een formule introduceren met behulp van INDIRECTE, ADRES, COLUMN en RIJ functies. Stel dat uw originele tabel binnen bereik is A1: C4, om een kolom-naar-rij-transformatie uit te voeren en de geroteerde gegevens verbonden te houden met de brongegevensset, volgt u de onderstaande stappen.
Stap 1: Voer de formule in
Voer de onderstaande formule in de meest linkse cel van het bestemmingsbereik in (A6 in ons geval), en druk op Enter:
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- Je zou moeten veranderen A1 naar de meest linkse cel van uw daadwerkelijke bronbereik dat u gaat transponeren, en houd de dollartekens zoals ze zijn. Het dollarteken ($) voor de kolomletter en het rijnummer geeft een absolute verwijzing aan, waardoor de kolomletter en het rijnummer ongewijzigd blijven wanneer u de formule naar andere cellen verplaatst of kopieert.
- Als er lege cellen in het oorspronkelijke bereik zijn, converteert de formule de lege cellen naar nullen (nullen). Om de nulresultaten kwijt te raken en de lege cellen te behouden tijdens het transponeren, moet u profiteren van de IF functie:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1))))
Stap 2: Kopieer de formule naar de cellen rechts en onder
Selecteer de formulecel en sleep de vulgreep (het kleine groene vierkantje in de rechterbenedenhoek van de cel) omlaag en vervolgens naar rechts naar zoveel rijen en kolommen als nodig is.
Resultaat
De kolommen en rijen worden meteen verwisseld.
Converteer kolommen naar rijen met Plakken speciaal en Zoeken en vervangen
Met nog een paar stappen met de methode Plakken speciaal en de functie Zoeken en vervangen kunt u gegevens transponeren terwijl u de broncellen koppelt aan de getransponeerde cellen.
Stap 1: Kopieer het bereik dat moet worden getransponeerd
Selecteer het cellenbereik dat u wilt transponeren en druk vervolgens op CTRL + C om het bereik te kopiëren.
Stap 2: Pas de optie Koppeling plakken toe
-
Klik met de rechtermuisknop op een lege cel en klik vervolgens op plakken speciaal.
-
Klik op plakken Link.
U krijgt het resultaat zoals hieronder weergegeven:
Stap 3: Zoek en vervang de gelijktekens (=) uit het resultaat van Link plakken
-
Selecteer het resultaatbereik (A6: C9) en druk op Ctrl + Hen dan vervangen = Met @EO (of elk(e) teken(s) die niet bestaan in het geselecteerde bereik) in de Zoeken en vervangen dialoogvenster.
-
Klik op Alles vervangenen sluit vervolgens het dialoogvenster. Hieronder ziet u hoe de gegevens eruit zullen zien.
Stap 4: Transponeer het vervangen Paste Link-resultaat
Selecteer het bereik (A6: C9) en druk op CTRL + C om het te kopiëren. Klik met de rechtermuisknop op een lege cel (hier heb ik geselecteerd A11) en selecteer de transponeren icon oppompen van Plak opties om het getransponeerde resultaat te plakken.
Stap 5: Haal gelijktekens (=) terug om het getransponeerde resultaat te koppelen aan de oorspronkelijke gegevens
-
Bewaar de getransponeerde resultaatgegevens A11: D13 geselecteerd en druk vervolgens op Ctrl + Hen vervangen @EO Met = (het tegenovergestelde van stap 3).
-
Klik op Alles vervangenen sluit vervolgens het dialoogvenster.
Resultaat
De gegevens worden getransponeerd en gekoppeld aan de oorspronkelijke cellen.
Gegevens transponeren en koppelen aan de bron Power Query
Power Query is een krachtige tool voor gegevensautomatisering waarmee u eenvoudig gegevens in Excel kunt omzetten. U kunt de klus klaren door de onderstaande stappen te volgen:
Stap 1: Selecteer het bereik dat u wilt transponeren en open het Power Query editor
Selecteer het gegevensbereik dat moet worden getransponeerd. En dan, op de Data tab, in de Gegevens ophalen en transformeren groep, klik Van tafel/bereik.
- Als het geselecteerde gegevensbereik niet in een tabel staat, a Tabel maken er verschijnt een dialoogvenster; Klik OK om er een tabel voor te maken.
- Als u Excel 2013 of 2010 gebruikt en u kunt het niet vinden Van tafel/bereik op de Data tabblad, moet u het downloaden en installeren vanaf het Microsoft Power Query voor Excel-pagina. Ga na de installatie naar de Power Query tab, klik Van tafel in de Excel-gegevens groep.
Stap 2: Converteer kolommen naar rijen met Power Query
-
Ga naar uw Transformeren tab. In de Gebruik de eerste rij als kopteksten vervolgkeuzemenu, selecteren Gebruik kopteksten als eerste rij.
-
Klik op transponeren.
Stap 3: Sla de getransponeerde gegevens op een blad op
Op de Dien in tab, klik Sluiten en laden om het Power Editor-venster te sluiten en een nieuw werkblad te maken om de getransponeerde gegevens te laden.
Resultaat
De getransponeerde gegevens worden geconverteerd naar een tabel in een nieuw aangemaakt werkblad.
- Extra kolomkoppen worden gegenereerd in de eerste rij, zoals hierboven weergegeven. Om de eerste rij onder de koppen te promoveren naar kolomkoppen, selecteert u een cel binnen de gegevens en klikt u op Vraag > Edit. Selecteer vervolgens Transformeren > Gebruik de eerste rij als kopteksten. Selecteer ten slotte Home > Sluiten en laden.
- Als er wijzigingen zijn aangebracht in de oorspronkelijke dataset, kunt u de getransponeerde gegevens hierboven bijwerken met die wijzigingen door op te klikken verversen icon naast de tafel in de Vragen en verbindingen deelvenster, of door te klikken op de verversen knop op de Vraag Tab.
(AD) Transformeer bereik met Kutools in een paar klikken
De Transformeer bereik hulpprogramma in Kutools voor Excel kan u helpen een verticale kolom eenvoudig in meerdere kolommen te transformeren (converteren) of andersom, of een rij in meerdere rijen te converteren of omgekeerd.
Kutools for Excel - Bevat meer dan 300 handige functies die uw werk veel gemakkelijker maken. Ontvang nu een volledige gratis proefperiode van 30 dagen!
Gerelateerde artikelen
- Hoe snel bereiken transponeren en lege cellen overslaan in Excel?
- Wanneer we een bereik als getransponeerd plakken, worden de lege cellen ook geplakt. Soms willen we echter gewoon de bereiken transponeren en lege cellen negeren, zoals onderstaand screenshot. Zijn er manieren om snel bereiken te transponeren en lege cellen over te slaan in Excel?
- Hoe transponeer ik elke 5 of n rijen van één kolom naar meerdere kolommen?
- Stel dat u lange gegevens in kolom A heeft, en nu wilt u elke 5 rijen van kolom A naar meerdere kolommen transponeren, zoals A1: A5 naar C6: G6, A6: A10 naar C7: G7, enzovoort, zoals volgende screenshot getoond. Hoe zou u met deze taak kunnen omgaan zonder herhaaldelijk te kopiëren en plakken in Excel?
- Hoe kolommen en rijen in één kolom te transponeren / converteren?
- Wanneer u een Excel-werkblad gebruikt, komt u soms dit probleem tegen: hoe kunt u een reeks gegevens converteren of transponeren in een enkele kolom? (Zie de volgende screenshots :) Nu introduceer ik drie snelle trucs om dit probleem op te lossen.
- Hoe kolommen en rijen in een enkele rij te transponeren / converteren?
- Hoe voeg ik meerdere rijen en kolommen samen tot één lange rij? Misschien lijkt het u gemakkelijk, omdat u ze een voor een kunt kopiëren en ze handmatig in een rij kunt samenvoegen. Dit kan echter tijdrovend en vervelend zijn als er honderden rijen en kolommen zijn. Hier zal ik het hebben over enkele snelle trucs om het op te lossen.
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Inhoudsopgave
- Video: gegevens omzetten in Excel
- Schakel kolommen naar rijen met Plakken speciaal
- Transponeer en koppel gegevens aan de bron
- Met TRANSPOSE-functie
- Met INDIRECT, ADRES, KOLOM en RIJ functies
- Met Plakken speciaal en Zoeken en vervangen
- met Power Query
- Gerelateerde artikelen
- De beste tools voor kantoorproductiviteit
- Heb je vragen? Stel ze hier.