Đề tài Data Validation trong Excel

 khai báo này, vùng danh sách phải cùng nằm trong cùng một sheet với những ô 
cần thể hiện. Điều đó là hạn chế nếu ta khai thác vùng danh sách này ở sheet khác. Vậy cách 
khắc phục hạn chế này như thế nào? Bằng cách tạo Name cho vùng (K2:K15) là chúng ta có 
thể khắc phục nhược điểm này. Khi đó trong Source sẽ thể hiện tên vùng (hình 14). 
Hình 13: Khai báo danh sách từ vùng trong bảng tính 
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 9 
Hình 14: Khai báo danh sách từ tên một vùng 
Bên cạnh những ưu điểm trên, hạn chế của chức năng List là không tự động dò đến phần 
tử cần tìm trong danh sách khi gõ ký tự đầu tiên. Vì vậy, với danh sách nhiều đối tượng việc dò 
tìm sẽ mất nhiều thời gian. 
1.1.4. Date: 
Chức năng này chỉ cho phép nhập liệu là ngày. Việc điều khiển về khoảng ngày nhập giống 
như đối với Whole number. Điều đó có nghĩa là chọn ngày trong 1 khoảng xác định, ngày bắt 
đầu, ngày kết thúc,... 
Hình 15: Khai báo phạm vi ngày nhập 
Ví dụ như trong hình 15, chỉ cho phép nhập ngày trong khoảng từ ngày hôm nay (Today) và 
cộng thêm 3 ngày nữa. Cần lưu ý về định dạng ngày tháng và cách nhập liệu ngày tháng cho 
đú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. 
Hình 18: Thông báo khi chọn ô khi dùng Input message 
1.3. Chức năng Error Alert 
Như chúng ta đã biết, khi ô đã được đặt chế độ Data validation, nếu nhập liệu không đúng 
quy định sẽ bị thông báo ở hình 6. Ta có thể thay đổi nội dung thông báo mặc định đó theo các 
hoàn cảnh khác nhau và hiển thị nội dung thông báo đó bằng tiếng Việt cho dễ hiểu và sửa đổi. 
Từ hình 2 chuyển sang Error Alert (hình 19), cửa sổ Error Alert gồm các nội dung sau: 
- Show error alert after invalid data is selected: Bật (tắt) chế độ hiển thị cảnh báo sau khi 
dữ liệu được nhập vào ô. Trường hợp này chọn Bật. 
- Style: Kiểu cảnh báo, gồm Stop (dừng lại), Warrning (cảnh báo), Information (thông tin). 
Tuỳ mức độ cảnh báo mà có cách xử lý phù hợp. 
 + Stop : Thông báo lỗi nhập liệu nghiêm trọng, Excel không chấp nhận giá trị nhập 
liệu này và yêu cầu phải nhập đúng mới được chấp nhận (hình 20). 
 + Warning : Thông báo lỗi nhập liệu mang tính cảnh báo, tùy trường hợp có thể chấp 
nhập hoặc không chấp nhận giá trị nhập liệu (hình 21). 
 + Stop : Thông báo lỗi nhập liệu mang tính thông tin, bạn có thể bỏ qua trường hợp 
nhập liệu không đúng quy định (hình 22). 
- Title: Nội dung tiêu đề thông báo, dùng kiểu gõ Unicode. 
- Input message: Nội dung thông báo (chú ý nội dung theo những hoàn cảnh cụ thể), dùng 
kiểu gõ Unicode. 
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 12 
Hình 19: Cửa sổ Error Alert 
Hình 20: Thông báo Stop khi nhập liệu không đúng 
Hình 21: Thông báo Warrning khi nhập liệu không đúng 
Như vậy, chúng ta đã tìm hiểu một cách khá tổng quát về chức năng Data validation. Chức 
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, 
tương ứng với nó là các huyện (quận) khai báo trong B2:B21. Để đơn giản, chúng ta lập thêm 
danh sách C2:C4 chứa tên các tỉnh (thành phố) bằng cách thủ công(có thể dùng VBA nhưng tôi 
không đề cập trong nội dung này). 
Bây giờ chúng ta xây dựng vùng nhập liệu cho tỉnh (thành phố) và huyện (quận) tương ứng 
trong E2:F13. Với trường hợp danh sách là tỉnh (thành phố) thì quá đơn giản, cách thực hiện 
như hình 24. Tuy nhiên, để lọc được danh sách các huyện (quận) tương ứng với tỉnh (thành 
phố) đó thì không hề đơn giản. Để giải quyết bài toán này bắt buộc phải sử dụng công thức và 
hàm trong Source (hình 25): 
=OFFSET($A$1,MATCH(E2,$A$2:$A$21,0),1,COUNTIF($A$2:$A$21,E2),1) 
Trong công thức trên, hàm Match xác định ô đầu tiên của vùng huyện (quận), Countif xác 
định số ô chứa huyện (quận) trong 1 tỉnh (thành phố). Hàm Offset sẽ tham chiếu đến cả vùng 
chứa huyện (quận) trong 1 tỉnh. Cần lưu ý về cách sử dụng địa chỉ tuyệt đối và tương đối của 
hàm này. 
Như vậy, mức độ khai thác chức năng Data validation còn phụ thuộc vào khả năng sử dụng 
hàm và công thức của người sử dụng. Khi đó ứng dụng còn rộng rãi hơn, mạnh mẽ hơn. 
Hình 24: Tạo danh sách tham chiếu phụ thuộc 
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 15 
Hình 25: Tạo danh sách các huyện (quận) tương ứng với tỉnh (thành phố) 
Hình 26: Danh sách nhập liệu các huyện (quận) tương ứng với tỉnh (thành phố) 
1.4.3. Giới hạn tổng giá trị nhập liệu trong vùng: 
Trong một số trường hợp, yêu cầu tổng giá trị nhập trong vùng chỉ tới 1 giới hạn nào đó. Nếu 
tổng giá trị nhập vượt vùng giới hạn đó thì Excel sẽ báo lỗi. Ví dụ tổng thu nhập của 1 người là 
15 triệu/ tháng. Khoản tiền đó sẽ được chi tiêu trong 1 tháng với nhiều loại chi phí (hình 27). 
Tại trường hợp này, sử dụng Custom trong danh sách của Allow, công thức nhập ở dưới: 
=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 tuhuongdcct36@gmail.com 
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! 
Tài liệu tham khảo chính và một số nội dung lấy từ: