Senin, 02 Januari 2017

Mencari Nilai Terendah/tertinggi Dengan VLOOKUP


Formula MIN/MAX yang disediakan oleh Microsoft Excel merupakan formula yang sering digunakan selain SUM ataupun AVERAGE, dimana kegunaannya adalah untuk mencari nilai tertinggi atau terendah yang terdapat dalam sebuah data. Tutorial kali ini rumahexcel akan membahas tentang bagaimana cara mengkombinasikan antara formula MIN/MAX dan VLOOKUP.
Dalam tampilan berikut, kombinasi antara MIN/MAX dan VLOOKUP akan mencari nilai (angka) terendah atau tertinggi yang terdapat dalam kolom A dan akan menghasilkan nilai yang sesuai dari data yang terdapat di kolom B .

+ Buatlah tabel dengan kolom A merupakan data nilai rapor
+ Dan kolom B merupakan nama siswa
+ Kolom D2 berikan formula untuk mencari nilai yang paling rendah =VLOOKUP(MIN(A2:A7);A2:B7;2;0)
+ Kolom E2 berikan formula untuk mencari nilai yang paling tinggi =VLOOKUP(MAX(A2:A7);A2:B7;2;0). Lihat ilustrasi tabel dibawah ini

A B C D E
1 NILAI NAMA - Nilai terendah Nilai tertinggi
2 79 Wasis Zain - Gilbert Pendi
3 88 Pendi - =VLOOKUP(MIN(A2:A7);A2:B7;2;0) =VLOOKUP(MAX(A2:A7);A2:B7;2;0)
4 80 Ahmad -
5 65 Gilbert -
6 68 Filbert - - -
7 85 Abu Bakar - - -

Dari tampilan di atas, range A2:A7 merupakan range yang digunakan untuk mencari angka terkecil/terbesar, sedangkan range A2:B7 berisi keseluruhan data.
Jika terdapat nilai terkecil/terbesar yang sama (ganda) maka hasil yang didapatkan merujuk pada nilai yang terdapat di baris paling atas.

Untuk mempelajari lebih lanjut tentang cara mencari nilai terendah/tertinggi dengan VLOOKUP, silahkan anda pelajari artikel semakna dengan Mencari Nilai Terendah/tertinggi Dengan VLOOKUP pada link dibawah ini

>> Memahami Range_Lookup pada Fungsi VLOOKUP
>> Memahami dan Menggunakan Fungsi Vlookup

Hasil Editan Terakhir

Saya sengaja kembali menulis artikel rumah excel dalam alamat sub domain iparengan.com, agar pembaca bisa dengan mudah menemukan artikel yang sedang dicarinya, dan artikel ini bukan berarti merupakan editan terakhir dari iparengan.com, melainkan tutorial untuk menampilkan sebuah informasi perubahan terakhir yang dilakukan dari sebuah data. Dalam Windows Explorer informasi seperti ini di cantumkan dalam sebuah kolom yang bernama Data Modified yang artinya kapan data tersebut terakhir dimodifikasi.

Didalam Microsoft Excel, kita akan meniru konsep seperti yang ada di Windows Explorer, hanya saja perlakuannya untuk baris-baris data. Untuk lebih jelasnya lihat tampilan berikut.


Dari tampilan di atas dapat disimpulkan bahwa, Segala bentuk perubahan yang terjadi di kolom A akan menampilkan informasi di kolom B dan kolom C, di mana kolom B berisi kapan data di baris tersebut di buat sementara kolom C berisi kapan data di baris tersebut di modifikasi.

Untuk membuat seperti contoh di atas, tempelkan baris kode berikut di dalam sheet yang diingikan dan pilih prosedur Worksheet_Change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Cells.Count = 1 Then
If Len(Target.Value) > 0 And _
Target(1, 2) = "" Then
Target(1, 2) = Now
Target(1, 2).NumberFormat = "dd-mmm-yyyy hh:mm:ss"
Target(1, 3) = Now
Target(1, 3).NumberFormat = "dd-mmm-yyyy hh:mm:ss"
ElseIf Len(Target.Value) > 0 And _
Target(1, 2) = "" Then
Target(1, 3) = Now
Target(1, 3).NumberFormat = "dd-mmm-yyyy hh:mm:ss"
Else
Target(1, 2).ClearContents
Target(1, 3).ClearContents
End If
End If
End If
End Sub

