MEMBUAT ISIAN DATA DI EXCEL MENGGUNAKAN FUNGSI VBA

Tanpa panjang lebar, mari kita langsung saja untuk membuat isian data menggunakan fungsi VBA. Yang mana form isian data ini dapat memudahkan kita menginputkan data dengan cepat dan lebih aman. Berikut langkah-langkahnya.

1. Langkah pertama, siapkan terlebih dulu data tabel di lembar kerja Excel seperti berikut.

2.  Selanjutnya membuat tombol tambah, caranya arahkan kursor ke Tab Developer > Insert > CommandButton (ActiveX Control).

3.  Lalu posisikan tombol tersebut sesuai kebutuhan, ganti nama tombol dengan tulisan Tambah, caranya klik kanan pada tombol lalu pilih CommandButton Object >> Edit. Hasilnya seperti berikut.

4.  Kemudian klik kanan pada tombol Tambah, pilih View Code, maka akan tampil halaman Visual Basic for Application, kemudian isikan code berikut ini.

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

Seperti pada tampilan berikut. (catatan, simpan halaman dengan nama IsianData.xlsb)

5.  Kemudian klik kanan pada tulisan “VBAProject (IsianData.xlsb)”, lalu pilih insert >> UserForm, seperti tampilan berikut.

6.  Selanjutnya buat tiga buah TextBox dengan satu tombol CommandButton, contohnya seperti tampilan berikut.

7.  Kemudian double klik pada button Simpan, setelah itu masukan code seperti berikut.

Private Sub CommandButton1_Click()
Set wsDtbsBrg = Sheets(“Sheet1″)
RecordAkhir = wsDtbsBrg.Cells(wsDtbsBrg.Rows.Count, “B”). _
End(xlUp).Offset(0, 0).Row
With wsDtbsBrg
.Cells(RecordAkhir + 1, 1).Value = TextBox1.Value
.Cells(RecordAkhir + 1, 2).Value = TextBox2.Value
.Cells(RecordAkhir + 1, 3).Value = TextBox3.Value
End With
Unload Me
End Sub

Private Sub Textbox3_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc(“0″) To Asc(“9″)
Case Else
KeyAscii = 0
End Select
End Sub

