Če imate nabor podatkov in ga želite prenesti v Excel (To pomeni pretvoriti vrstice v stolpce in stolpce v vrstice), ročna izvedba je popolnoma nemogoča!
vsebina
Transponirajte podatke s posebnim lepljenjem
Paste Special lahko naredi veliko neverjetnih stvari, ena od njih je transponiranje podatkov v Excelu.
Recimo, da imate nabor podatkov, ki izgleda takole:
Podatki imajo regije v stolpcih in četrtine v vrsticah.
Zdaj iz nekega razloga, če morate te podatke prenesti, lahko uporabite posebno lepljenje na naslednji način:
S tem boste podatke takoj kopirali in prilepili, vendar na način, ki je bil prenesen.Spodaj je demo, ki prikazuje celoten postopek.
Zgoraj prikazani koraki kopirajo vrednosti, formule (če obstajajo) in oblikovanje.Če želite samo kopirati vrednost, v posebnem pogovornem oknu za lepljenje izberite "Vrednost".
Upoštevajte, da so kopirani podatki statični in če spremenite izvirni nabor podatkov, se te spremembe ne bodo odrazile v prenesenih podatkih.
Če želite te transponirane celice povezati z izvirnimi celicami, lahko združite moč funkcije Najdi in zamenjaj s posebnim lepljenjem.
Prenesite podatke s posebnim lepljenjem ter jih poiščite in zamenjajte
Samo Paste Special vam daje statične podatke.To pomeni, da če se vaši izvirni podatki spremenijo in se posodobijo tudi podatki, ki jih želite prenesti, jih morate znova prenesti s posebnim lepljenjem.
To je kul trik, s katerim lahko prenesete podatke in jih še vedno povežete z izvirnimi celicami.
Recimo, da imate nabor podatkov, ki izgleda takole:
Tu so koraki za prenos podatkov, vendar ohranite povezavo nedotaknjeno:
- Izberite nabor podatkov (A1:E5).
- Podvojite ga (Ctrl + C ali desni klik in izberite Podvoji).
- Zdaj lahko prilepite transponirane podatke na novo lokacijo.V tem primeru želim kopirati v G1:K5, zato z desno tipko miške kliknite celico G1 in izberite Posebno lepljenje.
- V pogovornem oknu Posebno lepljenje kliknite gumb Prilepi povezavo.Tako boste dobili enak nabor podatkov, vendar so tukaj celice povezane z izvirnim naborom podatkov (npr. G1 je povezan z A1, G2 je povezan z A2 itd.).
- Z izbranimi na novo kopiranimi podatki pritisnite Control + H (ali pojdite na Domov -> Uredi -> Najdi in izberite -> Zamenjaj).To bo odprlo pogovorno okno Najdi in zamenjaj.
- V pogovornem oknu Najdi in zamenjaj uporabite naslednje ukaze:
- V Najdi kaj:=
- Zamenjajte z: !@# (upoštevajte, da sem uporabil !@#, ker je edinstvena kombinacija znakov in je malo verjetno, da bo del vaših podatkov. Uporabite lahko kateri koli tak edinstven nabor znakov).
- Kliknite Zamenjaj vse.To bo nadomestilo enake v formuli in v vsaki celici boste imeli !@#, ki mu bo sledila sklic na celico.
- Z desno tipko miške kliknite in kopirajte nabor podatkov (ali uporabite Control + C).
- Izberite novo lokacijo, z desno tipko miške kliknite in izberite Posebno lepljenje.V tem primeru ga prilepim v celico G7.Lahko ga prilepite kamor koli želite.
- V pogovornem oknu Posebno lepljenje izberite Transpose in kliknite V redu.
- Te na novo ustvarjene transponirane podatke kopirajte in prilepite na lokacijo, kjer so bili ustvarjeni.
- Zdaj znova odprite pogovorno okno Najdi in zamenjaj in zamenjajte !@# z =.
Tako boste dobili prenesene povezane podatke.Preneseni podatki se bodo samodejno posodobili, če boste spremenili izvirni nabor podatkov.
Opomba: Ker je A1 prazen v naših izvirnih podatkih, morate ročno odstraniti 1 v G0.0 se prikaže, ko prilepimo povezavo, ker povezava na prazno celico še vedno vrne 0.Prav tako morate formatirati nov nabor podatkov (lahko preprosto kopirate in prilepite oblikovanje iz izvirnega nabora podatkov).
Transponirajte podatke s funkcijo TRANSPOSE Excel
Excelovo funkcijo TRANSPOSE - kot že ime pove - lahko uporabite za transponiranje podatkov v Excelu.
Recimo, da imate nabor podatkov, ki izgleda takole:
Tu so koraki za prenos:
- Izberite celice, v katere želite prenesti nabor podatkov.Upoštevajte, da morate kot neobdelane podatke izbrati natančno število celic.Torej, na primer, če imate 2 vrstici in 3 stolpce, morate izbrati 3 vrstice in 2 stolpca celic, kamor želite prenesti podatke.V tem primeru, ker je 5 vrstic in 5 stolpcev, morate izbrati 5 vrstic in 5 stolpcev.
- V aktivno celico vnesite =TRANSPOSE(A1:E5) (izbrana bi morala biti zgornja leva celica in pritisnite Control Shift Enter.
To bo preneslo nabor podatkov.
Tukaj je tisto, kar morate vedeti o funkciji TRANSPOSE:
- To je funkcija matrike, zato morate namesto samo Enter uporabiti Control-Shift-Enter.
- Dela rezultata ne morete izbrisati.Izbrisati morate celoten niz vrednosti, ki jih vrne funkcija TRANSPOSE.
- Funkcija transponiranja kopira samo vrednost, ne pa formata.
Prenesite podatke s funkcijo Power Query
Power Query je zmogljivo orodje, ki vam omogoča hitro prenašanje podatkov v Excelu.
Power Query DaExcel 2016(»Pridobi in transformiraj« na zavihku »Podatki«), če pa uporabljateExcel 2013 ali 2010, ga morate namestiti kot dodatek.
Recimo, da imate nabor podatkov, ki izgleda takole:
Tu so koraki za prenos teh podatkov z uporabo Power Query:
v Excelu 2016
Upoštevajte, da ker je zgornja leva celica našega nabora podatkov prazna, dobi splošno ime Column1 (prikazano spodaj).To celico lahko odstranite iz transponiranih podatkov.
V Excelu 2013/2010
V Excelu 2013/10 morate namestiti Power Query kot dodatek.
Klikni tukajPrenesite vtičnik in pridobite navodila za namestitev.
Po namestitvi Power Query pojdite na Power Query -> Excelovi podatki -> Iz tabele.
To bo odprlo pogovorno okno Ustvari tabelo.Zdaj sledite istim korakom, kot je prikazano v Excelu 2016.
Morda vam bodo všeč tudi naslednje vadnice za Excel:
- Pomnožite v Excelu s posebnim lepljenjem.
- Povežite obsege Excela (z ločili in brez njih).
