Belajar Microsoft Excel: Cara Menggunakan 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 mengijinkan cuma entri angka saja , atau text saja. Selain itu ada juga contoh rule validasi untuk menegaskan input text mudah-mudahan dimulai , diakhiri , atau mengandung beberapa karakter tertentu. Dengan custom validation , kita juga bisa menegaskan input entri unik , menghambat entri duplikat , dan lain sebagainya.

Dalam bimbingan sebelumnya sudah dibahas perihal dasar-dasar cara memakai fitur data validation di Excel: Apa manfaatnya , bagaimana fitur ini melakukan pekerjaan , dan bagaimana mempergunakan 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 cuma sekedar yang biasa-biasa saja.

Secara garis besar , pembahasan mencakup:

Namun sebelum melangkah lebih jauh. Alangkah baiknya apabila pembaca sudah mengetahui apalagi dulu 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 diskusikan satu persatu topik pembahasan custom validation.

Cara Membuat Custom Validation Berdasarkah Formula

Excel sudah menawarkan sejumlah rule validasi siap pakai (predefined rules) untuk angka , tanggal , dan text. Namun , predefined rule ini cuma bisa menanggulangi skenario yang bersifat basic saja. Jika kita mengharapkan validasi sel dengan standar yang lebih fleksible tanpa terpatok pada opsi standar yang sudah tersedia , maka kita perlu bikin custom validation menurut suatu rumus. Berikut ini langkah dasar cara bikin custom validation rule:

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

    Custom data validation di Excel

    Selain hal di atas , anda juga bisa menyertakan pesan input dan pesan error. Pesan input akan melakukan pekerjaan pada ketika user memilih sel tervalidasi , sedangkan pesan error aka tampil ketika user memasukan data invalid.

    Catatan: Semua rule data validation di excel , baik built-in maupun custom , cuma 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 mendapatkan data entri yang tidak cocok dengan standar pada sel tervalidasi maka gunakan fitur Circle Invalid Data seumpama yang sudah dibahas dalam postingan sebelumnya.

    Data Validation Untuk Mengizinkan Entri Angka Saja

    Sebenarnya excel sudah menawarkan rule validasi siap pakai untuk angka yakni whole number , decimal , date dan time. Namun rule validasi tersebut mewajibkan adanya nilai minimal dan optimal 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 seumpama contoh berikut ini:

    =ISNUMBER(C2)

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

    Baca Juga  Belajar Microsoft Excel: Mengkalkulasikan Jumlah Sel Dengan Standar Warna

    Formula Data Validation Mengizinkan Angka Saja

    Catatan: fungsi ISNUMBER cuma mengijinkan entri data numerik pada sel tervalidasi. Data numerik ini bisa berupa bilangan lingkaran , desimal , pecahan , tergolong tanggal dan waktu yang juga diketahui selaku bilangan oleh excel.

    Data validation untuk mengijinkan entri text saja.

    Sebagai kebalikan dari yang pertama , kita juga bisa bikin rule validasi yang mengijinkan cuma entri berupa text saja , memakai fungsi ISTEXT.

    Contoh :

    =ISTEXT(D2) Dimana D2 yakni sel teratas pada range yang hendak divalidasi.

    Formula Data Validation Mengizinkan Entri Text Saja

    Selain memakai fungsi ISTEXT , kita juga bisa memaksai entri text dengan memakai fungsi ISNUMBER yang bernilai FALSE , sehingga rumus =ISTEXT(D2) dapat menciptakan pengaruh yang serupa dengan rumus : =ISNUMBER(D2)=FALSE

    Validasi mudah-mudahan entri text dimulai dengan awalan karakter atau kata tertentu.

    Jika semua value dalam range tertentu mesti dimulai dengan karakter atau frase kata tertentu , maka gunakanlah custom validation menurut fungsi COUNTIF dikombinasikan dengan karakter wildcard. Secara sederhana , syntax rumus sanggup dituliskan selaku berikut:

      =COUNTIF(sel ,”text*”)

    Sebagai contoh , untuk menegaskan semua entri instruksi ID pada kolom A dimulai dengan awalan “XX-” , “xx-” , “Xx-” atau “xX-“  (tidak menatap jenis aksara kapital maupun aksara kecil) maka kita sanggup mendefinisikan suatu custom rule dengan rumus berikut:

    =COUNTIF(A2 ,”xx-*”)


    Formuls Data Validation Entri Awalan Tertentu

    Formula validasi dengan kebijaksanaan OR (Multiple Criteria)

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

    Contoh:

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

    Formula Data Validation , Entri Multi Kriteria

    Formula Validasi bersifat Case Sensitive

    Jika perbedaan jenis aksara kapital dan aksara kecil menjadi kasus , maka kita bisa memakai fungsi EXACT dikombinasikan dengan fungsi LEFT. Hal ini akan bikin rule validasi yang bersifat case sensitive , sehingga entri data pada  sel tervalidasi mesti berupa text berawalan karakter tertentu dengan jenis aksara kapital maupun aksara kecil yang ditentukan.

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

    Sebagai contoh , untuk mengijinkan instruksi 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 mengerjakan perbadingan case sensitive dengan instruksi substring yang diputuskan (contoh : “XX-“). validasi akan bernilai benar (TRUE) kalau 3 karakter pertama berupa text “XX-” sama persis tergolong jenis  aksara kapital atau kecilnya.

    Formula Data Validation Supaya Case Sensitive


    Mengizinkan entri yang mengandung kata tertentu.

    Untuk mengijinkan entri mudah-mudahan mengandung kata tertentu pada pecahan mana saja (permulaan , pertengahan ataupun pada simpulan text) , maka gunakanlah fungsi ISNUMBER dikombinasikan dengan dalah satu fungsi FIND atau SEARCH , tergantung apakah anda ingin bikin validasi text case sensitive atau tidak.

     Validasi tidak case sensitive memakai variasi fungsi ISNUMBER dan SEARCH.
     =ISNUMBER(SEARCH(text , sel))

     Validasi case sensitive memakai variasi fungsi ISNUMBER dan FIND.
    =ISNUMBER(FIND(text , sel))

    Dengan memakai tabel contoh sebelumnya , untuk mengijinkan cuma 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 melakukan pekerjaan dengan kebijaksanaan selaku 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” didapatkan dalam sel A2 maka fungsi FIND atau SEARCH menciptakan suatu bilangan yang ialah nomor urut posisi substring tersebut dalam text yang ada di sel A2. Sebuah bilangan kalau dicek dengan fungsi ISNUMBER akan menciptakan nilai TRUE , → menyanggupi standar → entri diizinkan.
    • Jika substring “XX” tidak didapatkan dalam sel A2 maka fungsi FIND atau SEARCH menghasilkan nilai error #VALUE!. Nilai error ini kalau dicek dengan fungsi ISNUMBER akan menciptakan nilai FALSE , → tidak menyanggupi standar → entri tidak diizinkan.

    Berikut  screenshot contoh penerapan validasi case sensitive memakai variasi fungsi ISNUMBER dan FIND:

    Formula Data Validation Text Ada String Tertentu

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

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

    Data validation untuk menghambat duplikat entri.

    Dalam suasana dimana kolom tertentu dilarang berisi data duplikat , maka kita bisa mengkonfigurasikan custom data validation berikut untuk cuma mengijinkan input data unik.

    Baca Juga  Cara ke 2 memasukkan data massal dengan PHP dan MySQL

      =COUNTIF(range , sel_terAtas)<=1

    Sebagai contoh , untuk menegaskan bahwa cuma nilai unik yang diinput pada kolom hingga dengan A7 , maka gunakan formula berikut selaku custom rule:

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

    Ketika suatu value unik dimasukan ke sel tervalidasi , maka formula akan menciptakan nilai TRUE , dan validasi berhasil. Jika value yang serupa sudah ada di sel yang lain dalam range tervalidasi , maka COUNTIF menciptakan FALSE , dan input tidak menyanggupi standar validasi.

    Harap diamati bahwa range mesti dikunci dengan rujukan absolute (A$2:$A$7) dan gunakan rujukan relative untuk sel paling atas (A2) mudah-mudahan mendapat rumus untuk menertibkan secara sempurna masing-masig sel dalam range tervalidasi.

    Formula Data Validation Mencegah Duplikat


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

    Formula Validasi untuk tanggal dan waktu.

    Fitur data validation di excel sudah menawarkan lumayan banyak standar siap pakai yang sanggup digunakan untuk menghambat entri tanggal , yaitu: antara dua tanggal , sebelum , sehabis , atau sama dengan tanggal ditentukan. Jika anda mengharapkan kendali lebih kepada data validation di dalam spreadsheet , maka anda sanggup melaksanakan kustomisasi rule dengan mengetikan rumus anda sendiri untuk meningkatan kapabilitas data validation kepada entri tanggal di excel.

    Mengizinkan entri tanggal dalam rentang tertentu (antara 2 tanggal)

    Untuk menghambat entri data dengan rentang tanggal tertentu , anda sanggup memakai salah satu rule siap pakei “between” atau memakai custom validation rule dengan rumus lazim selaku 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 ditawarkan lewat fungsi DATE atau rujukan kepada sel yang berisi tanggal.

    Sebagai contoh , untuk mengijinkan cuma 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)

    Formula data validation rentang tanggal

    Harap diamati bahwa batas-batas tanggal  mesti dikunci oleh rujukan absolute

    Mengizinkan cuma entri tanggal hari kerja dan simpulan pekan.

    Untuk menghambat user mudah-mudahan cuma memasukan hari kerja dan/atau simpulan pekan , maka kerjakan konfigurasi rule validasi memakai fungsi WEEKDAY. Dengan argumen return_type di set ke angka 2 , maka fungsi WEEKDAY akan menciptakan 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 mengijinkan entri tanggal hari kerja:
    WEEKDAY(sel ,2)<6

    Hanya mengijinkan tanggal simpulan pekan:
    WEEKDAY(sel ,2)>5

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

    =WEEKDAY(C2 ,2)<6

    Formula data validation entri tanggal hari kerja

    Validasi data menurut tanggal ketika ini.

    Dalam beberapa suasana , anda mungkin ingin membuat tanggal ketika ini selaku pola tanggal permulaan yang dizinkan. Untuk mendapat tanggal ketika ini , kita bisa memakai fugsi TODAY.

    Selanjutnya tambahkan suatu bilangan yang mengambarkan jumlah hari sesudah tanggal ketika ini untuk mengkalkulasikan tanggal akhir. Misalnya , untuk menghambat entri data optimal 6 hari dari kini (7 hari tergolong hari ini) , kita akan memakai rule siap pakai dengan standar 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

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

    Formula data validation tanggal hari ini ke depan

    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 standar , apakah less than untuk mengijinkan cuma waktu sebelum ketika ini , ataupun greather than untuk mengijinkan waktu sesudah ketika ini.

    Dalam kotak end time atau start time (tergantung standar 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 menampilkan suatu rule yang mengijinkan input cuma waktu sesudah ketika ini.

    Formula data validation waktu kini ke depan

    Custom data validation tidak bekerja.

    Jika data validaton menurut rumus atau formula tidak melakukan pekerjaan sesuai   harapan , maka ada 3 hal yang perlu dicek:

    1. Formula validasi mesti benar.
    2. Formula Validasi dilarang mereferensi ke sel kosong.
    3. Harus memakai rujukan sel yang tepat.
    Baca Juga  Membuat custom filter pada datatable

    Cara menganalisa benar tidaknya suatu formula data validation.

    • Untuk mengawali , copy formula validasi kedalam sejumlah sel untuk menegaskan bahwa formula tersebut tidak menciptakan nilai error seumpama #N/A , #VALUE or #DIV/0!.
    • Jika anda bikin suatu custom rule , formula mesti menciptakan nilai kebijaksanaan TRUE atau FALSE , atau nilai 1 dan 0.
    • Jika anda memakai suatu standar berdasakan formula dalam suatu rule build in (seperti sudah kita buat untuk validasi waktu menurut waktu sekarang) , ini juga akan menciptakan nilai numerik lainnya.

      Formula data validation dilarang mereferensi ke sel kosong.

      Dalam beberapa suasana , kalau anda menegaskan kotak Ignore Blank ketika mendefinisikan rule (biasanya terseleksi secara default) , kemudian ada satu atau lebih sel kosong dalam range rujukan maka nilai apapun akan diizinkan dalam sel tervalidasi. Berikut screenshot yang yang menggambarkan kasus tersebut:

      Contoh penyebab formula data validation tidak berfungsi

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

      Penggunaan rujukan relative dan absolute dalam rumus validasi.

      Ketika anda men-setting suatu rule validasi menurut formula , tetapkan bahwa semua rujukan sel dalam formula bersifat relative kepada sel paling kiri atas dalam range diseleksi.

      Penjelasan perihal perbedaan rujukan relative dan absolute sanggup disimak pada artikel: Perbedaan alamat sel relatif , adikara dan semi absolut.

      Jika anda bikin suatu rule untuk lebih dari satu sel dan standar validasi anda tergantung pada sel-sel tertentu , tetapkan gunakan rujukan absolute (menggunakan tanda dolar , misalnya $A$2). Jika tidak maka rule validasi tidak akan melakukan pekerjaan sesuai yang diharapkan.

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

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

      Formula data validation rujukan tidak tepat

      Perhatikan gambar di atas. Secara sepintas , rumus pada kotak minimum dan maksimum kelihatannya benar. Namun kalau anda menjajal memasukan entri data pada sel D2 , D3 dan D4 , anda akan mendapati cuma di sel D2 validasi melakukan pekerjaan secara benar.

      Setelah diteliti , ternyata masalahnya yakni rumus pada kotak minimum dan maksimum bersifat relatif , sehingga rule validasi di sel D2 , D3 , dan D4 berlawanan 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 seumpama screenshot di bawah ini.

      Formula data validation rujukan relative

      Perhatikan rujukan pada kotak minimum dan maksimum. Ternyata rujukan berubah secara relatif kepada rujukan permulaan (A2 menjadi A3 dan B2 menjadi B3). Inilah yang membuat rule validasi tidak melakukan pekerjaan dengan benar di sel D3 , alasannya yakni sel A3 dan B3 selaku rujukan minimum dan maksimum ialah sel kosong.

      Untuk menangani kasus tersebut , cukup dengan mengetikan tanda dolar “$” sebelum rujukan kolom dan baris untuk menguncinya :

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

      Dalam kasus penentuan rujukan serentak tidak ada kewajiban haruskah memakai absolute atau relative. Ini semua tergantung pada pola data dan tabel standar yang tersedia. anda dapat memakai relative , absolute atau variasi keduanya dalam suasana yang lebih komplek.

      Demikian pembahasan perihal cara memakai custom validation di excel. Semoga penjelasannya gampang difahami. Kritik dan nasehat dari pembaca sungguh diperlukan untuk perbaikan kedepannya. Salam.

      Silahkan dicek juga catatan pelajaran excel lainnya:

      Need Help? Chat with us