Buzz
Kiểm Tra Dữ Liệu Trong Excel là một tính năng quan trọng mà bạn nên biết. Dưới đây là hướng dẫn cách áp dụng Kiểm Tra Dữ Liệu Trong Excel.
Khi nói đến phần mềm bảng tính, hầu hết mọi người nghĩ ngay đến Microsoft Excel. Mặc dù có nhiều lựa chọn thay thế như Google Sheets, WPS Office Spreadsheets, Zoho Sheets..., nhưng Microsoft Excel vẫn là ưu tiên hàng đầu trong doanh nghiệp, công ty, tập đoàn lớn. Vì sao vậy? Vì Excel cung cấp nhiều tính năng tuyệt vời, dễ sử dụng mà các chương trình khác không có.
Trong Microsoft Excel, bạn có thể tìm thấy nhiều hàm tính toán, ngày tháng, lượng giác, logic... Tất cả giúp việc xử lý thông tin, đặc biệt là với bảng dữ liệu lớn, trở nên dễ dàng, chính xác và nhanh chóng hơn bao giờ hết. Data Validation là một trong những tính năng hỗ trợ xử lý dữ liệu phổ biến nhất trong Excel.
Data validation trong Excel giúp bạn xác định dữ liệu trong ô một cách chính xác. Điều này giúp giảm thiểu sự nhầm lẫn trong các tài liệu, biểu mẫu, bảng tính... Dưới đây là cách áp dụng Data Validation trong Excel.
Khi nào nên sử dụng Data Validation?
Xác thực dữ liệu trong Excel rất quan trọng khi bạn muốn chia sẻ một workbook với người khác và muốn đảm bảo dữ liệu được nhập vào là chính xác, nhất quán. Ngoài ra, Data Validation Excel cũng hữu ích trong các trường hợp sau:
- Hạn chế nhập liệu trong danh sách dữ liệu được xác định trước. Ví dụ, giới hạn lựa chọn bộ phận như kế toán, hành chính, nhân sự...
- Hạn chế giá trị nằm ngoài một phạm vi cụ thể. Ví dụ, bạn có thể chỉ định tỷ lệ tăng lương hàng năm tối đa là 3% hoặc chỉ cho phép nhập số nguyên từ 1 đến 100.
- Hạn chế ngày nhập nằm ngoài khoảng thời gian xác định. Ví dụ, trong đơn xin nghỉ, bạn có thể thiết lập không cho phép chọn ngày nghỉ trước ngày hiện tại.
- Hạn chế thời gian nhập ngoài khung giờ cụ thể. Ví dụ, bạn có thể lên lịch hợp từ 8 giờ sáng đến 5 giờ chiều.
- Hạn chế số lượng ký tự trong ô văn bản. Ví dụ, bạn có thể giới hạn văn bản trong một ô chỉ tối đa 10 ký tự.
Cách áp dụng Data Validation trong Excel trên Windows
1. Lựa chọn ô bạn muốn đặt quy tắc cho.
2. Nhấp chuột vào Data > Data Validation.
3. Trên tab Cài đặt, trong mục Cho phép, chọn một trong các tùy chọn sau:
- Số nguyên - Hạn chế ô chỉ chấp nhận các giá trị số nguyên.
- Thập phân - Hạn chế ô chỉ chấp nhận số có phần thập phân.
- Danh sách - Chọn giá trị từ danh sách thả xuống.
- Ngày - Hạn chế ô chỉ chấp nhận giá trị ngày.
- Thời gian - Hạn chế ô chỉ chấp nhận giá trị thời gian.
- Độ dài văn bản - Hạn chế độ dài của văn bản.
- Tùy chỉnh – Tùy chỉnh theo công thức của bạn.
4. Trong phần Dữ liệu, chọn một điều kiện:
- trong khoảng - Ở giữa
- không trong khoảng - Không nằm trong khoảng
- bằng - Bằng
- không bằng - Không bằng
- lớn hơn - Lớn hơn
- nhỏ hơn - Nhỏ hơn
- lớn hơn hoặc bằng - Lớn hơn hoặc bằng
- nhỏ hơn hoặc bằng - Nhỏ hơn hoặc bằng
5. Trên tab Cài đặt, trong phần Cho phép, chọn một trong các tùy chọn có sẵn.
6. Đặt các giá trị được yêu cầu khác, dựa trên những gì bạn chọn cho Cho phép và Dữ liệu. Ví dụ, nếu chọn trong khoảng, sau đó chọn các giá trị cho Tối thiểu: và Tối đa: cho các ô đó.
7. Chọn ô Bỏ qua ô trống nếu muốn bỏ qua các ô trống.
8. Muốn thêm tiêu đề - Tiêu đề và thông báo cho quy tắc của bạn, chọn tab Thông báo nhập, sau đó nhập tiêu đề và thông báo mong muốn.
9. Đánh dấu Hiện thông báo nhập khi ô được chọn để hiển thị thông báo khi người dùng chọn hoặc trỏ chuột qua các ô lựa chọn.
10. Chọn OK.
Nếu người dùng nhập giá trị không hợp lệ, một cửa sổ pop-up sẽ xuất hiện với thông báo “Giá trị này không phù hợp với các ràng buộc xác thực dữ liệu cho ô này.”
Cách sử dụng Data Validation trong Excel trên macOS
Nếu đang tạo một sheet yêu cầu người dùng nhập dữ liệu, bạn có thể hạn chế mục nhập vào một phạm vi ngày hoặc số cụ thể, hoặc đảm bảo chỉ số nguyên dương được nhập vào. Excel có thể giới hạn mục nhập dữ liệu cho các ô nhất định bằng cách dùng Xác thực dữ liệu, nhắc người dùng nhập dữ liệu hợp lệ khi chọn một ô và hiện thông báo lỗi lúc họ nhập sai dữ liệu.
Office 2013 trở lên
Giới hạn nhập dữ liệu
1. Chọn ô muốn hạn chế nhập dữ liệu.
2. Trên tab Dữ liệu, click Xác thực dữ liệu > Xác thực dữ liệu.
3. Trong hộp Cho phép, chọn loại dữ liệu bạn muốn cho phép nhập, điền vào trong tiêu chí và các giá trị hạn chế.
Nhắc người dùng các mục nhập hợp lệ
Khi người dùng click vào một ô có các yêu cầu nhập dữ liệu, bạn có thể hiện thông báo giải thích dữ liệu như thế nào là hợp lệ.
1. Chọn ô muốn nhắc người dùng các mục nhập dữ liệu hợp lệ.
2. Trên tab Dữ liệu, click Xác thực dữ liệu > Xác thực dữ liệu.
3. Trên tab Tin nhắn đầu vào, tích ô Hiển thị tin nhắn khi ô được chọn.
4. Trong hộp Tiêu đề, gõ tiêu đề thông báo.
5. Trong hộp Tin nhắn đầu vào, gõ thông báo bạn muốn hiển thị.
Hiện thông báo lỗi khi nhập dữ liệu không hợp lệ
Nếu có các hạn chế dữ liệu tại chỗ và người dùng nhập dữ liệu không hợp lệ vào một ô, bạn có thể hiện thông báo giải thích lỗi đó.
1. Chọn những ô mà bạn muốn hiển thị thông báo lỗi.
2. Trên tab Dữ liệu, click vào Giới hạn dữ liệu > Giới hạn dữ liệu.
3. Trên tab
4. Trong hộp Tin nhắn lỗi, nhập thông báo bạn muốn hiển thị nếu có ai đó nhập dữ liệu không hợp lệ.
5. Thực hiện một trong những thao tác sau:
| Tác vụ | Click lựa chọn sau trên menu pop-up Style |
| Yêu cầu người dùng sửa lỗi trước khi xử lý | Stop |
| Cảnh báo người dùng dữ liệu không hợp lệ, yêu cầu họ chọn Yes hoặc No nếu muốn quay lại hoặc tiếp tục | Warning |
| Cảnh báo người dùng dữ liệu không hợp lệ, nhưng cho phép họ tiếp tục sau khi bỏ qua cảnh báo | Important |
Office 2011
Giới hạn nhập dữ liệu
1. Chọn ô mà bạn muốn giới hạn việc nhập dữ liệu.
2. Trên tab Dữ liệu, trong Công cụ, click vào Xác thực.
3. Trong menu pop-up Cho phép, chọn loại dữ liệu mà bạn muốn cho phép.
4. Trên menu Dữ liệu, chọn loại tiêu chí giới hạn mong muốn và nhập các giá trị hạn chế tương ứng.
Thông báo cho người dùng về các mục nhập hợp lệ
Khi người dùng nhấp vào một ô chứa yêu cầu nhập dữ liệu, bạn có thể hiện thông báo giải thích về dữ liệu hợp lệ.
1. Chọn ô mà bạn muốn nhắc người dùng nhập dữ liệu hợp lệ.
2. Trên tab Data, trong Công cụ, nhấp vào Xác nhận.
3. Trên tab Tin nhắn nhập, chọn ô Hiện thông báo khi ô được chọn.
4. Trong ô Tiêu đề, nhập tiêu đề thông báo của bạn.
5. Trong ô Tin nhắn nhập, nhập thông báo bạn muốn hiển thị.
Hiển thị thông báo lỗi khi nhập dữ liệu không hợp lệ
Nếu người dùng nhập dữ liệu không hợp lệ vào ô đã có hạn chế dữ liệu tại chỗ, bạn có thể hiển thị thông báo lỗi như sau:
1. Chọn các ô muốn hiển thị thông báo lỗi.
2. Trên tab Data, trong Công cụ, nhấp vào Xác nhận.
3. Trên tab Thông báo lỗi, trong ô Tiêu đề, nhập tiêu đề thông báo.
4. Trong ô Thông báo lỗi, nhập nội dung thông báo muốn hiển thị.
5. Chọn một trong những lựa chọn sau trong menu pop-up Style
- Stop - Yêu cầu người dùng chỉnh sửa lỗi trước khi tiếp tục.
- Warning - Cảnh báo người dùng về dữ liệu không hợp lệ, chọn Có hoặc Không để tiếp tục hoặc quay lại.
- Important - Cảnh báo về dữ liệu không hợp lệ nhưng cho phép tiếp tục sau khi bỏ qua cảnh báo.
Cách áp dụng Xác thực Dữ liệu trong Excel trên Web
Thêm xác thực dữ liệu vào một ô hoặc một phạm vi
- Chọn một hoặc nhiều ô cần xác thực.
- Trên tab Data, trong nhóm Data Tools, nhấp Data Validation.
- Trên tab Settings > Allow, chọn List.
- Trong box Source, nhập các giá trị được liệt kê, phân tách bằng dấu phẩy. Ví dụ, nhập Low, Average, High.
- Chắc chắn đã chọn In-cell dropdown. Nếu không, bạn sẽ không thể thấy mũi tên thả xuống bên cạnh ô đó.
- Chọn hoặc bỏ tích ô Ignore blank để xác định cách xử lý các giá trị trống.
- Kiểm tra xác thực dữ liệu để đảm bảo hoạt động đúng. Hãy thử nhập cả dữ liệu hợp lệ và không hợp lệ vào các ô, từ đó bạn sẽ biết liệu các thiết lập đã hoạt động như dự định và thông báo đã xuất hiện như mong muốn hay không.
Thêm các loại kiểu xác thực dữ liệu khác
Dưới đây là bảng mô tả các loại xác thực dữ liệu khác và cách thêm chúng vào bảng tính.
| Nhiệm vụ | Các bước thực hiện |
| Hạn chế mục nhập dữ liệu ở số nguyên nằm trong giới hạn |
Bạn cũng có thể nhập công thức trả về giá trị số. Ví dụ: Bạn đang xác thực dữ liệu ở ô F1. Đặt giới hạn khấu trừ tối thiểu bằng 2 lần số con trong ô đó, chọn greater than or equal to trong box Data > nhập công thức: =2*F1 vào box Minimum. |
| Hạn chế mục nhập dữ liệu ở phân số trong giới hạn |
Bạn có thể nhập công thức trả về giá trị số. Ví dụ: Đặt giới hạn tối đa cho tiền thưởng và hoa hồng là 6% lương của nhân viên bán hàng trong ô E1, chọn less than or equal to trong Data > nhập công thức =E1*6% vào box Maximum. Lưu ý: Để cho phép người dùng nhập phần trăm, chọn Decimal trong Allow, chọn loại giới hạn bạn muốn ở box Data, nhập giá trị tối thiểu, tối đa hoặc cụ thể dưới dạng số thập phân (ví dụ: .2), rồi hiện ô xác thực dữ liệu ở dạng phần trăm bằng cách chọn ô đó, rồi click Percent Style trong nhóm Number ở tab Home. |
| Hạn chế mục nhập dữ liệu ở ngày trong phạm vi ngày |
Bạn cũng có thể nhập công thức trả về ngày. Ví dụ: Đặt khung thời gian giữa ngày hôm nay và 3 ngày sau đó, chọn between trong Data, nhập =TODAY() vào box Start day, nhập =TODAY()+3 vào End date. |
| Hạn chế mục nhập dữ liệu ở thời gian trong một khung giờ |
Ví dụ, thiết lập ô E2 ở thời gian bắt đầu (8:00 AM) và ô F2 với thời gian kết thúc (5:00 PM). Bạn muốn giới hạn thời gian họp giữa khung giờ này, chọn between trong box Data, nhập E2 ở box Start time, nhập =F2 ở box End time. |
| Hạn chế mục nhập dữ liệu văn bản ở độ dài lựa chọn |
|
| Tính toán giới hạn được phép dựa trên nội dung của ô khác. |
Ví dụ, chỉ cho phép các mục dành cho tài khoản nếu kết quả không vượt quá ngân sách trong ô E1, chọn Allow >Whole number, Data, less than or equal to, và Maximum >= =E1. |
Ví dụ về các công thức trong xác thực dữ liệu Excel
=AND(LEFT(C2,3)='ID-',LEN(C2)>9)
=ISTEXT(D2)
=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)
=COUNTIF($A$2:$A$10,A2)=1
Chú ý: Bạn cần nhập công thức xác thực dữ liệu cho ô A2 trước, sau đó sao chép A2 sang A3:A10 để đối số thứ hai trong COUNTIF khớp với ô hiện tại. Cụ thể, phần A2)= sẽ thay đổi thành A3)=1, A4)=1...
=ISNUMBER(FIND('@',B4))
