Noskatieties video - kā transponēt datus programmā Excel
Ja jums ir datu kopa un vēlaties to transponēt programmā Excel (kas nozīmē rindu pārvēršanu kolonnās un kolonnas rindās), to izdarīt manuāli ir pilnīgs NĒ!
Transponējiet datus, izmantojot īpašo ielīmēšanu
Īpašā ielīmēšana var paveikt daudz pārsteidzošu lietu, un viena šāda lieta ir datu transponēšana programmā Excel.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Šiem datiem ir reģioni kolonnā un ceturkšņi pēc kārtas.
Tagad kāda iemesla dēļ, ja jums ir nepieciešams transponēt šos datus, lūk, kā to izdarīt, izmantojot ielīmēt īpašo:
- Atlasiet datu kopu (šajā gadījumā A1: E5).
- Kopējiet datu kopu (Control + C) vai ar peles labo pogu noklikšķiniet un atlasiet kopēt.
- Tagad jūs varat ielīmēt transponētos datus jaunā vietā. Šajā piemērā es vēlos kopēt G1: K5, tāpēc ar peles labo pogu noklikšķiniet uz šūnas G1 un atlasiet ielīmēt īpašu.
- Īpašā ielīmēšanas dialoglodziņā atzīmējiet transponēšanas opciju apakšējā labajā stūrī.
- Noklikšķiniet uz Labi.
Tas uzreiz kopētu un ielīmētu datus, bet tādā veidā, ka tie ir transponēti. Zemāk ir demonstrācija, kas parāda visu procesu.
Iepriekš redzamās darbības kopē vērtību, formulu (ja tāda ir), kā arī formātu. Ja vēlaties tikai kopēt vērtību, īpašā ielīmēšanas dialoglodziņā atlasiet “vērtība”.
Ņemiet vērā, ka kopētie dati ir statiski, un, veicot izmaiņas sākotnējā datu kopā, šīs izmaiņas netiks atspoguļotas transponētajos datos.
Ja vēlaties, lai šīs transponētās šūnas tiktu saistītas ar sākotnējām šūnām, varat apvienot funkciju Find & Replace un Paste Special.
Transponējiet datus, izmantojot īpašo ielīmēšanu un Atrast un aizstāt
Izmantojot tikai Paste Special, tiek iegūti statiski dati. Tas nozīmē, ka, ja mainās jūsu sākotnējie dati un vēlaties, lai arī transponētie dati tiktu atjaunināti, tad, lai tos transponētu, vēlreiz jāizmanto Paste Special.
Šeit ir foršs triks, ko varat izmantot, lai transponētu datus un joprojām tos saistītu ar sākotnējām šūnām.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Tālāk ir norādītas darbības, kā transponēt datus, bet saglabāt saites neskartas.
- Atlasiet datu kopu (A1: E5).
- Kopējiet to (Control + C vai ar peles labo pogu noklikšķiniet un atlasiet kopiju).
- Tagad jūs varat ielīmēt transponētos datus jaunā vietā. Šajā piemērā es vēlos kopēt G1: K5, tāpēc ar peles labo pogu noklikšķiniet uz šūnas G1 un atlasiet ielīmēt īpašu.
- Dialoglodziņā Īpašais ielīmēšana noklikšķiniet uz pogas Ielīmēt saiti. Tādējādi jūs iegūsit to pašu datu kopu, bet šeit šūnas ir saistītas ar sākotnējo datu kopu (piemēram, G1 ir saistīts ar A1, bet G2 ir saistīts ar A2 utt.).
- Kad ir atlasīti šie jaunie kopētie dati, nospiediet Control + H (vai dodieties uz Sākums -> Rediģēšana -> Atrast un atlasīt -> Aizstāt). Tiks atvērts dialoglodziņš Atrast un aizstāt.
- Dialoglodziņā Atrast un aizstāt izmantojiet šādas darbības:
- Atrast ko: =
- In Replace with:!@# (Ņemiet vērā, ka es izmantoju!@#, Jo tā ir unikāla rakstzīmju kombinācija, kas, visticamāk, nebūs jūsu datu sastāvdaļa. Varat izmantot jebkuru šādu unikālu rakstzīmju kopu).
- Noklikšķiniet uz Aizstāt visu. Tas aizstās vienādu ar formulu, un jums būs!@#, Kam seko šūnas atsauce katrā šūnā.
- Ar peles labo pogu noklikšķiniet un kopējiet datu kopu (vai izmantojiet Control + C).
- Atlasiet jaunu atrašanās vietu, ar peles labo pogu noklikšķiniet un atlasiet ielīmēt īpašu. Šajā piemērā es to ielīmēju šūnā G7. Jūs varat to ielīmēt jebkurā vietā.
- Dialoglodziņā Īpašā ielīmēšana atlasiet Transponēt un noklikšķiniet uz Labi.
- Kopējiet un ielīmējiet šos jaunizveidotos transponētos datus tajā, no kuras tie ir izveidoti.
- Tagad vēlreiz atveriet dialoglodziņu Atrast un aizstāt un nomainiet!@# Ar =.
Tādējādi jūs iegūsit saistītus datus, kas ir transponēti. Ja veicat izmaiņas sākotnējā datu kopā, transponētie dati tiks automātiski atjaunināti.
Piezīme. Tā kā mūsu sākotnējos datos A1 ir tukšs, G1 manuāli jāizdzēš 0. Kad mēs ielīmējam saites, parādās 0, jo saite uz tukšu šūnu joprojām atgriež 0. Turklāt jums ir jāformatē jaunā datu kopa (jūs varat vienkārši nokopēt formātu tikai no sākotnējās datu kopas).
Transponējiet datus, izmantojot Excel TRANSPOSE funkciju
Excel TRANSPOSE funkciju - kā norāda nosaukums - var izmantot datu transponēšanai programmā Excel.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Tālāk ir norādītas tās transponēšanas darbības.
- Atlasiet šūnas, kurās vēlaties pārvietot datu kopu. Ņemiet vērā, ka kā sākotnējie dati ir jāizvēlas precīzs šūnu skaits. Piemēram, ja jums ir 2 rindas un 3 slejas, jums jāizvēlas 3 rindas un 2 šūnu kolonnas, kurās vēlaties pārvietot datus. Tā kā šajā gadījumā ir 5 rindas un 5 kolonnas, jums jāizvēlas 5 rindas un 5 kolonnas.
- Aktīvajā šūnā (kurai vajadzētu būt atlases augšējā kreisajā šūnā) ievadiet = TRANSPOSE (A1: E5) un nospiediet Control Shift Enter.
Tas transponētu datu kopu.
Šeit ir dažas lietas, kas jums jāzina par funkciju TRANSPOSE:
- Tā ir masīva funkcija, tāpēc jums ir jāizmanto Control-Shift-Enter, nevis tikai Enter.
- Jūs nevarat izdzēst daļu no rezultāta. Jums ir jāizdzēš viss vērtību masīvs, ko atgriež funkcija TRANSPOSE.
- Transponēšanas funkcija tikai kopē vērtības, nevis formatējumu.
Transponējiet datus, izmantojot Power Query
Power Query ir spēcīgs rīks, kas ļauj ātri transponēt datus programmā Excel.
Power Query ir daļa no programmas Excel 2016 (cilnē Dati iegūt un pārveidot), bet, ja izmantojat programmu Excel 2013 vai 2010, tā jāinstalē kā pievienojumprogramma.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Tālāk ir norādīti soļi šo datu transponēšanai, izmantojot Power Query.
Programmā Excel 2016
- Atlasiet datus un dodieties uz Dati -> Iegūt un pārveidot -> No tabulas.
- Dialoglodziņā Izveidot tabulu pārliecinieties, vai diapazons ir pareizs, un noklikšķiniet uz Labi. Tiks atvērts dialoglodziņš Vaicājumu redaktors.
- Dialoglodziņā Vaicājumu redaktors atlasiet cilni Pārveidot.
- Cilnē Pārveidot dodieties uz tabulu -> Izmantot pirmo rindu kā galvenes -> Izmantot galvenes kā pirmo rindu. Tas nodrošina, ka pirmā rinda (kurā ir galvenes: Q1, Q2, Q3 un Q4) arī tiek uzskatīta par datiem un tiek transponēta.
- Noklikšķiniet uz Transponēt. Tas uzreiz transponētu datus.
- Noklikšķiniet uz Izmantot pirmo rindu kā galvenes. Tas tagad padarītu pirmo transponēto datu rindu kā galvenes.
- Dodieties uz Fails -> Aizvērt un ielādēt. Tādējādi tiks aizvērts Power Editor logs un tiks izveidota jauna lapa, kurā būs transponētie dati.
Ņemiet vērā: tā kā mūsu datu kopas augšējā kreisā šūna bija tukša, tā iegūst vispārēju nosaukumu Kolonna1 (kā parādīts zemāk). Šo šūnu varat izdzēst no transponētajiem datiem.
Programmā Excel 2013/2010
Programmā Excel 2013/10 ir jāinstalē Power Query kā pievienojumprogramma.
Noklikšķiniet šeit, lai lejupielādētu spraudni un saņemtu instalēšanas instrukcijas.
Kad esat instalējis Power Query, dodieties uz Power Query -> Excel dati -> No tabulas.
Tiks atvērts tabulas izveides dialoglodziņš. Tagad veiciet tās pašas darbības, kas parādītas programmā Excel 2016.
