Fungsi Transpose dalam Excel

Dari semasa ke semasa, pengguna Excel mungkin mempunyai tugas untuk menukar julat data yang mempunyai struktur mendatar kepada menegak. Proses ini dipanggil transposisi. Perkataan ini baru kepada kebanyakan orang, kerana dalam kerja PC biasa anda tidak perlu menggunakan operasi ini. Walau bagaimanapun, mereka yang perlu bekerja dengan jumlah data yang besar perlu tahu cara melakukannya. Hari ini kita akan bercakap dengan lebih terperinci tentang cara melaksanakannya, dengan fungsi apa, dan juga melihat beberapa kaedah lain secara terperinci.

Fungsi TRANSPOSE – alihkan julat sel dalam Excel

Salah satu kaedah transposisi jadual yang paling menarik dan berfungsi dalam Excel ialah fungsi TRANSP. Dengan bantuannya, anda boleh menukar julat data mendatar menjadi menegak, atau melakukan operasi terbalik. Mari lihat bagaimana untuk bekerja dengannya.

Sintaks fungsi

Sintaks untuk fungsi ini adalah sangat mudah: TRANSPOSE(susunan). Iaitu, kita perlu menggunakan hanya satu hujah, iaitu set data yang perlu ditukar kepada pandangan mendatar atau menegak, bergantung pada asalnya.

Memindahkan julat menegak sel (lajur)

Katakan kita mempunyai lajur dengan julat B2:B6. Ia boleh mengandungi kedua-dua nilai siap pakai dan formula yang mengembalikan hasil kepada sel ini. Ia tidak begitu penting bagi kami, transposisi adalah mungkin dalam kedua-dua kes. Selepas menggunakan fungsi ini, panjang baris akan sama dengan panjang lajur julat asal.

Urutan langkah untuk menggunakan formula ini adalah seperti berikut:

1. Pilih satu baris. Dalam kes kami, ia mempunyai panjang lima sel.
2. Selepas itu, gerakkan kursor ke bar formula, dan masukkan formula di sana =TRANSP(B2:B6).
3. Tekan kombinasi kekunci Ctrl + Shift + Enter.

Sememangnya, dalam kes anda, anda perlu menentukan julat yang biasa untuk jadual anda.

Memindahkan julat sel mendatar (baris)

Pada dasarnya, mekanisme tindakan adalah hampir sama dengan perenggan sebelumnya. Katakan kita mempunyai rentetan dengan koordinat mula dan akhir B10:F10. Ia juga boleh mengandungi nilai dan formula secara langsung. Mari kita buat lajur daripadanya, yang akan mempunyai dimensi yang sama seperti baris asal. Urutan tindakan adalah seperti berikut:

1. Pilih lajur ini dengan tetikus. Anda juga boleh menggunakan kekunci pada papan kekunci Ctrl dan anak panah ke bawah, selepas mengklik pada sel paling atas lajur ini.
2. Selepas itu kita tulis formula =TRANSP(B10:F10) dalam bar formula.
3. Kami menulisnya sebagai formula tatasusunan menggunakan kombinasi kekunci Ctrl + Shift + Enter.

Transposing dengan Tampal Khas

Pilihan transposisi lain yang mungkin adalah menggunakan fungsi Tampal Khas. Ini bukan lagi pengendali untuk digunakan dalam formula, tetapi ia juga merupakan salah satu kaedah popular untuk menukar lajur kepada baris dan sebaliknya.

Pilihan ini terdapat pada tab Laman Utama. Untuk mengaksesnya, anda perlu mencari kumpulan "Papan Klip", dan di sana cari butang "Tampal". Selepas itu, buka menu yang terletak di bawah pilihan ini dan pilih item "Transpose". Sebelum itu, anda perlu memilih julat yang anda ingin pilih. Akibatnya, kita akan mendapat julat yang sama, hanya sebaliknya yang dicerminkan.

3 Cara untuk Mengubah Jadual dalam Excel

Tetapi sebenarnya, terdapat lebih banyak cara untuk menukar lajur menjadi baris dan sebaliknya. Mari kita terangkan 3 kaedah yang boleh digunakan untuk menukar jadual dalam Excel. Kami membincangkan dua daripadanya di atas, tetapi kami akan memberikan beberapa lagi contoh supaya anda mendapat idea yang lebih baik tentang uXNUMXbuXNUMXbcara menjalankan prosedur ini.

Kaedah 1: Tampal Khas

