Hvis du har et datasett og du vil transponere det i Excel (Dette betyr å konvertere rader til kolonner og kolonner til rader), manuell utførelse er helt umulig!
Innhold
Transponer data ved å bruke Paste Special
Paste Special kan gjøre mange fantastiske ting, en av dem er å transponere data i Excel.
Anta at du har et datasett som ser slik ut:
Dataene har regioner i kolonner og kvartaler i rader.
Av en eller annen grunn, hvis du trenger å transponere disse dataene, kan du bruke Paste Special på følgende måte:
Dette vil kopiere og lime inn dataene umiddelbart, men på en måte som er transponert.Nedenfor er en demo som viser hele prosessen.
Trinnene vist ovenfor kopierer verdiene, formlene (hvis noen) og formateringen.Hvis du bare vil kopiere verdien, velg "Verdi" i den spesielle lim-dialogen.
Merk at de kopierte dataene er statiske, og hvis du gjør endringer i det originale datasettet, vil disse endringene ikke reflekteres i de transponerte dataene.
Hvis du vil koble disse transponerte cellene til de originale cellene, kan du kombinere kraften til Finn og erstatt med Lim inn spesial.
Transponer data ved å bruke Paste Special og finn og erstatt
Paste Special alene gir deg statiske data.Dette betyr at hvis de opprinnelige dataene dine endres og dataene du ønsker å transponere også oppdateres, må du transponere dem på nytt ved å bruke Paste Special.
Dette er et kult triks du kan bruke til å transponere dataene og fortsatt koble dem til de originale cellene.
Anta at du har et datasett som ser slik ut:
Her er trinnene for å transponere dataene, men hold koblingen intakt:
- Velg datasettet (A1:E5).
- Dupliser den (Control + C, eller høyreklikk og velg Dupliser).
- Nå kan du lime inn de transponerte dataene til den nye plasseringen.I dette eksemplet vil jeg kopiere i G1:K5, så høyreklikk celle G1 og velg Lim inn spesielt.
- I dialogboksen Lim inn spesial klikker du på Lim inn lenke-knappen.Dette vil gi deg samme datasett, men her er cellene knyttet til det originale datasettet (f.eks. er G1 knyttet til A1, G2 er knyttet til A2 osv.).
- Med disse nylig kopierte dataene valgt, trykk Ctrl + H (eller gå til Hjem -> Rediger -> Finn og velg -> Erstatt).Dette åpner dialogboksen Finn og erstatt.
- I dialogboksen Finn og erstatt bruker du følgende kommandoer:
- I Finn hva:=
- Erstatt med: !@# (merk at jeg brukte !@# siden det er en unik kombinasjon av tegn og sannsynligvis ikke er en del av dataene dine. Du kan bruke et hvilket som helst slikt unikt tegnsett).
- Klikk Erstatt alle.Dette vil erstatte likhetene i formelen, og du vil ha !@# etterfulgt av cellereferansen i hver celle.
- Høyreklikk og kopier datasettet (eller bruk Ctrl+C).
- Velg en ny plassering, høyreklikk og velg Lim inn spesial.I dette eksemplet limer jeg det inn i celle G7.Du kan lime den inn hvor som helst du vil.
- I dialogboksen Lim inn spesial velger du Transponer og klikker OK.
- Kopier og lim inn disse nyopprettede transponerte dataene til stedet der de ble opprettet.
- Åpne nå dialogboksen Finn og erstatt igjen og erstatt !@# med =.
Dette vil gi deg de transponerte koblede dataene.De transponerte dataene oppdateres automatisk hvis du gjør endringer i det originale datasettet.
Merk: Siden A1 er tom i våre originale data, må du manuelt fjerne 1-en i G0.0-en vises når vi limer inn koblingen, fordi en kobling til en tom celle fortsatt returnerer 0.Du må også formatere det nye datasettet (du kan bare kopiere og lime inn formateringen fra det originale datasettet).
Transponer data ved å bruke Excel TRANSPOSE-funksjonen
Excel TRANSPOSE-funksjonen - som navnet antyder - kan brukes til å transponere data i Excel.
Anta at du har et datasett som ser slik ut:
Her er trinnene for å transponere det:
- Velg cellene der du vil transponere datasettet.Merk at du må velge det nøyaktige antallet celler som rådata.Så, for eksempel, hvis du har 2 rader og 3 kolonner, må du velge de 3 radene og 2 kolonnene med celler der du vil transponere dataene.I dette tilfellet, siden det er 5 rader og 5 kolonner, må du velge 5 rader og 5 kolonner.
- Skriv inn =TRANSPOSER(A1:E5) i den aktive cellen (skal være cellen øverst til venstre valgt, og trykk Control Shift Enter.
Dette vil transponere datasettet.
Her er det du trenger å vite om TRANSPOSE-funksjonen:
- Det er en array-funksjon, så du må bruke Control-Shift-Enter i stedet for bare Enter.
- Du kan ikke slette deler av resultatet.Du må slette hele utvalget av verdier som returneres av TRANSPOSE-funksjonen.
- Transponeringsfunksjonen kopierer bare verdien, ikke formatet.
Transponer data med Power Query
Power Query er et kraftig verktøy som lar deg raskt transponere data i Excel.
Strømforespørsel JaExcel 2016("Get and Transform" i "Data"-fanen), men hvis du brukerExcel 2013 eller 2010, må du installere det som et tillegg.
Anta at du har et datasett som ser slik ut:
Her er trinnene for å overføre disse dataene ved hjelp av Power Query:
i Excel 2016
Merk at siden den øverste venstre cellen i datasettet vårt er tom, får det et generisk navn Kolonne1 (vist nedenfor).Du kan fjerne denne cellen fra de transponerte dataene.
I Excel 2013/2010
I Excel 2013/10 må du installere Power Query som et tillegg.
Klikk herLast ned plugin og få installasjonsinstruksjoner.
Etter å ha installert Power Query, gå til Power Query -> Excel-data -> Fra tabell.
Dette åpner dialogboksen Opprett tabell.Følg nå de samme trinnene som vist i Excel 2016.
Du vil kanskje også like følgende Excel-veiledninger:
- Multipliser i Excel ved å bruke Paste Special.
- Sammenslå Excel-områder (med og uten skilletegn).
