Pada postingan kali ini, kita akan membuat record macro untuk memudahkan pekerjaan kita pada microsoft excel. Saya mencontohkan, jika suatu ketika kita diminta mengolah data nilai siswa untuk kepentingan ujian nasional, maka kita bisa menyelesaikannya dengan menggunakan record macro

Dari data tersebut akan kita olah menggunakan record macro

Langkah pertama adalah menyederhanakan tabel agar hanya bagian yang penting saja yang akan terolah. Misalnya dari Ilustrasi 1, hapus baris 1 {Semester} dan kolom A {No} sehingga tabel simpel seperti berikut:

Selanjutnya adalah mengolah data awal tersebut salah satunya dengan men-TRANSPOSE data agar formatnya mirip dengan format akhir. Ini adalah salah satu langkah alternatif bagi orang awam. Dalam hal ini daftar mata pelajaran semula berada di bagian baris atas diubah menjadi bagian kolom paling kiri, sedangkan kolom nama yang tadinya berada pada kolom kiri diubah menjadi bagian baris di atas.

Cara men-TRANSPOSE:

1.    Kopi bagian yang penting saja (dalam contoh ini saya hanya mengkopi range mata pelajaran dan nama siswa).

2.    Tempatkan pointer pada cell dimana data tersebut akan di letakkan.

3.    Klik kanan pada cell tadi pada pilih Transpose pada Paste Option.

Pada kolom A kita lakukan format text yang mengarah vertikal agar mendaftar, blok kolom tersebut, klik kanan, Format Cells -> Alignment -> Ubah orientasi teks menjadi 0 derajat (degre) sehingga hasilnya seperti ilustrasi berikut.

Langkah berikutnya adalah menghapus kolom A (tapi kita mesti ingat urutan mata pelajarannya) dan juga baris 8 dan 15 karena ini tidak kita perlukan, juga border (garis-garis tabel) perlu dihapus. Sisipkan 2 baris kosong di bawah baris nama (untuk judul kolom tabel) dan beri warna beda pada baris nilai semester 4 (baris 10-15), untuk membedakan nilai semester 3, 4, dan 5. Hasilnya seperti ilustrasi berikut.

Pekerjaan berikutnya adalah membuat MACRO (merekam LANGKAH KERJA  dengan RECORD MACRO)