Kaedah ini adalah yang paling mudah. Ia cukup untuk menekan beberapa butang, dan pengguna menerima versi transposed jadual. Mari kita berikan contoh kecil untuk lebih jelas. Katakan kita mempunyai jadual yang mengandungi maklumat tentang jumlah produk yang ada dalam stok pada masa ini, serta jumlah kos keseluruhannya. Jadual itu sendiri kelihatan seperti ini.

Kami melihat bahawa kami mempunyai pengepala dan lajur dengan nombor produk. Dalam contoh kami, pengepala mengandungi maklumat tentang produk apa, kosnya, jumlahnya dalam stok dan jumlah kos semua produk yang berkaitan dengan item ini yang ada dalam stok. Kami mendapat kos mengikut formula di mana kos didarab dengan kuantiti. Untuk menjadikan contoh lebih visual, mari jadikan tajuk hijau.

Tugas kami adalah untuk memastikan bahawa maklumat yang terkandung dalam jadual terletak secara mendatar. Iaitu, supaya lajur menjadi baris. Urutan tindakan dalam kes kami adalah seperti berikut:

1. Pilih julat data yang perlu kita putar. Selepas itu, kami menyalin data ini.
2. Letakkan kursor di mana-mana sahaja pada helaian. Kemudian klik pada butang kanan tetikus dan buka menu konteks.
3. Kemudian klik pada butang "Tampal Khas".

Selepas melengkapkan langkah-langkah ini, anda perlu mengklik pada butang "Transpose". Sebaliknya, tandai kotak di sebelah item ini. Kami tidak menukar tetapan lain, dan kemudian klik pada butang "OK".

Selepas melakukan langkah-langkah ini, kita ditinggalkan dengan jadual yang sama, hanya baris dan lajurnya disusun secara berbeza. Juga ambil perhatian bahawa sel yang mengandungi maklumat yang sama diserlahkan dalam warna hijau. Soalan: apakah yang berlaku kepada formula yang berada dalam julat asal? Lokasi mereka telah berubah, tetapi mereka sendiri kekal. Alamat sel hanya bertukar kepada yang terbentuk selepas transposisi.

Tindakan yang hampir sama perlu dilakukan untuk menukar nilai, bukan formula. Dalam kes ini, anda juga perlu menggunakan menu Tampal Khas, tetapi sebelum itu, pilih julat data yang mengandungi nilai. Kami melihat bahawa tetingkap Tampal Khas boleh dipanggil dalam dua cara: melalui menu khas pada reben atau menu konteks.

Kaedah 2. Fungsi TRANSP dalam Excel

Malah, kaedah ini tidak lagi digunakan secara aktif seperti pada awal kemunculan program hamparan ini. Ini kerana kaedah ini jauh lebih rumit daripada menggunakan Paste Special. Walau bagaimanapun, ia mendapati penggunaannya dalam mengautomasikan transposisi jadual.

Selain itu, fungsi ini terdapat dalam Excel, jadi adalah penting untuk mengetahui tentangnya, walaupun ia tidak lagi digunakan hampir. Terdahulu kita mempertimbangkan prosedur, bagaimana untuk bekerja dengannya. Sekarang kita akan menambah pengetahuan ini dengan contoh tambahan.

1. Pertama, kita perlu memilih julat data yang akan digunakan untuk menukar jadual. Anda hanya perlu memilih kawasan sebaliknya. Sebagai contoh, dalam contoh ini kita mempunyai 4 lajur dan 6 baris. Oleh itu, adalah perlu untuk memilih kawasan dengan ciri bertentangan: 6 lajur dan 4 baris. Gambar menunjukkannya dengan sangat baik.
2. Selepas itu, kami segera mula mengisi sel ini. Adalah penting untuk tidak membuang pilihan secara tidak sengaja. Oleh itu, anda mesti menentukan formula secara langsung dalam bar formula.
3. Seterusnya, tekan kombinasi kekunci Ctrl + Shift + Enter. Ingat bahawa ini ialah formula tatasusunan, kerana kami sedang bekerja dengan set data yang besar sekali gus, yang akan dipindahkan ke set sel besar yang lain.

Selepas kami memasukkan data, kami menekan kekunci Enter, selepas itu kami mendapat hasil berikut.

Kami melihat bahawa formula tidak dipindahkan ke jadual baharu. Pemformatan juga hilang. Poeto

Semua ini perlu dilakukan secara manual. Di samping itu, ingat bahawa jadual ini berkaitan dengan yang asal. Oleh itu, sebaik sahaja beberapa maklumat ditukar dalam julat asal, pelarasan ini dibuat secara automatik pada jadual yang dipindah.

Oleh itu, kaedah ini sangat sesuai dalam kes di mana anda perlu memastikan bahawa jadual yang diubah suai dipautkan dengan yang asal. Jika anda menggunakan sisipan khas, kemungkinan ini tidak akan berlaku lagi.

