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.