1. Tempatkan Active cell pada B1. Pastikan menge-klik Use Relative References (pada tab menu View -> tab Macros -> klik spin (segitiga kecil) pilih Use Relative References.

2. Kemudian klik Record Macro… kalau kita sudah siap melakukannya, maka akan muncul popup menu isian, isikan seperlunya, jangan lupa memberi nama macro dan membuat shortcut ctrl+…(saya contohkan Ctrl+w). Ini bertujuan jika hendak menjalan macro kita tinggal menggunakan shorcut tersebut.

3. Berikutnya adalah membuat satu tabel secara manual (untuk data satu siswa saja), ubahlah seperti format tabel jadi (seperti ilustrasi 1 di atas). Caranya sisipkan kolom sebelah kanan siswa pertama (cell B1) sebanyak yang diperlukan (dalam contoh ini saya sisipkan sebanyak 8 kolom).

4. Lakukan pekerjaan secara manual: bisa menambahkan Tulisan pada beberapa cell seperti No, Mata Pelajaran, Semester 3, 4, 5 dll, memindah kolom tabel, memformat garis batas (border), memberikan warna cell, menulis rumus perhitungan tertentu dll sesuai format tabel yang kita harapkan. Jika semua sudah selesai format tabel seperti yang diharapkan, letakkan pointer pada cell pada nama siswa diurutan ke-3 (cell K1). Selesai…. Silahkan Klik Stop recording… toolbar-nya ada pada bagian saat kita Record Macro… atau dibagian kiri bawah halaman lembar kerja Excel kita.

Hasil Akhir :

5. Sampai disini kita bisa mencoba kerja macro untuk tabel siswa ke dua, caranya tempatkan pointer pada cell nama siswa ke-3; Tekan shorcut key Ctrl+w seperti yang anda tentukan sebelumnya (saat akan me-record macro).

6. Jika hasilnya persis tabel yang kita buat namun berisi data siswa ke-2 maka itu berarti pekerjaan kita telah benar.

7. Kalau jumlah siswanya banyak, anggaplah 200 siswa, maka kita perlu menekan Ctrl+w tadi sebanyak sisa-nya yang belum dibuatkan tabel. gar hanya cukup sekali klik tombol Ctrl+w kita lakukan sedikit tambahan pada macro. Caranya klik menu macro -> view macro – klik edit dan tambahkan baris (setelah baris komentar yg ditandai awal dengan ‘…..) ketikkan:

Do Until Activecell.Value=""

dan pada bagian bawah sebelum end sub tulisan

Loop seperti ilustrasi berikut:

Coba jalankan macro tadi dan tunggu beberapa detik kemudian data anda akan terubah secara menyeluruh menjadi tabel sesuai yang diharapkan. Data tadi akan diubah menjadi tabel persiswa dan tabelnya berderet ke kanan (secara horisontal)

Selamat mencoba dan semoga bermanfaat!

Adapun langkah-langkah pembuatan form sebagai berikut :

1.      Buka Ms. Excel

2.      Double Klik pada nama worksheet lalu ganti nama Sheet1 menjadi “PARTSDATA” (bisa diganti dengan nama lain).

3.       Pada Row 1 kolom kita buat nama heading untuk tabelnya seperti tampak pada gambar dibawah ini :

4.      Kemudian kita simpan file sebagai excel makro dengan nama Data Barang. File > Save As > Excel Macro Enabled Workbook > Data Barang.

Langkah berikutnya membuat Macro untuk file tersebut, adapun langkah-langkah pembuatan macro sebagai berikut :

1.      Pada worksheet pilih menu view klik tab Macros pilih view macros :

Hingga muncul text dialog seperti di bawah ini :

 2.       Isikan macro name : “FORM” klik create

 3.      . Klik kanan mouse pada VBA Project > Insert > Use Form

4.      Buatlah Label dengan menggunakan toolbox dan letakkan pada Form kemudian ganti Caption tiap label :

Label1 Caption diganti dengan “Kode Barang”
Label2 Caption diganti dengan “Nama Barang”
Label3 Caption diganti dengan “Satuan”
Label4 Caption diganti dengan “Harga”

5.      Kemudian buatlah textbox. Cara membuatnya hampir sama dengan membuat label yaitu dengan menggunakan toolbox dan pilih textbox. Dan jangan lupa ganti nama pada tiap textbox.

TextBox1 Name diganti dengan “tkode”
TextBox2 Name diganti dengan “tnama”
TextBox3 Name diganti dengan “tsatuan”
TextBox4 Name diganti dengan “tharga”

6.      Buatlah Commond Button “TAMBAH” dan “TUTUP”. Jangan lupa ganti name dan caption tiap commond button.

command button1 Caption diganti dengan “TAMBAH”
command button1 Name diganti dengan “CMDTMBH”
command button2 Caption diganti dengan “TUTUP”
command button2 Name diganti dengan “CMDTTP”

7.      Membuat kode pada Commond Button “TAMBAH”. Klik command button “TAMBAH“ > View > Code

Masukan kode dibawah ini (biar gak cape copy paste saja code dibawah ini):

Private Sub CMDTMBH_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(“PARTSDATA”)

‘menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

‘check untuk sebuah kode
If Trim(Me.tkode.Value) = “” Then
Me.tkode.SetFocus
MsgBox “Masukan Kode Barang”
Exit Sub
End If

‘copy data ke database
ws.Cells(iRow, 1).Value = Me.tkode.Value
ws.Cells(iRow, 2).Value = Me.tnama.Value
ws.Cells(iRow, 3).Value = Me.tsatuan.Value
ws.Cells(iRow, 4).Value = Me.tharga.Value

‘clear data
Me.tkode.Value = “”
Me.tnama.Value = “”
Me.tsatuan.Value = “”
Me.tharga.Value = “”
Me.tkode.SetFocus
End Sub

 

8.      Membuat kode pada Commond Button “TUTUP”. Caranya sama dengan membuat kode pada Commond Button “TAMBAH”, hanya saja kodenya yang berbeda. Kode pada Commond Button yaitu :

Private Sub CMDTTP_Click()
Unload Me
End Sub

9.      Untuk menghindari menutup melalui tanda X pada form masukan kode dibawah ini dengan klik kanan mouse pada form dan pilih view code dan ketikan kode dibawah ini :

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox “MAKE TOMBOL ATUH KANG!”
End If
End Sub

10.  Mengisi kode pada module klik kanan module pilih view code dan isikan kode dibawah ini :

Sub FORM()
UserForm1.Show
End Sub

11.  Klik Run > Run Sub/Use Form atau bisa dengan menekan tombol F5

12.  Hasilnya akan menjadi seperti ini

Selesai.

Selamat mencoba dan semoga berhasil!

Input data siswa yang masih menggunakan Excel sebagai pengolahan database siswa memang masih banyak sekali digunakan. Pada postingan saya sebelumnya, saya hanya sebatas menggunakan rumus-rumus excel standar yang biasa dan sering digunakan sehari-hari. Kali ini saya akan membuat sebuah Form Input Data Siswa Berbasis Excel dengan VBA.

Form input data siswa ini saya buat secara lengkap dari mulai NIS, NISN, Nama siswa, Tempat lahir, tanggal lahir, jenis kelamin, alamat, No. HP, No, SKHUN, No. Ijasah, Nama Ibu kandung, tahun lahir ibu kandung, pekerjaan ibu, pendidikan ibu, nama ayah, tahun lahir ayah, pekerjaan ayah, pendidikan ayah, penghasilan orang tua, alamat orang tua.

Field-field ini adalah sesuai dengan data di Aplikasi Dapodikmen untuk SMA. Form input data siswa ini juga dilengkapi dengan tombol "SIMPAN" untuk menyimpan dokumen data siswa yang telah di entri ke dalam database.

Bagaimana cara membuat Form Input Data Siswa Berbasis Excel ini?

1.      Buat sebuah dokumen baru kemudian aktifkan VBA editor dengan menekan kombinasi keyboar ALT+F11 kemudian klik Insert > UserForm

2.      Untuk langkah kedua setelah menyisipkan sebuah form baru maka kita edit ukuran form nya sesuai dengan kebutuhan kemudian buat sebuah label dengan menggunakan Toolbox dan letakkan pada Form kemudian ganti Caption menjadi NIS

3.      Setelah label NIS terbuat silakan buat label berikutnya sesuai dengan Field diatas dan caranya sama dengan tahap 2. Jangan lupa ganti properties Caption sesuai dengan fieldnya

4.      Untuk langkah ke-4 Anda harus membuat beberapa TextBox sebagai syarat untuk pengisian data. Bagi yang belum tahu cara membuat textbox caranya adalah klik TextBox pada Toolbox kemudan drag ke UserForm misalnya untuk membuat textbox nis seperti berikut jangan lupa ganti properties Name menjadi "TXTNis" untuk mendefinisikan supaya tidak lupa dalam membuat kode program VBA nya.

Dari langkah ke-4 tersebut silakan  buat beberapa textbox Anda bisa juga menggunakan teknik copy paste dan silakan ganti propertis name menjadi seperti berikut ini.

Textbox1 ganti dengan    TXTNis
Textbox2 ganti dengan    TXTNama
Textbox3 ganti dengan    TXTTempatLahir
Textbox4 ganti dengan    TXTTglLahir
Textbox5 ganti dengan    TXTALamat
Textbox6 ganti dengan    TXTNISN
Textbox7 ganti dengan    TXTHP
Textbox8 ganti dengan    TXTSKHUN
Textbox9 ganti dengan    TXTIjasah
Textbox10 ganti dengan    TXTNamaIbu
Textbox11 ganti dengan    TXTThnLahirIbu
Textbox12 ganti dengan    TXTPekIbu
Textbox13 ganti dengan    TXTNamaAyah
Textbox14 ganti dengan    TXTThnLahirAyah
Textbox15 ganti dengan    TXTPekAyah
Textbox16 ganti dengan    TXTPengAyah
Textbox17 ganti dengan    TXTAlamatOrtu

Untuk ComboBox silakan buat dengan menggunakan Toolbox dan ganti properties name nya
ComboBox1 ganti dengan CBO CBOKelamin
ComboBox2 ganti dengan CBO CBOPendidikanIbu
ComboBox3 ganti dengan CBO CBOPendidikanAyah

Hasil akhir kira-kira seperti ini

5.      Untuk tahap ke-5 silakan buat 3 buah tombol dengan menggunakan CommandButton seperti diatas yang pertama tombol "Simpan" ke-2 tombol "Cari Data Siswa" dan yang ke-3 "Close" untuk masing-masing tombol silakan ganti properties name menjadi "TBLSimpan", "TBLCariData", CMDClose"

Untuk yang belum tahu cara membuat CommandButton caranya hampir sama dengan membuat texbox tetapi disini toolbox yang dipake adalah CommandButton. Jadi kalo untuk membuat tiga buah tombol maka kita harus membuat 3 buah CommandButton 

6.      Langkah berikutnya adalah langkah membuat script VBA. dan yang pertama silakan View Code kemudian tuliskan script berikut untuk membuat initial pada UserForm

Private Sub UserForm_Initialize()
With CBOKelamin
    .AddItem "Laki-Laki"
    .AddItem "Perempuan"
End With
With CBOPendidikanIbu
    .AddItem "Tidak Sekolah"
    .AddItem "SD"
    .AddItem "SMP"
    .AddItem "SMA"
    .AddItem "D1"
    .AddItem "D2"
    .AddItem "D3"
    .AddItem "S1"
    .AddItem "S2"
    .AddItem "S3"
End With
With CBOPendidikanAyah
    .AddItem "Tidak Sekolah"
    .AddItem "SD"
    .AddItem "SMP"
    .AddItem "SMA"
    .AddItem "D1"
    .AddItem "D2"
    .AddItem "D3"
    .AddItem "S1"
    .AddItem "S2"
    .AddItem "S3"
End With
End Sub

7.      Tahap ke-7 kita akan membuat sebuah script VBA untuk simpan data siswa silakan ketikkan script VBA nya di bawah ini

Private Sub TBLSimpan_Click()
Dim iRow As Long
Dim Ws As Worksheet
Set Ws = Worksheets("databasesiswa")

'menemukan baris kosong pada database siswa
iRow = Ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check untuk sebuah nis
If Trim(Me.TXTNis.Value) = "" Then
  Me.TXTNis.SetFocus
  MsgBox "Masukan NIS terlebih dahulu Kang.."
  Exit Sub
End If


'copy data ke database siswa
Ws.Cells(iRow, 1).Value = Range("X1").Value
Ws.Cells(iRow, 2).Value = Me.TXTNis.Value
Ws.Cells(iRow, 3).Value = Me.TXTNama.Value
Ws.Cells(iRow, 4).Value = Me.TXTTempatLahir.Value
Ws.Cells(iRow, 5).Value = Me.TXTTglLahir.Value
Ws.Cells(iRow, 6).Value = Me.CBOKelamin.Value
Ws.Cells(iRow, 7).Value = Me.TXTAlamat.Value
Ws.Cells(iRow, 8).Value = Me.TXTNISN.Value
Ws.Cells(iRow, 9).Value = Me.TXTHP.Value
Ws.Cells(iRow, 10).Value = Me.TXTSKHUN.Value
Ws.Cells(iRow, 11).Value = Me.TXTIjasah.Value
Ws.Cells(iRow, 12).Value = Me.TXTNamaIbu.Value
Ws.Cells(iRow, 13).Value = Me.TXTThnLahirIbu.Value
Ws.Cells(iRow, 14).Value = Me.TXTPekIbu.Value
Ws.Cells(iRow, 15).Value = Me.CBOPendidikanIbu.Value
Ws.Cells(iRow, 16).Value = Me.TXTNamaAyah.Value
Ws.Cells(iRow, 17).Value = Me.TXTThnAyah.Value
Ws.Cells(iRow, 18).Value = Me.TXTPekAyah.Value
Ws.Cells(iRow, 19).Value = Me.CBOPendidikanAyah.Value
Ws.Cells(iRow, 20).Value = Me.TXTPengAyah.Value
Ws.Cells(iRow, 21).Value = Me.TXTAlamat.Value

'clear data siswa
Me.TXTNis.Value = ""
Me.TXTNama.Value = ""
Me.TXTTempatLahir.Value = ""
Me.TXTTglLahir.Value = ""
Me.CBOKelamin.Value = ""
Me.TXTAlamat.Value = ""
Me.TXTNISN.Value = ""
Me.TXTHP.Value = ""
Me.TXTSKHUN.Value = ""
Me.TXTIjasah.Value = ""
Me.TXTNamaIbu.Value = ""
Me.TXTThnLahirIbu.Value = ""
Me.TXTPekIbu.Value = ""
Me.CBOPendidikanIbu.Value = ""
Me.TXTNamaAyah.Value = ""
Me.TXTThnAyah.Value = ""
Me.TXTPekAyah.Value = ""
Me.CBOPendidikanAyah.Value = ""
Me.TXTPengAyah.Value = ""
Me.TXTAlamatOrtu.Value = ""
Me.TXTNis.SetFocus

'Simpan data
Application.ActiveWorkbook.Save
End Sub

8.      Membuat perintah CLOSE silakan ketikkan script nya dibawah ini dengan cara double klik tombol CLOSE

Private Sub CMDClose_Click()
Unload Me
End Sub

9.      Membuat sebuah validasi data untuk textbox yang berisi data angka agar data yang diinput hanya berupa data angka buka hurfu. Apabila data yang dimasukan pada textbox bukan berupa data angka maka secara otomatis akan menampilkan pesan error dan ini dia script nya

Private Sub HanyaAngka()
    If TypeName(Me.ActiveControl) = "TextBox" Then
        With Me.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Maaf, Masukan data angka saja"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

10.  Tahap 10 Anda bisa menggunakan variasi apabila ketika textbox aktif atau dipilih maka warna akan berubah. Ini dia script nya. Script ini juga akan menghasilkan perintah error yang merupakan pemanggilan prosedur dari tahap 9 diatas.

Private Sub TXTNISN_Change()
HanyaAngka
End Sub

Private Sub TXTHP_Change()
HanyaAngka
End Sub

Private Sub txtnis_Enter()
TXTNis.BackColor = &H80000005
End Sub
Private Sub txtnis_Exit(ByVal Cancel As MSForms.ReturnBoolean)
HanyaAngka
TXTNis.BackColor = &HE0E0E0
End Sub

Private Sub txtnama_enter()
TXTNama.BackColor = &H80000005
End Sub
Private Sub txtnama_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNama.BackColor = &HE0E0E0
End Sub

Private Sub txttempatlahir_enter()
TXTTempatLahir.BackColor = &H80000005
End Sub
Private Sub txttempatlahir_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTTempatLahir.BackColor = &HE0E0E0
End Sub

Private Sub txttgllahir_enter()
TXTTglLahir.BackColor = &H80000005
End Sub
Private Sub txttgllahir_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTTglLahir.BackColor = &HE0E0E0
End Sub

Private Sub txtalamat_Enter()
TXTAlamat.BackColor = &H80000005
End Sub
Private Sub txtalamat_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTAlamat.BackColor = &HE0E0E0
End Sub

Private Sub cbokelamin_Enter()
CBOKelamin.BackColor = &H80000005
End Sub
Private Sub cbokelamin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CBOKelamin.BackColor = &HE0E0E0
End Sub

Private Sub txtnisn_Enter()
TXTNISN.BackColor = &H80000005
End Sub
Private Sub txtnisn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNISN.BackColor = &HE0E0E0
End Sub

Private Sub txthp_Enter()
TXTHP.BackColor = &H80000005
End Sub
Private Sub txthp_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTHP.BackColor = &HE0E0E0
End Sub

Private Sub txtskhun_Enter()
TXTSKHUN.BackColor = &H80000005
End Sub
Private Sub txtskhun_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTSKHUN.BackColor = &HE0E0E0
End Sub

Private Sub txtijasah_Enter()
TXTIjasah.BackColor = &H80000005
End Sub
Private Sub txtijasah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTIjasah.BackColor = &HE0E0E0
End Sub

Private Sub txtnamaibu_Enter()
TXTNamaIbu.BackColor = &H80000005
End Sub
Private Sub txtnamaibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNamaIbu.BackColor = &HE0E0E0
End Sub

Private Sub txtthnlahiribu_Enter()
TXTThnLahirIbu.BackColor = &H80000005
End Sub
Private Sub txtthnlahiribu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTThnLahirIbu.BackColor = &HE0E0E0
End Sub

Private Sub txtpekibu_Enter()
TXTPekIbu.BackColor = &H80000005
End Sub
Private Sub txtpekibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTPekIbu.BackColor = &HE0E0E0
End Sub

Private Sub cbopendidikanibu_Enter()
CBOPendidikanIbu.BackColor = &H80000005
End Sub
Private Sub cbopendidikanibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CBOPendidikanIbu.BackColor = &HE0E0E0
End Sub

Private Sub txtnamaayah_Enter()
TXTNamaAyah.BackColor = &H80000005
End Sub
Private Sub txtnamaayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNamaAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtthnayah_Enter()
TXTThnAyah.BackColor = &H80000005
End Sub
Private Sub txtthnayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTThnAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtpekayah_Enter()
TXTPekAyah.BackColor = &H80000005
End Sub
Private Sub txtpekayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTPekAyah.BackColor = &HE0E0E0
End Sub

Private Sub cbopendidikanayah_Enter()
CBOPendidikanAyah.BackColor = &H80000005
End Sub
Private Sub cbopendidikanayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CBOPendidikanAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtpengayah_Enter()
TXTPengAyah.BackColor = &H80000005
End Sub
Private Sub txtpengayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTPengAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtalamatortu_Enter()
TXTAlamatOrtu.BackColor = &H80000005
End Sub
Private Sub txtalamatortu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTAlamatOrtu.BackColor = &HE0E0E0
End Sub

11.  Untuk tahap terakhir silakan buat sebuah Worksheet untuk menyimpan database yang telah disimpan apabila menekan tombol simpan data maka data siswa akan tersimpan pada sheet yang telah ditentukan misalnya saya ganti sheet untuk menyimpan database dengan nama "DatabaseSiswa"

Selesai.
tahapan membuat sebuah userform untuk input data siswa sudah berhasil dibuat 

untuk menjalankan nya silakan coba klik RUN

Selamat mencoba dan semoga berhasil!

    Setelah sebelumnya kita mempelajari beberapa fungsi sederhana pada Microsoft Excel, kali ini kita akan membahas tentang formula baru yaitu VLOOKUP dan HLOOKUP yang terdapat dalam Fungsi Lookup & Reference. 

    Berikut penjelasan ringkas mengenai VLOOKUP dan HLOOKUP beserta contoh kasusnya.

    1.      VLOOKUP

digunakan untuk menentukan suatu nilai tertentu berdasarkan kondisi dimana data diambil dari tabel sumber secara vertikal.

Sintaks =VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])

