Tutorial menunjukkan cara yang berbeza untuk menukar baris ke lajur dalam Excel: formula, kod VBA, dan alat khas.

Transposing data dalam Excel adalah tugas yang biasa kepada banyak pengguna. Seringkali anda membina jadual yang kompleks hanya untuk menyedari bahawa ia masuk akal untuk memutarnya untuk analisis yang lebih baik atau pembentangan data dalam graf.

Dalam artikel ini, anda akan menemui beberapa cara untuk menukar baris ke lajur (atau lajur ke baris), mengikut mana yang anda panggil, ia adalah perkara yang sama :) penyelesaian ini berfungsi dalam semua versi Excel 2010 melalui Excel 365, meliputi banyak senario yang mungkin, dan menerangkan kesilapan yang paling biasa.

Tukar baris ke lajur dalam Excel menggunakan tampal khas

Katakan anda mempunyai dataset yang serupa dengan apa yang anda lihat di bahagian atas grafik di bawah. Nama -nama negara dianjurkan dalam lajur, tetapi senarai negara terlalu panjang, jadi kami lebih baik mengubah lajur ke baris untuk jadual yang sesuai dalam skrin:

Untuk menukar baris ke lajur dalam Excel, lakukan langkah -langkah ini:

1. Pilih data asal. Untuk dengan cepat memilih keseluruhan jadual, iaitu semua sel dengan data dalam spreadsheet, tekan rumah CTRL dan kemudian CTRL shift end .
2. Salin sel yang dipilih sama ada dengan mengklik kanan pemilihan dan memilih salinan dari menu konteks atau dengan menekan Ctrl c .
3. Pilih sel pertama julat destinasi.

   Pastikan untuk memilih sel yang jatuh di luar julat yang mengandungi data asal anda, supaya kawasan salinan dan kawasan tampal tidak bertindih. Sebagai contoh, jika anda kini mempunyai 4 lajur dan 10 baris, jadual yang ditukar akan mempunyai 10 lajur dan 4 baris.

4. Klik kanan sel destinasi dan pilih Tampal Khas dari menu konteks, kemudian pilih Transpose .

Untuk maklumat lanjut, sila lihat cara menggunakan Paste Special di Excel.

Nota. Jika data sumber anda mengandungi formula, pastikan anda menggunakan rujukan relatif dan mutlak bergantung kepada sama ada ia perlu diselaraskan atau tetap dikunci ke sel -sel tertentu.

Seperti yang anda lihat, ciri khas Paste membolehkan anda melakukan row ke lajur (atau lajur ke baris) transformasi secara harfiah dalam beberapa saat. Kaedah ini juga menyalin pemformatan data asal anda, yang menambah satu lagi hujah yang memihak kepadanya.

Walau bagaimanapun, pendekatan ini mempunyai dua kelemahan yang menghalangnya daripada dipanggil penyelesaian yang sempurna untuk memindahkan data dalam Excel:

• Ia tidak sesuai untuk berputar jadual Excel yang berfungsi sepenuhnya. Jika anda menyalin seluruh jadual dan kemudian buka dialog khas Paste , anda akan mendapati pilihan transpose dilumpuhkan. Dalam kes ini, anda perlu menyalin jadual tanpa tajuk lajur atau menukarnya ke julat terlebih dahulu.
• Tampal Khas > Transpose tidak menghubungkan jadual baru dengan data asal, jadi ia hanya sesuai untuk penukaran satu kali. Apabila sumber data berubah, anda perlu mengulangi proses dan memutar jadual baru. Tiada siapa yang mahu membuang masa mereka untuk menukar baris dan lajur yang sama berulang kali, bukan?

Cara Mengubah Jadual dan Pautkannya ke Data Asal

Mari lihat bagaimana anda boleh menukar baris ke lajur menggunakan teknik khas tampal yang biasa, tetapi sambungkan jadual yang dihasilkan ke dataset asal. Perkara terbaik mengenai pendekatan ini ialah apabila anda menukar data dalam jadual sumber, jadual terbalik akan mencerminkan perubahan dan kemas kini dengan sewajarnya.

1. Salin baris yang ingin anda tukar ke lajur (atau lajur yang akan ditukar kepada baris).
2. Pilih sel kosong dalam lembaran kerja yang sama atau lain.
3. Buka dialog khas Paste , seperti yang dijelaskan dalam contoh sebelumnya dan klik pautan paste di sudut kiri bawah:

   

   Anda akan mempunyai hasil yang serupa dengan ini:

   

4. Pilih jadual baru dan buka dialog Cari dan Ganti Excel (atau tekan Ctrl H untuk mendapatkan tab pengganti dengan segera).
5. Gantikan semua "=" aksara dengan "xxx" atau mana -mana watak lain yang tidak wujud di mana -mana sahaja dalam data sebenar anda.

   

   Ini akan menjadikan jadual anda menjadi sesuatu yang agak menakutkan, seperti yang anda lihat dalam tangkapan skrin di bawah, tetapi jangan panik, hanya 2 langkah lagi, dan anda akan mencapai hasil yang diinginkan.

   

