Langkah langkah validasi data di excel?

Microsoft Office Excel merupakan aplikasi pengolah data yang memilili banyak sekali fitur. Salah satu fitur yang sering dimanfaatkan oleh pengguna Excel dalam menyajikan laporan adalah validasi data. Setiap data yang disajikan harus relevan dan sesuai logika. Untuk menghidari input data yang keliru, data yang dimasukkan ke dalam sel-sel Excel (Excel cells) perlu divalidasi agar sesuai dengan ketentuan.

Ketika data yang dimasukkan tidak sesuai ketentuan, Microsoft Office Excel akan memberi tahu Anda bahwa data yang dimasukkan melanggar batasan yang telah ditentukan . Validasi data dianggap sangat penting bagi mereka yang keseharianya mengandalkan Excel untuk bekerja dengan data. Validadi data dapat diterapkan untuk semua tipe data yang diterima oleh Excel.

Baca artikel Membuat Daftar Tarik Turun (Drop-Down List) Di Excel

Anda dapat menerapkan aturan-aturan tertentu sesuai kebutuhan untuk memberlakukan validasi data. Berikut ini adalah contoh validasi data sederhana dalam membuat laporan penjualan suatu barang. Jumlah barang yang terjual adalah nilai sah lebih besar atau sama dengan 0. Apabila Anda tanpa sengaja memasukkan nilai lebih kecil dari 0 (misalnya -3), Excel akan menolak nilai tersebut dengan menampilakan peringatan.

Buat atau buka lembar kerja Excel berisi data yang akan diberi validasi data.

Sorot atau blok sel-sel Excel (a range of cells) yang akan diterapkan validasi data.

Pastikan Anda berada di tab Data (1). Di kelompok Data Tools, klik tombol Data Validation (2) dan kemudian pilih Data Validation... (3).

Di kotak dialog Data Validation yang muncul, pastikan Anda berada di tab Settings (1), buat pengaturan seperti nomor (2), (3) dan (4) dan kemudian klik tombol OK (5).

Masih di kotak dialog Data Validation, selanjutnya klik tab Error Alert (1), beri judul untuk kotak dialog Error Alert yang muncul, misalnya "Validasi Data" (2), ketikkan pesan yang ingin ditampilkan di kotak dialog Error Alert (3) dan kemudian klik tombol OK (4).

Untuk mencoba validata data yang diterapkan, di sel C7, beri masukan nilai negatif, misalnya -5 dan kemudian tekan tombol keyboard Enter.

Pesan bahwa nilai yang dimasukkan tidak valid akan muncul dengan setting yang telah Anda tentukan sebelumnya. Tekan tombol Retry untuk merubah nilai dengan nilai valid.

Anda dapat mencoba memasukkan nilai valid, misalnya 5 dan kemudian tekan tombol keyboard Enter. Nilai akan diterima dan pesan tidak akan muncul.

Di excel, Validasi Data adalah fitur berguna yang dapat Anda gunakan untuk membatasi apa yang dapat dimasukkan pengguna ke dalam sel. Misalnya, fitur validasi data dapat membantu Anda membatasi panjang string teks, atau teks yang diawali/diakhiri dengan karakter tertentu, atau nilai unik yang akan dimasukkan dan sebagainya.

Tutorial ini, kita akan berbicara tentang cara menambahkan, menggunakan, dan menghapus validasi data di Excel, beberapa operasi dasar dan lanjutan dari fitur ini juga akan didemonstrasikan secara rinci.

Daftar Isi:

1. Apa itu validasi data di Excel?

2. Bagaimana cara menambahkan validasi data di Excel?

3. Contoh dasar untuk validasi data

• 3.1 Validasi data untuk bilangan bulat dan desimal
• 3.2 Validasi data untuk tanggal dan waktu
• 3.3 Validasi data untuk panjang teks
• 3.4 Daftar validasi data (daftar drop-down)

4. Aturan khusus lanjutan untuk validasi data

• 4.1 Validasi data hanya mengizinkan angka atau teks
  • Izinkan hanya angka yang dimasukkan dengan fungsi Validasi Data
  • Izinkan hanya string teks yang dimasukkan dengan fungsi Validasi Data
  • 4.2 Validasi data hanya mengizinkan nilai alfanumerik
    • Izinkan hanya nilai alfanumerik dengan fungsi Validasi Data
    • Izinkan hanya nilai alfanumerik dengan fitur luar biasa
  • 4.3 Validasi data memungkinkan teks dimulai atau diakhiri dengan karakter tertentu
    • Izinkan teks dimulai atau diakhiri dengan karakter tertentu hanya dengan satu syarat
    • Izinkan teks dimulai atau diakhiri dengan karakter tertentu dengan beberapa kriteria (logika ATAU)
  • 4.4 Validasi data memungkinkan entri harus berisi / tidak boleh berisi teks tertentu
    • Izinkan entri harus berisi satu atau satu dari banyak teks tertentu
    • Izinkan entri tidak boleh berisi satu atau satu dari banyak teks tertentu
  • 4.5 Validasi data hanya mengizinkan nilai unik
    • Izinkan hanya nilai unik dengan fungsi Validasi Data
    • Izinkan hanya nilai unik dengan kode VBA
    • Izinkan hanya nilai unik dengan fitur praktis
  • 4.6 Validasi data hanya mengizinkan huruf besar / huruf kecil / huruf besar saja
  • 4.7 Validasi data memungkinkan nilai yang ada / tidak ada di daftar lain
  • 4.8 Validasi data memaksa hanya format nomor telepon yang dimasukkan
    • Paksa format nomor telepon saja dengan fungsi Validasi Data
    • Paksa format nomor telepon saja dengan fitur yang berguna
  • 4.9 Validasi data hanya memaksa alamat email yang akan dimasukkan
    • Paksa format alamat Email saja dengan fungsi Validasi Data
    • Paksa format alamat Email saja dengan fitur praktis
  • 4.10 Validasi data memaksa hanya alamat IP yang dimasukkan
    • Paksa format alamat IP saja dengan fungsi Validasi Data
    • Paksa format alamat IP saja dengan kode VBA
    • Paksa format alamat IP saja dengan fitur mudah
  • 4.11 Validasi data membatasi nilai yang melebihi nilai total
  • 4.12 Validasi data membatasi entri sel berdasarkan sel lain
  • 4.13 Validasi data hanya mengizinkan hari kerja atau akhir pekan untuk dimasukkan
  • 4.14 Validasi data memungkinkan tanggal yang dimasukkan berdasarkan tanggal hari ini
  • 4.15 Validasi data memungkinkan waktu yang dimasukkan berdasarkan waktu saat ini
  • 4.16 Validasi data tanggal tahun tertentu atau tahun berjalan
  • 4.17 Validasi data tanggal dalam minggu atau bulan ini
    • Izinkan untuk memasukkan tanggal minggu ini
    • Izinkan untuk memasukkan tanggal bulan ini

