TUGAS TIK

Fungsi VLOOKUP

Ada syarat-syarat sebelum menggunakan fungsi ini, kolom NIM, tabel Keterangan Angkatan dan Keterangan Jurusan harus diubah formatnya menjadi TEXT. Soalnya pas waktu aku coba, fungsinya menghasilkan error N#A ketika kolom-kolom tadi tidak dibuah menjadi teks. Ciri format teks dan number adalah jika number angka 0 didepan angka tidak akan ditampilkan. Ada dua cara untuk mengubah format number menjadi teks, cara pertama adalah memberi awalan ‘ (tanda petik satu pada angka yang dimasukkan) dan kedua dengan format cell seperti gambar berikut ini:

Jika data anda berisi campuran teks dan angka dan maka anda tidak perlu mengubah formatnya menjadi teks karena biasanya otomatis dianggap teks. Jika sudah dirubah maka kita cari dulu angkatannya dengan mengambil 2 karakter terdepan dari NIM. Pilih sel C3 dan masukkan fungsi seperti ini:

code: =VLOOKUP(LEFT(B3;2);$H$3:$I$7;2;FALSE)

Ada empat parameter ada bagian yang dibutuhkan oleh fungsi VLOOKUP, yaitu: 1. teks yang diuji(dites), 2. tabel sumbernya, 3. kolom offset yang ingin ditampilkan, dan 4. range lookup. Kita bahas satu-satu.

Teks yang diuji adalah cell atau bagian yang akan kita cocokkan dengan data pada tabel sumbernya. Pada fungsi diatas kita mengambil dua karakter awal dari NIM dengan fungsi LEFT(B3,2). B3 adalah sel NIM dan dua berarti 2 karakter dari kiri.

Tabel sumbernya adalah tabel Keterangan Angkatan dimana memiliki range antara H3 sampai I7. Bagaimana cara memilih range dengan mouse? Pertama klik cell H3 dan drag(jangan dilepas) dan tarik hingga cell I7. Gambarnya seperti ini:

Jika anda lihat fungsi VLOOKUP diatas range tabelnya agak aneh, jika secara normal anda memilih range akan menghasilkan H3:I7 maka saya rubah menjadi $H$3:$I$7. Ada penambahan tanda dollar pada tiap per bagian cell. Ini berfungsi menjadikan alamat range tabel menjadi absolut alias tetap . Jika anda mengcopykan rumus ini kebawah maka range tabel tidak bergeser. Bagian ini tidak boleh dihilangkan atau terlupa.

• Kolom offset yang ingin ditampilkan adalah kolom tabel sumber yang ingin kita tampakkan dihitung dari kiri ke kanan
• Range lookup hanya bernilai TRUE atau FALSE. JIka tabel anda datanya berurutan misalnya -2, -1, 0, 1, 2, maka anda bisa menggunakan TRUE tapi jika datanya tidak beraturan atau acak maka anda harus menggunakan FALSE

Dengan sedikit keberuntungan anda akan melihat kolomnya berhasil terisi dengan data tabel di Keterangan Angkatan. Berikutnya kita lanjutkan ke bagian mencocokkan NIM dengan tabel jurusan. Kode jurusan diambil dari karakter ketiga dari NIM dan untuk mengambil karakter ketiga kita memakai fungsi MID(teksnya;mulai;jumlahyangdiambil). Fungsi lengkapnya untuk menampilkan jurusan pada kolom Angkatan/Jurusan adalah:

code: =VLOOKUP(MID(B3;3;1);$H$10:$I$17;2;TRUE)

Caranya sama persis dengan fungsi VLOOKUP sebelumnya cuma karakter yang diuji berasal dari karakter ketiga NIM dan diambil dengan fungsi MID. Terus range lookupnya saya jadi TRUE karena data tabel di Keterangan Jurusan sudah berurutan. dari 1 sampai 8. (meskipun sepertinya itu angka 1-8 tapi sebenarnya itu formatnya sudah saya ubah menjadi teks). Trus bagaimana cara menggabungkan kedua fungsi diatas menjadi satu? Untuk menggabungkan teks bisa digunakan fungsi CONCATENATE(teks1;teks2;ds.t..). Kode lengkapnya seperti ini dan menghasilkan teks seperti: 2005 – Sejarah.

code: =CONCATENATE (VLOOKUP (LEFT(B3;2);$H$3:$I$7;2;FALSE);” – “; VLOOKUP (MID(B3;3;1);$H$10:$I$17;2;TRUE))

Sebenarnya tidak fair jika saya sudah menggabungkan fungsi VLOOKUP dengan fungsi lain seperti LEFT, MID dsb. Tapi Jika data anda lebih sederhana tanpa harus memotong-motong teks maka saya yakin akan lebih mudah bagi anda untuk menggunakan fungsi VLOOKUP ini. Semoga pembahasan ini bisa berguna bagi anda yang belum tahu bagaimana menggunakan fungsi IF dan VLOOKUP pada excel terutama bagi yang belum pernah kursus. Jika anda menginginkan file source dari pembahasan ini anda bisa mengklik link ini [ download file excel ]. Berikut ini adalah penggunaan fungsi IF dan VLOOKUP yang lebih kompleks dengan melakukan cek kolom NIM dan kolom nilai. Jika kolom NIM kosong atau berisi data bukan teks maka akan menghasilkan tanda “-” tapi jika ada datanya program akan mengeksekusinya.

code: =IF(AND(ISTEXT(B3);NOT(ISBLANK(B3)));CONCATENATE(VLOOKUP(LEFT(B3;2); $H$3:$I$7;2;FALSE);” – “;VLOOKUP(MID(B3;3;1);$H$10:$I$17;2;TRUE));”-”)

Kode ini untuk bagian nilai yang berupa huruf A, B, C dsb dimana jika kolom nilai tidak berisi angka atau kosong maka akan menghasilkan tanda “-”, tapi jika ada angkanya maka akan dieksekusi.

code: =IF(AND(ISNUMBER(D3);NOT(ISBLANK(D3)));IF(D3>80;”A”; IF(D3>70;”B”;IF(D3>60;”C”;IF(D3>50;”D”;”E”))));”-”)