Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 1

Table of Contents
1. Đặt vấn đề 2
1.1. Chức năng Settings 3
1.1.1. Whole number: 3
1.1.2. Decimal: 6
1.1.3. List: 7
1.1.4. Date: 9
1.1.5. Text length: 10
1.2. Chức năng Input Message 10
1.3. Chức năng Error Alert 11
1.4. Các ví dụ về Data validation 13
1.4.1. Nhập số thập phân tăng dần theo 1 cột: 13
1.4.2. Tạo danh sách tham chiếu phụ thuộc: 14
1.4.3. Giới hạn tổng giá trị nhập liệu trong vùng: 15
2. Kết luận 16 Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 2

SỬ DỤNG CHỨC NĂNG DATA VALIDATION TRONG EXCEL
Giới thiệu: Khi xây dựng bảng tính trong Excel, một số trường hợp yêu cầu nhập liệu là
giá trị cụ thể hoặc trong danh sách sẵn có. Làm thế nào bạn có thể kiểm soát được dữ liệu đầu
vào đó theo đúng yêu cầu? Làm thế nào Excel tự thông báo lỗi nhập không đúng? Chức năng
Data validation (xác nhận tính hợp lệ của dữ liệu) sẽ giúp bạn thực hiện công việc đó một cách
khá trọn vẹn những đòi hỏi trên.

thi, Khi chọn Whole number, chức năng Data xuất hiện cho phép khống chế phạm vi giá trị
nhập (hình 5).
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 4 Hình 4: Chọn kiểu phạm vi nhập liệu
a. Between (not between):
Chỉ cho phép nhập giá trị trong một vùng xác định (hoặc ngoài một vùng xác định). Ví dụ
điểm thi phải nằm trong phạm vi từ 0 đến 10, ngày trong tháng từ 1 đến 31, các thứ trong
tuần,
- Minimum: giá trị giới hạn nhỏ nhất khi nhập liệu.
- Maximum: giá trị giới hạn lớn nhất khi nhập liệu.
- Ingnore blank: bỏ qua ô trống, tức là không xét điều kiện nhập liệu khi ô trống.
- Clear All: huỷ bỏ
Giá trị giới hạn có thể cố định (không thay đổi được) hoặc không cố định (có thể thay đổi
được) tuỳ vào mục đích sử dụng.

Hình 5: Phạm vi nhập liệu
- Giới hạn cố định: là số khi nhập giá trị trong ô giới hạn. Trong hình 5, giá trị nhập nhỏ nhất
bằng 0 và lớn nhất là 100. Nếu bạn nhập giá trị ngoài khoảng, Excel sẽ báo lỗi như hình 6
(thông báo lỗi mặc định của Excel). Chúng ta có thể thay đổi nội dung thông báo đó theo mục
đích sử dụng cụ thể ở mục 1.3.
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 5 Hình 6: Thông báo lỗi nhập liệu mặc định trong Excel

1.1.2. Decimal:
Chức năng này chỉ cho phép nhập liệu là số nguyên hoặc số thập phân (hình 3). Cách thực
hiện tương tự đối với Whole number. Sử dụng kiểu giá trị Decimal cho phép tránh được
những sai sót do quy định về số thập phân Quốc tế và Việt Nam (dấu chấm và phẩy).
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 7 Hình 10: Giá trị giới hạn lớn nhất khi nhập liệu
1.1.3. List:
Chức năng này chỉ cho phép nhập liệu từ một danh sách sẵn có (hình 3). Danh sách này có
thể được nhập trực tiếp, từ một vùng trong sheet, từ tên (Name) vùng sẵn có hoặc từ file khác.
Nên sử dụng chức năng này khi bạn cần nhập liệu được lấy từ một cơ sở dữ liệu (CSDL) sẵn
có nằm hạn chế sai sót, rút ngắn thời gian. Ví dụ như nhập 1 phần tử (linh kiện, môn học,
người, ) trong danh sách phụ tùng xe máy, ô tô, máy tính, môn học, danh sách thành viên,
đã có sẵn. Điều này cho phép hạn chế tối đa do nhập liệu không đúng dẫn đến kết quả tính
toán sai, đặc biệt là đối tượng nhập là chuỗi.

Hình 11: Tạo danh sách dữ liệu trực tiếp trong Source
a. Danh sách nhập trực tiếp:
Bạn gõ danh trực tiếp trong Source (hình 11), mỗi phần tử được ngăn cách bởi dấu phẩy.
Với danh sách cố định (không sửa đổi) nên sử dụng trường hợp này. Với trường hợp nhập trực
tiếp, bạn phải dùng kiểu gõ Unicode để hiển thị tiếng Việt. Nếu sử dụng kiểu gõ TCVN thì sẽ bị
lỗi hiển thị chữ tiếng Việt, nhưng nội dung hiển thị trên ô không bị sai (tức là chỉ khó nhìn thôi).
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 8

Trong cửa sổ Data validation (hình 11), chức năng In-cell dropdown cho phép khi chọn

đúng, nếu không Excel luôn báo lỗi nhập liệu.
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 10

1.1.5. Text length:
Chức năng này chỉ cho phép nhập liệu là chuỗi có độ dài xác định (tính bằng số ký tự, kể cả
khoảng trắng, dấu, ). Việc điều khiển về chiều dài chuỗi nhập giống như đối với Whole
number. Điều đó có nghĩa là có thể kiểm soát được chiều dài chuỗi nhập giới hạn trong 1
khoảng xác định, chiều dài chuỗi nhập nhỏ nhất, chiều dài chuỗi nhập lớn nhất, Ví dụ như
trong hình 16, chỉ cho phép nhập mã hàng có chiều dài 6 ký tự.

