Fungsi Transpose di Excel

Dari waktu ke waktu, pengguna Excel mungkin memiliki tugas untuk mengubah rentang data yang memiliki struktur horizontal menjadi vertikal. Proses ini disebut transposisi. Kata ini baru bagi kebanyakan orang, karena dalam pekerjaan PC normal Anda tidak harus menggunakan operasi ini. Namun, mereka yang harus bekerja dengan data dalam jumlah besar perlu mengetahui cara melakukannya. Hari ini kita akan berbicara lebih detail tentang cara melakukannya, dengan fungsi apa, dan juga melihat beberapa metode lain secara rinci.

Fungsi TRANSPOSE – mengubah rentang sel di Excel

Salah satu metode transposisi tabel yang paling menarik dan fungsional di Excel adalah fungsi TRANSP. Dengan bantuannya, Anda dapat mengubah rentang data horizontal menjadi vertikal, atau melakukan operasi sebaliknya. Mari kita lihat bagaimana bekerja dengannya.

Sintaks fungsi

Sintaks untuk fungsi ini sangat sederhana: TRANSPOSE (array). Artinya, kita hanya perlu menggunakan satu argumen, yaitu kumpulan data yang perlu dikonversi ke tampilan horizontal atau vertikal, tergantung pada asalnya.

Mentranspos rentang vertikal sel (kolom)

Misalkan kita memiliki kolom dengan rentang B2:B6. Mereka dapat berisi nilai dan formula siap pakai yang mengembalikan hasil ke sel-sel ini. Itu tidak begitu penting bagi kami, transposisi dimungkinkan dalam kedua kasus. Setelah menerapkan fungsi ini, panjang baris akan sama dengan panjang kolom rentang asli.

Urutan langkah-langkah untuk menggunakan rumus ini adalah sebagai berikut:

1. Pilih satu baris. Dalam kasus kami, ia memiliki panjang lima sel.
2. Setelah itu, pindahkan kursor ke bilah rumus, dan masukkan rumus di sana =TRANSP(B2:B6).
3. Tekan kombinasi tombol Ctrl + Shift + Enter.

Secara alami, dalam kasus Anda, Anda perlu menentukan rentang yang khas untuk tabel Anda.

Mentranspos rentang sel horizontal (baris)

Pada prinsipnya, mekanisme aksinya hampir sama dengan paragraf sebelumnya. Misalkan kita memiliki string dengan koordinat awal dan akhir B10:F10. Itu juga dapat berisi nilai dan rumus langsung. Mari kita buat kolom darinya, yang akan memiliki dimensi yang sama dengan baris aslinya. Urutan tindakannya adalah sebagai berikut:

1. Pilih kolom ini dengan mouse. Anda juga dapat menggunakan tombol pada keyboard Ctrl dan panah bawah, setelah mengklik sel paling atas kolom ini.
2. Setelah itu kita tulis rumusnya =TRANSP(B10:F10) di bilah rumus.
3. Kami menulisnya sebagai rumus array menggunakan kombinasi tombol Ctrl + Shift + Enter.

Transpose dengan Tempel Spesial

Opsi transposisi lain yang mungkin adalah menggunakan fungsi Tempel Spesial. Ini bukan lagi operator yang digunakan dalam rumus, tetapi juga merupakan salah satu metode populer untuk mengubah kolom menjadi baris dan sebaliknya.

Opsi ini ada di tab Beranda. Untuk mengaksesnya, Anda perlu menemukan grup "Clipboard", dan di sana temukan tombol "Tempel". Setelah itu, buka menu yang terletak di bawah opsi ini dan pilih item "Transpose". Sebelum itu, Anda harus memilih rentang yang ingin Anda pilih. Akibatnya, kita akan mendapatkan rentang yang sama, hanya kebalikannya yang dicerminkan.

3 Cara Transpose Tabel di Excel

Namun nyatanya, ada lebih banyak cara untuk mengubah kolom menjadi baris dan sebaliknya. Mari kita jelaskan 3 metode yang dengannya kita dapat mentranspos tabel di Excel. Kami membahas dua di antaranya di atas, tetapi kami akan memberikan beberapa contoh lagi sehingga Anda mendapatkan ide yang lebih baik tentang uXNUMXbuXNUMXbbagaimana melakukan prosedur ini.

Metode 1: Tempel Spesial