Demikianlah tips kali ini tetang Hasil Editan Terakhir, silahkan aplikasikan tips ini dengan menggunakan microsoft excel visual basic for applicatin, semoga dapat dipahami

Jangan Tambah Sheet Ya

Sheet atau Worksheet adalah lembar atau halaman-halaman yang terdapat di dalam buku kerja Excel (WorkBook) yang secara default hanya berisi 3 halaman yang siap di gunakan. Kita masih bisa menambahkan lembar kerja, merubah nama setiap lembarnya atau mungkin menghapusnya.

Microsoft Excel tidak memberikan batasan atau bahkan larangan kepada pengguna jika ingin menambah lembar kerja jika masih kurang. Namun dengan kode Macro VBA, kita bisa meminta kepada Microsoft Excel untuk mencegah pengguna agar tidak bisa menambahkan sheet (dengan cara apapun) di dalam buku kerjanya. Bagaimana caranya???

Jika buku kerja anda tidak ingin ditambah sheet lagi, maka tempelkan baris kode berikut di dalam ThisWorkbook untuk Procedure NewSheet.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Sh.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
MsgBox "Jangan Tambah Sheet Lagi Ya...."
End Sub
Setelah anda menambahkan kode macro diatas maka setiap ali anda akan menambahkan sheet baru akan ada alert atau kotak peringatan "Jangan Tambah Sheet Lagi Ya....", tips ini akan berjalan dengan lancar ketika macro security pada microsoft excel anda telah aktif, jika tidak maka anda harus mengaktifkanya terlebih dahulu, lihat artikel sebelumnya tentang cara mengaktifkan macro


Demikianlah tips kali ini tetang Jangan Tambah Sheet Lagi Ya, silahkan aplikasikan tips ini dengan menggunakan microsoft excel visual basic for applicatin, semoga dapat dipahami

Memahami Range_Lookup pada Fungsi VLOOKUP


Tutorial kali ini adalah lanjutan dari tutorial sebelumnya (Memahami dan Menggunakan Fungsi Vlookup)yang masih membahas Fungsi VLOOKUP. Akan tetapi, lebih di spesifikasikan tentang kaidah penggunaan pilihan Range_LookUp pada fungsi tersebut.

Pada sebagian pengguna Fungsi VLOOKUP, pilihan Range_LookUp hampir sering diabaikan - hal ini memang bagian ini hanya bersifat pilihan saja. Meskipun demikian, pilihan ini bisa sangat berpengaruh terhadap hasil akhirnya.

Pilihan yang disediakan untuk Range_LookUp ada 2, yakni TRUE dan FALSE.
TRUE atau bisa juga ditulis menggunakan angka 1, mempunyai arti bahwa hasil pencarian mengabaikan nilai atau data yang sama persis, pada beberapa kasus penggunaan nilia TRUE mungkin akan menghasilkan nilai yang tidak diharapkan atau bahkan nilai Error.
+ Contoh penulisannya
=VLOOKUP(C1;A1:B10;2;TRUE)
+ atau
=VLOOKUP(C1;A1:B10;2;1)
+ atau
=VLOOKUP(C1;A1:B10;2)
FALSE atau bisa juga ditulis dengan angka 0, berfungsi untuk mencari data antara dua atau lebih tabel yang sama persis dan pada umumnya dibutuhkan ketika melakukan pencarian data berupa teks.
Contoh penulisannya
=VLOOKUP(C1;A1:B10;2;FALSE)
+ atau
=VLOOKUP(C1;A1:B10;2;0)

TRUE bisa ditulis dengan angka 1
FALSE bisa ditulis dengan angka 0

Untuk lebih jelasnya, lihat perbandingan antara TRUE dan FALSE berikut :

Tabel data

NO A B C
1 TABEL DATA-
2 NAMA PENDAPATAN -
3 andi 50.000 / hari
4 ani 250.000 / hari
5 andika 150.000 / hari

Contoh 1 andi

