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

Penggunaan Rumus OFFSET untuk Transpose Data Excel

Cara ke-4 untuk melakuan transpose / merubah orientasi kolom dan baris data excel adalah menggunakan rumus kombinasi fungsi OFFSET serta ROW dan COLUMN. Fungsi OFFSET ini berguna untuk mendapatkan nilai berdasarkan posisi relative sebuah range terhadap range acuan.

Adapun syntax dari fungsi OFFSET adalah sebagai berikut:

  =OFFSET(reference, rows, cols, [height], [width])

Tulisan ini tidak akan menjelaskan secara detail fungsi OFFSET dan parameter fungsinya, namun hanya akan menjelaskan kaitan fungsi OFFSET dengan proses transpose data. Untuk transpose data, parameter fungsi OFFSET yang diperlukan adalah reference, rows dan cols.





Reference = range acuan, bisa berupa sebuah sel atau beberapa sel. Dalam kasus transpose data, maka range acuan yang diperlukan adalah sebuah sel.
Rows=posisi relatif sel yang dicari nilainya terhadap sel acuan
Cols = posisi relatif sel yang dicari nilainya terhadap sel acuan
Selanjutnya mari kita praktekan kembali pada lembar kerja excel menggunakan tabel data asli seperti cara pertama.

• Menggunakan contoh kasus seperti pembahasan Transpose bagian pertama (Copy Paste Transpose), Bagian Kedua (TRANSPOSE) dan bagian ketiga (INDEX), Pada bagian ke-4 ini menggunakan fungsi OFFSET, Masukan rumus pada sel D1 sebagai berikut: 

=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))

• Copy sel D1 tersebut ke range D1:I12 Sehingga mendapatkan data transpose pada range D1:I2.
• Jika anda menginginkan format data transpose identik dengan format data asli, lakukan copy paste special format transpose dari data asli ke range D1:I10 dengan cara sebagai berikut: seleksi range data asli --> CTR+C  --> klik kanan cel D1 --> paste special --> pada dialog box tick formats dan ticks transpose --> Ok

• Dengan memperhatikan formula di atas, prinsip kerja formula OFFSET hampir sama dengan INDEX yaitu membalik parameter kolom dengan baris.

Demikian semoga bermanfaat

Belajar Excel..! Excellent

Artikel Terkait

5 Cara Transpose Data Excel

Penggunaan Formula Array TRANSPOSE

Transpose Data Menggunakan Rumus INDEX

Penggunaan Rumus INDEX untuk Transpose Data Excel

Pada postingan sebelumnya kita sudah belajar bagaimana melakukan transpose data excel menggunakan cara copy paste special transpose dan menggunakan rumus array transpose. Pada kesempatan ini mari kita lanjutkan pada pembahasan cara ke-3 yaitu transpose data excel menggunakan rumus yang sedikit lebih panjang, yaitu menggunakan rumus INDEX dikombinasikan
Fungsi INDEX berguna untuk mendapatkan nilai dari sel berdasarkan posisi sel tersebut dalam sebuah range referensi.

Adapun syntax dari fungsi INDEX adalah :  =INDEX(reference, row_num, [column_num],[area_num])

• Reference =range sebagai referensi, misalnya sebuah tabel yang kita gunakan sebagai data asli sebelum di-transpose
• Row_num = posisi nomor baris dalam referensi
• Column_num = posisi nomor kolom dalam referensi, bersifat opsional namun sangat penting dalam kaitan transpose data tabel
• Area_num = jumlah area dalam referensi, bersifat opsional dan jarang digunakan

Untuk mendapatkan nilai pada baris ke-1 dan kolom ke-2 pada sebuah referensi, kita dapat menuliskan rumus sbb:  =INDEX(referensi,1,2).

Sebaliknya, untuk mendapatkan nilai pada baris ke-2 dan kolom ke-1 kita dapat menuliskan rumus sbb:  =INDEX(referensi,2,1).

 Perhatikan penggunaan parameter row_num dan column_num, kita harus menuliskan row terlebih dahulu sebelum column. Dan jika dibalik antara row dan column maka kita mendapakan nilai transpose dari tabel data referensi.

Dengan bantuan fungsi COLUMN dan ROW  yang berguna untuk mendapatkan nomor kolom dan baris dari sebuah sel, maka kita dapat menyusun fungsi INDEX untuk melakukan transpose data tabel.
Baiklah supaya lebih jelas, mari kita praktekan pada lembar kerja excel.

• Dengan menggunakan data yang sama dengan contoh cara pertama, copy range A1:B6 --> klik kanan sel D1 --> paste special --> pada dialog box tick formats dan tick transpose --> klik OK.  Proses ini akan mengcopy transpose format dari data asli.

• Ketikan formula berikut pada sel D1 

=INDEX($A$1:$B$6,COLUMN(A1)-COLUMN($A$1)+1,ROW(A1)-ROW($A$1)+1)

• Perhatikan tanda dolar ($) digunakan untuk menandakan absolute reference supaya tidak berubah jika dicopy ke sel lainnya. Dalam hal ini sel A1 yang letaknya pada posisi baris ke-1 dan kolom ke-1 digunakan sebagai rujukan untuk menghitung posisi sel lainnya dalam formula.

• Sorot sel D1 --> klik kanan + copy atau CTR+C --> seleksi range D1:I2 --> klik kanan --> paste special --> pada dialog box tick Formulas --> Ok.  Langkah ini mengcopy formula dari sel D1 ke range D1:I2 yang tidak lain adalah tabel  transpose dari range A1:B6

• Berhasil...! Kita sudah membuat data transpose menggunakan rumus kombinasi fungsi INDEX, COLUMN dan ROW.

Sekian dan semoga bermanfaat:

Belajar Excel..! Excellent..!

Artikel Terkait:

• 5 Cara Transpose Data Excel
• Penggunaan Rumus TRANSPOSE Excel
• Transpose Tabel Menggunakan Fungsi INDIRECT ADDRESS