Data Validation trong Excel là gì? Hướng dẫn toàn diện để thêm, chỉnh sửa và xóa các quy tắc Data Validation trong Excel

Hiểu và triển khai các nguyên tắc data validation là điều thiết yếu đối với cả nhà phân tích dữ liệu mới vào nghề lẫn các chuyên gia dày dạn. Trong hướng dẫn này, chúng ta sẽ tìm hiểu cách thực hiện data validation trong Excel. Chúng ta sẽ đề cập đến các kỹ thuật data validation khác nhau và minh họa cách thêm, chỉnh sửa, cũng như xóa các quy tắc data validation.

Nếu bạn muốn ôn lại các kiến thức cơ bản về Excel trước khi bắt đầu, bao gồm các công thức chính, toán tử, hàm toán học và nhiều nội dung khác, hãy xem Cheat Sheet Công Thức Excel của chúng tôi.

Data Validation trong Excel là gì?

Data validation trong Excel là một tính năng cụ thể giúp kiểm soát các giá trị có thể nhập vào một ô. Nó cung cấp nhiều kỹ thuật có thể dùng để kiểm tra cả các giá trị hiện có hoặc tạo các quy tắc sẽ áp dụng cho các giá trị được nhập sau.

Để tìm tính năng Data Validation trong Excel, chúng ta làm theo các bước sau:

1. Mở thẻ Data.
2. Đi đến nhóm Data Tools.
3. Nhấp vào nút Data Validation.

Mở tính năng Data Validation

Cửa sổ pop-up sau sẽ xuất hiện trên màn hình.

Cửa sổ pop-up Data Validation

Ở phần sau của hướng dẫn, chúng ta sẽ tìm hiểu chi tiết cách thiết lập cửa sổ này, bao gồm cách chỉnh sửa và xóa các quy tắc data validation. Trước tiên, hãy tìm hiểu thêm về data validation. 

Vì sao Data Validation quan trọng?

Data validation là quá trình kiểm tra tính chính xác và nhất quán của dữ liệu đã hoặc sẽ được đưa vào cơ sở dữ liệu để lưu trữ và xử lý tiếp. Với data validation, chúng ta đảm bảo dữ liệu tuân theo các tiêu chí cụ thể, được xác định trước. Các tiêu chí này có thể bao gồm đúng kiểu dữ liệu, giá trị nằm trong phạm vi xác định, và độ dài phần tử dữ liệu phù hợp.

Data validation vững chắc là điều bắt buộc vì nhiều lý do. Nó giúp ngăn lỗi nhập liệu và đảm bảo thông tin dùng trong phân tích là đáng tin cậy và hợp lệ. Hãy xem kỹ hơn các lý do chính cho data validation.

Chất lượng dữ liệu

Data validation ngăn chặn các giá trị không chính xác hoặc không nhất quán được lưu trong cơ sở dữ liệu. Điều này giảm lỗi phát sinh, nâng cao chất lượng dữ liệu tổng thể và tăng ý nghĩa của phân tích dữ liệu.

Tầm quan trọng của dữ liệu chất lượng cao được thể hiện hoàn hảo trong khái niệm GIGO, vốn phổ biến trong khoa học máy tính và là viết tắt của garbage-in, garbage-out (đầu vào rác, đầu ra rác). Về bản chất, phân tích dữ liệu sai lệch sẽ dẫn đến kết quả sai và gây hiểu lầm.

Hãy xem một số ví dụ khi không áp dụng các quy tắc data validation khiến dữ liệu có các giá trị kỳ lạ.

• Hồ sơ đại học: Một trường đại học không thể có số sinh viên là số thập phân; chỉ số nguyên mới hợp lệ.
• Hồ sơ y tế: Nhiệt độ cơ thể tối đa của một người không thể cao hơn 50°C.
• Nhập liệu tuổi: Trường tuổi chỉ nên chấp nhận các giá trị thực tế, thường trong khoảng 0 đến 120.
• Địa chỉ email: Trường email phải theo một định dạng cụ thể, bao gồm ký tự '@'. 
• Kiểm kê hàng hóa: Số lượng sản phẩm trong hệ thống tồn kho không được âm.

Hãy nhớ rằng dù data validation đảm bảo tính toàn vẹn dữ liệu, nó không đảm bảo tính đúng đắn tuyệt đối. Ví dụ, trong một bảng về mèo và chó, một quy tắc validation có thể đảm bảo chỉ cho phép nhập "cat" hoặc "dog", nhưng không thể ngăn việc gán nhầm một con mèo là chó.

Tính toàn vẹn dữ liệu và tuân thủ

Data validation rất quan trọng để duy trì tính toàn vẹn dữ liệu và đảm bảo tuân thủ các quy định, tiêu chuẩn của ngành.