NO D E F
1 FUNGSI VLOOKUP
2 pencarian LOOKUP TRUE LOOKUP FALSE
3 - =VLOOKUP(D4;A3:B10;2;TRUE) =VLOOKUP(D4;A3:B10;2;FALSE)
4 andi 50.000 / hari 50.000 / hari

Contoh 2 ani

NO D E F
1 FUNGSI VLOOKUP
2 pencarian LOOKUP TRUE LOOKUP FALSE
3 - =VLOOKUP(D4;A3:B10;2;TRUE) =VLOOKUP(D4;A3:B10;2;FALSE)
4 ani 250.000 / hari 250.000 / hari

Contoh 3 andika - lihat yang berwarna merah

NO D E F
1 FUNGSI VLOOKUP
2 pencarian LOOKUP TRUE LOOKUP FALSE
3 - =VLOOKUP(D4;A3:B10;2;TRUE) =VLOOKUP(D4;A3:B10;2;FALSE)
4 andika 50.000 / hari 150.000 / hari

Semoga bisa menambah pemahaman Fungsi VLookUp Anda !!!
Demikianlah tips kali ini tetang Memahami Range_Lookup pada Fungsi VLOOKUP, silahkan aplikasikan tips ini dengan menggunakan microsoft excel visual basic for applicatin, semoga dapat dipahami

Minggu, 01 Januari 2017

Memahami dan Menggunakan Fungsi Vlookup


Salah satu fungsi yang sangat popular, sangat sering digunakan dalam Excel serta merupakan salah satu tool yang fantastis adalah VLOOKUP. Fungsi ini digunakan untuk melihat sekumpulan data dalam sebuah tabel, yang dimanfaatkan tidak secara langsung melainkan sebagai datau Acuan saja.

Syntax VLOOKUP

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

Untuk lebih mudah memahami fungsi vlookup, ikuti langkah-langkah berikut :

Menyiapkan DATA TABEL (data Induk)

Disebut sebagai data induk karena data-data inilah yang nantinya kita jadikan sebagai patokan atau hasil yang akan kita panggil sesuai dengan kriteria yang kita masukkan. Untuk itu, persiapkan sebuah data seperti dalam tabel DATA INDUK berikut :

No A B CD
1 ACUAN NILAI YANG DITAMPILKAN
2 60 F--
3 65 E--
4 70 D--
5 75 C--
6 80 B--
7 85 A--
8 90 A+--
9 --
10 --
11 --
Untuk pengisian data yang terdapat di kolom A, mulai dengan nilai yang terkecil di akhiri dengan nilai tertinggi
Hal ini akan berpengaruh terhadap hasil akhir nanti

Membuat DATA TAMBAHAN

Sebenarnya istilah nama 'Data tambahan' ini hanya sebatas istilah saja guna memahami penggunaan fungsi VLOOKUP. Didalamnya terdapat sebuah kolom yang sengaja dikosongkan untuk nanti akan diisi (secara otomatis) sesuai dengan nilai atau data yang terdapat dalam 'Data Induk'

Sebagai contoh untuk tutorial ini, kita akan membuat sebuah data berupa Kumpulan Nilai Ujian Sementara, seperti tampak pada tabel DATA TAMBAHAN berikut

No E F G H I J
1
2 NAMA SISWA B ARAB AQIDAH SEJARAH ISLAM RATA RATA PREDIKAT
3 Ahmad 88 94 93 91,7
4 Pa'Had 75 77 68 73,3
5 Lisya 65 73 68 68,7
6 Isya 81 92 83 85,3
7 Fendi 80 81 78 79,7
8 Ahmad 61 68 60 63,0
9 Wasis 78 88 85 83,7
10 - -

Penjelasan

Kolom No, Nama Siswa, Indo, Inggris, Mtk di isi secara manual
kolom rata-rata di isi menggunakan fungsi Average
Kolom predikat biarkan kosong untuk kemudian nanti kita isi menggunakan fungsi VLOOKUP

Mengisi Kolom Predikat

Acuan pengisian kolom Predikat ini adalah berdasarkan nilai rata-rata, sehingga ketika nilainya (hampir) sama dengan nilai yang terdapat di Data Induk (kolom A) maka akan menghasilkan nilai yang terdapat di kolom sebelah kanannya (Kolom B)

Saya mengasumsikan Anda sudah selesai mengisikan data-data seperti tabel di atas, selanjutnya klik sel J3 dan ketikkan fungsi berikut :

No E F G H I J
1
2 NAMA SISWA B ARAB AQIDAH SEJARAH ISLAM RATA RATA PREDIKAT
3 Ahmad 88 94 93 91,7 =VLOOKUP(I3,$A$2:$B$8,2,TRUE)
4 Pa'Had 75 77 68 73,3

atau

No E F G H I J
1
2 NAMA SISWA B ARAB AQIDAH SEJARAH ISLAM RATA RATA PREDIKAT
3 Ahmad 88 94 93 91,7 =VLOOKUP(I3,$A$2:$B$8,2)
4 Pa'Had 75 77 68 73,3

+ I3 = Kriteria pencarian
+ $A$2:$B$8 = Range dari DATA INDUK
+ 2 = kolom ke-2 dari DATA INDUK, dalam contoh ini mengacu pada Kolom B
+ TRUE) = Mengabaikan nilai yang sama persis antara kriteria (Kolom I3) dengan Acuan (Kolom A) dan mencari nilai yang hampir sama antara kedua

Skema VLOOKUP

DATA INDUK dengan DATA TAMBAHAN


Langkah selanjutnya adalah tinggal Drag hasil yang terdapat di kolom J3 ke sel bawahnya.


Demikianlah tips VBA untuk hari ini, tips Memahami dan Menggunakan Fungsi Vlookup bisa diaplikasikan dengan menggunakan aplikasi microsoft excel visual basic for application (VBA), jika ada kesulitan dan pertanyaan seputar microsoft excel silahkan tanyakan pada kolom komentar dibawah, terimakasih.

Sabtu, 31 Desember 2016

Menjumlahkan Data Berdasarkan Warna Sel


Mengenal Excel sudah hampir dipastikan sudah mengenal fungsi dasar aplikasi ini. Ya... fungsi dasar yang dimaksud disini adalah fungsi penjumlahan (SUM) yang pastinya sangat familiar dikalangan pengguna Excel, baik untuk pemula terlebih untuk tingkat lanjut

Varian dari fungsi SUM dalam menjumlahkan suatu data berupa angka yang terdapat dalam sel atau range antara lain; fungsi SUMIF, Fungsi SUMIFs, Fungsi SUMPRODUCT, dan lainnya. Akan tetapi, beberapa dari kita lebih umum dan sering menggunakan fungsi SUM standard ketimbang yang lainnya.

Andai kata kita hanya menginginkan untuk menjumlah nilai berdasarkan suatu kondisi atau kriteria tertentu yang mana kriteria tersebut kita tentukan, maka dapat menggunakan Fungsi SUMIF/s, begitu pula untuk kasus-kasus penjumlahan yang lainnya kita dapat menggunakan varian dari Fungsi SUM.

Akan tetapi Jika kriteria atau kondisi yang dimaksud adalah warna, maka tentunya tidak dapat dilakukan dengan varian dari fungsi SUM standard ini. Lihat ilustrasi berikut untuk lebih jelasnya.


Hasil penjumlahan tersebut didapatkan dari angka-angka dengan kriteria warna sebagai kuncinya. Untuk melakukan penghitungan berdasarkan kriteria seperti tersebut di atas, dibutuhkan sedikit bantuan kode-kode VBA yang dijadikan sebagai function

Fungsi VBA

+ .Aktifkan jendela Visual Basic Editor (tekan Alt + F11)
+ .Klik menu Insert > module
+ .Tempelkan kode berikut

Function HitungWarna(Data As Range, Warna As Range) As Double
Dim Wrn As Range, Jml As Double, ColorIndex As Integer

ColorIndex = Warna.Cells(1, 1).Interior.ColorIndex
Jml = 0
On Error Resume Next
For Each Wrn In Data.Cells
If Wrn.Interior.ColorIndex = ColorIndex Then
Jml = Jml + Wrn.Value
End If
Next Wrn
On Error GoTo 0
Set Wrn = Nothing
HitungWarna = Jml
End Function

