Cara Menggunakan Rumus Data Validation Excel

Kemampuan dasar data validation sudah cukup untuk memenuhi validasi sederhana.

Penggunaan rumus bisa memperbesar kemampuan validasi data di luar apa yang sudah disediakan Excel.

Artikel ini membahas bagaimana cara menggunakan rumus data validation Excel untuk melengkapi fitur bawaan.

Kami menggunakan data yang file-nya bisa teman-teman unduh di link berikut.

File: rumus-data-validation.xlsx.

Apakah Rumus Data Validation Excel Itu?

Data validation adalah fitur untuk melakukan validasi entri di worksheet.

Rumus data validation Excel menggunakan rumus-rumus Excel untuk memperluas kemampuan data validation.

Penggunaan rumus data validation bisa dilakukan pada tab Setting kotak dialog bersangkutan.

Setelahnya, pilih Custom di dropdown Allow, lalu isikan rumus yang diinginkan pada Formula, dan klik OK.

Rumus Apa yang Bisa Digunakan Pada Data Validation?

Rumus yang sesuai untuk digunakan di data validation adalah rumus Boolean.

Rumus Boolean mengembalikan hanya salah satu dari dua nilai, yaitu TRUE (Benar) atau FALSE (Salah).

Excel akan menganggap suatu entri sebagai error dan menampilkan pesan kesalahan bila rumus menghasilkan nilai FALSE, yaitu tidak memenuhi kriteria validasi.

Cara Menggunakan Rumus Data Validation untuk Validasi Teks

Demo berikut ini bertujuan untuk menyaring agar nama karyawan hanya menerima entri dalam bentuk teks. Entri tipe data lain akan ditolak.

(Demo 01) Berikut adalah cara menggunakan rumus data validation Excel untuk entri tipe data teks.

1. Pilih cell atau range yang akan divalidasi, yaitu B2:B6.
2. Pastikan pilihan Custom untuk Allow.
3. Pada Formula, isikan =ISTEXT(B2).
4. Klik OK.

Bila kita terapkan Circled Invalid Data pada range bersangkutan, Excel akan melingkari cell B4 dan B6 sebagai bukti validasi berjalan.

Cara Menggunakan Rumus Data Validation untuk Validasi Angka

Demo berikut bertujuan agar kolom gaji karyawan hanya menerima entri dalam bentuk angka dan tidak tipe data lainnya.

(Demo 02) Berikut adalah cara menggunakan rumus data validation Excel untuk entri angka.

1. Pilih cell atau range yang akan divalidasi, yaitu E2:E6.
2. Pastikan pilihan Custom untuk Allow.
3. Pada Formula, isikan =ISNUMBER(E2).
4. Klik OK.

Bila teman-teman jalankan Circled Invalid Data pada range E2:E6, E4 dan E6 akan dilingkari. Hal ini menunjukkan validasi pada range bersangkutan bekerja.

Cara Menggunakan Rumus Data Validation untuk Entri Sesuai Rentang Angka

Demo berikut bertujuan untuk menyaring agar usia karyawan berada pada rentang nilai yang sudah ditentukan, yaitu 18 sampai 65 tahun.

Untuk itu, kami menggunakan fungsi logika AND.

(Demo 03) Berikut cara menggunakan rumus data validation Excel dengan fungsi logika AND.

1. Pilih cell atau range yang akan divalidasi, yaitu C2:C6.
2. Pastikan pilihan Custom untuk Allow.
3. Pada Formula, isikan =AND(C2>=$J$2; C2<=$K$2).
4. Klik OK.

Pada Gambar 06, Excel menampilkan pesan kesalahan saat kami memasukkan nilai 77, yang berada di luar rentang 18 – 65.

Referensi Relatif dan Absolut di Rumus Data Validation Excel

Pada Demo 01 dan Demo 02, kami memasukkan E2 dan C2 sebagai referensi cell di kotak dialog Data Validation, walau pun cell-cell yang ingin divalidasi terdiri dari E2:E6 dan C2:C6.

Dalam penggunaan rumus data validation, kita memang harus memasukkan cell terkiri atas sebagai objek validasi.

Penggunaan E2 dan C2 adalah contoh referensi relatif.

Referensi relatif adalah referensi yang relatif terhadap lokasi cell. Saat menggunakan referensi relatif, Excel akan menyesuaikan referensi tersebut di setiap cell di range E2:E6 dan C2:C6.

Sebagai pembuktian, teman-teman bisa pilih cell C3 dan buka kotak dialog Data Validation. Perhatikan bahwa pada kotak dialog tersebut, cell referensi adalah C3 dan bukan C2. Lihat Gambar 07.

