Transpose Data Menggunakan Fungsi INDIRECT ADRESS

Pada 4 postingan terdahulu, kita sudah belajar mengenai cara transpose data excel dengan berbagai cara yang berbeda. Ya, semakin lama belajar excel memang semakin menantang untuk mencoba cara baru untuk tugas yang sama.

Cara ke-5 untuk  transpose data excel adalah menggunakan gabungan rumus fungsi INDIRECT, ADDRESS, COLUMN dan ROW. Jangan terlalu dipusingkan dengan penggunaan 4 fungsi dalam satu formula. Jika kita memahami konsepnya, hal ini tidak terlalu rumit.

Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string address, misalnya jika kita mengetikan formula =INDIRECT(“A1”) maka kita akan mendapatkan nilai dari sel A1. Perhatikan “A1” dalam tanda kutip menandakan A1 adalah string, bukan referensi A1, sehingga jika dalam sel B1 berisi text “A1” kemudian kita menuliskan rumus berikut =INDIRECT(B1) maka akan diperoleh nilai dari A1, karena B1 tanpa tanda kutip menandakan referensi, sehingga fungsi INDIRECT akan mencari nilai tidak langsung dari text address yang berada di dalam sel B1.



Fungsi ADDRESS berguna untuk mendapatkan alamat sel dengan mengacu pada nomor row dan nomor column.  Berikut contoh penulisan fungsi Address dan hasilnya:

=ADRESS(1,1)    --->  hasil =  A1   (baris ke-1,  kolom ke-1)
=ADRESS(2,1)    --->  hasil =  A2   (baris ke-2, kolom ke-1)
=ADRESS(1,2)    --->  hasil =  B1   (baris ke-1, kolom ke-2)

Pada saat mengetikan fungsi address, mungkin anda akan melihat hint parameter selain row_num dan column_num, hamun dalam hal ini jangan terlalu dihiraukan karena untuk transpose data hanya diperlukan 2 parameter seperti yang dicontohkan. Prinsip kerjanya hampir sama dengan transpose data menggunakan fungsi INDEX dan OFFSET, yaitu membalik row dan column.

Fungsi COLUMN dan ROW berguna untuk mendapatkan index kolom dan index baris dari referensi terpilih.

Secara sederhana, rumus untuk tranpose data dapat dituliskan sebagai berikut:

=INDIRECT(ADDRESS(COLUMN,ROW))

Selanjutnya mari kita praktekan dalam lembar kerja excel.

• Masih menggunakan data yang sama pada cara ke-1 s.d ke-4, ketikan rumus berikut pada sel D1

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

• Lalu copy sel D1 ke range D1:I2 sehingga kita sudah mendapatkan data transpose pada range D1:I2
• Kelemahan dari formula tersebut adalah hanya berlaku bila sel pertama pada range data asli memiliki nomor kolom dan nomor baris yang sama, misalnya A1, B2, C3, D4, E5 dan seterusnya. Coba test dengan melakukan insert colom pada kolom A atau insert Row pada baris 1, maka data transpose akan berubah dan akan kembali seperti semula jika sel pertama data asli berada pada sel A1, B2, C3 dan seterusnya.
• Lakukan modifikasi pada formula diatas sehingga bisa lebih fleksibel dengan mengetikan formula berikut pada sel D1

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

• Lalu copy formula dari sel D1 ke range D1:I2, lakukan test dengan insert kolom pada kolom A dan insert baris pada baris 1. Disini kita akan mendapatkan data transpose yang konsisten.

• Sampai pada tahap ini kita sudah berhasil melakukan transpose data menggunakan INDIRECT, ADDRESS, COLUMN dan ROW. 

Demikian bagian terakhir mengenai Lima cara untuk melakukan transpose atau merubah orientasi baris menjadi kolom dan sebaliknya pada data excel.  Jika pembaca memiliki metode lain silahkan berbagi dengan komentar pada postingan ini.

Semoga bermanfaat.
Belajar Excel..! Excellent..!

Artikel Terkait:





5 Cara Transpose Data Excel
Transpose Menggunakan Fungsi Array TRANSPOSE
Transpose Menggunakan Fungsi Index
Transpose Menggunakan fungsi OFFSET