Database yang baik pada Excel

Database merupakan suatu bagian yang penting pada suatu program, karena dengan database yang baik maka pengguna akan dapat mengolah suatu data menjadi sebuah informasi yang bermanfaat. Tetapi saat ini masih banyak ditemui kesalahan dari pengguna Excel dalam membuat database, mereka mencampur adukan antara inputan, database, dan laporan, akibatnya adalah kesulitan saat akan melakukan suatu perhitungan atau saat akan menyajikan laporan baik dalam bentuk grafik maupun tabel. Padahal database itu bukanlah suatu laporan atau pun inputan, melainkan suatu bagian yang terpisah dari kedua hal tersebut.

Alur dari suatu data untuk menjadi laporan adalah sbb:

DATA ==> Proses Input ==> DATABASE ==> Proses Output ==> LAPORAN

Dengan mengetahui perbedaan antara database dengan inputan/laporan maka diharapkan para pengguna Excel dapat membuat database yang baik dan benar.

Syarat database yang baik dan benar pada Excel:

1. Dimulai dari cell A1
2. Baris 1 adalah header dari kolom atau data
3. Baris 2 ke bawah adalah data
4. Tidak ada merge cell baik di header maupun di data
5. Tidak ada baris & kolom kosong antar data
6. Tipe data pada setiap kolom harus seragam
7. Database harus memiliki 1 buah kolom yang berfungsi sebagai Primary Key, yang akan digunakan untuk menghubungkan sebuah database dengan database yang lain. Primary Key ini harus bersifat unik dan tidak ada yang sama, contoh Primary Key adalah Nomor Induk Kependudukan (NIK), Nomor Induk Pegawai (NIP), Nomor Induk Siswa (NIS) dll.
8. Agar database bersifat dinamis, coba manfaatkan fitur Excel Tabel (versi 2007 ke atas) atau List (versi 2003 ke bawah)
9. Definisikan nama tabel menjadi sebuah range

Dengan memiliki database yang baik dan benar maka akan memudahkan user untuk melakukan perhitungan, membuat laporan, menampilkan grafik, meng-ekspor database dari suatu sistem ke sistem yang lain dsb.

Gambar di bawah ini adalah contoh database sederhana dengan memanfaatkan fitur Excel Table

Semoga dengan mengetahui cara pembuatan database yang baik maka para pengguna Excel yang sebelumnya masih mencampur-adukan antara database dengan format input/laporan dapat segera memperbaikinya.

Filter Data Berdasarkan Kriteria

Untuk melakukan Filter data pada Excel dapat dilakukan dengan berbagai macam cara, salah satu cara yang paling umum digunakan adalah dengan menggunakan fitur Filter atau Advanced Filter yang dapat diakses melalui ribbon Data ==> tab Sort & Filter ==> klik Filter atau Advanced Filter, seperti gambar di bawah ini:

Tetapi bukan cara tersebut yang akan dibahas pada posting kali ini, posting kali ini akan membahas mengenai cara melakukan filter data berdasarkan suatu kriteria tertentu, dalam pembahasan kali ini hanya akan menggunakan 1 buah kriteria tetapi nanti juga akan dijelaskan bagaimana melakukan filter dengan kriteria lebih dari 1 (multiple kriteria).

Data yang akan difilter terdiri dari 3 kolom yaitu: Nomer, Kategori, dan Keterangan seperti gambar di bawah ini:

Tujuan yang ingin dicapai adalah membuat daftar berdasarkan kategori pada kolom B.

Untuk menyelesaikan kasus ini, setidaknya ada 6 cara yang saya ketahui selain dengan memanfaatkan fitur Advanced Filter bawaan dari Excel.

Misal kriteria ada di cell F1 lalu akan dibuat daftar/list pada cell F4 ke bawah berdasarkan kriteria berupa Vowel atau Consonant, maka untuk solusinya harus dibuat sebuah kolom bantu di cell E4 sampai E27 untuk mendapatkan letak baris data yang sesuai dengan kriteria yang telah ditentukan.

Kolom bantu dibuat dengan cara:

1. Melakukan blok mulai dari cell E4 sampai dengan cell E27
2. Pada formula bar diketik formula berikut ini: =Small(If(B2:B27=F1;A2:A27);A2:A27)
3. Tekan 3 tombol berikut ini: CTRL Shift Enter sehingga akan muncul tanda kurung kurawal di awal dan akhir formula

Hasil akhir dari formula ini  adalah sebuah Array Data yang tersusun dari atas ke bawah.
Perhatian, tanda pemisah yang saya gunakan adalah titik koma karena regional setting di komputer saya adalah Indonesia, jika anda menggunakan regional setting English maka ubah setiap titik koma di atas menjadi koma.

Alur logika dari formula di atas adalah:
Dari formula yang paling dalam:
If(kriteria , data_jika_kriteria_bernilai_TRUE , data_jika_kriteria_bernilai_FALSE)
jika formula If di atas dimasukkan ke dalam blok warna maka akan menjadi seperti berikut:
If(B2:B27=F1;A2:A27)
perhatikan bahwa nomor data hanya diambil jika kriteria bernilai TRUE, sedangkan jika bernilai FALSE akan diabaikan.
Jika ingin membuat filter data berdasarkan banyak kriteria, maka lakukan modifikasi pada kriteria, conrohnya adalah sebagai berikut:

If((kriteria_1) * (kriteria_2) * (kriteria_3) * (kriteria_4) * (kriteria_n) , data_jika_kriteria_bernilai_TRUE , data_jika_kriteria_bernilai_FALSE)

Tanda * adalah pengganti fungsi AND yang setara dengan perkalian pada Excel.

Sedangkan formula yang paling luar berfungsi untuk menyusun hasil secara Ascending, jika ingin disusun hasil secara Descending maka ubah fungsi Small dengan fungsi Large.
syntax dari Small adalah:
Small(Array , jumlah_data_terkecil)

Mungkin para pembaca ada yang belum paham dengan Array Formula yang digunakan di atas, Array formula adalah formula yang menghasilkan nilai lebih dari 1, untuk membentuk suatu formula menjadi Array harus dengan cara menekan 3 tombol yaitu CTRL Shift Enter sehingga Array Formula juga sering disebut sebagai CSE Formula. Lalu kenapa harus menggunakan Array Formula? Jawabannya karena dalam kasus ini ingin dibuat sebuah daftar dari suatu kriteria tertentu, dan proses membandingkan kriteria di dalam fungsi IF adalah melakukan perbandingan satu data terhadap banyak data, oleh sebab itu dibutuhkan Array Formula.

Setelah diperoleh nomor urut data yang sesuai berdasarkan kriteria, maka untuk mengambil nilai datanya dapat digunakan cara-cara berikut ini:

1. LookUp, formulanya adalah : LookUp(E4;$A$2:$A$27;$C$2:$C$27)
2. VLookUp, formulanya adalah VLookUp(E4;$A$2:$C$27;3;0)
3. Index, formulanya adalah Index($C$2:$C$27;E4)
4. OffSet, formulanya adalah OffSet($C$1;E4;0)

Untuk Error trapnya pada Excel versi 2007 ke atas bisa menggunakan IfError sedangkan pada versi 2003 ke bawah bisa mengginakan IsErr.

Cara ke-5 adalah dengan memanfaatkan fitur Pivot Table
Cara ke-6 adalah menggunakan VBA (Visual Basic for Application)

Script VBA ditulis dalam sebuah modul VBE adalah dan dipanggil dengan cara menekan sebuah shape (tombol) :


Option Explicit

' -------------------------- '
'  Filter Data dengan VBA    '
'  Code by : Dwint Ruswanto  '
'  25 Desemeber 2012         '
' -------------------------- '

Sub FilterData()                                                  
    Dim HdKrit As Range, Rng As Range, cRg As Range             
    Dim Krit As String                                          
    Dim IdxRow As Long                                          
    
    Krit = Range("f1").Value                                    
    Range(Cells(4, 12), Cells(Rows.Count, 12)).ClearContents    
    Set HdKrit = Range("b1")                                
    Set Rng = Range(HdKrit.Offset(1, 0), HdKrit.End(xlDown))
    For Each cRg In Rng                                   
        If cRg = Krit Then                              
            IdxRow = Cells(Rows.Count, 12).End(xlUp).Row + 1   
            Cells(IdxRow, 12).Value = cRg.Offset(0, 1).Value   
        End If                                            
    Next cRg                                       
