RECORD MACRO PADA SPREADSHEET

CARA MENGGUNAKAN RECORD MACRO PADA SPREADSHEET

 

Kesempatan kali ini saya akan menjelaskan langkah membuat record macro pada spreadsheet

Berikut langkahnya:

Step 1

Ketikkan data yang akan digunakan dengan cara ketik manual. Seperti contoh dibawah:

Gambar 1.1

Step 2

Pekerjaan yang dapat kita lakukan adalah mengolah data tersebut salah satunya dengan men-TRANSPOSE data agar formatnya mirip dengan format akhir. 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. Copy bagian yang penting saja (dalam contoh ini saya hanya mengcopy 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.

Gambar 2.1

Step 3

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.

Gambar 3.1

Step 4

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 gambar dibawah:

Gambar 4.1

Oh ya sedikit trik terkait fungsi iterasi yang akan kita gunakan, tambahakan pada ujung sebelah kanan kolom akhir (kolom U) tulisan apa saja misalnya stop, agar semua data terproses, nanti kalau sudah beres boleh hapus kolom akhir ini.

Step 5

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

1. Tempatkan Active cell pada W1. Pastikan menge-klik Use Relative References (pada tab menu View -> Tab Macros -> pilih Use Relative References.

Gambar 5.1

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.

Gambar 5.2

3. Berikutnya adalah membuat satu tabel secara manual (untuk data satu siswa saja). Caranya sisipkan kolom sebelah kanan siswa pertama (cell W1) sebanyak yang diperlukan (dalam contoh ini saya sisipkan sebanyak 8 kolom).

Gambar 5.4

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.
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.

Gambar 5.5

7. Nah kalau siswanya banyak sekali anggaplah 200 siswa maka kita perlu menekan Ctrl+w tadi sebanyak sisa-nya yang belum dibuatkan tabel. Jadi tetep capek deh… agar 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:

Gambar 5.6 Edit macro hasil record

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).

Sekian dan Terima Kasih

 

Read more...

Input Data Berbasis Excel

Cara Membuat Form Input Data Siswa Berbasis Excel

 

Pada kesempatan kali ini saya akan menjelaskan cara membuat form input data siswa berbasis excel. Mari simak langkah demi langkah:

STEP  1

Buat sebuah dokumen baru kemudian aktifkan VBA editor dengan menekan kombinasi keyboar ALT+F11 kemudian cari menu Insert dan klik UserForm

Gambar 1

STEP 2

Langkah selanjutnya yaitu menyisipkan sebuah form baru, lalu edit ukuran form nya sesuai dengan kebutuhan kemudian buat sebuah label dengan menggunakan Toolbox dan letakkan pada Form kemudian ganti Caption menjadi NIS

Gambar 2.1

Gambar 2.2

STEP 3

Setelah label NIS terbuat silakan buat label berikutnya sesuai dengan Field diatas dan caranya sama dengan tahap 2 hingga menjadi seperti tampilan berikut. Jangan lupa ganti properties Caption sesuai dengan fieldnya

Gambar 3.1

STEP 4

Untuk langkah ke-4 kita harus membuat beberapa TextBox sebagai syarat untuk pengisian data. Bagi yang belum tahu cara membuat textbox caranya adalah klik TextBox pada Toolbox kemudian 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.

Gambar 4.1

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

Gambar 4.2

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
STEP 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”

Gambar 5.1

Untuk yang belum tahu cara membuat CommandButton caranya hampir sama dengan membuat texbox tetapi disini toolbox yang dipakai adalah CommandButton. Jadi kalo untuk membuat tiga buah tombol maka kita harus membuat 3 buah CommandButton.
STEP 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

STEP 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
STEP 8
Membuat perintah CLOSE silakan ketikkan script nya dibawah ini dengan cara double klik tombol CLOSE
Private Sub CMDClose_Click()
Unload Me
End Sub

STEP 9
Membuat sebuah validasi data untuk textbox yang berisi data angka agar data yang diinput hanya berupa data angka buka huruf. 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

STEP 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

Gambar 10.1

STEP 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”

Sekian dan Terima Kasih

 

Read more...

FORM ISIAN DATA SEDERHANA PADA MICROSOFT EXCEL

CARA MEMBUAT FORM ISIAN DATA SEDERHANA PADA MICROSOFT EXCEL

       Pada kesempatan kali ini saya akan menjelaskan cara membuat form isian data sebagai berikut:

1. Buka file Microsoft Excel
2. Double Klik di nama worksheet lalu ganti nama yang awalnya Sheet1 menjadi “PARTSDATA” (bisa diganti dengan nama lain).
3. Pada row (baris) 1 buatlah nama heading untuk tabelnya seperti tampak pada gambar dibawah ini

Gambar a

4. Kemudian simpanlah file sebagai excel makro dengan cara klik File>klik Save As>ketik nama file pada File Name>pada save as type pilih Excel Macro Enabled Workbook>klik

Gambar b

5. Pada worksheet, pilih tab View, kemudian pada group Macros klik Macros lalu pilih View Macros.

Gambar c

Sehingga akan muncul kotak dialog seperti yang tampak pada gambar di bawah ini

Gambar d

6. Isilah macro name dengan “Form”, kemudian klik Create
7. Klik kanan mouse pada VBA Project>klik Insert>Klik UserForm, atau dapat juga dilakukan dengan cara klik tab Insert>klik UserForm

Gambar e

Gambar f

8. Langkah selanjutnya adalah design form. Dalam hal ini control yang akan kita gunakan adalah Label,Textbox, dan Command Button seperti tampak pada gambar berikut :

Gambar g

Selanjutnya berilah nama dan caption untuk tiap Label :
Label1 Caption diganti dengan “Kode”
Label2 Caption diganti dengan “Nama Barang”
Label3 Caption diganti dengan “Satuan”
Label4 Caption diganti dengan “Harga”

Gambar h

Selanjutnya berilah nama dan caption untuk tiap TextBox :
TextBox1 Name diganti dengan “tkode”
TextBox2 Name diganti dengan “tnama”
TextBox3 Name diganti dengan “tsatuan”
TextBox4 Name diganti dengan “tharga

Gambar i

Selanjutnya berilah nama dan caption untuk tiap CommandButton :
CommandButton1 Caption diganti dengan “TAMBAH”
CommandButton1 Name diganti dengan “CMDTMBH”
CommandButton2 Caption diganti dengan “TUTUP”
CommandButton2 Name diganti dengan “CMDTTP”

9. Langkah selanjutnya adalah membuat kode Klik command button “Tambah” kemudian Klik menu bar > klik view code atau klik kanan mouse pada CommandButton “TAMBAH” > pilih View Code

Gambar j

Kemudian, masukanlah kode 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

Setelah itu, lanjutkan dengan pengisian code pada tombol Tutup. Masukanlah kode di bawah ini :

Private Sub CMDTTP_Click()
Unload Me
End Sub

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
Setelah itu, isi code pada module dengan cara klik kanan module>klik View Code dan isikan kode dibawah ini :
Sub FORM()
Show
End Sub
Nah, berikut adalah Gambar Form Code (tempat untuk mengisi/mengetikkan kode)

Gambar k

Sekarang kita kembali ke worksheet kemudian kita akan membuat tombol untuk mengaktifkan form.

1. Buat rectangle seperti digambar tersebut
2. Masukkan kode dengan cara klik kanan mouse pada rectangle tersebut>klik “Assign Macro”

Gambar l

Sehingga akan muncul kotak dialog “Assign Macro”>pilih “form data barang”>OK

 

Gambar m

Apabila teman-teman mengikuti tahapan membuat form isian data sederhana secarabenar, maka hasil akhirnya akan tampak seperti gambar di bawah ini :

 

Gambar n

Terima kasih atas perhatiannya semoga dapat berguna bagi kita semua

Read more...

Penggunaan Fungsi VLOOKUP Dan HLOOKUP Pada Excel

PENGGUNAAN FORMULA DAN FUNGSI PADA MICRSOFT EXCEL

(Lanjutan)

 

PENGGUNAAN FUNGSI VLOOKUP DAN HLOOKUP

a. FUNGSI VLOOKUP

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

Berikut cara untuk menggunakan vlookup:

1. Pada kolom D5 isikan rumus seperti dibawah
2. Salin (drag and drop) dari O3 s/d R20
3. Beri Absolut pada O3:R20 dengan menekan f4. Maka hasilnya akan menjadi seperti ini $O$3:$R$20
4. Kemudian pilih kolom berapa yang akan digunakan lalu pilih False agar hasil yang muncul bernilai benar

Gambar a

Tabel Vlookup 

Gambar a.1

Keterangan :

>>Untuk mencari tunjangan dan transport, kita dapat mengganti angka 2 pada rumus masing-masing dengan angka 3 dan 4. Angka 3 dan 4 menunjukkan letak kolom untuk data tunjangan dan transport pada daftar gaji

b. FUNGSI HLOOKUP

   Hlookup berfungsi untuk menentukan suatu nilai tertentu berdasarkan kondisi dimana data diambil dari tabel sumber secara horizontal.

Berikut cara untuk menggunakan Hlookup:

1. Pada sel J5 ketikan formulanya seperti contoh di bawah
2. Salin (drag and drop) dari O22 s/d AF23
3. Beri tanda absolut pada O22:AF23 dengan menekan f4. Maka hasilnya akan jadi seperti ini: $O$22:$AF$23
4. Kemudian pilih baris berapa yang akan digunakan lalu pilih False agar hasil yang muncul bernilai benar

Gambar b

Tabel Hlookup

Gambar b.1

2. PENGGUNAAN FUNGSI PADA TEKS

Actually, banyak sekali fungsi pada teks. Namun, kali ini saya akan menjelaskan sedikit tentang penggunaan fungsi teks untuk nama depan, tengah, dan belakang. Berikut table untuk formula mencari nama depan, tengah, dan belakang.

Gambar 2

Akan tetapi, rumus diatas tidak dapat berlaku untuk seluruh nama. Formula diatas hanya berlaku untuk nama yang dimasukkan pada formula itu saja. Sehingga saya akan memberikan rumus yang dapat digunakan untuk segala bentuk nama seperti gambar dibawah

>>Nama Depan

>>Nama Tengah

>>Nama belakang dengan tiga kata nama

>>Nama belakang dengan dua kata nama

3. PENGGUNAAN FUNGSI IF

      Fungsi IF digunakan untuk menentukan dua kondisi jika kondisinya terpenuhi maka hasil pertama yang keluar dan jika kondisinya tidak terpenuhi maka yang keluar adalah hasil yang kedua.

Sintaks untuk formula if: =If(logial_test,[value_if_true],[value_if_false])

Gambar 3

4. PENGGUNAAN FUNGSI OR, AND, DAN NOT

a. Fungsi Or

    Fungsi Or digunakan untuk menghasilkan hasil dimana terdapat beberapa kondisi. Jika salah satu kondisi terpenuhi maka hasil yang muncul adalah benar.

Gambar a

b. Fungsi And

      Fungsi And digunakan untuk menghasilkan hasil dimana terdapat beberapa kondisi. Jika salah satu kondisi tidak terpenuhi maka hasil yang muncul adalah salah.

Gambar b

c. Fungsi Not

   Fungsi Not digunakan untuk menghasilkan hasil dimana terdapat beberapa kondisi. Jika seluruh kondisi tidak terpenuhi maka hasil yang muncul adalah salah.

Gambar c

Sekian dan terima kasih

 

Read more...

Penggunaan Operasi Fungsi yang Terdapat Pada Excel

PENGGUNAAN FORMULA DAN FUNGSI PADA MICROSOFT EXCEL 

 

OPERASI STANDAR

a. Operasi penjumlahan, pengurangan,perkalian, penjumlahan, dan perpangkatan

Gambar a

Keterangan:

>> Untuk operasi standar di excel, jika terdapat operasi perkalian dan pembagian dalam satu operasi bilangan maka harus diberi tanda kurung pada proses yang hendak dilakukan pertama kali.

Seperti  contoh : 2/4×5 –> maka untuk formulanya =2/(4*5)

>> Untuk operrasi perpangkatan, jika terdapat perpangkatan dimana pangkatnya bilangan pecahan maka pangkat tersebut harus diberi tanda kurung.

Seperti contoh : 2^{1/ 2} + 3^{–1 –>} maka untuk formula excel nya =2^(1/2)+3^-1
 >> Untuk bentuk operasi bilangan yang lainnya sama dengan standar matematika
b. Operasi Logaritma dan Akar Kuadrat

Gambar b

Keterangan :
>> Untuk perpangkatan hanya bisa digunakan untuk akar kuadrat, selain akar kuadrat harus menggunakan pangkat bilangan pecahan seperti contoh operasi penjumlahan, pengurangan, perkalian, penjumlahan, dan perpangkatan.

2. PENGGUNAAN ABSOLUT DAN SEMI ABSOLUT

a. Absolut

>> Pada sel D6 Ketikan =C6*B3 kemudian tekan f4 lalu (ENTER)
>> Copylah formula dari sel D6 tersebut s/d sel D10, dengan cara drag tanda + (pada pojok kanan bawah sel)

Gambar a

b. Semi Absolut

Bisa kita lihat, sel B2 merupakan hasil penjumlahan dari B1 dan A2. Contoh lainnya dapat kita lihat pada sel D7 yang merupakan hasil penjumlahan antara sel D4 dan sel A7, dan demikian seterusnya. Tentu akan sangat merepotkan apabila kita harus mengetik formula tersebut pada setiap sel, bukan?

Nah, berikut merupakan cara efektif untuk mengatasinya :

1. Buat formatnya (lihat Gambar 2.2.1)
2. Pada sel B1 ketikkan =B1+A2
3. Klik sel B1 lalu tekan f4 beberapa kali sampai B1 berubah menjadi B$1
4. Kemudian klik sel A2 lalu tekan f4 beberapa kali sampai A2 berubah menjadi $A2
5. Tekan (enter)
6. Salin (drag and drop) dari B2 s/d E5

Simbol $ (dollar) menunjukan ketetapan, atau dalam excel dikenal dengan sebutan “absolut”. Jika formula tersebut diberi $, maka posisi sintaxnya tidak akan berubah meskipun di lakukan drag and drop (tergantung pemberian tanda $).

Sebagai contoh,  lihatlah Gambar b B$1 menyatakan bahwa baris ke satu bersifat absolut/tetap dan $A2 menunjukan bahwa kolom A bersifat absolut.

 

Gambar b

3. PENGGUNAAN FUNGSI STATISTIKA

a. Penggunaan formula SUM,MIN,MAX,AVERAGE, dan COUNTA

Rumus SUM,MIN,MAX,AVERAGE, dan COUNTA dapat kita lihat pada Gambar a (bagian kanan bawah).

Penggunaan fungsi statistika hanya sekadar penamaan saja, sebenarnya formula di bawah bisa digunakan untuk permasalahan yang lainnya juga.

Gambar a

4.  PENGGUNAAN FUNGSI COUNTIF, COUNTA, DAN SUMIF

a. Fungsi COUNTIF

>> Masukan formulanya (lihat Gambar a; sel I8), lalu tekan Enter
>> Kemudian salin (drag and drop) dari sel I8 s/d I13

Gambar a

b.  Fungsi COUNTA

>> Counta digunakan untk menghitung semua banyaknya datum termasuk data teks
>> Masukan formulanya (lihat Gambar b; sel J8), lalu tekan Enter
>> Klik kanan pada cel J8, pilih format cells, lalu pilih percentage, lalu OK
>> Salin (drag and drop) dari sel J8 s/d J13

Gambar b

c. Fungsi SUMIF

>> Rumus SUMIF dapat kita lihat pada Gambar c (bagian kanan bawah).

Gambar c

5. PENGGUNAAN FUNGSI LOGIKA

a. Fungsi And, Or, dan Not

Gambar a

Keterangan :

Fungsi AND bernilai benar (TRUE) jika semua syaratnya terpenuhi.

Cara mengunakannya :

1. Pada sel D3 ketikan =AND(A3>0;B3>0;C3>0) kemudian tekan ENTER
2. Salin sintaxnya (drag and drop) dari sel D3 s.d D5. Sel tersebut akan bernilai benar (TRUE) hanya apabila ketiga kriteria syarat tersebut lebih dari 0, jika ada salah satu dari ketiga syarat tersebut tidak terpenuhi, maka yang keluar adalah (FALSE).

Fungsi OR akan bernilai benar (TRUE) jika ada salah satu syarat yang terpenuhi.

Cara menggunakannya:

1. Pada sel E3 ketikkan =OR(A3>0;B3>0;C3>0) kemudian tekan ENTER
2. Salin sintaxnya (drag and drop) dari sel E3 s.d E5. Kolom OR akan bernilai benar apabila ada salah satu dari ketiga syarat tersebut terpenuhi. Setiap ada salah satu categori selnya yang benar, maka hasilnya benar (TRUE).

Fungsi NOT digunakan untuk kebalikan dari nilai benar. Maksudnya, jika logika yang dimasukan bernilai benar, maka hasilnya (FALSE).

Cara menggunakannya :

1. Pada sel F3 ketikan=NOT(D3=E3)
2. Salin sintaxnya (drag and drop) dari sel F3 s.d F5

 Jadi itu saja dari saya sekian dan terima kasih

 

Read more...