Pintar Excel

Di Excel kita bisa membuat rumus untuk mengkonversi huruf kolom menjadi angka nomor urut kolom dalam spreadsheet. Contohnya : A menjadi 1 B menjadi 2 C menjadi 3 Z menjadi 26 AA menjadi 27 … dan seterusnya. Adapun cara atau formula yang dapat digunakan adalah dengan memanfaatkan kombinasi fungsi COLUMN dan INDIRECT Contoh Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka Untuk lebih jelasnya mari kita lihat contoh rumus berikut. Silahkan copy tabel berikut ke dalam spreadsheet sel A1 sehingga kita bisa melihat hasilnya. A B C 2 Contoh Rumus Penjelasan 3 =COLUMN(INDIRECT("B1")) Mengkonversi huruf B menjadi angka. Nomor urut kolom B adalah 2 4 =COLUMN(INDIRECT(B9&1)) Mengkonversi text kolom pada sel B9 (AA) menjadi angka. Nomor urut kolom AA adalah 27 5 =COLUMN(INDIRECT("XYZ1")) Mencoba mengkonversi huruf XYZ menjadi nomor. Menghasilkan error #REF! karena ZYZ melebihi batas maksimuk kolom pada excel (...

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 addr...

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 mengguna...

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 k...