Lookup_value	nilai yang dicari pada kolom (vlookup) pertama dari table array (table referensi)
Tabel_array	tabel sumber data atau table suatu text, angka atau nilai logikal dimana data akan diambil
Col_index_num	nomor/urutan kolom pada table referensi dimana data akan diambil
Range_lookup	nilai logikal

 

2.      HLOOKUP
digunakan untuk menentukan suatu nilai tertentu berdasarkan kondisi dimana data diambil dari tabel sumber secara horizontal.

Sintaks

=HLOOKUP(lookup_value;table_array;row_index_num;[range_lookup])

Lookup_value	nilai yang dicari pada kolom (vlookup) pertama dari table array (table referensi)
Tabel_array	tabel sumber data atau table suatu text, angka atau nilai logikal dimana data akan diambil
Row_index_num	nomor/urutan baris pada table referensi dimana data akan diambil
Range_lookup	nilai logikal

Contoh Penggunaan VLOOKUP dan HLOOKUP

Kali ini kita akan membuat daftar penghasilan. 

1. Buatlah tabel dengan data berupa nama pegawai, golongan, dan tabel untuk daftar gaji setiap golongan

2. Untuk mengisi kolom gaji pokok pegawai, kita dapat menggunakan fungsi VLOOKUP dan menggunakan 2 tabel yang telah disediakan. Ketik formula =VLOOKUP(B6;$M$5:$P$24;2) pada sel D6.

