Validasi data Excel: menambah, menggunakan, menyalin, dan menghapus validasi data di Excel
Di Excel, fitur Validasi Data merupakan alat yang ampuh yang dapat Anda gunakan untuk membatasi apa yang dapat dimasukkan pengguna ke dalam sel. Misalnya, Anda dapat menetapkan aturan untuk membatasi panjang teks, membatasi entri ke format tertentu, menerapkan nilai unik, atau memastikan teks dimulai atau diakhiri dengan karakter tertentu. Kemampuan ini membantu menjaga integritas data dan mengurangi kesalahan dalam lembar kerja Anda.
Tutorial ini membahas cara menambahkan, menggunakan, dan menghapus validasi data di Excel. Tutorial ini mencakup operasi dasar dan lanjutan, serta menyediakan panduan langkah demi langkah terperinci untuk membantu Anda menerapkan fitur ini secara efektif pada tugas Anda.
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
- 4.3 Validasi data memungkinkan teks dimulai atau diakhiri dengan karakter tertentu
- 4.4 Validasi data memungkinkan entri harus berisi / tidak boleh berisi teks tertentu
- 4.5 Validasi data hanya mengizinkan nilai unik
- 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
- 4.9 Validasi data hanya memaksa alamat email yang akan dimasukkan
- 4.10 Validasi data memaksa hanya alamat IP yang dimasukkan
- 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
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?
1. Apa itu validasi data di Excel?
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:
![]() |
|
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 Pengaturan 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:
![]() |
|
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:
![]() |
|
Bila nilai yang dimasukkan tidak valid, kotak peringatan akan muncul, seperti yang ditunjukkan pada gambar di bawah ini:
berhenti pilihan: Klik Mencoba kembali untuk memasukkan kembali nilai atau Cancel untuk membuang entri.
peringatan pilihan: Klik Yes untuk menerima entri yang tidak valid, Tidak untuk mengubahnya, atau Cancel untuk membuangnya.
Informasi pilihan: Klik OK untuk menerima entri yang tidak valid atau Cancel untuk membuangnya.
Note: Jika Anda tidak mengatur pesan khusus Anda sendiri di Peringatan Kesalahan kotak, default berhenti Kotak peringatan akan ditampilkan, seperti yang ditunjukkan 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 Pengaturan tab, lakukan operasi berikut:
- Pilih item yang sesuai Bilangan bulat or Desimal dalam mengizinkan kotak drop-down.
- Dan kemudian pilih salah satu kriteria 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 dan Maksimum nilai yang Anda butuhkan (dalam hal ini, angka antara 0 dan 100).
- Terakhir, klik OK .
3Sekarang, hanya bilangan bulat antara 0 dan 100 yang dapat dimasukkan ke dalam sel yang dipilih.
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 Pengaturan tab, lakukan operasi berikut:
- Pilih item yang sesuai Tanggal or Waktu dalam mengizinkan kotak drop-down.
- Dan kemudian pilih salah satu kriteria 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 .
3Sekarang, hanya tanggal yang lebih besar dari 8/20/2021 yang dapat dimasukkan ke dalam sel yang dipilih.
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 Pengaturan tab, lakukan operasi berikut:
- Pilih Panjang teks dari mengizinkan kotak drop-down.
- Dan kemudian pilih salah satu kriteria 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 .
3Sekarang, sel yang dipilih hanya mengizinkan string teks dengan kurang dari 10 karakter untuk dimasukkan
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 Pengaturan 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
Izinkan hanya angka yang dimasukkan dengan fungsi Validasi Data
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 Validasi Data kotak dialog yang muncul, di bawah Pengaturan 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 TIDAK ADA fungsi memungkinkan nilai numerik apa pun dalam sel yang divalidasi, termasuk bilangan bulat, desimal, pecahan, tanggal, dan waktu.
Izinkan hanya string teks yang dimasukkan dengan fungsi Validasi Data
Untuk membatasi entri sel ke teks saja, Anda dapat menggunakan Validasi Data fitur dengan formula khusus berdasarkan ISTEKS fungsi, lakukan seperti ini:
1. Pilih rentang sel yang hanya ingin Anda masukkan string teks.
2. Klik Data > Validasi Data > Validasi Data. di Validasi Data kotak dialog yang muncul, di bawah Pengaturan 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 mungkin ingin hanya mengizinkan huruf dan nilai numerik sambil membatasi karakter khusus seperti ~, %, $, atau spasi. Bagian ini memperkenalkan beberapa metode yang berguna.
Izinkan hanya nilai alfanumerik dengan fungsi Validasi Data
Untuk mencegah karakter khusus dan hanya mengizinkan nilai alfanumerik, buat rumus kustom di Validasi Data berfungsi dengan mengikuti langkah-langkah 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 Pengaturan 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:
Izinkan hanya nilai alfanumerik dengan fitur luar biasa
Rumus di atas mungkin tampak rumit untuk dipahami dan diingat. Di sini, saya memperkenalkan fitur praktis yang disebut Cegah Pengetikan dari Kutools untuk Excel, yang menyederhanakan tugas ini secara signifikan.
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:
Kutools untuk Excel - Sempurnakan Excel dengan lebih dari 300 alat penting. Nikmati fitur AI gratis selamanya! Get It Now
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 sub-string tertentu, Anda dapat menggunakan validasi data dengan rumus khusus berdasarkan fungsi EXACT, LEFT, RIGHT, atau COUNTIF.
Izinkan teks dimulai atau diakhiri dengan karakter tertentu hanya dengan satu syarat
Misalnya, jika Anda ingin entri teks di sel tertentu dimulai atau diakhiri dengan “CN”, ikuti langkah-langkah berikut:
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 Pengaturan 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.
3Mulai sekarang, hanya string teks yang diawali atau diakhiri dengan karakter tertentu yang dapat dimasukkan ke dalam sel yang dipilih. Jika tidak, peringatan akan muncul, seperti yang ditunjukkan pada 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.
Izinkan teks dimulai atau diakhiri dengan karakter tertentu dengan beberapa kriteria (logika ATAU)
Misalnya, jika Anda ingin entri teks dimulai atau diakhiri dengan “CN” atau “UK” seperti yang ditunjukkan pada gambar di bawah, Anda perlu menambahkan contoh EXACT lainnya dengan menggunakan tanda plus (+). 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 Pengaturan 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 dan 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 atau satu dari banyak teks tertentu
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 Pengaturan 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, jika nilai yang dimasukkan tidak berisi teks yang dibutuhkan, 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 menghendaki salah satu dari beberapa string teks diizinkan di dalam sel, seperti ditunjukkan dalam tangkapan layar berikut, Anda harus menggunakan fungsi SUMPRODUCT, FIND dan ISNUMBER secara bersamaan 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 Pengaturan 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 atau satu dari banyak teks tertentu
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 Pengaturan 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.
3Sekarang, entri yang berisi teks tertentu tidak akan dapat dimasukkan.
Izinkan entri tidak boleh mengandung salah satu dari banyak teks tertentu
Untuk mencegah salah satu dari banyak string teks dalam daftar dimasukkan, seperti yang ditunjukkan pada gambar di bawah, ikuti langkah-langkah berikut:
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 Pengaturan 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 teks tertentu tidak akan dapat dimasukkan lagi.
4.5 Validasi data hanya mengizinkan nilai unik
Jika Anda ingin mencegah data duplikat dimasukkan ke dalam rentang sel, bagian ini memperkenalkan beberapa metode cepat untuk menyelesaikan tugas ini di Excel.
Izinkan hanya nilai unik dengan fungsi Validasi Data
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 Pengaturan 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.
3Sekarang, hanya nilai unik yang dapat dimasukkan, dan pesan peringatan akan muncul jika data duplikat dimasukkan, seperti yang ditunjukkan pada gambar di bawah ini:
Izinkan hanya nilai unik dengan kode VBA
Kode VBA berikut juga dapat membantu Anda untuk mencegah nilai duplikat dimasukkan, lakukan seperti ini:
1Klik kanan tab lembar yang ingin Anda izinkan hanya nilai unik, dan pilih Lihat kode dari menu konteks, di pop-up 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 dan 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 yang ditunjukkan pada gambar di bawah ini:
Izinkan hanya nilai unik dengan fitur praktis
Jika Anda memiliki Kutools untuk Excel, Dengan yang Mencegah Duplikat fitur, Anda dapat dengan cepat mengatur validasi data untuk mencegah duplikat dalam rentang sel hanya dengan beberapa klik.
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. Pesan peringatan akan muncul, memberitahukan Anda bahwa Validasi Data akan dihapus saat menerapkan fitur ini. Klik Yes, dan pada kotak prompt berikutnya, klik OK, seperti yang ditunjukkan pada gambar tangkapan layar di bawah ini:
![]() | ![]() | ![]() |
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:
Kutools untuk Excel - Sempurnakan Excel dengan lebih dari 300 alat penting. Nikmati fitur AI gratis selamanya! Get It Now
4.6 Validasi data hanya mengizinkan huruf besar / huruf kecil / huruf besar saja
Fitur Validasi Data adalah alat yang ampuh yang dapat membantu pengguna menerapkan entri huruf besar, huruf kecil, atau huruf kapital dalam rentang sel. Lakukan 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 Pengaturan 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.
3Sekarang, 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 atau mencegah nilai berdasarkan keberadaannya di daftar lain dapat menjadi tugas yang menantang bagi banyak pengguna. Faktanya, Anda dapat 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 Pengaturan 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, hanya entri yang 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.
Paksa format nomor telepon saja dengan fungsi Validasi Data
Untuk mengizinkan hanya format nomor telepon tertentu yang dimasukkan, ikuti langkah-langkah berikut:
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, pilih lagi dan buka Validasi Data kotak dialog dengan mengklik Data > Validasi Data > Validasi DataPada dialog pop-up, di bawah Pengaturan 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:
Paksa format nomor telepon saja dengan fitur yang berguna
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.
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:
Kutools untuk Excel - Sempurnakan Excel dengan lebih dari 300 alat penting. Nikmati fitur AI gratis selamanya! Get It Now
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.
Paksa format alamat Email saja dengan fungsi Validasi Data
Dengan menggunakan fitur Validasi Data dengan rumus khusus, Anda dapat dengan cepat membuat aturan untuk mencegah alamat email yang tidak valid dimasukkan. Ikuti langkah-langkah berikut:
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 Pengaturan 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 tidak sesuai dengan format alamat email, kotak pesan peringatan akan muncul untuk memberi tahu Anda, lihat tangkapan layar:
Paksa format alamat Email saja dengan fitur praktis
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.
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:
Kutools untuk Excel - Sempurnakan Excel dengan lebih dari 300 alat penting. Nikmati fitur AI gratis selamanya! Get It Now
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.
Paksa format alamat IP saja dengan fungsi Validasi Data
Untuk mengizinkan hanya alamat IP yang dimasukkan dalam rentang sel tertentu, ikuti langkah-langkah berikut:
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 Pengaturan 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 alamat IP yang tidak valid dimasukkan ke dalam sel, kotak pesan peringatan akan muncul, seperti yang ditunjukkan pada gambar di bawah ini:
Paksa format alamat IP saja dengan kode VBA
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 valid yang dapat dimasukkan ke dalam sel yang ditentukan.
Paksa format alamat IP saja dengan fitur mudah
Jika Anda memiliki Kutools untuk Excel diinstal di buku kerja Anda, itu Validasi Alamat IP fitur dapat membantu Anda untuk menyelesaikan tugas ini juga.
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:
Kutools untuk Excel - Sempurnakan Excel dengan lebih dari 300 alat penting. Nikmati fitur AI gratis selamanya! Get It Now
4.11 Validasi data membatasi nilai yang melebihi nilai total
Misalkan, Anda memiliki laporan pengeluaran bulanan, dan total anggaran adalah $18000. Anda perlu memastikan bahwa jumlah total dalam daftar pengeluaran tidak melebihi total yang telah ditetapkan, seperti yang ditunjukkan pada gambar di bawah. Dalam kasus ini, Anda dapat membuat aturan validasi data dengan menggunakan fungsi SUM untuk mencegah jumlah nilai melebihi total yang telah ditetapkan.
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 Pengaturan 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 nilai kurang dari $18000, validasi akan lolos. Jika ada nilai yang menyebabkan total melebihi $18,000, kotak pesan peringatan akan muncul untuk memberi tahu Anda.
4.12 Validasi data membatasi entri sel berdasarkan sel lain
Bila Anda ingin membatasi entri data dalam daftar sel berdasarkan nilai di sel lain, fitur Validasi Data juga dapat membantu menyelesaikan tugas ini. Misalnya, jika sel C1 berisi teks "Ya", rentang A2:A9 mengizinkan entri apa pun. Namun, jika sel C1 berisi teks lain, entri dalam rentang A2:A9 dibatasi, seperti yang ditunjukkan pada gambar 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 Pengaturan 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 Pengaturan 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.
3Sekarang, Anda hanya dapat memasukkan tanggal hari kerja atau akhir pekan di sel yang ditentukan berdasarkan pilihan 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 Pengaturan 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 setelah hari ini yang dapat dimasukkan ke dalam sel. Jika tidak, kotak pesan peringatan akan muncul untuk memberi tahu 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 Pengaturan 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 Pengaturan 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 memasukkan tanggal untuk minggu atau bulan saat ini di sel tertentu, bagian ini memperkenalkan beberapa rumus untuk menyelesaikan tugas ini di Excel.
Izinkan untuk memasukkan tanggal minggu ini
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 Pengaturan 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: =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:
Izinkan untuk memasukkan tanggal bulan 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 Pengaturan 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 .
3Mulai sekarang, hanya tanggal dalam bulan berjalan yang dapat 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:
4Semua sel dengan validasi data kini telah dipilih di lembar kerja saat ini.
Tips:Jika Anda ingin memilih jenis validasi data tertentu, pertama-tama, pilih sel yang berisi validasi data yang diinginkan, lalu masuk ke 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, gunakan metode berikut.
Hapus validasi data dalam rentang yang dipilih dengan fungsi validasi data
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 Pengaturan 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.
Hapus validasi data dalam rentang yang dipilih dengan fitur praktis
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.
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:
3Pada kotak prompt yang muncul, klik OK, dan aturan validasi data akan dihapus sesuai kebutuhan Anda.
Kutools untuk Excel - Sempurnakan Excel dengan lebih dari 300 alat penting. Nikmati fitur AI gratis selamanya! Get It Now
Hapus validasi data dari semua lembar kerja dengan kode VBA
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()
'Updateby Extendoffice
Dim xwsh As Worksheet
For Each xwsh In 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
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Tab Office Membawa 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.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!