Xác thực dữ liệu là một tính năng trong Excel được sử dụng để kiểm soát những gì người dùng có thể nhập vào một ô. Ví dụ: bạn có thể sử dụng xác thực dữ liệu để đảm bảo giá trị là số từ 1 đến 6, đảm bảo ngày diễn ra trong 30 ngày tới hoặc đảm bảo mục nhập văn bản ít hơn 25 ký tự
Xác thực dữ liệu có thể chỉ hiển thị một thông báo cho người dùng cho họ biết những gì được phép như hình bên dưới
Xác thực dữ liệu cũng có thể dừng đầu vào không hợp lệ của người dùng. Ví dụ: nếu mã sản phẩm không xác thực được, bạn có thể hiển thị thông báo như thế này
Ngoài ra, bạn có thể sử dụng tính năng xác thực dữ liệu để hiển thị cho người dùng một lựa chọn được xác định trước trong menu thả xuống
Đây có thể là một cách thuận tiện để cung cấp cho người dùng chính xác các giá trị đáp ứng yêu cầu
Kiểm soát xác thực dữ liệu
Xác thực dữ liệu được triển khai thông qua các quy tắc được xác định trong giao diện người dùng của Excel trên tab Dữ liệu của dải băng
giới hạn quan trọng
Điều quan trọng là phải hiểu rằng xác thực dữ liệu có thể dễ dàng bị đánh bại. Nếu người dùng sao chép dữ liệu từ ô không có xác thực sang ô có xác thực dữ liệu, thì xác thực sẽ bị hủy [hoặc thay thế]. Xác thực dữ liệu là một cách hay để cho người dùng biết những gì được phép hoặc mong đợi, nhưng đó không phải là cách hoàn hảo để đảm bảo đầu vào.
Xác định quy tắc xác thực dữ liệu
Xác thực dữ liệu được xác định trong một cửa sổ có 3 tab. Cài đặt, Thông báo đầu vào và Cảnh báo lỗi
Tab cài đặt là nơi bạn nhập tiêu chí xác thực. Có một số quy tắc xác thực được tích hợp sẵn với nhiều tùy chọn khác nhau hoặc bạn có thể chọn Tùy chỉnh và sử dụng công thức của riêng mình để xác thực đầu vào như bên dưới
Tab Thông báo đầu vào xác định một thông báo sẽ hiển thị khi một ô có quy tắc xác thực được chọn. Thông báo đầu vào này là hoàn toàn tùy chọn. Nếu không có thông báo đầu vào nào được đặt, thì sẽ không có thông báo nào xuất hiện khi người dùng chọn một ô có áp dụng xác thực dữ liệu. Thông báo đầu vào không ảnh hưởng đến những gì người dùng có thể nhập — nó chỉ hiển thị một thông báo để cho người dùng biết những gì được phép hoặc mong đợi.
Tab Cảnh báo Lỗi kiểm soát cách xác thực được thực thi. Ví dụ: khi kiểu được đặt thành "Dừng", dữ liệu không hợp lệ sẽ kích hoạt cửa sổ có thông báo và đầu vào không được phép.
Người dùng nhìn thấy một thông báo như thế này
Khi kiểu được đặt thành Thông tin hoặc Cảnh báo, một biểu tượng khác sẽ hiển thị cùng với thông báo tùy chỉnh nhưng người dùng có thể bỏ qua thông báo và nhập các giá trị không vượt qua xác thực. Bảng dưới đây tóm tắt hành vi cho từng tùy chọn cảnh báo lỗi
Cảnh báo StyleBehaviorStopNgăn người dùng nhập dữ liệu không hợp lệ vào một ô. Người dùng có thể thử lại nhưng phải nhập giá trị vượt qua quá trình xác thực dữ liệu. Cửa sổ Cảnh báo dừng có hai tùy chọn. Thử lại và Hủy bỏ. Cảnh báoCảnh báo người dùng rằng dữ liệu không hợp lệ. Cảnh báo không làm gì để ngăn chặn dữ liệu không hợp lệ. Cửa sổ Cảnh báo cảnh báo có ba tùy chọn. Có [để chấp nhận dữ liệu không hợp lệ], Không [để chỉnh sửa dữ liệu không hợp lệ] và Hủy [để xóa dữ liệu không hợp lệ]. Thông tinThông báo cho người dùng rằng dữ liệu không hợp lệ. Thông báo này không làm gì để ngăn chặn dữ liệu không hợp lệ. Cửa sổ Cảnh báo thông tin có 2 lựa chọn. OK để chấp nhận dữ liệu không hợp lệ và Hủy để xóa dữ liệu đóTùy chọn xác thực dữ liệu
Khi quy tắc xác thực dữ liệu được tạo, có tám tùy chọn khả dụng để xác thực đầu vào của người dùng
Bất kỳ giá trị nào - không có xác thực nào được thực hiện. Ghi chú. nếu xác thực dữ liệu trước đó được áp dụng với Thông báo đầu vào đã đặt, thông báo sẽ vẫn hiển thị khi ô được chọn, ngay cả khi Giá trị bất kỳ được chọn
Số nguyên - chỉ các số nguyên mới được phép. Khi tùy chọn số nguyên được chọn, các tùy chọn khác sẽ khả dụng để giới hạn đầu vào hơn nữa. Ví dụ: bạn có thể yêu cầu một số nguyên từ 1 đến 10
Thập phân - hoạt động giống như tùy chọn số nguyên nhưng cho phép giá trị thập phân. Ví dụ: với tùy chọn Số thập phân được định cấu hình để cho phép các giá trị từ 0 đến 3, các giá trị như. 5, 2. 5 và 3. 1 đều được phép
Danh sách - chỉ các giá trị từ danh sách được xác định trước mới được phép. Các giá trị được hiển thị cho người dùng dưới dạng điều khiển menu thả xuống. Các giá trị được phép có thể được mã hóa cứng trực tiếp vào tab Cài đặt hoặc được chỉ định dưới dạng một phạm vi trên trang tính
Ngày - chỉ ngày được phép. Ví dụ: bạn có thể yêu cầu một ngày từ ngày 1 tháng 1 năm 2018 đến ngày 31 tháng 12 năm 2021 hoặc ngày sau ngày 1 tháng 6 năm 2018
Thời gian - chỉ thời gian được phép. Ví dụ: bạn có thể yêu cầu thời gian từ 9. 00 giờ sáng và 5. 00 PM hoặc chỉ cho phép thời gian sau 12 giờ. 00 giờ chiều
Độ dài văn bản - xác thực thông tin nhập vào dựa trên số ký tự hoặc chữ số. Ví dụ: bạn có thể yêu cầu mã có 5 chữ số
Tùy chỉnh - xác thực đầu vào của người dùng bằng công thức tùy chỉnh. Nói cách khác, bạn có thể viết công thức của riêng mình để xác thực đầu vào. Công thức tùy chỉnh mở rộng đáng kể các tùy chọn để xác thực dữ liệu. Ví dụ: bạn có thể sử dụng công thức để đảm bảo giá trị là chữ hoa, giá trị chứa "xyz" hoặc ngày là ngày trong tuần trong 45 ngày tới
Tab cài đặt cũng bao gồm hai hộp kiểm
Bỏ qua ô trống - yêu cầu Excel không xác thực các ô không chứa giá trị. Trong thực tế, cài đặt này dường như chỉ ảnh hưởng đến lệnh "khoanh tròn dữ liệu không hợp lệ". Khi được bật, các ô trống sẽ không được khoanh tròn ngay cả khi chúng không xác thực được
Áp dụng những thay đổi này cho các ô khác có cùng cài đặt - cài đặt này sẽ cập nhật xác thực được áp dụng cho các ô khác khi nó khớp với xác thực [gốc] của [các] ô đang được chỉnh sửa
Ghi chú. Bạn cũng có thể chọn thủ công tất cả các ô có áp dụng xác thực dữ liệu bằng cách sử dụng Chuyển đến + Đặc biệt, như được giải thích bên dưới
Menu thả xuống đơn giản
Bạn có thể cung cấp menu tùy chọn thả xuống bằng cách mã hóa cứng các giá trị vào hộp cài đặt hoặc chọn một phạm vi trên trang tính. Ví dụ: để hạn chế mục nhập cho các hành động "MUA", "GIỮ" hoặc "BÁN", bạn có thể nhập các giá trị này được phân tách bằng dấu phẩy như bên dưới
Khi được áp dụng cho một ô trong trang tính, menu thả xuống sẽ hoạt động như thế này
Một cách khác để cung cấp giá trị cho menu thả xuống là sử dụng tham chiếu trang tính. Ví dụ, với kích thước [i. e. nhỏ, vừa, v.v. ] trong khoảng F3. F6, bạn có thể cung cấp phạm vi này trực tiếp bên trong cửa sổ cài đặt xác thực dữ liệu
Lưu ý rằng dải ô được nhập dưới dạng địa chỉ tuyệt đối để ngăn không cho dải ô thay đổi khi xác thực dữ liệu được áp dụng cho các ô khác
Mẹo. Nhấp vào biểu tượng mũi tên nhỏ ở ngoài cùng bên phải của trường nguồn để thực hiện lựa chọn trực tiếp trên trang tính, do đó bạn không phải nhập phạm vi theo cách thủ công
Bạn cũng có thể sử dụng phạm vi đã đặt tên để chỉ định giá trị. Ví dụ: với dải ô được đặt tên là "kích thước" cho F3. F7, bạn có thể nhập tên trực tiếp vào cửa sổ, bắt đầu bằng dấu bằng
Phạm vi được đặt tên là tự động tuyệt đối, vì vậy chúng sẽ không thay đổi khi xác thực dữ liệu được áp dụng cho các ô khác nhau. Nếu các phạm vi được đặt tên là mới đối với bạn, thì trang này có một cái nhìn tổng quan tốt và một số mẹo liên quan
Bạn cũng có thể tạo danh sách thả xuống phụ thuộc bằng công thức tùy chỉnh
Mẹo - nếu bạn sử dụng bảng cho các giá trị thả xuống, Excel sẽ tự động mở rộng hoặc thu hẹp bảng khi các giá trị thả xuống được thêm hoặc xóa. Nói cách khác, Excel sẽ tự động giữ danh sách thả xuống đồng bộ với các giá trị trong bảng khi các giá trị được thay đổi, thêm hoặc xóa. Nếu mới sử dụng Bảng Excel, bạn có thể xem bản minh họa trong video này về Phím tắt bảng
Xác thực dữ liệu bằng công thức tùy chỉnh
Công thức xác thực dữ liệu phải là công thức logic trả về TRUE khi đầu vào hợp lệ và FALSE khi đầu vào không hợp lệ. Ví dụ: để cho phép nhập bất kỳ số nào vào ô A1, bạn có thể sử dụng hàm ISNUMBER trong công thức như sau
=ISNUMBER[A1]
Nếu người dùng nhập một giá trị như 10 trong A1, ISNUMBER trả về TRUE và xác thực dữ liệu thành công. Nếu họ nhập một giá trị như "quả táo" trong A1, ISNUMBER sẽ trả về FALSE và quá trình xác thực dữ liệu không thành công
Để bật xác thực dữ liệu bằng công thức, hãy chọn "Tùy chỉnh" trong tab cài đặt, sau đó nhập công thức vào thanh công thức bắt đầu bằng dấu bằng [=] như bình thường
Công thức khắc phục sự cố
Excel bỏ qua các công thức xác thực dữ liệu trả về lỗi. Nếu một công thức không hoạt động và bạn không thể hiểu tại sao, hãy thiết lập các công thức giả để đảm bảo công thức hoạt động như bạn mong đợi. Công thức giả chỉ đơn giản là các công thức xác thực dữ liệu được nhập trực tiếp trên trang tính để bạn có thể dễ dàng xem kết quả trả về của chúng. Màn hình bên dưới hiển thị một ví dụ
Sau khi bạn có được công thức giả hoạt động như ý muốn, chỉ cần sao chép và dán nó vào khu vực công thức xác thực dữ liệu
Nếu ý tưởng công thức giả này làm bạn bối rối, hãy xem video này, video này cho biết cách sử dụng công thức giả để hoàn thiện các công thức định dạng có điều kiện. Khái niệm này hoàn toàn giống nhau
Ví dụ về công thức xác thực dữ liệu
Khả năng cho các công thức tùy chỉnh xác thực dữ liệu hầu như không giới hạn. Dưới đây là một vài ví dụ để cung cấp cho bạn một số cảm hứng
Để chỉ cho phép 5 giá trị ký tự bắt đầu bằng "z", bạn có thể sử dụng
=AND[LEFT[A1]="z",LEN[A1]=5]
Công thức này chỉ trả về TRUE khi mã có 5 chữ số và bắt đầu bằng "z". Hai giá trị được khoanh tròn trả về FALSE với công thức này.
Để chỉ cho phép một ngày trong vòng 30 ngày kể từ ngày hôm nay
=AND[A1>TODAY[],A1