3. Ulangi langkah kedua untuk mengisi kolom tunjangan dan transport, tetapi kita perlu mengganti sintaks col_index_num pada rumusnya menjadi 3 untuk tunjangan dan 4 untuk transport

            Tunjangan       : =VLOOKUP(B6;$M$5:$P$22;3)

            Transport        : =VLOOKUP(B6;$M$5:$P$22;4) 

4. Untuk tunjangan suami/istri kita perlu menggunakan fungsi logika IF yang telah kita pelajari sebelumnya. Logikal testnya jika pegawai sudah menikah maka ia mendapatkan tunjangan suami/istri jadi jika diaplikasikan dalam rumus menjadi =IF(G6="K";10%*D6;0)

5. Untuk mendapatkan total gaji, kita hanya perlu menjumlahkan gaji pokok, tunjangan, transport, dan tunjangan suami/istri. Ketik formula =SUM(D6:F6;H6) pada sel I6.

 

6. Buatlah daftar tarif pajak untuk setiap golongan

7. Pada kolom pajak, kita dapat mengisinya dengan menggunakan Fungsi HLOOKUP dengan formula =HLOOKUP(B6;$C$32:$T$33;2;FALSE)*I6  

8. Gaji bersih merupakan selisih dari gaji pokok dengan pajak. Kita dapat menggunakan rumus =I6-J6

Download Contoh Kasus VLOOKUP da HLOOKUP

Selaat belajar dan semoga bermanfaat!