6. Salin jadual dengan nilai "xxx", dan kemudian gunakan tampal khas> transpose ke flip lajur ke baris
7. Akhirnya, buka Dialog Cari dan Ganti sekali lagi untuk membalikkan perubahan, iaitu menggantikan semua "xxx" dengan "=" untuk memulihkan pautan ke sel -sel asal.

   

Ini adalah penyelesaian yang agak panjang tetapi elegan, bukan? Satu -satunya kelemahan pendekatan ini adalah bahawa pemformatan asal hilang dalam proses dan anda perlu memulihkannya secara manual (saya akan menunjukkan cara cepat untuk melakukan ini lebih lanjut dalam tutorial ini).

Cara Transpose Dalam Excel Menggunakan Formula

Cara yang lebih cepat untuk menukar lajur secara dinamik ke baris dalam Excel adalah dengan menggunakan formula transpose atau indeks/alamat. Seperti contoh sebelumnya, formula ini juga menyimpan sambungan ke data asal tetapi berfungsi sedikit berbeza.

Tukar baris ke lajur dalam Excel menggunakan fungsi transpose

Seperti namanya, fungsi transpose direka khas untuk memindahkan data dalam Excel:

Dalam contoh ini, kita akan menukar jadual lain yang menyenaraikan negara -negara kita mengikut populasi:

1. Kira bilangan baris dan lajur dalam jadual asal anda dan pilih bilangan sel kosong yang sama, tetapi ke arah yang lain.

   Sebagai contoh, jadual sampel kami mempunyai 7 lajur dan 6 baris, termasuk tajuk. Oleh kerana fungsi transpose akan mengubah lajur ke baris, kami memilih julat 6 lajur dan 7 baris.

2. Dengan sel -sel kosong yang dipilih, taipkan formula ini:

   =TRANSPOSE(A1:G6)

   

3. Oleh kerana formula kami perlu digunakan untuk pelbagai sel, tekan CTRL Shift Enter untuk menjadikannya formula array.

Voilà, lajur diubah menjadi baris, tepat seperti yang kita mahu:

Kelebihan fungsi transpose:

Manfaat utama menggunakan fungsi transpose ialah jadual yang diputar mengekalkan sambungan ke jadual sumber dan apabila anda menukar data sumber, jadual yang dipindahkan akan berubah dengan sewajarnya.

Kelemahan fungsi transpose:

• Pemformatan jadual asal tidak disimpan dalam jadual yang ditukar, seperti yang anda lihat dalam tangkapan skrin di atas.
• Sekiranya terdapat sel -sel kosong di dalam jadual asal, sel -sel yang dipindahkan akan mengandungi 0 sebaliknya. Untuk membetulkannya, gunakan transpose dalam kombinasi dengan fungsi IF seperti yang dijelaskan dalam contoh ini: bagaimana untuk transpose tanpa sifar.
• Anda tidak boleh mengedit mana -mana sel dalam jadual berputar kerana ia sangat bergantung kepada data sumber. Jika anda cuba menukar beberapa nilai sel, anda akan berakhir dengan ralat "anda tidak boleh mengubah sebahagian daripada array".

Membungkus, apa sahaja yang baik dan mudah digunakan fungsi transpose adalah, ia pasti tidak mempunyai fleksibiliti dan oleh itu mungkin bukan cara terbaik untuk pergi dalam banyak situasi.

Untuk maklumat lanjut, sila lihat fungsi transpose Excel dengan contoh.

Tukar baris ke lajur dengan fungsi tidak langsung dan alamat

Dalam contoh ini, akan menggunakan gabungan dua fungsi, yang agak rumit. Oleh itu, mari kita putar jadual yang lebih kecil supaya dapat memberi tumpuan lebih baik pada formula.

Katakan, anda mempunyai data dalam 4 lajur (a - d) dan 5 baris (1 - 5):

Untuk mempunyai lajur beralih ke baris, lakukan yang berikut:

1. Masukkan formula di bawah di sebelah kiri kebanyakan sel destinasi, katakan A7, dan tekan kekunci Enter :

   =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

2. Salin formula ke kanan dan ke bawah ke seberapa banyak baris dan lajur yang diperlukan dengan menyeret salib hitam kecil di sudut kanan bawah sel yang dipilih:

   

Itu sahaja! Dalam jadual yang baru dibuat, semua lajur dihidupkan ke baris.

Jika data anda bermula dalam beberapa baris selain 1 dan lajur selain A, anda perlu menggunakan formula yang lebih kompleks:

=INDIRECT(ADDRESS(COLUMN(A1) - COLUMN($A$1) ROW($A$1), ROW(A1) - ROW($A$1) COLUMN($A$1)))

Di mana A1 adalah sel paling kiri paling kiri dari jadual sumber anda. Juga, sila ingat penggunaan rujukan sel mutlak dan relatif.

