Data Validation Nâng Cao Trong Excel: Dropdown Phụ Thuộc, Custom Rules Và Bảo Vệ Dữ Liệu

Data Validation là tuyến phòng thủ đầu tiên chống nhập sai dữ liệu. Nhưng hầu hết người dùng chỉ biết tạo dropdown cơ bản. Bài viết này sẽ giới thiệu các kỹ thuật nâng cao: dropdown phụ thuộc (chọn Tỉnh → tự lọc Quận), custom rules, và cách kết hợp với Conditional Formatting.

1. Dropdown Cơ Bản — Ôn Lại Nhanh #

// Data → Data Validation → Allow: List
// Source: trực tiếp
Hà Nội, HCM, Đà Nẵng, Cần Thơ

// Source: từ range
=$Sheet2.$A$1:$A$10

// Source: từ Named Range
=DanhSachTinh

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

Ví dụ: Chọn Tỉnh/Thành ở cột B → cột C chỉ hiện danh sách Quận/Huyện của tỉnh đã chọn.

2.1 Cách 1: INDIRECT + Named Range #

Bước 1: Tạo Named Range cho từng tỉnh — tên range = tên tỉnh (không dấu, gạch dưới thay khoảng trắng):

// Named Ranges:
// Ha_Noi = Sheet2!$B$1:$B$10  (Ba Đình, Hoàn Kiếm, Đống Đa...)
// HCM = Sheet2!$C$1:$C$15     (Quận 1, Quận 3, Bình Thạnh...)
// Da_Nang = Sheet2!$D$1:$D$8  (Hải Châu, Thanh Khê...)

// Data Validation cho cột C (Quận/Huyện):
// Allow: List
// Source: =INDIRECT(SUBSTITUTE(B2," ","_"))

Giải thích: INDIRECT chuyển text thành reference. Khi B2 = "Hà Nội", SUBSTITUTE đổi thành "Ha_Noi", INDIRECT tham chiếu đến Named Range Ha_Noi.

2.2 Cách 2: FILTER (Excel 365) #

// Bảng dữ liệu: Sheet2 có 2 cột: A=Tỉnh, B=Quận
// Data Validation cột C: Allow: List
// Source không dùng được FILTER trực tiếp

// Giải pháp: Tạo helper column dùng FILTER
// Ô H1: =FILTER(Sheet2!B:B, Sheet2!A:A=B2)
// Data Validation: Source = $H$1:$H$20

2.3 Dropdown 3 cấp #

Tỉnh → Quận → Phường: làm tương tự, mỗi cấp INDIRECT tham chiếu đến Named Range dựa trên giá trị cấp trước.

3. Custom Validation Rules #

Chọn Allow: Custom → viết công thức trả về TRUE/FALSE.

3.1 Chỉ cho nhập số điện thoại đúng format #

// Cho phép: 10 chữ số, bắt đầu bằng 0
=AND(LEN(A2)=10, LEFT(A2,1)="0", ISNUMBER(A2*1))

3.2 Không cho nhập trùng #

// Không cho cột A có giá trị trùng
=COUNTIF($A:$A, A2)<=1

3.3 Ngày phải lớn hơn ngày hiện tại #

=A2>TODAY()

3.4 Email hợp lệ #

// Kiểm tra có @ và dấu chấm sau @
=AND(ISERROR(FIND(" ",A2)), NOT(ISERROR(FIND("@",A2))), NOT(ISERROR(FIND(".",A2,FIND("@",A2)))))

3.5 Tổng cột không vượt quá budget #

// Cho phép nhập vào cột D, nhưng tổng D không vượt quá 100 triệu
=SUM($D:$D)<=100000000

4. Input Message Và Error Alert #

4.1 Input Message #

Tab Input Message: hiện tooltip hướng dẫn khi user click vào ô.

• Title: "Nhập Số điện thoại"

• Input Message: "Nhập 10 chữ số, bắt đầu bằng 0. VD: 0912345678"

• Giúp user biết format đúng TRƯỚC KHI nhập → giảm lỗi

4.2 Error Alert — 3 loại #

• Stop (🛑): Không cho nhập sai — bắt buộc nhập lại. Dùng cho dữ liệu critical (mã sản phẩm, TK kế toán)

• Warning (⚠️): Cảnh báo nhưng vẫn cho nhập. Dùng cho soft rules (giá trị bất thường)

• Information (ℹ️): Chỉ thông báo. Dùng cho gợi ý nhẹ

5. Kết Hợp Với Conditional Formatting #

Data Validation ngăn nhập sai, Conditional Formatting hiển thị trực quan:

// Highlight ô có Data Validation bị vi phạm (paste from external)
// Home → Conditional Formatting → New Rule → Use a formula:

// Highlight SĐT sai format
=OR(LEN(A2)<>10, LEFT(A2,1)<>"0")
// Format: đỏ nhạt background

// Highlight giá trị trùng
=COUNTIF($A:$A, A2)>1
// Format: vàng background

6. Circle Invalid Data #

Ít người biết: Data → Data Validation → Circle Invalid Data. Excel sẽ khoanh tròn đỏ tất cả ô vi phạm validation rules — cực hữu ích khi data được paste từ nguồn khác.

7. Tips Nâng Cao #

• Dùng INDIRECT với Table name cho dropdown động: =INDIRECT("Table1[TenCot]")

• Copy Data Validation: copy ô có DV → Paste Special → Validation

• Xóa DV hàng loạt: Select all → Data → DV → Clear All

• Kết hợp DV + VBA Worksheet_Change để auto-fill các ô liên quan

• Lock cells + Protect Sheet sau khi setup DV để user không tắt được

• Dùng Name Manager (Ctrl+F3) để quản lý Named Ranges cho dropdown

Kết Luận #

Data Validation là kỹ thuật "phòng bệnh hơn chữa bệnh" — setup 1 lần, tiết kiệm hàng giờ sửa lỗi. Dropdown phụ thuộc + Custom Rules + Conditional Formatting = bộ ba bảo vệ dữ liệu hoàn hảo.

Theo dõi Trà Đá Data để học thêm Excel nâng cao! 🍵