Metode ini adalah yang paling sederhana. Cukup dengan menekan beberapa tombol, dan pengguna menerima versi tabel yang diubah. Mari kita beri contoh kecil untuk kejelasan yang lebih besar. Misalkan kita memiliki tabel yang berisi informasi tentang berapa banyak produk yang saat ini tersedia, serta berapa biaya totalnya. Tabelnya sendiri terlihat seperti ini.

Kami melihat bahwa kami memiliki header dan kolom dengan nomor produk. Dalam contoh kita, header berisi informasi tentang produk apa, berapa harganya, berapa stoknya, dan berapa total biaya semua produk yang terkait dengan item yang ada dalam stok ini. Kami mendapatkan biaya sesuai dengan rumus di mana biaya dikalikan dengan kuantitas. Untuk membuat contoh lebih visual, mari buat header berwarna hijau.

Tugas kita adalah memastikan bahwa informasi yang terkandung dalam tabel terletak secara horizontal. Artinya, agar kolom menjadi baris. Urutan tindakan dalam kasus kami adalah sebagai berikut:

1. Pilih rentang data yang perlu kita putar. Setelah itu, kami menyalin data ini.
2. Tempatkan kursor di mana saja pada lembar. Kemudian klik tombol kanan mouse dan buka menu konteks.
3. Kemudian klik tombol "Tempel Spesial".

Setelah menyelesaikan langkah-langkah ini, Anda perlu mengklik tombol "Transpose". Sebaliknya, centang kotak di sebelah item ini. Kami tidak mengubah pengaturan lain, dan kemudian klik tombol "OK".

Setelah melakukan langkah-langkah ini, kita dibiarkan dengan tabel yang sama, hanya baris dan kolomnya yang diatur berbeda. Perhatikan juga bahwa sel yang berisi informasi yang sama disorot dengan warna hijau. Pertanyaan: apa yang terjadi dengan rumus yang ada di kisaran aslinya? Lokasi mereka telah berubah, tetapi mereka sendiri tetap. Alamat sel hanya berubah menjadi yang terbentuk setelah transposisi.

Tindakan yang hampir sama perlu dilakukan untuk mentranspos nilai, bukan rumus. Dalam hal ini, Anda juga perlu menggunakan menu Tempel Spesial, tetapi sebelum itu, pilih rentang data yang berisi nilai. Kita melihat bahwa jendela Tempel Spesial dapat dipanggil dengan dua cara: melalui menu khusus pada pita atau menu konteks.

Metode 2. Fungsi TRANSP di Excel

Bahkan, metode ini tidak lagi digunakan seaktif pada awal kemunculan program spreadsheet ini. Hal ini dikarenakan cara ini jauh lebih rumit daripada menggunakan Paste Special. Namun, ia menemukan penggunaannya dalam mengotomatisasi transposisi tabel.

Juga, fungsi ini ada di Excel, jadi sangat penting untuk mengetahuinya, meskipun hampir tidak digunakan lagi. Sebelumnya kami mempertimbangkan prosedurnya, cara bekerja dengannya. Sekarang kita akan melengkapi pengetahuan ini dengan contoh tambahan.

1. Pertama, kita perlu memilih rentang data yang akan digunakan untuk mentranspos tabel. Anda hanya perlu memilih area sebaliknya. Misalnya, dalam contoh ini kita memiliki 4 kolom dan 6 baris. Oleh karena itu, perlu untuk memilih area dengan karakteristik yang berlawanan: 6 kolom dan 4 baris. Gambar menunjukkannya dengan sangat baik.
2. Setelah itu, kita langsung mulai mengisi sel ini. Penting untuk tidak menghapus pilihan secara tidak sengaja. Oleh karena itu, Anda harus menentukan rumus secara langsung di bilah rumus.
3. Selanjutnya, tekan kombinasi tombol Ctrl + Shift + Enter. Ingat bahwa ini adalah rumus array, karena kita bekerja dengan kumpulan data yang besar sekaligus, yang akan ditransfer ke kumpulan sel besar lainnya.

Setelah kita memasukkan data, kita tekan tombol Enter, setelah itu kita mendapatkan hasil sebagai berikut.

Kami melihat bahwa rumus tidak dipindahkan ke tabel baru. Pemformatan juga hilang. puisi

Semua ini harus dilakukan secara manual. Selain itu, ingat bahwa tabel ini terkait dengan yang asli. Oleh karena itu, segera setelah beberapa informasi diubah dalam rentang asli, penyesuaian ini secara otomatis dilakukan pada tabel yang dialihkan.

Oleh karena itu, metode ini sangat cocok dalam kasus di mana Anda perlu memastikan bahwa tabel yang dialihkan terkait dengan yang asli. Jika Anda menggunakan sisipan khusus, kemungkinan ini tidak lagi.