Hình 16: Khai báo mã hàng có chiều dài 6 ký tự

Hình 17: Khai báo mã hàng có chiều dài 6 ký tự
1.2. Chức năng Input Message
Chức năng Input Message cho phép hiển thị thông tin nhập liệu khi di chuyển chuột vào ô
đó, từ đó định hướng cho công việc nhập liệu. Tất nhiên khi thực hiện chức năng này thì bạn
đã thiết lập chế độ Settings cho các ô đó. Để hiển thị thông tin nhập liệu cho khối ô nào đó,
đầu tiên ta bôi đen khối ô đó (C6:C15 trong hình 17). Bước thực hiện như hình 1, 2 và chuyển
sang Input Message (hình 17). Cửa sổ Input Message gồm các nội dung sau:
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 11

- Show input message when cell is selected: Bật (tắt) chế độ hiển thị thông báo khi ô
được chọn. Trường hợp này chọn Bật.
- Title: Nội dung tiêu đề hiển thị (hình 18), dùng kiểu gõ Unicode.
- Input message: Nội dung thông báo, dùng kiểu gõ Unicode.


năng cho phép kiểm soát tốt những dữ liệu nhập, đặc biệt là khi thực hiện với CSDL. Chức
năng này giúp Excel trở nên gần gũi hơn với phần mềm Access (chuyên về CSDL).
Để hiểu rõ hơn khả năng áp dụng Data validation, chúng ta sẽ đi vào các ví dụ cụ thể ở
mục tiếp theo.
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 13 Hình 22: Thông báo Information khi nhập liệu không đúng
1.4. Các ví dụ về Data validation
1.4.1. Nhập số thập phân tăng dần theo 1 cột:
Trong nhiều bài toán về kỹ thuật, yêu cầu nhập liệu là số (số thập phân) theo 1 cột với giá trị
tăng dần. Nếu sai do không kiểm soát tốt (chẳng hạn giá trị sau nhỏ hơn giá trị trước), quá trình
tính toán sẽ bị sai lệch. Có thể sử dụng cách lập trình sự kiện đối với ô nhập (VBA) để kiểm
soát nhưng sẽ phức tạp, nhất là khi xử lý với nhiều ô, nhiều khối ô. Khi sử dụng Data validation
thì công việc trở nên khá đơn giản.
Trong hình 23, vùng D7:D29 yêu cầu sắp xếp theo giá trị tăng dần, giá trị nhỏ nhất tại ô D7.
Chú ý khi khai báo công thức trong Validation sử dụng địa chỉ tuyệt đối.

Hình 23: Thiết lập nhập dữ liệu là số tăng dần
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 14

1.4.2. Tạo danh sách tham chiếu phụ thuộc:
Có nhiều bài toán phải dùng đến 2 hay 3 danh sách có liên quan đến nhau. Ví dụ như trong
hình 24 có danh sách tỉnh (thành phố), trong mỗi tỉnh (thành phố) lại có các huyện (quận) khác
nhau. Yêu cầu là khi lọc dữ liệu cho 1 tỉnh (thành phố) nào đó thì danh sách các huyện (quận)
của tỉnh đó phải hiện ra. Ở ví dụ này, danh sách tỉnh (thành phố) được khai báo trong A2:A21,

=SUM($C$5:$C$12)<$C$2
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 16 Hình 27: Sử dụng Custom để quản lý giới hạn nhập liệu
Sau đó thiết lập thông báo lỗi trong Error Alert, kiểu Warning. Khi nhập quá giới hạn trên,
thông báo sẽ hiện ra và bạn tuỳ chọn cách xử lý (hình 28).

Hình 28: Thông báo lỗi khi chi tiêu vượt quá giới hạn
2. Kết luận
- Data validation là chức năng rất hay và linh hoạt trong việc xử lý dữ liệu nhập trong bảng
tính. Khả năng kiểm soát dữ liệu kèm theo thông báo hỗ trợ giúp người sử dụng đưa ra biện
pháp xử lý thích hợp.
- Kết hợp sự định dạng, trang trí bảng tính, thiết kế giao diện, khai thác và sử dụng hàm cùng
với Data validation sẽ giúp chúng ta có một bảng tính hoàn chỉnh, đẹp, khả năng ứng dụng cao.
Đó cũng là xu hướng tất yếu với những ai xây dựng các sản phẩm ứng dụng trong Excel.
- Với khả năng này, Excel đã trở nên gần gũi hơn với CSDL vì có khả năng kiểm soát dữ liệu
nhập. Việc khai thác CSDL trong Excel bằng hàm, công thức và các chức năng nâng cao khác
đã giúp nhiều người sử dụng Excel làm CSDL.
Data Validation trong Excel Tác giả: PhanTuHuong (tuhuongdcct36@gmailcom)

Bài viết mừng sinh nhật diễn đàn www.giaiphapexcel.com tròn 4 tuổi (02/7/2010) Trang 17 Mọi ý kiến góp ý, bổ sung xin gửi tới [email protected]
Chúc các bạn sử dụng tốt ứng dụng này trong công việc chuyên
môn của mình!