5. Bagaimana cara mengedit validasi data di Excel?

6. Bagaimana menemukan dan memilih sel dengan validasi data di Excel?

7. Bagaimana cara menyalin aturan validasi data ke sel lain?

8. Bagaimana cara menggunakan validasi data untuk melingkari entri yang tidak valid di Excel?

9. Bagaimana cara menghapus validasi data di Excel?

• Hapus validasi data dalam rentang yang dipilih dengan fungsi validasi data
• Hapus validasi data dalam rentang yang dipilih dengan fitur praktis
• Hapus validasi data dari semua lembar kerja dengan kode VBA

1. Apa itu validasi data di Excel?

The Validasi Data fitur dapat membantu Anda membatasi konten input di lembar kerja Anda. Biasanya, Anda dapat membuat beberapa aturan validasi untuk mencegah atau mengizinkan hanya beberapa jenis data yang dimasukkan dalam daftar sel yang dipilih.

Beberapa penggunaan dasar fitur Validasi Data:

1. Nilai apa pun: tidak ada validasi yang dilakukan, Anda dapat memasukkan apa pun ke dalam sel yang ditentukan. 2. Nilai keseluruhan: hanya bilangan bulat yang diperbolehkan. 3. Desimal: memungkinkan bilangan bulat serta desimal dimasukkan. 4. Daftar: hanya nilai dari daftar yang telah ditentukan yang diizinkan untuk dimasukkan atau dipilih. Nilai ditampilkan dalam daftar dropdown. 5. Tanggal: hanya tanggal yang diperbolehkan. 6. Waktu: hanya waktu yang diperbolehkan. 7. Panjang teks: hanya mengizinkan panjang teks yang ditentukan untuk dimasukkan. 8. Kustom: membuat aturan formula khusus untuk memvalidasi input pengguna.

2. Bagaimana cara menambahkan validasi data di Excel?

Di lembar kerja Excel, Anda dapat menambahkan validasi data dengan langkah-langkah berikut:

1. Pilih daftar sel tempat Anda ingin mengatur validasi data, lalu klik Data > Validasi Data > Validasi Data, lihat tangkapan layar:

2. di Validasi Data kotak dialog, di bawah Settings tab, silakan buat aturan validasi Anda sendiri. di kotak kriteria, Anda dapat menyediakan salah satu dari jenis berikut:

• Nilai - Nilai: Ketik angka di kotak kriteria secara langsung;
• Referensi sel: Referensi sel di lembar kerja atau lembar kerja lain;
• Rumus: Buat formula yang lebih kompleks sebagai kondisi.

Sebagai contoh, saya akan membuat aturan yang mengizinkan hanya bilangan bulat antara 100 dan 1000 yang dimasukkan, di sini atur kriteria seperti gambar di bawah ini:

3. Setelah mengonfigurasi kondisi, Anda dapat pergi ke masukan Pesan or Peringatan Kesalahan tab untuk mengatur pesan input atau peringatan kesalahan untuk sel validasi sesuka Anda. (Jika Anda tidak ingin mengatur peringatan, silakan klik OK untuk menyelesaikan secara langsung.)

3.1) Tambahkan pesan masukan (opsional):

Anda dapat membuat pesan yang muncul saat memilih sel yang berisi validasi data. Pesan ini membantu mengingatkan pengguna apa yang dapat mereka masukkan ke dalam sel.

Pergi ke masukan Pesan tab dan lakukan hal berikut:

Periksalah Tampilkan pesan masukan saat sel dipilih pilihan; Masukkan judul dan pesan pengingat yang Anda inginkan ke dalam bidang yang sesuai; Klik OK untuk menutup kotak dialog ini.

Sekarang, ketika Anda memilih sel yang divalidasi, kotak pesan akan ditampilkan sebagai berikut:

3.2) Buat pesan kesalahan yang berarti (opsional):

Selain membuat pesan input, Anda juga dapat menampilkan peringatan kesalahan saat data yang tidak valid dimasukkan ke dalam sel dengan validasi data.

Pergi ke Peringatan Kesalahan tab dari Validasi Data kotak dialog, lakukan seperti ini:

Periksalah Tampilkan peringatan kesalahan setelah data yang tidak valid dimasukkan pilihan; Dalam majalah Gaya daftar drop-down, pilih satu jenis peringatan yang Anda butuhkan: Berhenti (bawaan): Jenis peringatan ini mencegah pengguna memasukkan data yang tidak valid. peringatan: Memperingatkan pengguna bahwa data tidak valid, tetapi tidak mencegah memasukkannya. Informasi: Menginformasikan pengguna tentang entri data yang tidak valid saja. Masukkan judul dan pesan peringatan yang Anda inginkan ke dalam bidang yang sesuai; Klik OK untuk menutup kotak dialog

Dan sekarang, ketika memasukkan nilai yang tidak valid, kotak peringatan pesan akan muncul seperti gambar di bawah ini:

berhenti pilihan: Anda dapat mengklik Mencoba kembali untuk mengetik nilai lain atau Cancel untuk menghapus entri.

peringatan pilihan: Klik Yes untuk memasukkan entri yang tidak valid, Tidak untuk mengubahnya, atau Cancel untuk menghapus entri.

Informasi pilihan: Klik OK untuk memasukkan entri yang tidak valid atau Cancel untuk menghapus entri.

Note: Jika Anda tidak mengatur pesan khusus Anda sendiri di Peringatan Kesalahan kotak, default berhenti Akan muncul kotak alert prompt seperti di bawah ini:

3. Contoh dasar untuk validasi data

Saat menggunakan fitur Validasi Data ini, ada 8 opsi bawaan yang disediakan bagi Anda untuk mengatur validasi data. Seperti: nilai apa pun, bilangan bulat dan desimal, tanggal dan waktu, daftar, panjang teks, dan rumus khusus. Di bagian ini kita akan membahas bagaimana menggunakan beberapa opsi bawaan di Excel?

3.1 Validasi data untuk bilangan bulat dan desimal

1. Pilih daftar sel tempat Anda ingin mengizinkan hanya bilangan bulat atau desimal, lalu klik Data > Validasi Data > Validasi Data.

2. di Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih item yang sesuai Bilangan bulat or Desimal dalam mengizinkan kotak drop-down.
• Dan kemudian, pilih salah satu kriteria yang Anda butuhkan di Data kotak (Dalam contoh ini, saya memilih antara pilihan).
• Tips: Kriteria berisi: antara, tidak antara, sama dengan, tidak sama dengan, lebih besar dari, kurang dari, lebih besar dari atau sama dengan, kurang dari atau sama dengan.
• Selanjutnya, masukkan Minimum serta Maksimum nilai yang Anda butuhkan (saya ingin angka antara 0 dan 1).
• Terakhir, klik OK .

