Konversi Angka Berformat Text Menjadi Angka Nyata

Ketika bekerja dengan data di excel, kita mungkin pernah menemukan jenis data yang seolah-olah berupa angka, namun excel memperlakukannya tidak sebagai data numerik, melainkan sebagai text. Data jenis ini, karena sifatnya sebagai text, bila dikalkulasi menggunakan beberapa fungsi seperti fungsi SUM, maka nilainya akan dianggap sebagai Nol. Untuk menjadikan data tersebut dapat diolah dalam fungsi pengolah bilangan, maka angka berformat text terlebih dahulu harus dikonversi menjadi bilangan yang benar-benar bilangan. Catatan singkat ini akan menjelaskan bagaimana mengkonversi angka berformat text menjadi angka real.

Video tutorial konversi angka berformat text menjadi angka nyata:

Mengenali Angka berformat text

Bilangan berformat text sering kita jumpai pada data hasil import dari aplikasi lain. Data ini juga bisa diperoleh dari hasil rumus pengolah text (misal fungsi RIGHT, LEFT dan MID) yang mengambil porsi angka dari text tertentu.

Screenhoot dibawah ini bisa membantu kita mengenali bilangan berformat text dengan lebih jelas.

Dapat kita lihat pada gambar di atas. Apabila format alignment dibiarkan sesuai default, maka semua data berjenis text akan rata kiri, sedangkan data berjenis angka atau numerik akan rata kanan.

Selain itu, text bilangan yang diketik dengan menambahkan tanda petik satu di depannya, juga dapat dikenali dengan adanya tanda segitiga kecil berwarna hijau di pojok kiri atas sel berisi data tersebut.
Perhatikan bilangan 1000 pada baris ke-2 dan ke-3. Data tersebut merupakan bilangan berformat text dan dapat dikenali dengan alignment rata kiri, serta adanya tanda segitiga kecil pada pojok kiri atas sel.

Efeknya lebih lanjut: pada saat kita mencoba membuat rumus SUM menggunakan referensi range berisi campuran data jenis text dan bilangan (contoh : range A2:A5) maka data text akan dikesampingkan, sehingga rumus =SUM(A2:A5) akan menghasilkan nilai 2000, bukan 4000. Hasil rumus ini mungkin bukan yang kita harapkan sehingga perlu dicari solusinya.

Cara Konversi Angka Berformat Text Menjadi Angka Nyata

Supaya angka berformat text bisa diolah lebih lanjut dalam formula pengolah data numerik, maka terlebih dahulu kita harus mengkonversi angka berformat text tersebut menjadi angka nyata.

Berikut akan kita ulas beberapa tehnik atau cara konversi angka berformat text menjadi angka nyata.

Cara 1: Menggunakan Alat Pemeriksa Kesalahan

Pada setiap sel yang berisi angka berformat text, khususnya yang diketik didahului tanda petik satu, kalau kita klik biasanya akan muncul pemeriksa kesalahan dengan logo tanda seru berlatar segi empat ketupat berwarna kuning. Jika tanda tersebut kita klik maka akan muncul beberapa menu terkait error pada sel tersebut. Kita bisa meng-klik Convert to Number untuk mengkonversi angka atau bilangan berformat text menjadi angka real.

Langkah-Langkah nya cukup sederhana yaitu sebagai berikut:

Seleksi range yang yang akan dikonversi. Penting: sel pertama, yaitu baris dan kolom pertama range yang diseleksi, harus mengandung angka berformat text yang akan dikonversi. Sedangkan baris / kolom selanjutnya bebas.

Pada saat seleksi range, tanda pemeriksa kesalahan akan muncul disamping sel pertama. Klik tanda tersebut sehingga muncul pilihan menu, kemudian klik Convert To Number.

Cara pertama ini cukup efektif dapat merubah semua angka berformat text menjadi angka real  pada range yang diseleksi.  Namun ada sedikit kelemahannya yaitu ketika baris data yang akan dikonversi cukup banyak dan posisi sel pertama range yang diseleksi berada belum diketahui lokasinya. Hal ini biasanya agak menyulitkan untuk mencari sel pertama-nya.

