Xác thực dữ liệu của Excel là một công cụ tuyệt vời để đảm bảo rằng chỉ những dữ liệu chấp nhận được mới được nhập vào một ô. Điều này đặc biệt hữu ích khi người dùng không phải là người đã tạo trang tính sẽ điền dữ liệu. Công cụ xác thực dữ liệu cho phép bạn thiết lập các quy tắc chi phối các loại dữ liệu được phép, hiển thị thông báo đầu vào thông báo cho người dùng về các quy tắc và hiển thị thông báo lỗi nếu quy tắc bị vi phạm. Rõ ràng, điều này có thể làm giảm lỗi trong trang tính
Loại xác thực dữ liệu mạnh mẽ nhất là loại Tùy chỉnh, dựa trên các công thức. Trong bài này tôi sẽ trình bày một phương pháp để loại bỏ các ký tự không được phép từ dữ liệu được nhập vào một phạm vi hợp lệ. Tôi đã đăng giải pháp này trên Yahoo. Câu trả lời để đáp lại một người hỏi đã nói
Người dùng cuối sẽ nhập văn bản dạng tự do vào tệp Excel mà sau đó tôi sẽ cần tải lên hệ thống của mình. Tôi muốn có thể hạn chế các ký tự họ có thể nhập. Tôi không muốn họ có thể nhập các ký tự đặc biệt [&. , ; . Tôi thực sự không muốn chỉ sử dụng Thông báo đầu vào để thông báo cho người dùng rằng họ không thể nhập x, y, z;
Hãy tưởng tượng rằng bạn tạo một bảng tính sẽ được phân phối cho các bộ phận khác nhau để điền vào các văn bản và/hoặc số quan trọng. Một trong những điều đầu tiên mà bạn có thể sẽ làm là sử dụng tính năng bảo vệ trang tính để ngăn người dùng nhập dữ liệu vào các ô mà họ không nên nhập. Bạn có thể làm điều này bằng cách mở khóa các ô nơi dữ liệu sẽ được nhập và sau đó bảo vệ trang tính. Điều này hoạt động rất tốt, nhưng nó không kiểm soát những gì người dùng có thể nhập vào các ô không được bảo vệ. Đây là nơi xác thực dữ liệu [và một lượng nhỏ VBA] đến để giải cứu
Giả sử rằng người dùng sẽ nhập dữ liệu vào A2. A30. Có lẽ, sẽ có những công thức ở nơi khác dựa trên dữ liệu này. Để xác thực dữ liệu, chúng tôi cần tìm kiếm trong văn bản/số được nhập để kiểm tra các ký tự không hợp lệ. Vì vậy, trước tiên chúng ta cần tạo một danh sách các ký tự không hợp lệ. Trong C1, nhập “Không được phép” và sau đó trong C2. C6 nhập. %, &, /,. , và #. Ghi chú. Nếu bạn muốn không cho phép dấu hoa thị hoặc dấu chấm hỏi, bạn phải nhập chúng với dấu ngã ở phía trước [e. g. , ~*]
Tiếp theo, chúng ta cần có khả năng xác định động ô hiện đang hoạt động trong trang tính [nơi người dùng có thể nhập dữ liệu]. Thật không may, tôi không biết cách nào để làm điều này mà không cần sử dụng VBA, vì vậy chúng tôi sẽ viết mã một chút. VBA có một sự kiện được gọi là Worksheet_SelectionChange được kích hoạt mỗi khi ô hiện hoạt được thay đổi trên trang tính. Ngoài ra còn có một sự kiện được gọi là Workbook_SheetSelectionChange kích hoạt mỗi khi lựa chọn thay đổi trong bất kỳ trang tính nào trong toàn bộ sổ làm việc, nhưng chúng tôi sẽ sử dụng phiên bản trang tính để giảm thiểu chi phí. Chúng tôi chỉ cần một dòng mã để nhập địa chỉ của ô đang hoạt động vào trang tính của chúng tôi, nơi chúng tôi có thể làm việc với nó
Private Sub Worksheet_SelectionChange[ByVal Target As Range]
Range[“f1”].Value = Target.Address
End Sub
Điều đó sẽ đặt địa chỉ của ô mới được kích hoạt dưới dạng chuỗi vào ô F1. Bây giờ chúng ta có thể sử dụng địa chỉ đó để nạp vào hàm Search[]. Hàm Search[] sẽ xem qua một chuỗi và xác định vị trí xuất hiện đầu tiên của một ký tự [hoặc chuỗi]. Vì vậy, điều đó sẽ cho chúng ta biết vị trí của ký tự, nhưng chỉ khi ký tự đó nằm trong chuỗi. Điều đó sẽ không hiệu quả. Tuy nhiên, hàm Search[] sẽ trả về lỗi #VALUE nếu không tìm thấy ký tự. Bây giờ, điều đó hữu ích. Chúng ta chỉ cần biết liệu một ký tự cụ thể có trong chuỗi hay không [1 hoặc 0, có hoặc không]. Vì vậy, chúng ta có thể sử dụng câu lệnh IF[], kết hợp với hàm IsError[] để tìm ra điều này. Nhập công thức sau vào D2
=IF[ISERROR]],0,1]
Nếu có lỗi [ký tự KHÔNG có trong ô], thì điều này sẽ trả về 0. Nếu ký tự không được phép nằm trong chuỗi, nó sẽ trả về 1
Lưu ý rằng tôi đang sử dụng hàm Indirect[] mà tôi đã sử dụng trước đây [xem bài đăng này]. Trong trường hợp này, hàm Gián tiếp sẽ lấy địa chỉ ô đang hoạt động từ F1 và trả về chuỗi chứa trong ô đang hoạt động. Chuỗi đó sau đó sẽ được đưa vào hàm Search[] nơi nó sẽ được xử lý theo công thức của chúng tôi
Bây giờ, điều đó sẽ cho chúng tôi biết nếu ký tự không được phép đầu tiên được nhập. Tuy nhiên, chúng tôi có toàn bộ danh sách để kiểm tra, vì vậy hãy sao chép công thức từ D2 sang D3. D6. Bảng tính bây giờ sẽ trông giống như trong hình bên dưới
Cuối cùng, chúng ta cần thiết lập quy tắc xác thực dữ liệu. Chọn A2. A30 nơi quy tắc xác thực dữ liệu sẽ được áp dụng. Sau đó, trong Excel 2007, chuyển đến tab Dữ liệu và chọn Xác thực dữ liệu từ nhóm Công cụ dữ liệu. Trong menu, chọn Xác thực dữ liệu. [Trong Excel 2003 trở về trước, chọn Dữ liệu rồi chọn Xác thực từ menu. ] Cuối cùng, trong hộp thoại, chọn Tùy chỉnh từ danh sách thả xuống Cho phép. Công thức mà chúng ta nhập cần dẫn đến câu trả lời Đúng/Sai. Nhớ lại rằng trước đây chúng ta đã thiết lập các công thức [trong D2. D6] bằng 0 nếu ký tự không được phép không có trong ô và 1 nếu có. Vì vậy, nếu chúng ta cộng kết quả của các công thức đó và nhận được 0 thì chúng ta biết rằng người dùng đã không nhập một ký tự không được phép. Nếu tổng lớn hơn 0, thì họ đã làm. Do đó, chúng tôi chỉ muốn cho phép nhập nếu tổng của D2. D6 bằng không. công thức là
=SUM=0
Điều này sẽ chỉ đúng nếu không có ký tự không được phép nhập. Lưu ý rằng chúng ta cần sử dụng tham chiếu ô tuyệt đối để có thể áp dụng quy tắc xác thực dữ liệu này cho toàn bộ phạm vi ô. Hộp thoại Xác thực dữ liệu sẽ giống như hình bên dưới
Lưu ý rằng có hai tab bổ sung trong hộp thoại Xác thực dữ liệu. Trong các tab này, bạn có thể xác định "thông báo đầu vào" cho người dùng biết nội dung họ nên nhập và "thông báo lỗi" sẽ bật lên nếu họ vi phạm quy tắc. Cá nhân mình thấy phần nhập tin nhắn có thể hơi khó chịu và thường để trống. Tuy nhiên, thông báo lỗi có thể rất hữu ích đối với những người dùng có thể nhầm lẫn về lý do tại sao mục nhập của họ bị từ chối
Nếu bạn đã làm theo, hãy thử nhập một chuỗi chứa ký tự không được phép vào A2. Bạn sẽ nhận được một thông báo lỗi. Nhấp vào nút Thử lại và nhập một chuỗi sạch. Nó nên được nhập mà không có bất kỳ vấn đề
Bạn có thể tải xuống một bảng tính ví dụ. Lưu ý rằng đây là tệp Excel 2007 có hỗ trợ macro, vì vậy bạn sẽ cần cho phép macro chạy nếu bạn nhận được thông báo cảnh báo. Nếu bạn đang sử dụng Excel 2003 hoặc cũ hơn, bạn sẽ cần gói tương thích miễn phí để mở tệp