jadual ringkasan

Ini adalah kaedah asas yang baru, yang memungkinkan bukan sahaja untuk menukar jadual, tetapi juga untuk menjalankan sejumlah besar tindakan. Benar, mekanisme transposisi akan agak berbeza berbanding dengan kaedah sebelumnya. Urutan tindakan adalah seperti berikut:

1. Mari kita buat jadual pangsi. Untuk melakukan ini, anda perlu memilih jadual yang perlu kami transpose. Selepas itu, pergi ke item "Sisipkan" dan cari "Jadual Pangsi" di sana. Kotak dialog seperti dalam tangkapan skrin ini akan muncul.
2. Di sini anda boleh menetapkan semula julat dari mana ia akan dibuat, serta membuat beberapa tetapan lain. Kami kini berminat terutamanya pada tempat jadual pangsi - pada helaian baharu.
3. Selepas itu, susun atur jadual pangsi akan dibuat secara automatik. Ia adalah perlu untuk menandakan di dalamnya barang-barang yang akan kita gunakan, dan kemudian mereka mesti dipindahkan ke tempat yang betul. Dalam kes kami, kami perlu mengalihkan item "Produk" ke "Nama Lajur", dan "Harga sekeping" ke "Nilai".
4. Selepas itu, jadual pangsi akhirnya akan dibuat. Bonus tambahan ialah pengiraan automatik nilai akhir.
5. Anda boleh menukar tetapan lain juga. Sebagai contoh, nyahtandai item "Harga sekeping" dan tandai item "Jumlah kos". Akibatnya, kami akan mempunyai jadual yang mengandungi maklumat tentang kos produk. Kaedah transposisi ini jauh lebih berfungsi daripada yang lain. Mari kita terangkan beberapa faedah jadual pangsi:

1. Automasi. Dengan bantuan jadual pangsi, anda boleh meringkaskan data secara automatik, serta menukar kedudukan lajur dan lajur sewenang-wenangnya. Untuk melakukan ini, anda tidak perlu melakukan sebarang langkah tambahan.
2. Interaktiviti. Pengguna boleh menukar struktur maklumat seberapa banyak kali yang dia perlukan untuk melaksanakan tugasnya. Sebagai contoh, anda boleh menukar susunan lajur, serta data kumpulan dengan cara sewenang-wenangnya. Ini boleh dilakukan seberapa banyak yang diperlukan oleh pengguna. Dan ia benar-benar mengambil masa kurang daripada satu minit.
3. Mudah untuk memformat data. Sangat mudah untuk mengatur jadual pangsi mengikut cara yang diingini oleh seseorang. Untuk melakukan ini, hanya buat beberapa klik tetikus.
4. Mendapat nilai. Jumlah besar formula yang digunakan untuk membuat laporan terletak dalam kebolehcapaian langsung seseorang dan mudah untuk disepadukan ke dalam jadual pangsi. Ini adalah data seperti penjumlahan, mendapatkan min aritmetik, menentukan bilangan sel, mendarab, mencari nilai terbesar dan terkecil dalam sampel yang ditentukan.
5. Keupayaan untuk mencipta carta ringkasan. Jika Jadual Pangsi dikira semula, carta berkaitannya dikemas kini secara automatik. Anda boleh membuat seberapa banyak carta yang anda perlukan. Kesemuanya boleh diubah untuk tugas tertentu dan ia tidak akan saling berkaitan.
6. Keupayaan untuk menapis data.
7. Anda boleh membina jadual pangsi berdasarkan lebih daripada satu set maklumat sumber. Oleh itu, fungsi mereka akan menjadi lebih hebat.

Benar, apabila menggunakan jadual pangsi, sekatan berikut mesti diambil kira:

1. Tidak semua maklumat boleh digunakan untuk menjana jadual pangsi. Sebelum ia boleh digunakan untuk tujuan ini, sel mesti dinormalisasi. Dengan kata mudah, perbetulkan. Keperluan mandatori: kehadiran baris pengepala, kepenuhan semua baris, kesamaan format data.
2. Data dikemas kini secara separa automatik. Untuk mendapatkan maklumat baharu dalam jadual pangsi, anda mesti klik pada butang khas.
3. Jadual pangsi mengambil banyak ruang. Ini boleh menyebabkan beberapa gangguan pada komputer. Juga, fail akan sukar dihantar melalui E-mel kerana ini.

Selain itu, selepas mencipta jadual pangsi, pengguna tidak mempunyai keupayaan untuk menambah maklumat baharu.