Cara 2: Menggunakan Copy Paste Special

Fitur Copy Paste Special juga ternyata dapat digunakan untuk mengkonversi angka berformat text menjadi angka real. Hal inilah yang mungkin sedikit kurang disadari oleh kebanyakan pengguna excel.

Berikut langkah-langkahnya:

• Pilih sebuah sel kosong (blank) kemudian klik kanan → Copy.
• Seleksi range yang akan dikovert yaitu range yang mengandung sel berisi data angka berformat text.
• Kemudian klik kanan → paste special
• Pada jendela paste special, tik opsi Values, kemudian tik opsi Add
• Terakhir : klik OK

Untuk lebih jelasnya, perhatikan langkah-langkah dalam gambar Gif berikut:

Cara ke-3 : Menggunakan Fungsi VALUE dan Rumus

Cara ke-3 ini efektif digunakan untuk mengkonversi text angka menjadi angka real dalam sebuah formula jika text angka yang dikonversi merupakan hasil dari sebuah formula pengolah text, misalnya fungsi MID, LEFT, dan RIGHT.

Untuk lebih jelasnya, perhatikan gambar berikut:

Sesuai screenshot di atas, kolom A berisi Kode, dan kolom B berisi 2 digit angka yang merupakan output dari sebuah fungsi RIGHT yang mengambil 2 karakter terakhir dari kode di kolom A.

Secara visual, data di kolom B nampak sebagai bilangan atau angka, namun sebenarnya data tersebut merupakan text. Kita dapat mengenalinya dengan alignment default rata kiri. Selain itu kita juga dapat mengetesnya dengan membuat rumus SUM menggunakan referensi range B2:B5. Rumus SUM memberikan output 0 karena memang tidak ada angka nyata dalam referensi B2:B5.

Supaya kolom B bisa berisi angka real yang dapat dikalkulasi lebih lanjut, maka kita bisa mengkonversinya menggunakan fungsi VALUE.

Dalam hal ini, rumus  =RIGHT(A2,2) dapat dilengkapi dengan fungsi VALUE, sehinggar rumus menjadi =VALUE(RIGHT(A2,2))

Silahkan perhatikan gambar berikut untuk lebih jelasnya:

Setelah dilewatkan pada fungsi VALUE, kita bisa lihat text angka di kolom B sudah dikonversi menjadi anaka real. Angka real dapat dikenali dengan alignment dafault rata kanan, serta fungsi SUM menghasilkan bilangan hasil penjumlahan sesuai harapan.

Alternative Rumus:

Selain menggunakan fungsi VALUE, kita juga bisa mengkonversi text angka dengan menggunakan rumus perkalian, penjumlahan, dan pengurangan. Lebih tepatnya, saya sebut Rumus Kali Satu, Rumus Tambah Nol dan Rumus Kurang Nol:

Dengan melengkapi contoh rumus =RIGHT(A2,2) sesuai pembahasan di atas, maka kita bisa memodifikasinya untuk mendapatkan output angka real sebagai berikut:

• Rumus Kali Satu: =RIGHT(A2,2)*1
• Rumus Tambah Nol:   =RIGHT(A2,2)+0
• Rumus Kurang Nol:   =RIGHT(A2,2)-0

Eh, ternyata masih ada satu lagi rumus yang patut dicoba, yaitu menggunakan minus ganda atau double unary. Cukup menambahkan 2 tanda minus sebelum formula yang menghasilkan text angka.

• Rumus Minus Ganda:  =--RIGHT(A2,2)

Demikian pembahasan singkat mengenai bagaimana cara mengkonversi  bilangan atau angka berformat text menjadi angka nyata. Ada beberapa alternative yang sudah saya jelaskan di atas. Dalam prakteknya mungkin masih ada tehnik lain yang bisa dicoba. Apabila pembaca ada punya cara lainnya, saya sangat senang sekali apabila pembaca bisa berbagi dengan melengkapinya di kolom komentar.

