20 Công Thức Data Validation Nâng Cao Trong Excel

Data Validation là tính năng kiểm soát dữ liệu nhập vào Excel, giúp giảm sai sót và tự động hóa biểu mẫu. Bài viết tổng hợp 20 công thức nâng cao nhất, từ dropdown phụ thuộc đến kiểm tra trùng lặp.

Data Validation Là Gì? #

Data Validation là quy tắc ràng buộc dữ liệu nhập vào một ô hoặc range. Truy cập tại Data → Data Validation. Bạn có thể giới hạn kiểu dữ liệu (số, ngày, text length...), tạo dropdown list, và hiển thị thông báo lỗi tùy chỉnh.

Nhóm 1: Dropdown List Nâng Cao #

1. Dropdown Từ Named Range #

Tạo Named Range cho danh sách, rồi dùng trong validation:

Bước 1: Formulas → Name Manager → New → Name: DanhMucSP
Bước 2: Data Validation → List → Source: =DanhMucSP

2. Dropdown Phụ Thuộc (Dependent Dropdown) #

Dropdown thứ 2 thay đổi theo lựa chọn của dropdown thứ 1. Sử dụng INDIRECT:

Dropdown 1 (Danh mục): =DanhMuc
Dropdown 2 (Sản phẩm): =INDIRECT(A2)

Lưu ý: Tên Named Range phải trùng với giá trị dropdown 1
Ví dụ: Nếu A2 = "DienTu" → Named Range "DienTu" chứa các sản phẩm điện tử

3. Dropdown Tự Động Mở Rộng #

Khi thêm item mới vào danh sách, dropdown tự cập nhật:

Source: =OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A:$A), 1)

Hoặc dùng Table: chuyển danh sách thành Table (Ctrl+T)
rồi dùng Source: =Table1[TenCot]

4. Dropdown Không Trùng Lặp (Excel 365) #

Chỉ hiển thị giá trị duy nhất trong dropdown:

Source: =UNIQUE(Sheet2!A2:A100)

Nhóm 2: Kiểm Tra Số #

5. Chỉ Cho Phép Số Nguyên Dương #

Allow: Custom
Formula: =AND(ISNUMBER(A1), A1=INT(A1), A1>0)

6. Giá Trị Phải Là Bội Số #

Formula: =MOD(A1, 5) = 0
→ Chỉ chấp nhận bội số của 5 (5, 10, 15, 20...)

7. Giới Hạn Số Thập Phân #

Formula: =LEN(A1) - LEN(INT(A1)) - 1 <= 2
→ Tối đa 2 chữ số sau dấu chấm thập phân

Nhóm 3: Kiểm Tra Text #

8. Chỉ Cho Phép Chữ IN HOA #

Formula: =EXACT(A1, UPPER(A1))

9. Bắt Đầu Bằng Ký Tự Cụ Thể #

Formula: =LEFT(A1, 2) = "VN"
→ Mã sản phẩm phải bắt đầu bằng "VN"

10. Kiểm Tra Định Dạng Email #

Formula: =AND(
  ISERROR(FIND(" ", A1)),
  LEN(A1) - LEN(SUBSTITUTE(A1, "@", "")) = 1,
  FIND("@", A1) > 1,
  FIND(".", A1, FIND("@", A1)) > FIND("@", A1) + 1
)

11. Kiểm Tra Số Điện Thoại Việt Nam #

Formula: =AND(LEFT(A1,1)="0", LEN(A1)=10, ISNUMBER(A1*1))
→ Bắt đầu bằng 0, đúng 10 chữ số, toàn số

Nhóm 4: Kiểm Tra Ngày Tháng #

12. Ngày Không Được Là Cuối Tuần #

Formula: =WEEKDAY(A1, 2) <= 5
→ Chỉ chấp nhận ngày trong tuần (Thứ 2 → Thứ 6)

13. Ngày Phải Trong Tháng Hiện Tại #

Formula: =AND(
  MONTH(A1) = MONTH(TODAY()),
  YEAR(A1) = YEAR(TODAY())
)

14. Ngày Kết Thúc Phải Sau Ngày Bắt Đầu #

Áp dụng cho ô B1 (ngày kết thúc):
Formula: =B1 > A1

Nhóm 5: Chống Trùng Lặp #

15. Không Cho Phép Giá Trị Trùng #

Áp dụng cho range A2:A100:
Formula: =COUNTIF($A$2:$A$100, A2) <= 1

16. Không Trùng Với Sheet Khác #

Formula: =COUNTIF(Sheet2!$A:$A, A1) = 0
→ Giá trị nhập vào không được tồn tại trong cột A của Sheet2

Nhóm 6: Kiểm Tra Tổng Hợp #

17. Tổng Cột Không Vượt Quá Ngân Sách #

Áp dụng cho range B2:B20 (chi phí):
Formula: =SUM($B$2:$B$20) <= $D$1
→ Tổng chi phí không vượt quá giá trị ngân sách ở D1

18. Tỷ Lệ Phần Trăm Phải Tổng = 100% #

Áp dụng cho range C2:C10:
Formula: =SUM($C$2:$C$10) <= 1

19. Ô Bắt Buộc Nhập Nếu Ô Khác Có Giá Trị #

Áp dụng cho B1 (ghi chú bắt buộc khi A1 = "Khác"):
Formula: =OR(A1<>"Khác", AND(A1="Khác", LEN(B1)>0))

20. Chỉ Cho Phép Giá Trị Từ Danh Sách Đã Duyệt #

Formula: =NOT(ISNA(MATCH(A1, ApprovedList, 0)))
→ Giá trị phải nằm trong Named Range "ApprovedList"

Mẹo Khi Dùng Data Validation #

1. Input Message: Thêm hướng dẫn nhập liệu hiển thị khi click vào ô (tab Input Message trong Data Validation).

2. Error Alert tùy chỉnh: Thay đổi Style (Stop/Warning/Information) và nội dung thông báo lỗi (tab Error Alert).

3. Circle Invalid Data: Data → Circle Invalid Data để khoanh tròn các ô vi phạm quy tắc (hữu ích khi paste data bỏ qua validation).

4. Ctrl+G → Special → Data Validation: Chọn tất cả ô có Data Validation trong sheet để quản lý hoặc xóa hàng loạt.

5. Copy Validation: Copy ô có validation → Paste Special → Validation để áp dụng nhanh cho nhiều ô.

Tổng Kết #

Data Validation biến Excel thành công cụ nhập liệu chuyên nghiệp. 20 công thức trên bao quát hầu hết nhu cầu thực tế:

• Dropdown: Named Range, phụ thuộc, tự mở rộng, unique

• Số: Nguyên dương, bội số, giới hạn thập phân

• Text: IN HOA, tiền tố, email, SĐT

• Ngày: Không cuối tuần, trong tháng, sau ngày bắt đầu

• Logic: Chống trùng, giới hạn tổng, conditional required