Cara Menggunakan Rumus untuk Data Validation

Bagaimana cara menggunakan rumus untuk validasi data di Excel? Dengan rumus, kita bisa melakukan kustomisasi data validation. Yuk kita lihat contoh penerapan custom validation di excel. Termasuk diantaranya: contoh rule validasi yang mengizinkan hanya entri angka saja, atau text saja. Selain itu ada juga contoh rule validasi untuk memastikan input text supaya dimulai, diakhiri, atau mengandung beberapa karakter tertentu. Dengan custom validation, kita juga bisa memastikan input entri unik, mencegah entri duplikat, dan lain sebagainya.

Dalam tutorial sebelumnya sudah dibahas mengenai dasar-dasar cara menggunakan fitur data validation di Excel: Apa kegunaannya, bagaimana fitur ini bekerja, dan bagaimana memanfaatkan predefined rules untuk validasi data di excel. Sekarang kita akan melangkah lebih maju dengan menggali aspek-aspek penting custom data validation di excel, sehingga bisa melejitkan pemanfaatan fitur data validation bukan hanya sekedar yang biasa-biasa saja.

Secara garis besar, pembahasan mencakup:

• Cara Membuat Rule Validasi Berdasarkan Formula.
• Data Validation Untuk Entri Angka Saja
• Data Validation Untuk Entri Text Saja
• Mengizinkan Hanya Entri Text Yang Diawali Karakter Tertentu
• Mengizinkan Hanya Entri Text Yang Mengandung Kata Tertentu
• Mengizinkan Hanya Entri Unik dan Mencegah Duplikat
• Formula Validasi untuk Tanggal dan Waktu
• Mengatasi Masalah Rumus Validasi yang Tidak Berfungsi.

Namun sebelum melangkah lebih jauh. Alangkah baiknya bila pembaca sudah memahami terlebih dahulu dasar-dasar penggunaan data validation di excel. Pembahasan mendasar data validation dapat dilihat pada artikel: Cara Menggunakan Data Validation di Excel.

Jika anda sudah siap, mari kita bahas satu persatu topik pembahasan custom validation.

Cara Membuat Custom Validation Berdasarkah Formula

Excel sudah menyediakan sejumlah rule validasi siap pakai (predefined rules) untuk angka, tanggal, dan text. Namun, predefined rule ini hanya bisa menanggulangi skenario yang bersifat basic saja. Jika kita menginginkan validasi sel dengan kriteria yang lebih fleksible tanpa terpatok pada pilihan kriteria yang sudah tersedia, maka kita perlu membuat custom validation berdasarkan sebuah rumus. Berikut ini langkah dasar cara membuat custom validation rule:

1. Seleksi satu atau sejumlah sel  yang akan divalidasi.
2. Buka jendela Data validation dengan cara masuk ke tab Data > klik tombol Data Validation. Anda bisa juga menggunakan shortcut keyboard Alt > D > L , ditekan terpisah namun berurutan. 
3. Dalam jendela Data Validation, pada kotak Allow pilih Custom dan kemudian masukan rumus pada kotak formula.
4. Selanjutnya klik OK.

Selain hal di atas, anda juga bisa menambahkan pesan input dan pesan error. Pesan input akan bekerja pada saat user menyeleksi sel tervalidasi, sedangkan pesan error aka tampil ketika user memasukan data invalid.

Catatan: Semua rule data validation di excel, baik built-in maupun custom, hanya mampu memverifikasi data baru yang diketik pada sel tervalidasi. Sedangkan entri data dengan cara copy paste, tidak divalidasi. Demikian juga data yang sudah ada dalam sel sebelum rule validasi dibuat. Untuk menemukan data entri yang tidak sesuai dengan kriteria pada sel tervalidasi maka gunakan fitur Circle Invalid Data seperti yang sudah dibahas dalam artikel sebelumnya.

Data Validation Untuk Mengizinkan Entri Angka Saja

Sebenarnya excel sudah menyediakan rule validasi siap pakai untuk angka yaitu whole number, decimal, date dan time. Namun rule validasi tersebut mengharuskan adanya nilai minimal dan maksimal atau salah satunya. Jika anda ingin memaksa user untuk memasukan angka tanpa dibatasi nilai minimum atau maksimumnya maka anda dapat menerapkan custom validation berdasarkan formula, menggunakan fungsi ISNUMBER seperti contoh berikut ini:

=ISNUMBER(C2)

Dimana C2 adalah sel teratas pada range yang ingin kita validasi.

Catatan: fungsi ISNUMBER hanya mengizinkan entri data numerik pada sel tervalidasi. Data numerik ini bisa berupa bilangan bulat, desimal, pecahan, termasuk tanggal dan waktu yang juga dikenali sebagai bilangan oleh excel.

Data validation untuk mengizinkan entri text saja.

Sebagai kebalikan dari yang pertama, kita juga bisa membuat rule validasi yang mengizinkan hanya entri berupa text saja, menggunakan fungsi ISTEXT.

Contoh :

=ISTEXT(D2) Dimana D2 adalah sel teratas pada range yang akan divalidasi.

Selain menggunakan fungsi ISTEXT, kita juga bisa memaksai entri text dengan menggunakan fungsi ISNUMBER yang bernilai FALSE, sehingga rumus =ISTEXT(D2) dapat menghasilkan efek yang sama dengan rumus : =ISNUMBER(D2)=FALSE

Validasi supaya entri text dimulai dengan awalan karakter atau kata tertentu.

Jika semua value dalam range tertentu harus dimulai dengan karakter atau frase kata tertentu, maka gunakanlah custom validation berdasarkan fungsi COUNTIF dikombinasikan dengan karakter wildcard. Secara sederhana, syntax rumus dapat dituliskan sebagai berikut:

  =COUNTIF(sel,"text*")

Sebagai contoh, untuk memastikan semua entri kode ID pada kolom A dimulai dengan awalan "XX-", "xx-", "Xx-" atau "xX-"  (tidak memandang jenis huruf kapital maupun huruf kecil) maka kita dapat mendefinisikan sebuah custom rule dengan rumus berikut:

=COUNTIF(A2,"xx-*")

Formula validasi dengan logika OR (Multiple Criteria)

Dalam kasus dimana ada 2 atau lebih awalan yang valid, maka gunakanlah beberapa fungsi COUNTIF, sehingga data validation bekerja dengan logika OR.

Contoh:

=COUNTIF(A2,"xx-*")+COUNTIF(A2,"yy-*")

Formula Validasi bersifat Case Sensitive

Jika perbedaan jenis huruf kapital dan huruf kecil menjadi masalah, maka kita bisa menggunakan fungsi EXACT dikombinasikan dengan fungsi LEFT. Hal ini akan menciptakan rule validasi yang bersifat case sensitive, sehingga entri data pada  sel tervalidasi harus berupa text berawalan karakter tertentu dengan jenis huruf kapital maupun huruf kecil yang ditentukan.

=EXACT(LEFT(sel, jumlah_karakter), text)