Trong y tế, tuân thủ HIPAA là bắt buộc để bảo vệ thông tin bệnh nhân. Tương tự, các doanh nghiệp hoạt động tại EU phải tuân thủ GDPR, bảo đảm quyền riêng tư và an toàn dữ liệu cho cá nhân. 

Làm sạch và tiền xử lý dữ liệu

Một nguyên tắc nổi tiếng cho rằng các nhà phân tích dữ liệu dành khoảng 80% thời gian để chuẩn bị dữ liệu sẵn sàng sử dụng và chỉ 20% cho việc phân tích. Bởi vì họ thường dành phần lớn thời gian để làm sạch và tiền xử lý dữ liệu, các quy tắc data validation được áp dụng sớm trong quy trình có thể tiết kiệm rất nhiều thời gian. 

Ra quyết định

Phân tích dữ liệu đã được xác thực dẫn đến những phát hiện đáng tin cậy và sâu sắc, từ đó giúp đưa ra các quyết định dựa trên dữ liệu sáng suốt hơn. Về lâu dài, data validation giúp công ty tiết kiệm thời gian và chi phí, đồng thời mang lại hoạt động lành mạnh, hiệu quả và sinh lợi hơn.

Các kỹ thuật Data Validation khác nhau trong Excel

Excel có các cách tiếp cận data validation khác nhau để phù hợp với từng nhu cầu. Vì tiện lợi, hãy chia chúng thành các nhóm và mô tả từng nhóm.

Giới hạn kiểu dữ liệu và phạm vi/độ dài

Trong Excel, có thể chọn từ các kiểu dữ liệu sau: Whole number, Decimal, Date, Time và Text.

Giờ hãy tạo một bảng để xem xét tính áp dụng của một số điều kiện logic cho các kiểu dữ liệu khác nhau. Các điều kiện logic bao gồm: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, và less than or equal to.

• Between và Not Between: Các điều kiện này thường dùng với giá trị số, ngày và giờ. Khi áp dụng cho text, between và not between đề cập đến độ dài, hay số ký tự, trong một ô.

• Equal to và Not Equal to: Có thể áp dụng cho mọi kiểu dữ liệu. Với text, nó kiểm tra sự trùng khớp chính xác.

• Greater Than, Less Than, Greater Than or Equal To, và Less Than or Equal To: Các điều kiện này phù hợp với các kiểu dữ liệu whole number, date và time, nhưng không áp dụng trực tiếp cho text trừ khi xét đến độ dài văn bản hoặc, trong một số trường hợp cụ thể, thứ tự chữ cái.

Tạo danh sách xổ xuống

Đôi khi, bạn cần giới hạn các lựa chọn trong một số ô Excel theo một tập giá trị xác định trước. Hãy xét ví dụ trước đó, nơi chỉ mèo và chó là mục nhập hợp lệ trong một bảng. Cách tốt nhất để tạo quy tắc data validation nhằm ngăn cá sấu và các loài khác lọt vào bảng là tạo danh sách xổ xuống cho từng ô liên quan. Cùng xem các bước chi tiết.

1. Nhập các mục bạn muốn xuất hiện trong danh sách xổ xuống. Bạn cũng có thể sắp xếp danh sách bằng cách chọn toàn bộ danh sách, nhấp chuột phải, chọn Sort và chọn phương thức sắp xếp.

Nhập các mục cho danh sách xổ xuống

2. Chuyển danh sách của bạn thành bảng Excel. Chọn bất kỳ ô nào trong phạm vi và nhấn Ctrl+T. Bước này quan trọng vì nếu bạn thêm hoặc xóa mục khỏi danh sách, mọi danh sách xổ xuống phụ thuộc sẽ tự động được cập nhật.

Chuyển danh sách cho ô xổ xuống thành bảng

3. Chọn ô hoặc các ô Excel trong trang tính nơi bạn muốn thêm danh sách xổ xuống.
4. Mở cửa sổ pop-up Data Validation. Mở thẻ Data. Vào nhóm Data Tools và nhấp nút Data Validation.
5. Trên thẻ Settings, tại trường Allow, chọn List. Giữ chọn ô In-cell dropdown. Giữ chọn ô Ignore blank nếu ô Excel nơi bạn muốn thêm danh sách xổ xuống có thể để trống; nếu không, hãy bỏ chọn:

Đặt danh sách xổ xuống làm phương thức data validation

6. Đặt con trỏ vào hộp Source, chọn phạm vi danh sách và nhấn OK. Bạn cần bỏ qua hàng tiêu đề để tránh có nó trong các tùy chọn chọn.

Chọn nguồn cho danh sách xổ xuống