Menggunakan Fungsi

+ .Format penulisan fungsi ini adalah

=HitungWarna(range;kriteria warna)

Contoh Menggunakan Fungsi

=HitungWarna(A1:A10;C1)
Range A1:A10 adalah kumpulan sel (dengan warna-warna tertentu) yang ingin dijumlah
Range C1 adalah sel dengan background warna tertentu yang dijumlahkan

Ada baiknya untuk penggunaan warna sell (Fill Color) adalah warna-warna standard atau yang umum digunakan seperti yang tercantum dalam Index Warna yang tersedia pada microsoft excel

Demikianlah tips VBA untuk hari ini, tips Menjumlahkan Data Berdasarkan Warna Sel bisa diaplikasikan dengan menggunakan aplikasi microsoft excel visual basic for application (VBA), jika ada kesulitan dan pertanyaan seputar microsoft excel silahkan tanyakan pada kolom komentar dibawah, terimakasih.

Kamis, 29 Desember 2016

Link ke Internet dengan Kode VBA Excel


Internet Link with VBA Excel - Banyak sekali yang bisa dilakukan dengan sebuah Kode VBA yang sebagian kecil iparengan.com sudah sharing diblog ini, tentunya dengan harapan dapat membantu mengatasi masalah Microsoft Excel Anda.

Salah satu hal yang dapat dilakukan dengan memanfaatkan kode VBA Excel adalah membuat Link ke Internet, Kode berikut ini berfungsi untuk langsung menuju ke sebuah halaman website (browser default tergantung dari pengaturan pengguna komputer)

Sub LinkKeInternet
ActiveWorkbook.FollowHyperlink "http://www.iparengan.com"
End Sub

Tempatkan kode tersebut di sebuah CommandButton atau yang lainnya, sehingga ketika pengguna meng-klik tombol tersebut, maka membuka sebuah jendela browser (jika belum terbuka) dengan alamat website yang sudah tertulis dalam sebuah kode diatas (http://www.iparengan.com)

Kode diatas dapat berfungsi dengan baik ketika sebuah komputer terhubung dengan jaringan internet, artinya ketika komputer dalam keadaan off-line maka sebuah pesan error akan ditampilkan. Untuk mengatasi permasalah ini, tambahkan sebuah perintah On Error Resume Next, sehingga meskipun pengguna meng-klik kode tersebut sedangkan komputer dengan keaadaan off-line, pesan error akan diabaikan. Lihat cara mengatasi error berikut ini

Sub LinkKeInternet
On Error Resume Next
ActiveWorkbook.FollowHyperlink "http://www.iparengan.com"
End Sub

Atau menggunakan On error GoTo . . .

Sub LinkKeInternet
On Error GoTo EE
ActiveWorkbook.FollowHyperlink "http://www.iparengan.com"
Exit Sub
EE : Msgbox "Komputer anda sedang offline," ,"Aktifkan koneksi internet anda"
End Sub

Ganti alamat website (http://www.iparengan.com) tersebut dengan alamat website yang anda inginkan.

Link ke Internet dengan Kode VBA Excel

Anda juga bisa memanfaatkan Textbox untuk mengisikan alamat website yang akan dikunjungi, caranya
+ Masuk jendela VBE
+ Pilih insert Userform dengan kontrol Textbox name Textbox1 dan satu tombol Commandbutton dengan Name Commandbutton1
+ Masukan kode macro sebagaimana kode macro diatas dengan ganti alamat website dengan Textbox1 pada event Commandbutton Click seprti dibawah ini

Private Sub CommandButton1_Click()
On Error GoTo EE
ActiveWorkbook.FollowHyperlink Textbox1
Exit Sub
EE : Msgbox "Komputer anda sedang offline," ,"Aktifkan koneksi internet anda"
End Sub

+ Silahkan coba kreasi anda dan simpan dengan Enable Macro

Demikianlah tips VBA untuk hari ini, tips Link ke Internet dengan Kode VBA Excel bisa diaplikasikan dengan menggunakan aplikasi microsoft excel visual basic for application (VBA), jika ada kesulitan dan pertanyaan seputar microsoft excel silahkan tanyakan pada kolom komentar dibawah, terimakasih.