3. Sekarang, hanya bilangan bulat dari 0 hingga 100 yang diizinkan untuk dimasukkan ke dalam sel yang Anda pilih.

3.2 Validasi data untuk tanggal dan waktu

Untuk memvalidasi tanggal atau waktu tertentu yang akan dimasukkan, mudah dengan menggunakan ini Validasi Data, lakukan hal berikut:

1. Pilih daftar sel tempat Anda ingin mengizinkan hanya tanggal atau waktu tertentu, lalu klik Data > Validasi Data > Validasi Data.

2. di Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih item yang sesuai Tanggal or Waktu dalam mengizinkan kotak drop-down.
• Dan kemudian, pilih salah satu kriteria yang Anda butuhkan di Data kotak (Di sini saya memilih lebih besar dari pilihan).
• Tips: Kriteria berisi: antara, tidak antara, sama dengan, tidak sama dengan, lebih besar dari, kurang dari, lebih besar dari atau sama dengan, kurang dari atau sama dengan.
• Selanjutnya, masukkan Tanggal mulai yang Anda butuhkan (saya ingin tanggal lebih besar dari 8/20/2021).
• Terakhir, klik OK .

3. Sekarang, hanya tanggal yang lebih besar dari 8/20/2021 yang diizinkan untuk dimasukkan ke dalam sel yang Anda pilih.

3.3 Validasi data untuk panjang teks

Jika Anda perlu membatasi jumlah karakter yang dapat diketik ke dalam sel. Misalnya, untuk membatasi konten tidak lebih dari 10 karakter untuk rentang tertentu, ini Validasi Data juga dapat membantu Anda.

1. Pilih daftar sel tempat Anda ingin membatasi panjang teks, lalu klik Data > Validasi Data > Validasi Data.

2. di Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Panjang teks dari mengizinkan kotak drop-down.
• Dan kemudian, pilih salah satu kriteria yang Anda butuhkan di Data kotak (Dalam contoh ini, saya memilih kurang dari pilihan).
• Tips: Kriteria berisi: antara, tidak antara, sama dengan, tidak sama dengan, lebih besar dari, kurang dari, lebih besar dari atau sama dengan, kurang dari atau sama dengan.
• Selanjutnya, masukkan Maksimum jumlah yang perlu Anda batasi (saya ingin panjang teks tidak lebih dari 10 karakter).
• Terakhir, klik OK .

3. Sekarang, sel yang dipilih hanya memungkinkan untuk mengetik string teks kurang dari 10 karakter.

3.4 Daftar validasi data (daftar drop-down)

Dengan kekuatan ini Validasi Data fitur, Anda dapat membuat daftar drop-down di sel dengan cepat dan mudah juga. Silakan lakukan seperti ini:

1. Pilih sel target tempat memasukkan daftar drop-down, lalu klik Data > Validasi Data > Validasi Data.

2. di Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Daftar dari mengizinkan daftar drop-down.
• Dalam majalah sumber kotak teks, ketik item daftar langsung dipisahkan dengan koma. Misalnya, untuk membatasi input pengguna ke tiga pilihan, ketik Not Started,In Progress,Completed, atau Anda dapat memilih daftar sel yang berisi nilai untuk menyisipkan drop-down berdasarkan.
• Terakhir, klik OK .

3. Sekarang, daftar drop-down telah dibuat ke dalam sel seperti gambar di bawah ini:

Klik untuk mengetahui informasi lebih rinci dari daftar drop-down…

4. Aturan khusus lanjutan untuk validasi data

Bagian ini, saya akan memperkenalkan cara membuat beberapa aturan validasi data kustom tingkat lanjut untuk memecahkan jenis masalah Anda, seperti: membuat rumus validasi untuk mengizinkan hanya angka atau string teks, hanya nilai unik, hanya nomor telepon tertentu, alamat email, dan sebagainya .

4.1 Validasi data hanya mengizinkan angka atau teks

Untuk mengizinkan hanya angka dalam rentang sel, lakukan seperti ini:

1. Pilih rentang sel yang hanya ingin Anda masukkan angka.

2. Klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus ini: =INUMBER(A2) ke dalam Rumus kolom tulisan. (A2 adalah sel pertama dari rentang yang dipilih yang ingin Anda batasi)
• Klik OK tombol untuk menutup dialog ini.

3. Mulai sekarang, hanya angka yang dapat dimasukkan ke dalam sel yang dipilih.

Note: Ini ISNUMBER fungsi memungkinkan nilai numerik apa pun dalam sel yang divalidasi, termasuk bilangan bulat, desimal, pecahan, tanggal, dan waktu.

Untuk membatasi entri sel ke teks saja, Anda dapat menggunakan Validasi Data fitur dengan formula khusus berdasarkan ISTEXT fungsi, lakukan seperti ini:

1. Pilih rentang sel yang hanya ingin Anda masukkan string teks.

2. Klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus ini: =ITEXT(A2) ke dalam Rumus kolom tulisan. (A2 adalah sel pertama dari rentang yang dipilih yang ingin Anda batasi)
• Klik OK tombol untuk menutup dialog ini.

3. Sekarang, saat memasukkan data ke dalam sel tertentu, hanya data format teks yang diizinkan.

4.2 Validasi data hanya mengizinkan nilai alfanumerik

Untuk beberapa tujuan, Anda hanya ingin mengizinkan huruf dan nilai numerik untuk dimasukkan, tetapi membatasi karakter khusus seperti ~,%,$, spasi, dll., bagian ini akan memperkenalkan beberapa trik untuk Anda.

Untuk mencegah karakter khusus tetapi hanya mengizinkan nilai alfanumerik, Anda harus membuat rumus khusus ke dalam Validasi Data fungsi, lakukan hal berikut:

1. Pilih rentang sel yang Anda ingin hanya memasukkan nilai alfanumerik.

2. Klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih yang ingin Anda batasi.

3. Sekarang, hanya huruf dan nilai numerik yang diizinkan untuk dimasukkan, dan karakter khusus akan dibatasi saat mengetik seperti gambar di bawah ini:

Mungkin rumus di atas rumit untuk kita pahami dan ingat, di sini, saya akan memperkenalkan fitur praktis – Cegah Pengetikan of Kutools untuk Excel, dengan fitur ini, Anda dapat dengan cepat menyelesaikan pekerjaan ini dengan mudah.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih rentang sel yang Anda ingin hanya memasukkan nilai alfanumerik.

2. Lalu klik Kutools > Cegah Pengetikan > Cegah Pengetikan, lihat tangkapan layar:

3. Di muncul keluar Cegah Pengetikan kotak dialog, pilih Cegah ketik karakter khusus opsi, lihat tangkapan layar:

4. Lalu klik Ok tombol, dan di kotak prompt berikut, klik Yes > OK untuk menyelesaikan operasi. Sekarang, di sel yang dipilih, hanya huruf dan nilai numerik yang diizinkan, lihat tangkapan layar:

4.3 Validasi data memungkinkan teks dimulai atau diakhiri dengan karakter tertentu

Jika semua nilai dalam rentang tertentu harus dimulai atau diakhiri dengan karakter atau substring tertentu, Anda dapat menggunakan validasi data dengan rumus khusus berdasarkan fungsi EXACT, LEFT, RIGHT, atau COUNTIF.

Misalnya, saya ingin teks harus dimulai atau diakhiri dengan "CN" saat memasukkan string teks dalam sel tertentu, lakukan seperti ini:

1. Pilih rentang sel yang hanya mengizinkan teks yang dimulai atau diakhiri dengan karakter tertentu.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• Begin with: =EXACT(LEFT(A2,2),"CN")
  End with: =EXACT(RIGHT(A2,2),"CN")
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih, nomor 2 adalah jumlah karakter yang Anda tentukan, CN adalah teks yang ingin Anda mulai atau akhiri.

3. Mulai sekarang, hanya string teks yang dimulai atau diakhiri dengan karakter tertentu yang dapat dimasukkan ke dalam sel yang dipilih. Jika tidak, peringatan peringatan akan muncul untuk mengingatkan Anda seperti gambar di bawah ini:

Tip: Rumus di atas peka huruf besar/kecil, jika tidak perlu peka huruf besar/kecil, silakan terapkan rumus CONTIF di bawah ini:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

Note: Tanda bintang * adalah karakter pengganti yang cocok dengan satu atau lebih karakter.

Misalnya, jika Anda ingin teks harus dimulai atau diakhiri dengan "CN" atau "UK" seperti gambar di bawah ini, Anda perlu menambahkan contoh EXACT lainnya dengan menggunakan tanda plus (+). Silakan lakukan dengan langkah-langkah berikut:

1. Pilih rentang sel yang hanya mengizinkan teks yang dimulai atau diakhiri dengan beberapa kriteria.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
  End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih, nomor 2 adalah jumlah karakter yang Anda tentukan, CN serta UK adalah teks spesifik yang ingin Anda mulai atau akhiri.

3. Sekarang, hanya string teks yang dimulai atau diakhiri dengan karakter tertentu yang dapat dimasukkan ke dalam sel yang dipilih.

Tip: Untuk mengabaikan peka huruf besar/kecil, harap terapkan rumus CONTIF di bawah ini:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Note: Tanda bintang * adalah karakter pengganti yang cocok dengan satu atau lebih karakter.

4.4 Validasi data memungkinkan entri harus berisi / tidak boleh berisi teks tertentu

Bagian ini, saya akan berbicara tentang bagaimana menerapkan Validasi Data untuk memungkinkan nilai harus mengandung atau tidak boleh mengandung satu substring tertentu atau salah satu dari banyak substring di Excel.

Izinkan entri harus berisi satu teks tertentu

Untuk mengizinkan entri yang berisi string teks tertentu, misalnya, semua nilai yang dimasukkan harus berisi teks "KTE" seperti gambar di bawah ini, Anda dapat menerapkan validasi data dengan rumus khusus berdasarkan fungsi FIND dan ISNUMBER. Silakan lakukan seperti ini:

1. Pilih rentang sel yang hanya mengizinkan teks yang berisi teks tertentu.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari daftar drop-down Izinkan.
• Dan kemudian, masukkan salah satu rumus di bawah ini ke dalam Rumus kolom tulisan.
• =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
  =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih, teks KTE adalah string teks yang harus berisi entri.

3. Sekarang, ketika nilai yang dimasukkan tidak berisi teks yang dirancang, kotak peringatan akan muncul.

Izinkan entri harus berisi salah satu dari banyak teks tertentu

Rumus di atas hanya berfungsi untuk satu string teks, jika Anda memerlukan salah satu dari banyak string teks untuk diizinkan dalam sel seperti gambar berikut yang ditampilkan, Anda harus menggunakan fungsi SUMPRODUCT, FIND dan ISNUMBER bersama-sama untuk membuat rumus.

1. Pilih rentang sel yang hanya mengizinkan teks yang berisi salah satu dari banyak item.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Kemudian, masukkan salah satu rumus di bawah ini sesuai kebutuhan Anda ke dalam Rumus kolom tulisan.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
• Dan kemudian, klik OK untuk menutup kotak dialog

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih, C2: C4 adalah daftar nilai yang ingin Anda izinkan entri berisi salah satu dari nilai tersebut.

3. Dan sekarang, hanya entri yang berisi salah satu nilai dalam daftar tertentu yang dapat dimasukkan.

Izinkan entri tidak boleh berisi satu teks tertentu

Untuk memvalidasi entri tidak boleh berisi teks tertentu, misalnya, untuk mengizinkan nilai yang tidak boleh berisi teks "KTE" dalam sel, Anda bisa menggunakan fungsi ISERROR dan FIND untuk membuat aturan validasi data. Silakan lakukan seperti ini:

1. Pilih rentang sel yang hanya mengizinkan teks yang tidak berisi teks tertentu.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan salah satu rumus di bawah ini ke dalam Rumus kolom tulisan.
• =ISERROR(FIND("KTE",A2))                  (Case sensitive)
  =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih, teks KTE adalah string teks yang tidak boleh berisi entri.

3. Sekarang, entri yang berisi teks tertentu akan dicegah untuk dimasukkan.

Izinkan entri tidak boleh mengandung salah satu dari banyak teks tertentu

Untuk mencegah salah satu dari banyak string teks dalam daftar dimasukkan seperti gambar di bawah ini, Anda harus melakukan hal ini:

1. Pilih rentang sel yang Anda inginkan agar beberapa teks dicegah.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
• Dan kemudian, klik OK untuk menutup kotak dialog

Note: Dalam rumus di atas, A2 adalah sel pertama dari rentang yang dipilih, C2: C4 adalah daftar nilai yang ingin Anda cegah jika entri berisi salah satunya.

3. Mulai sekarang, entri yang berisi salah satu teks tertentu akan dicegah untuk dimasukkan.

4.5 Validasi data hanya mengizinkan nilai unik

Jika Anda ingin mencegah data duplikat dimasukkan ke dalam rentang sel, bagian ini akan memperkenalkan beberapa metode cepat untuk menyelesaikan tugas ini di Excel.