Seperti pada tampilan berikut. (catatan, setelah code di atas di tuliskan atau Anda bisa meng-copy pastenya, lalu save halaman?

8.  Setelah itu Anda kembali lagi ke lembar kerja Excel, untuk mengaktifkan tombol “Tambah”, Anda bisa arahkan kursor ke tab Developer >> Design Mode.

9.  Sekarang Anda coba klik tombol “Tambah”, jika tombol tersebut berfungsi maka akan muncul form dialog isian, dan coba Anda isi bagian Nama, Alamat dan No telepon, kemudian Klik Simpan, seperti berikut.

10.  Maka pada tabel data akan terisi sesuai dengan data yang di inputkan pada form isian data. Jika Anda ingin menambahkan data lebih banyak lagi, cukup klik tombol “Tambah” dan lakukan pengisian data seperti pada no 9 diatas.

Dengan demikian, jika Anda ingin mengisi data tabel hanya tinggal klik tombol “Tambah” saja, maka akan tampil form isian data. Semoga bermanfaat.

SOAL UH PRAKTIK AKHIR KELAS XI IPA dan IPS

HASIL :

Khusus kelas XI-A2 :

Beda Fungsi OR dan AND Excel

Perbedaan Fungsi AND dengan Fungsi OR adalah nilai untuk kondisi TRUE dan FALSE-nya. Pada fungsi AND, nilai TRUE akan digunakan jika semua kondisi terpenuhi (TRUE), dan nilai FALSE akan digunakan jika salah satu atau semua kondisi tidak terpenuhi (Salah satu atau semua kondisi FALSE).

Untuk lebih jelasnya perhatikan tabel berikut :

Tabel 1 Dengan Dua (2) Kondisi perbandingan

KONDISI 1	KONDISI 2	NILAI AND	NILAI OR
TRUE	TRUE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE
TRUE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	TRUE

Tabel 2 Dengan Tuga (3) Kondisi Perbandingan

KONDISI 1	KONDISI 2	KONDISI 3	NILAI AND	NILAI OR
TRUE	TRUE	TRUE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	FALSE
FALSE	TRUE	TRUE	FALSE	TRUE
TRUE	FALSE	TRUE	FALSE	TRUE
TRUE	TRUE	FALSE	FALSE	TRUE
FALSE	FALSE	TRUE	FALSE	TRUE
TRUE	FALSE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	FALSE	TRUE

TRUE=BENAR=YA=Kondisi Terpenuhi
FALSE=SALAH=TIDAK=Kondisi Tidak Terpenuhi

Untuk lebih jelas mengenai kondisi TRUE dan FALSE ini kita juga perlu untuk memahami operator perbandingan dalam program microsoft Excel.

Biasanya dua Fungsi ini (OR dan AND) kita kombinasikan dengan fungsi-fungsi lain misal Fungsi IF dalam penerapannya di Excel.

Fungsi IF Excel

Kali ini kita akan mulai belajar kelompok fungsi Logical. Salah satu fungsi Excel yang paling sering kita butuhkan dalam kategori atau kelompok logical ini adalah Fungsi IF. Sebelum lebih jauh membahas fungsi ini ada baiknya kita memahami dulu tentang operator Excel yang telah saya bahas sebelumnya. Kenapa? karena dalam mengaplikasikan fungsi ini kita akan membutuhkan memahami operator perbandingan dalam program Excel.

Fungsi IF mengembalikan satu nilai jika kondisi yang kita tentukan mengevaluasi ke TRUE, dan nilai lain jika kondisi itu mengevaluasi ke FALSE. Dengan kata lain  fungsi IF  ini akan mengambil suatu kondisi tertentu kemudian menentukan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.

TRUE = Benar. Kondisi terpenuhi.
FALSE = Salah. Kondisi tidak terpenuhi.

SINTAKS

IF(logical_test, [value_if_true], [value_if_false])

Argumen Fungsi IF :

• Logical_test : (Diperlukan) Nilai atau ekspresi logika yang bisa dievaluasi ke TRUE atau FALSE. Argumen ini merupakan kriteria acuan yang dijadikan pembanding.
• Value_if_true : (Opsional) Nilai yang ingin kita kembalikan jika argumen logical_test mengevaluasi ke TRUE. Argumen ini merupakan nilai jika kondisi yang terpenuhi.
• Value_if_false ; (Opsional) Nilai yang ingin kita kembalikan jika argumen logical_test mengevaluasi ke FALSE. . Argumen ini merupakan nilai jika kondisi yang tidak terpenuhi.

CONTOH

Keterangan Contoh:

FORMULA	KETERANGAN
=IF(D2>60;"LULUS";"GAGAL")	Jika Kondisi D2 Lebih dari 60 tulis LULUS, jika TIDAK lebih dari 60 Tulis GAGAL. Hasill evaluasi True maka ditulis LULUS.
=IF(D5>60;"LULUS";"GAGAL")	Jika Kondisi D5 Lebih dari 60 tulis LULUS, jika TIDAK lebih dari 60 Tulis GAGAL. Hasill evaluasi False maka ditulis GAGAL.
=IF(D2>=90;"A";IF(D2>=80;"B";"C"))	Jika Kondisi D2 Lebih dari atau sama dengan 90 tulis A, jika TIDAK Lebih dari atau sama dengan 90 evalusi lagi D2 apakah lebih dari atau sama dengan 80. Jika D2 Lebih dari atau sama dengan 80 Tulis B, jika tidak tulis C. Hasil akhir A.
=IF(D3>=90;"A";IF(D3>=80;"B";"C"))	Jika Kondisi D2 Lebih dari atau sama dengan 90 tulis A, jika TIDAK Lebih dari atau sama dengan 90 evalusi lagi D2 apakah lebih dari atau sama dengan 80. Jika D2 Lebih dari atau sama dengan 80 Tulis B, jika tidak tulis C. Hasil Akhir B.

TAMBAHAN

• Kita dapat menambahkan hingga 64 fungsi IF untuk ditumpukkan sebagai argumen Value_if_true dan Value_if_false dalam menyusun ujian-ujian lain yang lebih terperinci. Alternatif lain untuk menguji banyak kondisi, pertimbangkan juga penggunaan fungsi LOOKUP, VLOOKUP, HLOOKUP, atau CHOOSE.
• Jika argumen untuk IF adalah array, maka setiap elemen array dievaluasi ketika pernyataan IF dilakukan.
• Excel menyediakan fungsi tambahan yang dapat digunakan untuk menganalisis data Anda berdasarkan sebuah kondisi. Misalnya, untuk menghitung jumlah kemunculan string teks atau angka di dalam rentang sel, gunakan fungsi lembar kerja COUNTIF atau COUNTIFS. Untuk menghitung jumlah berdasarkan sebuah string teks atau angka di dalam rentang, gunakan fungsi lembar kerja SUMIF atau SUMIFS.

Penerapan Kombinasi Rumus Logika IF dengan NOT, OR, AND di Excel

Pada tulisan lainnya saya sudah membahas mengenai rumus logika di Excel meliputi IF, NOT, OR, dan AND.  Semua rumus logika tersebut yang paling banyak digunakan yaitu IF, namun jika dikombinasikan dengan ke-4 logika lainnya maka akan mempermudah anda dalam menyelesaikan pekerjaan atau data berupa data multi. Pada kesempatan ini saya akan sharing bagaimana cara mengkombinasikan rumus logika tersebut.

1. Logika IF + NOT

Kombinasi rumus ini sangat cocok digunakan untuk memberikan keterangan berdasarkan data angka.  Sebagai contoh misalnya nilai mahasiswa ” TIDAK LULUS” jika nilainya lebih kecil dari “65”.  Misal dari nilai rata-rata 3 kali ujian kita akan menentukkan kelulusan dengan syarat nilai lulus harus lebih besar dari 65.

Maka kita tulis rumusnya seperti di bawah ini:

=IF(NOT(E2<65),”LULUS”,”TIDAK LULUS”)

Keterangan : E2 adalah kolom nilai rata – rata.  Kemudian perhatikan tanda pemisah rumus yaitu koma (,) ada di komputer lain memakai tanda titik koma (;).

2. Logika IF + OR

Untuk kombinasi ini kita dapat menyeleksi data yang meliputi 2 variabel sekaligus.  Biasanya kombinasi ini dignakan untuk mengolah data berupa Text atau Tulisan.  Baiklah contoh kasusnya yaitu misal kita akan menyeleksi calon pelamar kerja dengan syarat diterima jika mereka lulusan D3 dan S1.

Maka rumus yang kita tulis yaitu:

=IF(OR(B2=”D3″,B2=”S1″),”DITERIMA”,”TOLAK”)

Keterangan : B2 adalah kolom Pendidikan.  Kemudian perhatikan tanda pemisah rumus yaitu koma (,) ada di komputer lain memakai tanda titik koma (;).

3. Kombinasi IF + AND

Untuk kombinasi ini dapat mengolah data berupa angka yang terdiri dari dua atau lebih variabel dan kemudian diterjemahkan ke dalam bahasa tulisan.  Contoh kasusnya seperti ini, misal dalam penyeleksian penerimaan karyawan baru di butuhkan pengalaman kerja lebih dari atau sama dengan 3 tahun dan memiliki IPK minimal 3,00.

Maka rumus yang kita tulis yaitu:

=ID(AND(B2>=3,C2>=3),”DITERIMA”,”DITOLAK”)

Keterangan : B2 adalah PENGALAMAN KERJA, C2 adalah IPK.  Kemudian perhatikan tanda pemisah rumus yaitu koma (,) ada di komputer lain memakai tanda titik koma (;).
Dengan menggunakan kombinasi rumus tersebut maka anda akan dimudahkan dalam mengerjakan tugas anda dan menghemat waktu dalam bekerja, selamat mencoba dan semoga bermanfaat.

Mencari Data pada Tabel dengan VLOOKUP pada Excel

Program Excel kita dapat menggunakan fungsi VLOOKUP untuk mencari kolom pertama suatu rentang atau range sel, lalu mengembalikan nilai dari sel mana pun di baris yang sama pada range tersebut.

Dengan fungsi VLOOKUP ini kita dapat mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik.

Awalan huruf V didepan kata lookup merupakan singkatan dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. 

SINTAKS

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Dari sintaks tersebut kita dapati empat argumen :

• lookup_value : (Diperlukan) Nilai yang akan kita cari di kolom pertama tabel. Argumen lookup_value dapat berupa suatu nilai atau referensi.
• table_array : (Diperlukan) Pilih rentang sel atau tabel yang berisi data tersebut.
• col_index_num : (Diperlukan) Nomor kolom dalam argumen table_array yang merupakan asal dari nilai yang cocok yang harus dikembalikan.
• range_lookup : (Opsional) Berisi TRUE atau FALSE. Nilai logika yang menetapkan apakah kita ingin VLOOKUP menemukan hasil yang persis sama atau mendekati. Jika range_lookup adalah TRUE atau dikosongkan, hasil yang sama persis atau mendekati dikembalikan. Jika hasil yang persis sama tidak ditemukan, nilai terbesar berikutnya yang kurang dari lookup_value dikembalikan.

Untuk lebih jelasnya perhatikan contoh berikut:

CONTOH 1

Penjelasan Contoh:

FORMULA	KETERANGAN
=VLOOKUP(F2;A1:D5;2;FALSE)	Formula ini akan mencari nilai pada sel F2 (3) pada kolom pertama tabel atau range A1:D5 jika ketemu maka akan mengembalikan nilai pada kolom 2 dari tabel tersebut yang satu baris dengan sel hasil pencarian. FALSE menunjukkan bahwa pencarian harus sama persis.
=VLOOKUP(F3;A1:D5;3;FALSE)	Formula ini akan mencari nilai pada sel F3 (2) pada kolom pertama tabel atau range A1:D5 jika ketemu maka akan mengembalikan nilai pada kolom 3 dari tabel tersebut yang satu baris dengan sel hasil pencarian. FALSE menunjukkan bahwa pencarian harus sama persis.
=VLOOKUP(F4;A1:D5;4;FALSE)	Formula ini akan mencari nilai pada sel F4 (3) pada kolom pertama tabel atau range A1:D5 jika ketemu maka akan mengembalikan nilai pada kolom 4 dari tabel tersebut yang satu baris dengan sel hasil pencarian. FALSE menunjukkan bahwa pencarian harus sama persis.

CONTOH 2

Penjelasan Contoh 2:

FORMULA	KETERANGAN
=VLOOKUP(F2;A2:B6;2)	Mencari Nilai 59 pada tabel atau range A2:B6 dan mengambil SKOR yang sebaris pada kolom 2. Karena argumen range_lookup kosong (tidak diisi) maka dikembalikan ke nilai default yakni TRUE maka ketika nilai 59 tidak ditemukan hasil dikembalikan ke nilai yang mendekati 59 yakni 60 sehingga menghasilkan skor "D".
=VLOOKUP(F3;A2:B6;2;TRUE)	Mencari Nilai 60 pada tabel atau range A2:B6 dan mengambil SKOR yang sebaris pada kolom 2. Karena argumen range_lookup TRUE maka dan nilai 60 dapat ditemukan maka menghasilkan skor "C".
=VLOOKUP(F4;A2:B6;2;FALSE)	Mencari Nilai 85 pada tabel atau range A2:B6 dan mengambil SKOR yang sebaris pada kolom 2. Karena argumen range_lookup FALSE yang artinya bahwa pencarian harus sama persis dan nilai 85 tidak ditemukan maka menghasilkan error (#N/A).
=VLOOKUP(F5;A2:B6;2;FALSE)	Mencari Nilai 90 pada tabel atau range A2:B6 dan mengambil SKOR yang sebaris pada kolom 2. Karena argumen range_lookup FALSE yang artinya bahwa pencarian harus sama persis dan nilai 90 ditemukan maka menghasilkan nilai "A".

KETERANGAN TAMBAHAN

• Jika nilai yang kita berikan untuk argumen lookup_value lebih kecil dari nilai terkecil di kolom pertama argumen table_array, VLOOKUP mengembalikan nilai kesalahan #N/A.
• Nilai argumen lookup_value ini dapat berupa teks, angka, atau nilai logika. Teks huruf besar dan huruf kecil dianggap sama.
• Jika range_lookup adalah TRUE atau dikosongkan, nilai-nilai di kolom pertama table_array harus ditempatkan dalam urutan naik, jika tidak, VLOOKUP mungkin tidak mengembalikan nilai yang benar.
• Argumen col_index_num kurang dari 1, VLOOKUP mengembalikan nilai kesalahan #VALUE! jika col_index_num lebih besar dari jumlah kolom dalam table_array, VLOOKUP mengembalikan nilai kesalahan #REF!.
• Ketika mencari nilai teks di kolom pertama table_array, pastikan bahwa data di kolom pertama table_array tidak berisi spasi awal, spasi akhir, penggunaan tanda kutip lurus ( ' atau " ) dan lengkung ( ‘ atau “) secara inkonsisten, atau karakter noncetak. Dalam kasus ini, VLOOKUP mungkin mengembalikan nilai yang salah atau tidak diharapkan.
• Ketika mencari nilai angka atau tanggal, pastikan bahwa data di kolom pertama table_array tidak disimpan sebagai nilai teks. Dalam kasus ini, VLOOKUP mungkin mengembalikan nilai yang salah atau tidak diharapkan.
• Jika range_lookup FALSE dan lookup_value adalah teks, Anda dapat mengunakan karakter wildcard,  — tanda tanya (?) dan tanda bintang (*) — dalam lookup_value. Tanda tanya cocok dengan karakter tunggal apa pun, tanda bintang cocok dengan urutan karakter apa pun.

Sebagai catatan terakhir bahwa fungsi ini hanya beraku jika tabel kita susun secara Vertikal. Lalu bagaimana dengan tabel yang disusun secara Horisontal? ini akan kita bahas pada artikel berikutnya. Pada artikel berikutnya kita akan belajar tentang HLOOKUP.

Mencari Data pada Tabel dengan HLOOKUP pada Excel

Jika sebelumnya kita menggunakan VLOOKUP untuk mencari data pada tabel berbentuk Vertikal, sekarang kita akan belajar menggunakan HLOOKUP untuk mencari data pada tabel berbentuk Horisontal.

Sebenarnya fungsi HLOOKUP sama dengan fungsi VLOOKUP yang berbeda adalah bentuk tabel dan letak value yang kita cari. Jika value yang kita cari dengan VLOOKUP ada pada kolom pertama sebuah tabel atau range dan nilai yang dikembalikan adalah pada baris yang sama, maka pencarian dengan HLOOKUP ada pada baris pertama sebuah tabel atau range dan hasil yang dikembalikan ada pada kolom yang sama.

SINTAKS

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Sama dengan VLOOKUP, VLOOKUP juga memiliki empat argumen :

1. lookup_value : (Diperlukan) Nilai yang dicari pada baris pertama tabel. Lookup_value bisa berupa nilai, referensi, atau string teks..
2. table_array : (Diperlukan) Tabel informasi tempat data dicari. Gunakan referensi ke sebuah range, tabel, nama range atau nama tabel.
3. row_index_num : (Diperlukan) Nomor baris dalam table_array yang akan mengembalikan nilai yang cocok.
4. range_lookup : (Opsional) Berisi TRUE atau FALSE. Nilai logika yang menentukan apakah kita ingin HLOOKUP mencari kecocokan persis atau kecocokan yang mendekati. Jika TRUE atau dikosongkan menghasilkan kecocokan yang mendekati. Jika FALSE, HLOOKUP akan menemukan kecocokan persis. Jika tidak ditemukan, dikembalikan nilai kesalahan #N/A.

CONTOH PENGGUNAAN

Penjelasan Contoh :

FORMULA	KETERANGAN
=HLOOKUP(F2;A2:D4;2)	Mencari F3 (Apel) pada baris pertama range A2:D4 dan mengembalikan nilai pada baris ke 2 pada range tersebut. Formula ini menghasilkan pesan error karena range_lookup kosong atau bernilai TRUE sedangkan tabel tidak diurutkan dari kecil ke besar atau dari A-Z.
=HLOOKUP(F3;A2:D4;2;TRUE)	Mencari F3 (Apel) pada baris pertama range A2:D4 dan mengembalikan nilai pada baris ke 2 pada range tersebut. Formula ini menghasilkan pesan error karena range_lookup bernilai TRUE sedangkan tabel tidak diurutkan dari kecil ke besar atau dari A-Z.
=HLOOKUP(F4;A2:D4;2;FALSE)	Mencari F4 (Apel) pada baris pertama range A2:D4 dan mengembalikan nilai pada baris ke 2 pada range tersebut.
=HLOOKUP(F5;A2:D4;3;FALSE)	Mencari F5 (Rambutan) pada baris pertama range A2:D4 dan mengembalikan nilai pada baris ke 3 pada range tersebut.
=HLOOKUP(F6;A2:D4;2)	Mencari F6 (Pisang) pada baris pertama range A2:D4 dan mengembalikan nilai pada baris ke 2 pada range tersebut. Meskipun menghasilkan nilai 100 sebenarnya formula ini menhasilkan nilai yang salah. Karena "Pisang sebenarnya tidak ada dalam tabel. :)
=HLOOKUP(F7;A2:D4;2;FALSE)	Mencari F7 (Pisang) pada baris pertama range A2:D4 dan mengembalikan nilai pada baris ke 2 pada range tersebut. Meskipun Formula ini menghasilkan pesan error formula ini menghasilkan nilai yang benar sebab pisang memnag tidak ada dalam tabel. :)

Penulisan serta penggunaan yang benar ada pada Acuan yg saya background hijau.

CATATAN

1. Jika HLOOKUP tidak dapat menemukan lookup_value, dan range_lookup TRUE, maka digunakan nilai terbesar yang kurang dari lookup_value.
2. Jika lookup_value lebih kecil daripada nilai terkecil dalam baris pertama table_array, HLOOKUP mengembalikan nilai kesalahan #N/A.
3. Jika range_lookup FALSE dan lookup_value berupa teks, kita dapat menggunakan karakter wildcard, tanda tanya (?) dan tanda bintang (*), di lookup_value.

Tidak sulit bukan? terkadang dalam penerapannya dua fungsi VLOOKUP dan HLOOKUP memang agak membingungkan. Untuk itu kita harus banyak berlatih dalam menggunakan dua fingsi ini.