Salam.

Jika ada masalah terkait permasalahan excel kamu. Janngan sungkan untuk mengirimkan contoh filenya ke Admin je.jenal@gmail.com. Kalau ada waktu, Insya Allah akan admin bantu. Tapi dibantu dengan video tutorial saja ya. Supaya sama-sama belajar.

Seperti contoh dibawah ini adalah video tutorial atas pertanyaan satu orang pembaca blog ini yang telah mengirimkan sampel filenya kepada admin.

Permasalahan: Text angka tidak bisa diubah menjadi angka nyata meskipun sudah mengikuti tutorial yang disampaikan di atas.

Penyebabnya setelah dianalisa ternyata ada karakter tidak terlihat di belakang text angka. Lengkapnya silahkan di cek video nya ya...

Artikel terkait:

• Konversi Huruf Kolom Menjadi Angka
• String Function : Pengolah Text Excel
• Cara Menghitung Jumlah Huruf dan Kata
• Konversi Excel Menjadi Gambar

Rahasia Cara Cepat Autosum

Bagi pengguna excel, melakukan penjumlahan pastilah hal yang biasa. Semua user pasti memiliki caranya sendiri yang disukai atau diketahui untuk melakukan penjumlahan. Ada yang  menggunakan rumus tambah (+), dan ada juga yang menggunakan fungsi SUM. Tulisan sangat singkat ini dikhususkan untuk membahas cara penjumlahan menggunakan fungsi SUM, terutama dengan tehnik Autosum.

Bagaimana Cara Melakukan Autosum di Excel?

Nampaknya mayoritas pengguna excel sebenarnya sudah mengetahui cara melakukan autosum yaitu dengan cara menekan tombol autosum.

Namun tahukah anda ternyata ada cara yang lebih cepat untuk melakukan autosum. Cara tersebut yaitu menggunakan shortcut pada keyboard. Adapun shortcutnya adalah :

ALT + =

Cara autosum menggunakan shortcut ALT + = inilah yang ingin ditekankan dalam kesempatan ini, karena saya rasa masih banyak user excel yang belum mengetahuinya.

Adapun cara melakukan autosum adalah dengan cara menyeleksi sel  untuk menempatkan rumus, kemudian tekan tombol  ALT + = pada keyboard. Maka secara otomatis excel akan mendeteksi bagian mana dalam range exel yang akan dijumlahkan. 

Perhatikan,  contoh berikut merupakan data jumlah buah-buahan yang terjual dalam seminggu. Untuk menjumlahkan total bobot buah-buahan per hari (penjumlahan secara vertikal) adalah dengan cara menyeleksi sel  yang akan ditempatkan rumus, anggaplan di sel C10, kemudian tekan shortcut ALT +  =

Dengan cara yang sama, kita juga dapat melakukan autosum secara horizontal, misalnya tempatkan seleksi sel I3, kemudian tekan ALT + =

Perlu dicatat bahwa pada saat melakukan autosum, excel akan mendeteksi otomatis range sel mana yang akan dijadikan referensi. Range biasanya akan terputus ketika menjumpai sel kosong atau sel yang berisi formula. Ilustrasi berikut menggambarkan autosum yang terputus  oleh sel yang blank.

Salah satu cara untuk mengatasi hal tersebut adalah dengancara mengisi angka nol pada sel yang blank.

Demikian, tips singkat ini. Harapannya anda dapat menggunakan jalan pintas atau shortcut CTR + = untuk melakukan autosum.

Semoga beranfaat.

Salam..

Artikel terkait:

• Fungsi SUM dan Rumus Penjumlahan Pada Excel
• Fungsi SUMIF dan Rumus Penjumlahan Bersyarat
• List Tombol Shortcut di Excel

STRING FUNCTION: Pengolah Text Excel

Excel tidak hanya menyediakan fasilitas pengolahan data angka atau numerik. Tetapi program canggih ini juga memungkinkan penggunanya untuk mengolah informasi text dengan cepat. Salah satu cara mengolah data text dalam excel adalah menggunakan rumus.

