Bagaimana di Excel saya bisa memastikan bahwa nomor faktur duplikat tidak dimasukkan dalam kolom Excel tertentu?
Di Excel 97, Anda bisa menggunakan fitur Validasi Data baru untuk melakukan ini. Dalam contoh kami, nomor faktur dimasukkan di kolom A. Berikut adalah cara mengaturnya untuk satu sel:
- Sel berikutnya yang akan dimasuki adalah A9. Klik di sel A9, dan pilih Data> Validasi dari menu.
- Di kotak tarik-turun "Izinkan:", pilih "Khusus"
- Masukkan rumus ini persis seperti yang terlihat:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Klik tab Peringatan Kesalahan di kotak dialog Validasi Data.
- Pastikan bahwa kotak "Show alert" dicentang.
- Untuk Gaya :, pilih Berhenti
- Masukkan Judul "Nilai Tidak Unik"
- Masukkan pesan "Anda harus memasukkan nomor faktur unik".
- Klik "OK"
Anda bisa mengujinya. Masukkan nilai baru, katakan 10001 di sel A9. Tidak masalah. Tapi, coba ulangi sebuah nilai, katakan 10088 dan berikut ini akan muncul:
Hal terakhir yang harus dilakukan adalah menyalin validasi ini dari sel A9 ke sel lain di kolom A.
- Klik di kolom A dan pilih Edit> salin untuk menyalin sel.
- Pilih sejumlah besar sel di kolom A. Mungkin A10: A500.
- Pilih Edit, Tempel Spesial. Dari dialog Tempel Spesial, pilih "Validasi" dan klik OK. Aturan validasi yang Anda masukkan dari sel A9 akan disalin ke semua sel hingga A500.
Jika Anda mengklik di sel A12 dan memilih Validasi Data, Anda akan melihat bahwa Excel mengubah rumus validasi menjadi =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))Hanya itu yang perlu Anda ketahui untuk membuatnya berfungsi. Bagi Anda yang ingin mengetahui lebih lanjut, saya akan menjelaskan dalam bahasa Inggris bagaimana rumus tersebut bekerja.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Kami sedang duduk di sel A9. Kami memberi tahu fungsi Vlookup untuk mengambil nilai sel yang baru saja kami masukkan (A9) dan mencoba menemukan kecocokan di sel yang berkisar dari A $ 1 hingga A8. Argumen berikutnya, 1, memberi tahu Vlookup bahwa ketika kecocokan ditemukan, beri tahu kami data dari kolom pertama. Akhirnya, False di vlookup mengatakan bahwa kami hanya mencari yang sama persis. Berikut adalah trik # 1: Jika VLOOKUP menemukan kecocokan, VLOOKUP akan mengembalikan nilai. Tapi, jika tidak menemukan kecocokan, itu akan mengembalikan nilai khusus "# N / A". Biasanya, nilai # N / A ini adalah hal yang buruk, tetapi dalam kasus ini, kami INGIN # N / A. Jika kami mendapatkan # N / A, maka Anda tahu bahwa entri baru ini unik dan tidak cocok dengan apa pun di atasnya. Cara mudah untuk menguji apakah suatu nilai # N / A adalah dengan menggunakan fungsi ISNA (). Jika sesuatu di dalam ISNA () mengevaluasi ke # N / A, Anda mendapatkan TRUE. Begitu,ketika mereka memasukkan nomor faktur baru dan tidak ditemukan dalam daftar di atas sel, vlookup akan mengembalikan # N / A, yang akan menyebabkan ISNA () menjadi benar.
Sedikit tipuan kedua ada di argumen kedua untuk fungsi Vlookup. Saya berhati-hati dalam menentukan A $ 1: A8. Tanda dolar sebelum 1 memberi tahu Excel bahwa ketika kita menyalin validasi ini ke sel lain, itu harus selalu mulai mencari di sel kolom saat ini. Ini disebut alamat absolut. Saya juga berhati-hati untuk tidak meletakkan tanda dolar sebelum angka 8 di A8. Ini disebut alamat relatif dan memberi tahu Excel bahwa saat kita menyalin alamat ini, alamat tersebut akan berhenti mencari di sel tepat di atas sel saat ini. Kemudian, saat kita menyalin validasi dan melihat validasi untuk sel A12, argumen kedua di vlookup dengan benar menunjukkan A $ 1: A11.
Ada dua masalah dengan solusi ini. Pertama, ini tidak akan berfungsi di Excel 95. Kedua, validasi hanya dilakukan pada sel yang berubah. Jika Anda memasukkan nilai unik di sel A9, lalu kembali ke atas dan mengedit sel A6 menjadi nilai yang sama yang Anda masukkan di A9, logika validasi di A9 tidak akan dipanggil dan Anda akan mendapatkan nilai duplikat di lembar kerja Anda.
Metode kuno yang digunakan di Excel 95 akan mengatasi kedua masalah ini. Dalam metode lama, Anda akan memiliki logika validasi yang berada di kolom B. Untuk mengatur ini, masukkan rumus berikut di sel B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Salin rumus ini dari B9. Tempel di sel B2: B500. Sekarang, saat Anda memasukkan nomor faktur di kolom A, kolom B akan menunjukkan BENAR jika faktur itu unik, dan SALAH jika tidak unik.
