Baik, pada pertemuan kali ini, akan mencoba membahas tentang
penggunaan Excel yang berkaitan dengan menghitung. Apakah yang akan
dihitung? Menghitung gaji pegawai yang bekerja pada suatu perusahaan
atau usaha rumahan dengan upah kerja yang diterima berdasarkan jam
kerja.
Misalnya : Rina adalah seorang bendahara keuangan yang tugasnya
selain memanej keuangan, ia juga ditugaskan untuk menghitung gaji
pegawai, pada perusahaan tempat Rina bekerja menerapkan sistem upah
berdasarkan jam kerja, jika diketahui upah para karyawan per jam sebesar
Rp 15.000. Berapakah jumlah gaji para pegawai yang harus di bayar?
Langkahnya, buat 2 buah tabel yaitu Data Pegawai dan Data Gaji Pegawai seperti berikut.
Pada
tabel Data Pegawai digunakan sebagai referensi data dari para pegawai,
sedangkan pada tabel Data Gaji Pegawai digunakan sebagai penghitungan
gaji para pegawai.
Mari kita eksekusi, pada tabel Data Gaji Pegawai bagian No Pegawai
(sel E6) cara mengisinya kita gunakan fungsi Combo Box. isi dengan no
pegawai secara acak (misal P02 ada di sel E6). Dan lakukan hal yang sama
pada sel E7 sampai E11.
Kemudian untuk mengisi Nama Pegawai (sel F6) masukan rumus berikut.
Untuk mengisi seluruh Nama Pegawai kita gunakan fungsi Auto Fill, Hasilnya sebagai berikut.
Selanjutnya isi pada kolom Jam (Masuk dan keluar) secara manual.
Untuk
mengisi Lama Kerja (sel I6) gunakan rumus =(H6-G6)*24, agar sel I6
sampai sel I11 dapat terisi secara otomatis gunakan fungsi Auto Fill.
Setelah Lama Kerja di dapat, maka selanjutnya mengisi Total bayar, dengan rumus berikut.
Maka
Rina harus mengeluarkan sejumlah uang untuk membayar gaji para pegawai
berdasarkan jam kerja pegawai sebesar yang terdapat pada Total Bayar.
Kesimpulan, menghitung gaji pegawai berdasarkan jam kerja pada excel
tidaklah sulit, hanya dengan memasukan beberapa rumus excel kita dapat
bekerja lebih cepat dan efisien dibanding menghitung dengan manual atau
menggunakan kalkulator.
Semoga bermanfaat, dan tentunya kita jangan lupa untuk terus belajar,
belajar, dan belajar mengikuti kursus online microsoft excel ini hehe.
tetap semangat untuk meraih tujuan yang ingin dicapai.
Cara Mengekstrak Nilai Tunggal dengan Fungsi DGET
Rumus Excel – Salah satu fungsi dalam kategori database adalah
fungsi DGET.
Dengan menggunakan fungsi ini, kita bisa menampilkan sebuah data yang
terdapat dalam suatu kelompok data yang sesuai dengan kriteria tertentu
yang ditulis. Secara lebih mendetail, fungsi DGET pada
Microsoft Excel
ini berguna untuk mengekstrak nilai tunggal yang terdapat pada kolom di
suatu daftar database yang sesuai dengan kriteria yang telah kita
tentukan. Bentuk rumusan fungsi ini adalah sebagai berikut:
=DGET(database, filed, criteria)
Ketika Anda menggunakan fungsi DGET dan
ternyata tidak terdapat suatu rekaman yang sesuai dan cocok dengan
kriteria yang telah dituliskan, DGET akan mengembalikan nilai kesalahan
dengan memunculkan pesan #VALUE. Dan, ketika terdapat lebih dari satu
rekaman yang sesuai dengan kriteria yang ditentukan maka DGET akan
mengembalikan pesan kesalahan dengan #NUM. Rentang sel yang digunakan
untuk menentukan argument kriteria selama mencakup satu label kolom dan
satu sel di bawah label kolom yang digunakan untuk menentukan kriteria.
Fungsi DGET merupakan fungsi yang hanya
menampilkan data tunggal, memiliki keunikan, ataupun tidak ganda namun
tetap sesuai dengan kriteria dan syarat yang telah ditentukan.
Salah satu contoh cara menentukan fungsi DGET adalah sebagai berikut:
Dari gambar di atas, kita bisa
menggunakan fungsi DGET untuk mendapatkan nilai pesanan penjualan pada
agen penjualan di bulan tertentu. Untuk bisa lebih memahami tentang
penggunaan fungsi ini, Anda bisa memasukkan data table tersebut pada sel
D1 hingga F13. Untuk E5 sebaiknya dibiarkan kosong karena di bagian sel
ini Anda akan menempatkan fungsi DGET. Sedangkan untuk penamaan fields,
pada tabel Anda yaitu sel D2 hingga F2. Bagian ini merupakan bagian
dari argument kriteria yang akan digunakan pada fungsi ini.
Langkah-langkah untuk memilih kriteria adalah sebagai berikut:
Kali ini kita akan menemukan data untuk seorang penjual sehingga kita masukkan nama field SalesRep di baris 3.
Di bagian F3, Anda ketik kriteria Harry.
Selanjutnya, pada sel E5 Anda bisa ketikkan judul #Orders ini untuk
menunjukkan bahwa bagian tersebut akan digunakan untuk menemukan fungsi
DGET.
Selanjutnya, Anda sorot pada sel D7 ke
F13 dalam tabel untuk menemukan kisaran. Lalu, Anda klik pada kotak nama
yang ada di atas kolom A. Kemudian, Anda bisa mengetikkan Salesdata ke
dalam kotak nama lalu dilanjutkan dengan menekan tombol Enter pada
keyboard. Hal ini menunjukkan bahwa Anda telah menyelesaikan entri data.
Anda bisa memanfaatkan kotak dialog
untuk memasukkan data pada Function Argument. Selanjutnya tahapan untuk
menampilkan fungsi DGET adalah:
Klik sel E5 yang merupakan lokasi fungsi DGET akan dimunculkan.
Lalu, klik tombol fungsi Wizard (fx) yang terdapat di bagian sebelah bar rumus fungsi.
Pada bagian Search, Anda ketikkan DGET
lalu Anda klik tombol GO untuk mencari fungsinya. Pada kotak dialog
harus bisa menunjukkan DGET dan daftar di Select di suatu jendela
fungsi. Lalu klik OK untuk membuka kotak dialog fungsi DGET.
Sedangkan untuk melengkapi argument,
Anda bisa klik pada bagian Database di
kotak dialog. Masukkan SalesData di rentang nama yang ada di dalam
baris. Pada baris Field, ketik #Orders. Dan klik pada baris Criteria.
Kemudian, sorot di sel D2 ke F3 untuk menunjukkan kisaran, lalu klik OK
untuk menutup kotak dialog. Anda akan melihat jawaban 217 di sel E5 yang
menunjukkan nilai penjualan Harry di bulan ini. Dan, bila Anda klik sel
E5, Anda akan melihat fungsi lengkapnya:
=DGET(Salesdata,”#Orders”,D2:F3) pada bar formula yang ada di bagian atas.
Menampilkan Nilai Tertinggi dan Terendah dengan Fungsi DMAX dan Fungsi DMIN
Rumus Excel
– Kecanggihan teknologi informasi bisa memberikan lebih banyak
kemudahan bagi orang-orang yang mau belajar dan memanfaatkannya dengan
baik. salah satunya adalah adanya program Microsoft Excel
yang bisa diaplikasikan untuk mengetahui berbagai jumlah sesuai dengan
fungsinya. Salah satu kategori pemanfaatan database dengan Excel adalah
dengan adanya fungsi DMAX dan fungsi DMIN. Kedua fungsi ini bisa
membantu untuk memeriksa dan menampilkan daftar informasi nilai terbesar
dengan fungsi DMAX dan mengetahui nilai terendah dengan fungsi DMIN.
Rumus bentuk penulisan untuk fungsi DMAX adalah:
= DMAX (database, field, criteria)
Database merupakan data yang
akan dicari nilainya, field merupakan nilainya, dan criteria adalah
kriterianya. Dengan memanfaatkan fungsi DMAX, kita bisa menghitung
berapa nilai terbesar pada rekaman yang terdapat di field tertentu yang
terdapat pada suatu database yang telah didasarkan pada criteria yang
kita butuhkan ataupun inginkan.
Untuk lebih memudahkan Anda
dalam memahami tentang penggunaan fungsi DMAX pada Excel, Anda bisa
melihat contoh di bawah ini. berikut ini adalah table yang bisa Anda
gunakan untuk menemukan nilai gaji tertinggi dari suatu daftar database
dengan criteria tertentu yang Anda butuhkan. Langkah pertama adalah Anda
beri name box pada sel A3:D20, beri nama data, sedangkan untuk
penulisan gaji harus dimulai dan diakhiri dengan memberikan tanda petik
dua, kriterianya adalah G3:G4.
Melalui table tersebut, Anda bisa menemukan nilai gaji tertinggi dengan cara mengetikkan rumus pada sel G6:
=DMAX(data,”gaji”,G3:G4)
Anda copy rumus tersebut ke kanan hingga J5 dan ke bawah hingga J8.
Sedangkan untuk rumusan fungsi DMIN adalah:
= DMIN (database, field, criteria)
Sedangkan untuk menghitung dan
menampilkan nilai terendah yang kita butuhkan pada criteria dan di
field tertentu kita bisa menggunakan fungsi DMIN.
Anda bisa melihat pada function arguments dari fungsi DMIN sebagaimana berikut ini:
Dari gambar tersebut, kita bisa mengetahui bahwa:
Database: yang berisi tentang
rentang sel tentang database atau daftar. Database tersebut berisi
dengan data yang berhubungan di mana baris informasi tersebut merupakan
rekaman dan kolom data merupakan bidang. Baris pertama pada database
adalah lebil untuk tiap kolom.
Field: merupakan suatu bagian yang mengidentifikasikan tentang kolom yang akan dipergunakan pada fungsi tersebut.
Criteria: merupakan bagian rentang sel yang berisi tentang syarat yang sudah Anda tentukan.
Untuk lebih memahami tentang pemanfaatan fungsi DMIN, kita bisa menyimak salah satu contohnya berikut ini:
Untuk menampilkan gaji terendah pada sel E19 adalah dengan menggunakan rumus berikut ini:
=DMIN(A5:G13,G5,A18:A19)
A5:G13 merupakan penetuan database, G5 merupakan field, dan A18:A19 adalah criteria yang Anda tentukan
Setelah itu, Anda bisa menghitung hasilnya pada baris selanjutnya dengan cara berikut:
Pertama Anda seleksi dengan
cara membuat blok mulai sel A19 hingga E23, kemudian klik menu Data dan
pada Data Tools, Anda bisa klik What if Analysis lalu klik Data Table
dan akan muncul dialog sebagai berikut ini:
Setelah itu, Anda bisa
meletakkan pointer pada Column input cell dan selanjutnya klik sel A19
dan klik OK. Setelah Anda klik OK, maka muncul hasilnya sebagai berikut
ini:
Dari beberapa contoh tersebut, Anda akan lebih memahami bagaimana mengaplikasikan fungsi DMAX dan fungsi DMIN pada
Excel untuk memudahkan Anda dalam menentukan jumlah nilai tertinggi
ataupun terendah di suatu database sesuai dengan criteria yang
ditentukan.
Membuat Isian Data Di Excel Menggunakan Fungsi VBA
Artikel rumus excel
lengkap untuk VBA. Pada kesempatan ini, kita akan membahas tentang
bagaimana cara membuat isian data di excel dengan menggunakan fungsi
VBA. Seperti yang diketahui, VBA merupakan sebuah halaman pada Microsoft Excel
yang digunakan untuk menuliskan code visual basic, dengan code tersebut
kita dapat membuat berbagai form yang dibutuhkan. Dengan VBA tersebut
kita juga dapat memungkinkan membuat sebuah aplikasi di Microsoft Excel.
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.
Membuat Tombol Hapus Dengan VBA Di Excel
Dengan kesaktian yang dimiliki Microsoft Excel,
kita bisa melakukan satu klik sata bisa menghapus seluruh isi lembar
kerja Excel, kok bisa? Tentunya bisa, karena pada Excel telah tertanam
rumus kode VBA yang dapat melakukan penghapusan data hanya dengan satu
tombol hapus. Bagaimana caranya? Ingin tahu, mari ikuti langkahlangkah
berikut.
Langkah pertama, tentunya siapkan terlebih dahulu data Excel seperti berikut.
Setelah data dibuat, selanjutnya arahkan kursor ke tab Developer >> Insert, pilih Command Button (ActiveX Control).
Lalu
posisikan tombol button tersebut sesuai kebutuhan, misalnya dibawah
data tabel. Kemudia nganti nama tombol tersebut dengan nama “Hapus”,
caranya klik kanan pada tombol, pilih Commandbutton Object >>
Edit, hasilnya seperti berikut.
Jika
edit nama sudah selesai, selanjutnya klik kanan lagi pada tombol lalu
pilih View Code, maka akan tampil halaman Microsot Visual Basic for
Application VBA, lalu masukan kode berikut.
Private Sub CommandButton1_Click()
Dim answer As Integer
answer = MsgBox(“Are you sure you want to empty the sheet?”, vbYesNo + vbQuestion, “Empty Sheet”)
If answer = vbYes Then
Cells.ClearContents
Else
‘do nothing
End If
End Sub
Seperti pada tampilan berikut.
Kemudian
simpan halaman tersebut dengan nama Hapus.xlsm, setelah itu kembali
lagi ke lembar kerja Excel, untuk mengaktifkan tombol Hapus, terlebih
dulu ke tab Developer >> Klik Design Mode, lalu sekarang Anda coba
klik tombol hapus, apa yang terjadi?
Iya, akan tampil dialog peringatat, jika Anda klik Yes maka isi dalam tabel akan terhapus semua, seperti berikut hasilnya.
Mudah
bukan? Dengan adanya tombol hapus ini Anda cukup sekali klik bisa
menghapus seluruh data yang tidak diperlukan, bagaimana, apakah Anda
ingin mencobanya? Selamat mencoba…
Cara Membuat Fungsi Input Box Pada Exce
Jika berbicara tentang fungsi dan formula pada Microsoft Excel,
memang sudah tidak bisa diragukan lagi kesaktiannya. Termasuk dengan
fungsi input box ini yang memanfaatkan Excel VBA dengan penggunaan
Command Button, yang jika dirincikan cara kerjanya yakni membuat sebuah
fungsi Tombol pada Excel jika di klik maka akan tampil dialog input box
yang mana dapat menginputkan data dan hasilnya akan tampil di lembar
kerja Excel. Untuk lebih jelasnya, mari simak ulasan berikut ini.
Langkah pertama, seperti biasa siapkan terlebih dahulu lembar kerja
Excel. Kemudian arahkan kursor ke Tab Developer >> Insert. Pilih
Command Button (ActiveX Control).
Setelah
dipilih, maka posisikan button atau tombol tersebut sesuai kebutuhan.
Kemudian klik kanan pada button, lalu pilih “View Code”.
Maka akan tampil halaman Microsoft Visual for Applications (VBA),
jangan lupa simpan dulu halaman tersebut dengan format Inputdata.xlsm.
Kemudian letakan kursor dibawah kode “Private Sub CommandButton1_Click(), lalu masukan masukan kode berikut.
Private Sub CommandButton1_Click()
Dim myValue As Variant
myValue = InputBox(“Masukan Data Anda”)
Range(“E3″).Value = myValue
End Sub
Kemudian simpan halaman tersebut, lalu sekarang kita kembali lagi ke
lembar kerja Excel. Untuk mengaktifkan tombol CommandButton1, terlebih
dahulu kita arahkan kursor ke Tab Developer >> klik Design Mode.
Maka
saatnya mengeksekusi tombol CommandButton1, klik maka akan tampil
dialog Masukan Data Anda. Nah, disini kita coba masukan tulisan “Saya
Senang Belajar Microsoft Excel” pada kotak text, klik OK.
Maka hasilnya akan seperti berikut.
Bagaimana
mudah bukan? Fungsi yang kita coba saat ini merupakan fungsi sederhana,
namun dalam penggunaannya yang lebih luas, bisa dipakai untuk membuat
sebuah aplikasi yang ditujukan untuk melakuakn inputa data secara
efektif dan efisien, Hebat bukan? Semoga Bermanfaat.
Membuat Form Input Data VBA Pada Excel
Pada kesempatan kali ini
akan mencoba berbagi ilmu bagaimana membuat aplikasi input sederhana
dengan memanfaatkan Visual Basic pada Microsoft excel.
Sebagai sedikit gambaran mengenai sistem kerja aplikasi input data
sederhana ini yaitu data yang dimasukkan dalam Sheet1 (Form Input)
dikirim ke dalam Sheet2 sebagai database. Untuk lebih jelasnya berikut
ini langkah pembuatannya :
1. Buka lembar kerja Microsoft Excel, Persiapan awal pada Sheet1 tuliskan data berikut.
2. Buka Sheet2 dan buatlah judul kolom seperti berikut.
3.
Menambahkan tombol Input pada Sheet1, caranya Klik tab menu Developer,
Klik Design mode dalam group Controls, Klik Insert pilih dan klik
Command button dalam group ActiveX Controls, kemudian Tempatkan Command
button dalam lembar kerja sheet1.
4. Klik dua kali tombol insert yang sudah terbentuk dan ketikkan kode
berikut ini pada lembarkerja Visual Basic Editor yang terbuka.
Private Sub cmbInput_Click()
Set info = Worksheets(“sheet1″)
If info.Range(“D4″).Value = “” Or info.Range(“D6″).Value = “” Then
pesan = MsgBox(“PERHATIAN!!!” & vbCrLf & “Kolom nama/kode barang
masih kosong!”, vbOKOnly + vbCritical, “INPUT GAGAL”)
Call Range(“D4″).Select
ElseIf info.Range(“D4″).Value <> “” And info.Range(“D6″).Value
<> “” Then pesan = MsgBox(“Masukkan data sekarang?”, vbYesNo +
vbInformation, “Informasi Data”)
If pesan = vbYes Then
Set dbs = Worksheets(“sheet2″)
linenext = dbs.Cells(Rows.Count, “A”).End(xlUp).Offset(1, 0).Row
dbs.Cells(linenext, 1).Value = Range(“F1″).Value + 1
dbs.Cells(linenext, 2).Value = Range(“D4″).Value
dbs.Cells(linenext, 3).Value = Range(“D6″).Value
End If
End If
End Sub
5. Membuat counter jumlah barang yang sudah dimasukkan Tulis “Jumlah
barang terdaftar :“ pada sel E1 (tanpa tanda petik) Pada sel F1 masukkan
formula =COUNTA(Sheet2!A2:A100)
6. Simpan file dengan nama Input.xlsm (Excel Macro-Enabled Workbook), untuk mencobanya masukan kode barang dan nama barang, kemudian klik Input
7. Maka akan muncul hasil pada sheet2, seperti pada tampilan berikut.
Demikian
cara membuat input data dengan menggunakan VBA pada Excel, hak ini
sangat membantu Anda untuk memasukan data secara cepat dan tepat, tanpa
harus mengedit lagi. Semoga Bermanfaat.