Belajar excel kali ini akan membahas bebeberapa fungsi  siap pakai dalam rumus excel yang dapat digunakan untuk mengolah informasi text. Berikut diantaranya yang cukup sering digunakan. List dikelompokan berdasarkan kegunaannya.

• LOWER, UPPER dan PROPER
• LEFT, RIGHT, dan MID
• FIND dan SEARCH
• CONCATENATE dan REPT
• SUBSTITUTE dan REPLACE

LOWER, UPPER dan PROPER

Ketiga fungsi ini digunakan untuk untuk mengkonversi  huruf dalam text menjadi huruf kecil atau sebaliknya

• LOWER  : Konversi semua huruf menjadi huruf kecil
• UPPER   : Konversi semua huruf menjadi huruf kapital
• PROPER : Konversi semua huruf pertama dalam setiap kata menjadi huruf kapital dan semua huruf lainnya menjadi huruf kecil

Syntak : 

=LOWER(text)

=UPPER(text)

=PROPER(text)

Contoh Rumus Cara Menggunakan fungsi LOWER, UPPER dan PROPER

No	Rumus	Hasil
1.	=LOWER("INDONESIA memang hebat")	indonesia memang hebat
2.	=UPPER("INDONESIA memang hebat")	INDONESIA MEMANG HEBAT
3.	=PROPER("INDONESIA memang hebat")	Indonesia Memang Hebat

LEFT, RIGHT, dan MID

Fungsi LEFT, RIGHT, dan MID digunakan untuk mengambil porsi atau bagian text berdasarkan posisi bagian tersebut didalam text induk.

• LEFT : Mengambil satu atau lebih karakter dari sebelah kiri
• RIGHT : Mengambil satu atau lebih karakter dari sebelah kanan
• MID : Mengambil satu atau lebih karakter dimulai dari nomor urut karakter tertentu

Syntak

=LEFT(text,jumlah_karakter)

=RIGHT(text,jumlah_karakter)

=MID(text,nomor_urut_mulai,jumlah_karakter)

Contoh Rumus Cara Menggunakan Fungsi LEFT, RIGHT dan MID

	Rumus	Hasil
1.	=LEFT("INDONESIA memang hebat",4)	INDO
2.	=RIGHT("INDONESIA memang hebat",5)	hebat
3.	=MID("INDONESIA memang hebat",11,6)	memang

Penjelasan Rumus

1. Rumus LEFT digunakan untuk mendapatkan 4 karakter pertama dari text "INDONESIA memang hebat". Hasilnya adalah text "INDO
2. Penggunaan rumus RIGHT hampir sama dengan rumus LEFT, hanya mengambil bagian dari sebelah kanan sebanyak 5 karakter. Hasilnya adalah text "hebat"
3. Rumus MID mengambil 6 karakter dimulai dari karakter ke-11, menghasilkan text "memang"

FIND dan SEARCH

Kedua fungsi tersebut digunakan untuk menemukan text tertentu dari sebuah text induk dan mendapatkan angka/bilangan yang merupakan nomor urut posisi karakter pertama dari text yang dicari dalam text induk. Yang dimaksud dengan karakter dalam excel adalah semua angka, huruf maupun symbol yang dapat diketik atau insert symbol. Termasuk spasi dan pemisah baris juga merupakan sebuah karakter sehingga dapat dihitung posisinya dalam text.

• FIND bersifat Case Sensitif artinya membedakan huruf kecil dan huruf kapital
• SEARCH tidak bersifat Case Sensitif artinya tidak membedakan huruf kecil dan huruf kapital

Syntax

=FIND(text_dicari,text_induk,nomor_mulai_pencarian)

=SEARCH(text_dicari,text_induk,nomor_mulai_pencarian)

Contoh Rumus Cara Menggunakan Fungsi FIND dan SEARCH