Biasanya, fitur Validasi Data dengan rumus khusus berdasarkan fungsi COUNTIF dapat membantu Anda, lakukan dengan langkah-langkah berikut:

1. Pilih sel atau kolom yang hanya ingin Anda masukkan nilai uniknya.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =COUNTIF($A$2:$A$9,A2)=1
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2: A9 adalah rentang sel yang hanya ingin Anda izinkan nilai uniknya, dan A2 adalah sel pertama dari rentang yang dipilih.

3. Sekarang, hanya nilai unik yang diizinkan untuk dimasukkan, dan pesan peringatan akan muncul saat data duplikat dimasukkan, lihat tangkapan layar:

Kode VBA berikut juga dapat membantu Anda untuk mencegah nilai duplikat dimasukkan, lakukan seperti ini:

1. Klik kanan tab lembar yang hanya ingin Anda izinkan nilai uniknya, dan pilih Lihat kode dari menu konteks, di-pop-out Microsoft Visual Basic untuk Aplikasi jendela, silakan salin dan tempel kode berikut ke dalam Modul kosong:

Kode VBA: Hanya izinkan nilai unik dalam rentang sel:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Note: Pada kode di atas, file A1: A100 serta A: A adalah sel-sel di kolom yang ingin Anda cegah duplikatnya, silakan ubah sesuai kebutuhan Anda.

2. Kemudian simpan dan tutup kode ini, sekarang, saat memasukkan nilai duplikat di sel A1:A100, kotak peringatan akan muncul seperti gambar di bawah ini:

Jika Anda memiliki Kutools untuk Excel, Dengan yang Mencegah Duplikat fitur, Anda dapat mengatur validasi data untuk mencegah duplikat untuk rentang sel hanya dengan beberapa klik.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih rentang sel yang Anda inginkan untuk mencegah nilai duplikat tetapi hanya mengizinkan data unik.

2. Lalu klik Kutools > Cegah Pengetikan > Mencegah Duplikat, lihat tangkapan layar:

3. Dan pesan peringatan akan muncul untuk mengingatkan Anda bahwa Validasi Data akan dihapus jika menerapkan fitur ini, klik Yes dan di kotak prompt berikut, klik OK, lihat tangkapan layar:

4. Sekarang, ketika Anda memasukkan beberapa data duplikat di sel yang Anda tentukan, kotak prompt ditampilkan untuk mengingatkan Anda bahwa data duplikat tidak valid, lihat tangkapan layar:

4.6 Validasi data hanya mengizinkan huruf besar / huruf kecil / huruf besar saja

Validasi Data ini adalah fitur yang kuat, ini juga dapat membantu untuk memungkinkan pengguna memasukkan hanya entri huruf besar, huruf kecil, atau huruf besar yang tepat dalam rentang sel. Silakan lakukan dengan langkah-langkah berikut:

1. Pilih rentang sel yang hanya ingin Anda masukkan huruf besar, huruf kecil, atau teks huruf besar.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan salah satu rumus di bawah ini yang Anda butuhkan ke dalam Rumus kolom tulisan.
• =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan.

3. Sekarang, hanya entri yang sesuai dengan aturan yang Anda buat yang akan diterima.

4.7 Validasi data memungkinkan nilai yang ada / tidak ada di daftar lain

Mengizinkan nilai yang ada atau tidak ada dalam daftar lain untuk dimasukkan dalam rentang sel mungkin merupakan masalah yang menyakitkan bagi sebagian besar dari kita. Bahkan, Anda bisa menggunakan fitur validasi data dengan rumus sederhana berdasarkan fungsi COUNTIF untuk mengatasinya.

Misalnya, saya hanya ingin nilai dalam rentang C2:C4 dimasukkan dalam rentang sel seperti gambar di bawah ini, untuk menyelesaikan pekerjaan ini, lakukan seperti ini:

1. Pilih rentang sel yang ingin Anda terapkan validasi datanya.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan salah satu rumus di bawah ini yang Anda butuhkan ke dalam Rumus kolom tulisan.
• =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
  =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan, C2: C4 adalah daftar nilai yang ingin Anda cegah atau izinkan jika entri adalah salah satunya.

3. Sekarang, entri hanya sesuai dengan aturan yang Anda buat yang dapat dimasukkan, yang lain akan dicegah.

4.8 Validasi data memaksa hanya format nomor telepon yang dimasukkan

Ketika Anda menginput informasi karyawan perusahaan Anda, satu kolom perlu mengetikkan nomor telepon, untuk memastikan untuk memasukkan nomor telepon dengan cepat dan akurat, dalam hal ini Anda dapat mengatur validasi data untuk nomor telepon. Misalnya, saya hanya ingin nomor telepon sebagai format ini (123) 456-7890 diizinkan masuk ke lembar kerja, bagian ini akan memperkenalkan dua trik cepat untuk menyelesaikan tugas ini.

Untuk hanya mengizinkan format nomor telepon tertentu yang dimasukkan, lakukan seperti ini:

1. Pilih daftar sel yang ingin Anda masukkan format nomor telepon tertentu, lalu klik kanan, pilih Format Cells dari menu konteks, lihat tangkapan layar:

2. di Format Cells kotak dialog, di bawah Jumlah tab, pilih Kustom di kiri Kategori kotak daftar, lalu masukkan format nomor telepon yang Anda butuhkan ke dalam kotak teks Jenis, misalnya, saya akan menggunakan ini (###) ### - #### format, lihat tangkapan layar:

3. Lalu klik OK untuk menutup kotak dialog

4. Setelah memformat sel, lanjutkan memilih sel, lalu buka Validasi Data kotak dialog dengan klik Data > Validasi Data > Validasi Data, dalam dialog yang muncul, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus ini = DAN (ISNUMBER (A2), LEN (A2) = 10) ke dalam kotak teks Formula.
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda validasi nomor teleponnya.

5. Sekarang, ketika memasukkan nomor 10 digit, itu akan dikonversi ke format nomor telepon tertentu secara otomatis sesuai kebutuhan, lihat tangkapan layar:

Note: Jika nomor yang dimasukkan bukan 10 digit, kotak pesan peringatan akan muncul untuk mengingatkan Anda, lihat tangkapan layar:

Kutools untuk Excel'S Validasi Nomor Telepon fitur juga dapat membantu Anda untuk memaksa hanya format nomor telepon yang akan dimasukkan hanya dengan beberapa klik.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih daftar sel yang hanya mengizinkan nomor telepon tertentu, lalu, klik Kutools > Cegah Pengetikan > Validasi Nomor Telepon, lihat tangkapan layar:

2. di Nomor telepon kotak dialog, pilih format nomor telepon tertentu yang Anda butuhkan atau Anda dapat membuat format Anda sendiri dengan mengklik Add tombol, lihat tangkapan layar:

3. Setelah memilih atau mengatur pemformatan nomor telepon, klik OK, sekarang, hanya nomor telepon dengan format tertentu yang dapat dimasukkan, jika tidak, pesan peringatan akan muncul untuk mengingatkan Anda, lihat tangkapan layar:

4.9 Validasi data hanya memaksa alamat email yang akan dimasukkan

Misalkan, Anda perlu mengetikkan beberapa alamat email di kolom lembar kerja, untuk mencegah beberapa format alamat email yang salah dimasukkan, biasanya, Anda dapat mengatur aturan validasi data untuk hanya mengizinkan pemformatan alamat email.

Dengan menggunakan fitur Validasi Data dengan rumus khusus, Anda dapat membuat aturan untuk mencegah masuknya alamat email yang tidak valid dengan cepat, lakukan seperti ini:

1. Pilih sel yang Anda ingin hanya memasukkan alamat email, lalu klik Data > Validasi Data > Validasi Data.

2. Di muncul keluar Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus ini = ISNUMBER (MATCH ("*@*.?*", A2,0)) ke dalam Rumus kolom tulisan.
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan.

3. Sekarang, jika teks yang dimasukkan bukan format alamat email, kotak pesan peringatan akan muncul untuk mengingatkan Anda, lihat tangkapan layar:

Kutools untuk Excel mendukung fitur luar biasa - Validasi Alamat Email, dengan utilitas ini, Anda dapat mencegah alamat email yang tidak valid hanya dengan satu klik.

Setelah menginstal Kutools untuk Excel, lakukan hal berikut:

1. Pilih sel yang hanya Anda izinkan untuk memasukkan alamat email, lalu klik Kutools > Cegah Pengetikan > Validasi Alamat Email. Lihat tangkapan layar:

2. Dan kemudian, hanya pemformatan alamat email yang diizinkan untuk dimasukkan, jika tidak, kotak pesan peringatan akan muncul untuk mengingatkan Anda, lihat tangkapan layar:

4.10 Validasi data memaksa hanya alamat IP yang dimasukkan

Bagian ini, saya akan memperkenalkan beberapa trik cepat untuk mengatur validasi data agar hanya menerima alamat IP dalam rentang sel.

Izinkan hanya alamat IP untuk diketik ke dalam rentang sel tertentu, lakukan seperti ini:

1. Pilih sel yang Anda ingin hanya memasukkan alamat IP, lalu klik Data > Validasi Data > Validasi Data.

2. Di muncul keluar Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
• Klik OK tombol untuk menutup dialog ini.

Catatan: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan.

3. Sekarang, jika Anda memasukkan alamat IP yang tidak valid ke dalam sel, kotak pesan peringatan akan muncul untuk mengingatkan Anda seperti gambar di bawah ini:

Di sini, kode VBA berikut juga dapat membantu untuk mengizinkan hanya alamat IP yang dimasukkan dan membatasi penginputan lainnya, lakukan seperti ini:

1. Klik kanan tab lembar dan klik Lihat kode dari menu konteks, di pembukaan Microsoft Visual Basic untuk Aplikasi jendela, salin kode VBA di bawah ini ke dalamnya.

Kode VBA: memvalidasi sel untuk hanya menerima alamat IP

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Note: Pada kode di atas, A2: A10 adalah rentang sel yang ingin Anda terima hanya alamat IP.

2. Kemudian simpan dan tutup kode ini, sekarang, hanya alamat IP yang benar yang diizinkan untuk dimasukkan ke dalam sel tertentu.

Jika Anda memiliki Kutools untuk Excel diinstal di buku kerja Anda, itu Validasi Alamat IP fitur dapat membantu Anda untuk menyelesaikan tugas ini juga.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih sel yang hanya Anda izinkan untuk memasukkan alamat IP, lalu klik Kutools > Cegah Pengetikan > Validasi Alamat IP. Lihat tangkapan layar:

2. Setelah menerapkan fitur ini, sekarang, hanya alamat IP yang diizinkan untuk dimasukkan, jika tidak, kotak pesan peringatan akan muncul untuk mengingatkan Anda, lihat tangkapan layar:

4.11 Validasi data membatasi nilai yang melebihi nilai total

Misalkan, Anda memiliki laporan pengeluaran bulanan, dan total anggaran adalah $ 18000, sekarang, Anda memerlukan jumlah total dalam daftar pengeluaran tidak melebihi total preset $ 18000 seperti gambar di bawah ini. Dalam hal ini, Anda dapat membuat aturan validasi data dengan menggunakan fungsi SUM untuk mencegah jumlah nilai melebihi total preset.

1. Pilih daftar sel di mana Anda ingin nilai dibatasi.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =SUM($B$2:$B$7)<=18000
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, B2: B7 adalah rentang sel yang ingin Anda batasi entrinya.

3. Sekarang, saat memasukkan nilai dalam rentang B2:B7, jika total nilainya kurang dari $18000, validasi akan lolos. Jika ada nilai yang membuat total melebihi $18000, kotak pesan peringatan akan muncul untuk mengingatkan Anda.

4.12 Validasi data membatasi entri sel berdasarkan sel lain

Saat Anda ingin membatasi entri data dalam daftar sel berdasarkan nilai di sel lain, fitur Validasi Data juga dapat membantu menyelesaikan pekerjaan ini. Misalnya, jika sel C1 adalah teks "Ya", rentang A2:A9 diizinkan untuk memasukkan apa pun, tetapi, jika sel C1 adalah teks lain, tidak ada yang diizinkan untuk dimasukkan dalam rentang A2:A9 seperti tangkapan layar di bawah ini. :

Untuk mengatasi pemecahan ini, lakukan seperti ini:

1. Pilih daftar sel di mana Anda ingin nilai dibatasi.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =$C$1="Yes"
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, C1 adalah sel berisi teks spesifik yang ingin Anda gunakan, dan teks “Yes” adalah teks yang ingin Anda batasi selnya, silakan ubah sesuai kebutuhan Anda.

3. Sekarang, jika sel C1 memiliki teks "Ya", apa pun dapat dimasukkan ke dalam rentang A2:A9, jika sel C1 memiliki teks lain, Anda tidak akan dapat memasukkan nilai apa pun, lihat demo di bawah ini:

4.13 Validasi data hanya mengizinkan hari kerja atau akhir pekan untuk dimasukkan

Jika Anda hanya membutuhkan hari kerja (dari Senin hingga Jumat) atau akhir pekan (Sabtu dan Minggu) untuk dimasukkan ke dalam daftar sel, Validasi Data juga dapat membantu Anda, silakan lakukan dengan langkah-langkah berikut:

1. Pilih daftar sel tempat Anda ingin memasukkan hari kerja atau hari kerja.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan salah satu rumus di bawah ini ke dalam Rumus kotak teks yang Anda butuhkan.
• =WEEKDAY(A2,2)<6                      (allow only weekdays)
  =WEEKDAY(A2,2)>5                      (allow only weekends)
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan.

3. Sekarang, Anda hanya dapat memasukkan tanggal hari kerja atau akhir pekan di sel tertentu berdasarkan kebutuhan Anda.

4.14 Validasi data memungkinkan tanggal yang dimasukkan berdasarkan tanggal hari ini

Terkadang, Anda mungkin hanya perlu mengizinkan tanggal yang lebih besar atau lebih kecil dari hari ini untuk dimasukkan ke dalam daftar sel. NS Validasi Data fitur dengan HARI INI fungsi dapat membantu Anda. Silakan lakukan seperti ini:

1. Pilih daftar sel di mana Anda hanya ingin tanggal yang akan datang (tanggal lebih besar dari hari ini) yang akan dimasukkan.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =A2>Today()
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan.

3. Sekarang, hanya tanggal yang lebih besar dari tanggal hari ini yang dapat dimasukkan ke dalam sel, jika tidak, kotak pesan peringatan akan muncul untuk mengingatkan Anda, lihat tangkapan layar:

Tip:

1. Agar tanggal yang lalu (tanggal kurang dari hari ini) dapat dimasukkan, harap terapkan rumus di bawah ini ke dalam Validasi Data:

=A2<Today()

2. Perkenankan tanggal dalam rentang tanggal tertentu untuk dimasukkan, seperti tanggal dalam 30 hari ke depan, silakan masukkan rumus di bawah ini ke dalam Validasi Data:

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 Validasi data memungkinkan waktu yang dimasukkan berdasarkan waktu saat ini

Jika Anda ingin memvalidasi data berdasarkan waktu saat ini, misalnya, hanya waktu sebelum atau sesudah waktu saat ini yang dapat diketikkan ke dalam sel. Anda dapat membuat rumus validasi data Anda sendiri, lakukan seperti ini:

1. Pilih daftar sel di mana Anda hanya ingin memasukkan waktu sebelum atau sesudah waktu saat ini.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Waktu dari mengizinkan daftar drop-down
• Lalu pilih kurang dari untuk mengizinkan hanya waktu sebelum waktu saat ini, atau lebih besar dari untuk memungkinkan waktu setelah waktu saat ini yang Anda butuhkan dari Data turun.
• Dan kemudian, di Akhir waktu or Waktu mulai kotak, masukkan rumus di bawah ini:
• =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan.

3. Sekarang, hanya waktu sebelum atau sesudah waktu saat ini yang dapat dimasukkan ke dalam sel tertentu.

4.16 Validasi data tanggal tahun tertentu atau tahun berjalan

Untuk mengizinkan hanya tanggal dalam tahun tertentu atau tahun berjalan yang akan dimasukkan, Anda dapat menggunakan validasi data dengan rumus khusus berdasarkan fungsi YEAR.

1. Pilih daftar sel tempat Anda hanya ingin memasukkan tanggal dalam tahun tertentu.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Kustom dari mengizinkan daftar drop-down
• Dan kemudian, masukkan rumus di bawah ini ke dalam Rumus kolom tulisan.
• =YEAR(A2)=2020
• Klik OK tombol untuk menutup dialog ini.

Note: Dalam rumus di atas, A2 adalah sel pertama dari kolom yang ingin Anda gunakan, 2020 adalah nomor tahun yang ingin Anda batasi.

3. Dan kemudian, hanya tanggal di tahun 2020 yang dapat dimasukkan, jika tidak, kotak pesan peringatan akan muncul seperti gambar di bawah ini:

Tip:

Untuk mengizinkan hanya tanggal di tahun berjalan, Anda dapat menerapkan rumus di bawah ini ke dalam validasi data:

=YEAR(A2)=YEAR(TODAY())

4.17 Validasi data tanggal dalam minggu atau bulan ini

Jika Anda ingin mengizinkan pengguna untuk dapat memasukkan tanggal minggu atau bulan saat ini dalam sel tertentu, bagian ini akan memperkenalkan beberapa rumus untuk menangani tugas ini di Excel.

1. Pilih daftar sel tempat Anda hanya ingin memasukkan tanggal dalam minggu ini.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Tanggal dari mengizinkan daftar drop-down
• Lalu, pilih antara dari Data turun.
• Dalam majalah Tanggal mulai kotak teks, masukkan rumus ini: =HARI INI()-WEEKDAY(HARI INI(),3)
• Dalam majalah Tanggal akhir kotak teks, masukkan rumus ini: =HARI INI()-WEEKDAY(HARI INI(),3)+6
• Terakhir, klik OK .

3. Kemudian, hanya tanggal dalam minggu ini yang dapat dimasukkan, tanggal lain akan dicegah seperti gambar di bawah ini:

Untuk hanya mengizinkan tanggal bulan berjalan yang akan dimasukkan, lakukan hal berikut:

1. Pilih daftar sel di mana Anda hanya ingin tanggal di bulan ini yang akan dimasukkan.

2. Lalu klik Data > Validasi Data > Validasi Data, di munculkan Validasi Data kotak dialog, di bawah Settings tab, lakukan operasi berikut:

• Pilih Tanggal dari mengizinkan daftar drop-down
• Dan kemudian, pilih antara dari Data turun.
• Dalam majalah Tanggal mulai kotak teks, masukkan rumus ini: =TANGGAL(TAHUN(HARI INI()),BULAN(HARI INI()),1)
• Dalam majalah Tanggal akhir kotak teks, masukkan rumus ini: =TANGGAL(TAHUN(HARI INI()),BULAN(HARI INI()),HARI(TANGGAL(TAHUN(HARI INI()),BULAN(HARI INI())+1,1)-1))
• Terakhir, klik OK .

3. Mulai sekarang, hanya tanggal bulan ini yang diizinkan untuk dimasukkan ke dalam sel yang dipilih.

5. Bagaimana cara mengedit validasi data di Excel?

Untuk mengedit atau mengubah aturan validasi data yang ada, ikuti langkah-langkah di bawah ini:

1. Pilih salah satu sel dengan aturan validasi data.

2. Lalu klik Data > Validasi Data > Validasi Data untuk pergi ke Validasi Data kotak dialog, di dalam kotak, edit atau ubah aturan sesuai kebutuhan Anda, lalu centang Terapkan perubahan ini ke semua sel lain dengan pengaturan yang sama opsi untuk menerapkan aturan baru ini ke semua sel lain dengan kriteria validasi asli. Lihat tangkapan layar:

3. Klik OK Untuk menyimpan perubahan

6. Bagaimana menemukan dan memilih sel dengan validasi data di Excel?

Jika Anda telah membuat beberapa aturan validasi data di lembar kerja Anda, sekarang, Anda perlu menemukan dan memilih sel yang menerapkan aturan validasi data, Pergi ke khusus perintah dapat membantu Anda memilih semua jenis validasi data atau jenis validasi data tertentu.

1. Aktifkan lembar kerja yang ingin Anda temukan dan pilih sel dengan validasi data.

2. Lalu klik Beranda > Temukan & Pilih > Pergi ke khusus, lihat tangkapan layar:

3. di Pergi ke khusus kotak dialog, pilih Validasi data > Semua, lihat tangkapan layar:

4. Dan semua sel dengan validasi data telah dipilih sekaligus di lembar kerja saat ini.

Tips: Jika Anda hanya ingin memilih satu jenis validasi data tertentu, pertama, pilih satu sel yang berisi validasi data tertentu yang ingin Anda ketahui, lalu buka Pergi ke khusus kotak dialog, dan pilih Validasi data > Sama.

7. Bagaimana cara menyalin aturan validasi data ke sel lain?

Misalkan, Anda telah membuat aturan validasi data untuk daftar sel, dan sekarang, Anda perlu menerapkan aturan validasi data yang sama ke sel lain. Alih-alih membuat aturan lagi, Anda dapat menyalin dan menempelkan aturan yang ada ke sel lain dengan cepat dan mudah.

1. Klik untuk memilih satu sel dengan aturan validasi yang ingin Anda gunakan, lalu tekan Ctrl + C untuk menyalinnya

2. Kemudian, pilih sel yang ingin Anda validasi, untuk memilih beberapa sel yang tidak berdekatan, tekan dan tahan tombol Ctrl kunci saat memilih sel.

3. Dan kemudian, klik kanan pilihan, pilih sisipkan Khusus opsi, lihat tangkapan layar:

4. di sisipkan Khusus kotak dialog, pilih Pengesahan opsi, lihat tangkapan layar:

5. Klik OK tombol, sekarang aturan validasi disalin ke sel baru.

8. Bagaimana cara menggunakan validasi data untuk melingkari entri yang tidak valid di Excel?

Terkadang, Anda mungkin perlu membuat aturan validasi data untuk data yang ada, dalam hal ini, beberapa data yang tidak valid mungkin muncul dalam rentang sel. Bagaimana cara memeriksa data yang tidak valid dan memodifikasinya? Di Excel, Anda dapat menggunakan: Lingkari Data Tidak Valid fitur untuk menyorot data yang tidak valid dengan lingkaran merah.

Untuk melingkari data tidak valid yang Anda butuhkan, Anda harus menerapkan Validasi Data fitur untuk menetapkan aturan untuk rentang data. Silakan lakukan dengan langkah-langkah berikut:

1. Pilih rentang data yang ingin Anda lingkari data yang tidak valid.

2. Lalu klik Data > Validasi Data > Validasi Data, Dalam Validasi Data kotak dialog, atur aturan validasi sesuai kebutuhan Anda, misalnya, di sini, saya akan memvalidasi nilai lebih besar dari 500, lihat tangkapan layar:

3. Lalu klik OK untuk menutup kotak dialog. Setelah mengatur aturan validasi data, klik Data > Validasi Data > Lingkari Data Tidak Valid, maka semua nilai yang tidak valid yang kurang dari 500 telah dilingkari dengan oval merah. Lihat tangkapan layar:

Catatan:

• 1. Segera setelah Anda mengoreksi data yang tidak valid, lingkaran merah akan hilang secara otomatis.
• 2. Ini Lingkari Data Tidak Valid fitur hanya dapat melingkari 255 sel paling banyak. Saat Anda menyimpan buku kerja saat ini, semua lingkaran merah akan dihapus.
• 3. Lingkaran ini tidak dapat dicetak.
• 4. Anda juga dapat menghilangkan lingkaran merah dengan mengklik Data > Validasi Data > Hapus Lingkaran Validasi.

9. Bagaimana cara menghapus validasi data di Excel?

Untuk menghapus aturan validasi data dari rentang sel, lembar kerja saat ini atau seluruh buku kerja, metode berikut dapat membantu Anda.

1. Pilih sel dengan validasi data yang ingin Anda hapus.

2. Lalu klik Data > Validasi Data > Validasi Data, di kotak dialog yang muncul, di bawah Settings tab, klik Batal Semua tombol, lihat tangkapan layar:

3. Lalu klik OK tombol untuk menutup kotak dialog ini. Dan aturan validasi data yang diterapkan pada rentang yang dipilih telah dihapus sekaligus.

Tips: Untuk menghapus validasi data dari lembar kerja saat ini, silakan pilih seluruh lembar terlebih dahulu, lalu terapkan langkah-langkah di atas.

Jika Anda memiliki Kutools untuk Excel, nya Hapus Batasan Validasi Data Fitur ini juga dapat membantu menghapus aturan validasi data dari rentang yang dipilih atau seluruh lembar kerja.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih rentang sel atau seluruh lembar kerja berisi validasi data yang ingin Anda hapus.

2. Lalu klik Kutools > Cegah Pengetikan > Hapus Batasan Validasi Data, lihat tangkapan layar:

3. Di kotak prompt yang muncul, klik OK, dan aturan validasi data telah dihapus sesuai kebutuhan.

Untuk menghapus aturan validasi data dari seluruh buku kerja, metode di atas akan memakan waktu jika ada banyak lembar kerja, di sini, kode di bawah ini dapat membantu Anda untuk menangani tugas ini dengan cepat.

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Lalu klik Menyisipkan > Modul, dan tempelkan makro berikut di file Modul jendela.

Kode VBA: Hapus aturan validasi data di semua lembar kerja:

Sub RemoveDataValidation() 'Pembaruanoleh Extendoffice
  Redupkan xwsh Sebagai Lembar Kerja Untuk Setiap xwsh Di ActiveWorkbook.Worksheets xwsh.Cells.Validation.Delete Next xwsh End Sub

3. Lalu tekan F5 untuk menjalankan kode ini, dan semua aturan validasi data telah segera dihapus dari seluruh buku kerja.

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

• Super Formula Bar (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
• Gabungkan Sel / Baris / Kolom dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... Mencegah Sel Duplikat; Bandingkan Rentang...
• Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
• Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
• Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
• Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
• Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
• Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
• Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...

Office Tab - menghadirkan antarmuka tab ke Office, dan membuat pekerjaan Anda jauh lebih mudah

• Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
• Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
• Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!

Langkah validasi data di Excel?

Langkah awal untuk melakukan validasi data adalah?

Apa itu validasi data Excel?

Bagaimana validasi data?