End Sub                                            

Pembahasan lebih detail mengenai masing-masing fungsi di atas, Pivot Table, dan VBA insya ALLAH akan saya tuliskan dalam suatu postingan yang lain.

File sampel dapat diunduh pada link berikut ini https://www.box.com/s/xa57ld0e7t0w6c2bn51x

Validasi Data menggunakan List

Excel merupakan sebuah aplikasi yang sangat fleksibel, sehingga banyak orang yang menggunakannya untuk kegiatan sehari-hari, baik untuk menyelesaikan tugas-tugas sekolah/kampus, untuk pekerjaan, atau bahkan hanya untuk sekedar melakukan perhitungan anggaran penerimaan dan belanja ibu-ibu rumah tangga.

Salah satu fitur yang dimiliki oleh Excel adalah membuat validasi atas sebuah proses input yang dilakukan oleh user. Dengan adanya validasi ini maka kesalahan input oleh user dapat dicegah. Salah satu bentuk validasi data pada Excel adalah dengan memanfaatkan List dari sebuah daftar atau tabel.

Pada postingan kali ini akan dibahas cara pembuatan Data Validasi menggunakan List dari sebuah tabel dengan data bersifat dinamis. 

Cell A1 : header data

Cell A2 ke bawah adalah data, jumlahnya dinamis (bisa bertambah atau berkurang)

Cell D1 : lokasi Data Validasi

bentuk tabel data dan input adalah seperti gambar di bawah ini:

Cara pembuatan Data Validasi di cell D1 dengan bentuk data yang dinamis adalah seperti berikut (contoh dibuat menggunakan Excel versi 2010, tetapi cara ini juga kompatibel dengan versi 2007 ke bawah):

Letakan kursor di cell D1

Pada ribbon Data ==> tab Data Tools ==> klik Data Validation:

sehingga muncul kotak seperti di bawah ini:

Pada kotak Allow pilih List, lalu pada kotak Source masukkan formula berikut ini:

=OffSet($A$2;0;0;CountA($A:$A)-1;1)

sehingga hasilnya seperti gambar di bawah ini:

kemudian tekan tombol OK, maka hasil akhirnya adalah seperti gambar di bawah ini:

Perhatikan, tanda pemisah yang saya gunakan adalah titik koma ; karena regional setting yang saya gunakan adalah Indonesia, jika anda menggunakan regional setting English maka ganti semua titik koma menjadi koma.

Alur logika dari formula di atas adalah sebagai berikut:

Formula yang paling luar adalah OffSet, syntax dari fungsi ini adalah:

=OffSet(alamat_range_awal_yang_menjadi_rujukan , nomor_indeks_baris , nomor_indeks_kolom , tinggi_baris , lebar_kolom)

sehingga jika disusun berdasarkan per blok warna di atas akan menjadi seperti ini:

=OffSet($A$2;0;0;CountA($A:$A)-1;1)

• alamat_range_awal yang menjadi rujukan (titik NOL, awal dari pergerakan) adalah cell A2, ini adalah data pertama di dalam tabel
• nomor_indeks_baris adalah 0 (NOL), artinya adalah dari range awal tidak berpindah naik atau turun
• nomor_indeks_kolom adalah 0 (NOL), artinya adalah dari range awal tidak berpindah ke kanan atau ke kiri
• tinggi_baris diperoleh dari fungsi CountA terhadap kolom A, pada contoh ini akan menghasilkan nilai 27 yaitu jumlah cell pada kolom A yang ada datanya, lalu nilai ini dikurangi dengan angka 1 yaitu untuk mengeluarkan header data dari perhitungan sehingga tinggi baris akhir adalah 26 baris yaitu sebanyak data yang ada.
• lebar_kolom adalah 1, yaitu hanya pada kolom A saja.

Semoga postingan ini bermanfaat untuk semuanya.