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.

excel-001

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

Baca lebih lanjut

Iklan

Validasi Data Di Microsoft Excel

combo_exJika 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.
    Tulis data
  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.
    Settings
  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.
    Error alert
  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.
    hasil
  7. Dan jika anda memaksa mengisi dengan nilai di luar data tersebut, kotak peringatan yang telah anda konfigurasi tersebut akan muncul.
    Kotak peringatan

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

src:edisusanto_dot_com

membuat fungsi pembilang

terbilang

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.
    menu Visual Basic Editor
  3. Di Visual Basic Editor, klik menu Insert -> Module.
    Insert module menu
  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

    shot vbe

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

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.
set macro security level

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

Baca lebih lanjut

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.

Photo Sharing and Video Hosting at Photobucket
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.

Baca lebih lanjut