Microsoft Excel adalah salah satu program spreadsheet yang paling populer di dunia. Dalam penggunaannya, ada banyak fitur dan rumus yang dapat mempermudah pekerjaan kita, terutama dalam pengelolaan data. Namun, tidak jarang kita mengalami kesulitan dalam mengolah data yang diperlukan, terutama ketika data tersebut bercampur dengan format teks. Untuk mengatasi hal ini, kita dapat menggunakan rumus dan fitur tertentu dalam Excel yang akan membantu kita mengambil angka dari teks data entri.
Belajar Microsoft Excel: Mengekstrak Angka dari Text Data Entri
Sebelum memulai, mari kita pahami terlebih dahulu apa itu data entri. Data entri adalah data yang dimasukkan langsung ke dalam sebuah cell pada Excel. Bisa berupa angka, teks, atau kombinasi keduanya. Salah satu kelemahan dari data ini adalah bentuknya yang tidak standar dan tidak mudah diolah.
Contohnya, kita memiliki daftar nama produk dan harga dalam satu kolom seperti di bawah ini:
Dalam contoh ini, kita ingin mengambil hanya angka harga dari setiap produk. Salah satu cara yang paling mudah adalah dengan menggunakan fungsi LEFT, RIGHT, dan MID yang secara otomatis akan mengambil sejumlah karakter dari teks data entri.
Misalnya, jika kita akan mengambil angka harga dari produk “Buku Panduan Microsoft Excel 2019”, kita dapat menggunakan rumus =MID(A2, SEARCH(“:”, A2, SEARCH(“:”, A2)+1)+2, LEN(A2)). Rumus ini akan mengambil angka setelah tanda titik dua di posisi kedua dari teks data entri.
Sedangkan jika kita ingin mengambil angka harga dari semua produk, kita dapat menggunakan kombinasi antara LEFT, RIGHT, dan MID seperti pada rumus di bawah ini:
=IFERROR(MID(A2, SEARCH(":", A2, SEARCH(":", A2)+1)+2, IFERROR(SEARCH(".", A2, SEARCH(":", A2, SEARCH(":", A2)+1)), LEN(A2))-SEARCH(":", A2, SEARCH(":", A2)+1)-2), "")
Rumus ini akan mengambil angka harga dari setiap produk secara otomatis dan mengabaikan seluruh teks lain yang tidak relevan.
Rumus Excel Vlookup Mengambil Data Dari File Lain
Selain mengambil data dari teks data entri, seringkali juga kita memerlukan data dari file lain dalam pengolahan data kita di Excel. Untuk mengambil data seperti ini, kita dapat menggunakan rumus Excel Vlookup.
Rumus Vlookup sangat berguna ketika kita perlu mengambil data dari satu daftar dan memasukkannya ke dalam daftar lain berdasarkan nilai tertentu seperti nomor ID, nama, atau data lainnya. Contoh, kita memiliki dua buah tabel daftar karyawan dengan kriteria yang berbeda seperti di bawah ini:
Dalam contoh ini, kita ingin mengambil informasi gaji setiap karyawan dari tabel kedua dan menempatkannya di tabel pertama menggunakan nomor ID sebagai kriteria. Kita dapat menggunakan rumus Vlookup seperti di bawah ini:
=VLOOKUP(E2, $H$2:$I$6, 2, FALSE)
Dalam rumus ini, E2 adalah nomor ID karyawan di tabel pertama yang akan digunakan sebagai kriteria. $H$2:$I$6 adalah range data tabel kedua yang berisi nomor ID dan informasi gaji karyawan. Angka 2 di dalam rumus menunjukkan bahwa kita ingin mengambil informasi gaji yang berada di kolom kedua tabel kedua, sedangkan FALSE menunjukkan bahwa kita ingin mencari nilai yang cocok dengan kriteria secara tepat.
Dengan demikian, Excel akan mencocokkan nomor ID karyawan dari tabel pertama dengan nomor ID karyawan dari tabel kedua dan mengambil data gaji yang sesuai untuk dimasukkan ke dalam tabel pertama.
Cara Membuat Aplikasi VBA Excel Input Data Pada File Berbeda Bagian 1
Selain menggunakan rumus-rumus Excel, kita juga dapat memanfaatkan VBA (Visual Basic for Applications) untuk membuat aplikasi input data pada file berbeda. Dalam tutorial ini, kita akan membuat aplikasi sederhana yang akan menampilkan form input data pada sebuah file Excel dan menulis data tersebut ke dalam file Excel lain.
Hal pertama yang harus dilakukan adalah membuat form input data di dalam file Excel pertama. Caranya, buka file Excel yang ingin kita gunakan, lalu klik pada menu Developerrarr;Insert dan pilih tombol Text Box. Buat beberapa kolom form dengan menempatkan tombol Text Box pada halaman Excel seperti pada gambar di bawah ini:
Setelah membuat form input data, kita harus membuat kode VBA untuk mengeksekusi input data dan menyimpannya dalam file Excel lain. Caranya, klik dua kali pada salah satu tombol Text Box yang telah dibuat. Nantinya, secara otomatis akan terbuka jendela Editor VBA.
Lalu, buatlah kode VBA seperti di bawah ini:
Private Sub CommandButton1_Click() Dim Path As String, sht As Worksheet, lastrow As Long filename = "D:\Data Input.xlsm" 'ganti dengan alamat file Excel yang ingin diisi Set sht = Workbooks.Open(filename).Worksheets(1) lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1 sht.Cells(lastrow, "A").Value = Me.TextBox1.Value sht.Cells(lastrow, "B").Value = Me.TextBox2.Value sht.Cells(lastrow, "C").Value = Me.TextBox3.Value sht.Cells(lastrow, "D").Value = Me.TextBox4.Value sht.Cells(lastrow, "E").Value = Me.TextBox5.Value Workbooks(filename).Close savechanges:=True End Sub
Dalam contoh di atas, kode tersebut akan menambahkan data dari form input ke dalam file Excel berbeda yang berada pada direktori D:\ dan bernama “Data Input.xlsm”. Jika ingin mengubah alamat dan nama file, Anda dapat mengganti baris kode filename = “D:\Data Input.xlsm” dengan alamat dan nama file yang diinginkan.
Demikianlah cara membuat aplikasi input data pada file Excel berbeda menggunakan VBA. Dalam tutorial selanjutnya, kita akan membahas bagaimana cara membuat aplikasi tersebut menjadi lebih interaktif dan menggunakan banyak form input untuk menyimpan data ke dalam file Excel sekaligus.
Cara Mengambil Data Dari Sheet Lain di Excel
Selain rumus Vlookup, Excel juga memiliki fitur lain untuk mengambil data dari sheet lain yaitu Advanced Filter. Advanced Filter memungkinkan kita untuk melakukan filtering data berdasarkan kriteria tertentu dan menampilkan data yang sesuai di sheet yang berbeda.
Contoh, kita memiliki data penjualan produk di beberapa wilayah berbeda seperti di bawah ini:
Dalam contoh ini, kita ingin mengambil hanya data penjualan untuk wilayah Asia dan menampilkan data tersebut di sheet yang berbeda. Kita dapat menggunakan fitur Advanced Filter dengan langkah-langkah sebagai berikut:
- Pilih seluruh data penjualan di sheet “Data Penjualan”.
- Klik pada menu Data rarr; Sort & Filter rarr; Advanced.
- Pada jendela dialog yang muncul, pilih opsi “Copy to another location”.
- Isi range sheet “Data Penjualan Asia” sebagai range data serta kriteria “Wilayah=Asia”.
- Kemudian klik OK untuk mengeksekusi Advanced Filter.
- Excel akan menyalin data penjualan untuk wilayah Asia ke dalam sheet “Data Penjualan Asia”.
Dengan cara ini, kita dapat dengan mudah mengambil data dari sheet lain dan menampilkan data yang sesuai dengan kriteria tertentu ke dalam sheet yang berbeda.
FAQ
Apa itu VBA?
VBA (Visual Basic for Applications) adalah bahasa pemrograman yang digunakan oleh Excel (dan program Microsoft Office lainnya) untuk memungkinkan penggunaan makro dan script dalam pengolahan data.
Bagaimana cara menggunakan rumus Vlookup pada Excel?
Untuk menggunakan rumus Vlookup, kita perlu memasukkan beberapa parameter yang meliputi nilai yang akan dicari (lookup value), rentang data tabel yang akan dicari (table array), nomor kolom data yang ingin diambil (column index number), dan jenis pencarian nilai (TRUE atau FALSE).
Contoh rumus Vlookup yang umum digunakan adalah:
=VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])
Di sini, lookup_value merupakan nilai yang ingin dicari, table_array merupakan range data tabel yang akan dicari, column_index_number merupakan nomor kolom data yang ingin diambil, dan range_lookup merupakan jenis pencarian nilai.
Range_lookup memiliki dua nilai yaitu TRUE dan FALSE. Jika TRUE, pencarian nilai akan mencari nilai yang paling mendekati dengan nilai yang dicari. Sedangkan jika FALSE, pencarian nilai akan mencari nilai yang cocok secara tepat.
Video Youtube
Berikut adalah video tutorial tentang rumus Excel Vlookup:
