Fungsi TRANSPOSE Excel

Apa Fungsi Transpose Lakukan di Excel?

Fungsi transpose di excel digunakan untuk memutar nilai atau mengganti nilai dari baris ke kolom dan kolom ke baris, karena fungsi ini mengambil array sel sebagai argumen sehingga untuk menjalankannya kita perlu menekan CSE atau Control + Shift + Enter, pertama-tama pilih ukuran tepat rentang yang perlu diubah urutannya, lalu gunakan rumus transposisi.

Sintaksis

Di bawah ini adalah Formula TRANSPOSE di excel.

Argumen

array: Wajib. Rentang sel yang ingin Anda ubah urutannya.

Ini adalah fungsi array .

Bagaimana cara menggunakan Fungsi TRANSPOSE di Excel? (dengan Contoh)

Anda dapat mendownload Template Excel Fungsi TRANSPOSE ini di sini - TRANSPOSE Function Excel Template

Contoh 1

Data asli yang ingin Anda transpos ada di A3: B8.

  • Langkah 1: Pilih rentang di mana Anda ingin nilai yang dialihkan, di sini, D6: I7.

  • Langkah 2: Sekarang, ketik Rumus TRANSPOSE di wilayah yang dipilih dan tekan CTRL + SHIFT + ENTER (atau COMMAND + SHIFT + ENTER di Mac).

= TRANSPOSE (A3: B7)

Anda akan mendapatkan output yang dialihkan dalam D6: I7 seperti yang ditunjukkan di bawah ini.

Dalam hal ini, D3: I4 adalah keluaran transposisi salin-tempel dan D6: I7 adalah keluaran transposisi.

Perhatikan bahwa Fungsi TRANSPOSE Excel adalah

{= TRANSPOSE (A3: B7)}

Tanda kurung kurawal {} yang ditampilkan di atas menunjukkan bahwa fungsi telah dimasukkan sebagai Rumus Array.

Sekarang, jika Anda harus mengubah nilai apa pun dalam data asli, katakan B8 = SUM (B3: B7) output yang dialihkan, di mana Anda telah menggunakan TRANSPOSE, akan diperbarui secara otomatis, tetapi tidak dalam kasus di mana Anda telah menggunakan copy- opsi tempel.

Ini juga dapat digunakan bersama dengan fungsi lain untuk mendapatkan keluaran yang diinginkan. Mari kita lihat beberapa contoh tentang ini.

Contoh # 2

Misalkan Anda memiliki daftar siswa dan nilai mereka seperti yang ditunjukkan di bawah ini. Beberapa siswa tidak memberikan ujian. Oleh karena itu, kolom nilai dikosongkan untuk siswa tersebut.

Anda ingin mengubah urutan data ini, dan saat sel kosong, Anda ingin meletakkan "Absen" di sel itu.

Anda dapat melakukannya dengan menggunakan Transpose bersama dengan fungsi IF di excel. Anda dapat menggunakan Rumus TRANSPOSE berikut di excel untuk melakukannya:

= TRANSPOSE (JIKA (B3: D10 = ””, ”ABSEN”, B3: D10))

'IF (B3: D10 = "", "ABSENT"' berarti bahwa jika ada sel dalam rentang B3: D10 kosong, itu akan menempatkan "Absen" sebagai gantinya. Ini akan mengubah urutan rentang yang diberikan.

Misalkan dalam daftar ini, jika ada siswa yang mendapat nilai kurang dari 70 atau belum muncul dalam ujian dianggap GAGAL, ​​Anda dapat mengubah Formula TRANSPOSE sebagai:

= TRANSPOSE (JIKA (B3: D10 <70, "FAIL", B3: D10)

dan Anda akan mendapatkan hasil akhir seperti yang ditunjukkan di bawah ini.

Contoh # 3

Terkadang ada kebutuhan untuk menambahkan beberapa karakter ke yang sudah ada bersama dengan transpose. Misalkan Anda memiliki daftar ID di B4: B7 seperti yang ditunjukkan di bawah ini.

Anda ingin mengubah urutan id dan menambahkan awalan "ID" ke dalamnya. Anda dapat melakukannya dengan menggunakan Formula di excel:

= TRANSPOSE (“ID” & B4: B7)

Ini akan mentranspos data dan menambahkan awalan ke setiap konten sel.

Anda juga dapat menambahkan nilai apapun sebagai sufiks menggunakan Rumus TRANSPOSE di atas. Ini mungkin berguna saat menggunakan fungsi Transpose untuk menggabungkan kata-kata yang diberikan dalam baris berbeda ke dalam satu sel. Untuk melakukannya, Anda dapat mengikuti langkah-langkah berikut:

  • Ketik sintaks: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Jangan tekan Enter.
  • Pilih fungsi excel transpose seperti yang ditunjukkan di bawah ini.

  • Tekan tombol F9.

  • Hapus {} dari rumus

  • Tekan enter.

Ini akan menggabungkan sel dalam satu sel.

Contoh # 4

Misalkan Anda memiliki data perpustakaan dimana beberapa mata pelajaran disusun di rak, dan terdapat rak yang berbeda seperti gambar di bawah ini.

Anda mungkin ingin mengambil lokasi dari salah satu subjek (diberikan dalam H3: H11) menggunakan data ini (B4: E6).

Anda dapat melakukannya menggunakan sintaks

= INDEKS ($ B $ 4: $ B $ 6, MATCH (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)), 0))

Mari kita lihat bagaimana Formula TRANSPOSE ini bekerja.

  • - ($ C $ 4: $ E $ 6 =)

Ketika diberikan dalam H3, itu akan menjadi - ($ C $ 4: $ E $ 6 = H3).

Ini membentuk larik 1 dan 0 untuk menunjukkan ada atau tidak adanya nilai. Untuk Biologi di H3 akan membentuk array sebagai berikut: {1,0,0; 0,0,0; 0,0,0}

  • TRANSPOSE (KOLOM ($ C $ 4: $ E $ 6) ^ 0))

Ini membuat larik 3 baris dalam satu kolom, dan pangkat 0 memastikan bahwa angkanya diubah menjadi 1. Output dari fungsi ini adalah {1,1,1}

  • MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0))

MMULT di excel mengalikan output dari A dan B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) memberikan keluaran {1; 0; 0}

  • PERTANDINGAN (1, MMULT (), 0)

Ini akan mencocokkan output C dengan 1. MATCH (1, {1; 0; 0}, 0) mengembalikan posisi 1.

  • INDEKS ($ B $ 4: $ B $ 6, MATCH (, 0))

Ini akan mengidentifikasi nilai sel yang fungsi kecocokannya menentukan posisinya. INDEX ($ B $ 4: $ B $ 6, 1) akan mengembalikan A1.

Demikian pula untuk "Geografi" sebagai subjek, ini akan menjadi:

  1. - ($ C $ 4: $ E $ 6 = D6) mengembalikan {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)) kembali ke {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) mengembalikan {0; 0; 1}
  4. MATCH (1, {0; 0; 1}, 0) mengembalikan 3
  5. INDEX ($ B $ 4: $ B $ 6, 3) menghasilkan A3.

Hal-hal untuk diingat

  1. Opsi salin-tempel membuat duplikat.
  2. Fungsi ini menghubungkan data ke sumber.
  3. Jika jumlah baris dan kolom yang dipilih tidak sama dengan kolom dan baris dari data sumber, itu akan memberikan kesalahan #VALUE
  4. Setelah dimasukkan, setiap sel yang merupakan bagian dari fungsi ini tidak dapat diubah.
  5. Tekan CTRL + SHIFT + ENTER untuk memasukkan rumus di excel.