Apa kegunaan rumus isnumber pada data validation microsoft excel?

Di Microsoft Excel, fungsi ISNUMBER() digunakan untuk menguji atau memeriksa apakah nilai yang disediakan adalah numerik (angka). Jika nilai yang diuji atau diperiksa adalah numerik (angka), maka fungsi ISNUMBER() akan mengembalikan nilai TRUE, sebaliknya akan mengembalikan nilai FALSE. Fungsi ISNUMBER() di Microsoft Excel merupakan salah satu fungsi yang telah terdefinisi (built-in function) dan termasuk ke dalam kategori fungsi-fungsi informasi (information function) Microsoft Excel.

Table of Contents Show

1. Sintaksis fungsi ISNUMBER().
2. Penerapan fungsi ISNUMBER().
3. Hasil fungsi ISNUMBER().
Cara Membuat Custom Validation Berdasarkah Formula
Data Validation Untuk Mengizinkan Entri Angka Saja
Data validation untuk mengizinkan entri text saja.
Validasi supaya entri text dimulai dengan awalan karakter atau kata tertentu.
Formula validasi dengan logika OR (Multiple Criteria)
Formula Validasi bersifat Case Sensitive
Mengizinkan entri yang mengandung kata tertentu.
Data validation untuk mencegah duplikat entri.
Formula Validasi untuk tanggal dan waktu.
Custom data validation tidak bekerja.
Apa kegunaan fungsi Isnumber () pada Microsoft Excel?
Apa Fungsi Data Validation di Excel?
Data Validation Excel ada dimana?
Bagaimana cara melakukan validasi data?

1. Sintaksis fungsi ISNUMBER().

Adapun sintaksis fungsi ISNUMBER() di Microsoft Excel adalah sebagai berikut:

=ISNUMBER(value)

value adalah argumen atau parameter fungsi ISNUMBER() yang diuji atau diperiksa. Argumen tersebut dapat berupa nilai atau acuan (reference) ke suatu nilai. Jika argumen value adalah numerik, maka fungsi ISNUMBER() akan mengembalikan nilai TRUE. Jika argumen value bukan numerik, maka fungsi ISNUMBER() akan mengembalikan nilai FALSE.

2. Penerapan fungsi ISNUMBER().

Berikut ini adalah contoh tabel data dan penerapan fungsi ISNUMBER() dengan berbagai variasi argumen.

3. Hasil fungsi ISNUMBER().

Di gambar berikut ini, Anda dapat melihat hasil penerapan fungsi ISNUMBER() pada tabel data.

Fungsi ISNUMBER() ini berlaku untuk Excel 2000, Excel XP, Excel 2003, Excel 2007, Excel 2010, Excel 2011 for Mac, Excel 2013 dan juga Excel 2016.

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:

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:

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 menggunakan 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 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:

Formula validasi harus benar.
Formula Validasi tidak boleh mereferensi ke sel kosong.
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/

Apa kegunaan fungsi Isnumber () pada Microsoft Excel?

Di Microsoft Excel, fungsi ISNUMBER() digunakan untuk menguji atau memeriksa apakah nilai yang disediakan adalah numerik (angka).

Apa Fungsi Data Validation di Excel?

Validasi data sangat berguna jika Anda ingin berbagi buku kerja dengan orang lain dan ingin agar data yang dimasukkan konsisten dan akurat. Selain itu, Anda dapat menggunakan validasi data untuk hal-hal berikut: Membatasi entri ke item yang sudah ditentukan sebelumnya dalam daftar.