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

Penggunaan Rumus TRANSPOSE Excel

Tulisan ini merupakan bagian kedua, kelanjutan dari postingan sebelumnya perihal 5 Cara Transpose Excel. Pada bagian pertama, kita sudah belajar merubah orientasi data baris dan kolom excel dengan cara copy paste special untuk menghasilkan data transpose baik yang tidak link atau link ke data awal. Nah, pada kesempatan ini kita akan belajar excel bagaimana menggunakan rumus TRANSPOSE untuk melakukan tugas yang sama seperti contoh pertama. Bedanya penggunaan fungsi TRANSPOSE pasti menghasilkan data yang link ke data awal.




Dengan menggunakan contoh kasus yang sama seperti bagian pertama, mari kita praktekan cara penggunaan rumus TRANSPOSE pada lembar kerja excel.

• Langkah pertama alangkah baiknya copy special transpose dahulu data tabel seperti pada cara pertama, tetapi hanya formatnya saja dengan cara --> seleksi range A1:B6 --> CTR+C --> klik kanan pada  sel D1 --> Paste Special --> pada dialog box  tick Formats dan tick Transpose --> klik Ok

• Setelah langkah pertama selesai, otomatis akan terseleksi range baru yang merupakan format data tabel yang sudah di-transpose.

• Dalam kondisi range data yang sudah ditranspose terseleksi (range D1:I2), selanjutnya ketikan rumus berikut:  =TRANSPOSE(A1:B6)   --> lalu tekan CTR+SHIFT+ENTER 

Sampai disini transpose data menggunakan fungsi TRANSPOSE sudah selesai. Perhatikan bahwa formulanya pada masing-masing sel pada range D1:I2 sama persis. Karena merupakan fungsi Array, fungsi TRANSPOSE akan berada dalam tanda kurung kurawal {} (curly braces) seperti ini :  {=TRANSPOSE(A1:B6)}. jangan mengetik sendiri tanda kurung kurawal tersebut karena hal tersebut tidak akan berguna, tanda kurung kurawal untuk formula array yang benar hanya dapat diperoleh dengan menekan CTR+SHIFT+ENTER. Pembahasan lebih detail perihal formula array akan saya tulis dalam postingan lainnya.

Demikian Semoga bermanfaat, Salam

Belajar Excel...!  Excellent..!

Artikel Terkait:

• 5 Cara Transpose Data Excel
• 3 Fitur Canggih Excel Yang Sangat Membantu Pekerjaan Anda
• VLOOKUP : Rumus Jitu Yang Wajib Dikuasai

INILAH 5 CARA TRANSPOSE DATA EXCEL

Proses Transpose atau merubah orientasi data excel dari kolom (vertical) menjadi baris (horizontal) atau sebaliknya seringkali diperlukan, entah itu dengan tujuan untuk memudahkan dalam membaca/menganalisa data ataupun penyusunan source data untuk pembuatan grafik. Bagi sahabat Belajar Excel, Ada beberapa cara / rumus transpose di excel yaitu:

1. Dengan cara Paste Special – Transpose
2. Menggunakan Rumus TRANSPOSE
3. Menggunakan Kombinasi Rumus INDEX, COLUMN dan ROW
4. Menggunakan Kombinasi Rumus OFFSET, COLUMN, dan ROW
5. Menggunakan kombinasi Rumus INDIRECT, ADDRESS, COLUMN, dan ROW




Supaya postingan ini tidak terlalu panjang maka pada kesempatan ini yang akan kita bahas adalah cara transpose dengan Copy - Paste Special.

Sedangkan transpose Data Excel menggunakan Formula akan saya posting dalam kesempatan yang lain. :-) sehingga ada 5 postingan membahas Tehnik Transpose data excel

1. Paste Special Transpose

Metode ini merupakan cara yang paling umum dan sederhana.

Anggaplah kita memiliki data pada range A1:B6 seperti ilustrasi di atas. Dalam hal ini saya mengambil contoh data jenis pupuk dan dosisnya per Hektar.

Lalu bagaimana caranya merubah orientasi data pada range A1:B6 tersebut  menjadi seperti  data pada range D1:I2   ?

Pertama : Mendapatkan Data Transpose Tanpa Link/Rumus ke data awal

• Seleksi  range A1:B6, kemudian tekan CTR +C atau klik kanan pada area seleksi tersebut --> Klik Copy
• Seleksi cell D1 dan klik kanan --> Klik Paste Special,   sehingga muncul dialog box berikut:

• Tick Transpose --> Klik OK
• Sampai disini sebenarnya kita sudah berhasil melakukan transpose data. Namun bagaimana jika kita menginginkan ada link ke sumber data asli? Sehingga data transpose akan terupdate otomatis jika ada perubahan pada data asli. 
• Hal ini dapat dilakukan dengan membuat proses bantuan, ikuti trik sederhana pada point berikutnya:

Kedua   : Mendapatkan Data Transpose dengan Link/Rumus ke Data awal

• Seleksi kembali data pada range A1:B6 --> CTR+C atau Klik Kanan lalu Copy
• Seleksi sel A9 -->Klik kanan --> Paste Special --> Klik Paste Link

• Tekan CTR + H, untuk memunculkan dialog box Replace --> replace tanda samadengan (=) dengan text yang unik yang tidak ada text yang sama dalam range yang diseleksi, misalnya xxx, sehingga kita mendapatkan data sbb:

• Tanda samadengan (=) sudah digantikan xxx sehingga tidak ada formula pada data tersebut. 
• Mungkin anda bertanya-tanya, apa maksudnya ini. 
• Sabar, hanya beberapa langkah lagi, kita mendapatkan data transpose yang link ke data asli :-)
• Tekan CTR+C untuk mencopy range bantuan tadi, lalu seleksi sel D1 --> Klik Kanan --> Paste Special --> pada dialog box tick values dan transpose --> Ok
• Tekan CTR+H untuk proses replace balik. Ganti kembali xxx menjadi tanda sama dengan (=).  Proses ini menghasilkan range transpose yang link ke data asli. 

• Jika format belum tercopy, maka seleksi kembali data asli pada range A1:B6--> CTR+C --> seleksi cel D1 --> klik kanan -->paste special --> tick format dan tick transpose -->Ok 
• Setelah selesai, kita dapat menghapus data sementara pada range A9:B14. 
• Sampai disini kita sudah dapat mendapatkan tabel data, berikut formatnya yang sudah di-transpose pada range D1:I2 dan asyiknya data tersebut link ke data asli.

Demikian Semoga Bermanfaat, Salam
Belajar Excel - Excellent ..!

Artikel Terkait :

• Transpose Data Menggunakan Rumus INDIRECT ADDRESS
• Transpose Data Menggunakan Rumus OFFSET
• Cara Menggunakan Rumus Array TRANSPOSE Pada Excel
• Transpose Data Menggunakan Rumus INDEX