tabel ringkasan

Ini adalah metode baru yang fundamental, yang memungkinkan tidak hanya untuk mengubah tabel, tetapi juga untuk melakukan sejumlah besar tindakan. Benar, mekanisme transposisi akan sedikit berbeda dibandingkan dengan metode sebelumnya. Urutan tindakannya adalah sebagai berikut:

1. Mari kita buat tabel pivot. Untuk melakukan ini, Anda perlu memilih tabel yang perlu kita transpose. Setelah itu, buka item "Sisipkan" dan cari "Tabel Pivot" di sana. Kotak dialog seperti yang ada di tangkapan layar ini akan muncul.
2. Di sini Anda dapat menetapkan kembali rentang dari mana itu akan dibuat, serta membuat sejumlah pengaturan lainnya. Kami sekarang terutama tertarik pada tempat tabel pivot – pada lembar baru.
3. Setelah itu, layout tabel pivot akan otomatis dibuat. Penting untuk menandai di dalamnya barang-barang yang akan kita gunakan, dan kemudian mereka harus dipindahkan ke tempat yang benar. Dalam kasus kami, kami perlu memindahkan item "Produk" ke "Nama Kolom", dan "Harga per Potongan" ke "Nilai".
4. Setelah itu, tabel pivot akhirnya akan dibuat. Bonus tambahan adalah perhitungan otomatis dari nilai akhir.
5. Anda juga dapat mengubah pengaturan lainnya. Misalnya, hapus centang item "Harga per potong" dan centang item "Total biaya". Akibatnya, kita akan memiliki tabel yang berisi informasi tentang berapa biaya produk. Metode transposisi ini jauh lebih fungsional daripada yang lain. Mari kita jelaskan beberapa manfaat tabel pivot:

1. Otomatisasi. Dengan bantuan tabel pivot, Anda dapat meringkas data secara otomatis, serta mengubah posisi kolom dan kolom secara sewenang-wenang. Untuk melakukan ini, Anda tidak perlu melakukan langkah tambahan apa pun.
2. Interaktivitas. Pengguna dapat mengubah struktur informasi sebanyak yang dia butuhkan untuk melakukan tugasnya. Misalnya, Anda dapat mengubah urutan kolom, serta mengelompokkan data secara arbitrer. Ini dapat dilakukan sebanyak yang dibutuhkan pengguna. Dan itu benar-benar membutuhkan waktu kurang dari satu menit.
3. Memformat data dengan mudah. Sangat mudah untuk mengatur tabel pivot sesuai keinginan seseorang. Untuk melakukan ini, cukup lakukan beberapa klik mouse.
4. Mendapatkan nilai. Banyaknya formula yang digunakan untuk membuat laporan terletak di aksesibilitas langsung seseorang dan mudah diintegrasikan ke dalam tabel pivot. Ini adalah data seperti penjumlahan, mendapatkan mean aritmatika, menentukan jumlah sel, mengalikan, menemukan nilai terbesar dan terkecil dalam sampel yang ditentukan.
5. Kemampuan untuk membuat grafik ringkasan. Jika PivotTable dihitung ulang, bagan yang terkait akan diperbarui secara otomatis. Dimungkinkan untuk membuat grafik sebanyak yang Anda butuhkan. Semuanya dapat diubah untuk tugas tertentu dan tidak akan saling berhubungan.
6. Kemampuan untuk menyaring data.
7. Dimungkinkan untuk membuat tabel pivot berdasarkan lebih dari satu kumpulan informasi sumber. Karena itu, fungsionalitasnya akan menjadi lebih besar.

Benar, saat menggunakan tabel pivot, batasan berikut harus diperhitungkan:

1. Tidak semua informasi dapat digunakan untuk menghasilkan tabel pivot. Sebelum mereka dapat digunakan untuk tujuan ini, sel-sel harus dinormalisasi. Dengan kata sederhana, lakukan dengan benar. Persyaratan wajib: kehadiran baris header, kepenuhan semua baris, kesetaraan format data.
2. Data diperbarui semi-otomatis. Untuk mendapatkan informasi baru di tabel pivot, Anda harus mengklik tombol khusus.
3. Tabel pivot memakan banyak ruang. Hal ini dapat menyebabkan beberapa gangguan pada komputer. Juga, file akan sulit untuk dikirim melalui E-mail karena hal ini.

Juga, setelah membuat tabel pivot, pengguna tidak memiliki kemampuan untuk menambahkan informasi baru.