7. Chọn ô hoặc các ô Excel mà bạn đã tạo data validation. Giờ đây, bạn sẽ thấy một tam giác xổ xuống ở bên phải ô đó. Sau khi nhấp vào tam giác, bạn có thể chọn giá trị cần cho ô.

Mở danh sách xổ xuống trong một ô

Áp dụng công thức tùy chỉnh

• range: Ở đây chúng ta nói đến phạm vi các ô Excel mà chúng ta muốn áp dụng data validation dựa trên công thức tùy chỉnh.
• uppermost_cell: Ở đây chúng ta nói đến ô đầu tiên (cao nhất) trong phạm vi.

Chỉ số hoặc chỉ văn bản

Mục đích của hai công thức sau là xác minh nội dung của một ô cụ thể là số hay văn bản. Điều này hữu ích khi bạn cần phân biệt giữa giá trị số và không phải số, hoặc giữa văn bản và không phải văn bản, tương ứng.

=ISNUMBER(uppermost_cell)Được hỗ trợ bởi

=ISTEXT(uppermost_cell)Được hỗ trợ bởi

Chỉ văn bản in hoa hoặc chỉ văn bản in thường

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))Được hỗ trợ bởi

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))Được hỗ trợ bởi

Không trùng lặp

=COUNTIF(range, uppermost_cell)<=1Được hỗ trợ bởi

Chỉ các giá trị bắt đầu bằng một chuỗi con cụ thể

Các công thức sau kiểm tra xem n ký tự đầu của một chuỗi có khớp chính xác hay không. Kết quả sẽ trả về TRUE hoặc FALSE. 

=EXACT(LEFT(uppermost_cell, n), substring)Được hỗ trợ bởi

Chỉ ngày trong tuần hoặc chỉ cuối tuần

=WEEKDAY(uppermost_cell, 2)<=5Được hỗ trợ bởi

=WEEKDAY(uppermost_cell, 2)>5Được hỗ trợ bởi

Chỉ ngày trong quá khứ

=uppermost_cell<TODAY()Được hỗ trợ bởi

Không có khoảng trắng ở đầu hoặc cuối

=uppermost_cell=TRIM(uppermost_cell)Được hỗ trợ bởi

Không có khoảng trắng

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")Được hỗ trợ bởi

Không vượt quá tổng

=SUM(range)<=total_cellĐược hỗ trợ bởi

Để nhanh chóng nâng cao trình độ về công thức trong Excel, hãy cân nhắc tham gia khóa học thực hành và toàn diện Phân Tích Dữ Liệu Trong Excel.

Cách sử dụng Data Validation trong Excel

Tôi đã chia phần này thành hai tiểu mục để rõ ràng hơn. Tiểu mục đầu sẽ hướng dẫn bạn cách cấu hình sổ làm việc để tích hợp các quy tắc data validation. Phần thứ hai sẽ giải thích cách áp dụng các quy tắc này cho những ô đã có dữ liệu. Hãy cùng xem. 

Cách cấu hình sổ làm việc Excel cho các quy tắc data validation

1. Chọn ô hoặc các ô Excel nơi bạn muốn áp dụng data validation.
2. Mở cửa sổ pop-up Data Validation. Mở thẻ Data. Vào nhóm Data Tools và nhấp nút Data Validation.
3. Trên thẻ Settings, tại trường Allow, chọn phương thức cần thiết, mặc định là Any value (nghĩa là không có data validation).

Chọn phương thức cần thiết

4. Giữ chọn ô Ignore blank nếu ô Excel nơi bạn muốn thực hiện data validation có thể để trống; nếu không, hãy bỏ chọn.

Xử lý các ô trống

5. Nếu bạn đã chọn phương thức List, hãy chọn một quy tắc được xác định trước phù hợp với kiểu dữ liệu của bạn. Nếu bạn chọn phương thức Custom, hãy nhập công thức tùy chỉnh.

Chọn điều kiện logic để giới hạn phạm vi/độ dài dữ liệu

Nhập giới hạn dưới và trên để giới hạn phạm vi/độ dài dữ liệu

Áp dụng công thức tùy chỉnh

6. Trên thẻ Input Message, bạn có thể đặt Title và Input message. Mặc định không có thông điệp nhập. 

Đặt thông điệp nhập để nhắc người dùng loại giá trị phù hợp

Xem thông điệp nhập hiển thị như thế nào

7. Trên thẻ Error Alert, bạn có thể đặt Style (Stop, Warning hoặc Information), Title và Error message cho thông báo lỗi. Mặc định, thông báo lỗi có kiểu Stop.

Đặt thông báo lỗi