Pada Demo 03, rumus memiliki referensi absolut (perhatikan tanda $ untuk kolom dan baris) ke cell J2 dan K2.

Referensi absolut adalah referensi yang tidak berubah atau tetap sama di lokasi cell mana pun. Pada Demo 03, masing-masing cell pada range C2:C6 akan selalu mengacu pada J2 dan K2.

Pada Gambar 07, cell C3 tetap mengacu ke K2.

Lebih lanjut mengenai referensi relatif dan absolut, baca artikel di bawah ini.

Baca: Referensi Cell Excel.

Data Validation Excel dengan Rumus COUNTIF

Demo di bagian ini bertujuan untuk membatasi agar setiap cell di kolom Jenis Kelamin hanya punya salah satu dari dua nilai, yaitu P (Pria) atau W (Wanita).

Untuk tujuan tersebut, kami menggunakan fungsi COUNTIF.

(Demo 04) Berikut cara data validation Excel dengan rumus COUNTIF untuk membatasi entri pada nilai-nilai tertentu.

1. Pilih cell atau range yang akan divalidasi, yaitu D2:D6.
2. Pastikan pilihan Custom untuk Allow.
3. Pada Formula, isikan =COUNTIF($P$2:$P$3; D2)=1.
4. Klik OK.

Cara Menggunakan Data Validation Excel dengan Rumus IF

Demo kali ini bertujuan untuk membatasi entri biaya dandan (make up) hanya untuk karyawan wanita. Biaya tersebut berkisar antara Rp 0 sampai 10% gaji.

Untuk tujuan itu, kita akan gunakan data validation Excel dengan rumus IF.

Sebelum lanjut, kita lihat dulu rumus satu ini:

=IF(D2="W"; TRUE; FALSE)

Telah kami sampaikan di atas bahwa Excel akan menerima entri bila rumus bernilai TRUE dan menolak bila FALSE.

Rumus di atas berarti entri akan diterima hanya bila D2 bernilai W.

Berangkat dari situ, kita modifikasi rumus tersebut untuk keperluan Demo 05.

(Demo 05) Berikut cara menggunakan data validation Excel dengan rumus IF.

1. Pilih cell atau range yang akan divalidasi, yaitu F2:F6.
2. Pastikan pilihan Custom untuk Allow.
3. Pada Formula, isikan =IF(D2="W"; AND(F2>=0; F2<=10%*E2); FALSE).
4. Klik OK.

Perhatian: Ekstra hati-hati dalam menggunakan fungsi IF.

Fungsi IF tidak selalu mengembalikan nilai Boolean. Rumus =IF(D2="P"; "Pria"; "Wanita") mengembalikan nilai teks.

Bila kita tetap masukkan rumus tersebut sebagai validasi data, maka semua entri akan dianggap salah.

Data Validation Excel Untuk Entri Unik, Tidak Duplikat

Kali ini kita akan entri kode karyawan dengan kriteria:

• Dua karakter terawal di kode karyawan harus didahului oleh prefiks AB, BC, atau CD. Setiap prefiks ini bisa dilihat di rentang M1:N4.
• Kode karyawan harus unik satu sama lain, tidak boleh duplikat.

Pada demo-demo sebelumnya, kami menyediakan langkah-langkah pengaturan di kotak dialog Data Validation. Pada demo kali ini, kami ajak teman-teman menyusun rumus yang dibutuhkan.

(Demo 06)

1. Untuk mendapatkan dua karakter terawal di kode, gunakan fungsi LEFT. Kode karyawan berada di range A2:A6. Dengan demikian =LEFT(A2; 2).
2. Untuk memeriksa apakah dua karakter tersedia di master data M1:N4, gunakan COUNTIF. Dengan gitu, rumusnya adalah =COUNTIF($M$2:$M$4; LEFT(A2; 2))=1.
3. Untuk melihat apakah kode karyawan unik atau tidak, kembali kita gunakan COUNTIF, yaitu =COUNTIF($A$2:$A$6; A2)=1.
4. Berhubung kita akan mengevaluasi nilai pada Langkah 2, maka kita gunakan fungsi IF.
5. Bila evaluasi Langkah 4 benar (prefiks tersedia di master data), kita jalankan Langkah 4.
6. Bila evaluasi Langkah 4 salah (prefiks tidak tersedia), kembalikan nilai FALSE.

Secara keseluruhan, rumus adalah sebagai berikut:

=IF(COUNTIF($M$2:$M$4; LEFT(A2; 2))=1; COUNTIF($A$2:$A$6; A2)=1; FALSE)

Data Validation Excel dengan Rumus VLOOKUP

Demo kali ini bertujuan untuk memberikan bonus pada setiap karyawan. Besaran bonus terkecil adalah Rp 0. Besaran bonus terbesar yang bisa diberikan dihitung dari persentase gaji karyawan. Persentase ini bisa dilihat dari dua kode awal kode karyawan.

Untuk tujuan itu, kita akan menggunakan fungsi LEFT untuk melihat dua karakter awal kode karyawan, VLOOKUP untuk melihat besaran persentase, dan AND untuk menampilkan rentang bonus.

(Demo 07) Berikut cara menggunakan data validation Excel dengan rumus VLOOKUP.

1. Pilih cell atau range yang akan divalidasi, yaitu G2:G6.
2. Pastikan pilihan Custom untuk Allow.
3. Pada Formula, isikan =AND(G2>=0; G2<=VLOOKUP(LEFT(A2; 2); $M$2:$N$4; 2)*E2).
4. Klik OK.

Mengapa Data Validation Excel Tidak Berfungsi?

Ada beberapa sebab mengapa data validation Excel tidak berfungsi.

Periksa Tanda Sama Dengan Sebelum Rumus Ditulis

Kedengarannya sederhana, tapi kami sudah lupa berapa kali lupa masukkan tanda sama dengan (=). Excel akan menerima rumus dengan atau tanpa tanda sama dengan.

Rumus seperti Gambar 11 akan diterima. Tapi entri apa pun akan dianggap sebagai tidak valid.

Referensi Relatif dan Absolut di Rumus Data Validation

Salah menggunakan referensi relatif dan absolut adalah salah satu sebab paling sering mengapa data validation Excel tidak berfungsi.

Kami sudah membahas perbedaan penggunaan referensi relative dan absolut.

Masalah satu ini kedengarannya sulit (memang gak gampang), tapi tidak sehoror yang kita bayangkan.

Sering-seringlah latihan (dan membuat kesalahan).

Referensi di Data Validation Excel Mengacu ke Cell Kosong

Pada Gambar 12, rumus mengacu pada cell kosong. Excel menganggap cell kosong sebagai cell bernilai nol.

Dengan rumus di atas, berapa pun angka yang kita masukkan, sebagai contoh 180, Excel bakal menampilkan pesan kesalahan. Karena, 180 memang lebih besar dari 160, tapi tidak lebih kecil dari 0.

Bila teman-teman jawab hanya angka imajinasi yang bisa, salah itu. Bilangan imajiner pun tidak.

Rumus tidak mengembalikan nilai TRUE or FALSE

Perhatikan rumus berikut:

=IF("P"; "Pria"; "Wanita")

Rumus di atas akan diterima oleh Excel. Tapi, rumus itu gak bakal kerja sebagaimana mestinya karena tipe data yang dikembalikan adalah teks.

Dalam kondisi seperti itu, Excel akan selalu menganggap hasilnya adalah FALSE. Akibatnya, semua nilai yang kita masukkan akan ditolak.

Periksa Ulang Rumus yang Dimasukkan

Bila semua cara di atas sudah dilakukan tapi data validation Excel tidak berfungsi juga, tidak ada salahnya periksa ulang rumus.

Pecah rumus menjadi bagian-bagian kecil dan tes di cell dengan data yang sama. Pastikan setiap bagian tidak mengembalikan nilai-nilai #N/A, #VALUE, atau #DIV/0!.

Sebisa mungkin jangan sok pede memeriksa rumus hanya dengan membaca tanpa tes sama sekali (kami sering karena malas).

Pada Gambar 13, kami memecah rumus Demo 07 ke rumus-rumus pembentuknya.

Perhatikan bagaimana alur rumus lebih gampang dibaca ketimbang =AND(G2>=0; G2<=VLOOKUP(LEFT(A2; 2); $M$2:$N$4; 2)*E2).

Penutup

Demikian bahasan artikel kami kali ini mengenai cara menggunakan rumus data validation Excel.

Data validation adalah fitur melakukan validasi entri di worksheet. Kemampuan data validation bisa ditingkatkan lagi dengan menggunakan rumus.

Kami juga telah membahas beberapa contoh menggunakan rumus data validation dan mengapa data validation Excel tidak berfungsi.

Bila teman-teman ada pertanyaan, masukan, atau request tutorial, silakan layangkan pada kolom komentar.

Sampai jumpa lagi di artikel berikutnya.