No	Rumus	Hasil
1.	=SEARCH("e","INDONESIA memang hebat",1)	6
2.	=FIND("e","INDONESIA memang hebat",1)	12
3.	=FIND("e","INDONESIA memang hebat",13)	19
4.	=SEARCH("MEMANG","INDONESIA memang hebat",1)	11
5.	=FIND("MEMANG","INDONESIA memang hebat",1)	#VALUE!

Penjelasan Rumus

1. Rumus SEARCH digunakan untuk mencari karakter text “e” di dalam text "INDONESIA memang hebat". Pencarian dimulai dari nomor urut 1. Dikarenakan fungsi SEARCH bersifat tidak case sensitif, maka rumus ini akan menghasilkan bilangan 6 yang merupakan posisi huruf E dalam text "INDONESIA memang hebat". Dengan kata lain rumus search tidak membedakan antara huruf “e” dan “E”
2. Dengan cara yang sama dengan nomor 1 tetapi menggunakan rumus FIND. Diperoleh hasil 12 karena fungsi FIND bersifat  case sensitif sehingga membedakan antara huruf “e” dan E.
3. Dengan cara yang sama dengan nomor 2, tetapi pencarian dilakukan mulai huruf ke 13. Menghasilkan bilangan 19, yang merupakan nomor urut huruf “e” yang ditemukan pertama dengan pencarian dimulai nomor urut 13.
4. Rumus SEARCH digunakan untuk mencari karakter text “MEMANG” di dalam text "INDONESIA memang hebat". Pencarian dimulai dari huruf ke-1. Menghasilkan nilai  11
5. Hampir sama dengan  nomor 4 tetapi menggunakan fungsi FIND. Menghasilkan nilai error #VALUE karena fungsi FIND tidak bisa menemukan text “MEMANG” di dalam text "INDONESIA memang hebat". Hal ini karena fungsi FIND memilah antara huruf kecil dan besar (kapital) atau bersifat case sensitif

CONCATENATE dan REPT

Fungsi CONCATENATE digunakan untuk menggabungkan beberapa text yang berbeda, sedangkan fungsi REPT digunakan untuk menggabungkan text yang sama dengan beberapa pengulangan.

Syntak

=CONCATENATE(text1,[text2],…)

=REPT(text,jumlah_pengulangan)

Perhatikan parameter fungsi yang berada dalam tanda [ ] argument fungsi CONCATENATE. Ini menandakan bahwa argument tersebut bersifat opsional. Dengan kata lain untuk rumus CONCATENATE memerlukan minimal satu TEXT sebagai argument. Namun dalam prakteknya minimal harus ada 2 text yang akan digabung sehingga rumus CONCATENATE menjadi berarti.

Contoh Rumus Cara Menggunakan Fungsi CONCATENATE dan REPT

No	Rumus	Hasil
1.	=CONCATENATE("Indonesia ","Memang ","Hebat")	IndonesiaMemangHebat
2.	=CONCATENATE("Indonesia ","Memang ","Hebat")	Indonesia Memang Hebat
3.	=CONCATENATE("Indonesia"," ","Memang"," ","Hebat")	Indonesia Memang Hebat
4.	=REPT("?",10)	??????????

Penjelasan Rumus

1. Rumus CONCATENATE digunakan untuk menggabungkan text “Indonesia”, text “Memang” dan text “Hebat”. Hasilnya berupa text gabungan “IndonesiaMemangHebat”
2. Hampir sama dengan nomor 1 dengan menambahkan spasi pada text “Indonesia” dan “Memang”. Hal ini supaya menghasilkan kata yang terpisah dalam kalimat. Hasilnya “Indonesia Memang Hebat”
3. Hampir sama dengan nomor2 , tetapi tanda spasi “ “ diletakan sebagai parameter tersendiri dalam rumus CONCATENATE
4. Rumus REPT digunakan untuk mengulang tanda tanya “?” sebanyak 10 kali. Hasilnya “??????????”

SUBSTITUTE dan REPLACE