Sau khi nhấp OK, bạn có thể kiểm tra bằng cách chọn bất kỳ ô Excel trống nào mà data validation này áp dụng và nhập một giá trị không hợp lệ. Để so sánh, dưới đây là ví dụ thông báo lỗi ở mỗi kiểu. 

Nhận thông báo lỗi kiểu Stop khi nhập giá trị không hợp lệ

Nhận thông báo lỗi kiểu Warning khi nhập giá trị không hợp lệ

Nhận thông báo lỗi kiểu Information khi nhập giá trị không hợp lệ

Thông báo lỗi mặc định khi nhập giá trị không hợp lệ

Lưu ý: Thông báo lỗi kiểu Warning hoặc Information không ngăn người dùng nhập dữ liệu không hợp lệ, trong khi kiểu Stop thì có.

Cách dùng data validation trong Excel cho các ô đã có dữ liệu

Điều gì xảy ra nếu một số ô Excel nơi bạn muốn thực hiện data validation đã có sẵn giá trị? Tính năng Data Validation cho phép xác thực dữ liệu cả trước và sau khi nhập. 

1. Chọn các ô Excel nơi bạn muốn thực hiện data validation. Một số ô trong đó sẽ đã chứa giá trị.

Chọn các ô để data validation, bao gồm cả các ô đã có giá trị

2. Mở cửa sổ pop-up Data Validation. Mở thẻ Data. Vào nhóm Data Tools và mở nút Data Validation. 
3. Trên thẻ Settings, tại trường Allow, chọn phương thức cần thiết, mặc định là Any value.

Thiết lập data validation cho các ô đã chọn

3. Chọn bất kỳ ô Excel nào trong số các ô đã áp dụng data validation này, mở thẻ Data, nhấp vào mũi tên xổ xuống bên phải nút Data Validation trong nhóm Data Tools và chọn Circle Invalid Data.

Khoanh tròn dữ liệu không hợp lệ trong Excel

Dữ liệu không hợp lệ đã được khoanh tròn trong Excel

4. Bây giờ bạn có thể chỉnh sửa từng giá trị không hợp lệ để đáp ứng các tiêu chí đã đặt. Sau khi làm vậy, vòng tròn quanh giá trị sẽ tự động biến mất. Cần nhớ rằng nếu bạn chỉ xóa một giá trị không hợp lệ, vòng tròn vẫn sẽ còn.
5. Để xóa các vòng tròn xác thực, chọn bất kỳ ô Excel nào trong số các ô đã áp dụng data validation này, mở thẻ Data, vào nhóm Data Tools, mở nút Data Validation, rồi chọn Clear Validation Circles. 

Xóa các vòng tròn xác thực khỏi dữ liệu không hợp lệ

Cách chỉnh sửa Data Validation trong Excel

1. Chọn bất kỳ ô Excel nào đã được áp dụng data validation.
2. Mở cửa sổ pop-up Data Validation. Mở thẻ Data. Vào nhóm Data Tools và nhấp nút Data Validation.
3. Thực hiện mọi chỉnh sửa cần thiết ở bất kỳ thẻ nào của cửa sổ Data Validation. Nếu cần, hãy xem lại hướng dẫn từ phần Cách sử dụng Data Validation trong Excel.
4. Đi đến thẻ Settings và chọn Apply these changes to all other cells with the same settings.
5. Nhấn OK.

Áp dụng các chỉnh sửa data validation cho tất cả các ô có cùng thiết lập

Cách xóa Data Validation trong Excel

1. Chọn bất kỳ ô Excel nào đã được áp dụng data validation.
2. Mở cửa sổ pop-up Data Validation. Mở thẻ Data. Vào nhóm Data Tools và nhấp nút Data Validation.
3. Đi đến thẻ Settings và chọn Apply these changes to all other cells with the same settings.
4. Nhấp nút Clear All.
5. Nhấn OK.

Xóa data validation

Kết luận

Hướng dẫn này đã đề cập định nghĩa về data validation, lợi ích, cách triển khai trong Excel, các kỹ thuật có sẵn, và cách sử dụng, chỉnh sửa, cũng như xóa data validation trong Excel.

Nếu bạn muốn khai thác tối đa sức mạnh của Excel và học cách chuẩn bị, trực quan hóa, phân tích dữ liệu qua các bài tập thực hành, cũng như khám phá một số tính năng nâng cao, hãy thử lộ trình kỹ năng toàn diện và thân thiện với người mới bắt đầu Excel Fundamentals. Ngoài ra, hãy cân nhắc các khóa học chuyên sâu như Data Preparation in Excel hoặc Financial Modeling in Excel để được đào tạo trọng tâm, giúp bạn nâng cao chuyên môn. Những tài nguyên này sẽ giúp bạn tiến từ data validation cơ bản đến làm chủ các tính năng Excel nâng cao.