Walau bagaimanapun, sel -sel yang dipindahkan kelihatan sangat jelas dan membosankan, berbanding dengan data asal:

Tetapi jangan kecewa, masalah ini boleh diperbaiki dengan mudah. Untuk memulihkan pemformatan asal, inilah yang anda lakukan:

• Salin jadual asal.
• Pilih jadual yang dihasilkan.
• Klik kanan jadual yang dihasilkan dan pilih Pilihan Tampal > Pemformatan .

Kelebihan : Formula ini menyediakan cara yang lebih fleksibel untuk menjadikan baris ke lajur dalam Excel. Ia membolehkan membuat apa -apa perubahan dalam jadual yang dipindahkan kerana anda menggunakan formula biasa, bukan formula array.

Kekurangan : Saya hanya dapat melihat satu - pemformatan data ordinal hilang. Walaupun, anda dapat dengan cepat memulihkannya, seperti yang ditunjukkan di atas.

Bagaimana formula ini berfungsi

Sekarang anda tahu bagaimana menggunakan kombinasi tidak langsung / alamat, anda mungkin ingin mendapatkan wawasan tentang formula yang sebenarnya dilakukan.

Seperti namanya, fungsi tidak langsung, digunakan untuk secara tidak langsung merujuk sel. Tetapi kuasa sebenar tidak langsung adalah bahawa ia boleh mengubah rentetan ke dalam rujukan, termasuk rentetan yang anda bina menggunakan fungsi lain dan nilai -nilai sel -sel lain. Dan inilah yang akan kita lakukan. Sekiranya anda mengikuti ini, anda akan memahami semua yang lain dengan mudah :)

Seperti yang anda ingat, kami telah menggunakan 3 lagi fungsi dalam formula - alamat, lajur dan baris.

Fungsi alamat memperoleh alamat sel oleh baris dan nombor lajur yang anda nyatakan, masing -masing. Sila ingat pesanan: Pertama - baris, kedua - lajur.

Dalam formula kami, kami membekalkan koordinat dalam urutan terbalik, dan inilah yang sebenarnya melakukan silap mata! Dalam erti kata lain, bahagian ini dari alamat formula (lajur (A1), baris (A1)) bertukar baris ke lajur, iaitu mengambil nombor lajur dan mengubahnya ke nombor baris, kemudian mengambil nombor baris dan menghidupkannya ke nombor lajur.

Akhirnya, fungsi tidak langsung mengeluarkan data yang diputar. Tidak ada yang mengerikan sama sekali, bukan?

Transpose data dalam Excel menggunakan makro VBA

Untuk mengautomasikan penukaran baris ke lajur dalam Excel, anda boleh menggunakan makro berikut:

Untuk menambah makro ke lembaran kerja anda, sila ikuti garis panduan yang diterangkan dalam cara memasukkan dan menjalankan kod VBA di Excel.

Nota. Transposing dengan VBA, mempunyai batasan 65536 elemen. Sekiranya array anda melebihi had ini, data tambahan akan dilepaskan secara senyap.

Cara menggunakan makro untuk menukar baris ke lajur

Dengan makro yang dimasukkan ke dalam buku kerja anda, lakukan langkah -langkah di bawah untuk memutar jadual anda:

1. Buka lembaran kerja sasaran, tekan Alt F8 , pilih Macro TranspoSecolumnSrows , dan klik Run .

   

2. Pilih julat di mana anda ingin menukar baris dan lajur dan klik OK :

   

3. Pilih sel kiri atas julat destinasi dan klik OK :

   

Nikmati hasilnya :)

Tukar lajur dan baris dengan alat transpose

Sekiranya anda perlu melakukan transformasi baris ke lajur secara teratur, anda mungkin sebenarnya mencari cara yang lebih cepat dan mudah. Nasib baik, saya mempunyai cara sedemikian dalam excel saya, dan begitu juga pengguna lain dari suite muktamad kami :)

Izinkan saya menunjukkan kepada anda cara menukar baris dan lajur dalam Excel secara harfiah dalam beberapa klik:

1. Pilih mana -mana sel tunggal dalam jadual anda, pergi ke tab Cabebits > Kumpulan Transform , dan klik butang Transpose .

   

2. Tetapan lalai berfungsi dengan baik dalam kebanyakan kes, jadi anda hanya klik Transpose tanpa mengubah apa -apa.

   Jika anda ingin menampal nilai sahaja atau membuat pautan ke data sumber untuk memaksa jadual berputar untuk mengemas kini secara automatik dengan setiap perubahan yang anda buat ke jadual asal, pilih pilihan yang sepadan.

   

Selesai! Jadual ini dipindahkan, pemformatan dipelihara, tidak ada manipulasi yang diperlukan:

Sekiranya anda ingin mencuba ini dan 70 alat profesional lain untuk Excel, saya menjemput anda untuk memuat turun versi percubaan suite kami. Terima kasih kerana membaca dan saya berharap dapat melihat anda di blog kami minggu depan!

Atas ialah kandungan terperinci Cara menukar baris dan lajur di Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!