Làm cách nào trong Excel để tôi có thể đảm bảo rằng số hóa đơn trùng lặp không được nhập vào một cột Excel cụ thể?
Trong Excel 97, bạn có thể sử dụng tính năng Xác thực Dữ liệu mới để thực hiện việc này. Trong ví dụ của chúng tôi, các số hóa đơn đang được nhập vào cột A. Đây là cách thiết lập nó cho một ô duy nhất:
- Ô tiếp theo được nhập là A9. Bấm vào ô A9 và chọn Dữ liệu> Xác thực từ menu.
- Trong hộp thả xuống "Cho phép:", chọn "Tùy chỉnh"
- Nhập công thức này chính xác cách nó xuất hiện:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Bấm vào tab Cảnh báo Lỗi trong hộp thoại Xác thực Dữ liệu.
- Đảm bảo rằng hộp "Hiển thị cảnh báo" được chọn.
- Đối với Phong cách :, chọn Dừng
- Nhập tiêu đề "Giá trị không duy nhất"
- Nhập thông báo "Bạn phải nhập một số hóa đơn duy nhất."
- Nhấp vào "OK"
Bạn có thể kiểm tra nó ra. Nhập một giá trị mới, giả sử 10001 trong ô A9. Không vấn đề gì. Tuy nhiên, hãy thử lặp lại một giá trị, chẳng hạn như 10088 và thông tin sau sẽ xuất hiện:
Điều cuối cùng cần làm là sao chép xác thực này từ ô A9 sang các ô khác trong cột A.
- Nhấp vào cột A và chọn Chỉnh sửa> sao chép để sao chép ô.
- Chọn một dải ô lớn trong cột A. Có lẽ A10: A500.
- Chọn Chỉnh sửa, Dán Đặc biệt. Từ hộp thoại Dán Đặc biệt, chọn "Xác thực" và nhấp vào OK. Quy tắc xác thực mà bạn đã nhập từ ô A9 sẽ được sao chép vào tất cả các ô xuống A500.
Nếu bạn nhấp vào ô A12 và chọn Xác thực Dữ liệu, bạn sẽ thấy rằng Excel đã thay đổi công thức xác thực thành =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))Đó là tất cả những gì bạn cần biết để làm cho nó hoạt động. Đối với những bạn muốn biết thêm, tôi sẽ giải thích bằng tiếng Anh cách thức hoạt động của công thức.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Chúng tôi đang ngồi trong phòng giam A9. Chúng tôi đang yêu cầu hàm Vlookup lấy giá trị của ô mà chúng tôi vừa nhập (A9) và cố gắng tìm kết quả khớp trong các ô có phạm vi từ A $ 1 đến A8. Đối số tiếp theo, đối số 1, nói với Vlookup rằng khi tìm thấy kết quả khớp sẽ cho chúng ta biết dữ liệu từ cột đầu tiên. Cuối cùng, Sai trong vlookup nói rằng chúng tôi chỉ tìm kiếm các kết quả phù hợp chính xác. Đây là mẹo số 1: Nếu hàm VLOOKUP tìm thấy một kết quả phù hợp, nó sẽ trả về một giá trị. Nhưng, nếu nó không tìm thấy kết quả phù hợp, nó sẽ trả về giá trị đặc biệt của "# N / A". Thông thường, các giá trị # N / A này là những thứ không tốt, nhưng trong trường hợp này, chúng tôi MUỐN # N / A. Nếu chúng tôi nhận được # N / A, thì bạn biết rằng mục nhập mới này là duy nhất và không khớp với bất kỳ điều gì ở trên nó. Một cách dễ dàng để kiểm tra xem giá trị có phải là # N / A hay không là sử dụng hàm ISNA (). Nếu thứ gì đó bên trong ISNA () đánh giá là # N / A, bạn sẽ nhận được TRUE. Vì thế,khi họ nhập số hóa đơn mới và không tìm thấy số này trong danh sách phía trên ô, vlookup sẽ trả về giá trị # N / A, điều này khiến ISNA () là true.
Chút mẹo thứ hai nằm trong đối số thứ hai cho hàm Vlookup. Tôi đã cẩn thận chỉ định A $ 1: A8. Dấu đô la trước số 1 cho Excel biết rằng khi chúng tôi sao chép xác thực này sang các ô khác, nó sẽ luôn bắt đầu tìm kiếm trong ô của cột hiện tại. Đây được gọi là địa chỉ tuyệt đối. Tôi cũng cẩn thận không đặt một ký hiệu đô la trước số 8 trong A8. Đây được gọi là địa chỉ tương đối và cho Excel biết rằng khi chúng ta sao chép địa chỉ này, địa chỉ này sẽ ngừng tìm kiếm trong ô ngay phía trên ô hiện tại. Sau đó, khi chúng tôi sao chép xác thực và xem xét xác thực cho ô A12, đối số thứ hai trong vlookup hiển thị chính xác A $ 1: A11.
Có hai vấn đề với giải pháp này. Đầu tiên, nó sẽ không hoạt động trong Excel 95. Thứ hai, xác thực chỉ được thực hiện trên các ô thay đổi. Nếu bạn nhập một giá trị duy nhất vào ô A9, sau đó sao lưu và chỉnh sửa ô A6 cho giống với giá trị bạn đã nhập trong A9, logic xác thực trong A9 sẽ không được gọi và bạn sẽ có các giá trị trùng lặp trong trang tính của mình.
Phương pháp cũ được sử dụng trong Excel 95 sẽ giải quyết cả hai vấn đề này. Trong phương pháp cũ, bạn sẽ có logic xác thực nằm trong cột tạm thời B. Để thiết lập điều này, hãy nhập công thức sau vào ô B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Sao chép công thức này từ B9. Dán nó vào các ô B2: B500. Bây giờ, khi bạn nhập số hóa đơn vào cột A, cột B sẽ hiển thị TRUE nếu hóa đơn là duy nhất và FALSE nếu nó không phải là duy nhất.