Sebagai contoh, untuk mengizinkan kode ID dimulai dengan awalan "XX-" (bukan "xx-" maupun "Xx-"

=EXACT(LEFT(A2,3),"XX-")

Fungsi LEFT digunakan untuk mengekstrak 3 karakter dari text yang ada di sel A2. Selanjutnya fungsi EXACT menjalankan perbadingan case sensitive dengan kode substring yang ditentukan (contoh : "XX-"). validasi akan bernilai benar (TRUE) jika 3 karakter pertama berupa text "XX-" sama persis termasuk jenis  huruf kapital atau kecilnya.

Mengizinkan entri yang mengandung kata tertentu.

Untuk mengizinkan entri supaya mengandung kata tertentu pada bagian mana saja (permulaan, pertengahan ataupun pada akhir text), maka gunakanlah fungsi ISNUMBER dikombinasikan dengan dalah satu fungsi FIND atau SEARCH, tergantung apakah anda ingin membuat validasi text case sensitive atau tidak.

 Validasi tidak case sensitive menggunakan kombinasi fungsi ISNUMBER dan SEARCH.
 =ISNUMBER(SEARCH(text, sel))

 Validasi case sensitive menggunakan kombinasi fungsi ISNUMBER dan FIND.
=ISNUMBER(FIND(text, sel))

Dengan menggunakan tabel contoh sebelumnya, untuk mengizinkan hanya entri tertentu yang mengndung kata “AA” dalam sel A2:A7, maka gunakan rumus berikut:

Case-insensitive: =ISNUMBER(SEARCH("XX", A2))
Case-sensitive: =ISNUMBER(FIND("XX", A2))

Formula tersebut bekerja dengan logika sebagai berikut:

• Fungsi FIND atau SEARCH digunakan untuk mencari substring "XX" pada sel A2. Sedangkan fungsi ISNUMBER digunakan untuk menguji hasil fungsi FIND atau SEARCH apakah berupa angka atau bukan (error).
• Jika substring "XX" ditemukan dalam sel A2 maka fungsi FIND atau SEARCH menghasilkan sebuah bilangan yang merupakan nomor urut posisi substring tersebut dalam text yang ada di sel A2. Sebuah bilangan jika dicek dengan fungsi ISNUMBER akan menghasilkan nilai TRUE, → memenuhi kriteria → entri diizinkan.
• Jika substring "XX" tidak ditemukan dalam sel A2 maka fungsi FIND atau SEARCH menghasilkan nilai error #VALUE!. Nilai error ini jika dicek dengan fungsi ISNUMBER akan menghasilkan nilai FALSE, → tidak memenuhi kriteria → entri tidak diizinkan.

Berikut  screenshot contoh penerapan validasi case sensitive menggunakan kombinasi fungsi ISNUMBER dan FIND:

Dapat kita perhatikan dari gambar di atas: dengan rumus validasi ISNUMBER FIND, maka data yang dimasukan ke sel tervalidasi harus mengandung substring XX berupa huruf kapital semua.

Hal ini dikarenakan fungsi FIND bersifat case sensitive. Selanjutnya silahkan dicoba untuk mengganti fungsi FIND dengan fungsi SEARCH, dan bandingkan perbedaannya.

Data validation untuk mencegah duplikat entri.

Dalam situasi dimana kolom tertentu tidak boleh berisi data duplikat, maka kita bisa mengkonfigurasikan custom data validation berikut untuk hanya mengizinkan input data unik.

  =COUNTIF(range, sel_terAtas)<=1

Sebagai contoh, untuk memastikan bahwa hanya nilai unik yang diinput pada kolom sampai dengan A7, maka gunakan formula berikut sebagai custom rule:

=COUNTIF($A$2:$A$7, A2)<=1

Ketika sebuah value unik dimasukan ke sel tervalidasi, maka formula akan menghasilkan nilai TRUE, dan validasi berhasil. Jika value yang sama sudah ada di sel lainnya dalam range tervalidasi, maka COUNTIF menghasilkan FALSE, dan input tidak memenuhi kriteria validasi.

Harap diperhatikan bahwa range harus dikunci dengan referensi absolute (A$2:$A$7) dan gunakan referensi relative untuk sel paling atas (A2) supaya mendapatkan rumus untuk mengatur secara tepat masing-masig sel dalam range tervalidasi.

Catatan: contoh formula validasi di atas data tidak bersifat case sensitive, artinya tidak membedakan keberadaan huruf kapital maupun huruf kecil.

Formula Validasi untuk tanggal dan waktu.

Fitur data validation di excel sudah menyediakan cukup banyak kriteria siap pakai yang dapat digunakan untuk membatasi entri tanggal, yaitu: antara dua tanggal, sebelum, sesudah, atau sama dengan tanggal ditentukan. Jika anda menginginkan kontrol lebih terhadap data validation di dalam spreadsheet, maka anda dapat melakukan kustomisasi rule dengan mengetikan rumus anda sendiri untuk meningkatan kapabilitas data validation terhadap entri tanggal di excel.

Mengizinkan entri tanggal dalam rentang tertentu (antara 2 tanggal)

Untuk membatasi entri data dengan rentang tanggal tertentu, anda dapat menggunakan salah satu rule siap pakei “between” atau menggunakan custom validation rule dengan rumus umum sebagai berikut: AND(sel>=tanggal_mulai), sel<=tanggal_selesai) dimana:

• sel adalah sel paling kiri atas dalam range yang divalidasi.
• tanggal_mulai dan tanggal_selesai merupakan tanggal valid yang disediakan melalui fungsi DATE atau referensi terhadap sel yang berisi tanggal.

Sebagai contoh, untuk mengizinkan hanya entri tanggal dalam bulan Juli tahun 2018 pada kolom D, maka gunakan formula berikut:

=AND(D2>=DATE(2018,7,1),D2<=DATE(2018,7,31))

Atau,masukan tanggal_mulai dan tanggal_selesai pada beberapa sel (contoh G1 dan G2), kemudian referensikan sel-sel tersebut dalam formula.

=AND(D2>=$G$1, D2<=$G$2)

Harap diperhatikan bahwa batasan tanggal  harus dikunci oleh referensi absolute

Mengizinkan hanya entri tanggal hari kerja dan akhir pekan.

Untuk membatasi user supaya hanya memasukan hari kerja dan/atau akhir pekan, maka lakukan konfigurasi rule validasi menggunakan fungsi WEEKDAY. Dengan argumen return_type di set ke angka 2, maka fungsi WEEKDAY akan menghasilkan nilai integer mulai angka 1 (Senin) sampai dengan 7 (Sabtu), sehingga untuk hari kerja (senin ke jumat) bernilai kurang dari 6 dan weekend (sabtu dan minggu) bernilai lebih dari 5.

Hanya mengizinkan entri tanggal hari kerja:
WEEKDAY(sel,2)<6

Hanya mengizinkan tanggal akhir pekan:
WEEKDAY(sel,2)>5

Sebagai contoh, rumus dibawah ini bisa digunakan untuk mengizinkan entri hanya berupa hari kerja pada sel C2:C7:

=WEEKDAY(C2,2)<6

Validasi data berdasarkan tanggal saat ini.

Dalam beberapa situasi, anda mungkin ingin menjadikan tanggal saat ini sebagai acuan tanggal awal yang dizinkan. Untuk mendapatkan tanggal saat ini, kita bisa menggunakan fugsi TODAY.

Selanjutnya tambahkan sebuah bilangan yang menunjukan jumlah hari setelah tanggal saat ini untuk menghitung tanggal akhir. Misalnya, untuk membatasi entri data maksimal 6 hari dari sekarang (7 hari termasuk hari ini), kita akan menggunakan rule siap pakai dengan kriteria berdasarkan formula:

• Pada kotak allow, pilih Date.
• Selanjutnya pada kotak data, pilih between
• Pada kotak start date, masukan rumus =TODAY()
• Dalam kotak End date, masukan rumus =TODAY()+6

Dengan cara yang serupa, anda dapat membatasi entri tanggal sebelum dan sesudah tanggal saat ini. Untuk itu, pilih salah satu less then atau greater than pada kotak Data, kemudian masukan =TODAY() dalam kotak end date atau start date

Validasi waktu berdasarkan waktu saat ini

Untuk memvalidasi data berdasarkan waktu saat ini, kita dapat memodifikasi rule predefined untuk waktu dengan menggunakan formula.

Pada kotak Allow, pilih Time Dalam kotak Data, anda dapat mengambil salah satu kriteria, apakah less than untuk mengizinkan hanya waktu sebelum saat ini, ataupun greather than untuk mengizinkan waktu setelah saat ini.

Dalam kotak end time atau start time (tergantung kriteria yang anda seleksi pada tahap sebelumnya), masukan salah satu formula berikut:

Untuk memvalidasi tanggal dan waktu berdasarkan tanggal dan waktu sekarang: =NOW()

Untuk memvalidasi waktu berdasarkan waktu sekarang:

=TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

Screenshot dibawah ini memperlihatkan sebuah rule yang mengizinkan input hanya waktu setelah saat ini.

Custom data validation tidak bekerja.

Jika data validaton berdasarkan rumus atau formula tidak bekerja sesuai   harapan, maka ada 3 hal yang perlu dicek:

1. Formula validasi harus benar.
2. Formula Validasi tidak boleh mereferensi ke sel kosong.
3. Harus menggunakan referensi sel yang tepat.

Cara mengecek benar tidaknya sebuah formula data validation.

• Untuk memulai, copy formula validasi kedalam sejumlah sel untuk memastikan bahwa formula tersebut tidak menghasilkan nilai error seperti #N/A, #VALUE or #DIV/0!.
• Jika anda membuat sebuah custom rule, formula harus menghasilkan nilai logika TRUE atau FALSE, atau nilai 1 dan 0.
• Jika anda menggunakan sebuah kriteria berdasakan formula dalam sebuah rule build in (seperti sudah kita buat untuk validasi waktu berdasarkan waktu sekarang), ini juga akan menghasilkan nilai numerik lainnya.