• Fungsi SUBSTITUTE digunakan untuk mengganti text tertentu dalam sebuah text induk dengan text lainnya
• Fungsi REPLACE digunakan untuk menghapus bagian tertentu dalam sebuah text induk dan menggantinya dengan text lain. 

Syntak

=SUBSTITUTE(text_induk,text_diganti,text_pengganti)

=REPLACE(text_induk,nomor_awal,nomor_akhir,text_sisip)

Contoh Rumus : Cara Menggunakan Fungsi SUBSTITUTE dan REPLACE

No	Rumus	Hasil
1.	=SUBSTITUTE("INDONESIA memang hebat","hebat","Mantap")	INDONESIA memang Mantap
2.	=REPLACE("INDONESIA memang hebat",6,4,"")	INDON memang hebat

Penjelasan Rumus

1. Rumus SUBSTITUTE digunakan untuk mengganti text “hebat” menjadi “Mantap” di dalam text INDONESIA memang hebat" sehingga hasilnya menjadi “INDONESIA memang Mantap"
2. Rumus REPLACE digunakan untuk mengganti dimulai huruf ke-6 sebanyak 4 huruf dalam text "INDONESIA memang hebat" dengan text “”, atau dengan text kosong. Oleh karena itu seakan-akan menghapus bagian “ESIA” dari text “INDONESIA”

Sampai disini dulu pembahasan ringkas mengenai berbagai fungsi excel yang digunakan untuk mengolah data text. Sebenarnya masih ada beberapa lagi fungsi terkait. Namun yang dirincikan diatas merupakan beberapa yang paling sering digunakan, setidaknya oleh penulis..:-)

Belajar Excel.... Excellent !

Artikel terkait:

• Rumus Mendapatkan Nama Hari dan Bulan dalam Berbagai Bahasa
• Cara Mudah Memahami Fungsi dan Rumus Excel
• Cara Memisahkan Kata Per Huruf

Referensi:
https://support.office.com/en-US/article/Text-functions-reference-CCCD86AD-547D-4EA9-A065-7BB697C2A56E

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

Formula Matematika Excel : Kuadrat, Akar Kuadrat dan Pangkat

Tidaklah sulit untuk menghitung kuadrat, akar kuadrat dan pangkat menggunakan microsoft excel.  Dalam hal ini kita bisa menggunakan operator pangkat (^) maupun menggunakan fungsi SQRT, dan POWER.  Berikut contoh cara menuliskan formula operasi kuadrat, akar kuadrat dan pangkat  menggunakan excel.

Rumus Matematika	Rumus Excel  (Indonesia)	Rumus Excel  (US)
=22	=2^2	=2^2
=22	=POWER(2;2)	=POWER(2,2)
=√9	=SQRT(9)	=SQRT(9)
=√9	=9^(1/2)	=9^(1/2)
=√9	=9^0,5	=9^0.5
=√9	=POWER(9;0,5)	=POWER(9,0.5)
=23	=2^3	=2^3
=23	=POWER(2;3)	=POWER(2,3)
=81/3	=8^(1/3)	=8^(1/3)
=81/3	=POWER(8;1/3)	=POWER(8,1/3)

Dengan memperhatikan contoh formula diatas, penggunakan operator matematika pangkat “^” dan fungsi excel POWER nampaknya bersifat lebih fleksibel dimana dapat digunakan untuk pangkat maupun untuk akar.  Selebihnya tergantung anda lebih suka yang mana.  

Contoh formula excel diatas tentunya hanya gambaran bagaimana membuat formula matematika pangkat, akar dan kuadrat menggunakan excel. Pada kenyataanya contoh dimaksud tidak terlalu praktikal, karena aktualnya lebih sering menggunakan referensi pada formula dibandingkan dengan menggunakan numerik secara langsung.

Demikian semoga bermanfaat. Salam

Belajar Excel..!  Excellent..!

Artikel Terkait
Cara Mudah Memahami Fungsi dan Formula Excel
Formula TEXT untuk Mendapatkan Nama Hari dan Bulan dalam Berbagai Bahasa