Excel : Menandai Data Sesuai Kriteria

Excel bukan sekedar Aplikasi yang biasa. Pernahkah anda mencoba untuk menandai data dengan excel ? Mungkin sudah sering. Dari contoh kasus dibawah ini, kita akan coba menandai data nilai yang tidak lulus dan yang lulus sesuai dengan kriteria yang akan kita tentukan.

Dari data berikut ini, kita akan menandakan data yang bernilai dibawah 75 diberi tanda warna “Merah” dan yang diatas 75 diberi warna “Abu-abu“. Atau anda juga dapat menentukan sendiri kriterianya sesuai dengan bentuk soal atau penerapan dilapangan. Lets go kita coba..

Validasi Data Di Microsoft Excel

Jika anda adalah pengguna Microsoft Excel, tentu salah satu tugas kita sebagai pembuat project-nya tentu kita akan membuat project tersebut berisi dengan data-data yang akurat. Tetapi sayangnya secara default, Excel tidak menjaga keakuratan data anda tersebut. Dan hal itu akan berpengaruh pada hasil analisa laporan anda karena banyak data input yang tidak sesuai yang anda harapkan.

Tetapi jangan kuatir, Excel juga telah menyediakan cara untuk anda. Fitur tersebut bernama Data Validation. Untuk penggunaannya sebagai berikut:

1. Setelah Microsoft Excel anda terbuka, ketikan daftar isian yang anda inginkan.
   
2. Arahkan ke cell dimana anda ingin isian dilakukan.
3. Klik menu Data -> Data Validation.
4. Di tab Settings, beri combo Allow: nilai List,  klik isian Source lalu blok data yang sudah anda buat di langkah 1.
   
5. Klik tab Error Alert. Pastikan radio check Show error after invalid data is entered dalam keadaan tercentang. Untuk Style pilih Stop lalu tulis Title kotak peringatan dan Error Message: dengan isi kesalahan. Lalu klik tombol OK.
   
6. Nah, sekarang jika kita akan memasukan nilai di cell tersebut, akan muncul tombol anak panah kebawah yang jika di klik akan muncul nilai-nilai yang sudah di tentukan.
   
7. Dan jika anda memaksa mengisi dengan nilai di luar data tersebut, kotak peringatan yang telah anda konfigurasi tersebut akan muncul.
   

Dengan demikian data yang dimasukan bisa valid dan sesuai dengan yang anda harapkan.

src:edisusanto_dot_com

membuat fungsi pembilang

Sebetulnya pembuatan fungsi terbilang ini tidaklah susah jika kita mau menggunakan logika kita sedikit, Berikut langkah yang harus anda ambil:

1. Buka Microsoft Excel.
2. Tekan Alt + F11 untuk membuka VBA Editor, atau klik menu Tools -> Macro -> Visual Basic Editor.
   
3. Di Visual Basic Editor, klik menu Insert -> Module.
   
4. Lalu copy-paste script ini ke bidang kerjanya:

   Public Function TERBILANG(x As Double) As String
   Dim tampung As Double
   Dim teks As String
   Dim bagian As String
   Dim i As Integer
   Dim tanda As Boolean
   
   Dim letak(5)
   letak(1) = "RIBU "
   letak(2) = "JUTA "
   letak(3) = "MILYAR "
   letak(4) = "TRILYUN "
   
   If (x < 0) Then
       TERBILANG = ""
   Exit Function
   End If
   
   If (x = 0) Then
       TERBILANG = "NOL"
   Exit Function
   End If
   
   If (x < 2000) Then
       tanda = True
   End If
   teks = ""
   
   If (x >= 1E+15) Then
       TERBILANG = "NILAI TERLALU BESAR"
   Exit Function
   End If
   
   For i = 4 To 1 Step -1
       tampung = Int(x / (10 ^ (3 * i)))
       If (tampung > 0) Then
           bagian = ratusan(tampung, tanda)
           teks = teks & bagian & letak(i)
       End If
       x = x - tampung * (10 ^ (3 * i))
   Next
   
   teks = teks & ratusan(x, False)
   TERBILANG = teks & " Rupiah"
   End Function
   
   Function ratusan(ByVal y As Double, ByVal flag As Boolean) As String
   Dim tmp As Double
   Dim bilang As String
   Dim bag As String
   Dim j As Integer
   
   Dim angka(9)
   angka(1) = "SE"
   angka(2) = "DUA "
   angka(3) = "TIGA "
   angka(4) = "EMPAT "
   angka(5) = "LIMA "
   angka(6) = "ENAM "
   angka(7) = "TUJUH "
   angka(8) = "DELAPAN "
   angka(9) = "SEMBILAN "
   
   Dim posisi(2)
   posisi(1) = "PULUH "
   posisi(2) = "RATUS "
   
   bilang = ""
   For j = 2 To 1 Step -1
       tmp = Int(y / (10 ^ j))
       If (tmp > 0) Then
           bag = angka(tmp)
           If (j = 1 And tmp = 1) Then
               y = y - tmp * 10 ^ j
               If (y >= 1) Then
                   posisi(j) = "BELAS "
               Else
                   angka(y) = "SE"
               End If
               bilang = bilang & angka(y) & posisi(j)
               ratusan = bilang
               Exit Function
           Else
               bilang = bilang & bag & posisi(j)
       End If
   End If
   y = y - tmp * 10 ^ j
   Next
   
   If (flag = False) Then
       angka(1) = "SATU "
   End If
   bilang = bilang & angka(y)
   ratusan = bilang
   End Function

   

