Cara Memakai Rumus Untuk Data Validation
Bagaimana cara memakai rumus untuk validasi data di Excel? Dengan rumus, kita bisa melaksanakan 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 agar 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 memakai 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:
Namun sebelum melangkah lebih jauh. Alangkah baiknya bila pembaca sudah memahami terlebih dahulu dasar-dasar penggunaan data validation di excel. Pembahasan fundamental data validation sanggup dilihat pada artikel: Cara Menggunakan Data Validation di Excel.
Jika anda sudah siap, mari kita bahas satu persatu topik pembahasan custom validation.
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 menurut sebuah rumus. Berikut ini langkah dasar cara membuat custom validation rule:
Selain hal di atas, anda juga bisa menambahkan pesan input dan pesan error. Pesan input akan bekerja pada ketika 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 bisa memverifikasi data gres 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 menyerupai yang sudah dibahas dalam artikel sebelumnya.
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 sanggup menerapkan custom validation menurut formula, memakai fungsi ISNUMBER menyerupai contoh berikut ini:
=ISNUMBER(C2)
Dimana C2 yakni 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.
Contoh :
=ISTEXT(D2) Dimana D2 yakni sel teratas pada range yang akan divalidasi.
Selain memakai fungsi ISTEXT, kita juga bisa memaksai entri text dengan memakai fungsi ISNUMBER yang bernilai FALSE, sehingga rumus =ISTEXT(D2) dapat menghasilkan efek yang sama dengan rumus : =ISNUMBER(D2)=FALSE
Jika semua value dalam range tertentu harus dimulai dengan karakter atau frase kata tertentu, maka gunakanlah custom validation menurut fungsi COUNTIF dikombinasikan dengan karakter wildcard. Secara sederhana, syntax rumus sanggup dituliskan sebagai berikut:
=COUNTIF(sel,"text*")
Sebagai contoh, untuk memastikan semua entri isyarat ID pada kolom A dimulai dengan awalan "XX-", "xx-", "Xx-" atau "xX-" (tidak memandang jenis abjad kapital maupun abjad kecil) maka kita sanggup mendefinisikan sebuah custom rule dengan rumus berikut:
=COUNTIF(A2,"xx-*")
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-*")
Jika perbedaan jenis abjad kapital dan abjad kecil menjadi masalah, maka kita bisa memakai fungsi EXACT dikombinasikan dengan fungsi LEFT. Hal ini akan membuat rule validasi yang bersifat case sensitive, sehingga entri data pada sel tervalidasi harus berupa text berawalan karakter tertentu dengan jenis abjad kapital maupun abjad kecil yang ditentukan.
=EXACT(LEFT(sel, jumlah_karakter), text)
Sebagai contoh, untuk mengizinkan isyarat ID dimulai dengan awalan "XX-" (bukan "xx-" maupun "Xx-"
=EXACT(LEFT(A2,3),"XX-")
Fungsi LEFT dipakai untuk mengekstrak 3 karakter dari text yang ada di sel A2. Selanjutnya fungsi EXACT menjalankan perbadingan case sensitive dengan isyarat substring yang ditentukan (contoh : "XX-"). validasi akan bernilai benar (TRUE) jikalau 3 karakter pertama berupa text "XX-" sama persis termasuk jenis abjad kapital atau kecilnya.
Untuk mengizinkan entri agar mengandung kata tertentu pada bab mana saja (permulaan, pertengahan ataupun pada tamat 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 memakai kombinasi fungsi ISNUMBER dan SEARCH.
=ISNUMBER(SEARCH(text, sel))
Validasi case sensitive memakai kombinasi fungsi ISNUMBER dan FIND.
=ISNUMBER(FIND(text, sel))
Dengan memakai 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:
Dapat kita perhatikan dari gambar di atas: dengan rumus validasi ISNUMBER FIND, maka data yang dimasukan ke sel tervalidasi harus mengandung substring XX berupa abjad kapital semua.
Hal ini dikarenakan fungsi FIND bersifat case sensitive. Selanjutnya silahkan dicoba untuk mengganti fungsi FIND dengan fungsi SEARCH, dan bandingkan perbedaannya.
Dalam situasi dimana kolom tertentu dihentikan 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 hingga 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 acuan absolute (A$2:$A$7) dan gunakan acuan relative untuk sel paling atas (A2) agar mendapat rumus untuk mengatur secara sempurna masing-masig sel dalam range tervalidasi.
Catatan: contoh formula validasi di atas data tidak bersifat case sensitive, artinya tidak membedakan keberadaan abjad kapital maupun abjad kecil.
Fitur data validation di excel sudah menyediakan cukup banyak kriteria siap pakai yang sanggup dipakai 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 sanggup melaksanakan kustomisasi rule dengan mengetikan rumus anda sendiri untuk meningkatan kapabilitas data validation terhadap entri tanggal di excel.
Untuk membatasi entri data dengan rentang tanggal tertentu, anda sanggup memakai salah satu rule siap pakei “between” atau memakai custom validation rule dengan rumus umum sebagai berikut: AND(sel>=tanggal_mulai), sel<=tanggal_selesai) dimana:
=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 acuan absolute
Untuk membatasi user agar hanya memasukan hari kerja dan/atau tamat pekan, maka lakukan konfigurasi rule validasi memakai fungsi WEEKDAY. Dengan argumen return_type di set ke angka 2, maka fungsi WEEKDAY akan menghasilkan nilai integer mulai angka 1 (Senin) hingga 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 tamat pekan:
WEEKDAY(sel,2)>5
Sebagai contoh, rumus dibawah ini bisa dipakai untuk mengizinkan entri hanya berupa hari kerja pada sel C2:C7:
=WEEKDAY(C2,2)<6
Dalam beberapa situasi, anda mungkin ingin menjadikan tanggal ketika ini sebagai contoh tanggal awal yang dizinkan. Untuk mendapat tanggal ketika ini, kita bisa memakai fugsi TODAY.
Selanjutnya tambahkan sebuah bilangan yang menerangkan jumlah hari setelah tanggal ketika ini untuk menghitung tanggal akhir. Misalnya, untuk membatasi entri data maksimal 6 hari dari kini (7 hari termasuk hari ini), kita akan memakai rule siap pakai dengan kriteria menurut formula:
Untuk memvalidasi data menurut waktu ketika ini, kita sanggup memodifikasi rule predefined untuk waktu dengan memakai formula.
Pada kotak Allow, pilih Time Dalam kotak Data, anda sanggup mengambil salah satu kriteria, apakah less than untuk mengizinkan hanya waktu sebelum ketika ini, ataupun greather than untuk mengizinkan waktu setelah ketika 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 menurut tanggal dan waktu sekarang: =NOW()
Untuk memvalidasi waktu menurut waktu sekarang:
=TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))
Screenshot dibawah ini mengatakan sebuah rule yang mengizinkan input hanya waktu setelah ketika ini.
Jika data validaton menurut rumus atau formula tidak bekerja sesuai harapan, maka ada 3 hal yang perlu dicek:
Dalam beberapa situasi, jikalau anda menentukan kotak Ignore Blank ketika mendefinisikan rule (biasanya terseleksi secara default), kemudian ada satu atau lebih sel kosong dalam range acuan maka nilai apapun akan diizinkan dalam sel tervalidasi. Berikut screenshot yang yang menggambarkan duduk kasus tersebut:
Dari screenshot diatas, kita sanggup mengetahui: dikarenakan sel yang dijadikan acuan 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.
Ketika anda men-setting sebuah rule validasi menurut formula, pastikan bahwa semua acuan sel dalam formula bersifat relative terhadap sel paling kiri atas dalam range diseleksi.
Penjelasan mengenai perbedaan acuan relative dan absolute sanggup disimak pada artikel: Perbedaan alamat sel relatif, otoriter dan semi absolut.
Jika anda membuat sebuah rule untuk lebih dari satu sel dan kriteria validasi anda tergantung pada sel-sel tertentu, pastikan gunakan acuan absolute (menggunakan tanda dolar, contohnya $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 menyerupai terlihat dalam gambar berikut ini:
Perhatikan gambar di atas. Secara sepintas, rumus pada kotak minimum dan maksimum tampaknya benar. Namun jikalau 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 yakni 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 menyerupai screenshot di bawah ini.
Perhatikan acuan pada kotak minimum dan maksimum. Ternyata acuan berubah secara relatif terhadap acuan awal (A2 menjadi A3 dan B2 menjadi B3). Inilah yang menjadikan rule validasi tidak bekerja dengan benar di sel D3, alasannya yakni sel A3 dan B3 sebagai acuan minimum dan maksimum merupakan sel kosong.
Untuk mengatasi duduk kasus tersebut, cukup dengan mengetikan tanda dolar “$” sebelum acuan kolom dan baris untuk menguncinya :
Demikian pembahasan mengenai cara memakai custom validation di excel. Semoga penjelasannya gampang difahami. Kritik dan saran dari pembaca sangat dibutuhkan untuk perbaikan kedepannya. Salam.
Silahkan dicek juga catatan pelajaran excel lainnya:
Dalam tutorial sebelumnya sudah dibahas mengenai dasar-dasar cara memakai 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 fundamental data validation sanggup 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 menurut sebuah rumus. Berikut ini langkah dasar cara membuat custom validation rule:
- Seleksi satu atau sejumlah sel yang akan divalidasi.
- Buka jendela Data validation dengan cara masuk ke tab Data > klik tombol Data Validation. Anda bisa juga memakai shortcut keyboard Alt > D > L , ditekan terpisah namun berurutan.
- Dalam jendela Data Validation, pada kotak Allow pilih Custom dan kemudian masukan rumus pada kotak formula.
- Selanjutnya klik OK.
Selain hal di atas, anda juga bisa menambahkan pesan input dan pesan error. Pesan input akan bekerja pada ketika 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 bisa memverifikasi data gres 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 menyerupai 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 sanggup menerapkan custom validation menurut formula, memakai fungsi ISNUMBER menyerupai contoh berikut ini:
=ISNUMBER(C2)
Dimana C2 yakni sel teratas pada range yang ingin kita validasi.
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, memakai fungsi ISTEXT.Contoh :
=ISTEXT(D2) Dimana D2 yakni sel teratas pada range yang akan divalidasi.
Validasi agar 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 menurut fungsi COUNTIF dikombinasikan dengan karakter wildcard. Secara sederhana, syntax rumus sanggup dituliskan sebagai berikut:
=COUNTIF(sel,"text*")
Sebagai contoh, untuk memastikan semua entri isyarat ID pada kolom A dimulai dengan awalan "XX-", "xx-", "Xx-" atau "xX-" (tidak memandang jenis abjad kapital maupun abjad kecil) maka kita sanggup 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
=EXACT(LEFT(sel, jumlah_karakter), text)
Sebagai contoh, untuk mengizinkan isyarat ID dimulai dengan awalan "XX-" (bukan "xx-" maupun "Xx-"
=EXACT(LEFT(A2,3),"XX-")
Fungsi LEFT dipakai untuk mengekstrak 3 karakter dari text yang ada di sel A2. Selanjutnya fungsi EXACT menjalankan perbadingan case sensitive dengan isyarat substring yang ditentukan (contoh : "XX-"). validasi akan bernilai benar (TRUE) jikalau 3 karakter pertama berupa text "XX-" sama persis termasuk jenis abjad kapital atau kecilnya.
Mengizinkan entri yang mengandung kata tertentu.
Untuk mengizinkan entri agar mengandung kata tertentu pada bab mana saja (permulaan, pertengahan ataupun pada tamat 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 memakai kombinasi fungsi ISNUMBER dan SEARCH.
=ISNUMBER(SEARCH(text, sel))
Validasi case sensitive memakai kombinasi fungsi ISNUMBER dan FIND.
=ISNUMBER(FIND(text, sel))
Dengan memakai 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 dipakai untuk mencari substring "XX" pada sel A2. Sedangkan fungsi ISNUMBER dipakai 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 jikalau 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 jikalau dicek dengan fungsi ISNUMBER akan menghasilkan nilai FALSE, → tidak memenuhi kriteria → entri tidak diizinkan.
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 dihentikan 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 hingga 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 acuan absolute (A$2:$A$7) dan gunakan acuan relative untuk sel paling atas (A2) agar mendapat rumus untuk mengatur secara sempurna masing-masig sel dalam range tervalidasi.
Catatan: contoh formula validasi di atas data tidak bersifat case sensitive, artinya tidak membedakan keberadaan abjad kapital maupun abjad kecil.
Formula Validasi untuk tanggal dan waktu.
Fitur data validation di excel sudah menyediakan cukup banyak kriteria siap pakai yang sanggup dipakai 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 sanggup melaksanakan 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 sanggup memakai salah satu rule siap pakei “between” atau memakai 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 acuan terhadap sel yang berisi tanggal.
=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)
Mengizinkan hanya entri tanggal hari kerja dan tamat pekan.
Untuk membatasi user agar hanya memasukan hari kerja dan/atau tamat pekan, maka lakukan konfigurasi rule validasi memakai fungsi WEEKDAY. Dengan argumen return_type di set ke angka 2, maka fungsi WEEKDAY akan menghasilkan nilai integer mulai angka 1 (Senin) hingga 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 tamat pekan:
WEEKDAY(sel,2)>5
Sebagai contoh, rumus dibawah ini bisa dipakai untuk mengizinkan entri hanya berupa hari kerja pada sel C2:C7:
=WEEKDAY(C2,2)<6
Validasi data menurut tanggal ketika ini.
Dalam beberapa situasi, anda mungkin ingin menjadikan tanggal ketika ini sebagai contoh tanggal awal yang dizinkan. Untuk mendapat tanggal ketika ini, kita bisa memakai fugsi TODAY.
Selanjutnya tambahkan sebuah bilangan yang menerangkan jumlah hari setelah tanggal ketika ini untuk menghitung tanggal akhir. Misalnya, untuk membatasi entri data maksimal 6 hari dari kini (7 hari termasuk hari ini), kita akan memakai rule siap pakai dengan kriteria menurut 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
Validasi waktu menurut waktu ketika ini
Untuk memvalidasi data menurut waktu ketika ini, kita sanggup memodifikasi rule predefined untuk waktu dengan memakai formula.
Pada kotak Allow, pilih Time Dalam kotak Data, anda sanggup mengambil salah satu kriteria, apakah less than untuk mengizinkan hanya waktu sebelum ketika ini, ataupun greather than untuk mengizinkan waktu setelah ketika 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 menurut tanggal dan waktu sekarang: =NOW()
Untuk memvalidasi waktu menurut waktu sekarang:
=TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))
Screenshot dibawah ini mengatakan sebuah rule yang mengizinkan input hanya waktu setelah ketika ini.
Custom data validation tidak bekerja.
Jika data validaton menurut rumus atau formula tidak bekerja sesuai harapan, maka ada 3 hal yang perlu dicek:
- Formula validasi harus benar.
- Formula Validasi dihentikan mereferensi ke sel kosong.
- Harus memakai acuan 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 menyerupai #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 memakai sebuah kriteria berdasakan formula dalam sebuah rule build in (seperti sudah kita buat untuk validasi waktu menurut waktu sekarang), ini juga akan menghasilkan nilai numerik lainnya.
Formula data validation dihentikan mereferensi ke sel kosong.
Dalam beberapa situasi, jikalau anda menentukan kotak Ignore Blank ketika mendefinisikan rule (biasanya terseleksi secara default), kemudian ada satu atau lebih sel kosong dalam range acuan maka nilai apapun akan diizinkan dalam sel tervalidasi. Berikut screenshot yang yang menggambarkan duduk kasus tersebut:
Dari screenshot diatas, kita sanggup mengetahui: dikarenakan sel yang dijadikan acuan 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 acuan relative dan absolute dalam rumus validasi.
Ketika anda men-setting sebuah rule validasi menurut formula, pastikan bahwa semua acuan sel dalam formula bersifat relative terhadap sel paling kiri atas dalam range diseleksi.
Penjelasan mengenai perbedaan acuan relative dan absolute sanggup disimak pada artikel: Perbedaan alamat sel relatif, otoriter dan semi absolut.
Jika anda membuat sebuah rule untuk lebih dari satu sel dan kriteria validasi anda tergantung pada sel-sel tertentu, pastikan gunakan acuan absolute (menggunakan tanda dolar, contohnya $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 menyerupai terlihat dalam gambar berikut ini:
Perhatikan gambar di atas. Secara sepintas, rumus pada kotak minimum dan maksimum tampaknya benar. Namun jikalau 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 yakni 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 menyerupai screenshot di bawah ini.
Perhatikan acuan pada kotak minimum dan maksimum. Ternyata acuan berubah secara relatif terhadap acuan awal (A2 menjadi A3 dan B2 menjadi B3). Inilah yang menjadikan rule validasi tidak bekerja dengan benar di sel D3, alasannya yakni sel A3 dan B3 sebagai acuan minimum dan maksimum merupakan sel kosong.
Untuk mengatasi duduk kasus tersebut, cukup dengan mengetikan tanda dolar “$” sebelum acuan kolom dan baris untuk menguncinya :
- kotak minimum =$A$2
- kotak maksimum =$B$2
Demikian pembahasan mengenai cara memakai custom validation di excel. Semoga penjelasannya gampang difahami. Kritik dan saran dari pembaca sangat dibutuhkan untuk perbaikan kedepannya. Salam.
Silahkan dicek juga catatan pelajaran excel lainnya:
Post a Comment for "Cara Memakai Rumus Untuk Data Validation"