Formula data validation tidak boleh mereferensi ke sel kosong.

Dalam beberapa situasi, jika anda memilih kotak Ignore Blank ketika mendefinisikan rule (biasanya terseleksi secara default), kemudian ada satu atau lebih sel kosong dalam range referensi maka nilai apapun akan diizinkan dalam sel tervalidasi. Berikut screenshot yang yang menggambarkan masalah tersebut:

Dari screenshot diatas, kita dapat mengetahui: dikarenakan sel yang dijadikan referensi nilai maksimal (sel B2) tidak ada konten apapun di dalamnya, alias kosong, maka validasi tidak bekerja dengan benar pada range D2:D4 dimana angka berapapun bisa diinput, tidak lagi dibatasi nilai minimum maupun maksimum.

Penggunaan referensi relative dan absolute dalam rumus validasi.

Ketika anda men-setting sebuah rule validasi berdasarkan formula, pastikan bahwa semua referensi sel dalam formula bersifat relative terhadap sel paling kiri atas dalam range diseleksi.

Penjelasan mengenai perbedaan referensi relative dan absolute dapat disimak pada artikel: Perbedaan alamat sel relatif, absolut dan semi absolut.

Jika anda membuat sebuah rule untuk lebih dari satu sel dan kriteria validasi anda tergantung pada sel-sel tertentu, pastikan gunakan referensi absolute (menggunakan tanda dolar, misalnya $A$2). Jika tidak maka rule validasi tidak akan bekerja sesuai yang diharapkan.

Untuk menggambarkan hal ini, mari kita perhatikan contoh berikut: Anggaplah, anda ingin membatasi data entri di sel D2 s/d D4 dengan bilangan minimum dan maksimum yang mereferensi ke sel A2 (minimum) dan B2 (maksimum).

Kemudian anda membuat rule validasi whole number – between – dan memasukan rumus pada kotak minimum dan maksimum seperti terlihat dalam gambar berikut ini:

Perhatikan gambar di atas. Secara sepintas, rumus pada kotak minimum dan maksimum sepertinya benar. Namun jika anda mencoba memasukan entri data pada sel D2, D3 dan D4, anda akan mendapati hanya di sel D2 validasi bekerja secara benar.

Setelah diteliti, ternyata masalahnya adalah rumus pada kotak minimum dan maksimum bersifat relatif, sehingga rule validasi di sel D2, D3, dan D4 berbeda beda.

 Mari kita cek rule validasi di sel D3 dengan cara tekan seleksi sel D3, kemudian tekan Alt → D → L  secara berurutan untuk masuk ke jendela Data Validation seperti screenshot di bawah ini.

Perhatikan referensi pada kotak minimum dan maksimum. Ternyata referensi berubah secara relatif terhadap referensi awal (A2 menjadi A3 dan B2 menjadi B3). Inilah yang mengakibatkan rule validasi tidak bekerja dengan benar di sel D3, karena sel A3 dan B3 sebagai referensi minimum dan maksimum merupakan sel kosong.

Untuk mengatasi masalah tersebut, cukup dengan mengetikan tanda dolar “$” sebelum referensi kolom dan baris untuk menguncinya :

• kotak minimum   =$A$2
• kotak maksimum =$B$2

Dalam kasus penentuan referensi sebenarnya tidak ada keharusan apakah harus menggunakan absolute atau relative. Ini semua tergantung pada pola data dan tabel kriteria yang tersedia. anda bisa menggunakan relative, absolute atau kombinasi keduanya dalam situasi yang lebih komplek.

Demikian pembahasan mengenai cara menggunakan custom validation di excel. Semoga penjelasannya mudah difahami. Kritik dan saran dari pembaca sangat diharapkan untuk perbaikan kedepannya. Salam.

Silahkan dicek juga catatan pelajaran excel lainnya:

• Dasar-Dasar Cara Menggunakan Data Validation di Excel
• Kumpulan Catatan Pelajaran Excel
• Perbedaan Alamat Sel Relatif, Absolut dan Semi Absolut

Referensi:
https://www.ablebits.com/office-addins-blog/2017/08/17/use-data-validation-excel-custom-rules-formulas/