Bagaimana dalam Excel saya dapat memastikan bahawa nombor invois pendua tidak dimasukkan dalam lajur Excel tertentu?
Di Excel 97, anda boleh menggunakan ciri Pengesahan Data baru untuk melakukan ini. Dalam contoh kami, nombor invois dimasukkan di lajur A. Berikut adalah cara menetapkannya untuk satu sel:
- Sel seterusnya yang akan dimasukkan ialah A9. Klik di sel A9, dan pilih Data> Pengesahan dari menu.
- Dalam kotak lungsur "Benarkan:", pilih "Custom"
- Masukkan formula ini dengan tepat bagaimana ia muncul:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Klik tab Makluman Ralat di kotak dialog Pengesahan Data.
- Pastikan bahawa kotak "Tunjukkan amaran" dicentang.
- Untuk Gaya :, pilih Berhenti
- Masukkan Tajuk "Nilai Tidak Unik"
- Masukkan mesej "Anda mesti memasukkan nombor invois unik."
- Klik "OK"
Anda boleh mengujinya. Masukkan nilai baru, katakan 10001 dalam sel A9. Tiada masalah. Tetapi, cubalah mengulang nilai, katakan 10088 dan yang berikut akan muncul:
Perkara terakhir yang perlu dilakukan adalah menyalin pengesahan ini dari sel A9 ke sel lain di lajur A.
- Klik pada lajur A dan pilih Edit> salin untuk menyalin sel.
- Pilih sebilangan besar sel di lajur A. Mungkin A10: A500.
- Pilih Edit, Tampal Khas. Dari dialog Tampal Khas, pilih "Pengesahan" dan klik OK. Peraturan pengesahan yang anda masukkan dari sel A9 akan disalin ke semua sel hingga A500.
Sekiranya anda mengklik sel A12 dan memilih Pengesahan Data, anda akan melihat bahawa Excel mengubah formula pengesahan menjadi =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))Itulah yang perlu anda ketahui untuk membuatnya berfungsi. Bagi anda yang ingin mengetahui lebih lanjut, saya akan menerangkan dalam bahasa Inggeris bagaimana formula berfungsi.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Kami duduk di sel A9. Kami memberitahu fungsi Vlookup untuk mengambil nilai sel yang baru kami masukkan (A9) dan untuk berusaha mencari padanan dalam sel yang berkisar antara A $ 1 hingga A8. Argumen seterusnya, 1, memberitahu Vlookup bahawa ketika pertandingan dijumpai untuk memberitahu kami data dari lajur pertama. Akhirnya, Salah dalam pandangan mengatakan bahawa kita hanya mencari padanan yang tepat. Inilah trik # 1: Sekiranya VLOOKUP menemui padanan, ia akan mengembalikan nilai. Tetapi, jika tidak menemui padanan, ia akan mengembalikan nilai khas "# N / A". Biasanya, nilai # N / A ini adalah perkara buruk, tetapi dalam kes ini, kita INGIN # N / A. Sekiranya kami mendapat # N / A, maka anda tahu bahawa entri baru ini unik dan tidak sepadan dengan apa-apa di atasnya. Cara mudah untuk menguji jika nilai adalah # N / A adalah dengan menggunakan fungsi ISNA (). Sekiranya sesuatu di dalam ISNA () dinilai menjadi # N / A, anda akan mendapat BENAR. Jadi,apabila mereka memasukkan nombor invois baru dan tidak terdapat dalam senarai di atas sel, vlookup akan mengembalikan # N / A, yang akan menyebabkan ISNA () menjadi benar.
Tipuan kedua adalah dalam argumen kedua untuk fungsi Vlookup. Saya berhati-hati untuk menyatakan A $ 1: A8. Tanda dolar sebelum angka 1 memberitahu Excel bahawa apabila kita menyalin pengesahan ini ke sel lain, ia mesti selalu mula dilihat di sel lajur semasa. Ini dipanggil alamat mutlak. Saya juga berhati-hati untuk tidak meletakkan tanda dolar sebelum angka 8 di A8. Ini dipanggil alamat relatif dan memberitahu Excel bahawa apabila kita menyalin alamat ini, alamat tersebut akan berhenti mencari di sel tepat di atas sel semasa. Kemudian, apabila kita menyalin pengesahan dan melihat pengesahan untuk sel A12, argumen kedua dalam vlookup menunjukkan A $ 1: A11 dengan betul.
Terdapat dua masalah dengan penyelesaian ini. Pertama, ia tidak akan berfungsi di Excel 95. Kedua, pengesahan hanya dilakukan pada sel yang berubah. Sekiranya anda memasukkan nilai unik di sel A9, dan kemudian kembali dan edit sel A6 untuk menjadi nilai yang sama dengan yang anda masukkan di A9, logik pengesahan di A9 tidak akan dipanggil dan anda akan berakhir dengan nilai pendua di lembaran kerja anda.
Kaedah kuno yang digunakan dalam Excel 95 akan menangani kedua-dua masalah ini. Dalam kaedah lama, anda akan mempunyai logik pengesahan dalam lajur sementara B. Untuk menyiapkannya, masukkan formula berikut di sel B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Salin formula ini dari B9. Tampalkannya di sel B2: B500. Sekarang, semasa anda memasukkan nombor invois di lajur A, lajur B akan menunjukkan BENAR jika invois itu unik, dan SALAH jika tidak unik.