5. Setelah di paste, coba cek script ini dengan klik menu Debug -> Compile VBA Project, jika tidak keluar kotak peringatan apapun biasanya script anda sudah beres.
6. Tutup jendela Visual Basic Editor anda.

Nah sekarang tinggal coba aja:

1. Ketik di cell sembarang sederet angka (misal di cell A1), lalu untuk memanggil fungsi terbilang ini (misal di cell A2), tinggal ketik =TERBILANG(A1).
   

Just in case, jika macro ini tidak bisa di jalankan di Excel anda (karena secara default Excel menset keamanan terhadap macro ke level tinggi), cek Security macro setting di Excel anda. Untuk melihat konfigurasi Security macro, klik Tools -> Macro -> Security dan pilih opsi Low lalu klik tombol OK. Saran saya pastikan anda sudah mempunya anti virus terinstal di komputer anda untuk menjaga agar virus macro tidak dapat menyerang komputer anda.

src:edisusanto_dot_com

Mengamankan rumus ms Excel

Hal yang paling menjengkelkan kalau kita sudah susah-susah membuat table-table Excel kita adalah di hancurkan orang lain. Bisa saja rumus yang sudah kita buat susah-susah di hapus secara sengaja atau tidak atau juga rumus tersebut di curi (jika rumus itu adalah hasil pemikiran kita berhari-hari tentu tidak menyenangkan ada orang lain mengambil dengan seenaknya tanpa seijin kita).

Pembulatan 50-an pada MS Excel

Fungsi Round, Rondup, rounddown pada MS Excel adalah melakukan pembulatan hasil operasi matematika, di mana pembulatannya merupakan kelipatan 1 (satuan), 10 (puluhan), 100 (ratusan), 1000 (ribuan), 10000 (sepuluh ribuan) dst,.. Sebagai contoh, sekarang Anda buka MS Excel, kemudian ketikkan pada masing-masing cell :

A1 : =ROUND((100000/3);0)
A2 : =ROUND((100000/3);-1)
A3 : =ROUND((100000/3);-2)
A4 : =ROUND((100000/3);-3)
A5 : =ROUND((100000/3);-4)
Kemudian
B1 : =ROUNDUP((100000/3);0)
B2 : =ROUNDUP((100000/3);-1)
B3 : =ROUNDUP((100000/3);-2)
B4 : =ROUNDUP((100000/3);-3)
B5 : =ROUNDUP((100000/3);-4)
Kemudian
C1 : =ROUNDDOWN((100000/3);0)
C2 : =ROUNDDOWN((100000/3);-1)
C3 : =ROUNDDOWN((100000/3);-2)
C4 : =ROUNDDOWN((100000/3);-3)
C5 : =ROUNDDOWN((100000/3);-4)
hasilnya adalah seperti gambar di bawah ini.

Hasil pembulatan oleh ketiga fungsi tersebut adalah 33.333 atau 33.334 (kelipatan satuan), 33.330 atau 33.340 (kelipatan puluhan), 33.300 atau 33.400 (kelipatan ratusan), 33.000 (kelipatan ribuan), dan 30.000 (sepuluh ribuan).

Bagaimana seandainya, agar hasil pembulatannya menjadi 33.335 (kelipatan limaan), 33.350 (kelipatan lima puluhan), 33.500 (kelipatan lima ratusan), 35.000 (kelipatan lima ribuan), atau 50.000 (kelipatan lima puluh ribuan) dst,..

Untuk itu diperlukan sedikit manipulasi (ngakali, bahasa Jawa) agar diperoleh pembulatan sesuai dengan yang kita kehendaki.

Nah, pada tips kali ini, saya akan memberi contoh pembulatan ke atas 50-an (kelipatan lima puluhan). Untuk kasus-kasus yang lain seperti pembulatan ke bawah 50-an, pembulatan ke atas/ke bawah 500-an, pembulatan ke atas/ke bawah 5000-an, dapat Anda coba sendiri.

Dengan mengetikkan angka presentase tertentu dari angka tertentu, kita bisa langsung melihat hasilnya dengan pembulatan ke atas 50-an.